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

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

ClickHouseはCSVからのデータのインポートおよびCSVへのデータのエクスポートをサポートしています。CSVファイルはヘッダー行、カスタム区切り文字、エスケープ記号など、さまざまなフォーマットの特性を持つ可能性があるため、ClickHouseはそれぞれのケースに効率的に対応するためのフォーマットと設定を提供します。

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

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

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

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

ここでは、FORMAT CSV句を使ってClickHouseにファイルの形式を理解させています。また、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行をスキップします：

fileには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()関数とペアで使用して調べることができます：

ここで、ClickHouseはCSVファイルのカラム型を効率的に推測することができました。ClickHouseに推測を行わせたくない場合、次のオプションを使用してこれを無効にできます：

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

明示的なカラム型を用いたCSVのエクスポートおよびインポート​

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

この形式では二つのヘッダー行が含まれます - 一つはカラム名で、もう一つはカラム型です。これにより、ClickHouse（および他のアプリケーション）がそのようなファイルからデータをロードする際にカラム型を識別できるようになります：

これで、ClickHouseは推測の代わりに（第二の）ヘッダー行に基づいてカラム型を識別します。

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

高度にカスタマイズされた方法でフォーマットされたテキストデータでも、構造を持っている場合があります。ClickHouseには、そのようなケースに対応するための特別なCustomSeparated形式があり、カスタムのエスケープルール、区切り文字、行のセパレータ、および開始/終了記号を設定することができます。

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

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

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

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

大きなCSVファイルとの作業​

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

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

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

その他のフォーマット​

ClickHouseは、さまざまなシナリオやプラットフォームをカバーするために、テキストおよびバイナリ形式の多くのサポートを導入しています。以下のドキュメントで、さまざまなフォーマットやその操作方法を調査してください：

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