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

На этом шаге мы используем рабочее пространство 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 — запросы

После завершения экспорта эти данные можно импортировать в таблицу 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.

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

SELECT * FROM mytable LIMIT 10;

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