在“數據庫”中查詢半結構化數據
請注意
在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類型，而不是string類型選擇生：商店．自行車．價格：：雙從store_data
+------------------+|價格|+------------------+|19.95|+------------------+
——使用from_json轉換為更複雜的類型選擇from_json（生：商店．自行車，'價格雙倍，顏色字符串'）自行車從store_data——返回的列是一個包含列price和color的結構體
+------------------+|自行車|+------------------+|｛||“價格”：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操作符不能以您想要的方式轉換數據時，提供許多額外的選項。