テーブル関数 file, s3, url, hdfs, azureBlobStorage.
structure があります。この引数を指定しないか、auto に設定した場合は、構造がデータから推論されます。
例:
user_files ディレクトリに、以下の内容を持つ JSONEachRow フォーマットのファイル hobbies.jsonl があるとします。
JSONEachRow は、ファイル拡張子 .jsonl に基づいて自動的に判定されました。
自動的に判定された構造は、DESCRIBE クエリで確認できます:
テーブルエンジン File, S3, URL, HDFS, azureBlobStorage
CREATE TABLE クエリでカラムの一覧を指定しない場合、テーブルの構造はデータから自動的に推論されます。
例:
ファイル hobbies.jsonl を使用します。このファイルのデータを使って、File エンジンのテーブルを作成できます:
clickhouse-local には、入力データの構造を指定するオプションのパラメーター -S/--structure があります。このパラメーターを指定しない場合、または auto に設定した場合は、構造がデータから推論されます。
例:
ファイル hobbies.jsonl を使ってみましょう。clickhouse-local を使うと、このファイルのデータに対してクエリを実行できます。
file/s3/url/hdfs を使用してテーブルにデータを挿入する場合、
データから構造を抽出する代わりに、挿入先テーブルの構造を使用するオプションがあります。
スキーマ推論には時間がかかることがあるため、これにより挿入性能を向上させることができます。また、テーブルに最適化されたスキーマが定義されている場合にも有効で、
型変換は行われません。
この動作を制御する特別な設定 use_structure_from_insertion_table_in_table_functions
があります。これには 3 つの設定可能な値があります。
- 0 - テーブル関数はデータから構造を抽出します。
- 1 - テーブル関数は挿入先テーブルの構造を使用します。
- 2 - ClickHouse が、挿入先テーブルの構造を使用できるか、またはスキーマ推論を使用するかを自動的に判定します。デフォルト値です。
hobbies1 を作成します。
hobbies.jsonl からデータを挿入します:
hobbies2 を作成してみましょう。
hobbies.jsonl からデータを挿入します:
SELECT クエリ内のすべてのカラムがテーブルに存在するため、ClickHouse は挿入先テーブルの構造を使用します。
これは、JSONEachRow、TSKV、Parquet など、カラムの一部を読み取れる入力フォーマットでのみ機能する点に注意してください (したがって、たとえば TSV フォーマットでは機能しません) 。
例 3:
次の構造でテーブル hobbies3 を作成しましょう。
hobbies.jsonl からデータを挿入します:
SELECT クエリではカラム id が使われていますが、テーブルにはこのカラムがありません (identifier という名前のカラムがあります) 。
そのため、ClickHouse は挿入先テーブルの構造を利用できず、スキーマ推論が使用されます。
例 4:
次の構造でテーブル hobbies4 を作成しましょう。
hobbies.jsonl からデータを挿入します:
SELECT クエリ内でカラム hobbies に対していくつかの操作が行われているため、ClickHouse は挿入先テーブルの構造を利用できず、スキーマ推論が行われます。
ほとんどの入力フォーマットでは、スキーマ推論のためにデータの一部を読み取ってその構造を判定する必要があり、この処理には時間がかかることがあります。
ClickHouse が同じファイルからデータを読み取るたびに毎回同じスキーマを推論しないよう、推論されたスキーマはcacheされます。そのため、同じファイルに再度アクセスした場合、ClickHouse はcache内のスキーマを使用します。
このcacheを制御する特別な設定があります:
schema_inference_cache_max_elements_for_{file/s3/hdfs/url/azure}- 対応するテーブル関数についてcacheされるスキーマの最大数です。デフォルト値は4096です。これらの設定はサーバー設定で指定する必要があります。schema_inference_use_cache_for_{file,s3,hdfs,url,azure}- スキーマ推論でcacheを使用するかどうかをオン/オフできます。これらの設定はクエリで使用できます。
url テーブル関数で URL 経由でアクセスされる一部のファイルには、最終更新時刻の情報が含まれていない場合があります。このケースのために、特別な設定
schema_inference_cache_require_modification_time_for_url があります。この設定を無効にすると、そのようなファイルでも最終更新時刻がなくてもcache内のスキーマを使用できます。
現在cache内にあるすべてのスキーマを確認できるシステムテーブル schema_inference_cache と、SYSTEM CLEAR SCHEMA CACHE [FOR File/S3/URL/HDFS]
というシステムクエリもあり、これによりすべてのソース、または特定のソースのスキーマcacheをクリアできます。
例:
S3 上のサンプル dataset github-2022.ndjson.gz の構造を推論し、スキーマ推論cacheがどのように動作するかを見てみましょう:
system.schema_inference_cache テーブルの内容を確認してみましょう。
input_format_max_rows_to_read_for_schema_inference (デフォルトは 25000) および input_format_max_bytes_to_read_for_schema_inference (デフォルトは 32Mb) で制御されます。
デフォルトでは、推論されたすべての型は Nullable ですが、schema_inference_make_columns_nullable を設定することで変更できます (例は 設定 セクションを参照してください) 。
JSON フォーマットでは、ClickHouse は JSON 仕様に従って値を解析し、最も適切なデータ型を見つけようとします。
ここでは、その仕組み、推論できる型、およびJSON フォーマットで使用できる具体的な設定について説明します。
例
以降の例では、format テーブル関数を使用します。
整数、浮動小数点数、Bool、String:
nullが含まれている場合、ClickHouseは他のArray要素の型を使用します:
input_format_json_infer_array_of_dynamic_from_array_of_different_types が有効な場合 (デフォルトで有効) 、その型は Array(Dynamic) になります:
input_format_json_try_infer_named_tuples_from_objects が有効な場合、スキーマ推論時に ClickHouse は JSON オブジェクトから名前付き Tuple を推論します。
生成される名前付き Tuple には、サンプルデータ内の対応するすべての JSON オブジェクトに含まれる全要素が含まれます。
input_format_json_infer_array_of_dynamic_from_array_of_different_types が無効な場合、JSON フォーマットでは、要素の型が異なる Array は名前のない Tuple として扱われます。
nullまたは空の場合は、他の行にある対応する値の型を使用します。
input_format_json_read_objects_as_strings と input_format_json_try_infer_named_tuples_from_objects が無効になっている場合にのみ機能します。
input_format_json_infer_incomplete_types_as_strings が有効であれば型 String が使用され、そうでなければ例外がスローされます:
input_format_json_try_infer_numbers_from_strings
input_format_json_try_infer_named_tuples_from_objects
Query
Response
Query
Response
input_format_json_use_string_type_for_ambiguous_paths_in_named_tuples_inference_from_objects
input_format_json_try_infer_named_tuples_from_objects が有効な場合、JSON オブジェクトから名前付き Tuple を推論する際に、あいまいなパスで例外を発生させる代わりに String 型を使用できるようになります。
これにより、あいまいなパスが存在する場合でも、JSON オブジェクトを名前付き Tuple として読み取ることができます。
デフォルトでは無効です。
例
設定が無効な場合:
Query
Response
Query
Response
input_format_json_read_objects_as_strings
input_format_json_try_infer_named_tuples_from_objects が無効になっている場合のみです。
input_format_json_read_numbers_as_strings
input_format_json_read_bools_as_numbers
input_format_json_read_bools_as_strings
input_format_json_read_arrays_as_strings
input_format_json_infer_incomplete_types_as_strings
Null/{}/[] のみを含む JSON キーに対して String 型を使用できます。
JSON フォーマットでは、対応する設定がすべて有効になっていれば任意の値を String として読み取ることができ (これらはすべてデフォルトで有効です) 、型が不明なキーに String 型を使用することで、スキーマ推論時に Cannot determine type for column 'column_name' by first 25000 rows of data, most likely this column contains only Nulls or empty Arrays/Maps のようなエラーを回避できます。
例:
Query
Response
input_format_csv_use_best_effort_in_schema_inference
を無効にできます。そうすると、ClickHouse はすべてのカラムを String として扱います。
設定 input_format_csv_detect_header が有効な場合、ClickHouse はスキーマ推論時に、カラム名 (場合によっては型も含む) を持つヘッダーを検出しようとします。この設定はデフォルトで有効です。
例:
整数、浮動小数点数、Bool、String:
input_format_csv_use_best_effort_in_schema_inference 設定を無効にした例:
input_format_csv_detect_header が有効な場合) :
列名のみ:
input_format_csv_try_infer_numbers_from_strings
input_format_tsv_use_best_effort_in_schema_inference を無効にできます。
その場合、ClickHouse はすべてのカラムを String として扱います。
設定 input_format_tsv_detect_header が有効な場合、ClickHouse はスキーマ推論時にカラム名 (場合によっては型も含む) を持つヘッダーの検出を試みます。この設定はデフォルトで有効です。
例:
整数、浮動小数点数、Bool、文字列:
input_format_tsv_use_best_effort_in_schema_inference を無効にした場合の例:
input_format_tsv_detect_header が有効な場合) :
名前のみ:
String型以外である場合にのみ、ヘッダーを検出できる点に注意してください。すべてのカラムがString型の場合、ヘッダーは検出されません。
input_format_tsv_use_best_effort_in_schema_inference 設定を無効にした例:
input_format_custom_detect_header が有効な場合、ClickHouse はスキーマ推論時に、カラム名 (場合によっては型も含む) を持つヘッダーの検出を試みます。この設定はデフォルトで有効です。
例
input_format_custom_detect_header が有効になっている場合) :
resultset があるとします。
row_format:
input_format_max_rows_to_read_for_schema_inference/input_format_max_bytes_to_read_for_schema_inference
input_format_max_rows_to_read_for_schema_inferenceは25000input_format_max_bytes_to_read_for_schema_inferenceは33554432(32 Mb)
c1,c2,c3,... の代わりに使用されます。形式: column1,column2,column3,...。
例
Nullableにするかどうかを制御します。設定可能な値:
- 0 - 推論される型が
Nullableになることはありません。 - 1 - 推論される型はすべて
Nullableになります, - 2 または ‘auto’ - テキストフォーマットでは、スキーマ推論時に解析されるサンプル内のカラムに
NULLが含まれている場合にのみ、推論される型はNullableになります。厳密に型付けされたフォーマット (Parquet、ORC、Arrow) では、NULL 許容性の情報はファイルのメタデータから取得されます。 - 3 - テキストフォーマットでは
Nullableを使用し、厳密な型情報を持つフォーマットではファイルメタデータを使用します。
この設定は
JSON データ型には適用されません。Int64 になります。少なくとも 1 つの数値が浮動小数点数である場合、結果の型は Float64 になります。
サンプルデータに整数しか含まれておらず、かつ少なくとも 1 つの整数が正の値で Int64 の範囲を超える場合、ClickHouse は UInt64 を推論します。
デフォルトで有効です。
例
DateTime または DateTime64 型の推論を試みます。
サンプルデータ内のあるカラムについて、すべてのフィールドが日時として正常に解析された場合、結果の型は DateTime または DateTime64(9) になります (いずれかの日時に小数部が含まれている場合) 。
1 つでも日時として解析されないフィールドがある場合、結果の型は String になります。
デフォルトで有効です。
例
input_format_try_infer_datetimes が有効になっていれば、日時の値に小数部が含まれていなくても、ClickHouse は常に DateTime64(9) を推論します。
デフォルトでは無効です。
例
Date 型を推論しようとします。
サンプルデータ内のあるカラムのすべてのフィールドが日付として正常に解析された場合、結果の型は Date になります。
1 つでも日付として解析されないフィールドがある場合、結果の型は String になります。
デフォルトで有効です。
例
その他の Avro 型はサポートされていません。
Parquet フォーマットでは、ClickHouse はデータからスキーマを読み取り、以下の型対応に基づいて ClickHouse のスキーマに変換します。
その他の Parquet 型はサポートされていません。
Arrow フォーマットでは、ClickHouse はデータからスキーマを読み取り、次の型対応に基づいて ClickHouse のスキーマへ変換します。
そのほかの Arrow 型はサポートされていません。
ORCフォーマットでは、ClickHouseはデータからスキーマを読み取り、次の型対応に従ってClickHouseのスキーマへ変換します。
そのほかのORC型はサポートされていません。
Native フォーマットは ClickHouse 内部で使用され、データ自体にスキーマが含まれています。
スキーマ推論では、ClickHouse は変換を行わずにデータからスキーマを読み取ります。
この種のフォーマットでは、データを記述するスキーマを、特定のスキーマ言語で別ファイルに定義する必要があります。
この種のフォーマットのファイルからスキーマ推論を行うため、ClickHouse は別ファイルから外部スキーマを読み取り、ClickHouse のテーブルスキーマに変換します。
Protobuf フォーマットのスキーマ推論では、ClickHouse は以下の型対応を使用します。
CapnProto フォーマットのスキーマ推論では、ClickHouse は以下のように型対応します。
このようなフォーマットでは、シリアル化された各値にその型 (場合によっては名前も) に関する情報は含まれますが、テーブル全体に関する情報は含まれません。
このようなフォーマットのスキーマ推論では、ClickHouse はデータを行単位で読み取り (最大
input_format_max_rows_to_read_for_schema_inference 行または input_format_max_bytes_to_read_for_schema_inference バイトまで) 、各値の型 (場合によっては名前も) をデータから抽出し、
それらの型を ClickHouse の型に変換します。
MsgPack フォーマットでは行の間に区切り文字がないため、このフォーマットでスキーマ推論を使用するには、設定 input_format_msgpack_number_of_columns でテーブルのカラム数を指定する必要があります。ClickHouse では、次のように型対応します。
デフォルトでは、推論されたすべての型は
Nullable になりますが、設定 schema_inference_make_columns_nullable で変更できます。
BSONEachRow では、各行のデータが BSON ドキュメントとして表されます。スキーマ推論では、ClickHouse は BSON ドキュメントを 1 つずつ読み取り、データから値、名前、型を抽出したうえで、以下の型対応に基づいてそれらを ClickHouse の型に変換します。
デフォルトでは、推論されたすべての型は
Nullable でラップされますが、設定 schema_inference_make_columns_nullable を使って変更できます。
このようなフォーマットでは、データは常に同じスキーマになります。
このフォーマットでは、ClickHouse はデータの1行全体を String データ型の単一カラムとして読み込みます。このフォーマットで推論される型は常に String で、カラム名は line です。
例
String データ型の 1 つのカラムに読み込みます。このフォーマットで推論される型は常に String で、カラム名は json です。
例
JSON データ型の単一のカラムに読み込みます。このフォーマットで推論される型は常に JSON で、カラム名は json です。
例
default と union の 2 つの異なるモードで動作します。
このモードは、設定 schema_inference_mode で制御されます。
デフォルトモードでは、ClickHouse はすべてのファイルが同じスキーマを持つとみなし、スキーマを推定できるまでファイルを 1 つずつ読み取ります。
例:
data1.jsonl、data2.jsonl、data3.jsonl の 3 つのファイルがあり、内容は次のとおりだとします。
data1.jsonl:
data2.jsonl:
data3.jsonl:
Query
Response
data3.jsonl の field3 は含まれていません。
これは、ClickHouse がまずファイル data1.jsonl からスキーマを推論しようとしたものの、フィールド field2 がすべて NULL だったために失敗し、
その後 data2.jsonl からのスキーマ推論には成功したため、ファイル data3.jsonl のデータは読み込まれなかったためです。
ユニオンモードでは、ClickHouse はファイルごとに異なるスキーマを持つ可能性があるとみなし、すべてのファイルのスキーマを推論したうえで、それらを 1 つの共通スキーマにユニオンします。
次の内容を持つ 3 つのファイル data1.jsonl、data2.jsonl、data3.jsonl があるとします。
data1.jsonl:
data2.jsonl:
data3.jsonl:
Query
Response
- 一部のファイルには、推論後のスキーマに含まれるカラムの一部が存在しない場合があるため、ユニオンモードはカラムの部分集合の読み取りをサポートするフォーマット (JSONEachRow、Parquet、TSVWithNames など) でのみサポートされます。その他のフォーマット (CSV、TSV、JSONCompactEachRow など) では動作しません。
- ClickHouse がいずれかのファイルからスキーマを推論できない場合は、例外がスローされます。
- ファイル数が多い場合、それらすべてからスキーマを読み取るのにかなり時間がかかることがあります。
data があるとします。
ClickHouseが検出できるのは一部のフォーマットだけで、この検出には多少時間もかかるため、フォーマットは常に明示的に指定することをおすすめします。