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

Как загрузить данные из 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 сек

1. ​

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

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

   DECLARE export_path STRING;
   DECLARE n INT64;
   DECLARE i INT64;
   SET i = 0;
   
   -- Рекомендуем устанавливать n равным количеству миллиардов строк. Например, для 5 миллиардов строк: 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 позволяет бесплатно экспортировать в GCS до 50 ТБ в день. Пользователи платят только за хранение в GCS.
   • При экспорте автоматически создаётся несколько файлов, при этом размер каждого не превышает 1 ГБ данных таблицы. Это полезно для ClickHouse, поскольку позволяет распараллелить импорт.
   • Parquet как столбцово-ориентированный формат лучше подходит для обмена данными, поскольку он изначально сжат и позволяет BigQuery быстрее выполнять экспорт, а ClickHouse — запросы
2. ​

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

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

   Сначала необходимо создать таблицу в ClickHouse:

   -- Если таблица BigQuery содержит столбец типа STRUCT, необходимо включить этот параметр,
   -- чтобы сопоставить его со столбцом ClickHouse типа 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, вместо функции s3Cluster можно использовать табличную функцию s3.

   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 будут заменяться значениями по умолчанию для соответствующих столбцов, если такие значения заданы.

3. ​

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

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

   SELECT * FROM mytable LIMIT 10;
   

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

​

Дополнительные материалы и поддержка

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

Если у вас возникли проблемы с переносом данных из BigQuery в ClickHouse, свяжитесь с нами по адресу [e-mail].