Carga de datos desde BigQuery a ClickHouse

Cómo cargar datos de BigQuery en ClickHouse

Esta guía es compatible con ClickHouse Cloud y con ClickHouse autogestionado v23.5+.

Esta guía muestra cómo migrar datos de BigQuery a ClickHouse.

Primero exportamos una tabla al almacenamiento de objetos de Google (GCS) y luego importamos esos datos en ClickHouse Cloud. Estos pasos deben repetirse para cada tabla que desee exportar de BigQuery a ClickHouse.

​

¿Cuánto tarda exportar datos a ClickHouse?

El tiempo necesario para exportar datos de BigQuery a ClickHouse depende del tamaño del conjunto de datos. Como referencia, exportar el conjunto de datos público de Ethereum de 4 TB de BigQuery a ClickHouse con esta guía lleva aproximadamente una hora.

Tabla	Filas	Archivos exportados	Tamaño de los datos	Exportación desde BigQuery	Tiempo de slot	Importación a 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 día 8 h	18 min 5 s
traces	6,325,819,306	17,985	2.896TB	5 min 46 s	5 días 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 mil millones	23,577	3.982TB	8 min 3 s	> 6 días 5 h	53 min 45 s

1. ​

   Exportar datos de una tabla a GCS

   En este paso, utilizamos el workspace de SQL de BigQuery para ejecutar nuestros comandos SQL. A continuación, exportamos una tabla de BigQuery llamada mytable a un bucket de GCS mediante la sentencia EXPORT DATA.

   DECLARE export_path STRING;
   DECLARE n INT64;
   DECLARE i INT64;
   SET i = 0;
   
   -- Recomendamos establecer n para que corresponda a x mil millones de filas. Por ejemplo, para 5 mil millones de filas, 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;
   

   En la consulta anterior, exportamos nuestra tabla de BigQuery al formato de datos Parquet. También tenemos un carácter * en nuestro parámetro uri. Esto garantiza que la salida se divida en varios archivos, con un sufijo numérico ascendente, si la exportación supera 1 GB de datos.

   Este enfoque tiene varias ventajas:

   • Google permite exportar hasta 50 TB al día a GCS de forma gratuita. Los usuarios solo pagan por el almacenamiento en GCS.
   • Las exportaciones generan varios archivos automáticamente y limitan cada uno a un máximo de 1 GB de datos de la tabla. Esto es beneficioso para ClickHouse, ya que permite paralelizar las importaciones.
   • Parquet, como formato orientado a columnas, es un mejor formato de intercambio, ya que está comprimido de forma inherente y BigQuery lo exporta más rápido, además de que ClickHouse lo consulta con mayor rapidez
2. ​

   Importación de datos en ClickHouse desde GCS

   Una vez completada la exportación, podemos importar estos datos a una tabla de ClickHouse. Puede usar la consola SQL de ClickHouse o clickhouse-client para ejecutar los siguientes comandos.

   Primero debe crear la tabla en ClickHouse:

   -- Si tu tabla de BigQuery contiene una columna de tipo STRUCT, debes habilitar esta configuración
   -- para asignar esa columna a una columna de ClickHouse de tipo Nested
   SET input_format_parquet_import_nested = 1;
   
   CREATE TABLE default.mytable
   (
           `timestamp` DateTime64(6),
           `some_text` String
   )
   ENGINE = MergeTree
   ORDER BY (timestamp);
   

   Después de crear la tabla, habilita el ajuste parallel_distributed_insert_select si tienes varias réplicas de ClickHouse en tu clúster para acelerar la exportación. Si solo tienes un nodo de ClickHouse, puedes omitir este paso:

   SET parallel_distributed_insert_select = 1;
   

   Por último, podemos insertar los datos de GCS en nuestra tabla de ClickHouse usando el comando INSERT INTO SELECT, que inserta datos en una tabla a partir de los resultados de una consulta SELECT.

   Para obtener los datos para INSERT, podemos usar la función s3Cluster para recuperar datos de nuestro bucket de GCS, ya que GCS es interoperable con Amazon S3. Si solo tiene un nodo de ClickHouse, puede usar la función de tabla s3 en lugar de la función 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>'
   );
   

   El ACCESS_ID y el SECRET usados en la consulta anterior corresponden a su clave HMAC asociada con su bucket de GCS.

   Use ifNull al exportar columnas anulables

   En la consulta anterior, usamos la función ifNull con la columna some_text para insertar datos en nuestra tabla de ClickHouse asignando un valor predeterminado. También puede hacer que sus columnas en ClickHouse sean Nullable, pero no se recomienda, ya que puede afectar negativamente al rendimiento.

   Como alternativa, puede SET input_format_null_as_default=1, y cualquier valor ausente o NULL se reemplazará por el valor predeterminado de su columna correspondiente, si se ha definido.

3. ​

   Comprobar que la exportación de datos se realizó correctamente

   Para comprobar si tus datos se insertaron correctamente, simplemente ejecuta una consulta SELECT en tu nueva tabla:

   SELECT * FROM mytable LIMIT 10;
   

   Para exportar más tablas de BigQuery, simplemente vuelve a seguir los pasos anteriores para cada tabla adicional.

​

Lecturas complementarias y soporte

Además de esta guía, también te recomendamos leer nuestra entrada del blog, donde mostramos cómo usar ClickHouse para acelerar BigQuery y gestionar importaciones incrementales.

Si tienes problemas para transferir datos de BigQuery a ClickHouse, no dudes en ponerte en contacto con nosotros en [e-mail].