在“數據庫”中查詢半結構化數據
請注意
在Databricks Runtime 8.1及以上版本中可用。
本文描述了Databricks SQL操作符，可用於查詢和轉換存儲為JSON的半結構化數據。
請注意
該特性允許您讀取半結構化的數據，而無需將文件壓平。但是，為了獲得最佳的讀查詢性能，Databricks建議您提取具有正確數據類型的嵌套列。
您可以使用該語法從包含JSON字符串的字段中提取列<列名稱>:< extraction-path >,在那裏<列名稱>字符串列名稱和< extraction-path >是到要提取的字段的路徑。返回的結果是字符串。
創建具有高度嵌套數據的表
運行以下查詢以創建具有高度嵌套數據的表。本文中的示例都參考了這個表。
創建表格store_data作為選擇”{“存儲”:{“水果”:[{“重量”:8,“類型”:“蘋果”},{“重量”:9,“類型”:“梨”}),“籃子”:((1、2、{“b”:“y”,“一個”:“x”}),(3、4),(5、6)),“書”:(｛奈傑爾•裏斯”“作者”:“題目:“世紀語錄”，“類別”:“引用”,“價格”:8.95}，｛“作者”:“赫爾曼·麥爾維爾”,“標題”:“白鯨記”,“類別”:“小說”,“價格”:8.99,“isbn”:“0-553-21311-3”}，｛“作者”:“J。r·r·托爾金”,“標題”:《指環王》，“類別”:“小說”,“讀者”:({“年齡”:25歲的“名稱”:“bob”},{“年齡”:26日,“名字”:“傑克”}),“價格”:22.99,“isbn”:“0-395-19395-8”｝),“自行車”:{“價格”:19.95,“顏色”:“紅色”｝}，“老板”:“艾米”,“郵政編碼”:“94025”,“fb: testid”:“1234”}'作為生
提取頂級列
要提取列，請在提取路徑中指定JSON字段的名稱。
可以在括號內提供列名。括號內引用的列是匹配的大小寫敏感．列名的引用也不區分大小寫。
選擇生：老板，生：老板從store_data
+-------+-------+|老板|老板|+-------+-------+|艾米|艾米|+-------+-------+
使用括號時引用是區分大小寫的選擇生：老板case_insensitive，生:【“主人”］case_sensitive從store_data
+------------------+----------------+|case_insensitive|case_sensitive|+------------------+----------------+|艾米|零|+------------------+----------------+
使用反勾來轉義空格和特殊字符。字段名是匹配的大小寫如果不．
使用反引號轉義特殊字符。使用反勾號時引用不區分大小寫。使用括號區分大小寫。選擇生：｀郵政編碼代碼｀，生：｀郵政編碼代碼｀，生:【“fb: testid”］從store_data
+----------+----------+-----------+|郵政編碼代碼|郵政編碼代碼|神奇動物：testid|+----------+----------+-----------+|94025|94025|1234|+----------+----------+-----------+
請注意
如果一個JSON記錄包含多個列，由於不區分大小寫的匹配，這些列可以匹配您的提取路徑，那麼您將收到一個錯誤，要求您使用括號。如果有跨行列的匹配，則不會收到任何錯誤。以下操作將拋出錯誤:{“foo”:“酒吧”,“Foo”:“酒吧”}，下麵的操作不會拋出錯誤:
｛“foo”：“酒吧”｝｛“Foo”：“酒吧”｝
提取嵌套的字段
通過點表示法或使用括號指定嵌套字段。使用括號時，列是區分大小寫匹配的。
——使用點表示法選擇生：商店．自行車從store_data返回的列是一個字符串
+------------------+|自行車|+------------------+|｛||“價格”：19.95，||“顏色”：“紅色”||｝|+------------------+
——使用括號選擇生：商店［“自行車”),生：商店［“自行車”］從store_data
+------------------+---------+|自行車|自行車|+------------------+---------+|｛|零||“價格”：19.95，|||“顏色”：“紅色”|||｝||+------------------+---------+
從數組中提取值
用括號為數組中的元素建立索引。指數是基於0。你可以用星號(＊)，然後用點或括號符號從數組中的所有元素中提取子字段。
——索引元素選擇生：商店．水果［0),生：商店．水果［1］從store_data
+------------------+-----------------+|水果|水果|+------------------+-----------------+|｛|｛||“重量”：8，|“重量”：9，||“類型”：“蘋果”|“類型”：“梨”||｝|｝|+------------------+-----------------+
——從數組中提取子字段選擇生：商店．書［＊]。國際標準圖書編號從store_data
+--------------------+|國際標準圖書編號|+--------------------+|［||零，||“0-553-21311-3”，||“0-395-19395-8”||］|+--------------------+
——訪問數組中的數組或數組中的結構選擇生：商店．籃子［＊),生：商店．籃子［＊] [0］first_of_baskets，生：商店．籃子［0] [＊］first_basket，生：商店．籃子［＊] [＊］all_elements_flattened，生：商店．籃子［0] [2]。b子域從store_data
+----------------------------+------------------+---------------------+---------------------------------+----------+|籃子|first_of_baskets|first_basket|all_elements_flattened|子域|+----------------------------+------------------+---------------------+---------------------------------+----------+|［|［|［|［1，2, {“b”：“y”，“一個”：“x”}，3.，4，5，6］|y||［1，2, {“b”：“y”，“一個”：“x”}),|1，|1，||||［3.，4),|3.，|2，||||［5，6］|5|｛“b”：“y”，“一個”：“x”｝||||］|］|］|||+----------------------------+------------------+---------------------+---------------------------------+----------+
把值
您可以使用：：將值轉換為基本數據類型。使用from_json方法將嵌套結果轉換為更複雜的數據類型，如數組或結構。
——price作為double類型返回，而不是字符串選擇生：商店．自行車．價格：：雙從store_data
+------------------+|價格|+------------------+|19.95|+------------------+
——使用from_json轉換為更複雜的類型選擇from_json（生：商店．自行車，“價格加倍，顏色串”）自行車從store_data返回的列是一個包含列價格和顏色的結構
+------------------+|自行車|+------------------+|｛||“價格”：19.95，||“顏色”：“紅色”||｝|+------------------+
選擇from_json（生：商店．籃子［＊),“數組<數組<字符串> >”）籃子從store_data返回的列是字符串數組的數組
+------------------------------------------+|籃子|+------------------------------------------+|［||［“1”，“2”，“{\”b\”：\”y\”，\”一個\”：\”x\”}]”，||［“3”，“4”),||［“5”，“6”］||］|+------------------------------------------+
空的行為
類型存在JSON字段時零值，您將收到一個SQL零值，而不是零文本值。
選擇”{零}“關鍵”:“：關鍵是零sql_null，”{零}“關鍵”:“：關鍵= =“零”text_null
+-------------+-----------+|sql_null|text_null|+-------------+-----------+|真正的|零|+-------------+-----------+
使用Spark SQL操作符轉換嵌套數據
Apache Spark有許多用於處理複雜和嵌套數據的內置函數。下麵的筆記本裏有一些例子。
此外,高階函數當內置的Spark操作符無法以您想要的方式轉換數據時，提供了許多額外的選項。