Google
Cloud Platform
Amazon Web Services
Microsoft AzureConsultar dados semiestruturados no Databricks
Este artigo descreve os operadores Databricks SQL que você pode usar para query e transformar dados semiestruturados armazenados como JSON.
Observação
Esse recurso permite ler dados semiestruturados sem achatar os arquivos. No entanto, para desempenho query de leitura ideal, o Databricks recomenda que você extraia colunas aninhadas com os tipos de dados corretos.
Você extrai uma coluna de campos contendo strings JSON usando a sintaxe <column-name>:<extraction-path>, em que <column-name> é o nome da coluna de string e <extraction-path> é o caminho para o campo a ser extraído. Os resultados retornados são strings.
Crie uma tabela com dados altamente aninhados
execução da query a seguir para criar uma tabela com dados altamente aninhados. Todos os exemplos neste artigo fazem referência a esta tabela.
CREATE TABLE store_data AS SELECT
'{
"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
Extrair uma coluna de nível superior
Para extrair uma coluna, especifique o nome do campo JSON em seu caminho de extração.
Você pode fornecer nomes de coluna entre colchetes. As colunas referenciadas entre colchetes são casadas com distinção entre maiúsculas e minúsculas. O nome da coluna também é referenciado sem distinção entre maiúsculas e minúsculas.
SELECT raw:owner, RAW:owner FROM store_data
+-------+-------+
| owner | owner |
+-------+-------+
| amy | amy |
+-------+-------+
-- References are case sensitive when you use brackets
SELECT raw:OWNER case_insensitive, raw:['OWNER'] case_sensitive FROM store_data
+------------------+----------------+
| case_insensitive | case_sensitive |
+------------------+----------------+
| amy | null |
+------------------+----------------+
Use acentos graves para escapar de espaços e caracteres especiais. Os nomes dos campos não diferenciam maiúsculas de minúsculas.
-- Use backticks to escape special characters. References are case insensitive when you use backticks.
-- Use brackets to make them case sensitive.
SELECT raw:`zip code`, raw:`Zip Code`, raw:['fb:testid'] FROM store_data
+----------+----------+-----------+
| zip code | Zip Code | fb:testid |
+----------+----------+-----------+
| 94025 | 94025 | 1234 |
+----------+----------+-----------+
Observação
Se um registro JSON contiver várias colunas que podem corresponder ao seu caminho de extração devido à correspondência que não diferencia maiúsculas de minúsculas, você receberá um erro solicitando o uso de colchetes. Se você tiver correspondências de colunas em linhas, não receberá nenhum erro. O seguinte gerará um erro: {"foo":"bar", "Foo":"bar"}, e o seguinte não gerará um erro:
{"foo":"bar"}
{"Foo":"bar"}
Extrair campos aninhados
Você especifica campos aninhados por meio de notação de ponto ou usando colchetes. Quando você usa colchetes, as colunas são comparadas com distinção entre maiúsculas e minúsculas.
-- Use dot notation
SELECT raw:store.bicycle FROM store_data
-- the column returned is a string
+------------------+
| bicycle |
+------------------+
| { |
| "price":19.95, |
| "color":"red" |
| } |
+------------------+
-- Use brackets
SELECT raw:store['bicycle'], raw:store['BICYCLE'] FROM store_data
+------------------+---------+
| bicycle | BICYCLE |
+------------------+---------+
| { | null |
| "price":19.95, | |
| "color":"red" | |
| } | |
+------------------+---------+
Extrair valores de arrays
Você indexa elementos em arrays com colchetes. Os índices são baseados em 0. Você pode usar um asterisco (*) seguido de ponto ou notação de colchetes para extrair subcampos de todos os elementos em uma matriz.
-- Index elements
SELECT raw:store.fruit[0], raw:store.fruit[1] FROM store_data
+------------------+-----------------+
| fruit | fruit |
+------------------+-----------------+
| { | { |
| "weight":8, | "weight":9, |
| "type":"apple" | "type":"pear" |
| } | } |
+------------------+-----------------+
-- Extract subfields from arrays
SELECT raw:store.book[*].isbn FROM store_data
+--------------------+
| isbn |
+--------------------+
| [ |
| null, |
| "0-553-21311-3", |
| "0-395-19395-8" |
| ] |
+--------------------+
-- Access arrays within arrays or structs within arrays
SELECT
raw:store.basket[*],
raw:store.basket[*][0] first_of_baskets,
raw:store.basket[0][*] first_basket,
raw:store.basket[*][*] all_elements_flattened,
raw:store.basket[0][2].b subfield
FROM store_data
+----------------------------+------------------+---------------------+---------------------------------+----------+
| basket | first_of_baskets | first_basket | all_elements_flattened | subfield |
+----------------------------+------------------+---------------------+---------------------------------+----------+
| [ | [ | [ | [1,2,{"b":"y","a":"x"},3,4,5,6] | y |
| [1,2,{"b":"y","a":"x"}], | 1, | 1, | | |
| [3,4], | 3, | 2, | | |
| [5,6] | 5 | {"b":"y","a":"x"} | | |
| ] | ] | ] | | |
+----------------------------+------------------+---------------------+---------------------------------+----------+
Valores de elenco
Você pode usar :: para converter valores em tipos de dados básicos. Use o método from_json para converter resultados aninhados em tipos de dados mais complexos, como matrizes ou structs.
-- price is returned as a double, not a string
SELECT raw:store.bicycle.price::double FROM store_data
+------------------+
| price |
+------------------+
| 19.95 |
+------------------+
-- use from_json to cast into more complex types
SELECT from_json(raw:store.bicycle, 'price double, color string') bicycle FROM store_data
-- the column returned is a struct containing the columns price and color
+------------------+
| bicycle |
+------------------+
| { |
| "price":19.95, |
| "color":"red" |
| } |
+------------------+
SELECT from_json(raw:store.basket[*], 'array<array<string>>') baskets FROM store_data
-- the column returned is an array of string arrays
+------------------------------------------+
| basket |
+------------------------------------------+
| [ |
| ["1","2","{\"b\":\"y\",\"a\":\"x\"}]", |
| ["3","4"], |
| ["5","6"] |
| ] |
+------------------------------------------+
comportamento NULL
Quando existe um campo JSON com um valor null , você receberá um valor SQL null para essa coluna, não um valor de texto null .
select '{"key":null}':key is null sql_null, '{"key":null}':key == 'null' text_null
+-------------+-----------+
| sql_null | text_null |
+-------------+-----------+
| true | null |
+-------------+-----------+
Transformar uso aninhado de dados Operadores Spark SQL
O Apache Spark possui várias funções integradas para trabalhar com dados complexos e aninhados. O Notebook a seguir contém exemplos.
Além disso, as funções de ordem superior fornecem muitas opções adicionais quando os operadores Spark integrados não estão disponíveis para transformar os dados da maneira que você deseja.
