入力データからの自動スキーマ推論

ClickHouseは、サポートされているほぼすべての入力フォーマットで、入力データの構造を自動的に判定できます。このドキュメントでは、どのような場合にスキーマ推論が使用されるのか、各種入力フォーマットでどのように機能するのか、またそれを制御する設定について説明します。

使用

スキーマ推論は、ClickHouse が特定のデータフォーマットのデータを読み込む必要があり、その構造が不明な場合に使用されます。

テーブル関数 file, s3, url, hdfs, azureBlobStorage.

これらのテーブル関数には、入力データの構造を表す省略可能な引数 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)) │              │                    │         │                  │                │
└─────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

テーブルエンジン File, S3, URL, HDFS, azureBlobStorage

CREATE TABLE クエリでカラムの一覧を指定しない場合、テーブルの構造はデータから自動的に推論されます。例: ファイル hobbies.jsonl を使用します。このファイルのデータを使って、File エンジンのテーブルを作成できます:

CREATE TABLE hobbies ENGINE=File(JSONEachRow, 'hobbies.jsonl')

Ok.

SELECT * FROM hobbies

┌─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']           │
└────┴─────┴────────┴──────────────────────────────────┘

DESCRIBE TABLE hobbies

┌─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'

  25    Josh    ['football','cooking','music']
  19    Alan    ['tennis','art']
  32    Lana    ['fitness','reading','shopping']
  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 テーブル関数で URL 経由でアクセスされる一部のファイルには、最終更新時刻の情報が含まれていない場合があります。このケースのために、特別な設定 schema_inference_cache_require_modification_time_for_url があります。この設定を無効にすると、そのようなファイルでも最終更新時刻がなくてもキャッシュ内のスキーマを使用できます。現在キャッシュ内にあるすべてのスキーマを確認できるシステムテーブル schema_inference_cache と、SYSTEM CLEAR SCHEMA CACHE [FOR File/S3/URL/HDFS] というシステムクエリもあり、これによりすべてのソース、または特定のソースのスキーマキャッシュをクリアできます。例: S3 上のサンプル dataset github-2022.ndjson.gz の構造を推論し、スキーマ推論キャッシュがどのように動作するかを見てみましょう:

DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/github/github-2022.ndjson.gz')

┌─name───────┬─type─────────────────────────────────────────┐
│ type       │ Nullable(String)                             │
│ actor      │ Tuple(                                      ↴│
│            │↳    avatar_url Nullable(String),            ↴│
│            │↳    display_login Nullable(String),         ↴│
│            │↳    id Nullable(Int64),                     ↴│
│            │↳    login Nullable(String),                 ↴│
│            │↳    url Nullable(String))                    │
│ repo       │ Tuple(                                      ↴│
│            │↳    id Nullable(Int64),                     ↴│
│            │↳    name Nullable(String),                  ↴│
│            │↳    url Nullable(String))                    │
│ created_at │ Nullable(String)                             │
│ payload    │ Tuple(                                      ↴│
│            │↳    action Nullable(String),                ↴│
│            │↳    distinct_size Nullable(Int64),          ↴│
│            │↳    pull_request Tuple(                     ↴│
│            │↳        author_association Nullable(String),↴│
│            │↳        base Tuple(                         ↴│
│            │↳            ref Nullable(String),           ↴│
│            │↳            sha Nullable(String)),          ↴│
│            │↳        head Tuple(                         ↴│
│            │↳            ref Nullable(String),           ↴│
│            │↳            sha Nullable(String)),          ↴│
│            │↳        number Nullable(Int64),             ↴│
│            │↳        state Nullable(String),             ↴│
│            │↳        title Nullable(String),             ↴│
│            │↳        updated_at Nullable(String),        ↴│
│            │↳        user Tuple(                         ↴│
│            │↳            login Nullable(String))),       ↴│
│            │↳    ref Nullable(String),                   ↴│
│            │↳    ref_type Nullable(String),              ↴│
│            │↳    size Nullable(Int64))                    │
└────────────┴──────────────────────────────────────────────┘
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')

┌─name───────┬─type─────────────────────────────────────────┐
│ type       │ Nullable(String)                             │
│ actor      │ Tuple(                                      ↴│
│            │↳    avatar_url Nullable(String),            ↴│
│            │↳    display_login Nullable(String),         ↴│
│            │↳    id Nullable(Int64),                     ↴│
│            │↳    login Nullable(String),                 ↴│
│            │↳    url Nullable(String))                    │
│ repo       │ Tuple(                                      ↴│
│            │↳    id Nullable(Int64),                     ↴│
│            │↳    name Nullable(String),                  ↴│
│            │↳    url Nullable(String))                    │
│ created_at │ Nullable(String)                             │
│ payload    │ Tuple(                                      ↴│
│            │↳    action Nullable(String),                ↴│
│            │↳    distinct_size Nullable(Int64),          ↴│
│            │↳    pull_request Tuple(                     ↴│
│            │↳        author_association Nullable(String),↴│
│            │↳        base Tuple(                         ↴│
│            │↳            ref Nullable(String),           ↴│
│            │↳            sha Nullable(String)),          ↴│
│            │↳        head Tuple(                         ↴│
│            │↳            ref Nullable(String),           ↴│
│            │↳            sha Nullable(String)),          ↴│
│            │↳        number Nullable(Int64),             ↴│
│            │↳        state Nullable(String),             ↴│
│            │↳        title Nullable(String),             ↴│
│            │↳        updated_at Nullable(String),        ↴│
│            │↳        user Tuple(                         ↴│
│            │↳            login Nullable(String))),       ↴│
│            │↳    ref Nullable(String),                   ↴│
│            │↳    ref_type Nullable(String),              ↴│
│            │↳    size Nullable(Int64))                    │
└────────────┴──────────────────────────────────────────────┘

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_try_infer_named_tuples_from_objects=0, 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

ご覧のとおり、推論されるスキーマに影響する可能性のある設定が変更されたため、同じファイルに対しては cache 内のスキーマは使用されませんでした。 system.schema_inference_cache テーブルの内容を確認してみましょう。

SELECT schema, format, source FROM system.schema_inference_cache WHERE storage='S3'

┌─schema──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─format─┬─source───────────────────────────────────────────────────────────────────────────────────────────────────┐
│ type Nullable(String), actor Tuple(avatar_url Nullable(String), display_login Nullable(String), id Nullable(Int64), login Nullable(String), url Nullable(String)), repo Tuple(id Nullable(Int64), name Nullable(String), url Nullable(String)), created_at Nullable(String), payload Tuple(action Nullable(String), distinct_size Nullable(Int64), pull_request Tuple(author_association Nullable(String), base Tuple(ref Nullable(String), sha Nullable(String)), head Tuple(ref Nullable(String), sha Nullable(String)), number Nullable(Int64), state Nullable(String), title Nullable(String), updated_at Nullable(String), user Tuple(login Nullable(String))), ref Nullable(String), ref_type Nullable(String), size Nullable(Int64)) │ 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 つの異なるスキーマがあります。システムクエリを使って、スキーマ cache をクリアできます。

SYSTEM CLEAR SCHEMA CACHE FOR S3

Ok.

SELECT count() FROM system.schema_inference_cache WHERE storage='S3'

┌─count()─┐
│       0 │
└─────────┘

テキストフォーマット

テキストフォーマットでは、ClickHouse はデータを1行ずつ読み取り、フォーマットに従ってカラムの値を抽出した後、再帰的なパーサーやヒューリスティクスを使って各値の型を判定します。スキーマ推論でデータから読み取る最大行数と最大バイト数は、設定 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 フォーマット

JSON フォーマットでは、ClickHouse は JSON 仕様に従って値を解析し、最も適切なデータ型を見つけようとします。ここでは、その仕組み、推論できる型、およびJSON フォーマットで使用できる具体的な設定について説明します。例以降の例では、format テーブル関数を使用します。整数、浮動小数点数、Bool、String:

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)  │              │                    │         │                  │                │
└────────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

日付、DateTime 型:

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)) │              │                    │         │                  │                │
└────────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Arrays:

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))) │              │                    │         │                  │                │
└───────────────┴───────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Arrayにnullが含まれている場合、ClickHouseは他のArray要素の型を使用します：

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_infer_array_of_dynamic_from_array_of_different_types が有効な場合 (デフォルトで有効) 、その型は Array(Dynamic) になります：

SET input_format_json_infer_array_of_dynamic_from_array_of_different_types=1;
DESC format(JSONEachRow, '{"arr" : [42, "hello", [1, 2, 3]]}');

┌─name─┬─type───────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ arr  │ Array(Dynamic) │              │                    │         │                  │                │
└──────┴────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

名前付きタプル: 設定 input_format_json_try_infer_named_tuples_from_objects が有効な場合、スキーマ推論時に ClickHouse は JSON オブジェクトから名前付き Tuple を推論します。生成される名前付き Tuple には、サンプルデータ内の対応するすべての 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))) │              │                    │         │                  │                │
└──────┴────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

名前のない Tuple: 設定 input_format_json_infer_array_of_dynamic_from_array_of_different_types が無効な場合、JSON フォーマットでは、要素の型が異なる Array は名前のない Tuple として扱われます。

SET input_format_json_infer_array_of_dynamic_from_array_of_different_types = 0;
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または空の場合は、他の行にある対応する値の型を使用します。

SET input_format_json_infer_array_of_dynamic_from_array_of_different_types=0;
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))) │              │                    │         │                  │                │
└───────┴──────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Map型: JSON では、値の型がすべて同じオブジェクトを Map 型として読み取ることができます。注: これは、設定 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)) │              │                    │         │                  │                │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

ネストされた複合型:

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))) │              │                    │         │                  │                │
└───────┴──────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

データに null のみ、空のオブジェクト、または空の配列しか含まれていないために、ClickHouse が一部のキーの型を判定できない場合、設定 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設定

input_format_json_try_infer_numbers_from_strings

この設定を有効にすると、文字列の値から数値を推論できるようになります。この設定はデフォルトで無効です。例:

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) │              │                    │         │                  │                │
└───────┴─────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

input_format_json_try_infer_named_tuples_from_objects

この設定を有効にすると、JSON オブジェクトから名前付き Tuple を推論できるようになります。生成される名前付き Tuple には、サンプルデータ内の対応するすべての JSON オブジェクトに含まれる全要素が含まれます。これは、JSON データがスパースではなく、データのサンプルに取り得るすべてのオブジェクトキーが含まれている場合に有用です。この設定はデフォルトで有効になっています。例

Query

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}}}')

Response

┌─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))) │              │                    │         │                  │                │
└──────┴────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Query

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"}]}')

Response

┌─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_try_infer_named_tuples_from_objects が有効な場合、JSON オブジェクトから名前付き Tuple を推論する際に、あいまいなパスで例外を発生させる代わりに String 型を使用できるようになります。これにより、あいまいなパスが存在する場合でも、JSON オブジェクトを名前付き Tuple として読み取ることができます。デフォルトでは無効です。例設定が無効な場合:

Query

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"}}}');

Response

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)

有効時:

Query

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"}}}');

Response

┌─name─┬─type──────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ obj  │ Tuple(a Nullable(String))     │              │                    │         │                  │                │
└──────┴───────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
┌─obj─────────────────┐
│ ('42')              │
│ ('{"b" : "Hello"}') │
└─────────────────────┘

input_format_json_read_objects_as_strings

この設定を有効にすると、ネストされた 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) │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

input_format_json_read_numbers_as_strings

この設定を有効にすると、数値を文字列として読み込めるようになります。この設定はデフォルトで有効になっています。例

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) │              │                    │         │                  │                │
└───────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

input_format_json_read_bools_as_numbers

この設定を有効にすると、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) │              │                    │         │                  │                │
└───────┴─────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

input_format_json_read_bools_as_strings

この設定を有効にすると、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) │              │                    │         │                  │                │
└───────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

input_format_json_read_arrays_as_strings

この設定を有効にすると、JSON array の値を文字列として読み込めるようになります。この設定はデフォルトで有効です。例

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]                                   │
└───────────────────────┴─────────────────┴───────────────────────────────────────────┘

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

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" : []}}');

Response

┌─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 が一部のパーサーやヒューリスティクスを使って複雑な型を判定しないようにしたい場合は、設定 input_format_csv_use_best_effort_in_schema_inference を無効にできます。そうすると、ClickHouse はすべてのカラムを String として扱います。設定 input_format_csv_detect_header が有効な場合、ClickHouse はスキーマ推論時に、カラム名 (場合によっては型も含む) を持つヘッダーを検出しようとします。この設定はデフォルトで有効です。例: 整数、浮動小数点数、Bool、String:

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) │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

日付、DateTime 型：

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)) │              │                    │         │                  │                │
└──────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Array:

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)) │              │                    │         │                  │                │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Map:

DESC format(CSV, $$"{'key1' : 42, 'key2' : 24}"$$)

┌─name─┬─type─────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Map(String, Nullable(Int64)) │              │                    │         │                  │                │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

入れ子になった Array と Map:

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))))) │              │                    │         │                  │                │
└──────┴───────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

データに null しか含まれていないため、ClickHouse が引用符で囲まれた値の型を判定できない場合、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 type以外である場合にのみ、ヘッダーを検出できることに注意してください。すべてのカラムがString typeである場合、ヘッダーは検出されません。

SELECT * FROM format(CSV,
$$"first_column","second_column"
"Hello","World"
"World","Hello"
$$)

┌─c1───────────┬─c2────────────┐
│ first_column │ second_column │
│ Hello        │ World         │
│ World        │ Hello         │
└──────────────┴───────────────┘

CSV の設定

input_format_csv_try_infer_numbers_from_strings

この設定を有効にすると、文字列の値から数値を推定できるようになります。この設定はデフォルトで無効です。例:

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 はすべてのカラムを String として扱います。設定 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)  │              │                    │         │                  │                │
└────────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Date、DateTime 型：

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)) │              │                    │         │                  │                │
└──────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Array:

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)) │              │                    │         │                  │                │
└──────┴──────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Map:

DESC format(TSV, $${'key1' : 42, 'key2' : 24}$$)

┌─name─┬─type─────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Map(String, Nullable(Int64)) │              │                    │         │                  │                │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

ネストされたArray、Tuple、Map:

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))))) │              │                    │         │                  │                │
└──────┴─────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

データに NULL しか含まれていないため ClickHouse が型を判定できない場合、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 フォーマットでは、ClickHouse は行からカラムの値を抽出し、リテラルのパース時と同様の再帰的なパーサーを使用してパースします。例：整数、浮動小数点数、Bool、String:

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)  │              │                    │         │                  │                │
└──────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

日付、DateTime 型:

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)) │              │                    │         │                  │                │
└──────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Arrays:

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))) │              │                    │         │                  │                │
└──────┴───────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

ArrayにNULLが含まれる場合、ClickHouseは他のArray要素の型を使用します：

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)) │              │                    │         │                  │                │
└──────┴──────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Map型:

DESC format(Values, $$({'key1' : 42, 'key2' : 24})$$)

┌─name─┬─type─────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Map(String, Nullable(Int64)) │              │                    │         │                  │                │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

ネストされたArray、Tuple、Map：

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))))) │              │                    │         │                  │                │
└──────┴─────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

データに NULL しか含まれていないため ClickHouse が型を判定できない場合は、例外がスローされます:

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)) │              │                    │         │                  │                │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

テキストフォーマットの設定

input_format_max_rows_to_read_for_schema_inference/input_format_max_bytes_to_read_for_schema_inference

これらの設定は、スキーマ推論時に読み取るデータ量を制御します。読み取る行数/バイト数が多いほど、スキーマ推論にかかる時間は長くなりますが、型を正しく判定できる可能性も高くなります (特に、データに NULL が多く含まれる場合) 。デフォルト値:

input_format_max_rows_to_read_for_schema_inference は 25000
input_format_max_bytes_to_read_for_schema_inference は 33554432 (32 Mb)

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 $

null許容性に関する情報を持たないフォーマットのスキーマ推論において、推論された型をNullableにするかどうかを制御します。設定可能な値:

0 - 推論される型が Nullable になることはありません。
1 - 推論される型はすべて Nullable になります,
2 または ‘auto’ - テキストフォーマットでは、スキーマ推論時に解析されるサンプル内のカラムに NULL が含まれている場合にのみ、推論される型は Nullable になります。厳密に型付けされたフォーマット (Parquet、ORC、Arrow) では、NULL 許容性の情報はファイルのメタデータから取得されます。
3 - テキストフォーマットでは Nullable を使用し、厳密な型情報を持つフォーマットではファイルメタデータを使用します。

デフォルト: 3。例

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) │              │                    │         │                  │                │
└─────────┴───────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

input_format_try_infer_integers

この設定は 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) │              │                    │         │                  │                │
└────────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

input_format_try_infer_datetimes

有効な場合、ClickHouse はテキストフォーマットのスキーマ推論で、文字列フィールドから DateTime または DateTime64 型の推論を試みます。サンプルデータ内のあるカラムについて、すべてのフィールドが日時として正常に解析された場合、結果の型は DateTime または DateTime64(9) になります (いずれかの日時に小数部が含まれている場合) 。 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) │              │                    │         │                  │                │
└────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

input_format_try_infer_datetimes_only_datetime64

有効な場合、input_format_try_infer_datetimes が有効になっていれば、日時の値に小数部が含まれていなくても、ClickHouse は常に DateTime64(9) を推論します。デフォルトでは無効です。例

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 に従います

input_format_try_infer_dates

有効にすると、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) │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

input_format_try_infer_exponent_floats

有効にすると、ClickHouse はテキストフォーマットで指数表記の数値を浮動小数点数として推論しようとします (JSON を除く。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 テーブルのスキーマに変換します。

-WithNamesAndTypes 接尾辞付きフォーマット

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 入力フォーマット (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 データ型	ClickHouse データ型
`boolean`	Bool
`int`	Int32
`int (date)` *	Date32
`long`	Int64
`float`	Float32
`double`	Float64
`bytes`, `string`	String
`fixed`	FixedString(N)
`enum`	Enum
`array(T)`	Array(T)
`union(null, T)`, `union(T, null)`	Nullable(T)
`null`	Nullable(Nothing)
`string (uuid)` *	UUID
`binary (decimal)` *	Decimal(P, S)

Avro 論理型

その他の Avro 型はサポートされていません。

Parquet

Parquet フォーマットでは、ClickHouse はデータからスキーマを読み取り、以下の型対応に基づいて ClickHouse のスキーマに変換します。

Parquet データ型	ClickHouse データ型
`BOOL`	Bool
`UINT8`	UInt8
`INT8`	Int8
`UINT16`	UInt16
`INT16`	Int16
`UINT32`	UInt32
`INT32`	Int32
`UINT64`	UInt64
`INT64`	Int64
`FLOAT`	Float32
`DOUBLE`	Float64
`DATE`	Date32
`TIME (ms)`	DateTime
`TIMESTAMP`, `TIME (us, ns)`	DateTime64
`STRING`, `BINARY`	String
`DECIMAL`	Decimal
`LIST`	Array
`STRUCT`	Tuple
`MAP`	Map

その他の Parquet 型はサポートされていません。

Arrow

Arrow フォーマットでは、ClickHouse はデータからスキーマを読み取り、次の型対応に基づいて ClickHouse のスキーマへ変換します。

Arrow データ型	ClickHouse データ型
`BOOL`	Bool
`UINT8`	UInt8
`INT8`	Int8
`UINT16`	UInt16
`INT16`	Int16
`UINT32`	UInt32
`INT32`	Int32
`UINT64`	UInt64
`INT64`	Int64
`FLOAT`, `HALF_FLOAT`	Float32
`DOUBLE`	Float64
`DATE32`	Date32
`DATE64`	DateTime
`TIMESTAMP`, `TIME32`, `TIME64`	DateTime64
`STRING`, `BINARY`	String
`DECIMAL128`, `DECIMAL256`	Decimal
`LIST`	Array
`STRUCT`	Tuple
`MAP`	Map

そのほかの Arrow 型はサポートされていません。

ORC

ORCフォーマットでは、ClickHouseはデータからスキーマを読み取り、次の型対応に従ってClickHouseのスキーマへ変換します。

ORCデータ型	ClickHouseデータ型
`Boolean`	Bool
`Tinyint`	Int8
`Smallint`	Int16
`Int`	Int32
`Bigint`	Int64
`Float`	Float32
`Double`	Float64
`Date`	Date32
`Timestamp`	DateTime64
`String`, `Char`, `Varchar`,`BINARY`	String
`Decimal`	Decimal
`List`	Array
`Struct`	Tuple
`Map`	Map

そのほかのORC型はサポートされていません。

Native

Native フォーマットは ClickHouse 内部で使用され、データ自体にスキーマが含まれています。スキーマ推論では、ClickHouse は変換を行わずにデータからスキーマを読み取ります。

外部スキーマを使用するフォーマット

この種のフォーマットでは、データを記述するスキーマを、特定のスキーマ言語で別ファイルに定義する必要があります。この種のフォーマットのファイルからスキーマ推論を行うため、ClickHouse は別ファイルから外部スキーマを読み取り、ClickHouse のテーブルスキーマに変換します。

Protobuf

Protobuf フォーマットのスキーマ推論では、ClickHouse は以下の型対応を使用します。

Protobuf data type	ClickHouse data type
`bool`	UInt8
`float`	Float32
`double`	Float64
`int32`, `sint32`, `sfixed32`	Int32
`int64`, `sint64`, `sfixed64`	Int64
`uint32`, `fixed32`	UInt32
`uint64`, `fixed64`	UInt64
`string`, `bytes`	String
`enum`	Enum
`repeated T`	Array(T)
`message`, `group`	Tuple

CapnProto

CapnProto フォーマットのスキーマ推論では、ClickHouse は以下のように型対応します。

CapnProto データ型	ClickHouse データ型
`Bool`	UInt8
`Int8`	Int8
`UInt8`	UInt8
`Int16`	Int16
`UInt16`	UInt16
`Int32`	Int32
`UInt32`	UInt32
`Int64`	Int64
`UInt64`	UInt64
`Float32`	Float32
`Float64`	Float64
`Text`, `Data`	String
`enum`	Enum
`List`	Array
`struct`	Tuple
`union(T, Void)`, `union(Void, T)`	Nullable(T)

厳密に型付けされたバイナリ形式

このようなフォーマットでは、シリアル化された各値にその型 (場合によっては名前も) に関する情報は含まれますが、テーブル全体に関する情報は含まれません。このようなフォーマットのスキーマ推論では、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 data type (`INSERT`)	ClickHouse data type
`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 の型に変換します。

BSON Type	ClickHouse type
`\x08` boolean	Bool
`\x10` int32	Int32
`\x12` int64	Int64
`\x01` double	Float64
`\x09` datetime	DateTime64
`\x05` binary with`\x00` binary subtype, `\x02` string, `\x0E` symbol, `\x0D` JavaScript code	String
`\x07` ObjectId,	FixedString(12)
`\x05` binary with `\x04` uuid subtype, size = 16	UUID
`\x04` array	Array/Tuple (ネストされた型が異なる場合)
`\x03` document	Named Tuple/Map (String キーを使用する場合)

デフォルトでは、推論されたすべての型は Nullable でラップされますが、設定 schema_inference_make_columns_nullable を使って変更できます。

固定スキーマのフォーマット

このようなフォーマットでは、データは常に同じスキーマになります。

LineAsString

このフォーマットでは、ClickHouse はデータの1行全体を 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 データ型の 1 つのカラムに読み込みます。このフォーマットで推論される型は常に 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オブジェクト全体を、JSON データ型の単一のカラムに読み込みます。このフォーマットで推論される型は常に JSON で、カラム名は json です。例

DESC format(JSONAsObject, '{"x" : 42, "y" : "Hello, World!"}');

┌─name─┬─type─┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ json │ JSON │              │                    │         │                  │                │
└──────┴──────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

スキーマ推論モード

データファイルの集合に対するスキーマ推論は、default と union の 2 つの異なるモードで動作します。このモードは、設定 schema_inference_mode で制御されます。

デフォルトモード

デフォルトモードでは、ClickHouse はすべてのファイルが同じスキーマを持つとみなし、スキーマを推定できるまでファイルを 1 つずつ読み取ります。例: data1.jsonl、data2.jsonl、data3.jsonl の 3 つのファイルがあり、内容は次のとおりだとします。 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つのファイルに対してスキーマ推論を試してみましょう：

Query

:) DESCRIBE file('data{1,2,3}.jsonl') SETTINGS schema_inference_mode='default'

Response

┌─name───┬─type─────────────┐
│ field1 │ Nullable(Int64)  │
│ field2 │ Nullable(String) │
└────────┴──────────────────┘

ご覧のとおり、ファイル data3.jsonl の field3 は含まれていません。これは、ClickHouse がまずファイル data1.jsonl からスキーマを推論しようとしたものの、フィールド field2 がすべて NULL だったために失敗し、その後 data2.jsonl からのスキーマ推論には成功したため、ファイル data3.jsonl のデータは読み込まれなかったためです。

ユニオンモード

ユニオンモードでは、ClickHouse はファイルごとに異なるスキーマを持つ可能性があるとみなし、すべてのファイルのスキーマを推論したうえで、それらを 1 つの共通スキーマにユニオンします。次の内容を持つ 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つのファイルに対して、スキーマ推論を試してみましょう：

Query

:) DESCRIBE file('data{1,2,3}.jsonl') SETTINGS schema_inference_mode='union'

Response

┌─name───┬─type───────────────────┐
│ field1 │ Nullable(Int64)        │
│ field2 │ Nullable(String)       │
│ field3 │ Array(Nullable(Int64)) │
└────────┴────────────────────────┘

ご覧のとおり、すべてのファイルのすべてのフィールドが含まれています。注:

一部のファイルには、推論後のスキーマに含まれるカラムの一部が存在しない場合があるため、ユニオンモードはカラムの部分集合の読み取りをサポートするフォーマット (JSONEachRow、Parquet、TSVWithNames など) でのみサポートされます。その他のフォーマット (CSV、TSV、JSONCompactEachRow など) では動作しません。
ClickHouse がいずれかのファイルからスキーマを推論できない場合は、例外がスローされます。
ファイル数が多い場合、それらすべてからスキーマを読み取るのにかなり時間がかかることがあります。

フォーマットの自動検出

データのフォーマットが指定されておらず、ファイル拡張子からも判断できない場合、ClickHouse は内容に基づいてファイルのフォーマットを自動検出しようとします。例: 次のような内容の data があるとします。

"a","b"
1,"Data1"
2,"Data2"
3,"Data3"

フォーマットや構造を指定しなくても、このファイルを確認してクエリを実行できます。

:) desc file(data);

┌─name─┬─type─────────────┐
│ a    │ Nullable(Int64)  │
│ b    │ Nullable(String) │
└──────┴──────────────────┘

:) select * from file(data);

┌─a─┬─b─────┐
│ 1 │ Data1 │
│ 2 │ Data2 │
│ 3 │ Data3 │
└───┴───────┘

ClickHouseが検出できるのは一部のフォーマットだけで、この検出には多少時間もかかるため、フォーマットは常に明示的に指定することをおすすめします。

​使用

​テーブル関数 file, s3, url, hdfs, azureBlobStorage.

​テーブルエンジン File, S3, URL, HDFS, azureBlobStorage

​clickhouse-local

​挿入先テーブルの構造を使用する

​スキーマ推論キャッシュ

​テキストフォーマット

​JSON フォーマット

​JSON設定

input_format_json_try_infer_numbers_from_strings

input_format_json_try_infer_named_tuples_from_objects

input_format_json_use_string_type_for_ambiguous_paths_in_named_tuples_inference_from_objects

input_format_json_read_objects_as_strings

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

​CSV

​CSV の設定

input_format_csv_try_infer_numbers_from_strings

​TSV/TSKV

​値

​CustomSeparated

​Template

​Regexp

​テキストフォーマットの設定

​input_format_max_rows_to_read_for_schema_inference/input_format_max_bytes_to_read_for_schema_inference

​column_names_for_schema_inference

​schema_inference_hints

​schema_inference_make_columns_nullable $

​input_format_try_infer_integers

​input_format_try_infer_datetimes

​input_format_try_infer_datetimes_only_datetime64

​input_format_try_infer_dates

​input_format_try_infer_exponent_floats

​自己記述フォーマット

​-WithNamesAndTypes 接尾辞付きフォーマット

​メタデータを含む JSON フォーマット

​Avro

​Parquet

​Arrow

​ORC

​Native

​外部スキーマを使用するフォーマット

​Protobuf

​CapnProto

​厳密に型付けされたバイナリ形式

​MsgPack

​BSONEachRow

​固定スキーマのフォーマット

​LineAsString

​JSONAsString

​JSONAsObject

​スキーマ推論モード

​デフォルトモード

​ユニオンモード

​フォーマットの自動検出

使用

テーブル関数 file, s3, url, hdfs, azureBlobStorage.

テーブルエンジン File, S3, URL, HDFS, azureBlobStorage

clickhouse-local

挿入先テーブルの構造を使用する

スキーマ推論キャッシュ

テキストフォーマット

JSON フォーマット

JSON設定

CSV

CSV の設定

TSV/TSKV

値

CustomSeparated

Template

Regexp

テキストフォーマットの設定

input_format_max_rows_to_read_for_schema_inference/input_format_max_bytes_to_read_for_schema_inference

column_names_for_schema_inference

schema_inference_hints

schema_inference_make_columns_nullable $

input_format_try_infer_integers

input_format_try_infer_datetimes

input_format_try_infer_datetimes_only_datetime64

input_format_try_infer_dates

input_format_try_infer_exponent_floats

自己記述フォーマット

-WithNamesAndTypes 接尾辞付きフォーマット

メタデータを含む JSON フォーマット

Avro

Parquet

Arrow

ORC

Native

外部スキーマを使用するフォーマット

Protobuf

CapnProto

厳密に型付けされたバイナリ形式

MsgPack

BSONEachRow

固定スキーマのフォーマット

LineAsString

JSONAsString

JSONAsObject

スキーマ推論モード

デフォルトモード

ユニオンモード

フォーマットの自動検出