スキーマの設計
schema inference を使用して、JSONデータの最初のスキーマを確立し、S3などの場所でJSONデータファイルをクエリすることは可能ですが、ユーザーはデータの最適化されたバージョン管理スキーマを確立することを目指すべきです。以下に、JSON構造をモデリングするためのオプションを説明します。
抽出可能な場合は抽出する
可能な限り、ユーザーは頻繁にクエリするJSONキーをスキーマのルートにあるカラムに抽出することを推奨します。これにより、クエリ構文が単純化されるだけでなく、必要に応じてこれらのカラムを ORDER BY 句で使用したり、secondary indexを指定したりできます。
以下は、ガイド JSONスキーマ推論 で探求された arXiv データセット の例です：
versions.created の最初の値をメインのオーダリングキーにしたいと考えているとします。理想的には、これは挿入前に抽出されるか、挿入時に ClickHouse の materialized views または materialized columns を使用して行われるべきです。
Materialized columns は、クエリ時にデータを抽出する最も簡単な手段を表し、抽出ロジックが単純な SQL 式として捉えられる場合は好まれます。たとえば、published_date を arXiv スキーマに materialized column として追加し、オーダリングキーとして以下のように定義できます：
上記では、タプルに versions[1].1 という表記法を使用し、位置によって created カラムにアクセスする必要があります。これは、好ましい構文である versions.created_at[1] とは異なります。
データを読み込むと、カラムが抽出されます：
Materialized columns の値は常に挿入時に計算され、INSERT クエリで指定することはできません。デフォルトでは、materialized columns は SELECT * に返されません。これは、SELECT * の結果が常に INSERT を使用してテーブルに戻されることを保持するためです。この動作は、asterisk_include_materialized_columns=1 を設定することで無効化できます。
より複雑なフィルタリングや変換タスクについては、materialized views の使用を推奨します。
静的 vs 動的 JSON
JSON のスキーマを定義する主なタスクは、各キーの値に対する適切なタイプを決定することです。ユーザーは、各キーに対して適切なタイプを決定するために、JSON 階層内の各キーに次のルールを再帰的に適用することを推奨します。
- プリミティブタイプ - キーの値がプリミティブタイプである場合（それがサブオブジェクトの一部かルートにあるかにかかわらず）、一般的なスキーマの 設計ベストプラクティス および タイプ最適化ルール に従ってそのタイプを選択するようにしてください。以下の
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オブジェクトには新しいキーが追加されないため（新しい住所オブジェクトのみ）、これは 静的 と見なすことができます。再帰的に評価すると、geoを除くすべてのサブカラムはプリミティブ（型String）と見なすことができます。これは、latとlngの2つのFloat32カラムを持つ静的な構造でもあります。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 として返されることに注意してください。配列内の特定のオブジェクトに位置によってクエリするには、カラム名の後に配列オフセットを指定する必要があります。たとえば、最初の住所から通りにアクセスするには：
タプルの主な欠点は、サブカラムをオーダリングキーに使用できないことです。したがって、以下は失敗します：
タプル列はオーダリングキーで使用できませんが、全体のタプルは使用できます。可能ではありますが、これは稀にしか意味を持ちません。
デフォルト値の処理
JSONオブジェクトが構造化されている場合であっても、提供されるキーのサブセットのみでスパースであることがよくあります。幸いなことに、TupleタイプはJSONペイロード内のすべてのカラムを必要としません。提供されていない場合は、デフォルト値が使用されます。
以前の people テーブルと次のスパースなJSONを考慮し、suite、geo、phone_numbers、および catchPhrase キーが欠けています。
以下のように、この行が正常に挿入されることが確認できます：
この単一行をクエリすると、省略されたカラム（サブオブジェクトを含む）にデフォルト値が使用されることが確認できます：
ユーザーが、値が空であることと提供されていないことを区別する必要がある場合は、Nullable タイプを使用できます。この avoid nullable columns で明示的に回避するべきです。そうでないと、これらのカラムでのストレージとクエリパフォーマンスに悪影響を及ぼします。
新しいカラムの処理
静的なJSONキーに対して構造化されたアプローチが最も簡単ですが、スキーマ変更が計画できる場合でもこのアプローチを使用できます。つまり、新しいキーが事前に知られており、そのスキーマを適宜変更できる場合です。
ClickHouseは、デフォルトでペイロード内に提供されているJSONキーを無視し、スキーマに存在しないものに対して無視します。たとえば、nicknameキーの追加を伴う次の修正されたJSONペイロードを考えてください：
このJSONは正常に挿入され、nicknameキーは無視されます：
カラムは ALTER TABLE ADD COLUMN コマンドを使用してスキーマに追加できます。デフォルトは DEFAULT 句を介して指定でき、変更後の挿入時に指定されていない場合に使用されます。この値が存在しない行（それが作成された前に挿入されたため）もこのデフォルト値を返します。デフォルト値が指定されていない場合は、タイプのデフォルト値が使用されます。
たとえば：
動的オブジェクトの処理
動的オブジェクトを処理するための推奨される2つのアプローチがあります：
- Map(String,V) タイプ
- String と JSON 関数
最も適切なものを決定するために、以下のルールを適用できます。
- オブジェクトが非常に動的であり、予測可能な構造がなく、任意のネストされたオブジェクトを含む場合は、
Stringタイプを使用すべきです。値は、以下に示すように、クエリ時にJSON関数を使用して抽出できます。 - オブジェクトが主に同じタイプの任意のキーを格納するために使用される場合は、
Mapタイプを使用することを考慮してください。理想的には、一意のキーの数は数百を超えないべきです。また、サブオブジェクトに一貫性がある場合も、Mapタイプを考慮できます。一般に、ラベルやタグ用にMapタイプを使用することを推奨します。たとえば、ログデータにおけるKubernetesポッドのラベルなどです。
同じスキーマ内の異なるオブジェクトに異なる手法を適用できます。一部のオブジェクトは String で最もよく解決できるかもしれませんが、他のオブジェクトは Map で最も適しています。String タイプを使用すると、それ以上のスキーマ決定は必要ありません。逆に、以下に示すように、Map キー内にネストされたサブオブジェクトを配置することも可能です。
Stringの使用
上記のように構造化されたアプローチを使用したデータ管理は、動的なJSONを持つユーザーにとっては実行可能でないことが多いです。これは変更の対象であるか、スキーマが十分に理解されていないものです。絶対的な柔軟性を求める場合、ユーザーは単にJSONを String として格納し、その後必要に応じてフィールドを抽出するための関数を使用できます。これは、JSONを構造化されたオブジェクトとして扱うことの極端な反対を表しています。この柔軟性は、クエリ構文の複雑さとパフォーマンスの低下を招くという大きなコストがかかります。
前述のように、元の人オブジェクト において、tags カラムの構造を保証することはできません。元の行を挿入します（ここでは company.labels も含めますが、ここでは無視します）。Tags カラムを String と宣言します：
tags カラムを選択すると、JSONが文字列として挿入されたことがわかります：
このJSONから値を取得するために、JSONExtract 関数を使用できます。以下の単純な例を考えてみましょう：
関数は tags という String カラムへの参照と、抽出するJSON内のパスを両方とも必要とします。ネストされたパスは関数をネストする必要があり、たとえば、JSONExtractUInt(JSONExtractString(tags, 'car'), 'year') のように、カラム tags.car.year を抽出します。ネストされたパスの抽出は、JSON_QUERY および JSON_VALUE 関数を介して簡略化できます。
arxiv データセットの極端なケースを考えてみましょう。この場合、全体のボディを String と見なします。
このスキーマに挿入するには、JSONAsString 形式を使用する必要があります：
年ごとの公開論文数をカウントしたいとします。次のように、構造化されたバージョン のスキーマと文字列のみを使用した場合のクエリを対比します：
ここで、JSON_VALUE(body, '$.versions[0].created') のXPath式を使用して、JSONをメソッドでフィルタリングしていることに注意してください。
文字列関数は明示的な型変換よりもかなり遅く（> 10倍）なります。上記のクエリは常にテーブルスキャンとすべての行の処理を必要とします。これらのクエリは、このような小さなデータセットでは依然として速くなりますが、大きなデータセットではパフォーマンスが低下します。
このアプローチの柔軟性は、明確なパフォーマンスと構文のコストと引き換えであり、高度に動的なオブジェクトのスキーマにのみ使用するべきです。
簡単なJSON関数
上記の例では、JSON*関数のファミリーを使用しています。これらはsimdjson に基づく完全なJSONパーサーを利用しており、その解析は厳格で、異なるレベルにネストされた同じフィールドを区別します。これらの関数は、構文的には正しいが、整形が不十分なJSONにも対処できます。例えば、キーの間に余分な空白がある場合などです。
より速く、さらに厳密な機能セットが利用可能です。これらの simpleJSON* 関数は、主にJSONの構造とフォーマットに関する厳格な仮定を行うことによって、優れたパフォーマンスを提供します。具体的には：
-
フィールド名は定数である必要があります
-
フィールド名の一貫したエンコーディングが必要です。たとえば、
simpleJSONHas('{"abc":"def"}', 'abc') = 1ですが、visitParamHas('{"\\u0061\\u0062\\u0063":"def"}', 'abc') = 0です。 -
フィールド名はすべてのネストされた構造内でユニークでなければなりません。ネストレベル間の区別は行わず、マッチングは無差別です。複数の一致するフィールドがある場合は、最初の出現を使用します。
-
文字列リテラルの外に特別な文字を含めることはできません。これには空白が含まれます。以下は無効であり、解析されません。
以下は、正しく解析されます。
パフォーマンスが重要な場合で、JSONが上記の要件を満たす場合、これらを使用することが適切な場合があります。以下に、前のクエリを simpleJSON* 関数を使用して書き換えた例を示します：
上記は、simpleJSONExtractString を使用して created キーを抽出します。ここでは、公開日には最初の値のみが必要であることを利用しています。この場合、simpleJSON* 関数の制限は、パフォーマンス上の利点として受け入れるべきです。
Mapの使用
オブジェクトが主に同じタイプの任意のキーを格納するために使用される場合は、Map タイプの使用を考慮してください。理想的には、一意のキーの数は数百を超えないように設計してください。Map タイプはタグやラベル用に使用することをお勧めします。例えば、ログデータにおけるKubernetesポッドのラベルなどです。Mapはネストされた構造を表現するシンプルな方法ですが、いくつかの顕著な制限があります：
- フィールドはすべて同じタイプでなければなりません。
- サブカラムにアクセスするには特別なマップ構文が必要です。フィールドはカラムとして存在せず、オブジェクト全体がカラムです。
- サブカラムへのアクセスは、全体の
Map値（すべての兄弟およびそれぞれの値を含む）をロードします。大きなマップの場合、これにより大幅なパフォーマンスのペナルティが発生する可能性があります。
オブジェクトを Map としてモデル化する際は、JSONキー名を保存するために String キーが使用されます。マップはしたがって常に Map(String, T) で、ここで T はデータに応じます。
プリミティブ値
Mapの最も単純な適用は、オブジェクトが値として同じプリミティブ型を含む場合です。ほとんどの場合、これは値 T に対して String 型を使用することが含まれます。
以前の人のJSONを考えてみましょう。ここで company.labels オブジェクトは動的であると判断されました。重要なのは、私たちがこのオブジェクトに追加されることを期待しているのは、String 型のキーとバリューのペアだけであるということです。したがって、これを Map(String, String) と宣言することができます：
私たちは元の完全なJSONオブジェクトを挿入できます：
リクエストオブジェクト内のこれらのフィールドをクエリするには、マップの構文が必要です。例：
この時点での Map 関数の完全なセットは、こちらで説明されています。データが一貫性のある型でない場合は、必要な型強制を実行するための関数が存在します。
オブジェクト値
Map型は、サブオブジェクトを持つオブジェクトにも考慮される場合があります。ただし、後者はその型に一貫性が必要です。
たとえば、personsオブジェクトの tags キーが一貫した構造を必要とし、各 tag のサブオブジェクトが name および time カラムを持つ場合を考えます。このようなJSONドキュメントの簡略化された例は次のようになります：
これは次のように Map(String, Tuple(name String, time DateTime)) でモデル化できます：
この場合におけるマップの適用は通常稀であり、データが動的なキー名を持ってサブオブジェクトを持たないように再モデル化されるべきであることを示唆しています。たとえば、上記は以下のように再モデル化され、Array(Tuple(key String, name String, time DateTime))の使用を可能にします。