バリアント データにクエリを実行する

この記事では、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

バリアント列のクエリ フィールド

Azure Databricks で JSON 文字列やその他の複合データ型を照会するための構文は、次のような VARIANT データに適用されます。

• : を使用して、最上位レベルのフィールドを選択します。
• . または [<key>] を使用して、名前付きキーを持つ入れ子になったフィールドを選択します。
• [<index>] を使用して、配列から値を選択します。

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 型を使用して配列とスカラーを保存できます。 バリアント型を他の型にキャストしようとすると、通常のキャスト規則が個々の値とフィールドに適用され、次の追加規則が適用されます。

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: SQL NULL は値が見つからないことを示します。 これらは、構造化データを処理する場合と同じ NULL です。
• バリアント NULL: バリアント NULL は、バリアントに値 NULL が明示的に含まれていることを示します。 値 NULL はデータに格納されているため、これらは SQL NULL と同じではありません。

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|
+--------+------------+------------------+----------------------+