Вставка и выгрузка SQL данных в ClickHouse

ClickHouse можно легко интегрировать в инфраструктуру баз данных OLTP различными способами. Один из способов — это передача данных между другими базами данных и ClickHouse с использованием SQL дампов.

Создание SQL дампов

Данные можно выгрузить в SQL формате с использованием SQLInsert. ClickHouse будет записывать данные в форме INSERT INTO <имя таблицы> VALUES(... и использовать опцию настройки output_format_sql_insert_table_name в качестве имени таблицы:

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

Имена колонок можно пропустить, отключив опцию output_format_sql_insert_include_column_names:

SET output_format_sql_insert_include_column_names = 0

Теперь мы можем передать файл dump.sql в другую OLTP базу данных:

mysql some_db < dump.sql

Предполагаем, что таблица some_table существует в базе данных MySQL some_db.

Некоторые СУБД могут иметь ограничения на количество значений, которые могут быть обработаны в одном пакете. По умолчанию ClickHouse создаёт пакеты из 65k значений, но это можно изменить с помощью опции output_format_sql_insert_max_batch_size:

SET output_format_sql_insert_max_batch_size = 1000;

Экспорт набора значений

ClickHouse имеет формат Values, который похож на SQLInsert, но опускает часть INSERT INTO table VALUES и возвращает только набор значений:

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)

Вставка данных из SQL дампов

Для чтения SQL дампов используется 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 │
└────────────────────────────────┴────────────┴──────┘

По умолчанию ClickHouse будет пропускать неизвестные колонки (под контролем опции input_format_skip_unknown_fields) и обрабатывать данные для первой найденной таблицы в дампе (если в один файл было выгружено несколько таблиц). DDL операторы будут пропущены. Чтобы загрузить данные из MySQL дампа в таблицу (файл mysql.sql):

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

Мы также можем автоматически создать таблицу из файла MySQL дампа:

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

Здесь мы создали таблицу с именем table_from_mysql на основе структуры, которую ClickHouse автоматически вывел. ClickHouse либо обнаруживает типы на основе данных, либо использует DDL, если он доступен:

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 предлагает поддержку многих форматов, как текстовых, так и бинарных, для охвата различных сценариев и платформ. Изучите больше форматов и способы работы с ними в следующих статьях:

И также проверьте clickhouse-local - портативный многофункциональный инструмент для работы с локальными/удалёнными файлами без необходимости в сервере ClickHouse.

Создание SQL дампов​

Экспорт набора значений​

Вставка данных из SQL дампов​

Другие форматы​

Создание SQL дампов

Экспорт набора значений

Вставка данных из SQL дампов

Другие форматы