Загрузка данных из BigQuery в ClickHouse

Этот гид совместим с ClickHouse Cloud и для самоуправляемого ClickHouse версии v23.5+.

Этот гид показывает, как мигрировать данные из BigQuery в ClickHouse.

Сначала мы экспортируем таблицу в объектное хранилище Google (GCS), а затем импортируем эти данные в ClickHouse Cloud. Эти шаги необходимо повторить для каждой таблицы, которую вы хотите экспортировать из BigQuery в ClickHouse.

Как долго будет длиться экспорт данных в ClickHouse?

Экспорт данных из BigQuery в ClickHouse зависит от размера вашего набора данных. Для сравнения, перенос публичного набора данных Ethereum объемом 4 ТБ из BigQuery в ClickHouse с использованием данного гида занимает около часа.

Таблица	Строки	Экспортированные файлы	Размер данных	Экспорт из BigQuery	Время слота	Импорт в ClickHouse
blocks	16,569,489	73	14.53GB	23 сек	37 мин	15.4 сек
transactions	1,864,514,414	5169	957GB	1 мин 38 сек	1 день 8 часов	18 мин 5 сек
traces	6,325,819,306	17,985	2.896TB	5 мин 46 сек	5 дней 19 часов	34 мин 55 сек
contracts	57,225,837	350	45.35GB	16 сек	1 час 51 мин	39.4 сек
Всего	8.26 миллиардов	23,577	3.982TB	8 мин 3 сек	> 6 дней 5 часов	53 мин 45 сек

Экспорт данных таблицы в GCS

На этом этапе мы используем SQL-рабочую область BigQuery для выполнения наших SQL-команд. Ниже мы экспортируем таблицу BigQuery под названием mytable в корзину GCS с помощью оператора EXPORT DATA.

DECLARE export_path STRING;
DECLARE n INT64;
DECLARE i INT64;
SET i = 0;

-- We recommend setting n to correspond to x billion rows. So 5 billion rows, n = 5
SET n = 100;

WHILE i < n DO
  SET export_path = CONCAT('gs://mybucket/mytable/', i,'-*.parquet');
  EXPORT DATA
    OPTIONS (
      uri = export_path,
      format = 'PARQUET',
      overwrite = true
    )
  AS (
    SELECT * FROM mytable WHERE export_id = i
  );
  SET i = i + 1;
END WHILE;

В приведенном выше запросе мы экспортируем нашу таблицу BigQuery в формат данных Parquet. Мы также используем символ * в нашем параметре uri. Это гарантирует, что выходные данные будут разбиты на несколько файлов, с возрастающим числовым суффиксом, если экспорт превышает 1 ГБ данных.

Этот подход имеет несколько преимуществ:

• Google позволяет экспортировать до 50 ТБ в день в GCS бесплатно. Пользователи платят только за хранение в GCS.
• Экспорт автоматически создает несколько файлов, ограничивая каждый максимум до 1 ГБ данных таблицы. Это полезно для ClickHouse, поскольку позволяет параллелизовать импорт.
• Parquet, будучи ориентированным на колонки форматом, представляет собой лучший формат обмена, так как он изначально сжат и быстрее для экспорта из BigQuery и для запросов в ClickHouse.

Импорт данных в ClickHouse из GCS

После завершения экспорта мы можем импортировать эти данные в таблицу ClickHouse. Вы можете использовать SQL-консоль ClickHouse или clickhouse-client для выполнения команд ниже.

Сначала вы должны создать свою таблицу в ClickHouse:

-- If your BigQuery table contains a column of type STRUCT, you must enable this setting
-- to map that column to a ClickHouse column of type Nested
SET input_format_parquet_import_nested = 1;

CREATE TABLE default.mytable
(
        `timestamp` DateTime64(6),
        `some_text` String
)
ENGINE = MergeTree
ORDER BY (timestamp);

После создания таблицы, включите настройку parallel_distributed_insert_select, если у вас есть несколько реплик ClickHouse в вашем кластере, чтобы ускорить наш экспорт. Если у вас только один узел ClickHouse, вы можете пропустить этот шаг:

SET parallel_distributed_insert_select = 1;

Наконец, мы можем вставить данные из GCS в нашу таблицу ClickHouse с помощью команды INSERT INTO SELECT, которая вставляет данные в таблицу на основе результатов запроса SELECT.

Чтобы получить данные для INSERT, мы можем использовать функцию s3Cluster для извлечения данных из нашей корзины GCS, так как GCS совместим с Amazon S3. Если у вас только один узел ClickHouse, вы можете использовать функцию s3 вместо функции s3Cluster.

INSERT INTO mytable
SELECT
    timestamp,
    ifNull(some_text, '') AS some_text
FROM s3Cluster(
    'default',
    'https://storage.googleapis.com/mybucket/mytable/*.parquet.gz',
    '<ACCESS_ID>',
    '<SECRET>'
);

ACCESS_ID и SECRET, используемые в приведенном выше запросе, это ваш HMAC ключ, связанный с вашей корзиной GCS.

примечание

Используйте ifNull при экспорте nullable колонок

В приведенном выше запросе мы используем функцию ifNull с колонкой some_text, чтобы вставить данные в нашу таблицу ClickHouse с помощью значения по умолчанию. Вы также можете сделать свои колонки в ClickHouse Nullable, но это не рекомендуется, так как может негативно сказаться на производительности.

В качестве альтернативы вы можете SET input_format_null_as_default=1, и все пропущенные или NULL значения будут заменены значениями по умолчанию для соответствующих колонок, если эти значения по умолчанию указаны.

Проверка успешного экспорта данных

Чтобы проверить, были ли ваши данные правильно вставлены, просто выполните запрос SELECT на вашей новой таблице:

SELECT * FROM mytable LIMIT 10;

Чтобы экспортировать больше таблиц BigQuery, просто повторите шаги выше для каждой дополнительной таблицы.

Дальнейшее чтение и поддержка

В дополнение к этому гиду, мы также рекомендуем прочитать наш пост в блоге, который показывает как использовать ClickHouse для ускорения BigQuery и как обрабатывать инкрементные импорты.

Если у вас возникли проблемы с передачей данных из BigQuery в ClickHouse, пожалуйста, не стесняйтесь обращаться к нам по адресу support@clickhouse.com.