Dotazování částečně strukturovaných dat v Azure Databricks
Tento článek popisuje operátory SQL Databricks, které můžete použít k dotazování a transformaci částečně strukturovaných dat uložených ve formátu JSON.
Poznámka
Tato funkce umožňuje číst částečně strukturovaná data bez zploštění souborů. Pro optimální výkon dotazů pro čtení však Databricks doporučuje extrahovat vnořené sloupce se správnými datovými typy.
Extrahujete sloupec z polí obsahujících řetězce JSON pomocí syntaxe <column-name>:<extraction-path>, kde <column-name> je název sloupce řetězce a <extraction-path> je cesta k poli, které se má extrahovat. Vrácené výsledky jsou řetězce.
Vytvoření tabulky s vysoce vnořenými daty
Spuštěním následujícího dotazu vytvořte tabulku s vysoce vnořenými daty. Všechny příklady v tomto článku odkazují na tuto tabulku.
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
Extrahování sloupce nejvyšší úrovně
Pokud chcete extrahovat sloupec, zadejte název pole JSON v cestě extrakce.
Názvy sloupců můžete zadat v hranatých závorkách. Sloupce odkazované v závorkách se rozlišují malá a velká písmena. Na název sloupce se také odkazuje bez rozlišování velkých a malých písmen.
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 |
+------------------+----------------+
Pomocí zpětných znamétek můžete řídicí mezery a speciální znaky. Názvy polí se nerozlišují malá a velká písmena.
-- 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 |
+----------+----------+-----------+
Poznámka
Pokud záznam JSON obsahuje více sloupců, které můžou odpovídat vaší cestě extrakce kvůli porovnávání bez rozlišování malých a malých písmen, zobrazí se chyba s výzvou k použití hranatých závorek. Pokud máte shody sloupců napříč řádky, nezobrazí se žádné chyby. Následující příkaz vyvolá chybu: {"foo":"bar", "Foo":"bar"}a následující chyba se nevyvolá:
{"foo":"bar"}
{"Foo":"bar"}
Extrahování vnořených polí
Zadáváte vnořená pole prostřednictvím zápisu tečky nebo pomocí hranatých závorek. Při použití závorek se sloupce rozlišují malá a velká písmena.
-- 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" | |
| } | |
+------------------+---------+
Extrahování hodnot z polí
Prvky indexujete v polích s hranatými závorkami. Indexy jsou založené na 0. Můžete použít hvězdičku (*) následovanou tečkou nebo zápisem závorek k extrakci dílčích polí ze všech prvků v poli.
-- 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"} | | |
| ] | ] | ] | | |
+----------------------------+------------------+---------------------+---------------------------------+----------+
Přetypování hodnot
Hodnoty můžete přetypovat :: na základní datové typy. Pomocí metody from_json přetypujte vnořené výsledky do složitějších datových typů, jako jsou pole nebo struktury.
-- 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"] |
| ] |
+------------------------------------------+
Chování NULL
Pokud pole JSON s null hodnotou existuje, obdržíte pro tento sloupec hodnotu SQL null , nikoli textovou null hodnotu.
select '{"key":null}':key is null sql_null, '{"key":null}':key == 'null' text_null
+-------------+-----------+
| sql_null | text_null |
+-------------+-----------+
| true | null |
+-------------+-----------+
Transformace vnořených dat pomocí operátorů Spark SQL
Apache Spark obsahuje řadu předdefinovaných funkcí pro práci se složitými a vnořenými daty. Následující poznámkový blok obsahuje příklady.
Kromě toho funkce vyššího pořadí poskytují mnoho dalších možností, když integrované operátory Sparku nejsou k dispozici pro transformaci dat požadovaným způsobem.