Consultar dados de variantes

VARIANT é um tipo de dado semi-estruturado que armazena dados semelhantes a JSON num formato binário auto-descritivo. O VARIANT tipo de dados está disponível no Databricks Runtime 15.3 e superior.

Azure Databricks recomenda usar VARIANT em vez de strings JSON para dados semi-estruturados. Para usuários que atualmente usam cadeias de caracteres JSON que desejam migrar, consulte Como a variante é diferente das cadeias de caracteres JSON?.

Para consultar dados semi-estruturados armazenados como cadeias JSON, veja Consultar cadeias JSON.

Criar uma tabela com uma coluna variante

Para criar uma coluna variante, use a parse_json função (SQL ou Python).

Execute o seguinte para criar uma tabela com dados altamente aninhados armazenados como VARIANT. (Estes dados são usados noutros exemplos desta página.)

Python

# 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

Campos de consulta em uma coluna variante

Para extrair campos de uma coluna variante, use a variant_get função (SQL ou Python) que especifica o nome do campo JSON no seu caminho de extração. Os nomes de campo diferenciam sempre maiúsculas de minúsculas.

Python

# 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

Também pode usar a sintaxe SQL para consultar campos numa coluna variante. Veja a abreviação do SQL para variant_get.

Abreviação SQL para variant_get

A sintaxe SQL para consultar strings JSON e outros tipos de dados complexos no Azure Databricks aplica-se aos VARIANT dados, incluindo os seguintes:

• Use : para selecionar campos de nível superior.
• Use . ou [<key>] para selecionar campos aninhados com chaves nomeadas.
• Use [<index>] para selecionar valores de matrizes.

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

A sintaxe ['<field>'] permite escapar qualquer caráter especial num nome de campo, incluindo espaços, pontos (.), dois-pontos (:) e colchetes ([ ]). O Azure Databricks recomenda esta sintaxe para cada nome de campo que contenha um carácter especial. Por exemplo, use raw:['zip.code'] para selecionar um campo chamado zip.code, ou raw:['A[1]'] para selecionar um campo chamado A[1].

Os backticks também escapam de nomes de campos que contêm espaços ou dois-pontos, mas não escapam de pontos ou colchetes. Um nome de campo que contenha um ponto final ou parênteses retos devolve NULL quando é delimitado por plicas invertidas, por isso use a sintaxe ['<field>'] para esses nomes de campo.

Para extrair campos que contenham caracteres especiais no PySpark, use a mesma sintaxe de parênteses no variant_get caminho de extração:

# 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"))

Extrair campos aninhados variáveis

Para extrair campos aninhados de uma coluna variante, especifique-os usando notação de pontos ou colchetes. Os nomes de campo diferenciam sempre maiúsculas de minúsculas.

Python

# 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()

Se não for possível encontrar um caminho, o resultado será null do tipo VariantVal.

SQL

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

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

Se não for possível encontrar um caminho, o resultado será NULL do tipo VARIANT.

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

Extrair valores de matrizes variantes

Para extrair elementos de arrays, índice com parênteses. Os índices são baseados em 0.

Python

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

Se o caminho não puder ser encontrado, ou se o índice do array estiver fora dos limites, o resultado é nulo.

Trabalhar com variantes em Python

Podes extrair variantes dos DataFrames do Spark para Python como VariantVal e trabalhar com elas individualmente usando os métodos toPython e 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()

Produza a VariantVal como uma cadeia JSON:

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

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

Converter a VariantVal num objeto 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

Também podes construir VariantVal usando a VariantVal.parseJson função.

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

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

Imprima a variante como uma string JSON:

print(variant.toJson())

{"a":1}

Converte a variante para um objeto Python e imprime um valor:

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

1

Devolver o esquema de uma variante

Para devolver o esquema de uma variante, use a schema_of_variant função (SQL ou Python).

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;

Para devolver os esquemas combinados de todas as variantes de um grupo, use a schema_of_variant_agg função (SQL ou Python).

Os exemplos seguintes retornam o esquema e depois o esquema combinado para os dados json_datade exemplo .

Python

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

Python

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

Nivelar objetos e matrizes variantes

A variant_explode função geradora de valores de tabela (SQL ou Python) pode ser usada para achatar arrays e objetos variantes.

Python

Use a API DataFrame da função de valor de tabela (TVF) para expandir uma variante em várias linhas:

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

Como variant_explode é uma função geradora, use-a como parte da cláusula FROM em vez de na lista SELECT, como nos exemplos a seguir:

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]);

Regras de fundição de tipo variante

Você pode armazenar matrizes e escalares usando VARIANT o tipo. ** Ao tentar converter tipos variante para outros, as regras normais de conversão aplicam-se a valores individuais e campos, com as seguintes regras adicionais.

Ao inferir o tipo com schema_of_variant ou schema_of_variant_agg, as funções voltam ao VARIANT tipo em vez do STRING tipo quando estão presentes tipos conflitantes que não podem ser resolvidos.

Python

Use a try_variant_get função (Python) para converter:

# 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

Use a try_variant_get função (SQL) para converter:

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

Também pode usar :: ou cast para converter valores em tipos de dados suportados:

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

Também use a try_variant_get função (SQL ou Python) para lidar com falhas de cast:

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'
)

Regras de variante nulas

Use a is_variant_null função (SQL ou Python) para determinar se o valor variante é um variant null.

Python

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

As variantes podem conter dois tipos de nulos:

• SQL NULL: Os SQL NULLs indicam que o valor está ausente. Estes são os mesmos NULL que se usa quando se lida com dados estruturados.
• Variante NULL: A variante NULLs indica que a variante contém explicitamente um NULL valor. Estes não são os mesmos que SQL NULLs, porque o NULL valor é armazenado nos dados.

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

Recursos adicionais

• Como é que a variante é diferente das strings JSON?: Para uma comparação de VARIANT e strings JSON, incluindo orientação de migração para utilizadores que migram do armazenamento de cadeias JSON.
• Suporte de tipos variantes para Apache Iceberg e Delta Lake: Para a lista completa de VARIANT limitações e comportamentos específicos de Delta.