Consultar dados de variantes

Visualização

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

Criar uma tabela com uma coluna de variantes

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.

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 de variantes

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

Use : para selecionar os campos de nível superior.
Use . ou [<key>] para selecionar campos aninhados com chave nomeada.
Use [<index>] para selecionar valores de matrizes.

Observação

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:

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

Extrair 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 são sempre sensíveis a maiúsculas e minúsculas.

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 não for possível encontrar um caminho, o resultado será NULL do tipo VARIANT.

Extrair campos aninhados de variantes

O senhor especifica os campos aninhados por meio da notação de ponto ou usando colchetes. Os nomes dos campos são sempre sensíveis a maiúsculas e minúsculas.

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

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

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

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

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

Extrair valores de matrizes de variantes

O senhor indexa elementos em matrizes com colchetes. Os índices são baseados em 0.

-- 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 da matriz estiver fora dos limites, o resultado será NULL.

Achatar objetos e matrizes variantes

A função geradora com valor de tabela variant_explode pode ser usada para achatar matrizes e objetos VARIANT.

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

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

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 fundição de tipo variante

O senhor pode armazenar arrays e scalars 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.

Observação

variant_get e try_variant_get recebem 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 há tipos conflitantes que não podem ser resolvidos.

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

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

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

-- 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 de variantes nulas

As variantes podem conter dois tipos de nulos:

SQL NULL: SQL NULLs indicam que o valor está faltando. Esses são os mesmos NULLs usados para lidar com dados estruturados.
Variante NULL: A variante NULLs indica que a variante contém explicitamente um valor NULL. Eles não são iguais aos NULLs do SQL, pois o valor NULL é armazenado nos dados.

Use a função is_variant_null para determinar se o valor da variante é uma variante 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|
+--------+------------+------------------+----------------------+