Consulta de datos de tipo Variant
Importante
Esta característica está en versión preliminar pública.
En este artículo se describen los operadores de Databricks SQL que se pueden usar para consultar y transformar datos semiestructurados almacenados como VARIANT. El tipo de datos VARIANT está disponible en Databricks Runtime 15.3 y versiones posteriores.
Databricks recomienda usar VARIANT en vez de cadenas JSON. Para los usuarios que actualmente usan cadenas JSON que buscan migrar, consulte ¿En que se diferencia Variant de las cadenas JSON?
Si desea ver ejemplos para consultar datos semiestructurados almacenados con cadenas JSON, revise Consulta de cadenas JSON.
Creación de una tabla con una columna Variant
Ejecute la consulta siguiente para crear una tabla con datos altamente anidados como VARIANT. Todos los ejemplos de este artículo hacen referencia a esta tabla.
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
Campos de consulta en una columna Variant
La sintaxis para consultar cadenas JSON y otros tipos de datos complejos en Azure Databricks se aplica a los datos VARIANT, incluidos los siguientes:
- Use
:para seleccionar campos de nivel superior. - Use
.o[<key>]para seleccionar campos anidados con claves con nombre. - Use
[<index>]para seleccionar valores de matrices.
Nota:
Si un nombre de campo contiene un punto (.), debe aplicarle escape con corchetes ([ ]). Por ejemplo, la consulta siguiente selecciona un campo denominado zip.code:
SELECT raw:['zip.code'] FROM store_data
Extracción de un campo Variant de nivel superior
Para extraer un campo, especifique el nombre del campo JSON en la ruta de acceso de extracción. Los nombres de campo distinguen siempre entre mayúsculas y minúsculas.
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" |
+----------+-----------+
Si no se encuentra una ruta de acceso, el resultado es NULL del tipo VARIANT.
Extracción de campos anidados de Variant
Los campos anidados se especifican mediante notación de puntos o mediante corchetes. Los nombres de campo distinguen siempre entre mayúsculas y minúsculas.
-- 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 |
| } |
+------------------+
Si no se encuentra una ruta de acceso, el resultado es NULL del tipo VARIANT.
Extracción de valores de matrices de Variant
Los elementos de las matrices se indexan con corchetes. Los índices tienen base 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 |
| } | } |
+-------------------+------------------+
Si no se encuentra la ruta de acceso o si el índice de matriz está fuera de los límites, el resultado es NULL.
Simplificar objetos y matrices Variant
La función de generador con valores de tabla variant_explode se puede usar para simplificar matrices y objetos VARIANT.
Dado que variant_explode es una función de generador, se usa como parte de la cláusula FROM en lugar de en la lista SELECT, como en los ejemplos siguientes:
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"}|
+---+-----------------+
Reglas de conversión de tipos Variant
Puede almacenar matrices y escalares usando el tipo VARIANT. Al intentar convertir tipos Variant a otros tipos, las reglas de conversión normales se aplican a valores y campos individuales, con las siguientes reglas adicionales.
Nota:
variant_get y try_variant_get toman argumentos de tipo y siguen estas reglas de conversión.
| Tipo de origen | Comportamiento |
|---|---|
VOID |
El resultado es un valor NULL de tipo VARIANT. |
ARRAY<elementType> |
El valor elementType debe ser de un tipo que se pueda convertir en VARIANT. |
Al inferir el tipo con schema_of_variant o schema_of_variant_agg, las funciones vuelven al tipo VARIANT en lugar de al tipo STRING si hay tipos en conflicto que no se pueden resolver.
Puede usar el :: o cast para convertir los valores a tipos de datos admitidos.
-- 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" |
| } |
+------------------+
Reglas de valores null de Variant
Las variantes pueden contener dos tipos de valores null:
- SQL
NULL: los valoresNULLde SQL indican que falta el valor. Estos son los mismos valoresNULLque cuando se trabaja con datos estructurados. - Variante
NULL: los valoresNULLde la variante indican que esta contiene explícitamente un valorNULL. No son iguales que los valoresNULLde SQL, ya que el valorNULLse almacena en los datos.
Use la función is_variant_null para determinar si el valor de la variante es un valor NULL de variante.
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|
+--------+------------+------------------+----------------------+