Inserción y exportación de datos SQL en ClickHouse

ClickHouse se puede integrar fácilmente en infraestructuras de bases de datos OLTP de muchas formas. Una de ellas es transferir datos entre otras bases de datos y ClickHouse mediante volcados SQL.

Los datos pueden exportarse en formato SQL mediante SQLInsert. ClickHouse escribirá los datos en la forma INSERT INTO <table name> VALUES(... y usará la opción de configuración output_format_sql_insert_table_name como nombre de la tabla:

SET output_format_sql_insert_table_name = 'some_table';
SELECT * FROM some_data
INTO OUTFILE 'dump.sql'
FORMAT SQLInsert

Se pueden omitir los nombres de las columnas desactivando la opción output_format_sql_insert_include_column_names:

SET output_format_sql_insert_include_column_names = 0

Ahora podemos cargar el archivo dump.sql en otra base de datos OLTP:

mysql some_db < dump.sql

Suponemos que la tabla some_table existe en la base de datos MySQL some_db.

Algunos DBMS pueden tener límites sobre cuántos valores se pueden procesar en un solo lote. De forma predeterminada, ClickHouse creará lotes de 65k valores, pero eso se puede cambiar con la opción output_format_sql_insert_max_batch_size:

SET output_format_sql_insert_max_batch_size = 1000;

ClickHouse tiene el formato Values, que es similar a SQLInsert, pero omite la parte INSERT INTO table VALUES y devuelve únicamente un conjunto de valores:

SELECT * FROM some_data LIMIT 3 FORMAT Values

('Bangor_City_Forest','2015-07-01',34),('Alireza_Afzal','2017-02-01',24),('Akhaura-Laksam-Chittagong_Line','2015-09-01',30)

Para leer volcados SQL, se utiliza MySQLDump:

SELECT *
FROM file('dump.sql', MySQLDump)
LIMIT 5

┌─path───────────────────────────┬──────month─┬─hits─┐
│ Bangor_City_Forest             │ 2015-07-01 │   34 │
│ Alireza_Afzal                  │ 2017-02-01 │   24 │
│ Akhaura-Laksam-Chittagong_Line │ 2015-09-01 │   30 │
│ 1973_National_500              │ 2017-10-01 │   80 │
│ Attachment                     │ 2017-09-01 │ 1356 │
└────────────────────────────────┴────────────┴──────┘

De forma predeterminada, ClickHouse omitirá las columnas desconocidas (según la opción input_format_skip_unknown_fields) y procesará los datos de la primera tabla que encuentre en un volcado (en caso de que se hayan volcado varias tablas en un solo archivo). Se omitirán las sentencias DDL. Para cargar datos de un volcado de MySQL en una tabla (archivo mysql.sql):

INSERT INTO some_data
FROM INFILE 'mysql.sql' FORMAT MySQLDump

También podemos crear una tabla automáticamente a partir del archivo de volcado de MySQL:

CREATE TABLE table_from_mysql
ENGINE = MergeTree
ORDER BY tuple() AS
SELECT *
FROM file('mysql.sql', MySQLDump)

Aquí hemos creado una tabla llamada table_from_mysql a partir de una estructura que ClickHouse ha inferido automáticamente. ClickHouse detecta los tipos a partir de los datos o utiliza DDL cuando está disponible:

DESCRIBE TABLE table_from_mysql;

┌─name──┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ path  │ Nullable(String) │              │                    │         │                  │                │
│ month │ Nullable(Date32) │              │                    │         │                  │                │
│ hits  │ Nullable(UInt32) │              │                    │         │                  │                │
└───────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

ClickHouse ofrece compatibilidad con numerosos formatos, tanto de texto como binarios, para adaptarse a distintos escenarios y plataformas. Descubre más formatos y formas de trabajar con ellos en los siguientes artículos:

• Formatos CSV y TSV
• Parquet
• Formatos JSON
• Regex y plantillas
• Formatos nativos y binarios
• Formatos SQL

Consulta también clickhouse-local, una herramienta portátil y completa para trabajar con archivos locales o remotos sin necesidad de un servidor ClickHouse.