Pengambilan Data Varian

Artikel ini menjelaskan cara mengkueri dan mengubah data semi terstruktur yang disimpan sebagai VARIANT. Jenis VARIANT data tersedia di Databricks Runtime 15.4 ke atas.

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

Untuk mengkueri data semi terstruktur yang disimpan sebagai string JSON, lihat Mengkueri string JSON.

Catatan

VARIANT kolom tidak dapat digunakan untuk pengklusteran kunci, partisi, atau kunci urutan Z. Jenis data VARIANT tidak dapat digunakan untuk perbandingan, pengelompokan, pemesanan, dan operasi set. Untuk daftar lengkap batasan, lihat batasan .

Membuat tabel dengan kolom varian

Untuk membuat kolom varian, gunakan parse_json fungsi (SQL atau Python).

Jalankan yang berikut ini untuk membuat tabel dengan data yang sangat berlapis yang disimpan sebagai VARIANT. (Data ini digunakan dalam contoh lain di halaman ini.)

Phyton

# Create a table with a variant column
store_data='''
{
  "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"
}
'''

# Create a DataFrame
df = spark.createDataFrame([(store_data,)], ["json"])

# Convert to a variant
df_variant = df.select(parse_json(col("json")).alias("raw"))

# Alternatively, create the DataFrame directly
# df_variant = spark.range(1).select(parse_json(lit(store_data)))

df_variant.display()

# Write out as a table
df_variant.write.saveAsTable("store_data")

SQL

-- Create a table with a variant column
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

SELECT * FROM store_data

Bidang kueri dalam kolom varian

Untuk mengekstrak bidang dari kolom varian, gunakan variant_get fungsi (SQL atau Python) yang menentukan nama bidang JSON di jalur ekstraksi Anda. Nama bidang selalu sensitif terhadap huruf besar dan kecil.

Phyton

# Extract a top-level field
df_variant.select(variant_get(col("raw"), "$.owner", "string")).display()

SQL

-- Extract a top-level field
SELECT variant_get(store_data.raw, '$.owner') AS owner FROM store_data

Anda juga bisa menggunakan sintaks SQL untuk mengkueri bidang dalam kolom varian. Lihat SQL singkatan untuk variant_get.

SQL singkatan untuk variant_get

Sintaks SQL 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:owner FROM store_data

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

-- Enclose a field name that contains special characters in single quotes inside square brackets.
SELECT raw:['zip code'], raw:['fb:testid'] FROM store_data

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

Sintaks ['<field>'] meng-escape karakter khusus apa pun pada nama bidang, termasuk spasi, titik (.), titik dua (:), dan kurung siku ([ ]). Azure Databricks merekomendasikan sintaks ini untuk setiap nama bidang yang berisi karakter khusus. Misalnya, gunakan raw:['zip.code'] untuk memilih bidang bernama zip.code, atau raw:['A[1]'] untuk memilih bidang bernama A[1].

Backtick juga meng-escape nama kolom yang berisi spasi atau titik dua, tetapi tidak meng-escape tanda titik atau kurung siku. Nama bidang yang berisi titik atau kurung siku mengembalikan NULL saat di-escape dengan backtick, jadi gunakan sintaks ['<field>'] untuk nama-nama tersebut.

Untuk mengekstrak bidang yang berisi karakter khusus di PySpark, gunakan sintaks kurung yang sama dalam variant_get jalur ekstraksi:

# Escape special characters in the extraction path
df_variant.select(variant_get(col("raw"), "$['zip.code']", "string"))
df_variant.select(variant_get(col("raw"), "$['A[1]']", "string"))

Mengekstrak bidang berlapis varian

Untuk mengekstrak bidang berlapis dari kolom varian, tentukan bidang tersebut menggunakan notasi titik atau tanda kurung siku. Nama bidang selalu sensitif terhadap huruf besar dan kecil.

Phyton

# Use dot notation
df_variant.select(variant_get(col("raw"), "$.store.bicycle", "string")).display()

# Use brackets
df_variant.select(variant_get(col("raw"), "$.store['bicycle']", "string")).display()

Jika jalur tidak dapat ditemukan, hasilnya berjenis nullVariantVal.

SQL

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

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

Jika jalur tidak dapat ditemukan, hasilnya berjenis NULLVARIANT.

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

Mengekstrak nilai dari array varian

Untuk mengekstrak elemen dari array, indeks dengan tanda kurung. Indeks dimulai dari 0.

Phyton

# Index elements
df_variant.select((variant_get(col("raw"), "$.store.fruit[0]", "string")),(variant_get(col("raw"), "$.store.fruit[1]", "string"))).display()

SQL

-- 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 null.

Bekerja dengan varian di Python

Anda dapat mengekstrak varian dari Spark DataFrames ke Python sebagai VariantVal dan bekerja dengannya satu per satu menggunakan toPython metode dan toJson .

# toPython
data = [
    ('{"name": "Alice", "age": 25}',),
    ('["person", "electronic"]',),
    ('1',)
]

df_person = spark.createDataFrame(data, ["json"])

# Collect variants into a VariantVal
variants = df_person.select(parse_json(col("json")).alias("v")).collect()

Keluarkan VariantVal sebagai string JSON:

print(variants[0].v.toJson())

{"age":25,"name":"Alice"}

Mengonversi ke VariantVal objek Python:

# First element is a dictionary
print(variants[0].v.toPython()["age"])

25

# Second element is a List
print(variants[1].v.toPython()[1])

electronic

# Third element is an Integer
print(variants[2].v.toPython())

1

Anda juga dapat membangun VariantVal menggunakan fungsi .VariantVal.parseJson

# parseJson to construct VariantVal's in Python
from pyspark.sql.types import VariantVal

variant = VariantVal.parseJson('{"a": 1}')

Cetak varian sebagai string JSON:

print(variant.toJson())

{"a":1}

Konversikan varian menjadi objek Python dan cetak nilai:

print(variant.toPython()["a"])

1

Mengembalikan skema varian

Untuk mengembalikan skema varian, gunakan schema_of_variant fungsi (SQL atau Python).

Phyton

# Return the schema of the variant
df_variant.select(schema_of_variant(col("raw"))).display()

SQL

-- Return the schema of the variant
SELECT schema_of_variant(raw) FROM store_data;

Untuk mengembalikan skema gabungan dari semua varian dalam grup, gunakan schema_of_variant_agg fungsi (SQL atau Python).

Contoh berikut menghasilkan skema dan kemudian skema gabungan untuk data contoh json_data.

Phyton

json_data = [
    ('{"name": "Alice", "age": 25}',),
    ('{"id": 101, "department": "HR"}',),
    ('{"product": "Laptop", "price": 1200.50, "in_stock": true}',)
]

df_item = spark.createDataFrame(json_data, ["json"])

# Return the schema
df_item.select(parse_json(col("json")).alias("v")).select(schema_of_variant(col("v"))).display()

SQL

CREATE OR REPLACE TEMP VIEW json_data AS
SELECT '{"name": "Alice", "age": 25}' AS json UNION ALL
SELECT '{"id": 101, "department": "HR"}' UNION ALL
SELECT '{"product": "Laptop", "price": 1200.50, "in_stock": true}';

-- Return the schema
SELECT schema_of_variant(parse_json(json)) FROM json_data;

+-----------------------------------------------------------------+
| schema_of_variant(v)                                            |
+-----------------------------------------------------------------+
| OBJECT<age: BIGINT, name: STRING>                               |
| OBJECT<department: STRING, id: BIGINT>                          |
| OBJECT<in_stock: BOOLEAN, price: DECIMAL(5,1), product: STRING> |
+-----------------------------------------------------------------+

Phyton

# Return the combined schema
df.select(parse_json(col("json")).alias("v")).select(schema_of_variant_agg(col("v"))).display()

SQL

-- Return the combined schema
SELECT schema_of_variant_agg(parse_json(json)) FROM json_data;

+----------------------------------------------------------------------------------------------------------------------------+
| schema_of_variant(v)                                                                                                       |
+----------------------------------------------------------------------------------------------------------------------------+
| OBJECT<age: BIGINT, department: STRING, id: BIGINT, in_stock: BOOLEAN, name: STRING, price: DECIMAL(5,1), product: STRING> |
+----------------------------------------------------------------------------------------------------------------------------+

Meratakan objek dan array varian

Fungsi variant_explode generator bernilai tabel (SQL atau Python) dapat digunakan untuk meratakan array dan objek varian.

Phyton

Gunakan API DataFrame fungsi bernilai tabel (TVF) untuk memperluas variasi menjadi beberapa baris.

spark.tvf.variant_explode(parse_json(lit(store_data))).display()

# To explode a nested field, first create a DataFrame with just the field
df_store_col = df_variant.select(variant_get(col("raw"), "$.store", "variant").alias("store"))

# Perform the explode with a lateral join and the outer function to return the new exploded DataFrame
df_store_exploded_lj = df_store_col.lateralJoin(spark.tvf.variant_explode(col("store").outer()))
df_store_exploded = df_store_exploded_lj.drop("store")
df_store_exploded.display()

SQL

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);

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

Aturan pengubahan tipe varian

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

Catatan

variant_get dan try_variant_get ambil argumen jenis dan ikuti aturan casting ini.

Jenis sumber	Perilaku
VOID	Hasilnya adalah NULL tipe VARIANT.
ARRAY<elementType>	elementType harus merupakan jenis yang dapat dilemparkan ke VARIANT.

Saat menyimpulkan jenis dengan schema_of_variant atau schema_of_variant_agg, fungsi beralih ke jenis VARIANT daripada jenis STRING ketika ada jenis yang konflik dan tidak bisa diselesaikan.

Phyton

try_variant_get Gunakan fungsi (Python) untuk mentransmisikan:

# price is returned as a double, not a string
df_variant.select(try_variant_get(col("raw"), "$.store.bicycle.price", "double").alias("price"))

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

SQL

try_variant_get Gunakan fungsi (SQL) untuk mentransmisikan:

-- price is returned as a double, not a string
SELECT try_variant_get(raw, '$.store.bicycle.price', 'double') as price FROM store_data

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

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

-- 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"  |
| }                |
+------------------+

Gunakan juga fungsi try_variant_get (SQL atau Python) untuk menangani kegagalan konversi.

Phyton

spark.range(1).select(parse_json(lit('{"a" : "c", "b" : 2}')).alias("v")).select(try_variant_get(col('v'), '$.a', 'boolean')).display()

SQL

SELECT try_variant_get(
  parse_json('{"a" : "c", "b" : 2}'),
  '$.a',
  'boolean'
)

Aturan variasi null

is_variant_null Gunakan fungsi (SQL atau Python) untuk menentukan apakah nilai varian adalah varian null.

Phyton

data = [
    ('null',),
    (None,),
    ('{"field_a" : 1, "field_b" : 2}',)
]

df = spark.createDataFrame(data, ["null_data"])
df.select(parse_json(col("null_data")).alias("v")).select(is_variant_null(col("v"))).display()

+------------------+
|is_variant_null(v)|
+------------------+
|              true|
+------------------+
|             false|
+------------------+
|             false|
+------------------+

SQL

Varian dapat berisi dua jenis null:

• SQL NULL: SQL NULLs menunjukkan bahwa nilai hilang. Ini sama dengan NULL, seperti 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.

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|
+--------+------------+------------------+----------------------+

Sumber daya tambahan

• Apa perbedaan tipe variant dengan string JSON?: Untuk perbandingan antara VARIANT dan string JSON, termasuk panduan migrasi bagi pengguna yang beralih dari penyimpanan string JSON.
• Dukungan jenis varian untuk Apache Iceberg dan Delta Lake: Untuk daftar lengkap batasan VARIANT dan perilaku khusus Delta.