Data varian kueri

Artikel ini menjelaskan operator Databricks SQL yang dapat Anda gunakan untuk mengkueri dan mengubah data semi terstruktur yang disimpan sebagai VARIANT. Jenis VARIANT data tersedia di Databricks Runtime 15.3 ke atas.

Databricks merekomendasikan penggunaan VARIANT lebih dari string JSON. Untuk pengguna yang saat ini menggunakan string JSON yang ingin bermigrasi, lihat Bagaimana varian berbeda dari string JSON?.

Jika Anda ingin melihat contoh untuk mengkueri data semi terstruktur yang disimpan dengan string JSON, lihat Mengkueri string JSON.

Membuat tabel dengan kolom varian

Jalankan kueri berikut untuk membuat tabel dengan data yang sangat berlapis yang disimpan sebagai VARIANT. Contoh dalam artikel ini semuanya mereferensikan tabel ini.

CREATE TABLE store_data AS
SELECT parse_json(
  '{
    "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

Bidang kueri dalam kolom varian

Sintaks untuk mengkueri string JSON dan jenis data kompleks lainnya di Azure Databricks berlaku untuk VARIANT data, termasuk yang berikut ini:

• Gunakan : untuk memilih bidang tingkat atas.
• Gunakan . atau [<key>] untuk memilih bidang berlapis dengan kunci bernama.
• Gunakan [<index>] untuk memilih nilai dari array.

SELECT raw:['zip.code'] FROM store_data

Mengekstrak bidang varian tingkat atas

Untuk mengekstrak bidang, tentukan nama bidang JSON di jalur ekstraksi Anda. Nama bidang selalu peka huruf besar/kecil.

SELECT raw:owner FROM store_data

+-------+
| owner |
+-------+
| "amy" |
+-------+

-- Use backticks to escape special characters.
SELECT raw:`zip code`, raw:`fb:testid` FROM store_data

+----------+-----------+
| zip code | fb:testid |
+----------+-----------+
| "94025"  | "1234"    |
+----------+-----------+

Jika jalur tidak dapat ditemukan, hasilnya berjenis NULLVARIANT.

Mengekstrak bidang berlapis varian

Anda menentukan bidang bersarang melalui notasi titik atau menggunakan tanda kurung. Nama bidang selalu peka huruf besar/kecil.

-- Use dot notation
SELECT raw:store.bicycle FROM store_data

+------------------+
| bicycle          |
+------------------+
| {                |
|   "color":"red", |
|   "price":19.95  |
| }                |
+------------------+

-- Use brackets
SELECT raw:store['bicycle'] FROM store_data

+------------------+
| bicycle          |
+------------------+
| {                |
|   "color":"red", |
|   "price":19.95  |
| }                |
+------------------+

Jika jalur tidak dapat ditemukan, hasilnya berjenis NULLVARIANT.

Mengekstrak nilai dari array varian

Anda mengindeks elemen dalam array dengan tanda kurung. Indeks berbasis 0.

-- Index elements
SELECT raw:store.fruit[0], raw:store.fruit[1] FROM store_data

+-------------------+------------------+
| fruit             | fruit            |
+-------------------+------------------+
| {                 | {                |
|   "type":"apple", |   "type":"pear", |
|   "weight":8      |   "weight":9     |
| }                 | }                |
+-------------------+------------------+

Jika jalur tidak dapat ditemukan, atau jika indeks array berada di luar batas, hasilnya adalah NULL.

Meratakan objek dan array varian

Fungsi variant_explode generator bernilai tabel dapat digunakan untuk meratakan VARIANT array dan objek.

Karena variant_explode merupakan fungsi generator, Anda menggunakannya sebagai bagian FROM dari klausul daripada dalam SELECT daftar, seperti dalam contoh berikut:

SELECT key, value
  FROM store_data,
  LATERAL variant_explode(store_data.raw:store);

+-------+--------------------+
|    key|               value|
+-------+--------------------+
| basket|[[1,2,{"a":"x","b...|
|bicycle|{"color":"red","p...|
|   book|[{"author":"Nigel...|
|  fruit|[{"type":"apple",...|
+-------+--------------------+

SELECT pos, value
  FROM store_data,
  LATERAL variant_explode(store_data.raw:store.basket[0]);

+---+-----------------+
|pos|            value|
+---+-----------------+
|  0|                1|
|  1|                2|
|  2|{"a":"x","b":"y"}|
+---+-----------------+

Aturan transmisi jenis varian

Anda dapat menyimpan array dan skalar menggunakan VARIANT jenis. Saat mencoba melemparkan jenis varian ke jenis lain, aturan transmisi normal berlaku untuk nilai dan bidang individual, dengan aturan tambahan berikut.

Saat menyimpulkan jenis dengan schema_of_variant atau schema_of_variant_agg, fungsi kembali ke VARIANT jenis daripada STRING jenis ketika ada jenis yang bertentangan yang tidak dapat diselesaikan.

Anda dapat menggunakan :: atau cast untuk melemparkan nilai ke jenis data yang didukung.

-- price is returned as a double, not a string
SELECT raw:store.bicycle.price::double FROM store_data

+------------------+
| price            |
+------------------+
| 19.95            |
+------------------+

-- cast into more complex types
SELECT cast(raw:store.bicycle AS STRUCT<price DOUBLE, color STRING>) bicycle FROM store_data;
-- `::` also supported
SELECT raw:store.bicycle::STRUCT<price DOUBLE, color STRING> bicycle FROM store_data;

+------------------+
| bicycle          |
+------------------+
| {                |
|   "price":19.95, |
|   "color":"red"  |
| }                |
+------------------+

Aturan null varian

Varian dapat berisi dua jenis null:

• SQL NULL: SQL NULLs menunjukkan bahwa nilai hilang. Ini sama NULLseperti saat berhadapan dengan data terstruktur.
• Varian NULL: Varian NULLmenunjukkan bahwa varian secara eksplisit berisi NULL nilai. Ini tidak sama dengan SQL NULLs, karena NULL nilai disimpan dalam data.

is_variant_null Gunakan fungsi untuk menentukan apakah nilai varian adalah varian NULL.

SELECT
  is_variant_null(parse_json(NULL)) AS sql_null,
  is_variant_null(parse_json('null')) AS variant_null,
  is_variant_null(parse_json('{ "field_a": null }'):field_a) AS variant_null_value,
  is_variant_null(parse_json('{ "field_a": null }'):missing) AS missing_sql_value_null

+--------+------------+------------------+----------------------+
|sql_null|variant_null|variant_null_value|missing_sql_value_null|
+--------+------------+------------------+----------------------+
|   false|        true|              true|                 false|
+--------+------------+------------------+----------------------+