ClickHouseにおけるCSVおよびTSVデータの操作

ClickHouseはCSVからのデータのインポートと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はファイルからデータをインポートする際に最初の行をスキップします。

ヒント

23.1 バージョン以降、ClickHouseはCSVタイプが使用されているときに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 │
└─────────┘

ファイルには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を文字列として扱います（これは正しいです）：

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ファイルからClickHouseにデータをロードするには、TabSeparatedフォーマットが使用されます：

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

ヘッダのあるTSVファイルを操作するためのTabSeparatedWithNamesフォーマットもあります。また、CSVと同様に、input_format_tsv_skip_first_linesオプションを使用して最初のX行をスキップすることができます。

生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エクスポート

Windows環境でCSVファイルを正しく動作させるには、output_format_csv_crlf_end_of_lineオプションを有効にする必要があります。これにより、行の改行として\nの代わりに\r\nが使用されます：

SET output_format_csv_crlf_end_of_line = 1;

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

不明なCSVファイルを扱う場合が多いため、カラムに使用するタイプを調べる必要があります。ClickHouseはデフォルトで、与えられたCSVファイルの分析に基づいてデータフォーマットを推測しようとします。これを「スキーマ推測」と呼びます。検出されたデータ型は、DESCRIBEステートメントを[file()](/sql-reference/table-functions/file.md)関数と組み合わせて調べることができます：

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つのヘッダ行が含まれます。一つはカラム名で、もう一つはカラムタイプです。これにより、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およびテンプレートフォーマットを探索してください。

大きなCSVファイルの操作

CSVファイルは大きくなることがあり、ClickHouseは任意のサイズのファイルで効率的に動作します。大きなファイルは通常圧縮されて提供され、ClickHouseは処理前に圧縮を解く必要がありません。挿入時に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は、さまざまなシナリオやプラットフォームをカバーするために多くのフォーマット（テキストとバイナリの両方）をサポートしています。以下の記事でさらに多くのフォーマットやそれらとの作業方法を探ってみてください：

• CSVおよびTSVフォーマット
• Parquet
• JSONフォーマット
• Regexおよびテンプレート
• ネイティブおよびバイナリフォーマット
• SQLフォーマット

また、clickhouse-localを確認してください。Clickhouseサーバーを必要とせずに、ローカル/リモートファイルで作業するためのポータブルでフル機能のツールです。