静的 JSON と動的 JSON
- プリミティブ型 - キーの値がプリミティブ型である場合、そのキーがサブオブジェクト内にあるかルートにあるかにかかわらず、一般的なスキーマの設計ベストプラクティスおよび型の最適化ルールに従って型を選択してください。以下の
phone_numbersのようなプリミティブ値の配列は、Array(<type>)、たとえばArray(String)としてモデル化できます。 - 静的か動的か - キーの値が複雑なオブジェクト、つまりオブジェクトまたはオブジェクトの配列である場合は、それが変更される可能性があるかどうかを判断します。新しいキーが追加されることがまれで、その追加を予測でき、
ALTER TABLE ADD COLUMNによるスキーマ変更で対応できるオブジェクトは、静的 と見なせます。これには、一部の JSON ドキュメントでキーの一部しか存在しないオブジェクトも含まれます。新しいキーが頻繁に追加されるものや、追加されるキーを予測できないものは、動的 と見なすべきです。ただし、数百から数千のサブキーを持つ構造は、利便性の観点から動的と見なすことができます。
- ルートキー
name、username、email、websiteは型Stringとして表現できます。カラムphone_numbersは型Array(String)の Array プリミティブで、dobとidの型はそれぞれDateとUInt32です。 addressオブジェクトには新しいキーは追加されず (追加されるのは新しい address オブジェクトのみ) 、そのため 静的 と見なせます。再帰的に見ると、すべてのサブカラムはgeoを除きプリミティブ (型はString) と見なせます。geoも静的な構造で、latとlonという 2 つのFloat32カラムを持ちます。tagsカラムは 動的 です。このオブジェクトには、任意の型や構造の新しいタグが自由に追加される可能性があると想定します。companyオブジェクトは 静的 で、含まれるキーは指定された 3 つまでです。サブキーnameとcatchPhraseは型Stringです。キーlabelsは 動的 です。このオブジェクトには新しい任意のタグを追加できると想定します。値は常に文字列型のキー・バリューのペアです。
数百または数千もの静的キーを持つ構造では、それらのカラムを静的に宣言するのが現実的でないことが多いため、動的なものと見なせます。ただし、可能であれば、ストレージ使用量と推論のオーバーヘッドの両方を抑えるため、不要なパスをスキップしてください。
静的な構造の扱い
Tuple を使用することを推奨します。オブジェクトの配列は、タプルの配列、つまり Array(Tuple) として保持できます。タプルの内部でも、カラムとそれぞれの型は同じルールに従って定義する必要があります。その結果、以下に示すように、ネストしたオブジェクトを表現するために Tuple が入れ子になる場合があります。
これを示すため、先ほどの JSON の person の例を使い、動的なオブジェクトは省略します。
company カラムが Tuple(catchPhrase String, name String) として定義されている点に注目してください。address キーでは Array(Tuple) を使用し、geo カラムを表すためにネストした Tuple を使っています。
JSON は現在の構造のまま、このテーブルに挿入できます。
address.street カラムが Array として返される点に注意してください。配列内の特定のオブジェクトを位置でクエリするには、カラム名の後に配列のオフセットを指定する必要があります。たとえば、最初の住所の street にアクセスするには、次のようにします。
24.12 以降、ソートキーにも使用できます。
デフォルト値の扱い
Tuple 型では JSON payload 内のすべてのカラムを指定する必要はありません。指定されていない場合は、デフォルト値が使用されます。
先ほどの people table と、suite、geo、phone_numbers、catchPhrase のキーが欠けている次のスパースな JSON を見てみましょう。
空文字列と null の区別値が空であることと値が指定されていないことを区別する必要がある場合は、Nullable 型を使用できます。ただし、これらのカラムではストレージ効率やクエリ性能に悪影響を及ぼすため、絶対に必要な場合を除き避けるべきです。
新しいカラムの扱い
nickname キーを追加した次のような変更済み JSON ペイロードを見てみましょう。
nickname キーを無視しても正常に挿入できます:
ALTER TABLE ADD COLUMN コマンドを使用すると、スキーマにカラムを追加できます。DEFAULT 句でデフォルト値を指定でき、以降の insert 時に値が指定されなかった場合はその値が使用されます。この値を持たない行 (そのカラムが作成される前に挿入された行) についても、このデフォルト値が返されます。DEFAULT 値が指定されていない場合は、その型のデフォルト値が使用されます。
例えば:
半構造化/動的な構造の扱い
JSON 型を推奨します。
より具体的には、次のようなデータでは JSON 型を使用してください。
- 時間の経過とともに変化しうる、予測不能なキーがある。
- 値の型が一定ではない (たとえば、ある パス には文字列が入り、別のときには数値が入ることがある) 。
- 厳密な型付けが現実的ではなく、柔軟なスキーマが必要である。
- 静的ではあるものの、明示的に宣言するのが現実的でない 数百、あるいは数千 の パス がある。これはまれなケースです。
company.labels オブジェクトが動的であると判断した、前述の person JSON を考えてみましょう。
company.labels には任意のキーが含まれているとします。さらに、この構造内の各キーの型は、行ごとに一貫していない可能性があります。たとえば、次のようなケースです。
company.labels カラムはキーと型の観点で動的に変化するため、このデータをモデル化する方法はいくつかあります。
- 単一の JSONカラム - スキーマ全体を 1 つの
JSONカラムとして表現し、その配下のすべての構造を動的に扱えるようにします。 - 対象を絞った JSONカラム -
company.labelsカラムに対してのみJSON型 を使用し、その他すべてのカラムについては上記の構造化スキーマを維持します。
- データ検証 – 特定の構造を除けば、厳格なスキーマを適用することでカラム爆発のリスクを回避できます。
- カラム爆発のリスクを回避 - JSON 型は、サブカラムを専用カラムとして保存することで、潜在的には数千ものカラムまでスケールできます。ただし、その結果として過剰な数のカラムファイルが作成され、パフォーマンスに影響する「カラムファイルの爆発」を招く可能性があります。これを軽減するため、JSON の基盤となる Dynamic type には
max_dynamic_pathsパラメータがあり、個別のカラムファイルとして保存される一意のパス数を制限できます。しきい値に達すると、追加のパスはコンパクトにエンコードされたフォーマットを使用する共有カラムファイルに保存されるため、柔軟なデータのインジェストをサポートしつつ、パフォーマンスとストレージ効率を維持できます。ただし、この共有カラムファイルへのアクセスは、専用カラムほど高性能ではありません。なお、JSONカラム は 型ヒント と組み合わせて使用することもできます。“ヒント付き” カラムは、専用カラムと同等のパフォーマンスを発揮します。 - パスと型のイントロスペクションがより簡単 - JSON 型は、推論された型やパスを確認するための イントロスペクション関数 をサポートしていますが、静的な構造のほうが、たとえば
DESCRIBEを使って簡単に調べやすい場合があります。
単一の JSON カラム
JSON を使用してください。
パフォーマンスに関する考慮事項単一の JSON カラムは、不要な JSON パスをスキップして (保存せずに) 、型ヒント を使用することで最適化できます。型ヒントを使うと、ユーザーはサブカラムの型を明示的に定義できるため、クエリ時の推論や間接処理を省略できます。これにより、明示的なスキーマを使用した場合と同等のパフォーマンスを実現できます。詳しくは、“型ヒントの使用とパスのスキップ” を参照してください。
username カラムは並び順/主キーで使用するため、JSON 定義では 型ヒント を付けています。これにより、ClickHouse はこのカラムが null にならないことを認識でき、さらにどの username サブカラムを使うべきかも判断できます (型ごとに複数存在する可能性があるため、そうしないと曖昧になります) 。JSONAsObject フォーマットを使用できます。
. 記法でアクセスできます。たとえば、
NULL として返される点に注目してください。
さらに、同じ型のパスごとに個別のサブカラムが作成されます。たとえば、company.labels.type には String と Array(Nullable(String)) の両方に対応するサブカラムが存在します。可能な場合は両方が返されますが、.: 構文を使って特定のサブカラムを指定できます。
^ が必要です。これは、明示的に要求された場合を除き、多数のカラムを読み込まないようにするための設計です。^ を付けずにアクセスしたオブジェクトは、以下のように NULL を返します。
特定のJSONカラム
company.labels カラムに単一の JSON カラムを使用することでモデル化できます。
JSONEachRow フォーマットを使用できます。
company.labels カラムの推定パスと型を確認できます。
型ヒントとパスのスキップの使用
company.labels 内の JSON キー dissolved、employees、founded の型を指定しています
SKIP と SKIP REGEXP パラメータを使ってスキップできます。これにより、ストレージ使用量を最小限に抑え、不要なパスに対する不要な推論を避けられます。たとえば、上記のデータに対して 単一の JSON カラム を使用するとします。この場合、address と company のパスをスキップできます:”
型ヒントでパフォーマンスを最適化する
動的パスの設定
max_dynamic_paths parameter で制御されます。
SKIP パラメータを使って、保存する内容を制限してください。
この新しいカラム型の実装に興味がある方には、詳しくはブログ記事”ClickHouse の新しい強力な JSON データ型”を読むことをおすすめします。