JSON データ型
JSON
型は、JavaScript Object Notation (JSON) ドキュメントを単一のカラムに格納します。
JSON
型を使用する場合、及びこのページの例については、次のコマンドを使用してください:
しかし、ClickHouse Cloudを使用している場合、JSON
型の使用を有効にするために最初にサポートに連絡する必要があります。
ClickHouse Open-Sourceでは、JSONデータ型はバージョン25.3でプロダクション準備完了とマークされています。以前のバージョンでは、この型を本番環境で使用することは推奨されません。
JSON
型のカラムを宣言するには、次の構文を使用できます:
ここで、上記の構文のパラメータは次のように定義されています:
パラメータ | 説明 | デフォルト値 |
---|---|---|
max_dynamic_paths | 1つのデータブロックで個別に保存できるパスの数を示すオプショナルパラメータ。これは、(例えば、MergeTreeテーブルの単一データパートで)個別に保存されます。 この制限を超えると、他のすべてのパスは1つの構造体にまとめて保存されます。 | 1024 |
max_dynamic_types | 1 から255 の間のオプショナルパラメータで、単一パスのカラムにDynamic 型として保持されることのできる異なるデータ型の数を示します。この制限を超えると、すべての新しい型は String 型に変換されます。 | 32 |
some.path TypeName | JSON内の特定のパスのためのオプショナル型ヒント。これらのパスは常に指定された型でサブカラムとして保存されます。 | |
SKIP path.to.skip | JSONパース中にスキップすべき特定のパスに対するオプショナルヒント。こうしたパスはJSONカラムに保存されません。指定されたパスがネストされたJSONオブジェクトである場合、ネストされたオブジェクト全体がスキップされます。 | |
SKIP REGEXP 'path_regexp' | JSONパース中にパスをスキップするために使用される正規表現を持つオプショナルヒント。この正規表現に一致するすべてのパスはJSONカラムに保存されません。 |
JSONの作成
このセクションでは、JSON
を作成するためのさまざまな方法を見ていきます。
テーブルカラム定義におけるJSON
の使用
::JSON
を使用したCAST
特別な構文::JSON
を使用してさまざまな型をキャストすることが可能です。
String
からJSON
へのCAST
Tuple
からJSON
へのCAST
Map
からJSON
へのCAST
非推奨のObject('json')
からJSON
へのCAST
JSONのパスは平坦化されて保存されます。これは、a.b.c
のようなパスからJSONオブジェクトがフォーマットされる場合、そのオブジェクトが{ "a.b.c" : ... }
として構築されるべきか、{ "a" : {"b" : {"c" : ... }}}
として構築されるべきかを知ることが不可能であることを意味します。私たちの実装は常に後者を想定します。
例えば:
は次のように返します:
そして決して:
JSONパスをサブカラムとして読む
JSON
型は、すべてのパスを別々のサブカラムとして読むことをサポートしています。
要求されたパスの型がJSON型宣言で指定されていない場合、パスのサブカラムは常に型Dynamicを持ちます。
例えば:
要求されたパスがデータ内に見つからなかった場合、それはNULL
値で埋め込まれます:
返されたサブカラムのデータ型を確認しましょう:
ご覧のように、a.b
については、私たちがJSON型宣言で指定した通りにUInt32
型です。
他のすべてのサブカラムの型はDynamic
です。
特別な構文json.some.path.:TypeName
を使用して、Dynamic
型のサブカラムを読むことも可能です:
Dynamic
サブカラムは任意のデータ型にキャストできます。この場合、Dynamic
内の内部型が要求された型にキャストできない場合は例外がスローされます:
JSONサブオブジェクトをサブカラムとして読む
JSON
型は、特別な構文json.^some.path
を使用して、型JSON
としてネストされたオブジェクトをサブカラムとして読むことをサポートしています:
サブオブジェクトをサブカラムとして読むことは、効率が悪い可能性があります。これは、JSONデータ全体をほぼフルスキャンする必要があるからです。
パスの型推論
JSON
の解析中、ClickHouseは各JSONパスに最も適切なデータ型を検出しようとします。
これは、入力データからの自動スキーマ推論と似たように機能し、次の設定で制御されます:
- input_format_try_infer_dates
- input_format_try_infer_datetimes
- schema_inference_make_columns_nullable
- input_format_json_try_infer_numbers_from_strings
- input_format_json_infer_incomplete_types_as_strings
- input_format_json_read_numbers_as_strings
- input_format_json_read_bools_as_strings
- input_format_json_read_bools_as_numbers
- input_format_json_read_arrays_as_strings
いくつかの例を見てみましょう:
JSONオブジェクトの配列を処理する
オブジェクトの配列を含むJSONパスは、型Array(JSON)
として解析され、パスのDynamic
カラムに挿入されます。
オブジェクトの配列を読むには、それをDynamic
カラムからサブカラムとして抽出できます:
ご覧のとおり、ネストされたJSON
型のmax_dynamic_types
/max_dynamic_paths
パラメータは、デフォルト値と比較して減少しています。
これは、ネストされたJSONオブジェクトの配列で異なるサブカラムの数が無限に増えるのを避けるために必要です。
ネストされたJSON
カラムからサブカラムを読み取ってみましょう:
Array(JSON)
のサブカラム名を記述する際、特別な構文を使用して[]
の記述を省略できます:
[]
の数は、配列のレベルを示します。例えば、json.path[][]
はjson.path.:Array(Array(JSON))
に変換されます。
Array(JSON)
内のパスと型を確認しましょう:
Array(JSON)
カラムからサブカラムを読み取ってみましょう:
ネストされたJSON
カラムからサブオブジェクトのサブカラムも読み取ることができます:
データからのJSON型の読み取り
すべてのテキスト形式
(JSONEachRow
、
TSV
、
CSV
、
CustomSeparated
、
Values
、など)は、JSON
型の読み取りをサポートしています。
例:
テキスト形式でのJSON
は、JSONオブジェクトを含む文字列から解析されます:
JSON内の動的パスの限界に達する
JSON
データ型は、内部で別々のサブカラムとして保存できるパスの数に限りがあります。
デフォルトでこの上限は1024
ですが、型宣言内でパラメータmax_dynamic_paths
を使用して変更できます。
制限に達すると、JSON
カラムに挿入されるすべての新しいパスは、単一の共有データ構造に保存されます。
こうしたパスはサブカラムとして読み取ることができますが、値を抽出するために共有データ構造全体を読み取る必要があります。
この制限は、テーブルを使用不能にすることがある大量の異なるサブカラムが発生するのを避けるために必要です。
制限に達した場合に何が起こるかを、いくつかの異なるシナリオで見てみましょう。
データ解析中に制限に達する
データからJSON
オブジェクトを解析する際、現在のデータブロックの制限に達すると、すべての新しいパスは共有データ構造に保存されます。
次の2つのイントロスペクション関数JSONDynamicPaths
、JSONSharedDataPaths
を使用できます:
ご覧のとおり、e
とf.g
というパスが挿入された後、制限に達し、共有データ構造に挿入されました。
MergeTreeテーブルエンジンにおけるデータパーツのマージ中
MergeTree
テーブルで複数のデータパーツをマージする際、結果のデータパーツにおける JSON
カラムは動的パスの制限に達し、ソースパーツからのすべてのパスをサブカラムとして保存できなくなります。この場合、ClickHouseはマージ後にどのパスがサブカラムとして残り、どのパスが共有データ構造に保存されるかを選択します。ほとんどの場合、ClickHouseは非NULL値の数が最も多いパスを保持し、最も希少なパスを共有データ構造に移動させます。ただし、これは実装に依存します。
このようなマージの例を見てみましょう。まず、JSON
カラムを持つテーブルを作成し、動的パスの制限を 3
に設定し、次に 5
つの異なるパスを持つ値を挿入します:
各挿入は、JSON
カラムに単一のパスを含む別々のデータパーツを作成します:
さて、すべてのパーツを1つにマージし、何が起こるかを見てみましょう:
ご覧のように、ClickHouseは最も頻繁に出現するパスである a
、b
、c
を保持し、d
と e
のパスを共有データ構造に移動しました。
内部調査関数
JSON
カラムの内容を検査するのに役立つ関数がいくつかあります:
JSONAllPaths
JSONAllPathsWithTypes
JSONDynamicPaths
JSONDynamicPathsWithTypes
JSONSharedDataPaths
JSONSharedDataPathsWithTypes
distinctDynamicTypes
distinctJSONPaths and distinctJSONPathsAndTypes
例
2020-01-01
の日付に対する GH Archive データセットの内容を調査しましょう:
ALTER MODIFY COLUMNを使用してJSON型に変更
既存のテーブルを変更し、カラムの型を新しい JSON
型に変更することができます。現在、String
型からの ALTER
のみがサポートされています。
例
JSON型の値の比較
JSONオブジェクトは、マップと同様に比較されます。
例えば:
注意: 2つのパスが異なるデータ型の値を含む場合、それらは Variant
データ型の比較規則に従って比較されます。
JSON型のより良い使用のためのヒント
JSON
カラムを作成し、データをロードする前に、以下のヒントを考慮してください:
- データを調査し、できるだけ多くのパスヒントとタイプを指定してください。これにより、ストレージと読み取りがはるかに効率的になります。
- 必要なパスと決して必要ないパスについて考えます。必要ないパスは、
SKIP
セクション、SKIP REGEXP
セクションに指定してください。これにより、ストレージが改善されます。 max_dynamic_paths
パラメータを非常に高い値に設定しないでください。これは、ストレージと読み取りの効率を低下させる可能性があります。 システムパラメータ(メモリ、CPUなど)に依存するため、一般的な目安としてmax_dynamic_paths
> 10 000 に設定しないことをお勧めします。