Google
Cloud Platform
Amazon Web Services
Microsoft Azureバリアント データのクエリ
プレビュー
この機能はパブリックプレビュー段階です。
この記事では、 VARIANTとして保存された半構造化データをクエリおよび変換するために使用できる Databricks SQL 演算子について説明します。 VARIANTデータ型は、Databricks Runtime 15.3 以降で使用できます。
Databricks では、JSON 文字列よりもVARIANTを使用することをお勧めします。 現在 JSON 文字列を使用しており、移行を検討しているユーザーは、 「バリアント型と JSON 文字列の違いは何ですか?」を参照してください。
JSON 文字列で保存された半構造化データのクエリ例を確認するには、 「JSON 文字列のクエリ」を参照してください。
バリアント列を含むテーブルを作成する
次のクエリを実行して、高度にネストされたデータが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 |
| } | } |
+-------------------+------------------+
パスが見つからない場合、または array-index が範囲外の場合、結果は 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値がデータに格納されるため、これらは 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|
+--------+------------+------------------+----------------------+