Nota
El acceso a esta página requiere autorización. Puede intentar iniciar sesión o cambiar directorios.
El acceso a esta página requiere autorización. Puede intentar cambiar los directorios.
En este artículo se describen los operadores SQL de Databricks que puede usar para consultar y transformar datos semiestructurados almacenados como cadenas JSON.
Nota:
Esta característica permite leer datos semiestructurados sin aplanar los archivos. Sin embargo, para obtener un rendimiento óptimo de las consultas de lectura, Databricks recomienda extraer columnas anidadas con los tipos de datos correctos.
Se extrae una columna de campos que contienen cadenas JSON mediante la sintaxis <column-name>:<extraction-path>, donde <column-name> es el nombre de la columna de cadena y <extraction-path> es la ruta de acceso al campo que se va a extraer. Los resultados devueltos son cadenas.
Creación de una tabla con datos altamente anidados
Ejecute la consulta siguiente para crear una tabla con datos altamente anidados. Todos los ejemplos de este artículo hacen referencia a esta tabla.
CREATE TABLE store_data AS SELECT
'{
"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
Extraer una columna de nivel superior
Para extraer una columna, especifique el nombre del campo JSON en la ruta de extracción.
Puede proporcionar nombres de columna entre corchetes. Las columnas referenciadas entre corchetes son sensibles a mayúsculas y minúsculas. Se menciona el nombre de la columna sin distinción entre mayúsculas y minúsculas.
SELECT raw:owner, RAW:owner FROM store_data
+-------+-------+
| owner | owner |
+-------+-------+
| amy | amy |
+-------+-------+
-- References are case sensitive when you use brackets
SELECT raw:OWNER case_insensitive, raw:['OWNER'] case_sensitive FROM store_data
+------------------+----------------+
| case_insensitive | case_sensitive |
+------------------+----------------+
| amy | null |
+------------------+----------------+
Use tildes agudas para los espacios de escape y los caracteres especiales. Al buscar coincidencias con los nombres de campo, se tienen en cuenta las mayúsculas y minúsculas.
-- Use backticks to escape special characters. References are case insensitive when you use backticks.
-- Use brackets to make them case sensitive.
SELECT raw:`zip code`, raw:`Zip Code`, raw:['fb:testid'] FROM store_data
+----------+----------+-----------+
| zip code | Zip Code | fb:testid |
+----------+----------+-----------+
| 94025 | 94025 | 1234 |
+----------+----------+-----------+
Nota:
Si un registro JSON contiene varias columnas que pueden coincidir con la ruta de extracción debido a coincidencias sin distinguir entre mayúsculas y minúsculas, recibirá un error que le indicará que use corchetes. Si tiene coincidencias de columnas entre filas, no recibirá ningún error. A continuación se producirá un error: {"foo":"bar", "Foo":"bar"}y lo siguiente no producirá un error:
{"foo":"bar"}
{"Foo":"bar"}
Extracción de campos anidados
Los campos anidados se especifican mediante notación de puntos o mediante corchetes. Cuando se usan corchetes, las columnas se diferencian distinguiendo mayúsculas de minúsculas.
-- Use dot notation
SELECT raw:store.bicycle FROM store_data
-- the column returned is a string
+------------------+
| bicycle |
+------------------+
| { |
| "price":19.95, |
| "color":"red" |
| } |
+------------------+
-- Use brackets
SELECT raw:store['bicycle'], raw:store['BICYCLE'] FROM store_data
+------------------+---------+
| bicycle | BICYCLE |
+------------------+---------+
| { | null |
| "price":19.95, | |
| "color":"red" | |
| } | |
+------------------+---------+
Extracción de valores de matrices
Los elementos de las matrices se indexan con corchetes. Los índices tienen base 0. Puede usar un asterisco (*) seguido de la notación de puntos o corchetes para extraer subcampos de todos los elementos de una matriz.
-- Index elements
SELECT raw:store.fruit[0], raw:store.fruit[1] FROM store_data
+------------------+-----------------+
| fruit | fruit |
+------------------+-----------------+
| { | { |
| "weight":8, | "weight":9, |
| "type":"apple" | "type":"pear" |
| } | } |
+------------------+-----------------+
-- Extract subfields from arrays
SELECT raw:store.book[*].isbn FROM store_data
+--------------------+
| isbn |
+--------------------+
| [ |
| null, |
| "0-553-21311-3", |
| "0-395-19395-8" |
| ] |
+--------------------+
-- Access arrays within arrays or structs within arrays
SELECT
raw:store.basket[*],
raw:store.basket[*][0] first_of_baskets,
raw:store.basket[0][*] first_basket,
raw:store.basket[*][*] all_elements_flattened,
raw:store.basket[0][2].b subfield
FROM store_data
+----------------------------+------------------+---------------------+---------------------------------+----------+
| basket | first_of_baskets | first_basket | all_elements_flattened | subfield |
+----------------------------+------------------+---------------------+---------------------------------+----------+
| [ | [ | [ | [1,2,{"b":"y","a":"x"},3,4,5,6] | y |
| [1,2,{"b":"y","a":"x"}], | 1, | 1, | | |
| [3,4], | 3, | 2, | | |
| [5,6] | 5 | {"b":"y","a":"x"} | | |
| ] | ] | ] | | |
+----------------------------+------------------+---------------------+---------------------------------+----------+
Valores de conversión
Puede usar :: para convertir valores a tipos de datos básicos. Use el método from_json para convertir resultados anidados a tipos de datos más complejos, como matrices o estructuras.
-- price is returned as a double, not a string
SELECT raw:store.bicycle.price::double FROM store_data
+------------------+
| price |
+------------------+
| 19.95 |
+------------------+
-- use from_json to cast into more complex types
SELECT from_json(raw:store.bicycle, 'price double, color string') bicycle FROM store_data
-- the column returned is a struct containing the columns price and color
+------------------+
| bicycle |
+------------------+
| { |
| "price":19.95, |
| "color":"red" |
| } |
+------------------+
SELECT from_json(raw:store.basket[*], 'array<array<string>>') baskets FROM store_data
-- the column returned is an array of string arrays
+------------------------------------------+
| basket |
+------------------------------------------+
| [ |
| ["1","2","{\"b\":\"y\",\"a\":\"x\"}]", |
| ["3","4"], |
| ["5","6"] |
| ] |
+------------------------------------------+
Comportamiento de NULL
Cuando exista un campo JSON con un null valor, recibirá un valor SQL null para esa columna, no un null valor de texto.
select '{"key":null}':key is null sql_null, '{"key":null}':key == 'null' text_null
+-------------+-----------+
| sql_null | text_null |
+-------------+-----------+
| true | null |
+-------------+-----------+
Transformación de datos anidados mediante operadores de Spark SQL
Apache Spark tiene varias funciones integradas para trabajar con datos complejos y anidados. El cuaderno siguiente contiene ejemplos.
Además, las funciones de orden superior proporcionan muchas opciones adicionales cuando los operadores de Spark integrados no están disponibles para transformar los datos de la manera que desee.