查询变体数据
重要
此功能目前以公共预览版提供。
本文介绍 Databricks SQL 运算符,这些运算符可用于查询和转换以 VARIANT
形式存储的半结构化数据。 Databricks Runtime 15.3 和更高版本中提供了 VARIANT
数据类型。
Databricks 建议对 JSON 字符串使用 VARIANT
。 对于当前使用 JSON 字符串且想要迁移的用户,请参阅变体与 JSON 字符串有何不同?。
如果你想查看查询使用 JSON 字符串存储的半结构化数据的示例,请参阅查询 JSON 字符串。
创建一个包含变体列的表
运行以下查询以创建一个表,其中包含以 VARIANT
形式存储的高度嵌套的数据。 本文中的示例均引用此表。
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
查询变体列中的字段
在 Azure Databricks 上查询 JSON 字符串和其他复杂数据类型的语法适用于 VARIANT
数据,包括:
- 使用
:
选择顶级字段。 - 使用
.
或[<key>]
选择具有命名键的嵌套字段。 - 使用
[<index>]
从数组中选择值。
提取顶级变体字段
若要提取字段,请在提取路径中指定 JSON 字段的名称。 字段名称始终区分大小写。
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" |
+----------+-----------+
如果找不到路径,则结果是类型 VARIANT
为 NULL
。
提取变体嵌套字段
可以通过点表示法或使用方括号指定嵌套字段。 字段名称始终区分大小写。
-- 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 |
| } |
+------------------+
如果找不到路径,则结果是类型 VARIANT
为 NULL
。
从变体数组中提取值
可以使用方括号对数组中的元素进行索引。 索引从 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 |
| } | } |
+-------------------+------------------+
如果找不到路径,或者数组索引超出边界,则结果是 NULL
。
平展变体对象和数组
variant_explode
表值生成器函数可用于平展 VARIANT
数组和对象。
由于 variant_explode
是生成器函数,因此可将其用作 FROM
子句的一部分,而不是在 SELECT
列表中使用,如以下示例所示:
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"}|
+---+-----------------+
变体类型转换规则
你可以使用 VARIANT
类型存储数组和标量。 尝试将变体类型强制转换为其他类型时,普通强制转换规则适用于单个值和字段,并使用以下附加规则。
注意
variant_get
和 try_variant_get
采用类型参数并遵循这些强制转换规则。
源类型 | 行为 |
---|---|
VOID |
结果是类型 VARIANT 为 NULL 。 |
ARRAY<elementType> |
elementType 必须是可强制转换为 VARIANT 的类型。 |
当使用 schema_of_variant
或 schema_of_variant_agg
推断类型时,如果存在无法解决的冲突类型,函数会回退到 VARIANT
类型而不是 STRING
类型。
可以使用 ::
或 cast
将值转换为支持的数据类型。
-- 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" |
| } |
+------------------+
变体 null 规则
变体可以包含两种类型的 null:
- SQL
NULL
:SQLNULL
表示值缺失。 这些是与处理结构化数据时相同的NULL
。 - 变体
NULL
:变体NULL
表示变体显式包含NULL
值。 这些与 SQLNULL
不同,因为NULL
值存储在数据中。
使用 is_variant_null
函数确定变体值是否为变体 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|
+--------+------------+------------------+----------------------+