メインコンテンツまでスキップ
に最終更新

バリアントデータのクエリー

備考

プレビュー

この機能は パブリック プレビュー段階です。

この記事では、 VARIANTとして保存された半構造化データをクエリおよび変換する方法について説明します。VARIANTデータ型は、Databricks Runtime 15.3 以降で使用できます。

Databricks では、JSON 文字列よりも VARIANT を使用することをお勧めします。 現在 JSON 文字列を使用して移行を検討しているユーザーの場合は、「 バリアントと JSON 文字列の違い」を参照してください。

JSON 文字列で保存された半構造化データのクエリの例については、 JSON 文字列のクエリを参照してください。

注記

VARIANT 列は、クラスタリングキー、パーティション、または Z-Order キーには使用できません。 VARIANT データ型は、比較、グループ化、順序付け、およびセット操作には使用できません。制限事項の完全なリストについては、 制限事項を参照してください。

バリアント列を持つテーブルを作成する​

バリアント列を作成するには、 parse_json関数 ( SQLまたはPython ) を使用します。

以下を実行して、高度にネストされたデータがVARIANTとして保存されたテーブルを作成します。(このデータはこのページの他の例でも使用されます。)

SQL
-- Create a table with a variant column
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

SELECT * FROM store_data

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

バリアント列からフィールドを抽出するには、抽出パスで JSON フィールドの名前を指定してvariant_get関数 ( SQLまたはPython ) を使用します。フィールド名では常に大文字と小文字が区別されます。

SQL
-- Extract a top-level field
SELECT variant_get(store_data.raw, '$.owner') AS owner FROM store_data

SQL 構文を使用してバリアント列のフィールドをクエリすることもできます。variant_get の SQL 省略形を参照してください。

variant_get の SQL 省略形​

Databricks で JSON 文字列やその他の複雑なデータ型をクエリするための SQL 構文は、次のものを含め、 VARIANTデータに適用されます。

  • :を使用して、最上位のフィールドを選択します。
  • . または [<key>] を使用して、名前付きキーを持つネストされたフィールドを選択します。
  • [<index>] を使用して、配列から値を選択します。
SQL
SELECT raw:owner FROM store_data
+-------+
| owner |
+-------+
| "amy" |
+-------+
SQL
-- Use backticks to escape special characters.
SELECT raw:`zip code`, raw:`fb:testid` FROM store_data
+----------+-----------+
| zip code | fb:testid |
+----------+-----------+
| "94025"  | "1234"    |
+----------+-----------+

フィールド名にピリオド (.) が含まれている場合は、角括弧 ([ ]) でエスケープする必要があります。 たとえば、次のクエリは zip.code という名前のフィールドを選択します。

SQL
SELECT raw:['zip.code'] FROM store_data

さまざまなネストされたフィールドを抽出します​

バリアント列からネストされたフィールドを抽出するには、ドット表記または括弧を使用して指定します。フィールド名では常に大文字と小文字が区別されます。

SQL
-- Use dot notation
SELECT raw:store.bicycle FROM store_data
SQL
-- Use brackets
SELECT raw:store['bicycle'] FROM store_data

パスが見つからない場合、結果は VARIANT型のNULLになります。

+-----------------+
| bicycle         |
+-----------------+
| {               |
| "color":"red",  |
| "price":19.95   |
| }               |
+-----------------+

バリアント配列から値を抽出する​

配列から要素を抽出するには、括弧でインデックスを付けます。インデックスは 0 から始まります。

SQL
-- 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 になります。

Pythonでバリアントを扱う​

Spark DataFrames からバリアントをVariantValとして Python に抽出し、 toPythonメソッドとtoJsonメソッドを使用して個別に操作することができます。

Python
# toPython
data = [
    ('{"name": "Alice", "age": 25}',),
    ('["person", "electronic"]',),
    ('1',)
]

df_person = spark.createDataFrame(data, ["json"])

# Collect variants into a VariantVal
variants = df_person.select(parse_json(col("json")).alias("v")).collect()

VariantValを JSON 文字列として出力します。

Python
print(variants[0].v.toJson())
{"age":25,"name":"Alice"}

VariantValを Python オブジェクトに変換します。

Python
# First element is a dictionary
print(variants[0].v.toPython()["age"])
25
Python
# Second element is a List
print(variants[1].v.toPython()[1])
electronic
Python
# Third element is an Integer
print(variants[2].v.toPython())
1

VariantVal.parseJson関数を使用してVariantValを構築することもできます。

Python
# parseJson to construct VariantVal's in Python
from pyspark.sql.types import VariantVal

variant = VariantVal.parseJson('{"a": 1}')

バリアントを JSON 文字列として出力します。

Python
print(variant.toJson())
{"a":1}

バリアントを Python オブジェクトに変換し、値を出力します。

Python
print(variant.toPython()["a"])
1

バリアントのスキーマを返す​

バリアントのスキーマを返すには、 schema_of_variant関数 ( SQLまたはPython ) を使用します。

SQL
-- Return the schema of the variant
SELECT schema_of_variant(raw) FROM store_data;

グループ内のすべてのバリアントの結合されたスキーマを返すには、 schema_of_variant_agg関数 ( SQLまたはPython ) を使用します。

次の例では、サンプル データjson_dataのスキーマを返し、次に結合されたスキーマを返します。

SQL
CREATE OR REPLACE TEMP VIEW json_data AS
SELECT '{"name": "Alice", "age": 25}' AS json UNION ALL
SELECT '{"id": 101, "department": "HR"}' UNION ALL
SELECT '{"product": "Laptop", "price": 1200.50, "in_stock": true}';

-- Return the schema
SELECT schema_of_variant(parse_json(json)) FROM json_data;
+-----------------------------------------------------------------+
| schema_of_variant(v)                                            |
+-----------------------------------------------------------------+
| OBJECT<age: BIGINT, name: STRING>                               |
| OBJECT<department: STRING, id: BIGINT>                          |
| OBJECT<in_stock: BOOLEAN, price: DECIMAL(5,1), product: STRING> |
+-----------------------------------------------------------------+
SQL
-- Return the combined schema
SELECT schema_of_variant_agg(parse_json(json)) FROM json_data;
+----------------------------------------------------------------------------------------------------------------------------+
| schema_of_variant(v)                                                                                                       |
+----------------------------------------------------------------------------------------------------------------------------+
| OBJECT<age: BIGINT, department: STRING, id: BIGINT, in_stock: BOOLEAN, name: STRING, price: DECIMAL(5,1), product: STRING> |
+----------------------------------------------------------------------------------------------------------------------------+

バリアントオブジェクトと配列のフラット化​

variant_explodeテーブル値ジェネレーター関数 ( SQLまたはPython ) を使用して、バリアント配列とオブジェクトをフラット化できます。

variant_explode はジェネレータ関数であるため、次の例のように、SELECT リストではなく、FROM 句の一部として使用します。

SQL
SELECT key, value
  FROM store_data,
  LATERAL variant_explode(store_data.raw);
SQL
SELECT pos, value
  FROM store_data,
  LATERAL variant_explode(store_data.raw:store.basket[0]);

バリアント型のキャストルール​

配列とスカラーは、 VARIANT 型を使用して格納できます。 バリアント型を他の型にキャストしようとすると、通常のキャストルールが個々の値とフィールドに適用され、さらに次のルールが追加されます。

注記

variant_get try_variant_get型引数を受け取り、これらのキャスト規則に従う必要があります。

元の型

挙動

VOID

結果は、タイプ VARIANTのNULLになります。

ARRAY<elementType>

elementTypeは、 VARIANTにキャストできる型である必要があります。

schema_of_variant または schema_of_variant_aggを使用して型を推論する場合、解決できない競合する型が存在する場合STRING関数は VARIANT 型ではなく型にフォールバックします。

try_variant_get関数 ( SQL ) を使用してキャストします。

SQL
-- price is returned as a double, not a string
SELECT try_variant_get(raw, '$.store.bicycle.price', 'double') as price FROM store_data
+------------------+
| price            |
+------------------+
| 19.95            |
+------------------+

::またはcastを使用して、サポートされているデータ型に値をキャストすることもできます。

SQL
-- 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"  |
| }                |
+------------------+

また、キャストの失敗を処理するには、 try_variant_get関数 ( SQLまたはPython ) を使用します。

SQL
SELECT try_variant_get(
  parse_json('{"a" : "c", "b" : 2}'),
  '$.a',
  'boolean'
)

バリアントの null ルール​

バリアント値がバリアント null であるかどうかを確認するには、 is_variant_null関数 ( SQLまたはPython ) を使用します。

バリアントには、次の 2 種類の null を含めることができます。

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