Загрузка данных из 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 позволяет бесплатно экспортировать в GCS до 50 ТБ в день. Пользователи платят только за хранение в GCS.
При экспорте автоматически создаётся несколько файлов, при этом размер каждого не превышает 1 ГБ данных таблицы. Это полезно для ClickHouse, поскольку позволяет распараллелить импорт.
Parquet как столбцово-ориентированный формат лучше подходит для обмена данными, поскольку он изначально сжат и позволяет BigQuery быстрее выполнять экспорт, а ClickHouse — запросы

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

После завершения экспорта эти данные можно импортировать в таблицу ClickHouse. Для выполнения приведённых ниже команд можно использовать консоль ClickHouse SQL или 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, вместо функции 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 будут заменяться значениями по умолчанию для соответствующих столбцов, если такие значения заданы.

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

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

SELECT * FROM mytable LIMIT 10;

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

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

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

​Сколько времени занимает экспорт данных в ClickHouse?

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

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

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

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

Сколько времени занимает экспорт данных в ClickHouse?

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

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

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

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