Semi-gestructureerde gegevens opvragen in Azure Databricks
In dit artikel worden de Databricks SQL-operators beschreven die u kunt gebruiken om semi-gestructureerde gegevens op te vragen en te transformeren die zijn opgeslagen als JSON.
Notitie
Met deze functie kunt u semi-gestructureerde gegevens lezen zonder de bestanden plat te maken. Voor optimale leesqueryprestaties raadt Databricks u echter aan geneste kolommen te extraheren met de juiste gegevenstypen.
U extraheert een kolom uit velden met JSON-tekenreeksen met behulp van de syntaxis <column-name>:<extraction-path>, waarbij <column-name> de kolomnaam van de tekenreeks is en <extraction-path> het pad naar het veld is dat moet worden geëxtraheerd. De geretourneerde resultaten zijn tekenreeksen.
Een tabel met zeer geneste gegevens maken
Voer de volgende query uit om een tabel met zeer geneste gegevens te maken. De voorbeelden in dit artikel verwijzen allemaal naar deze tabel.
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
Een kolom op het hoogste niveau extraheren
Als u een kolom wilt extraheren, geeft u de naam op van het JSON-veld in het extractiepad.
U kunt kolomnamen tussen vierkante haken opgeven. Kolommen waarnaar binnen vierkante haken wordt verwezen, worden hoofdlettergevoelig vergeleken. De kolomnaam wordt ook niet hoofdlettergevoelig verwezen.
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 |
+------------------+----------------+
Gebruik backticks om spaties en speciale tekens te escapen. De veldnamen komen niet hoofdlettergevoelig overeen.
-- 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 |
+----------+----------+-----------+
Notitie
Als een JSON-record meerdere kolommen bevat die overeenkomen met uw extractiepad vanwege niet-hoofdlettergevoelige overeenkomsten, krijgt u een foutmelding waarin u wordt gevraagd haakjes te gebruiken. Als u overeenkomsten hebt met kolommen in rijen, worden er geen fouten weergegeven. Het volgende genereert een fout: {"foo":"bar", "Foo":"bar"}, en het volgende genereert geen fout:
{"foo":"bar"}
{"Foo":"bar"}
Geneste velden extraheren
U geeft geneste velden op via punt notatie of met vierkante haken. Wanneer u vierkante haken gebruikt, worden kolommen hoofdlettergevoelig vergeleken.
-- 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" | |
| } | |
+------------------+---------+
Waarden extraheren uit matrices
U indexeert elementen in matrices met vierkante haken. Indexen zijn gebaseerd op 0. U kunt een sterretje (*) gevolgd door punt- of haak notatie gebruiken om subvelden uit alle elementen in een matrix te extraheren.
-- 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"} | | |
| ] | ] | ] | | |
+----------------------------+------------------+---------------------+---------------------------------+----------+
Cast-waarden
U kunt :: waarden casten naar basisgegevenstypen. Gebruik de methode from_json om geneste resultaten te casten naar complexere gegevenstypen, zoals matrices of 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"] |
| ] |
+------------------------------------------+
NULL-gedrag
Wanneer een JSON-veld met een null waarde bestaat, ontvangt u een SQL-waarde null voor die kolom, niet een null tekstwaarde.
select '{"key":null}':key is null sql_null, '{"key":null}':key == 'null' text_null
+-------------+-----------+
| sql_null | text_null |
+-------------+-----------+
| true | null |
+-------------+-----------+
Geneste gegevens transformeren met Behulp van Spark SQL-operators
Apache Spark heeft een aantal ingebouwde functies voor het werken met complexe en geneste gegevens. Het volgende notebook bevat voorbeelden.
Bovendien bieden functies met een hogere volgorde veel extra opties wanneer ingebouwde Spark-operators niet beschikbaar zijn voor het transformeren van gegevens zoals u wilt.