Azure Databricks'te yarı yapılandırılmış verileri sorgulama
Bu makalede, JSON olarak depolanan yarı yapılandırılmış verileri sorgulamak ve dönüştürmek için kullanabileceğiniz Databricks SQL işleçleri açıklanmaktadır.
Dekont
Bu özellik, dosyaları düzleştirmeden yarı yapılandırılmış verileri okumanızı sağlar. Ancak, en iyi okuma sorgusu performansı için Databricks doğru veri türleriyle iç içe sütunları ayıklamanızı önerir.
söz dizimini <column-name>:<extraction-path>kullanarak JSON dizelerini içeren alanlardan bir sütun ayıklarsınız; burada <column-name> dize sütun adıdır ve <extraction-path> ayıklanan alanın yoludur. Döndürülen sonuçlar dizelerdir.
Yüksek oranda iç içe verilerle tablo oluşturma
Yüksek oranda iç içe veri içeren bir tablo oluşturmak için aşağıdaki sorguyu çalıştırın. Bu makaledeki örneklerin tümü bu tabloya başvurur.
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
En üst düzey sütunu ayıklama
Bir sütunu ayıklamak için ayıklama yolunuzda JSON alanının adını belirtin.
Köşeli ayraç içinde sütun adları sağlayabilirsiniz. Köşeli ayraçların içinde başvuruda bulunan sütunlar büyük/küçük harfe duyarlı bir şekilde eşleştirilir. Sütun adına büyük/küçük harfe duyarsız olarak da başvurulur.
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 |
+------------------+----------------+
Boşluklardan ve özel karakterlerden kaçmak için arka uçları kullanın. Alan adları büyük/küçük harfe duyarsız olarak eşleştirilir.
-- 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 |
+----------+----------+-----------+
Dekont
JSON kaydı, büyük/küçük harfe duyarsız eşleştirme nedeniyle ayıklama yolunuzla eşleşebilecek birden çok sütun içeriyorsa köşeli ayraç kullanmanızı isteyen bir hata alırsınız. Satırlar arasında sütunların eşleşmeleri varsa, herhangi bir hata almazsınız. Aşağıdakiler bir hata oluşturur: {"foo":"bar", "Foo":"bar"}ve aşağıdakiler hata oluşturmaz:
{"foo":"bar"}
{"Foo":"bar"}
İç içe alanları ayıklama
noktalı gösterimi veya köşeli ayraç kullanarak iç içe alanları belirtirsiniz. Köşeli ayraç kullandığınızda sütunlar büyük/küçük harfe duyarlı bir şekilde eşleştirilir.
-- 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" | |
| } | |
+------------------+---------+
Dizilerden değerleri ayıklama
Dizilerdeki öğeleri köşeli ayraçlarla dizinlersiniz. Dizinler 0 tabanlıdır. Dizideki tüm öğelerden alt alanları ayıklamak için yıldız işareti (*) ve ardından nokta veya köşeli ayraç gösterimi kullanabilirsiniz.
-- 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"} | | |
| ] | ] | ] | | |
+----------------------------+------------------+---------------------+---------------------------------+----------+
Değerleri atama
Değerleri temel veri türlerine dökmek için kullanabilirsiniz :: . İç içe sonuçları diziler veya yapılar gibi daha karmaşık veri türlerine ayırmak için from_json yöntemini kullanın.
-- 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 davranışı
Değer içeren bir null JSON alanı varsa, bu sütun için metin değeri değil null SQL null değeri alırsınız.
select '{"key":null}':key is null sql_null, '{"key":null}':key == 'null' text_null
+-------------+-----------+
| sql_null | text_null |
+-------------+-----------+
| true | null |
+-------------+-----------+
Spark SQL işleçlerini kullanarak iç içe verileri dönüştürme
Apache Spark,karmaşık ve iç içe verilerle çalışmaya yönelik bir dizi yerleşik işleve sahiptir. Aşağıdaki not defteri örnekler içerir.
Ayrıca, daha yüksek sıralı işlevler , yerleşik Spark işleçleri verileri istediğiniz gibi dönüştürmek için kullanılamadığında birçok ek seçenek sağlar.