メインコンテンツまでスキップ
メインコンテンツまでスキップ

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には、単一のバッチ内で処理できる値の制限があるかもしれません。デフォルトでは、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)

ここでは、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サーバーがなくても、ローカル/リモートファイルで作業するためのポータブルな多機能ツールです。