查詢 Azure Databricks 中的半結構化資料
本文說明可用來查詢和轉換儲存為 JSON 的半結構化資料的 Databricks SQL 運算子。
注意
這項功能可讓您讀取半結構化資料，而不需要壓平檔案。 不過，為了獲得最佳讀取查詢效能，Databricks 建議您擷取具有正確資料類型的巢狀資料行。
您可以使用 語法 <column-name>:<extraction-path> 從包含 JSON 字串的欄位擷取資料行，其中 <column-name> 是字串資料行名稱，而 <extraction-path> 是要擷取之欄位的路徑。 傳回的結果為字串。
建立具有高度巢狀資料的資料表
執行下列查詢以建立具有高度巢狀資料的資料表。 本文中的範例全都參考此資料表。
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
擷取最上層資料行
若要擷取資料行，請在擷取路徑中指定 JSON 欄位的名稱。
您可以在括弧內提供資料行名稱。 括弧內參考的資料行會區分大小寫 。 資料行名稱也以不區分大小寫的方式參考。
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 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 |
+----------+----------+-----------+
注意
如果 JSON 記錄包含多個資料行，這些資料行可能會因不區分大小寫的比對而符合您的擷取路徑，您會收到錯誤，要求您使用括弧。 如果您有跨資料列的資料行相符專案，則不會收到任何錯誤。 下列會擲回錯誤： {"foo":"bar", "Foo":"bar"} ，且下列專案不會擲回錯誤：
{"foo":"bar"}
{"Foo":"bar"}
擷取巢狀欄位
您可以透過點標記法或使用括弧來指定巢狀欄位。 當您使用括弧時，資料行會區分大小寫。
-- 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" | |
| } | |
+------------------+---------+
從陣列擷取值
您可以使用括弧在陣列中編制元素的索引。 索引是以 0 為基礎。 您可以使用星號 （ * ） 後面接著點或括弧標記法，從陣列中的所有元素擷取子欄位。
-- 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"} | | |
| ] | ] | ] | | |
+----------------------------+------------------+---------------------+---------------------------------+----------+
轉換值
您可以使用 :: 將值轉換成基本資料類型。 使用 from_json 方法，將巢狀結果轉換成更複雜的資料類型，例如陣列或結構。
-- 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 行為
當 JSON 欄位具有 null 值時，您會收到該資料行的 SQL null 值，而不是 null 文字值。
select '{"key":null}':key is null sql_null, '{"key":null}':key == 'null' text_null
+-------------+-----------+
| sql_null | text_null |
+-------------+-----------+
| true | null |
+-------------+-----------+
使用 Spark SQL 運算子轉換巢狀資料
Apache Spark 有數個內建函式，可用於處理複雜和巢狀資料。 下列筆記本包含範例。
此外，當內建的 Spark 運算子無法以您想要的方式轉換資料時， 較高順序的函 式會提供許多其他選項。