Carga de dados do BigQuery para o ClickHouse

Como carregar dados do BigQuery para o ClickHouse

Este guia é compatível com o ClickHouse Cloud e com o ClickHouse self-hosted v23.5+.

Este guia mostra como migrar dados do BigQuery para o ClickHouse.

Primeiro, exportamos uma tabela para o armazenamento de objetos do Google (GCS) e, em seguida, importamos esses dados para o ClickHouse Cloud. Essas etapas precisam ser repetidas para cada tabela que você quiser exportar do BigQuery para o ClickHouse.

​

Quanto tempo leva para exportar dados para o ClickHouse?

O tempo de exportação de dados do BigQuery para o ClickHouse depende do tamanho do seu conjunto de dados. Como referência, leva cerca de uma hora para exportar o conjunto de dados público do Ethereum de 4 TB do BigQuery para o ClickHouse usando este guia.

Tabela	Linhas	Arquivos exportados	Tamanho dos dados	Exportação do BigQuery	Tempo de slot	Importação para o ClickHouse
blocks	16,569,489	73	14.53GB	23 s	37 min	15.4 s
transactions	1,864,514,414	5169	957GB	1 min 38 s	1 dia 8 h	18 min 5 s
traces	6,325,819,306	17,985	2.896TB	5 min 46 s	5 dias 19 h	34 min 55 s
contracts	57,225,837	350	45.35GB	16 s	1 h 51 min	39.4 s
Total	8.26 bilhões	23,577	3.982TB	8 min 3 s	> 6 dias 5 h	53 min 45 s

1. ​

   Exportar dados da tabela para o GCS

   Nesta etapa, usamos o espaço de trabalho SQL do BigQuery para executar nossos comandos SQL. Abaixo, exportamos uma tabela do BigQuery chamada mytable para um bucket do GCS usando a instrução EXPORT DATA.

   DECLARE export_path STRING;
   DECLARE n INT64;
   DECLARE i INT64;
   SET i = 0;
   
   -- Recomendamos definir n para corresponder a x bilhões de linhas. Por exemplo, para 5 bilhões de linhas, 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;
   

   Na consulta acima, exportamos nossa tabela do BigQuery para o formato de dados Parquet. Também temos um caractere * no parâmetro uri. Isso garante que a saída seja dividida em vários arquivos, com um sufixo numérico crescente, caso a exportação exceda 1 GB de dados.

   Essa abordagem tem várias vantagens:

   • O Google permite exportar até 50 TB por dia para o GCS sem custo. Os usuários pagam apenas pelo armazenamento no GCS.
   • As exportações geram vários arquivos automaticamente, limitando cada um a no máximo 1 GB de dados da tabela. Isso é vantajoso para o ClickHouse, pois permite paralelizar as importações.
   • O Parquet, por ser um formato orientado a colunas, é uma opção melhor para intercâmbio de dados, já que é compactado por natureza e mais rápido para o BigQuery exportar e para o ClickHouse consultar
2. ​

   Importando dados do GCS para o ClickHouse

   Quando a exportação for concluída, podemos importar esses dados para uma tabela no ClickHouse. Você pode usar o Console SQL do ClickHouse ou o clickhouse-client para executar os comandos abaixo.

   Primeiro, você deve criar sua tabela no ClickHouse:

   -- Se sua tabela do BigQuery contiver uma coluna do tipo STRUCT, você deve habilitar esta configuração
   -- para mapear essa coluna para uma coluna do ClickHouse do tipo Nested
   SET input_format_parquet_import_nested = 1;
   
   CREATE TABLE default.mytable
   (
           `timestamp` DateTime64(6),
           `some_text` String
   )
   ENGINE = MergeTree
   ORDER BY (timestamp);
   

   Após criar a tabela, habilite a configuração parallel_distributed_insert_select se houver várias réplicas do ClickHouse no cluster, para acelerar o processo de exportação. Se houver apenas um nó do ClickHouse, você pode pular esta etapa:

   SET parallel_distributed_insert_select = 1;
   

   Por fim, podemos inserir os dados do GCS em nossa tabela no ClickHouse usando o comando INSERT INTO SELECT, que insere dados em uma tabela com base nos resultados de uma consulta SELECT.

   Para obter os dados para o INSERT, podemos usar a função s3Cluster para buscar dados do nosso bucket do GCS, já que o GCS é interoperável com o Amazon S3. Se você tiver apenas um nó do ClickHouse, poderá usar a função de tabela s3 em vez da função 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>'
   );
   

   O ACCESS_ID e o SECRET usados na consulta acima correspondem à sua chave HMAC associada ao seu GCS bucket.

   Use ifNull ao exportar colunas anuláveis

   Na consulta acima, usamos a função ifNull com a coluna some_text para inserir dados na nossa tabela no ClickHouse com um valor padrão. Você também pode definir suas colunas no ClickHouse como Nullable, mas isso não é recomendado, pois pode afetar negativamente o desempenho.

   Como alternativa, você pode usar SET input_format_null_as_default=1, e todos os valores ausentes ou NULL serão substituídos pelos valores padrão de suas respectivas colunas, se esses padrões estiverem especificados.

3. ​

   Testando se a exportação de dados foi bem-sucedida

   Para testar se seus dados foram inseridos corretamente, basta executar uma consulta SELECT na sua nova tabela:

   SELECT * FROM mytable LIMIT 10;
   

   Para exportar mais tabelas do BigQuery, basta repetir as etapas acima para cada tabela adicional.

​

Leitura adicional e suporte

Além deste guia, também recomendamos a leitura do nosso post no blog, que mostra como usar o ClickHouse para acelerar o BigQuery e lidar com importações incrementais.

Se você estiver com problemas para transferir dados do BigQuery para o ClickHouse, entre em contato conosco pelo e-mail [e-mail].