Запрос данных о вариантах

В этой статье описывается, как запрашивать и преобразовывать полуструктурированные данные, хранящиеся в виде VARIANT. Тип VARIANT данных доступен в Databricks Runtime 15.4 и выше.

Azure Databricks рекомендует использовать VARIANT вместо строк JSON для полуструктурированных данных. Для пользователей, которые в настоящее время используют строки JSON и хотят мигрировать, см. раздел Чем вариант отличается от JSON-строк?

Чтобы запросить полуструктурированные данные, хранящиеся в виде строк JSON, см. статью "Запрос строк JSON".

Примечание.

VARIANT столбцы нельзя использовать для кластеризации ключей, секций или ключей порядка Z. Тип данных VARIANT нельзя использовать для сравнения, группировки, упорядочивания и задания операций. Для полного списка ограничений см. Ограничения.

Создание таблицы с вариантным столбцом

Чтобы создать вариантный столбец, используйте функцию parse_json (SQL или Python).

Выполните следующую команду, чтобы создать таблицу с высоко вложенными данными, хранящимися как VARIANT. (Эти данные используются в других примерах на этой странице.)

Питон

# Create a table with a variant column
store_data='''
{
  "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"
}
'''

# Create a DataFrame
df = spark.createDataFrame([(store_data,)], ["json"])

# Convert to a variant
df_variant = df.select(parse_json(col("json")).alias("raw"))

# Alternatively, create the DataFrame directly
# df_variant = spark.range(1).select(parse_json(lit(store_data)))

df_variant.display()

# Write out as a table
df_variant.write.saveAsTable("store_data")

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

Поля запроса в столбце variant

Чтобы извлечь поля из варианта столбца, используйте variant_get функцию (SQL или Python), указав имя поля JSON в пути извлечения. Имена полей всегда чувствительны к регистру.

Питон

# Extract a top-level field
df_variant.select(variant_get(col("raw"), "$.owner", "string")).display()

SQL

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

Вы также можете использовать синтаксис SQL для запроса полей в столбце variant. См. сокращенную запись SQL для variant_get.

Сокращение SQL для variant_get

Синтаксис SQL для запроса строк JSON и других сложных типов данных в Azure Databricks применяется к VARIANT данным, включая следующие:

• Используется : для выбора полей верхнего уровня.
• Используйте . или [<key>], чтобы выбрать вложенные поля с именованными ключами.
• Используется [<index>] для выбора значений из массивов.

SELECT raw:owner FROM store_data

+-------+
| owner |
+-------+
| "amy" |
+-------+

-- Enclose a field name that contains special characters in single quotes inside square brackets.
SELECT raw:['zip code'], raw:['fb:testid'] FROM store_data

+----------+-----------+
| zip code | fb:testid |
+----------+-----------+
| "94025"  | "1234"    |
+----------+-----------+

Синтаксис ['<field>'] экранирует любой специальный символ в имени поля, включая пробелы, точки (.), двоеточия (:) и квадратные скобки ([ ]). Azure Databricks рекомендует этот синтаксис для каждого имени поля, содержащего специальный символ. Например, используйте raw:['zip.code'] для выбора поля с именем zip.codeили raw:['A[1]'] для выбора поля с именем A[1].

Обратные кавычки также экранируют имена полей, содержащие пробелы или двоеточия, но не экранируют точки или квадратные скобки. Имя поля, содержащее точку или квадратные скобки, при экранировании обратными кавычками возвращает NULL, поэтому для таких имён используйте синтаксис ['<field>'].

Чтобы извлечь поля со специальными символами в PySpark, используйте тот же синтаксис квадратных скобок в пути извлечения variant_get:

# Escape special characters in the extraction path
df_variant.select(variant_get(col("raw"), "$['zip.code']", "string"))
df_variant.select(variant_get(col("raw"), "$['A[1]']", "string"))

Извлечение вложенных полей вариантов

Чтобы извлечь вложенные поля из столбца варианта, укажите их с помощью нотации точек или квадратных скобок. Имена полей всегда чувствительны к регистру.

Питон

# Use dot notation
df_variant.select(variant_get(col("raw"), "$.store.bicycle", "string")).display()

# Use brackets
df_variant.select(variant_get(col("raw"), "$.store['bicycle']", "string")).display()

Если не удается найти путь, результат имеет null тип VariantVal.

SQL

-- Use dot notation
SELECT raw:store.bicycle FROM store_data

-- Use brackets
SELECT raw:store['bicycle'] FROM store_data

Если не удается найти путь, результат имеет NULL тип VARIANT.

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

Извлечение значений из массивов вариантов

Чтобы извлечь элементы из массивов, индексируйте с помощью квадратных скобок. Индексы начинаются с 0.

Питон

# Index elements
df_variant.select((variant_get(col("raw"), "$.store.fruit[0]", "string")),(variant_get(col("raw"), "$.store.fruit[1]", "string"))).display()

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 в Python VariantVal и работать с ними индивидуально, используя методы toPython и toJson.

# 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.

print(variants[0].v.toJson())

{"age":25,"name":"Alice"}

Преобразуйте VariantVal в объект Python.

# First element is a dictionary
print(variants[0].v.toPython()["age"])

25

# Second element is a List
print(variants[1].v.toPython()[1])

electronic

# Third element is an Integer
print(variants[2].v.toPython())

1

Можно также создать VariantVal с помощью VariantVal.parseJson функции.

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

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

Печать варианта в виде строки JSON:

print(variant.toJson())

{"a":1}

Преобразуйте вариант в объект Python и распечатайте значение:

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

1

Возвращает схему варианта

Чтобы вернуть схему варианта, используйте schema_of_variant функцию (SQL или Python).

Питон

# Return the schema of the variant
df_variant.select(schema_of_variant(col("raw"))).display()

SQL

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

Чтобы вернуть объединенные схемы всех вариантов в группе, используйте функцию schema_of_variant_agg (SQL или Python).

Следующие примеры возвращают схему, а затем объединенную схему для примера данных json_data.

Питон

json_data = [
    ('{"name": "Alice", "age": 25}',),
    ('{"id": 101, "department": "HR"}',),
    ('{"product": "Laptop", "price": 1200.50, "in_stock": true}',)
]

df_item = spark.createDataFrame(json_data, ["json"])

# Return the schema
df_item.select(parse_json(col("json")).alias("v")).select(schema_of_variant(col("v"))).display()

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

Питон

# Return the combined schema
df.select(parse_json(col("json")).alias("v")).select(schema_of_variant_agg(col("v"))).display()

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) можно использовать для преобразования вариантов массивов и объектов в плоский вид.

Питон

Используйте API фрейма данных с табличными значениями (TVF), чтобы распределить вариант на несколько строк.

spark.tvf.variant_explode(parse_json(lit(store_data))).display()

# To explode a nested field, first create a DataFrame with just the field
df_store_col = df_variant.select(variant_get(col("raw"), "$.store", "variant").alias("store"))

# Perform the explode with a lateral join and the outer function to return the new exploded DataFrame
df_store_exploded_lj = df_store_col.lateralJoin(spark.tvf.variant_explode(col("store").outer()))
df_store_exploded = df_store_exploded_lj.drop("store")
df_store_exploded.display()

SQL

Так как variant_explode это функция генератора, она используется как часть FROM предложения, а не в SELECT списке, как показано в следующих примерах:

SELECT key, value
  FROM store_data,
  LATERAL variant_explode(store_data.raw);

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

Правила приведения типов вариантов

Вы можете хранить массивы и скаляры с помощью VARIANT типа. При попытке приведения типов вариантов к другим типам обычные правила приведения применяются для отдельных значений и полей с помощью следующих дополнительных правил.

Примечание.

variant_get и try_variant_get принимают типы аргументов и следуют этим правилам приведения.

Тип источника	Поведение
VOID	Результатом является NULL типа VARIANT.
ARRAY<elementType>	Тип elementType должен быть таким, чтобы его можно было преобразовать в VARIANT.

При выводе типа с schema_of_variant или schema_of_variant_agg, функции возвращаются к типу VARIANT, а не к типу STRING при наличии конфликтующих типов, которые не могут быть разрешены.

Питон

Используйте функцию try_variant_get (Python) для приведения:

# price is returned as a double, not a string
df_variant.select(try_variant_get(col("raw"), "$.store.bicycle.price", "double").alias("price"))

+------------------+
| price            |
+------------------+
| 19.95            |
+------------------+

SQL

Используйте функцию try_variant_get (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 для приведения значений к поддерживаемым типам данных.

-- 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) для обработки сбоев преобразования:

Питон

spark.range(1).select(parse_json(lit('{"a" : "c", "b" : 2}')).alias("v")).select(try_variant_get(col('v'), '$.a', 'boolean')).display()

SQL

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

Вариантные правила NULL

Используйте функцию is_variant_null (SQL или Python), чтобы определить, является ли значение варианта значением null.

Питон

data = [
    ('null',),
    (None,),
    ('{"field_a" : 1, "field_b" : 2}',)
]

df = spark.createDataFrame(data, ["null_data"])
df.select(parse_json(col("null_data")).alias("v")).select(is_variant_null(col("v"))).display()

+------------------+
|is_variant_null(v)|
+------------------+
|              true|
+------------------+
|             false|
+------------------+
|             false|
+------------------+

SQL

Варианты могут содержать два типа null:

• SQL : SQL NULLNULLуказывает, что значение отсутствует. Это то же NULLсамое, что и при работе со структурированными данными.
• Variant NULL: Варианты NULL указывают, что вариант явно содержит NULL значение. Это не то же самое, что и SQL 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|
+--------+------------+------------------+----------------------+

Дополнительные ресурсы

• Чем variant отличается от строк JSON?: Сравнение VARIANT и строк JSON, включая рекомендации по миграции для пользователей, переходящих с хранения в виде строк JSON.
• Поддержка вариантных типов для Apache Iceberg и Delta Lake: Полный список ограничений VARIANT и особенностей поведения Delta Lake.