メインコンテンツへスキップ
メインコンテンツへスキップ

ClickHouse での SQL データの挿入とダンプ

ClickHouse は、さまざまな方法で OLTP データベース基盤に容易に統合できます。その 1 つの方法として、SQL ダンプを使用して他のデータベースと ClickHouse 間でデータを転送することが挙げられます。

SQL ダンプの作成​

SQLInsert を使用すると、データを SQL 形式でダンプできます。ClickHouse はデータを INSERT INTO <table name> 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_db MySQL データベース内に some_table テーブルが存在していることを前提とします。

一部の DBMS には、1 回のバッチで処理できる値の数に制限がある場合があります。デフォルトでは、ClickHouse は 1 バッチあたり 65k 個の値を含むバッチを作成しますが、これは output_format_sql_insert_max_batch_size オプションで変更できます。

SET output_format_sql_insert_max_batch_size = 1000;

値の集合のエクスポート​

ClickHouse には Values フォーマットがあり、SQL の INSERT 文に似ていますが、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 オプションで制御）、ダンプ内で最初に見つかったテーブルのデータのみを処理します（複数のテーブルが 1 つのファイルにダンプされている場合）。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)

ここでは、ClickHouse が自動的に推論したスキーマに基づいて、table_from_mysql という名前のテーブルを作成しました。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 サーバーを起動することなく、ローカル／リモートファイルを扱うことができる移植性の高いフル機能ツールです。