バリアント データにクエリを実行する
重要
この機能はパブリック プレビュー段階にあります。
この記事では、VARIANT として格納されている半構造化データのクエリと変換に使用できる Databricks SQL 演算子について説明します。 VARIANT データ型は、Databricks Runtime 15.3 以降で使用できます。
Databricks では、JSON 文字列より優先して VARIANT を使用することをお勧めします。 移行を検討中の現在 JSON 文字列を使用しているユーザーの場合は、「バリアントと JSON 文字列の違い」を参照してください。
JSON 文字列を使用して格納された半構造化データにクエリを実行する例については、「JSON 文字列のクエリを実行する」を参照してください。
Note
VARIANT 列は、クラスタリング・キー、パーティション、または Z オーダー・キーには使用できません。 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
バリアント列のクエリ フィールド
Azure Databricks で JSON 文字列やその他の複合データ型を照会するための構文は、次のような VARIANT データに適用されます。
:を使用して、最上位レベルのフィールドを選択します。.または[<key>]を使用して、名前付きキーを持つ入れ子になったフィールドを選択します。[<index>]を使用して、配列から値を選択します。
Note
フィールド名にピリオド (.) が含まれている場合、角かっこ ([ ]) でエスケープする必要があります。 たとえば、次のクエリでは 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" |
+----------+-----------+
パスが見つからない場合、結果は NULL 型の VARIANT になります。
バリアントの入れ子になったフィールドを抽出する
入れ子になったフィールドは、ドット表記または角かっこを使用して指定します。 フィールド名は常に、大文字小文字が区別されます。
-- 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 |
| } |
+------------------+
パスが見つからない場合、結果は NULL 型の VARIANT になります。
バリアント配列から値を抽出する
角かっこを使用して、配列の要素のインデックスを指定します。 インデックスは 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 はジェネレーター関数であるため、次の例のように、FROM リスト内ではなく、SELECT 句の一部として使用します。
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 型を使用して配列とスカラーを保存できます。 バリアント型を他の型にキャストしようとすると、通常のキャスト規則が個々の値とフィールドに適用され、次の追加規則が適用されます。
Note
variant_get と try_variant_get は型引数を受け取り、これらのキャスト規則に従います。
| 変換元の型 | Behavior |
|---|---|
VOID |
結果は NULL 型の VARIANT です。 |
ARRAY<elementType> |
elementType は、VARIANT にキャストできる型である必要があります。 |
schema_of_variant または schema_of_variant_agg を使用して型を推論する場合、解決できない競合する型が存在する場合、関数は VARIANT 型ではなく STRING 型にフォールバックします。
:: または 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はデータに格納されているため、これらは SQLNULLと同じではありません。
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|
+--------+------------+------------------+----------------------+