ClickHouseでCSVおよびTSVデータを扱う

ClickHouseは、CSVのインポートおよびエクスポートをサポートしています。CSVファイルは、ヘッダー行、独自の区切り文字、エスケープ記号など、フォーマットの仕様がさまざまであるため、ClickHouseではそれぞれのケースに効率よく対応できる各種フォーマットと設定を提供しています。

CSVファイルからデータをインポートする

データをインポートする前に、適切な構造のテーブルを作成しましょう。

CREATE TABLE sometable
(
    `path` String,
    `month` Date,
    `hits` UInt32
)
ENGINE = MergeTree
ORDER BY tuple(month, path)

CSVファイルからsometableテーブルにデータをインポートするには、ファイルを直接clickhouse-clientにパイプします。

clickhouse-client -q "INSERT INTO sometable FORMAT CSV" < data_small.csv

ClickHouse に CSV 形式のデータを取り込むことを知らせるために、FORMAT CSV を使用している点に注意してください。別の方法として、FROM INFILE 句を使用して、ローカルファイルからデータを読み込むこともできます。

INSERT INTO sometable
FROM INFILE 'data_small.csv'
FORMAT CSV

ここでは、ClickHouse にファイルのフォーマットを認識させるために、FORMAT CSV 句を使用します。また、url() 関数を使って URL から直接データを読み込んだり、s3() 関数を使って S3 ファイルから読み込んだりすることもできます。

file() および INFILE/OUTFILE では、明示的にフォーマットを指定しなくてもかまいません。その場合、ClickHouse はファイル拡張子に基づいてフォーマットを自動的に検出します。

ヘッダー付きCSVファイル

このCSVファイルにはヘッダーが含まれています:

head data-small-headers.csv

"path","month","hits"
"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34

このファイルからデータをインポートするには、CSVWithNamesフォーマットを使用します。

clickhouse-client -q "INSERT INTO sometable FORMAT CSVWithNames" < data_small_headers.csv

この場合、ClickHouse はファイルからデータをインポートする際に、最初の行をスキップします。

version 23.1 以降、CSV フォーマットを使用すると、ClickHouse は CSV ファイルのヘッダーを自動的に検出するため、CSVWithNames や CSVWithNamesAndTypes を使用する必要はありません。

カスタム区切り文字を使用したCSVファイル

CSVファイルでコンマ以外の区切り文字を使用している場合は、format_csv_delimiter オプションを使って、該当する記号を設定できます。

SET format_csv_delimiter = ';'

これで、CSVファイルからインポートする際は、区切り文字としてカンマではなく ; が使用されます。

CSVファイル内の行をスキップする

CSVファイルからデータをインポートする際、先頭の数行をスキップしたい場合があります。これは、input_format_csv_skip_first_lines オプションを使用して行えます。

SET input_format_csv_skip_first_lines = 10

この場合、CSVファイルの先頭10行をスキップします。

SELECT count(*) FROM file('data-small.csv', CSV)

┌─count()─┐
│     990 │
└─────────┘

このfileには 1k 行ありますが、最初の 10 行をスキップするよう指定したため、ClickHouse が読み込んだのは 990 行のみです。

file() 関数を使用する場合、ClickHouse Cloud では、ファイルが存在するマシン上で clickhouse client を使ってコマンドを実行する必要があります。別の方法として、clickhouse-local を使用してローカルでファイルを確認することもできます。

CSV ファイルでの NULL 値の扱い

NULL 値の表現方法は、そのファイルを生成したアプリケーションによって異なる場合があります。デフォルトでは、ClickHouse は CSV で NULL 値として \N を使用します。ただし、これは format_csv_null_representation オプションで変更できます。次のような CSV ファイルがあるとします。

> cat nulls.csv
Donald,90
Joe,Nothing
Nothing,70

このファイルからデータを読み込むと、ClickHouse は Nothing を String として扱います (これは正しい挙動です) :

SELECT * FROM file('nulls.csv')

┌─c1──────┬─c2──────┐
│ Donald  │ 90      │
│ Joe     │ Nothing │
│ Nothing │ 70      │
└─────────┴─────────┘

ClickHouse で Nothing を NULL として扱う場合は、次のオプションで指定できます。

SET format_csv_null_representation = 'Nothing'

これで、期待どおりの箇所に NULL が入っていることが確認できます：

SELECT * FROM file('nulls.csv')

┌─c1─────┬─c2───┐
│ Donald │ 90   │
│ Joe    │ ᴺᵁᴸᴸ │
│ ᴺᵁᴸᴸ   │ 70   │
└────────┴──────┘

TSV (タブ区切り) ファイル

タブ区切りのデータフォーマットは、データ交換用フォーマットとして広く使われています。TSV file から ClickHouse にデータを読み込むには、TabSeparated フォーマットを使用します。

clickhouse-client -q "INSERT INTO sometable FORMAT TabSeparated" < data_small.tsv

ヘッダー付きの TSV ファイルを扱うための TabSeparatedWithNames フォーマットもあります。また、CSV と同様に、input_format_tsv_skip_first_lines オプションを使って先頭の X 行をスキップできます。

Raw TSV

TSVファイルは、タブや改行がエスケープされないまま保存されていることがあります。そのようなファイルを扱うには、TabSeparatedRaw を使用します。

CSV へのエクスポート

前の例で使用したフォーマットは、いずれもデータのエクスポートにも使用できます。テーブル (またはクエリ) からデータを CSV フォーマットでエクスポートするには、同じ FORMAT 句を使用します。

SELECT *
FROM sometable
LIMIT 5
FORMAT CSV

"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34
"1971-72_Utah_Stars_season","2016-10-01",1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8","2015-12-01",73
"2016_Greater_Western_Sydney_Giants_season","2017-05-01",86

CSVファイルにヘッダーを追加するには、CSVWithNamesフォーマットを使用します。

SELECT *
FROM sometable
LIMIT 5
FORMAT CSVWithNames

"path","month","hits"
"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34
"1971-72_Utah_Stars_season","2016-10-01",1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8","2015-12-01",73
"2016_Greater_Western_Sydney_Giants_season","2017-05-01",86

エクスポートしたデータをCSVファイルに保存する

エクスポートしたデータをファイルに保存するには、INTO…OUTFILE 句を使用できます。

SELECT *
FROM sometable
INTO OUTFILE 'out.csv'
FORMAT CSVWithNames

36838935 rows in set. Elapsed: 1.304 sec. Processed 36.84 million rows, 1.42 GB (28.24 million rows/s., 1.09 GB/s.)

ClickHouse が 36m 行を CSVファイルに保存するのに約1 秒しかかからなかったことに注目してください。

カスタム区切り文字を使用したCSVのエクスポート

コンマ以外の区切り文字を使用する場合は、format_csv_delimiter 設定オプションを使用できます:

SET format_csv_delimiter = '|'

これで、ClickHouse は CSV フォーマットの区切り文字として | を使用するようになります:

SELECT *
FROM sometable
LIMIT 5
FORMAT CSV

"Akiba_Hebrew_Academy"|"2017-08-01"|241
"Aegithina_tiphia"|"2018-02-01"|34
"1971-72_Utah_Stars_season"|"2016-10-01"|1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8"|"2015-12-01"|73
"2016_Greater_Western_Sydney_Giants_season"|"2017-05-01"|86

Windows向けのCSVのエクスポート

CSVファイルをWindows環境で問題なく使えるようにするには、output_format_csv_crlf_end_of_line オプションを有効にすることを検討してください。これにより、改行コードとして \n ではなく \r\n が使用されます。

SET output_format_csv_crlf_end_of_line = 1;

CSVファイルのスキーマ推論

未知のCSVファイルを扱うことはよくあるため、カラムにどの型を使うべきかを確認する必要があります。ClickHouse はデフォルトで、指定されたCSVファイルを解析し、データのフォーマットを推測しようとします。これは “スキーマ推論” と呼ばれます。検出されたデータ型は、file() 関数と組み合わせて DESCRIBE ステートメントを使用することで確認できます。

DESCRIBE file('data-small.csv', CSV)

┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(String) │              │                    │         │                  │                │
│ c2   │ Nullable(Date)   │              │                    │         │                  │                │
│ c3   │ Nullable(Int64)  │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

ここでは、ClickHouse が CSVファイルのカラム型を効率よく推測できます。ClickHouse に推測させたくない場合は、次のオプションで無効にできます。

SET input_format_csv_use_best_effort_in_schema_inference = 0

この場合、すべてのカラム型は String 型として扱われます。

明示的にカラム型を指定した CSV のエクスポートとインポート

ClickHouse では、CSVWithNamesAndTypes (およびその他の *WithNames フォーマットファミリー) を使用してデータをエクスポートする際に、カラム型を明示的に指定することもできます。

SELECT *
FROM sometable
LIMIT 5
FORMAT CSVWithNamesAndTypes

"path","month","hits"
"String","Date","UInt32"
"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34
"1971-72_Utah_Stars_season","2016-10-01",1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8","2015-12-01",73
"2016_Greater_Western_Sydney_Giants_season","2017-05-01",86

このフォーマットには、2 つのヘッダー行が含まれます。1 つはカラム名、もう 1 つはカラム型です。これにより、ClickHouse (および他のアプリケーション) は、このようなファイルからデータを読み込む際にカラム型を識別できます。

DESCRIBE file('data_csv_types.csv', CSVWithNamesAndTypes)

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

これにより ClickHouse は、推測する代わりに (2 行目の) ヘッダー行に基づいてカラム型を判別するようになりました。

カスタムの区切り文字、セパレーター、エスケープルール

複雑なケースでは、テキストデータが高度にカスタマイズされた形式であっても、一定の構造を持っていることがあります。ClickHouse には、そのようなケース向けに特別な CustomSeparated フォーマットがあり、カスタムのエスケープルール、区切り文字、行セパレーター、開始/終了記号を設定できます。たとえば、ファイルに次のようなデータがあるとします。

row('Akiba_Hebrew_Academy';'2017-08-01';241),row('Aegithina_tiphia';'2018-02-01';34),...

個々の行が row() で囲まれ、行は , で区切られ、各値は ; で区切られていることがわかります。この場合、このファイルからデータを読み込むには次の設定を使用できます。

SET format_custom_row_before_delimiter = 'row(';
SET format_custom_row_after_delimiter = ')';
SET format_custom_field_delimiter = ';';
SET format_custom_row_between_delimiter = ',';
SET format_custom_escaping_rule = 'Quoted';

これで、独自フォーマットのファイルからデータを読み込めます。

SELECT *
FROM file('data_small_custom.txt', CustomSeparated)
LIMIT 3

┌─c1────────────────────────┬─────────c2─┬──c3─┐
│ Akiba_Hebrew_Academy      │ 2017-08-01 │ 241 │
│ Aegithina_tiphia          │ 2018-02-01 │  34 │
│ 1971-72_Utah_Stars_season │ 2016-10-01 │   1 │
└───────────────────────────┴────────────┴─────┘

CustomSeparatedWithNames を使用すると、ヘッダーを正しくエクスポートおよびインポートできます。さらに複雑なケースに対処するには、regex and template フォーマットも参照してください。

大容量のCSVファイルの操作

CSVファイルは大きくなることがありますが、ClickHouse はどのようなサイズのファイルでも効率よく処理できます。大容量ファイルは通常圧縮された状態で提供されますが、ClickHouse では処理前に展開する必要はありません。insert時に COMPRESSION 句を使用できます。

INSERT INTO sometable
FROM INFILE 'data_csv.csv.gz'
COMPRESSION 'gzip' FORMAT CSV

COMPRESSION 句を省略しても、ClickHouse はファイルの拡張子に基づいて圧縮形式を推定しようとします。同じ方法で、ファイルを圧縮フォーマットに直接エクスポートすることもできます。

SELECT *
FROM for_csv
INTO OUTFILE 'data_csv.csv.gz'
COMPRESSION 'gzip' FORMAT CSV

これにより、圧縮済みのdata_csv.csv.gzファイルが作成されます。

その他のフォーマット

ClickHouse は、さまざまなシナリオやプラットフォームに対応するため、テキスト形式とバイナリ形式の両方を含む多数のフォーマットをサポートしています。さらに多くのフォーマットとその活用方法については、以下の記事を参照してください。

あわせて、clickhouse-local もご確認ください。これは、ClickHouse server を必要とせずにローカル/リモートファイルを扱える、移植性の高い高機能ツールです。

ClickHouseガイド

ユースケースガイド

ClickHouseでCSVおよびTSVデータを扱う

CSVファイルからデータをインポートする

ヘッダー付きCSVファイル

カスタム区切り文字を使用したCSVファイル

CSVファイル内の行をスキップする

CSV ファイルでの NULL 値の扱い

TSV (タブ区切り) ファイル

Raw TSV

CSV へのエクスポート

エクスポートしたデータをCSVファイルに保存する

カスタム区切り文字を使用したCSVのエクスポート

Windows向けのCSVのエクスポート

CSVファイルのスキーマ推論

明示的にカラム型を指定した CSV のエクスポートとインポート

カスタムの区切り文字、セパレーター、エスケープルール

大容量のCSVファイルの操作

その他のフォーマット

​CSVファイルからデータをインポートする

​ヘッダー付きCSVファイル

​カスタム区切り文字を使用したCSVファイル

​CSVファイル内の行をスキップする

​CSV ファイルでの NULL 値の扱い

​TSV (タブ区切り) ファイル

​Raw TSV

​CSV へのエクスポート

​エクスポートしたデータをCSVファイルに保存する

​カスタム区切り文字を使用したCSVのエクスポート

​Windows向けのCSVのエクスポート

​CSVファイルのスキーマ推論

​明示的にカラム型を指定した CSV のエクスポートとインポート

​カスタムの区切り文字、セパレーター、エスケープルール

​大容量のCSVファイルの操作

​その他のフォーマット

CSVファイルからデータをインポートする

ヘッダー付きCSVファイル

カスタム区切り文字を使用したCSVファイル

CSVファイル内の行をスキップする

CSV ファイルでの NULL 値の扱い

TSV (タブ区切り) ファイル

Raw TSV

CSV へのエクスポート

エクスポートしたデータをCSVファイルに保存する

カスタム区切り文字を使用したCSVのエクスポート

Windows向けのCSVのエクスポート

CSVファイルのスキーマ推論

明示的にカラム型を指定した CSV のエクスポートとインポート

カスタムの区切り文字、セパレーター、エスケープルール

大容量のCSVファイルの操作

その他のフォーマット