Fråga halvstrukturerade data i Azure Databricks
I den här artikeln beskrivs de Databricks SQL-operatorer som du kan använda för att fråga efter och transformera halvstrukturerade data som lagras som JSON.
Kommentar
Med den här funktionen kan du läsa halvstrukturerade data utan att platta ut filerna. För optimal läsfråga rekommenderar Databricks dock att du extraherar kapslade kolumner med rätt datatyper.
Du extraherar en kolumn från fält som innehåller JSON-strängar med hjälp av syntaxen <column-name>:<extraction-path>, där <column-name> är strängkolumnnamnet och <extraction-path> är sökvägen till fältet som ska extraheras. De returnerade resultaten är strängar.
Skapa en tabell med mycket kapslade data
Kör följande fråga för att skapa en tabell med mycket kapslade data. Exemplen i den här artikeln refererar alla till den här tabellen.
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
Extrahera en kolumn på den översta nivån
Om du vill extrahera en kolumn anger du namnet på JSON-fältet i din extraheringssökväg.
Du kan ange kolumnnamn inom hakparenteser. Kolumner som refereras inom hakparenteser matchas skiftläge känsligt. Kolumnnamnet refereras också till skiftlägesokänsligt.
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 |
+------------------+----------------+
Använd backticks för att undkomma blanksteg och specialtecken. Fältnamnen matchas skiftlägesokänsligt.
-- 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 |
+----------+----------+-----------+
Kommentar
Om en JSON-post innehåller flera kolumner som kan matcha din extraheringssökväg på grund av skiftlägesokänslig matchning får du ett felmeddelande där du uppmanas att använda hakparenteser. Om du har matchningar av kolumner över rader får du inga fel. Följande utlöser ett fel: {"foo":"bar", "Foo":"bar"}, och följande genererar inget fel:
{"foo":"bar"}
{"Foo":"bar"}
Extrahera kapslade fält
Du anger kapslade fält via punkt notation eller med hakparenteser. När du använder hakparenteser matchas kolumner skiftlägeskänsligt.
-- 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" | |
| } | |
+------------------+---------+
Extrahera värden från matriser
Du indexeras element i matriser med hakparenteser. Index är 0-baserade. Du kan använda en asterisk (*) följt av punkt- eller hakparentes notation för att extrahera underfält från alla element i en matris.
-- 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"} | | |
| ] | ] | ] | | |
+----------------------------+------------------+---------------------+---------------------------------+----------+
Gjutna värden
Du kan använda :: för att omvandla värden till grundläggande datatyper. Använd metoden from_json för att omvandla kapslade resultat till mer komplexa datatyper, till exempel matriser eller 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-beteende
När ett JSON-fält finns med ett null värde får du ett SQL-värde null för den kolumnen, inte ett null textvärde.
select '{"key":null}':key is null sql_null, '{"key":null}':key == 'null' text_null
+-------------+-----------+
| sql_null | text_null |
+-------------+-----------+
| true | null |
+-------------+-----------+
Transformera kapslade data med Spark SQL-operatorer
Apache Spark har ett antal inbyggda funktioner för att arbeta med komplexa och kapslade data. Följande notebook-fil innehåller exempel.
Dessutom ger högre ordningsfunktioner många ytterligare alternativ när inbyggda Spark-operatorer inte är tillgängliga för att transformera data som du vill.