スキーマの設計
スキーマ推論を使用して、JSONデータの初期スキーマを確立し、S3などでJSONデータファイルをクエリすることができますが、ユーザーはデータに対して最適化されたバージョン管理されたスキーマを確立することを目指すべきです。以下に、JSON構造のモデリングに推奨されるアプローチを示します。
静的JSONと動的JSON
JSONのスキーマを定義する際の主なタスクは、各キーの値に適切な型を決定することです。ユーザーには、JSON階層内の各キーに対して以下のルールを再帰的に適用して、各キーの適切な型を決定することを推奨します。
- プリミティブ型 - キーの値がプリミティブ型である場合、サブオブジェクトの一部であるかルート上にあるかに関係なく、一般的なスキーマの設計ベストプラクティスおよびtype optimization rulesに従ってその型を選択してください。以下の
phone_numbersのようなプリミティブの配列は、Array(<type>)としてモデル化できます。例えば、Array(String)。 - 静的 vs 動的 - キーの値が複雑なオブジェクト（すなわち、オブジェクトまたはオブジェクトの配列）である場合、そのオブジェクトが変化する可能性があるかどうかを決定してください。新しいキーが稀に追加されるオブジェクトでは、新しいキーの追加が予測可能であり、
ALTER TABLE ADD COLUMNを介したスキーマ変更で対処できる場合、これらは静的と見なされます。これは、いくつかのJSONドキュメントに提供されるのはキーのサブセットのみであるオブジェクトを含みます。新しいキーが頻繁に追加されるオブジェクトや予測不可能な場合は、動的と見なすべきです。ここでの例外は、数百または数千のサブキーを持つ構造であり、便利さのために動的と見なすことができます。
値が静的か動的かを確認するには、以下の関連セクション静的オブジェクトの取り扱いおよび動的オブジェクトの取り扱いを参照してください。
重要: 上記のルールは再帰的に適用する必要があります。キーの値が動的であると判断された場合、さらなる評価は必要なく、動的オブジェクトの取り扱いのガイドラインに従うことができます。オブジェクトが静的な場合は、サブキーを評価し続け、キーの値がプリミティブであるか動的キーが見つかるまで続けます。
これらのルールを示すために、以下のJSON例を使用して人格を表現します:
これらのルールを適用すると:
- ルートキー
name、username、email、websiteはタイプStringとして表現できます。カラムphone_numbersは型Array(String)のプリミティブの配列で、dobとidはそれぞれタイプDateとUInt32です。 addressオブジェクトに新しいキーが追加されることはなく（新しい住所オブジェクトのみ）、したがってそれは静的と見なされます。再帰処理を続けると、すべてのサブカラムはプリミティブ（タイプString）と見なすことができますが、geoを除く。これもまた、２つのFloat32カラム（latとlon）を持つ静的構造です。tagsカラムは動的です。このオブジェクトに新しい任意のタグが追加されることを想定します。companyオブジェクトは静的で、常に指定された3つのキーしか含まれません。サブキーnameとcatchPhraseはStringタイプです。キーlabelsは動的です。このオブジェクトに新しい任意のタグが追加されることを想定しています。値は常に文字列タイプのキーと値のペアになります。
数百または数千の静的キーを持つ構造は動的と見なすことができます。これは、これらに対して静的にカラムを宣言することは現実的ではありません。ただし、可能であれば、ストレージと推論のオーバーヘッドを節約するためにスキップパスを使用してください。
静的な構造の取り扱い
静的な構造は、名前付きタプル、すなわちTupleを使用して処理することを推奨します。オブジェクトの配列は、タプルの配列、すなわちArray(Tuple)を使用して保持できます。タプル内でも、カラムとそのそれぞれの型は同じルールを使用して定義する必要があります。これにより、以下に示すようにネストされたオブジェクトを表すためのネストされたタプルが作成される可能性があります。
これを説明するために、動的オブジェクトを省略した先のJSON人物例を使用します:
このテーブルのスキーマは以下のようになります:
companyカラムがTuple(catchPhrase String, name String)として定義されていることに注目してください。addressキーはArray(Tuple)を使用し、geoカラムを表現するためにネストされたTupleを使用します。
現在の構造のJSONをこのテーブルに挿入できます:
上記の例では、データが最小限ですが、以下に示すように、タプルカラムをその期間区切り名でクエリできます。
address.streetカラムがArrayとして返される点に注意してください。配列内の特定のオブジェクトに位置でアクセスするには、カラム名の後に配列オフセットを指定する必要があります。たとえば、最初の住所から通りにアクセスするには:
サブカラムは、24.12からのキーの順序付けにも使用できます:
デフォルト値の処理
JSONオブジェクトが構造化されている場合でも、提供されるキーのサブセットのみでスパースなことがよくあります。幸いにも、Tuple型はJSONペイロード内のすべてのカラムを必要としません。提供されていない場合は、デフォルト値が使用されます。
先のpeopleテーブルと、キーsuite、geo、phone_numbers、およびcatchPhraseが欠けている次のスパースJSONを考えます。
以下のように、この行を正常に挿入できることがわかります:
この単一行をクエリすると、欠落したカラム（サブオブジェクトを含む）にデフォルト値が使用されることがわかります:
新しいカラムの扱い
静的なJSONキーの場合、構造化されたアプローチが最も簡単ですが、スキーマに対する変更を計画できる場合（すなわち、新しいキーが事前に知られていて、それに応じてスキーマを変更できる場合）でもこのアプローチを使用できます。
ClickHouseは、デフォルトでペイロード内に提供され、スキーマに存在しないJSONキーを無視することに注意してください。次の修正されたJSONペイロードのnicknameキーが追加された場合を考えます:
このJSONは、nicknameキーが無視されて正常に挿入されます:
ALTER TABLE ADD COLUMNコマンドを使用してスキーマにカラムを追加できます。DEFAULT句を使用してデフォルトを指定できます。これは、後続の挿入中に指定されていない場合に使用されます。この値が存在しない行（それは作成前に挿入されたため）もこのデフォルト値を返します。デフォルト値が指定されていない場合、型のデフォルト値が使用されます。
例えば:
半構造化/動的構造の取り扱い
JSONデータが半構造化されており、キーが動的に追加できたり、複数の型を持つ場合は、JSON型を推奨します。
特に、データに以下の条件がある場合はJSON型を使用します：
- 予測不可能なキーを持ち、時間と共に変わる可能性がある。
- 異なる型の値（例えば、パスが時々文字列を含み、時々数値を含む）を含む。
- 厳密な型指定が実現できないスキーマ柔軟性が必要。
- 幾つかの静的なパスがあるが明示的に宣言することは現実的ではない場合。これは稀である傾向があります。
先の人物JSONでは、company.labelsオブジェクトが動的であると判断されました。
company.labelsが任意のキーを含むと仮定しましょう。さらに、この構造内の任意のキーの型は行ごとに一貫していない可能性があります。例えば:
company.labelsカラムの動的な性質を考慮するにあたり、以下のようなオプションでこのデータをモデル化できます：
- 単一JSONカラム - スキーマ全体を単一の
JSONカラムとして表すことで、すべての構造がその下で動的になります。 - ターゲットJSONカラム -
company.labelsカラムにのみJSON型を使用し、他のすべてのカラムに対して上記の構造化されたスキーマを維持します。
最初のアプローチは先の方法論と一致しませんが、単一のJSONカラムアプローチはプロトタイピングやデータエンジニアリングタスクに役立ちます。
ClickHouseのスケールでの本番展開では、構造を明示的にし、可能であれば動的なサブ構造に対してJSON型を使用することを推奨します。
厳密なスキーマには多くの利点があります：
- データ検証 - 厳密なスキーマを強制することで、特定の構造を除いてカラムの爆発のリスクを回避します。
- カラムの爆発のリスクを回避 - JSON型は潜在的に千のカラムにスケールしますが、サブカラムが専用カラムとして保存される場合、数えきれないカラムファイルが作成され、パフォーマンスに影響を与える可能性があります。これを軽減するために、JSONで使用される基本のDynamic typeには、個別のカラムファイルとして保存されるユニークなパスの数を制限する
max_dynamic_pathsパラメータがあります。閾値に達すると、追加のパスはコンパクトなエンコーディング形式を使用して共有カラムファイルに保存され、パフォーマンスとストレージの効率を維持しながら、柔軟なデータ取り込みをサポートします。ただし、この共有カラムファイルへのアクセスは、パフォーマンスが劣ることがあります。ただし、JSONカラムはタイプヒントと共に使用できます。「ヒント付け」されたカラムは、専用のカラムと同じパフォーマンスを提供します。 - パスと型の簡単な内省 - JSON型は、推論された型とパスを特定するための内省関数をサポートしていますが、静的構造は探るのに簡単です。例えば
DESCRIBEを使って。
単一JSONカラム
このアプローチはプロトタイピングやデータエンジニアリングタスクに役立ちます。本番では、必要に応じて動的なサブ構造にのみJSONを使用するようにしてください。
単一のJSONカラムは、必要でないJSONパスをスキップ（保存しない）することで最適化できます。また、タイプヒントを使用することもできます。タイプヒントを使用することで、ユーザーはサブカラムの型を明示的に定義でき、推論と間接処理をクエリ時にスキップできます。これにより、明示的なスキーマを使用している場合と同じパフォーマンスを提供できます。“タイプヒントを使用してパスをスキップする”の詳細を参照してください。
単一JSONカラムのスキーマは次のようにシンプルです：
usernameカラムのJSON定義にはタイプヒントを提供しています。これは、順序付け/主キーで使用するためです。これにより、ClickHouseはこのカラムがnullにならないことを知り、使用すべきusernameサブカラムを把握します（各タイプごとに複数存在する可能性があるため、さもなければあいまいです）。
上記のテーブルに行を挿入するには、JSONAsObject形式を使用できます：
推論されたサブカラムとその型をは、内省関数を使用して決定できます。例えば:
内省関数による完全なリストについては、"内省関数"を参照してください。
サブパスにアクセスできます .記法を使用して、例えば：
行に欠けているカラムはNULLとして返される点に注意してください。
さらに、同じ型のパスに対しては別々のサブカラムが作成されます。例えば、company.labels.typeに対して、String型とArray(Nullable(String))型の両方にサブカラムが存在します。両方が可能な限り返されますが、特定のサブカラムを.:記法を使用してターゲットすることができます。
ネストされたサブオブジェクトを返すには、^が必要です。これは、読み取るカラムの数が多すぎないようにするための設計上の選択です。明示的に要求されない限り、オブジェクトにアクセスするとNULLが返されるでしょう。
ターゲットとした JSON カラム
プロトタイピングやデータエンジニアリングの課題では便利ですが、可能な限りプロダクションでは明示的なスキーマを使用することをお勧めします。
以前の例は、company.labels カラムのための単一の JSON カラムでモデル化できます。
このテーブルには、JSONEachRow フォーマットを使用して挿入できます:
Introspection functionsを使用して、company.labels カラムの推測されたパスとタイプを確認できます。
型ヒントとパスのスキップを使用する
型ヒントを使用することで、パスおよびそのサブカラムのタイプを指定し、不必要な型推論を防ぐことができます。以下の例を考えると、JSON カラム company.labels 内の JSON キー dissolved、employees、founded のタイプを指定します。
これらのカラムには、今や明示的な型があります:
さらに、私たちは、ストレージを最小化し、不要なパスの推論を避けるために、SKIP および SKIP REGEXP パラメータを使用して、保存したくない JSON 内のパスをスキップすることができます。たとえば、上記のデータに対して単一の JSON カラムを使用する場合を考えてみましょう。address と company パスをスキップできます:
私たちのカラムにデータが除外されていることに注目してください:
型ヒントでパフォーマンスを最適化する
型ヒントは、不必要な型推論を回避する方法以上のものを提供します - ストレージと処理の間接を完全に排除し、最適なプリミティブ型を指定できるようにします。型ヒントを持つ JSON パスは、常に従来のカラムのように保存され、識別子カラムやクエリ時の動的解決の必要性を回避します。
これにより、定義された型ヒントを使用すれば、ネストされた JSON キーは、最初から最上位カラムとしてモデル化されている場合と同じパフォーマンスと効率を実現します。
その結果、ほとんど一貫しているが、JSON の柔軟性から利益を得るデータセットに対して、型ヒントはスキーマやインジェストパイプラインを再構築する必要なくパフォーマンスを維持する便利な方法を提供します。
ダイナミックパスの設定
ClickHouse は、各 JSON パスを真の列指向レイアウトでサブカラムとして保存し、従来のカラムと同様のパフォーマンス上の利点（圧縮、SIMD 加速処理、最小限のディスク I/O など）を可能にします。JSON データ内の各ユニークなパスと型の組み合わせは、ディスク上でそれ自身のカラムファイルになります。
たとえば、異なる型で 2 つの JSON パスが挿入されると、ClickHouse はそれぞれの具体的な型の値を異なるサブカラムに保存します。これらのサブカラムには独立してアクセスでき、不必要な I/O を最小限に抑えます。複数の型を持つカラムをクエリする際、値は依然として単一の列指向応答として返されます。
さらに、オフセットを活用することで、ClickHouse はこれらのサブカラムが密度を保つようにし、存在しない JSON パスのためにデフォルト値を保存しません。このアプローチは圧縮を最大化し、さらに I/O を削減します。
しかし、高いカーディナリティまたは高い変動のある JSON 構造（テレメトリパイプライン、ログ、または機械学習の特徴ストアなど）におけるシナリオでは、この動作はカラムファイルの爆発を引き起こす可能性があります。各新しいユニークな JSON パスは新しいカラムファイルをもたらし、各型バリアントはそのパスの下で追加のカラムファイルをもたらします。これはリードパフォーマンスには最適ですが、運用上の課題（ファイルディスクリプタの枯渇、メモリ使用量の増加、小さなファイルの数が多いためマージが遅くなる）を導入します。
これを軽減するために、ClickHouse はオーバーフローサブカラムの概念を導入します。異なる JSON パスの数が閾値を超えた場合、追加のパスはコンパクトにエンコードされた形式で単一の共有ファイルに保存されます。このファイルは依然としてクエリ可能ですが、専用のサブカラムと同じ性能特性の利益を享受することはありません。
この閾値は、JSON 型宣言におけるmax_dynamic_paths パラメータで制御されます。
このパラメータを高すぎる設定は避けてください - 大きな値はリソース消費を増加させ、効率を低下させます。一般的な指針として、10,000 を下回るように保ってください。高い動的構造を持つワークロードには、型ヒントと SKIP パラメータを使用して、保存されるものを制限してください。
この新しいカラム型の実装に興味があるユーザーには、私たちの詳細なブログ記事 "ClickHouse のための新しい強力な JSON データ型" の読解をお勧めします。
