ClickHouseは、ほとんどすべてのサポートされている Input formats において、入力データの構造を自動的に判断できます。この文書では、スキーマ推論が使用される場合、様々な入力フォーマットとの動作、およびそれを制御する設定について説明します。
使用法
スキーマ推論は、ClickHouseが特定のデータフォーマットでデータを読み取る必要があるが、その構造が不明な場合に使用されます。
これらのテーブル関数には、入力データの構造を指定するオプションの引数 structure があります。この引数が指定されていないか、auto に設定されている場合、構造はデータから推論されます。
例:
user_files ディレクトリ内に、JSONEachRowフォーマットの hobbies.jsonl というファイルがあり、以下のような内容であるとしましょう：
{"id" : 1, "age" : 25, "name" : "Josh", "hobbies" : ["football", "cooking", "music"]}
{"id" : 2, "age" : 19, "name" : "Alan", "hobbies" : ["tennis", "art"]}
{"id" : 3, "age" : 32, "name" : "Lana", "hobbies" : ["fitness", "reading", "shopping"]}
{"id" : 4, "age" : 47, "name" : "Brayan", "hobbies" : ["movies", "skydiving"]}
ClickHouseは、このデータの構造を指定せずに読み取ることができます：
SELECT * FROM file('hobbies.jsonl')
┌─id─┬─age─┬─name───┬─hobbies──────────────────────────┐
│ 1 │ 25 │ Josh │ ['football','cooking','music'] │
│ 2 │ 19 │ Alan │ ['tennis','art'] │
│ 3 │ 32 │ Lana │ ['fitness','reading','shopping'] │
│ 4 │ 47 │ Brayan │ ['movies','skydiving'] │
└────┴─────┴────────┴──────────────────────────────────┘
注意: フォーマット JSONEachRow は、自動的に拡張子 .jsonl から判断されました。
自動的に決定された構造は、DESCRIBE クエリを使用して確認できます：
DESCRIBE file('hobbies.jsonl')
┌─name────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Nullable(Int64) │ │ │ │ │ │
│ age │ Nullable(Int64) │ │ │ │ │ │
│ name │ Nullable(String) │ │ │ │ │ │
│ hobbies │ Array(Nullable(String)) │ │ │ │ │ │
└─────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
CREATE TABLE クエリでカラムのリストが指定されていない場合、テーブルの構造はデータから自動的に推論されます。
例:
ファイル hobbies.jsonl を使用しましょう。このファイルからのデータで、エンジン File でテーブルを作成できます：
CREATE TABLE hobbies ENGINE=File(JSONEachRow, 'hobbies.jsonl')
┌─id─┬─age─┬─name───┬─hobbies──────────────────────────┐
│ 1 │ 25 │ Josh │ ['football','cooking','music'] │
│ 2 │ 19 │ Alan │ ['tennis','art'] │
│ 3 │ 32 │ Lana │ ['fitness','reading','shopping'] │
│ 4 │ 47 │ Brayan │ ['movies','skydiving'] │
└────┴─────┴────────┴──────────────────────────────────┘
┌─name────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Nullable(Int64) │ │ │ │ │ │
│ age │ Nullable(Int64) │ │ │ │ │ │
│ name │ Nullable(String) │ │ │ │ │ │
│ hobbies │ Array(Nullable(String)) │ │ │ │ │ │
└─────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
clickhouse-local
clickhouse-local には、入力データの構造を指定するオプションのパラメータ -S/--structure があります。このパラメータが指定されていないか、auto に設定されている場合、構造はデータから推論されます。
例:
ファイル hobbies.jsonl を使用しましょう。このファイルからデータをクエリするには、clickhouse-local を使用します：
clickhouse-local --file='hobbies.jsonl' --table='hobbies' --query='DESCRIBE TABLE hobbies'
id Nullable(Int64)
age Nullable(Int64)
name Nullable(String)
hobbies Array(Nullable(String))
clickhouse-local --file='hobbies.jsonl' --table='hobbies' --query='SELECT * FROM hobbies'
1 25 Josh ['football','cooking','music']
2 19 Alan ['tennis','art']
3 32 Lana ['fitness','reading','shopping']
4 47 Brayan ['movies','skydiving']
挿入テーブルからの構造の使用
テーブル関数 file/s3/url/hdfs がデータをテーブルに挿入するために使用されるとき、データから抽出するのではなく、挿入テーブルの構造を使用するオプションがあります。これは、スキーマ推論が時間を要するため、挿入性能を向上させることができます。また、テーブルに最適化されたスキーマがある場合、型間の変換が行われないため、役立ちます。
この動作を制御する特別な設定 use_structure_from_insertion_table_in_table_functions があります。これは3つの可能な値を持ちます：
- 0 - テーブル関数はデータから構造を抽出します。
- 1 - テーブル関数は挿入テーブルから構造を使用します。
- 2 - ClickHouseは、挿入テーブルから構造を使用できるか、スキーマ推論を使用するかを自動的に判断します。デフォルト値です。
例 1:
次の構造を持つテーブル hobbies1 を作成しましょう：
CREATE TABLE hobbies1
(
`id` UInt64,
`age` LowCardinality(UInt8),
`name` String,
`hobbies` Array(String)
)
ENGINE = MergeTree
ORDER BY id;
ファイル hobbies.jsonl からデータを挿入します：
INSERT INTO hobbies1 SELECT * FROM file(hobbies.jsonl)
この場合、ファイルのすべてのカラムが変更なしでテーブルに挿入されるため、ClickHouseはスキーマ推論の代わりに挿入テーブルから構造を使用します。
例 2:
次の構造を持つテーブル hobbies2 を作成しましょう：
CREATE TABLE hobbies2
(
`id` UInt64,
`age` LowCardinality(UInt8),
`hobbies` Array(String)
)
ENGINE = MergeTree
ORDER BY id;
ファイル hobbies.jsonl からデータを挿入します：
INSERT INTO hobbies2 SELECT id, age, hobbies FROM file(hobbies.jsonl)
この場合、SELECT クエリのすべてのカラムがテーブルに存在するため、ClickHouseは挿入テーブルから構造を使用します。なお、これはJSONEachRow、TSKV、Parquetなど、列のサブセットを読み取ることをサポートする入力フォーマットにのみ機能します（例として、TSVフォーマットでは機能しません）。
例 3:
次の構造を持つテーブル hobbies3 を作成しましょう：
CREATE TABLE hobbies3
(
`identifier` UInt64,
`age` LowCardinality(UInt8),
`hobbies` Array(String)
)
ENGINE = MergeTree
ORDER BY identifier;
ファイル hobbies.jsonl からデータを挿入します：
INSERT INTO hobbies3 SELECT id, age, hobbies FROM file(hobbies.jsonl)
この場合、SELECT クエリで id カラムが使用されますが、テーブルにはこのカラムがない（identifierという名前のカラムがある）ため、ClickHouseは挿入テーブルから構造を使用できず、スキーマ推論が使用されます。
例 4:
次の構造を持つテーブル hobbies4 を作成しましょう：
CREATE TABLE hobbies4
(
`id` UInt64,
`any_hobby` Nullable(String)
)
ENGINE = MergeTree
ORDER BY id;
ファイル hobbies.jsonl からデータを挿入します：
INSERT INTO hobbies4 SELECT id, empty(hobbies) ? NULL : hobbies[1] FROM file(hobbies.jsonl)
この場合、SELECT クエリでカラム hobbies に対して何らかの操作が行われるため、ClickHouseは挿入テーブルから構造を使用できず、スキーマ推論が使用されます。
スキーマ推論キャッシュ
ほとんどの入力フォーマットでは、スキーマ推論はデータを読み取って構造を判断するためにいくつかのデータを読み取ります。このプロセスは時間がかかる可能性があります。ClickHouseが同じファイルからデータを再び読み取る際に同じスキーマを推論するのを防ぐために、推論されたスキーマはキャッシュされ、同じファイルに再度アクセスすると、ClickHouseはキャッシュからスキーマを使用します。
このキャッシュを制御する特別な設定があります：
schema_inference_cache_max_elements_for_{file/s3/hdfs/url/azure} - 対応するテーブル関数に対する最大キャッシュスキーマ数。デフォルト値は 4096 です。これらの設定はサーバー設定で設定する必要があります。schema_inference_use_cache_for_{file,s3,hdfs,url,azure} - スキーマ推論のためのキャッシュの使用をオン/オフにすることを許可します。これらの設定はクエリで使用できます。
ファイルのスキーマは、データの変更やフォーマット設定の変更によって変更できます。この理由により、スキーマ推論キャッシュは、ファイルソース、フォーマット名、使用されたフォーマット設定、およびファイルの最終修正時刻によってスキーマを識別します。
注意: url テーブル関数でアクセスされる一部のファイルは、最終修正時刻に関する情報を含まない可能性があります。この場合、特別な設定 schema_inference_cache_require_modification_time_for_url があります。この設定を無効にすると、そのようなファイルに対して最終修正時刻なしでキャッシュからスキーマを使用することができます。
また、キャッシュ内のすべての現在のスキーマを持つシステムテーブル schema_inference_cache があり、システムクエリ SYSTEM DROP SCHEMA CACHE [FOR File/S3/URL/HDFS] を使用して、すべてのソースまたは特定のソースのスキーマキャッシュをクリーンアップできます。
例:
s3のサンプルデータセット github-2022.ndjson.gz の構造を推論して、スキーマ推論キャッシュの動作を確認してみましょう：
DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/github/github-2022.ndjson.gz')
SETTINGS allow_experimental_object_type = 1
┌─name───────┬─type─────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ type │ Nullable(String) │ │ │ │ │ │
│ actor │ Object(Nullable('json')) │ │ │ │ │ │
│ repo │ Object(Nullable('json')) │ │ │ │ │ │
│ created_at │ Nullable(String) │ │ │ │ │ │
│ payload │ Object(Nullable('json')) │ │ │ │ │ │
└────────────┴──────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
5 rows in set. Elapsed: 0.601 sec.
DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/github/github-2022.ndjson.gz')
SETTINGS allow_experimental_object_type = 1
┌─name───────┬─type─────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ type │ Nullable(String) │ │ │ │ │ │
│ actor │ Object(Nullable('json')) │ │ │ │ │ │
│ repo │ Object(Nullable('json')) │ │ │ │ │ │
│ created_at │ Nullable(String) │ │ │ │ │ │
│ payload │ Object(Nullable('json')) │ │ │ │ │ │
└────────────┴──────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
5 rows in set. Elapsed: 0.059 sec.
ご覧の通り、2回目のクエリはほぼ瞬時に成功しました。
推論されたスキーマに影響を与える設定を変更してみましょう：
DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/github/github-2022.ndjson.gz')
SETTINGS input_format_json_read_objects_as_strings = 1
┌─name───────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ type │ Nullable(String) │ │ │ │ │ │
│ actor │ Nullable(String) │ │ │ │ │ │
│ repo │ Nullable(String) │ │ │ │ │ │
│ created_at │ Nullable(String) │ │ │ │ │ │
│ payload │ Nullable(String) │ │ │ │ │ │
└────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
5 rows in set. Elapsed: 0.611 sec
キャッシュからのスキーマは同じファイルに対して使用されませんでした。なぜなら、推論されたスキーマに影響を与える設定が変更されたからです。
system.schema_inference_cache テーブルの内容を確認してみましょう：
SELECT schema, format, source FROM system.schema_inference_cache WHERE storage='S3'
┌─schema──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─format─┬─source───────────────────────────────────────────────────────────────────────────────────────────────────┐
│ type Nullable(String), actor Object(Nullable('json')), repo Object(Nullable('json')), created_at Nullable(String), payload Object(Nullable('json')) │ NDJSON │ datasets-documentation.s3.eu-west-3.amazonaws.com443/datasets-documentation/github/github-2022.ndjson.gz │
│ type Nullable(String), actor Nullable(String), repo Nullable(String), created_at Nullable(String), payload Nullable(String) │ NDJSON │ datasets-documentation.s3.eu-west-3.amazonaws.com443/datasets-documentation/github/github-2022.ndjson.gz │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────┘
ご覧の通り、同じファイルに対して2つの異なるスキーマがあります。
システムクエリを使用して、スキーマキャッシュをクリアします：
SYSTEM DROP SCHEMA CACHE FOR S3
SELECT count() FROM system.schema_inference_cache WHERE storage='S3'
┌─count()─┐
│ 0 │
└─────────┘
テキストフォーマット
テキストフォーマットの場合、ClickHouseはデータを行ごとに読み取り、フォーマットに従ってカラムの値を抽出し、各値の型を決定するために再帰的なパーサーとヒューリスティックを使用します。スキーマ推論で読み取るデータの最大行数とバイト数は、設定 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 を設定することでこれを変更できます（例については settings セクションを参照）。
JSONフォーマットでは、ClickHouseは値をJSON仕様に従って解析し、その後最も適切なデータ型を見つけようとします。
どのように機能するか、どの型が推論できるか、およびJSONフォーマットで使用できる特定の設定を見てみましょう。
例
ここでおよび今後の例では、 format テーブル関数が使用されます。
整数、浮動小数点、真偽値、文字列：
DESC format(JSONEachRow, '{"int" : 42, "float" : 42.42, "string" : "Hello, World!"}');
┌─name───┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ int │ Nullable(Int64) │ │ │ │ │ │
│ float │ Nullable(Float64) │ │ │ │ │ │
│ bool │ Nullable(Bool) │ │ │ │ │ │
│ string │ Nullable(String) │ │ │ │ │ │
└────────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
日付、日付時刻：
DESC format(JSONEachRow, '{"date" : "2022-01-01", "datetime" : "2022-01-01 00:00:00", "datetime64" : "2022-01-01 00:00:00.000"}')
┌─name───────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ date │ Nullable(Date) │ │ │ │ │ │
│ datetime │ Nullable(DateTime) │ │ │ │ │ │
│ datetime64 │ Nullable(DateTime64(9)) │ │ │ │ │ │
└────────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
配列：
DESC format(JSONEachRow, '{"arr" : [1, 2, 3], "nested_arrays" : [[1, 2, 3], [4, 5, 6], []]}')
┌─name──────────┬─type──────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ arr │ Array(Nullable(Int64)) │ │ │ │ │ │
│ nested_arrays │ Array(Array(Nullable(Int64))) │ │ │ │ │ │
└───────────────┴───────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
配列に null が含まれている場合、ClickHouseは他の配列要素から型を使用します：
DESC format(JSONEachRow, '{"arr" : [null, 42, null]}')
┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ arr │ Array(Nullable(Int64)) │ │ │ │ │ │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
名前付きタプル：
input_format_json_try_infer_named_tuples_from_objects を有効にすると、スキーマ推論中にClickHouseはJSONオブジェクトから名前付きタプルを推論しようとします。結果として得られる名前付きタプルは、サンプルデータのすべての対応するJSONオブジェクトからの要素を含みます。
SET input_format_json_try_infer_named_tuples_from_objects = 1;
DESC format(JSONEachRow, '{"obj" : {"a" : 42, "b" : "Hello"}}, {"obj" : {"a" : 43, "c" : [1, 2, 3]}}, {"obj" : {"d" : {"e" : 42}}}')
┌─name─┬─type───────────────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ obj │ Tuple(a Nullable(Int64), b Nullable(String), c Array(Nullable(Int64)), d Tuple(e Nullable(Int64))) │ │ │ │ │ │
└──────┴────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
名前なしタプル：
JSONフォーマットでは、異なる型の要素を持つ配列を名前なしタプルとして扱います。
DESC format(JSONEachRow, '{"tuple" : [1, "Hello, World!", [1, 2, 3]]}')
┌─name──┬─type─────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ tuple │ Tuple(Nullable(Int64), Nullable(String), Array(Nullable(Int64))) │ │ │ │ │ │
└───────┴──────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
いくつかの値が null または空である場合、他の行の対応する値から型を使用します：
DESC format(JSONEachRow, $$
{"tuple" : [1, null, null]}
{"tuple" : [null, "Hello, World!", []]}
{"tuple" : [null, null, [1, 2, 3]]}
$$)
┌─name──┬─type─────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ tuple │ Tuple(Nullable(Int64), Nullable(String), Array(Nullable(Int64))) │ │ │ │ │ │
└───────┴──────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
マップ：
JSONでは、同じ型の値を持つオブジェクトをマップ型として読み取ることができます。ただし、input_format_json_read_objects_as_strings と input_format_json_try_infer_named_tuples_from_objects を無効にしている場合のみ機能します。
SET input_format_json_read_objects_as_strings = 0, input_format_json_try_infer_named_tuples_from_objects = 0;
DESC format(JSONEachRow, '{"map" : {"key1" : 42, "key2" : 24, "key3" : 4}}')
┌─name─┬─type─────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ map │ Map(String, Nullable(Int64)) │ │ │ │ │ │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
JSONオブジェクト型（設定 allow_experimental_object_type が有効な場合）:
SET allow_experimental_object_type = 1
DESC format(JSONEachRow, $$
{"obj" : {"key1" : 42}}
{"obj" : {"key2" : "Hello, World!"}}
{"obj" : {"key1" : 24, "key3" : {"a" : 42, "b" : null}}}
$$)
┌─name─┬─type─────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ obj │ Object(Nullable('json')) │ │ │ │ │ │
└──────┴──────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
ネストされた複雑な型：
DESC format(JSONEachRow, '{"value" : [[[42, 24], []], {"key1" : 42, "key2" : 24}]}')
┌─name──┬─type─────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ value │ Tuple(Array(Array(Nullable(String))), Tuple(key1 Nullable(Int64), key2 Nullable(Int64))) │ │ │ │ │ │
└───────┴──────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
ClickHouseがすべてのキーの型を判断できない場合（データがすべてnullまたは空のオブジェクト/空の配列のみを含む場合）、設定 input_format_json_infer_incomplete_types_as_strings が有効な場合は型 String が使用され、それ以外は例外がスローされます：
DESC format(JSONEachRow, '{"arr" : [null, null]}') SETTINGS input_format_json_infer_incomplete_types_as_strings = 1;
┌─name─┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ arr │ Array(Nullable(String)) │ │ │ │ │ │
└──────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
DESC format(JSONEachRow, '{"arr" : [null, null]}') SETTINGS input_format_json_infer_incomplete_types_as_strings = 0;
Code: 652. DB::Exception: Received from localhost:9000. DB::Exception:
Cannot determine type for column 'arr' by first 1 rows of data,
most likely this column contains only Nulls or empty Arrays/Maps.
...
JSON設定
この設定を有効にすると、文字列値から数値推論が可能になります。
デフォルトではこの設定は無効です。
例:
SET input_format_json_try_infer_numbers_from_strings = 1;
DESC format(JSONEachRow, $$
{"value" : "42"}
{"value" : "424242424242"}
$$)
┌─name──┬─type────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ value │ Nullable(Int64) │ │ │ │ │ │
└───────┴─────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
この設定を有効にすると、JSONオブジェクトから名前付きタプルを推論できるようになります。結果として得られる名前付きタプルは、サンプルデータのすべての対応するJSONオブジェクトからの要素を含むことになります。JSONデータがスパースでない場合に便利で、データのサンプルがすべてのオブジェクトキーを含んでいる場合に役立ちます。
この設定はデフォルトで有効です。
例
SET input_format_json_try_infer_named_tuples_from_objects = 1;
DESC format(JSONEachRow, '{"obj" : {"a" : 42, "b" : "Hello"}}, {"obj" : {"a" : 43, "c" : [1, 2, 3]}}, {"obj" : {"d" : {"e" : 42}}}')
結果：
┌─name─┬─type───────────────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ obj │ Tuple(a Nullable(Int64), b Nullable(String), c Array(Nullable(Int64)), d Tuple(e Nullable(Int64))) │ │ │ │ │ │
└──────┴────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
SET input_format_json_try_infer_named_tuples_from_objects = 1;
DESC format(JSONEachRow, '{"array" : [{"a" : 42, "b" : "Hello"}, {}, {"c" : [1,2,3]}, {"d" : "2020-01-01"}]}')
結果：
┌─name──┬─type────────────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ array │ Array(Tuple(a Nullable(Int64), b Nullable(String), c Array(Nullable(Int64)), d Nullable(Date))) │ │ │ │ │ │
└───────┴─────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
##### input_format_json_use_string_type_for_ambiguous_paths_in_named_tuples_inference_from_objects \{#input_format_json_use_string_type_for_ambiguous_paths_in_named_tuples_inference_from_objects}
この設定を有効にすると、JSONオブジェクトからの名前付きタプルの推論中に、あいまいなパスに対してString型を使用できるようになります（`input_format_json_try_infer_named_tuples_from_objects`が有効な場合）。例外の代わりに使用されます。これにより、あいまいなパスがある場合でもJSONオブジェクトを名前付きタプルとして読み取ることができます。
デフォルトでは無効です。
**例**
無効な設定の場合:
```sql
SET input_format_json_try_infer_named_tuples_from_objects = 1;
SET input_format_json_use_string_type_for_ambiguous_paths_in_named_tuples_inference_from_objects = 0;
DESC format(JSONEachRow, '{"obj" : {"a" : 42}}, {"obj" : {"a" : {"b" : "Hello"}}}');
結果:
Code: 636. DB::Exception: The table structure cannot be extracted from a JSONEachRow format file. Error:
Code: 117. DB::Exception: JSON objects have ambiguous data: in some objects path 'a' has type 'Int64' and in some - 'Tuple(b String)'. You can enable setting input_format_json_use_string_type_for_ambiguous_paths_in_named_tuples_inference_from_objects to use String type for path 'a'. (INCORRECT_DATA) (version 24.3.1.1).
You can specify the structure manually. (CANNOT_EXTRACT_TABLE_STRUCTURE)
有効な設定の場合:
SET input_format_json_try_infer_named_tuples_from_objects = 1;
SET input_format_json_use_string_type_for_ambiguous_paths_in_named_tuples_inference_from_objects = 1;
DESC format(JSONEachRow, '{"obj" : {"a" : 42}}, {"obj" : {"a" : {"b" : "Hello"}}}');
SELECT * FROM format(JSONEachRow, '{"obj" : {"a" : 42}}, {"obj" : {"a" : {"b" : "Hello"}}}');
結果:
┌─name─┬─type──────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ obj │ Tuple(a Nullable(String)) │ │ │ │ │ │
└──────┴───────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
┌─obj─────────────────┐
│ ('42') │
│ ('{"b" : "Hello"}') │
└─────────────────────┘
この設定を有効にすると、ネストされたJSONオブジェクトを文字列として読み取ることができます。この設定を使用すると、JSONオブジェクト型を使用せずにネストされたJSONオブジェクトを読み取ることができます。
この設定はデフォルトで有効です。
注: この設定を有効にしても、input_format_json_try_infer_named_tuples_from_objectsが無効でなければ効果はありません。
SET input_format_json_read_objects_as_strings = 1, input_format_json_try_infer_named_tuples_from_objects = 0;
DESC format(JSONEachRow, $$
{"obj" : {"key1" : 42, "key2" : [1,2,3,4]}}
{"obj" : {"key3" : {"nested_key" : 1}}}
$$)
┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ obj │ Nullable(String) │ │ │ │ │ │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
この設定を有効にすると、数値を文字列として読み取ることができます。
この設定はデフォルトで有効です。
例
SET input_format_json_read_numbers_as_strings = 1;
DESC format(JSONEachRow, $$
{"value" : 1055}
{"value" : "unknown"}
$$)
┌─name──┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ value │ Nullable(String) │ │ │ │ │ │
└───────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
この設定を有効にすると、Bool値を数値として読み取ることができます。
この設定はデフォルトで有効です。
例:
SET input_format_json_read_bools_as_numbers = 1;
DESC format(JSONEachRow, $$
{"value" : true}
{"value" : 42}
$$)
┌─name──┬─type────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ value │ Nullable(Int64) │ │ │ │ │ │
└───────┴─────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
この設定を有効にすると、Bool値を文字列として読み取ることができます。
この設定はデフォルトで有効です。
例:
SET input_format_json_read_bools_as_strings = 1;
DESC format(JSONEachRow, $$
{"value" : true}
{"value" : "Hello, World"}
$$)
┌─name──┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ value │ Nullable(String) │ │ │ │ │ │
└───────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
この設定を有効にすると、JSON配列の値を文字列として読み取ることができます。
この設定はデフォルトで有効です。
例
SET input_format_json_read_arrays_as_strings = 1;
SELECT arr, toTypeName(arr), JSONExtractArrayRaw(arr)[3] from format(JSONEachRow, 'arr String', '{"arr" : [1, "Hello", [1,2,3]]}');
┌─arr───────────────────┬─toTypeName(arr)─┬─arrayElement(JSONExtractArrayRaw(arr), 3)─┐
│ [1, "Hello", [1,2,3]] │ String │ [1,2,3] │
└───────────────────────┴─────────────────┴───────────────────────────────────────────┘
この設定を有効にすると、スキーマ推論中にデータサンプルにNull/{}/[]のみを含むJSONキーに対してString型を使用できます。JSON形式では、対応する設定がすべて有効な場合は、任意の値を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というエラーを回避できます。この設定により、未知の型を持つキーに対してString型を使用できます。
例:
SET input_format_json_infer_incomplete_types_as_strings = 1, input_format_json_try_infer_named_tuples_from_objects = 1;
DESCRIBE format(JSONEachRow, '{"obj" : {"a" : [1,2,3], "b" : "hello", "c" : null, "d" : {}, "e" : []}}');
SELECT * FROM format(JSONEachRow, '{"obj" : {"a" : [1,2,3], "b" : "hello", "c" : null, "d" : {}, "e" : []}}');
結果:
┌─name─┬─type───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ obj │ Tuple(a Array(Nullable(Int64)), b Nullable(String), c Nullable(String), d Nullable(String), e Array(Nullable(String))) │ │ │ │ │ │
└──────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
┌─obj────────────────────────────┐
│ ([1,2,3],'hello',NULL,'{}',[]) │
└────────────────────────────────┘
CSV
CSV形式では、ClickHouseは行から区切り文字に従って列の値を抽出します。ClickHouseは、数字や文字列以外のすべての型が二重引用符で囲まれていることを期待しています。値が二重引用符で囲まれている場合、ClickHouseは再帰パーサーを使用して引用符内のデータを解析し、その後、最も適切なデータ型を見つけようとします。値が二重引用符で囲まれていない場合、ClickHouseはそれを数値として解析し、値が数値でない場合、ClickHouseは文字列として扱います。
ClickHouseが複雑な型をパーサーやヒューリスティックを使用して特定しないようにしたい場合は、input_format_csv_use_best_effort_in_schema_inferenceの設定を無効にすれば、ClickHouseはすべての列を文字列として扱います。
input_format_csv_detect_headerの設定が有効な場合、ClickHouseはスキーマを推論する際に列名（おそらく型も）を検出しようとします。この設定はデフォルトで有効です。
例:
整数、浮動小数点、Bool、文字列:
DESC format(CSV, '42,42.42,true,"Hello,World!"')
┌─name─┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(Int64) │ │ │ │ │ │
│ c2 │ Nullable(Float64) │ │ │ │ │ │
│ c3 │ Nullable(Bool) │ │ │ │ │ │
│ c4 │ Nullable(String) │ │ │ │ │ │
└──────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
引用符なしの文字列:
DESC format(CSV, 'Hello world!,World hello!')
┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(String) │ │ │ │ │ │
│ c2 │ Nullable(String) │ │ │ │ │ │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
日付、日付時刻:
DESC format(CSV, '"2020-01-01","2020-01-01 00:00:00","2022-01-01 00:00:00.000"')
┌─name─┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(Date) │ │ │ │ │ │
│ c2 │ Nullable(DateTime) │ │ │ │ │ │
│ c3 │ Nullable(DateTime64(9)) │ │ │ │ │ │
└──────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
配列:
DESC format(CSV, '"[1,2,3]","[[1, 2], [], [3, 4]]"')
┌─name─┬─type──────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Array(Nullable(Int64)) │ │ │ │ │ │
│ c2 │ Array(Array(Nullable(Int64))) │ │ │ │ │ │
└──────┴───────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
DESC format(CSV, $$"['Hello', 'world']","[['Abc', 'Def'], []]"$$)
┌─name─┬─type───────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Array(Nullable(String)) │ │ │ │ │ │
│ c2 │ Array(Array(Nullable(String))) │ │ │ │ │ │
└──────┴────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
配列にnullが含まれている場合、ClickHouseは他の配列要素の型を使用します:
DESC format(CSV, '"[NULL, 42, NULL]"')
┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Array(Nullable(Int64)) │ │ │ │ │ │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
マップ:
DESC format(CSV, $$"{'key1' : 42, 'key2' : 24}"$$)
┌─name─┬─type─────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Map(String, Nullable(Int64)) │ │ │ │ │ │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
ネストされた配列とマップ:
DESC format(CSV, $$"[{'key1' : [[42, 42], []], 'key2' : [[null], [42]]}]"$$)
┌─name─┬─type──────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Array(Map(String, Array(Array(Nullable(Int64))))) │ │ │ │ │ │
└──────┴───────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
ClickHouseが引用符内で型を特定できない場合、すなわちデータがnullのみを含む場合、ClickHouseはそれをStringとして扱います:
DESC format(CSV, '"[NULL, NULL]"')
┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(String) │ │ │ │ │ │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
input_format_csv_use_best_effort_in_schema_inferenceの設定を無効にした例:
SET input_format_csv_use_best_effort_in_schema_inference = 0
DESC format(CSV, '"[1,2,3]",42.42,Hello World!')
┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(String) │ │ │ │ │ │
│ c2 │ Nullable(String) │ │ │ │ │ │
│ c3 │ Nullable(String) │ │ │ │ │ │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
ヘッダー自動検出の例（input_format_csv_detect_headerが有効な場合）:
名前のみ:
SELECT * FROM format(CSV,
$$"number","string","array"
42,"Hello","[1, 2, 3]"
43,"World","[4, 5, 6]"
$$)
┌─number─┬─string─┬─array───┐
│ 42 │ Hello │ [1,2,3] │
│ 43 │ World │ [4,5,6] │
└────────┴────────┴─────────┘
名前と型:
DESC format(CSV,
$$"number","string","array"
"UInt32","String","Array(UInt16)"
42,"Hello","[1, 2, 3]"
43,"World","[4, 5, 6]"
$$)
┌─name───┬─type──────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ number │ UInt32 │ │ │ │ │ │
│ string │ String │ │ │ │ │ │
│ array │ Array(UInt16) │ │ │ │ │ │
└────────┴───────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
注: ヘッダーは、少なくとも1つの非String型の列が存在する場合にのみ検出されます。すべての列がString型の場合、ヘッダーは検出されません:
SELECT * FROM format(CSV,
$$"first_column","second_column"
"Hello","World"
"World","Hello"
$$)
┌─c1───────────┬─c2────────────┐
│ first_column │ second_column │
│ Hello │ World │
│ World │ Hello │
└──────────────┴───────────────┘
CSV設定
この設定を有効にすると、文字列から数値を推測できるようになります。
この設定はデフォルトで無効です。
例:
SET input_format_json_try_infer_numbers_from_strings = 1;
DESC format(CSV, '42,42.42');
┌─name─┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(Int64) │ │ │ │ │ │
│ c2 │ Nullable(Float64) │ │ │ │ │ │
└──────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
TSV/TSKV
TSV/TSKV形式では、ClickHouseはタブ区切り文字に従って行から列の値を抽出し、その後、抽出された値を再帰パーサーを使用して解析し、最も適切な型を判断します。型を特定できない場合、ClickHouseはこの値をStringとして扱います。
ClickHouseが複雑な型をパーサーやヒューリスティックを使用して特定しないようにしたい場合は、input_format_tsv_use_best_effort_in_schema_inferenceの設定を無効にすれば、ClickHouseはすべての列を文字列として扱います。
input_format_tsv_detect_headerの設定が有効な場合、ClickHouseはスキーマを推論する際に列名（おそらく型も）を検出しようとします。この設定はデフォルトで有効です。
例:
整数、浮動小数点、Bool、文字列:
DESC format(TSV, '42 42.42 true Hello,World!')
┌─name─┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(Int64) │ │ │ │ │ │
│ c2 │ Nullable(Float64) │ │ │ │ │ │
│ c3 │ Nullable(Bool) │ │ │ │ │ │
│ c4 │ Nullable(String) │ │ │ │ │ │
└──────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
DESC format(TSKV, 'int=42 float=42.42 bool=true string=Hello,World!\n')
┌─name───┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ int │ Nullable(Int64) │ │ │ │ │ │
│ float │ Nullable(Float64) │ │ │ │ │ │
│ bool │ Nullable(Bool) │ │ │ │ │ │
│ string │ Nullable(String) │ │ │ │ │ │
└────────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
日付、日付時刻:
DESC format(TSV, '2020-01-01 2020-01-01 00:00:00 2022-01-01 00:00:00.000')
┌─name─┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(Date) │ │ │ │ │ │
│ c2 │ Nullable(DateTime) │ │ │ │ │ │
│ c3 │ Nullable(DateTime64(9)) │ │ │ │ │ │
└──────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
配列:
DESC format(TSV, '[1,2,3] [[1, 2], [], [3, 4]]')
┌─name─┬─type──────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Array(Nullable(Int64)) │ │ │ │ │ │
│ c2 │ Array(Array(Nullable(Int64))) │ │ │ │ │ │
└──────┴───────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
DESC format(TSV, '[''Hello'', ''world''] [[''Abc'', ''Def''], []]')
┌─name─┬─type───────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Array(Nullable(String)) │ │ │ │ │ │
│ c2 │ Array(Array(Nullable(String))) │ │ │ │ │ │
└──────┴────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
配列にnullが含まれている場合、ClickHouseは他の配列要素の型を使用します:
DESC format(TSV, '[NULL, 42, NULL]')
┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Array(Nullable(Int64)) │ │ │ │ │ │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
タプル:
DESC format(TSV, $$(42, 'Hello, world!')$$)
┌─name─┬─type─────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Tuple(Nullable(Int64), Nullable(String)) │ │ │ │ │ │
└──────┴──────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
マップ:
DESC format(TSV, $${'key1' : 42, 'key2' : 24}$$)
┌─name─┬─type─────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Map(String, Nullable(Int64)) │ │ │ │ │ │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
ネストされた配列、タプル、およびマップ:
DESC format(TSV, $$[{'key1' : [(42, 'Hello'), (24, NULL)], 'key2' : [(NULL, ','), (42, 'world!')]}]$$)
┌─name─┬─type────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Array(Map(String, Array(Tuple(Nullable(Int64), Nullable(String))))) │ │ │ │ │ │
└──────┴─────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
ClickHouseが型を特定できない場合、すなわちデータがnullのみを含む場合、ClickHouseはそれをStringとして扱います:
DESC format(TSV, '[NULL, NULL]')
┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(String) │ │ │ │ │ │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
input_format_tsv_use_best_effort_in_schema_inferenceの設定を無効にした例:
SET input_format_tsv_use_best_effort_in_schema_inference = 0
DESC format(TSV, '[1,2,3] 42.42 Hello World!')
┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(String) │ │ │ │ │ │
│ c2 │ Nullable(String) │ │ │ │ │ │
│ c3 │ Nullable(String) │ │ │ │ │ │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
ヘッダー自動検出の例（input_format_tsv_detect_headerが有効な場合）:
名前のみ:
SELECT * FROM format(TSV,
$$number string array
42 Hello [1, 2, 3]
43 World [4, 5, 6]
$$);
┌─number─┬─string─┬─array───┐
│ 42 │ Hello │ [1,2,3] │
│ 43 │ World │ [4,5,6] │
└────────┴────────┴─────────┘
名前と型:
DESC format(TSV,
$$number string array
UInt32 String Array(UInt16)
42 Hello [1, 2, 3]
43 World [4, 5, 6]
$$)
┌─name───┬─type──────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ number │ UInt32 │ │ │ │ │ │
│ string │ String │ │ │ │ │ │
│ array │ Array(UInt16) │ │ │ │ │ │
└────────┴───────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
注: ヘッダーは、少なくとも1つの非String型の列が存在する場合にのみ検出されます。すべての列がString型の場合、ヘッダーは検出されません:
SELECT * FROM format(TSV,
$$first_column second_column
Hello World
World Hello
$$)
┌─c1───────────┬─c2────────────┐
│ first_column │ second_column │
│ Hello │ World │
│ World │ Hello │
└──────────────┴───────────────┘
Values
Valuesフォーマットでは、ClickHouseは行からカラムの値を抽出し、その後リテラルが解析される方法に似た再帰的なパーサーを使用して解析します。
例:
整数、浮動小数点数、ブール値、文字列:
DESC format(Values, $$(42, 42.42, true, 'Hello,World!')$$)
┌─name─┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(Int64) │ │ │ │ │ │
│ c2 │ Nullable(Float64) │ │ │ │ │ │
│ c3 │ Nullable(Bool) │ │ │ │ │ │
│ c4 │ Nullable(String) │ │ │ │ │ │
└──────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
日付、日時:
DESC format(Values, $$('2020-01-01', '2020-01-01 00:00:00', '2022-01-01 00:00:00.000')$$)
┌─name─┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(Date) │ │ │ │ │ │
│ c2 │ Nullable(DateTime) │ │ │ │ │ │
│ c3 │ Nullable(DateTime64(9)) │ │ │ │ │ │
└──────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
配列:
DESC format(Values, '([1,2,3], [[1, 2], [], [3, 4]])')
┌─name─┬─type──────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Array(Nullable(Int64)) │ │ │ │ │ │
│ c2 │ Array(Array(Nullable(Int64))) │ │ │ │ │ │
└──────┴───────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
配列にnullが含まれている場合、ClickHouseは他の配列要素から型を使用します:
DESC format(Values, '([NULL, 42, NULL])')
┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Array(Nullable(Int64)) │ │ │ │ │ │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
タプル:
DESC format(Values, $$((42, 'Hello, world!'))$$)
┌─name─┬─type─────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Tuple(Nullable(Int64), Nullable(String)) │ │ │ │ │ │
└──────┴──────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
マップ:
DESC format(Values, $$({'key1' : 42, 'key2' : 24})$$)
┌─name─┬─type─────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Map(String, Nullable(Int64)) │ │ │ │ │ │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
ネストされた配列、タプル、マップ:
DESC format(Values, $$([{'key1' : [(42, 'Hello'), (24, NULL)], 'key2' : [(NULL, ','), (42, 'world!')]}])$$)
┌─name─┬─type────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Array(Map(String, Array(Tuple(Nullable(Int64), Nullable(String))))) │ │ │ │ │ │
└──────┴─────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
ClickHouseが型を判断できない場合、データがすべてnullから構成されている場合、例外がスローされます:
DESC format(Values, '([NULL, NULL])')
Code: 652. DB::Exception: Received from localhost:9000. DB::Exception:
Cannot determine type for column 'c1' by first 1 rows of data,
most likely this column contains only Nulls or empty Arrays/Maps.
...
設定input_format_tsv_use_best_effort_in_schema_inferenceが無効化された例:
SET input_format_tsv_use_best_effort_in_schema_inference = 0
DESC format(TSV, '[1,2,3] 42.42 Hello World!')
┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(String) │ │ │ │ │ │
│ c2 │ Nullable(String) │ │ │ │ │ │
│ c3 │ Nullable(String) │ │ │ │ │ │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
CustomSeparated
CustomSeparatedフォーマットでは、ClickHouseは最初に指定された区切り文字に従って行からすべてのカラム値を抽出し、その後、エスケープルールに従って各値のデータ型を推測しようとします。
設定input_format_custom_detect_headerが有効になっている場合、ClickHouseはスキーマを推測する際にカラム名（おそらく型）のヘッダーを検出しようとします。この設定はデフォルトで有効です。
例
SET format_custom_row_before_delimiter = '<row_before_delimiter>',
format_custom_row_after_delimiter = '<row_after_delimiter>\n',
format_custom_row_between_delimiter = '<row_between_delimiter>\n',
format_custom_result_before_delimiter = '<result_before_delimiter>\n',
format_custom_result_after_delimiter = '<result_after_delimiter>\n',
format_custom_field_delimiter = '<field_delimiter>',
format_custom_escaping_rule = 'Quoted'
DESC format(CustomSeparated, $$<result_before_delimiter>
<row_before_delimiter>42.42<field_delimiter>'Some string 1'<field_delimiter>[1, NULL, 3]<row_after_delimiter>
<row_between_delimiter>
<row_before_delimiter>NULL<field_delimiter>'Some string 3'<field_delimiter>[1, 2, NULL]<row_after_delimiter>
<result_after_delimiter>
$$)
┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(Float64) │ │ │ │ │ │
│ c2 │ Nullable(String) │ │ │ │ │ │
│ c3 │ Array(Nullable(Int64)) │ │ │ │ │ │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
ヘッダーの自動検出（input_format_custom_detect_headerが有効な場合）の例:
SET format_custom_row_before_delimiter = '<row_before_delimiter>',
format_custom_row_after_delimiter = '<row_after_delimiter>\n',
format_custom_row_between_delimiter = '<row_between_delimiter>\n',
format_custom_result_before_delimiter = '<result_before_delimiter>\n',
format_custom_result_after_delimiter = '<result_after_delimiter>\n',
format_custom_field_delimiter = '<field_delimiter>',
format_custom_escaping_rule = 'Quoted'
DESC format(CustomSeparated, $$<result_before_delimiter>
<row_before_delimiter>'number'<field_delimiter>'string'<field_delimiter>'array'<row_after_delimiter>
<row_between_delimiter>
<row_before_delimiter>42.42<field_delimiter>'Some string 1'<field_delimiter>[1, NULL, 3]<row_after_delimiter>
<row_between_delimiter>
<row_before_delimiter>NULL<field_delimiter>'Some string 3'<field_delimiter>[1, 2, NULL]<row_after_delimiter>
<result_after_delimiter>
$$)
┌─number─┬─string────────┬─array──────┐
│ 42.42 │ Some string 1 │ [1,NULL,3] │
│ ᴺᵁᴸᴸ │ Some string 3 │ [1,2,NULL] │
└────────┴───────────────┴────────────┘
Template
Templateフォーマットでは、ClickHouseは最初に指定されたテンプレートに従って行からすべてのカラム値を抽出し、その後、エスケープルールに従って各値のデータ型を推測しようとします。
例
次の内容を持つファイルresultsetがあるとしましょう:
<result_before_delimiter>
${data}<result_after_delimiter>
次の内容を持つファイルrow_formatがあるとしましょう:
<row_before_delimiter>${column_1:CSV}<field_delimiter_1>${column_2:Quoted}<field_delimiter_2>${column_3:JSON}<row_after_delimiter>
これにより、次のクエリを実行できます:
SET format_template_rows_between_delimiter = '<row_between_delimiter>\n',
format_template_row = 'row_format',
format_template_resultset = 'resultset_format'
DESC format(Template, $$<result_before_delimiter>
<row_before_delimiter>42.42<field_delimiter_1>'Some string 1'<field_delimiter_2>[1, null, 2]<row_after_delimiter>
<row_between_delimiter>
<row_before_delimiter>\N<field_delimiter_1>'Some string 3'<field_delimiter_2>[1, 2, null]<row_after_delimiter>
<result_after_delimiter>
$$)
┌─name─────┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ column_1 │ Nullable(Float64) │ │ │ │ │ │
│ column_2 │ Nullable(String) │ │ │ │ │ │
│ column_3 │ Array(Nullable(Int64)) │ │ │ │ │ │
└──────────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
Regexp
Templateと同様に、Regexpフォーマットでは、ClickHouseは最初に指定された正規表現に従って行からすべてのカラム値を抽出し、その後、指定されたエスケープルールに従って各値のデータ型を推測しようとします。
例
SET format_regexp = '^Line: value_1=(.+?), value_2=(.+?), value_3=(.+?)',
format_regexp_escaping_rule = 'CSV'
DESC format(Regexp, $$Line: value_1=42, value_2="Some string 1", value_3="[1, NULL, 3]"
Line: value_1=2, value_2="Some string 2", value_3="[4, 5, NULL]"$$)
┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(Int64) │ │ │ │ │ │
│ c2 │ Nullable(String) │ │ │ │ │ │
│ c3 │ Array(Nullable(Int64)) │ │ │ │ │ │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
Settings for text formats
これらの設定は、スキーマ推測のために読み取るデータの量を制御します。
より多くの行/バイトが読み取られるほど、スキーマ推測により多くの時間がかかりますが、型を正しく決定できる可能性が高くなります（特に、データに多くのnullが含まれている場合）。
デフォルト値:
25000 は input_format_max_rows_to_read_for_schema_inference 用。33554432 （32 Mb）は input_format_max_bytes_to_read_for_schema_inference 用。
column_names_for_schema_inference
明示的なカラム名を持たないフォーマットのスキーマ推測で使用するカラム名のリスト。指定された名前はデフォルトの c1,c2,c3,... の代わりに使用されます。フォーマット: column1,column2,column3,...。
例
DESC format(TSV, 'Hello, World! 42 [1, 2, 3]') settings column_names_for_schema_inference = 'str,int,arr'
┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ str │ Nullable(String) │ │ │ │ │ │
│ int │ Nullable(Int64) │ │ │ │ │ │
│ arr │ Array(Nullable(Int64)) │ │ │ │ │ │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
schema_inference_hints
自動的に決定されたタイプの代わりにスキーマ推測で使用するカラム名とタイプのリスト。フォーマット: 'column_name1 column_type1, column_name2 column_type2, ...'。
この設定は、自動的に決定できなかったカラムの型を指定するか、スキーマを最適化するために使用できます。
例
DESC format(JSONEachRow, '{"id" : 1, "age" : 25, "name" : "Josh", "status" : null, "hobbies" : ["football", "cooking"]}') SETTINGS schema_inference_hints = 'age LowCardinality(UInt8), status Nullable(String)', allow_suspicious_low_cardinality_types=1
┌─name────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Nullable(Int64) │ │ │ │ │ │
│ age │ LowCardinality(UInt8) │ │ │ │ │ │
│ name │ Nullable(String) │ │ │ │ │ │
│ status │ Nullable(String) │ │ │ │ │ │
│ hobbies │ Array(Nullable(String)) │ │ │ │ │ │
└─────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
schema_inference_make_columns_nullable
スキーマ推測の際に型を Nullable にするかどうかを制御します。
この設定が有効な場合、すべての推測された型は Nullable になります。無効の場合、推測された型は決して Nullable にならず、設定が auto の場合、推測された型は、サンプルの解析中にカラムに NULL が含まれている場合のみ Nullable になります。またはファイルメタデータがカラムのnull許可に関する情報を含む場合のみ適用されます。
デフォルトで有効。
例
SET schema_inference_make_columns_nullable = 1
DESC format(JSONEachRow, $$
{"id" : 1, "age" : 25, "name" : "Josh", "status" : null, "hobbies" : ["football", "cooking"]}
{"id" : 2, "age" : 19, "name" : "Alan", "status" : "married", "hobbies" : ["tennis", "art"]}
$$)
┌─name────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Nullable(Int64) │ │ │ │ │ │
│ age │ Nullable(Int64) │ │ │ │ │ │
│ name │ Nullable(String) │ │ │ │ │ │
│ status │ Nullable(String) │ │ │ │ │ │
│ hobbies │ Array(Nullable(String)) │ │ │ │ │ │
└─────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
SET schema_inference_make_columns_nullable = 'auto';
DESC format(JSONEachRow, $$
{"id" : 1, "age" : 25, "name" : "Josh", "status" : null, "hobbies" : ["football", "cooking"]}
{"id" : 2, "age" : 19, "name" : "Alan", "status" : "married", "hobbies" : ["tennis", "art"]}
$$)
┌─name────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Int64 │ │ │ │ │ │
│ age │ Int64 │ │ │ │ │ │
│ name │ String │ │ │ │ │ │
│ status │ Nullable(String) │ │ │ │ │ │
│ hobbies │ Array(String) │ │ │ │ │ │
└─────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
SET schema_inference_make_columns_nullable = 0;
DESC format(JSONEachRow, $$
{"id" : 1, "age" : 25, "name" : "Josh", "status" : null, "hobbies" : ["football", "cooking"]}
{"id" : 2, "age" : 19, "name" : "Alan", "status" : "married", "hobbies" : ["tennis", "art"]}
$$)
┌─name────┬─type──────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Int64 │ │ │ │ │ │
│ age │ Int64 │ │ │ │ │ │
│ name │ String │ │ │ │ │ │
│ status │ String │ │ │ │ │ │
│ hobbies │ Array(String) │ │ │ │ │ │
└─────────┴───────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
注記
この設定は、JSON データ型には適用されません。
有効な場合、ClickHouseはテキストフォーマットにおいてスキーマ推測の際に浮動小数点数の代わりに整数を推測しようとします。
サンプルデータのカラム内のすべての数値が整数であれば、結果の型は Int64 になります。少なくとも1つの数値が浮動小数点数である場合、結果の型は Float64 になります。
サンプルデータが整数のみを含み、少なくとも1つの整数が正で Int64 をオーバーフローする場合、ClickHouseは UInt64 を推測します。
デフォルトで有効。
例
SET input_format_try_infer_integers = 0
DESC format(JSONEachRow, $$
{"number" : 1}
{"number" : 2}
$$)
┌─name───┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ number │ Nullable(Float64) │ │ │ │ │ │
└────────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
SET input_format_try_infer_integers = 1
DESC format(JSONEachRow, $$
{"number" : 1}
{"number" : 2}
$$)
┌─name───┬─type────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ number │ Nullable(Int64) │ │ │ │ │ │
└────────┴─────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
DESC format(JSONEachRow, $$
{"number" : 1}
{"number" : 18446744073709551615}
$$)
┌─name───┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ number │ Nullable(UInt64) │ │ │ │ │ │
└────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
DESC format(JSONEachRow, $$
{"number" : 1}
{"number" : 2.2}
$$)
┌─name───┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ number │ Nullable(Float64) │ │ │ │ │ │
└────────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
有効な場合、ClickHouseはテキストフォーマットにおいて文字列フィールドから DateTime または DateTime64 の推測を行います。
サンプルデータのカラム内のすべてのフィールドが日時として正常に解析された場合、結果の型は DateTime か DateTime64(9) になります（もし fractional part を持つ日時があれば）。
もし少なくとも1つのフィールドが日時として解析されなかった場合、結果の型は String になります。
デフォルトで有効。
例
SET input_format_try_infer_datetimes = 0;
DESC format(JSONEachRow, $$
{"datetime" : "2021-01-01 00:00:00", "datetime64" : "2021-01-01 00:00:00.000"}
{"datetime" : "2022-01-01 00:00:00", "datetime64" : "2022-01-01 00:00:00.000"}
$$)
┌─name───────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ datetime │ Nullable(String) │ │ │ │ │ │
│ datetime64 │ Nullable(String) │ │ │ │ │ │
└────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
SET input_format_try_infer_datetimes = 1;
DESC format(JSONEachRow, $$
{"datetime" : "2021-01-01 00:00:00", "datetime64" : "2021-01-01 00:00:00.000"}
{"datetime" : "2022-01-01 00:00:00", "datetime64" : "2022-01-01 00:00:00.000"}
$$)
┌─name───────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ datetime │ Nullable(DateTime) │ │ │ │ │ │
│ datetime64 │ Nullable(DateTime64(9)) │ │ │ │ │ │
└────────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
DESC format(JSONEachRow, $$
{"datetime" : "2021-01-01 00:00:00", "datetime64" : "2021-01-01 00:00:00.000"}
{"datetime" : "unknown", "datetime64" : "unknown"}
$$)
┌─name───────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ datetime │ Nullable(String) │ │ │ │ │ │
│ datetime64 │ Nullable(String) │ │ │ │ │ │
└────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
有効な場合、ClickHouseは input_format_try_infer_datetimes が有効な場合に常に DateTime64(9) を推測します。たとえ日時の値がfractional partを含まない場合でも。
デフォルトで無効。
例
SET input_format_try_infer_datetimes = 1;
SET input_format_try_infer_datetimes_only_datetime64 = 1;
DESC format(JSONEachRow, $$
{"datetime" : "2021-01-01 00:00:00", "datetime64" : "2021-01-01 00:00:00.000"}
{"datetime" : "2022-01-01 00:00:00", "datetime64" : "2022-01-01 00:00:00.000"}
$$)
┌─name───────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ datetime │ Nullable(DateTime64(9)) │ │ │ │ │ │
│ datetime64 │ Nullable(DateTime64(9)) │ │ │ │ │ │
└────────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
注意: スキーマ推測中の日時の解析は設定 date_time_input_format に従います。
有効な場合、ClickHouseはテキストフォーマットにおいて文字列フィールドから Date の推測を行います。
サンプルデータのカラム内のすべてのフィールドが日付として正常に解析された場合、結果の型は Date になります。
少なくとも1つのフィールドが日付として解析されなかった場合、結果の型は String になります。
デフォルトで有効。
例
SET input_format_try_infer_datetimes = 0, input_format_try_infer_dates = 0
DESC format(JSONEachRow, $$
{"date" : "2021-01-01"}
{"date" : "2022-01-01"}
$$)
┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ date │ Nullable(String) │ │ │ │ │ │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
SET input_format_try_infer_dates = 1
DESC format(JSONEachRow, $$
{"date" : "2021-01-01"}
{"date" : "2022-01-01"}
$$)
┌─name─┬─type───────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ date │ Nullable(Date) │ │ │ │ │ │
└──────┴────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
DESC format(JSONEachRow, $$
{"date" : "2021-01-01"}
{"date" : "unknown"}
$$)
┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ date │ Nullable(String) │ │ │ │ │ │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
有効な場合、ClickHouseはテキストフォーマットにおいて指数形式の浮動小数点数の推測を試みます（JSON では指数形式の数字は常に推測されます）。
デフォルトで無効。
例
SET input_format_try_infer_exponent_floats = 1;
DESC format(CSV,
$$1.1E10
2.3e-12
42E00
$$)
┌─name─┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(Float64) │ │ │ │ │ │
└──────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
自己記述フォーマットは、データの構造に関する情報をデータ自体に含んでいます。これは、説明のあるヘッダー、バイナリタイプツリー、または何らかの種類のテーブルである可能性があります。
そのようなフォーマットのファイルからスキーマを自動的に推測するために、ClickHouseは型に関する情報を含むデータの一部を読み取り、それをClickHouseテーブルのスキーマに変換します。
ClickHouseは、-WithNamesAndTypesというサフィックスを持ついくつかのテキストフォーマットをサポートしています。このサフィックスは、データに実際のデータの前にカラム名とタイプを含む2つの追加行が含まれていることを意味します。
このようなフォーマットのスキーマ推論では、ClickHouseは最初の2行を読み取り、カラム名とタイプを抽出します。
例
DESC format(TSVWithNamesAndTypes,
$$num str arr
UInt8 String Array(UInt8)
42 Hello, World! [1,2,3]
$$)
┌─name─┬─type─────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ num │ UInt8 │ │ │ │ │ │
│ str │ String │ │ │ │ │ │
│ arr │ Array(UInt8) │ │ │ │ │ │
└──────┴──────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
いくつかのJSON入力フォーマット（JSON、JSONCompact、JSONColumnsWithMetadata）には、カラム名とタイプを含むメタデータが含まれています。
このようなフォーマットのスキーマ推論では、ClickHouseはこのメタデータを読み取ります。
例
DESC format(JSON, $$
{
"meta":
[
{
"name": "num",
"type": "UInt8"
},
{
"name": "str",
"type": "String"
},
{
"name": "arr",
"type": "Array(UInt8)"
}
],
"data":
[
{
"num": 42,
"str": "Hello, World",
"arr": [1,2,3]
}
],
"rows": 1,
"statistics":
{
"elapsed": 0.005723915,
"rows_read": 1,
"bytes_read": 1
}
}
$$)
┌─name─┬─type─────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ num │ UInt8 │ │ │ │ │ │
│ str │ String │ │ │ │ │ │
│ arr │ Array(UInt8) │ │ │ │ │ │
└──────┴──────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
Avro
Avroフォーマットでは、ClickHouseはデータからスキーマを読み取り、次のタイプマッチを使用してClickHouseスキーマに変換します：
* Avro logical types
他のAvroタイプはサポートされていません。
Parquet
Parquetフォーマットでは、ClickHouseはデータからスキーマを読み取り、次のタイプマッチを使用してClickHouseスキーマに変換します：
他のParquetタイプはサポートされていません。デフォルトでは、すべての推論されたタイプは Nullable 内にありますが、設定 schema_inference_make_columns_nullable を使用して変更することができます。
Arrow
Arrowフォーマットでは、ClickHouseはデータからスキーマを読み取り、次のタイプマッチを使用してClickHouseスキーマに変換します：
他のArrowタイプはサポートされていません。デフォルトでは、すべての推論されたタイプは Nullable 内にありますが、設定 schema_inference_make_columns_nullable を使用して変更することができます。
ORC
ORCフォーマットでは、ClickHouseはデータからスキーマを読み取り、次のタイプマッチを使用してClickHouseスキーマに変換します：
他のORCタイプはサポートされていません。デフォルトでは、すべての推論されたタイプは Nullable 内にありますが、設定 schema_inference_make_columns_nullable を使用して変更することができます。
Native
ネイティブフォーマットはClickHouse内で使用され、データ内にスキーマが含まれています。
スキーマ推論では、ClickHouseはデータからスキーマを読み取りますが、変換は行いません。
そのようなフォーマットでは、特定のスキーマ言語でデータを説明するスキーマが別のファイルに必要です。
そのようなフォーマットのファイルからスキーマを自動的に推論するために、ClickHouseは別のファイルから外部スキーマを読み取り、それをClickHouseテーブルスキーマに変換します。
Protobuf
Protobufフォーマットのスキーマ推論では、ClickHouseは次のタイプマッチを使用します：
CapnProto
CapnProtoフォーマットのスキーマ推論では、ClickHouseは次のタイプマッチを使用します：
そのようなフォーマットでは、各シリアライズされた値がその型（おそらく名前についても）に関する情報を含みますが、全体のテーブルに関する情報はありません。
そのようなフォーマットのスキーマ推論では、ClickHouseはデータを行ごとに読み取り（最大で input_format_max_rows_to_read_for_schema_inference 行または input_format_max_bytes_to_read_for_schema_inference バイト）、各値の型（おそらく名前）をデータから抽出し、その後それらの型をClickHouseの型に変換します。
MsgPack
MsgPackフォーマットでは、行間に区切りがありません。このフォーマットに対してスキーマ推論を使用するには、input_format_msgpack_number_of_columns 設定を使用してテーブルのカラム数を指定する必要があります。ClickHouseは次のタイプマッチを使用します：
MessagePackデータタイプ (INSERT) | ClickHouseデータタイプ |
|---|
int N, uint N, negative fixint, positive fixint | Int64 |
bool | UInt8 |
fixstr, str 8, str 16, str 32, bin 8, bin 16, bin 32 | String |
float 32 | Float32 |
float 64 | Float64 |
uint 16 | Date |
uint 32 | DateTime |
uint 64 | DateTime64 |
fixarray, array 16, array 32 | Array |
fixmap, map 16, map 32 | Map |
デフォルトでは、すべての推論されたタイプは Nullable 内にありますが、設定 schema_inference_make_columns_nullable を使用して変更することができます。
BSONEachRow
BSONEachRowでは、データの各行がBSONドキュメントとして表示されます。スキーマ推論では、ClickHouseはBSONドキュメントを1つずつ読み取り、データから値、名前、および型を抽出し、その後これらの型を次のタイプマッチを使用してClickHouseの型に変換します：
デフォルトでは、すべての推論されたタイプは Nullable 内にありますが、設定 schema_inference_make_columns_nullable を使用して変更することができます。
そのようなフォーマットのデータは常に同じスキーマを持ちます。
LineAsString
このフォーマットでは、ClickHouseはデータから行全体を String データタイプの単一カラムに読み込みます。このフォーマットの推論された型は常に String で、カラム名は line です。
例
DESC format(LineAsString, 'Hello\nworld!')
┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ line │ String │ │ │ │ │ │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
JSONAsString
このフォーマットでは、ClickHouseはデータから全体のJSONオブジェクトを String データタイプの単一カラムに読み込みます。このフォーマットの推論された型は常に String で、カラム名は json です。
例
DESC format(JSONAsString, '{"x" : 42, "y" : "Hello, World!"}')
┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ json │ String │ │ │ │ │ │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
JSONAsObject
このフォーマットでは、ClickHouseはデータから全体のJSONオブジェクトを Object('json') データタイプの単一カラムに読み込みます。このフォーマットの推論された型は常に String で、カラム名は json です。
注：このフォーマットは、allow_experimental_object_type が有効な場合にのみ機能します。
例
DESC format(JSONAsString, '{"x" : 42, "y" : "Hello, World!"}') SETTINGS allow_experimental_object_type=1
┌─name─┬─type───────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ json │ Object('json') │ │ │ │ │ │
└──────┴────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
Schema inference modes
データファイルのセットからのスキーマ推論は、default と union の2つの異なるモードで動作します。
モードは、設定 schema_inference_mode によって制御されます。
Default mode
デフォルトモードでは、ClickHouseはすべてのファイルが同じスキーマを持つと仮定し、成功するまでファイルを1つずつ読み取ることによってスキーマを推論しようとします。
例：
例えば、次の内容の3つのファイル data1.jsonl、data2.jsonl、data3.jsonl があるとします：
data1.jsonl:
{"field1" : 1, "field2" : null}
{"field1" : 2, "field2" : null}
{"field1" : 3, "field2" : null}
data2.jsonl:
{"field1" : 4, "field2" : "Data4"}
{"field1" : 5, "field2" : "Data5"}
{"field1" : 6, "field2" : "Data5"}
data3.jsonl:
{"field1" : 7, "field2" : "Data7", "field3" : [1, 2, 3]}
{"field1" : 8, "field2" : "Data8", "field3" : [4, 5, 6]}
{"field1" : 9, "field2" : "Data9", "field3" : [7, 8, 9]}
これらの3つのファイルに対してスキーマ推論を試みてみましょう：
:) DESCRIBE file('data{1,2,3}.jsonl') SETTINGS schema_inference_mode='default'
結果：
┌─name───┬─type─────────────┐
│ field1 │ Nullable(Int64) │
│ field2 │ Nullable(String) │
└────────┴──────────────────┘
見ての通り、ファイル data3.jsonl の field3 は存在していません。
これは、ClickHouseが最初に data1.jsonl からスキーマを推論しようとし、field2 にnullしかなかったため失敗し、次に data2.jsonl からスキーマを推論しようとして成功したため、data3.jsonlのデータが読み込まれなかったからです。
Union mode
ユニオンモードでは、ClickHouseはファイルが異なるスキーマを持つ可能性があると仮定し、すべてのファイルのスキーマを推論し、それらを共通のスキーマにユニオンします。
例えば、次の内容の3つのファイル data1.jsonl、data2.jsonl、data3.jsonl があるとしましょう：
data1.jsonl:
{"field1" : 1}
{"field1" : 2}
{"field1" : 3}
data2.jsonl:
{"field2" : "Data4"}
{"field2" : "Data5"}
{"field2" : "Data5"}
data3.jsonl:
{"field3" : [1, 2, 3]}
{"field3" : [4, 5, 6]}
{"field3" : [7, 8, 9]}
これらの3つのファイルに対してスキーマ推論を試みてみましょう：
:) DESCRIBE file('data{1,2,3}.jsonl') SETTINGS schema_inference_mode='union'
結果：
┌─name───┬─type───────────────────┐
│ field1 │ Nullable(Int64) │
│ field2 │ Nullable(String) │
│ field3 │ Array(Nullable(Int64)) │
└────────┴────────────────────────┘
見ての通り、すべてのファイルからの全フィールドが得られました。
注意：
- 一部のファイルは、結果スキーマからのいくつかのカラムを含まない可能性があるため、ユニオンモードは、JSONEachRow、Parquet、TSVWithNamesなどのカラムのサブセットを読み取ることをサポートするフォーマットにしか対応しておらず、CSV、TSV、JSONCompactEachRowなどの他のフォーマットでは機能しません。
- ClickHouseがファイルの1つからスキーマを推論できない場合、例外がスローされます。
- 多くのファイルがある場合、すべてのファイルからスキーマを読み取るのに時間がかかることがあります。
データフォーマットが指定されておらず、ファイル拡張子から決定できない場合、ClickHouseはその内容によってファイルフォーマットを検出しようとします。
例：
例えば、次の内容の data があります：
"a","b"
1,"Data1"
2,"Data2"
3,"Data3"
このファイルをフォーマットや構造を指定することなく調査し、クエリを実行することができます：
┌─name─┬─type─────────────┐
│ a │ Nullable(Int64) │
│ b │ Nullable(String) │
└──────┴──────────────────┘
:) select * from file(data);
┌─a─┬─b─────┐
│ 1 │ Data1 │
│ 2 │ Data2 │
│ 3 │ Data3 │
└───┴───────┘
注記
ClickHouseは一部のフォーマットのサブセットのみを検出でき、検出には時間がかかるため、常にフォーマットを明示的に指定することが望ましいです。
