Pular para o conteúdo principal
Última atualização em

Consultar dados de variantes

info

Visualização

Esse recurso está em Public Preview.

Este artigo descreve como você pode consultar e transformar dados semiestruturados armazenados como VARIANT. O tipo de dados VARIANT está disponível no Databricks Runtime 15.3 e versões superiores.

A Databricks recomenda o uso do site VARIANT em vez de strings JSON. Para os usuários que usam atualmente JSON strings e desejam migrar, consulte Como a variante é diferente de JSON strings?

Se o senhor quiser ver exemplos de consulta de dados semiestruturados armazenados com JSON strings, consulte Query JSON strings .

nota

VARIANT não podem ser usadas para a chave clustering, partições ou chave Z-order. O tipo de dados VARIANT não pode ser usado para comparações, agrupamento, ordenação e operações de conjunto. Para obter uma lista completa das limitações, consulte Limitações.

Crie uma tabela com uma coluna variante​

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

execute o seguinte para criar uma tabela com dados altamente aninhados armazenados como VARIANT. (Esses dados são usados em outros exemplos nesta 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

Campos de consulta em uma coluna variante​

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

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

Você também pode usar a sintaxe SQL para consultar campos em uma coluna variante. Consulte a abreviação SQL para variant_get.

Abreviação SQL para variant_get​

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

  • Use : para selecionar campos de nível superior.
  • Use . ou [<key>] para selecionar campos aninhados com chave nomeada.
  • Use [<index>] para selecionar valores de matrizes.
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"    |
+----------+-----------+

Se o nome de um campo contiver um ponto (.), você deverá escapá-lo com colchetes ([ ]). Por exemplo, a consulta a seguir seleciona um campo chamado zip.code:

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

Extraia campos aninhados de variantes​

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

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

Se um caminho não puder ser encontrado, o resultado será NULL do tipo VARIANT.

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

Extraia valores de matrizes variantes​

Para extrair elementos de arrays, use índices entre colchetes. Os índices são baseados em 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     |
| }                 | }                |
+-------------------+------------------+

Se o caminho não for encontrado, ou se o índice da matriz estiver fora dos limites, o resultado será nulo.

Trabalhando com variantes em Python​

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

Gere o resultado VariantVal como uma string JSON :

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

Converter um VariantVal em um objeto 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

Você também pode construir VariantVal usando a função VariantVal.parseJson .

Python
# 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 :

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

Converta a variante em um objeto Python e imprima um valor:

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

Retorna o esquema de uma variante.​

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

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

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

Os exemplos a seguir retornam o esquema e, em seguida, o esquema combinado para os dados de exemplo 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> |
+----------------------------------------------------------------------------------------------------------------------------+

Nivelar objetos e matrizes variantes​

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

Como variant_explode é uma função geradora, você a usa como parte da cláusula FROM e não na lista SELECT, como nos exemplos a seguir:

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

Regras de transmissão do tipo variante​

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

nota

variant_get e try_variant_get pegam argumentos de tipo e seguem essas regras de conversão.

Tipo de origem

Comportamento

VOID

O resultado é um NULL do tipo VARIANT.

ARRAY<elementType>

O elementType deve ser um tipo que possa ser convertido em VARIANT.

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

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

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

Você também pode usar :: ou cast para converter valores para tipos de dados compatíveis:

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

Use também a função try_variant_get (SQL ou Python) para lidar com falhas de conversão:

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

Regras nulas variantes​

Use a função is_variant_null (SQL ou Python) para determinar se o valor da variante é uma variante nula.

As variantes podem conter dois tipos de nulos:

  • SQL NULL : SQL NULLs indica que o valor está faltando. Esses são os mesmos NULLs de quando se lida com dados estruturados.
  • Variante NULL : A variante NULLs indica que a variante contém explicitamente um valor NULL. Eles não são iguais ao SQL NULLs, porque o valor NULL é armazenado nos dados.
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|
+--------+------------+------------------+----------------------+