Consultar dados de variantes

info

Visualização

Esse recurso está em Public Preview.

Este artigo descreve os operadores Databricks SQL que o senhor pode usar para consultar e transformar dados semiestruturados armazenados como VARIANT. O tipo de dados VARIANT está disponível em Databricks Runtime 15.3 e acima.

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

Execute a seguinte consulta para criar uma tabela com dados altamente aninhados armazenados como VARIANT. Todos os exemplos deste artigo fazem referência a essa tabela.

SQL

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

Campos de consulta em uma coluna variante

A sintaxe para consulta de strings JSON e outros tipos de dados complexos na Databricks se aplica aos dados VARIANT, incluindo o seguinte:

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

nota

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 um campo de variante de nível superior

Para extrair um campo, especifique o nome do campo JSON em seu caminho de extração. Os nomes dos campos sempre diferenciam maiúsculas de minúsculas.

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 um caminho não puder ser encontrado, o resultado será NULL do tipo VARIANT.

Extraia campos aninhados de variantes

Você especifica campos aninhados por meio de notação de pontos ou usando colchetes. Os nomes dos campos sempre diferenciam maiúsculas de minúsculas.

SQL

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

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

SQL

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

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

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

Extraia valores de matrizes variantes

Você indexa elementos em matrizes com 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á NULL.

Nivelar objetos e matrizes variantes

A função geradora de valores de tabela variant_explode pode ser usada para nivelar matrizes e objetos VARIANT.

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:store);

+-------+--------------------+
|    key|               value|
+-------+--------------------+
| basket|[[1,2,{"a":"x","b...|
|bicycle|{"color":"red","p...|
|   book|[{"author":"Nigel...|
|  fruit|[{"type":"apple",...|
+-------+--------------------+

SQL

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

+---+-----------------+
|pos|            value|
+---+-----------------+
|  0|                1|
|  1|                2|
|  2|{"a":"x","b":"y"}|
+---+-----------------+

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.

Você pode usar :: ou cast para converter valores em tipos de dados compatíveis.

SQL

-- price is returned as a double, not a string
SELECT raw:store.bicycle.price::double FROM store_data

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

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

Regras nulas variantes

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.

Use a função is_variant_null para determinar se o valor da variante é uma variante NULL.

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