Consultar dados de variantes

Este artigo descreve como pode consultar e transformar dados semi-estruturados armazenados como VARIANT. O VARIANT tipo de dados está disponível no Databricks Runtime 15.3 e superior.

O Databricks recomenda o uso VARIANT de cadeias de caracteres JSON sobrepostas. Para usuários que atualmente usam cadeias de caracteres JSON que desejam migrar, consulte Como a variante é diferente das cadeias de caracteres JSON?.

Se você quiser ver exemplos para consultar dados semiestruturados armazenados com cadeias de caracteres JSON, consulte Consultar cadeias de caracteres 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.)

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

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

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.

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.

Python

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

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

-- Use backticks to escape special characters.
SELECT raw:`zip code`, raw:`fb:testid` FROM store_data

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

Se um nome de campo contiver um ponto (.), você deverá escapar dele entre colchetes ([ ]). Por exemplo, a consulta a seguir seleciona um campo chamado zip.code:

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

Extrair campos aninhados de variantes

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.

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.

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.

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

SQL

-- Index elements
SELECT raw:store.fruit[0], raw:store.fruit[1] FROM store_data

Python

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

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

SQL

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

Python

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

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 .

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;

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

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

Python

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

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

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

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

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.

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

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

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

SQL

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

Python

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

Regras nulas de variante

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

SQL

As variantes podem conter dois tipos de nulos:

• SQL NULL: SQL NULLs indicam que o valor está faltando. Estes são os mesmos NULLque 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|
+--------+------------+------------------+----------------------+

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