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

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

FORMAT CSVを使用していることに注意してください。これによりClickHouseは私たちがCSV形式のデータを取り込んでいることを認識します。あるいは、FROM INFILE句を使用してローカルファイルからデータを読み込むこともできます。

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

ここでは、FORMAT CSV句を使用してClickHouseにファイル形式を理解させています。また、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を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ファイルから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.)

36m行をCSVファイルに保存するのにClickHouseは約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ファイルの分析に基づいてデータ形式を推測しようとします。これは「スキーマ推論」として知られています。検出されたデータ型は、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行のヘッダーが含まれます。これにより、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は、さまざまなシナリオやプラットフォームをカバーするために、多くの形式（テキスト形式およびバイナリ形式）をサポートしています。次の記事で、より多くの形式やそれらとの作業方法を探索してください：

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