Consultar cadeias de caracteres JSON
Este artigo descreve os operadores SQL Databricks que você pode usar para consultar e transformar dados semiestruturados armazenados como cadeias de caracteres JSON.
Nota
Esse recurso permite que você leia dados semiestruturados sem nivelar os arquivos. No entanto, para um desempenho ideal em consultas de leitura, a Databricks recomenda que se extraiam columns aninhados com os tipos de dados corretos.
Você extrai um column de campos que contêm strings JSON usando a sintaxe <column-name>:<extraction-path>, where,<column-name> é o nome da string column e <extraction-path> é o caminho para o campo a ser extraído. Os resultados retornados são strings.
Execute a consulta a seguir para criar um table com dados altamente aninhados. Todos os exemplos neste artigo fazem referência a esta table.
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
Para extrair um column, especifique o nome do campo JSON no caminho de extração.
Pode fornecer nomes com column entre colchetes. Columns referenciado entre parênteses é diferenciado por maiúsculas e minúsculas em de forma sensível. O nome column 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 backticks para escapar de espaços e caracteres especiais. Os nomes dos campos são correspondidos sem distinção entre maiúsculas e 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 |
+----------+----------+-----------+
Nota
Se um registro JSON contiver vários columns 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 tiveres correspondências de columns entre linhas, não receberás nenhum erro. O seguinte irá gerar um erro: {"foo":"bar", "Foo":"bar"}, e o seguinte não irá lançar um erro:
{"foo":"bar"}
{"Foo":"bar"}
Você especifica campos aninhados por meio de notação de pontos ou usando colchetes. Quando usas colchetes, columns diferencia-se 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" | |
| } | |
+------------------+---------+
Você indexa elementos em matrizes com colchetes. Os índices são baseados em 0. Você pode usar um asterisco (*) seguido de notação de ponto ou colchete 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"} | | |
| ] | ] | ] | | |
+----------------------------+------------------+---------------------+---------------------------------+----------+
Você pode usar :: para converter values para 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"] |
| ] |
+------------------------------------------+
Quando existir um campo JSON com um valor null, receberá um valor SQL de null para esse column, e 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 |
+-------------+-----------+
O Apache Spark tem várias funções integradas para trabalhar com dados complexos e aninhados. O bloco de notas seguinte contém exemplos.
Além disso, as funções de ordem superior fornecem muitas opções adicionais quando os operadores integrados do Spark não estão disponíveis para transformar dados da maneira desejada.