Consultar os dados de grade
Importante
Esse recurso está em uma versão prévia.
Este artigo descreve os operadores do Databricks SQL que você pode usar para consultar e transformar dados armazenados semiestruturados como VARIANT. O tipo de dados VARIANT está disponível em Databricks Runtime 15.3 e superior.
O Databricks recomenda o uso VARIANT em cadeias de caracteres do JSON. Para usuários que atualmente usam cadeias de caracteres do JSON que procuram migrar, consulte Como a grade é diferente das cadeias de caracteres do JSON?.
Caso queira visualizar exemplos para consultar dados semi-estruturados armazenados com cadeias de caracteres do JSON, consulte Cadeias de caracteres do JSON de consulta.
Observação
VARIANT colunas não podem ser usadas para chaves de clustering, 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 de grade
Execute a consulta a seguir para criar uma tabela com dados altamente aninhados armazenados como VARIANT. Todos os exemplos neste artigo referenciam 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
Consultar campos em uma coluna de grade
A sintaxe para consultar cadeias de caracteres do JSON e outros tipos de dados complexos no Azure Databricks se aplica aos dados VARIANT, incluindo o seguinte:
- Use
:para selecionar campos de nível superior. - Use
.ou[<key>]selecione campos aninhados com chaves nomeadas. - Use
[<index>]para selecionar valores de matrizes.
Observação
Se um nome de campo contiver um ponto (.), você deve evitá-lo com colchetes ([ ]). Por exemplo, a consulta apresentada a seguir seleciona um campo denominado zip.code:
SELECT raw:['zip.code'] FROM store_data
Extrair um campo de grade de nível superior
Para extrair um campo, especifique o nome do campo do JSON no seu caminho de extração. Nomes de campo sempre diferenciam 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 um caminho não puder ser encontrado, o resultado será NULL do tipo VARIANT.
Extrair campos aninhados de grade
Especifique campos aninhados por meio de notação de ponto ou usando colchetes. Nomes de campo sempre diferenciam 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 um caminho não puder ser encontrado, o resultado será NULL do tipo VARIANT.
Extrair valores de matrizes de grades
Indexe elementos em matrizes usando 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 de grades
A variant_explode função gerador com valor de tabela pode ser usada para nivelar VARIANT matrizes e objetos.
Como variant_explode é uma função geradora, você a usa como parte da cláusula FROM em vez de na lista SELECT, como nos seguintes exemplos:
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 conversão de tipo de grade
Você pode armazenar matrizes e escalares usando o tipo VARIANT. Ao tentar converter tipos de grade para outros tipos, as regras de conversão normais se aplicam a valores e campos individuais, com as regras adicionais a seguir.
Observação
variant_get e try_variant_get usa argumentos de tipo e segue essas regras de conversão.
| Tipo de origem | Comportamento |
|---|---|
VOID |
O resultado é um NULL de tipo VARIANT. |
ARRAY<elementType> |
O elementType deve ser um tipo que pode 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 de do tipo STRING, quando tipos conflitantes estão presentes que não podem ser resolvidos.
Use o :: ou cast converter valores em tipos de dados com suporte.
-- 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 grades
As grades podem conter dois tipos de nulos:
- SQL
NULL: SQLsNULLindicam que o valor está ausente. Eles são os mesmos queNULLao lidar com dados estruturados. - Grade
NULL: gradesNULLindicam que a grade contém explicitamente um valorNULL. Eles não são iguais ao SQLNULLs, pois o valorNULLé armazenado nos dados.
Use a função is_variant_null para determinar se o valor da grade é 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|
+--------+------------+------------------+----------------------+