オブジェクトレベルのアプローチを採用する同じスキーマ内でも、オブジェクトごとに異なる手法を適用できます。たとえば、
String 型で扱うのが適したオブジェクトもあれば、Map 型で扱うのが適したオブジェクトもあります。String 型を一度選べば、それ以上スキーマに関する判断を行う必要はありません。一方で、以下に示すように、Map のキーの下にサブオブジェクトをネストすることもでき、JSON を表す String を含めることも可能です。String 型の使用
String 型を使用する必要があります。値は、以下で示すように JSON 関数を使ってクエリ時に抽出できます。
前述の構造化アプローチでデータを扱う方法は、JSON が動的で変更される可能性がある、あるいはスキーマが十分に把握されていないケースでは、現実的でないことが少なくありません。最大限の柔軟性を確保するには、JSON を単純に String として保存し、必要に応じて関数を使ってフィールドを抽出できます。これは、JSON を構造化オブジェクトとして扱う方法とは対極にあるアプローチです。ただし、この柔軟性には代償があり、大きな欠点も伴います。主なものは、クエリ構文が複雑になることと、パフォーマンスが低下することです。
前述のとおり、元の person オブジェクトでは、tags カラムの構造を保証できません。元の行 (ここではひとまず無視する company.labels を含む) を挿入し、Tags カラムを String として宣言します。
tags カラムを選択すると、JSON が文字列として挿入されていることを確認できます:
JSONExtract 関数を使用すると、この JSON から値を取得できます。以下の簡単な例を考えてみましょう。
String 型のカラム tags への参照と、抽出対象の JSON 内のパスの両方が必要になる点に注目してください。パスがネストされている場合は、関数もネストする必要があります。たとえば JSONExtractUInt(JSONExtractString(tags, 'car'), 'year') は、カラム tags.car.year を抽出します。ネストされたパスの抽出は、関数 JSON_QUERY と JSON_VALUE を使うことで簡略化できます。
極端な例として、arxiv データセットでは本文全体を String とみなすケースを考えてみましょう。
JSONAsString フォーマットを使用する必要があります。
JSON_VALUE(body, '$.versions[0].created') のように、XPath 式を使って method で JSON を絞り込んでいる点に注目してください。
String 関数は、インデックスを使った明示的な型変換に比べて大幅に遅く (> 10x) 、上記のクエリでは常にテーブル全体のスキャンとすべての行の処理が必要になります。このような小さなデータセットであれば、これらのクエリも依然として高速ですが、より大きなデータセットではパフォーマンスが低下します。
このアプローチは柔軟である一方、パフォーマンスと構文の両面で明確なコストを伴うため、スキーマ内で非常に動的なオブジェクトに対してのみ使用すべきです。
シンプルな JSON 関数
simpleJSON* 関数は、主に JSON の構造とフォーマットについて厳密な前提を置くことで、より高いパフォーマンスを発揮できる可能性があります。具体的には、次のとおりです。
- フィールド名は定数でなければなりません
-
フィールド名のエンコーディングは一貫している必要があります。たとえば、
simpleJSONHas('{"abc":"def"}', 'abc') = 1ですが、visitParamHas('{"\\u0061\\u0062\\u0063":"def"}', 'abc') = 0です - フィールド名は、すべてのネスト構造を通して一意でなければなりません。ネストレベルは区別されず、照合は区別なく行われます。複数の一致するフィールドがある場合は、最初に出現したものが使用されます。
-
文字列リテラルの外側では特殊文字を使用できません。これには空白も含まれます。以下は無効で、パースされません。
created キーを抽出するために simpleJSONExtractString を使用しています。この場合、パフォーマンス向上という利点を考えれば、simpleJSON* 関数の制約は許容できます。
Map 型を使う
オブジェクトが主に同一の型に属する任意のキーを格納するために使われる場合は、Map 型の使用を検討してください。理想的には、一意なキーの数は数百を超えないようにするのが望ましいです。Map 型はサブオブジェクトを含むオブジェクトにも使用を検討できますが、その場合はサブオブジェクトの型に統一性があることが前提です。一般に、Map 型はラベルやタグ、たとえばログデータ内の Kubernetes ポッドのラベルに使用することを推奨します。
Map はネストした構造を表現するシンプルな方法ですが、いくつか注意すべき制約があります。
- フィールドはすべて同じ型でなければなりません。
- フィールドはカラムとして存在しないため、サブカラムにアクセスするには特別な map 構文が必要です。オブジェクト全体 が 1 つのカラムです。
- サブカラムにアクセスすると、
Mapの値全体、つまりすべての兄弟要素とその値もあわせて読み込まれます。map が大きい場合、これは大幅な性能低下につながることがあります。
String キーオブジェクトを
Map として表現する場合、JSON のキー名を格納するために String キーが使われます。したがって、map は常に Map(String, T) となり、T はデータに応じて決まります。プリミティブ値
Map の最もシンプルな使い方は、オブジェクトの値がすべて同じプリミティブ型である場合です。ほとんどのケースでは、値 T に String 型を使います。
先ほどの person JSON では、company.labels オブジェクトは動的であると判断しました。重要なのは、このオブジェクトには String 型のキー・バリューの組だけが追加される想定である点です。したがって、これは Map(String, String) として宣言できます。
request オブジェクト内のこれらのフィールドをクエリするには、たとえば次のような Map 構文を使用します。
Map 関数一式が用意されており、こちらで説明されています。データの型に一貫性がない場合は、必要な型変換を行うための関数も用意されています。
オブジェクトの値
Map 型は、サブオブジェクトを持つオブジェクトにも使用できます。ただし、それらのサブオブジェクトの型に一貫性があることが前提です。
たとえば、persons オブジェクトの tags キーに一貫した構造が必要で、各 tag のサブオブジェクトが name と time のカラムを持つとします。このような JSON ドキュメントを簡略化すると、次のようになります。
Map(String, Tuple(name String, time DateTime)) で表現できます。
Array(Tuple(key String, name String, time DateTime)) を使用できるようになります。
Nested 型の使用
Nested 型 は、変更されることがほとんどない静的なオブジェクトを表現する際に、Tuple や Array(Tuple) の代替として使用できます。JSON にこの型を使用することは、挙動がわかりにくい場合が多いため、一般的には避けることを推奨します。Nested の主な利点は、サブカラムを ordering key に使用できることです。
以下では、静的なオブジェクトを表現するために Nested 型を使用する例を示します。JSON の次のような単純なログエントリを考えてみましょう。
request キーは Nested 型として宣言できます。Tuple と同様に、子カラムを指定する必要があります。
flatten_nested
設定flatten_nested は、nested の動作を制御します。
flatten_nested=1
1 (デフォルト) では、任意のレベルのネストはサポートされません。この値では、ネストされたデータ構造は、長さが同じ複数の Array カラムとして捉えるのが最も簡単です。method、path、version の各フィールドは、実質的にはそれぞれ独立した Array(Type) カラムですが、1つ重要な制約があります。method、path、version フィールドの長さは同じでなければなりません。 これは SHOW CREATE TABLE を使うと次のように確認できます。
-
JSON をネスト構造として insert するには、設定
input_format_import_nested_jsonを使用する必要があります。これを使用しない場合は、JSON をフラット化する必要があります。つまり、次のようになります。 -
ネストされたフィールド
method、path、versionは、JSON 配列として渡す必要があります。つまり、次のようになります。
Array を使用している点に注目してください。これは、ARRAY JOIN 句を含め、Array functions の幅広い機能を活用できる可能性があることを意味します。特に、カラムが複数の値を持つ場合に便利です。
flatten_nested=0
これにより、任意のレベルのネストが可能になり、ネストされたカラムはTuple の単一の配列として保持されます。つまり、実質的には Array(Tuple) と同じになります。
これは、Nested で JSON を使用する際に推奨される方法であり、多くの場合もっともシンプルな方法でもあります。以下で示すように、必要なのはすべてのオブジェクトがリストになっていることだけです。
以下では、テーブルを再作成し、1 行を再度挿入します。
-
input_format_import_nested_jsonは、挿入時には不要です。 -
Nested型はSHOW CREATE TABLEでも保持されます。内部的には、このカラムは実質的にArray(Tuple(Nested(method LowCardinality(String), path String, version LowCardinality(String))))になります。 -
そのため、
requestは配列として挿入する必要があります。つまり、
例
上記データのより大きな例は、S3 上の公開バケットs3://datasets-documentation/http/ で利用できます。
flatten_nested=0 を設定します。
次のステートメントでは 1,000 万行を挿入するため、実行に数分かかる場合があります。必要に応じて LIMIT を適用してください。
ペアワイズ配列の使用
ペアワイズ配列は、JSON を String として表現する柔軟性と、より構造化されたアプローチによるパフォーマンスとのバランスを実現します。スキーマは柔軟で、新しいフィールドをルートに追加できる可能性があります。ただし、その分クエリ構文は大幅に複雑になり、ネスト構造には対応していません。 例として、次のテーブルを考えてみましょう。JSONExtractKeysAndValues を使用する例を、次のクエリに示します。
indexOf 関数を使って、必要なキーの索引を特定する必要があります (これは値の並び順と一致している必要があります) 。これにより、values の Array 型カラム、つまり values[indexOf(keys, 'status')] にアクセスできます。さらに、request カラムについては JSON をパースする方法が必要で、この場合は simpleJSONExtractString を使用します。