Inserindo dados SQL e gerando dumps no ClickHouse

O ClickHouse pode ser integrado facilmente a infraestruturas de bancos de dados OLTP de várias maneiras. Uma delas é transferir dados entre outros bancos de dados e o ClickHouse usando dump SQL.‘

Criando dumps SQL

Os dados podem ser exportados em formato SQL usando SQLInsert. O ClickHouse gravará os dados no formato INSERT INTO <table name> VALUES(... e usará a opção de configuração output_format_sql_insert_table_name como nome da tabela:

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

Os nomes das colunas podem ser omitidos ao desativar a opção output_format_sql_insert_include_column_names:

SET output_format_sql_insert_include_column_names = 0

Agora podemos alimentar outro banco de dados OLTP com o arquivo dump.sql:

mysql some_db < dump.sql

Pressupomos que a tabela some_table exista no banco de dados MySQL some_db. Alguns SGBDs podem ter limites para a quantidade de valores que pode ser processada em um único lote. Por padrão, o ClickHouse criará lotes de 65 mil valores, mas isso pode ser alterado com a opção output_format_sql_insert_max_batch_size:

SET output_format_sql_insert_max_batch_size = 1000;

Exportando um conjunto de valores

O ClickHouse tem o formato Values, que é semelhante ao SQLInsert, mas omite a parte INSERT INTO table VALUES e retorna apenas um 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)

Inserindo dados de dumps SQL

Para ler dumps SQL, usa-se 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 │
└────────────────────────────────┴────────────┴──────┘

Por padrão, o ClickHouse ignora colunas desconhecidas (controlado pela opção input_format_skip_unknown_fields) e processa os dados da primeira tabela encontrada em um dump (caso várias tabelas tenham sido exportadas para um único arquivo). Instruções DDL serão ignoradas. Para carregar dados de um dump do MySQL em uma tabela (arquivo mysql.sql):

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

Também podemos criar uma tabela automaticamente a partir do dump do MySQL:

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

Aqui, criamos uma tabela chamada table_from_mysql com base em uma estrutura que o ClickHouse inferiu automaticamente. O ClickHouse detecta os tipos com base nos dados ou usa DDL quando disponível:

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) │              │                    │         │                  │                │
└───────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Outros formatos

O ClickHouse oferece suporte a muitos formatos, tanto de texto quanto binários, para abranger vários cenários e plataformas. Explore mais formatos e maneiras de trabalhar com eles nos artigos a seguir:

Confira também o clickhouse-local - uma ferramenta portátil e completa para trabalhar com arquivos locais/remotos sem precisar de um servidor ClickHouse.

​Criando dumps SQL

​Exportando um conjunto de valores

​Inserindo dados de dumps SQL

​Outros formatos

Criando dumps SQL

Exportando um conjunto de valores

Inserindo dados de dumps SQL

Outros formatos