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

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

ClickHouseはCSVからのデータのインポートとCSVへのデータのエクスポートをサポートしています。CSVファイルはヘッダ行、カスタム区切り文字、エスケープ記号など、異なるフォーマットの仕様で提供されることがあるため、ClickHouseでは各ケースに効率的に対処するためのフォーマットと設定が用意されています。

CSVファイルからのデータのインポート​

データをインポートする前に、関連する構造のテーブルを作成しましょう：

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

ここでは、ClickHouseにCSV形式のデータを取り込んでいることを通知するためにFORMAT CSVを使用しています。あるいは、FROM INFILE句を使ってローカルファイルからデータをロードすることもできます：

ここでは、ClickHouseがファイル形式を理解できるようにFORMAT CSV句を使用しています。また、url()関数を使用してURLから直接データをロードしたり、s3()関数を使用してS3ファイルからデータをロードすることも可能です。

ヒント

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

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

私たちのCSVファイルにヘッダがあると仮定しましょう：

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

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

ヒント

23.1 バージョン以降、ClickHouseはCSVタイプが使用されているときにCSVファイルのヘッダを自動的に検出するため、CSVWithNamesやCSVWithNamesAndTypesを使用する必要はありません。

カスタム区切り文字のあるCSVファイル​

CSVファイルがカンマ以外の区切り文字を使用している場合、format_csv_delimiterオプションを使用して関連する記号を設定することができます：

これで、CSVファイルからインポートする際に、カンマの代わりに;記号が区切り文字として使用されるようになります。

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

時には、CSVファイルからデータをインポートする際に特定の行数をスキップしたい場合があります。これは、input_format_csv_skip_first_linesオプションを使用して行うことができます：

この場合、CSVファイルの最初の10行をスキップします：

ファイルには1k行がありますが、最初の10行をスキップするように指定したため、ClickHouseは990行のみを読み込みました。

ヒント

file()関数を使用する場合、ClickHouse Cloudでは、ファイルが存在するマシンでclickhouse clientのコマンドを実行する必要があります。別のオプションは、ローカルファイルを探索するためにclickhouse-localを使用することです。

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

NULL値は、ファイルを生成したアプリケーションによって異なる方法でエンコードされることがあります。デフォルトでは、ClickHouseはCSV内のNULL値として\Nを使用します。しかし、format_csv_null_representationオプションを使用してこれを変更できます。

以下のCSVファイルを考えてみましょう：

このファイルからデータをロードすると、ClickHouseはNothingを文字列として扱います（これは正しいです）：

ClickHouseにNothingをNULLとして扱わせたい場合、次のオプションを定義できます：

これで、期待される場所にNULLがあります：

TSV（タブ区切り）ファイル​

タブ区切りデータフォーマットは、データ交換フォーマットとして広く使用されています。TSVファイルからClickHouseにデータをロードするには、TabSeparatedフォーマットが使用されます：

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

生TSV​

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

CSVへのエクスポート​

前の例に示した任意のフォーマットを使ってデータをエクスポートすることもできます。テーブル（またはクエリ）からCSV形式にデータをエクスポートするには、同じFORMAT句を使用します：

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

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

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

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

カスタム区切り文字でのCSVエクスポート​

カンマ以外の区切り文字を使用したい場合は、format_csv_delimiter設定オプションを使用します：

これでClickHouseはCSV形式の区切り文字として|を使用します：

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

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

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

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

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

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

明示的なカラムタイプを使用したCSVのエクスポートとインポート​

ClickHouseは、データをエクスポートする際にカラムタイプを明示的に設定することも許可しています。CSVWithNamesAndTypes（および他の*WithNames形式ファミリー）を使用します：

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

これでClickHouseは、推測するのではなく第（2）ヘッダ行に基づいてカラムタイプを識別します。

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

複雑なケースでは、テキストデータが非常にカスタムな方法でフォーマットされている場合でも、構造を持つことがあります。ClickHouseには、そのような場合のために特別なCustomSeparatedフォーマットがあり、カスタムエスケープルール、区切り文字、行セパレーター、開始/終了シンボルを設定できます。

以下のデータがファイルにあるとします：

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

これで、カスタムフォーマットのファイルからデータをロードできます：

CustomSeparatedWithNamesを使用して、ヘッダを正しくエクスポートおよびインポートすることもできます。さらに複雑なケースにはregexおよびテンプレートフォーマットを探索してください。

大きなCSVファイルの操作​

CSVファイルは大きくなることがあり、ClickHouseは任意のサイズのファイルで効率的に動作します。大きなファイルは通常圧縮されて提供され、ClickHouseは処理前に圧縮を解く必要がありません。挿入時にCOMPRESSION句を使用できます：

COMPRESSION句を省略した場合、ClickHouseは拡張子に基づいてファイルの圧縮を推測しようとします。同様のアプローチを使用して、直接圧縮されたフォーマットでファイルをエクスポートすることができます：

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

その他のフォーマット​

ClickHouseは、さまざまなシナリオやプラットフォームをカバーするために多くのフォーマット（テキストとバイナリの両方）をサポートしています。以下の記事でさらに多くのフォーマットやそれらとの作業方法を探ってみてください：

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