Consulta de archivos JSON mediante un grupo de SQL sin servidor en Azure Synapse Analytics
En este artículo, aprenderá a escribir una consulta mediante un grupo de SQL sin servidor en Azure Synapse Analytics. El objetivo de la consulta es leer archivos JSON con OPENROWSET.
- Archivos JSON estándar en los que se almacenan varios documentos JSON como una matriz JSON.
- Archivos JSON delimitados por líneas, donde los documentos JSON se separan con un carácter de nueva línea. Las extensiones comunes para estos tipos de archivos son
jsonl
,ldjson
yndjson
.
La forma más fácil de ver el contenido del archivo JSON es proporcionar la dirección URL del archivo a la función OPENROWSET
, especificar el elemento FORMAT
de CSV y establecer los valores 0x0b
para fieldterminator
y fieldquote
. Si necesita leer archivos JSON delimitados por líneas, esto es suficiente. Si tiene un archivo JSON clásico, debería establecer valores 0x0b
para rowterminator
. La función OPENROWSET
analizarán JSON y devolverá cada documento en el formato siguiente:
doc |
---|
{"date_rep":"2020-07-24","day":24,"month":7,"year":2020,"cases":3,"deaths":0,"geo_id":"AF"} |
{"date_rep":"2020-07-25","day":25,"month":7,"year":2020,"cases":7,"deaths":0,"geo_id":"AF"} |
{"date_rep":"2020-07-26","day":26,"month":7,"year":2020,"cases":4,"deaths":0,"geo_id":"AF"} |
{"date_rep":"2020-07-27","day":27,"month":7,"year":2020,"cases":8,"deaths":0,"geo_id":"AF"} |
Si el archivo está disponible públicamente o si la identidad de Microsoft Entra puede tener acceso a este archivo, debería ver el contenido del archivo mediante la consulta como la que se muestra en los ejemplos siguientes.
En la consulta de ejemplo siguiente se leen archivos JSON y archivos JSON delimitados por líneas y se devuelven todos los documentos como una fila independiente.
select top 10 *
from openrowset(
bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.jsonl',
format = 'csv',
fieldterminator ='0x0b',
fieldquote = '0x0b'
) with (doc nvarchar(max)) as rows
go
select top 10 *
from openrowset(
bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.json',
format = 'csv',
fieldterminator ='0x0b',
fieldquote = '0x0b',
rowterminator = '0x0b' --> You need to override rowterminator to read classic JSON
) with (doc nvarchar(max)) as rows
El documento JSON de la consulta de ejemplo anterior incluye una matriz de objetos. La consulta devuelve cada objeto como fila independiente en el conjunto de resultados. Asegúrese de que puede tener acceso a este archivo. Si el archivo está protegido con una clave SAS o una identidad personalizada, deberá configurar una credencial de nivel de servidor para el inicio de sesión de SQL.
El ejemplo anterior se usa la ruta de acceso completa al archivo. Como alternativa, puede crear un origen de datos externo con la ubicación que apunta a la carpeta raíz del almacenamiento y usar ese origen de datos y la ruta de acceso relativa al archivo en la función OPENROWSET
:
create external data source covid
with ( location = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases' );
go
select top 10 *
from openrowset(
bulk 'latest/ecdc_cases.jsonl',
data_source = 'covid',
format = 'csv',
fieldterminator ='0x0b',
fieldquote = '0x0b'
) with (doc nvarchar(max)) as rows
go
select top 10 *
from openrowset(
bulk 'latest/ecdc_cases.json',
data_source = 'covid',
format = 'csv',
fieldterminator ='0x0b',
fieldquote = '0x0b',
rowterminator = '0x0b' --> You need to override rowterminator to read classic JSON
) with (doc nvarchar(max)) as rows
Si un origen de datos está protegido con una clave SAS o una identidad personalizada, puede configurar el origen de datos con una credencial de ámbito de base de datos.
En las secciones siguientes, puede ver cómo consultar varios tipos de archivos JSON.
En las consultas de los ejemplos anteriores se devuelve cada documento JSON como una sola cadena en una fila independiente del conjunto de resultados. Puede usar las funciones JSON_VALUE
y OPENJSON
para analizar los valores de documentos JSON y devolverlos como valores relacionales, tal como se muestra en el ejemplo siguiente:
date_rep | cases | geo_id |
---|---|---|
2020-07-24 | 3 | AF |
2020-07-25 | 7 | AF |
2020-07-26 | 4 | AF |
2020-07-27 | 8 | AF |
Los ejemplos de consulta leen archivos json que contienen documentos con la infraestructura siguiente:
{
"date_rep":"2020-07-24",
"day":24,"month":7,"year":2020,
"cases":13,"deaths":0,
"countries_and_territories":"Afghanistan",
"geo_id":"AF",
"country_territory_code":"AFG",
"continent_exp":"Asia",
"load_date":"2020-07-25 00:05:14",
"iso_country":"AF"
}
Nota
Si estos documentos se almacenan como JSON delimitado por líneas, debe establecer FIELDTERMINATOR
y FIELDQUOTE
en 0x0b. Si tiene el formato JSON estándar, debe establecer ROWTERMINATOR
en 0x0b.
En la consulta siguiente se muestra cómo usar JSON_VALUE para recuperar valores escalares (date_rep
, countries_and_territories
, cases
) de un documento JSON:
select
JSON_VALUE(doc, '$.date_rep') AS date_reported,
JSON_VALUE(doc, '$.countries_and_territories') AS country,
CAST(JSON_VALUE(doc, '$.deaths') AS INT) as fatal,
JSON_VALUE(doc, '$.cases') as cases,
doc
from openrowset(
bulk 'latest/ecdc_cases.jsonl',
data_source = 'covid',
format = 'csv',
fieldterminator ='0x0b',
fieldquote = '0x0b'
) with (doc nvarchar(max)) as rows
order by JSON_VALUE(doc, '$.geo_id') desc
Una vez que extraiga las propiedades JSON de un documento JSON, puede definir los alias de columna y, opcionalmente, convertir el valor textual a algún tipo.
En la siguiente consulta se usa OPENJSON. Recuperará las estadísticas de COVID informadas en Serbia:
select
*
from openrowset(
bulk 'latest/ecdc_cases.jsonl',
data_source = 'covid',
format = 'csv',
fieldterminator ='0x0b',
fieldquote = '0x0b'
) with (doc nvarchar(max)) as rows
cross apply openjson (doc)
with ( date_rep datetime2,
cases int,
fatal int '$.deaths',
country varchar(100) '$.countries_and_territories')
where country = 'Serbia'
order by country, date_rep desc;
Los resultados son funcionalmente equivalentes a los que se devuelven mediante la función JSON_VALUE
. En algunos casos, OPENJSON
podría tener ventaja sobre JSON_VALUE
:
- En la cláusula
WITH
, puede establecer explícitamente los alias de columna y los tipos de cada propiedad. No es necesario colocar la funciónCAST
en todas las columnas de la listaSELECT
. - Puede que
OPENJSON
sea más rápido si devuelve un gran número de propiedades. Si solo devuelve una o dos propiedades, la funciónOPENJSON
podría sobrecargarse. - Debe usar la función
OPENJSON
si necesita analizar la matriz de cada documento y combinarla con la fila primaria.
En los siguientes artículos de esta serie se muestra: