クエリバリアントデータ
プレビュー
この機能は パブリック プレビュー段階です。
この記事では、 VARIANTとして格納された半構造化データのクエリと変換に使用できる Databricks SQL 演算子について説明します。 VARIANT データ型は、Databricks Runtime 15.3 以降で使用できます。
Databricks では、JSON 文字列よりも VARIANT を使用することをお勧めします。 現在 JSON 文字列を使用して移行を検討しているユーザーの場合は、「 バリアントと JSON 文字列の違い」を参照してください。
JSON 文字列で保存された半構造化データのクエリの例については、「 JSON 文字列のクエリ」を参照してください。
VARIANT 列は、クラスタリング キー、パーティション、または Z-Order キーには使用できません。 VARIANT データ型は、比較、グループ化、順序付け、およびセット操作には使用できません。制限事項の完全なリストについては、「 制限事項」を参照してください。
バリアント列を持つテーブルを作成する
次のクエリを実行して、高度にネストされたデータが VARIANTとして格納されたテーブルを作成します。 この記事の例はすべて、このテーブルを参照しています。
CREATE TABLE store_data AS
SELECT parse_json(
'{
"store":{
"fruit": [
{"weight":8,"type":"apple"},
{"weight":9,"type":"pear"}
],
"basket":[
[1,2,{"b":"y","a":"x"}],
[3,4],
[5,6]
],
"book":[
{
"author":"Nigel Rees",
"title":"Sayings of the Century",
"category":"reference",
"price":8.95
},
{
"author":"Herman Melville",
"title":"Moby Dick",
"category":"fiction",
"price":8.99,
"isbn":"0-553-21311-3"
},
{
"author":"J. R. R. Tolkien",
"title":"The Lord of the Rings",
"category":"fiction",
"reader":[
{"age":25,"name":"bob"},
{"age":26,"name":"jack"}
],
"price":22.99,
"isbn":"0-395-19395-8"
}
],
"bicycle":{
"price":19.95,
"color":"red"
}
},
"owner":"amy",
"zip code":"94025",
"fb:testid":"1234"
}'
) as raw
バリアント列のクエリフィールド
Databricks で JSON 文字列やその他の複雑なデータ型をクエリするための構文は、次のような VARIANT データに適用されます。
:を使用して、最上位のフィールドを選択します。.または[<key>]を使用して、名前付きキーを持つネストされたフィールドを選択します。[<index>]を使用して、配列から値を選択します。
フィールド名にピリオド (.) が含まれている場合は、角括弧 ([ ]) でエスケープする必要があります。 たとえば、次のクエリは zip.code という名前のフィールドを選択します。
SELECT raw:['zip.code'] FROM store_data
最上位のバリアントフィールドを抽出する
フィールドを抽出するには、抽出パスで JSON フィールドの名前を指定します。 フィールド名では、常に大文字と小文字が区別されます。
SELECT raw:owner FROM store_data
+-------+
| owner |
+-------+
| "amy" |
+-------+
-- Use backticks to escape special characters.
SELECT raw:`zip code`, raw:`fb:testid` FROM store_data
+----------+-----------+
| zip code | fb:testid |
+----------+-----------+
| "94025" | "1234" |
+----------+-----------+
パスが見つからない場合、結果は VARIANT型のNULLになります。
さまざまなネストされたフィールドを抽出します
ネストされたフィールドは、ドット表記または括弧を使用して指定します。 フィールド名は常に大文字と小文字が区別されます。
-- Use dot notation
SELECT raw:store.bicycle FROM store_data
+------------------+
| bicycle |
+------------------+
| { |
| "color":"red", |
| "price":19.95 |
| } |
+------------------+
-- Use brackets
SELECT raw:store['bicycle'] FROM store_data
+------------------+
| bicycle |
+------------------+
| { |
| "color":"red", |
| "price":19.95 |
| } |
+------------------+
パスが見つからない場合、結果は VARIANT型のNULLになります。
バリアント配列から値を抽出する
配列内の要素には角括弧でインデックスを付けます。 インデックスは 0 から始まります。
-- Index elements
SELECT raw:store.fruit[0], raw:store.fruit[1] FROM store_data
+-------------------+------------------+
| fruit | fruit |
+-------------------+------------------+
| { | { |
| "type":"apple", | "type":"pear", |
| "weight":8 | "weight":9 |
| } | } |
+-------------------+------------------+
パスが見つからない場合、または配列インデックスが範囲外の場合、結果は NULLになります。
バリアントオブジェクトと配列のフラット化
variant_explode テーブル値ジェネレータ関数を使用して、VARIANT配列とオブジェクトをフラット化できます。
variant_explode はジェネレータ関数であるため、次の例のように、SELECT リストではなく、FROM 句の一部として使用します。
SELECT key, value
FROM store_data,
LATERAL variant_explode(store_data.raw:store);
+-------+--------------------+
| key| value|
+-------+--------------------+
| basket|[[1,2,{"a":"x","b...|
|bicycle|{"color":"red","p...|
| book|[{"author":"Nigel...|
| fruit|[{"type":"apple",...|
+-------+--------------------+
SELECT pos, value
FROM store_data,
LATERAL variant_explode(store_data.raw:store.basket[0]);
+---+-----------------+
|pos| value|
+---+-----------------+
| 0| 1|
| 1| 2|
| 2|{"a":"x","b":"y"}|
+---+-----------------+
バリアント型のキャストルール
配列とスカラーは、 VARIANT 型を使用して格納できます。 バリアント型を他の型にキャストしようとすると、通常のキャストルールが個々の値とフィールドに適用され、さらに次のルールが追加されます。
variant_get try_variant_get型引数を受け取り、これらのキャスト規則に従う必要があります。
元の型 | 挙動 |
|---|---|
| 結果は、タイプ |
|
|
schema_of_variant または schema_of_variant_aggを使用して型を推論する場合、解決できない競合する型が存在する場合STRING関数は VARIANT 型ではなく型にフォールバックします。
:: または cast を使用して、サポートされているデータ型に値をキャストできます。
-- price is returned as a double, not a string
SELECT raw:store.bicycle.price::double FROM store_data
+------------------+
| price |
+------------------+
| 19.95 |
+------------------+
-- cast into more complex types
SELECT cast(raw:store.bicycle AS STRUCT<price DOUBLE, color STRING>) bicycle FROM store_data;
-- `::` also supported
SELECT raw:store.bicycle::STRUCT<price DOUBLE, color STRING> bicycle FROM store_data;
+------------------+
| bicycle |
+------------------+
| { |
| "price":19.95, |
| "color":"red" |
| } |
+------------------+
バリアントの null ルール
バリアントには、次の 2 種類の null を含めることができます。
- SQL
NULL: SQLNULLは、値が欠落していることを示します。これらは、構造化データを処理する場合と同じNULLです。 - バリアント
NULL: バリアントNULLは、バリアントにNULL値が明示的に含まれていることを示します。これらは、NULL値がデータに格納されるため、SQLNULLs と同じではありません。
is_variant_null 関数を使用して、バリアント値がバリアント NULLであるかどうかを判断します。
SELECT
is_variant_null(parse_json(NULL)) AS sql_null,
is_variant_null(parse_json('null')) AS variant_null,
is_variant_null(parse_json('{ "field_a": null }'):field_a) AS variant_null_value,
is_variant_null(parse_json('{ "field_a": null }'):missing) AS missing_sql_value_null
+--------+------------+------------------+----------------------+
|sql_null|variant_null|variant_null_value|missing_sql_value_null|
+--------+------------+------------------+----------------------+
| false| true| true| false|
+--------+------------+------------------+----------------------+