本文說明如何查詢並轉換儲存為 VARIANT的半結構化資料。 此 VARIANT 資料型別可在 Databricks Runtime 15.4 及以上版本中使用。
Azure Databricks 建議對半結構化資料使用 VARIANT，而非 JSON 字串。 對於目前使用想要移轉的 JSON 字串的使用者，請參閱 變體與 JSON 字串有什麼不同？。
若要查詢以 JSON 字串儲存的半結構化資料，請參見 查詢 JSON 字串。
注意
VARIANT 欄位無法用於叢集鍵、分割區或 Z 順序索引鍵。
VARIANT 資料類型無法用於比較、分組、排序和設定作業。 如需完整的限制清單，請參閱 限制。
使用多變數列建立資料表
要建立變體欄位，請使用函 parse_json 式（SQL 或 Python）。
執行以下操作即可建立一個資料表，資料高度巢狀儲存為 VARIANT。 （此資料已用於本頁其他範例。）
Python
# 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
查詢 Variant 資料行中的欄位
要從變體欄位擷取欄位，請使用 variant_get 函式（SQL 或 Python）指定擷取路徑中 JSON 欄位的名稱。 字段名稱一律區分大小寫。
Python
# 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
你也可以用 SQL 語法來查詢變體欄位中的欄位。 請參見 SQL 的 variant_get 簡寫形式。
SQL 中 variant_get 的簡寫
Azure Databricks 上查詢 JSON 字串及其他複雜資料型態的 SQL 語法適用於 VARIANT 以下資料：
- 使用
:來選取最上層欄位。 - 使用
.或[<key>]來選取具有具名索引鍵的巢狀欄位。 - 使用
[<index>]從陣列中選取值。
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" |
+----------+-----------+
該 ['<field>'] 語法可避開欄位名稱中的任何特殊字元，包括空格、句號（.）、冒號（:）、方括號（[ ]）。 Azure Databricks 建議對每個包含特殊字元的欄位名稱採用此語法。 例如，使用 raw:['zip.code'] 來選取名為 zip.code 的欄位，或使用 raw:['A[1]'] 來選取名為 A[1] 的欄位。
反引號也可將包含空格或冒號的欄位名稱逸出，但不會逸出句點或方括號。 包含句點或方括號的欄位名稱，若以反引號逸出，會回傳 NULL，因此對這類名稱請使用 ['<field>'] 語法。
要在 PySpark 中擷取包含特殊字元的欄位，請在擷取路徑中使用相同的括號語法 variant_get ：
# 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"))
擷取變體巢狀欄位
要從變體欄位中擷取巢狀欄位，請使用點符號或括號來指定。 字段名稱一律區分大小寫。
Python
# 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()
如果找不到路徑，則結果的類型null為 VariantVal 。
SQL
-- Use dot notation
SELECT raw:store.bicycle FROM store_data
-- Use brackets
SELECT raw:store['bicycle'] FROM store_data
如果找不到路徑，則結果的類型NULL為 VARIANT 。
+-----------------+
| bicycle |
+-----------------+
| { |
| "color":"red", |
| "price":19.95 |
| } |
+-----------------+
從 Variant 陣列擷取值
要從陣列中擷取元素，請用括號索引。 索引是以 0 為基礎。
Python
# 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 |
| } | } |
+-------------------+------------------+
若無法找到路徑，或陣列索引超出邊界，則結果為空。
在 Python 中處理變體
你可以從 Spark DataFrames 中擷取變體到 Python 格式 VariantVal ，並用 toPython and 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()
輸出 VariantVal 為 JSON 字串：
print(variants[0].v.toJson())
{"age":25,"name":"Alice"}
將 a VariantVal 轉換成 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
你也可以用這個VariantVal函數來構造VariantVal.parseJson。
# parseJson to construct VariantVal's in Python
from pyspark.sql.types import VariantVal
variant = VariantVal.parseJson('{"a": 1}')
將變體列印成 JSON 字串：
print(variant.toJson())
{"a":1}
將變體轉換成 Python 物件並列印一個值：
print(variant.toPython()["a"])
1
回傳變體的架構
要回傳變體的結構，請使用函 schema_of_variant 式（SQL 或 Python）。
Python
# 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;
要回傳群組中所有變體的合併結構，請使用函 schema_of_variant_agg 式（SQL 或 Python）。
以下範例會回傳範例資料 json_data的結構，接著是合併的結構。
Python
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> |
+-----------------------------------------------------------------+
Python
# 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> |
+----------------------------------------------------------------------------------------------------------------------------+
扁平化變體物件和陣列
variant_explode表格值產生函式（SQL 或 Python）可用來將變體陣列和物件扁平化。
Python
使用 表值函數（TVF）DataFrame API 將變體展開為多列：
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
因為 variant_explode 是產生器函式，所以您會使用它作為 子句的一 FROM 部分，而不是在 SELECT 清單中，如下列範例所示：
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]);
Variant 類型轉換規則
您可以使用VARIANT類型來儲存陣列和純量。 嘗試將變體類型轉換成其他類型時，一般轉型規則會套用至個別值和欄位，並包含下列其他規則。
注意
variant_get 並 try_variant_get 接受類型自變數，並遵循這些轉換規則。
| 來源類型 | 行為 |
|---|---|
VOID |
結果是 NULL 類型的 VARIANT。 |
ARRAY<elementType> |
elementType必須是可以轉換成VARIANT的型別。 |
使用schema_of_variant或schema_of_variant_agg推斷型別時，當出現衝突型別且無法解析時，函式會回復為VARIANT型別，而不是STRING型別。
Python
使用 try_variant_get 函式（Python）來鑄造：
# 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 函式（SQL）來cast：
-- 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 |
+------------------+
你也可以使用 :: 或 cast 將值鑄造成支援的資料型態：
-- 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" |
| } |
+------------------+
另外也可以使用try_variant_get函式（SQL 或 Python）來處理類型轉換失敗：
Python
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'
)
Variant 空值規則
使用 is_variant_null 函式（SQL 或 Python）來判斷變體值是否為變體空值。
Python
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
變數可以包含兩種空值。
-
SQL
NULL：SQLNULL表示值遺失。 處理結構化數據時，這些與NULL相同。 -
變體
NULLNULL表示該變體明確包含NULL值。 這些與 SQLNULL不同，因為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|
+--------+------------+------------------+----------------------+
其他資源
-
Variant 與 JSON 字串有何不同？：比較
VARIANT與 JSON 字串，包括為從 JSON 字串儲存遷移的使用者提供的遷移指南。 -
Apache Iceberg 與 Delta Lake 的變體類型支援：如需
VARIANT限制的完整清單及 Delta 專屬行為，請參閱。