Partilhar via

Consultar dados de variantes

  • Artigo

Importante

Esta funcionalidade está em Pré-visualização Pública.

Este artigo descreve os operadores Databricks SQL que você pode usar para consultar e transformar dados semiestruturados 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.

Nota

VARIANT colunas não podem ser usadas para agrupar chaves, partições ou chaves de ordem Z. 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 de limitações, consulte Limitações.

Criar uma tabela com uma coluna variante

Execute a consulta a seguir para criar uma tabela com dados altamente aninhados armazenados como VARIANT. Todos os exemplos neste artigo fazem referência a esta 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 variante

A sintaxe para consultar cadeias de caracteres JSON e outros tipos de dados complexos no Azure Databricks aplica-se aos VARIANT dados, incluindo o seguinte:

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

Nota

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

Para extrair um campo, especifique o nome do campo JSON no caminho de extração. Os nomes de campo diferenciam sempre maiúsculas de 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

Você especifica campos aninhados por meio de notação de pontos ou usando colchetes. Os nomes de campo diferenciam sempre maiúsculas de 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 variantes

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

Nivelar objetos e matrizes variantes

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

Como variant_explode é uma função geradora, você a usa como parte da cláusula em vez de FROM na SELECT lista, 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

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

Nota

variant_get e try_variant_get pegue argumentos de tipo e siga estas regras de casting.

Source type Comportamento
VOID O resultado é do NULL tipo VARIANT.
ARRAY<elementType> O elementType deve ser um tipo que pode ser moldado para VARIANT.

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.

Você pode usar :: ou cast converter valores para tipos de dados suportados.

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

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.

Use a is_variant_null função 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|
+--------+------------+------------------+----------------------+