Mengkueri string JSON
Artikel ini menjelaskan operator Databricks SQL yang dapat Anda gunakan untuk mengkueri dan mengubah data semi terstruktur yang disimpan sebagai string JSON.
Catatan
Fitur ini memungkinkan Anda membaca data semi-terstruktur tanpa meratakan file. Namun, untuk performa kueri baca yang optimal, Databricks merekomendasikan agar Anda mengekstrak kolom berlapis dengan jenis data yang benar.
Anda mengekstrak kolom dari bidang yang berisi string JSON menggunakan sintaks <column-name>:<extraction-path>, di mana <column-name> adalah nama kolom string dan <extraction-path> adalah jalur ke bidang yang akan diekstrak. Hasil yang dikembalikan adalah string.
Membuat tabel dengan data yang sangat berlapis
Jalankan kueri berikut untuk membuat tabel dengan data yang sangat berlapis. Contoh dalam artikel ini semuanya mereferensikan tabel ini.
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
Mengekstrak kolom tingkat atas
Untuk mengekstrak kolom, tentukan nama bidang JSON di jalur ekstraksi Anda.
Anda dapat menuliskan nama kolom di dalam tanda kurung siku. Kolom yang disebutkan di dalam tanda kurung cocok dengan huruf besar kecil yang sensitif kasusnya . Nama kolom juga dirujuk tanpa peka terhadap huruf besar/kecil.
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 |
+------------------+----------------+
Gunakan backtick untuk spasi escape dan karakter khusus. Nama bidang tidak peka huruf besar/kecil.
-- 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 |
+----------+----------+-----------+
Catatan
Jika rekaman JSON berisi beberapa kolom yang dapat mencocokkan jalur ekstraksi Anda karena pencocokan yang tidak peka terhadap huruf besar dan kecil, Anda akan menerima galat yang meminta Anda untuk menggunakan tanda kurung. Jika Anda memiliki kecocokan kolom di seluruh baris, Anda tidak akan menerima kesalahan apa pun. Berikut ini akan menimbulkan kesalahan: {"foo":"bar", "Foo":"bar"}, dan berikut ini tidak akan menimbulkan kesalahan:
{"foo":"bar"}
{"Foo":"bar"}
Mengekstrak bidang bersarang
Anda menentukan bidang bersarang melalui notasi titik atau menggunakan tanda kurung. Saat Anda menggunakan tanda kurung siku, kolom dicocokkan dengan huruf besar/kecil secara sensitif.
-- 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" | |
| } | |
+------------------+---------+
Mengekstrak nilai dari array
Anda mengindeks elemen dalam array dengan tanda kurung. Indeks berbasis 0. Anda dapat menggunakan tanda bintang (*) diikuti dengan notasi titik atau kurung untuk mengekstrak subbidang dari semua elemen dalam array.
-- 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"} | | |
| ] | ] | ] | | |
+----------------------------+------------------+---------------------+---------------------------------+----------+
Nilai pemrosesan
Anda dapat menggunakan :: untuk mengubah nilai ke tipe data dasar. Gunakan fungsi from_json untuk mentransmisikan hasil bersarang ke jenis data yang lebih kompleks, seperti array atau struct.
-- 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"] |
| ] |
+------------------------------------------+
Perilaku NULL
Saat bidang JSON ada dengan nilai null, Anda akan menerima nilai null SQL untuk kolom tersebut, bukan nilai teks null.
select '{"key":null}':key is null sql_null, '{"key":null}':key == 'null' text_null
+-------------+-----------+
| sql_null | text_null |
+-------------+-----------+
| true | null |
+-------------+-----------+
Mengubah data berlapis menggunakan operator Spark SQL
Apache Spark memiliki sejumlah fungsi bawaan untuk bekerja dengan data yang kompleks dan berlapis. Buku catatan berikut berisi contoh.
Selain itu, fungsi urutan yang lebih tinggi menyediakan banyak opsi tambahan saat operator Spark bawaan tidak tersedia untuk mengubah data seperti yang Anda inginkan.