他のJSONモデリングのアプローチ
以下は、ClickHouseでのJSONモデリングの代替方法です。これらは完結性のために文書化されており、一般的には推奨されず、ほとんどのユースケースに適用されません。
ネストを使用する
ネスト型は、変更が滅多にない静的オブジェクトをモデリングするために使用でき、TupleやArray(Tuple)の代替手段を提供します。この型はその動作がしばしば混乱を招くため、JSONには使用しないことを一般的に推奨します。Nestedの主な利点は、サブカラムをオーダリングキーに使用できることです。
以下は、静的オブジェクトをモデリングするためにネスト型を使用する例です。以下のJSONの簡単なログエントリを考えてみましょう:
requestキーをNestedとして宣言できます。Tupleと同様に、サブカラムを指定する必要があります。
flatten_nested
設定flatten_nestedは、ネストの動作を制御します。
flatten_nested=1
値が1（デフォルト）は、任意のネストレベルをサポートしません。この値を用いると、ネストされたデータ構造を長さが同じ複数のArrayカラムとして考えるのが最も簡単です。method、path、versionフィールドは全て、実質的には別々のArray(Type)カラムであり、1つの重要な制約があります: method、path、versionフィールドの長さは同じでなければなりません。 SHOW CREATE TABLEを用いると、これが明示されます:
以下にこのテーブルに挿入を行います:
いくつかの重要なポイントに注意してください:
-
JSONをネストされた構造として挿入するために
input_format_import_nested_json設定を使用する必要があります。これがなければ、JSONをフラット化する必要があります。つまり、 -
ネストされたフィールド
method、path、versionはJSON配列として渡される必要があります。つまり、
カラムはドット表記を使用してクエリできます:
サブカラムのArrayの使用により、Array関数を活用できる可能性があり、ARRAY JOIN句も利用可能です - これは、複数の値を持つカラムに便利です。
flatten_nested=0
これにより、任意のネストレベルが許可され、ネストされたカラムはTupleの単一の配列として保持されます - 効果的にArray(Tuple)と同じになります。
これは、JSONをNestedと共に使用するための好ましい方法であり、最も簡単な方法の1つです。下記に示すように、すべてのオブジェクトをリストとして持つだけで済みます。
以下にテーブルを再作成し、行を再挿入します:
いくつかの重要なポイントに注意してください:
-
挿入するために
input_format_import_nested_jsonは必要ありません。 -
SHOW CREATE TABLEでNested型が保持されます。このカラムの下には、実質的にはArray(Tuple(Nested(method LowCardinality(String), path String, version LowCardinality(String))))があります。 -
その結果、
requestを配列として挿入する必要があります。つまり、
カラムは再びドット表記を使用してクエリできます:
例
上記のデータの大きな例は、s3の公開バケットにあります: s3://datasets-documentation/http/。
JSONの制約と入力形式に基づいて、このサンプルデータセットを挿入するために次のクエリを使用します。ここでは、flatten_nested=0を設定します。
次の文は1000万行を挿入するので、実行に数分かかるかもしれません。必要に応じてLIMITを適用してください:
このデータにクエリを行うには、リクエストフィールドを配列としてアクセスする必要があります。以下に、特定の期間のエラーとHTTPメソッドを要約します。
ペアワイズ配列を使用する
ペアワイズ配列は、JSONを文字列として表現する柔軟性と、より構造化されたアプローチのパフォーマンスのバランスを提供します。このスキーマは柔軟であり、新しいフィールドをルートに追加することが可能です。しかし、これにはかなり複雑なクエリ構文が必要であり、ネスト構造とは互換性がありません。
例えば、以下のテーブルを考えてみます:
このテーブルに挿入するには、JSONをキーと値のリストとして構築する必要があります。次のクエリは、JSONExtractKeysAndValuesを使用してこれを達成する方法を示しています:
リクエストカラムが文字列として表されたネストされた構造が保持されています。ルートに任意の新しいキーを追加することも可能です。また、JSON自体に任意の違いを持つこともできます。ローカルテーブルに挿入するには、次のクエリを実行してください:
この構造をクエリするには、indexOf関数を使用して必要なキーのインデックスを特定する必要があります（これは値の順序と一致する必要があります）。これを使用して値の配列カラムをアクセスします。つまり、values[indexOf(keys, 'status')]を用います。リクエストカラムに対してはJSON解析メソッドが必要になります - この場合、simpleJSONExtractStringを使用します。