Consulta de archivos JSON mediante un grupo de SQL sin servidor en Azure Synapse Analytics

• Iniciar una prueba gratuita de Fabric.
• Asistente de migración para almacén de datos en tejido.

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 mediante 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 carácter de nueva línea. Las extensiones comunes para estos tipos de archivos son jsonl, ldjsony ndjson.

Leer documentos JSON

La manera más sencilla de ver el contenido del archivo JSON es proporcionar la dirección URL del archivo a la OPENROWSET función, especificar csv FORMATy establecer 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, tendría que establecer valores 0x0b para rowterminator. OPENROWSET function analizará JSON y devolverá todos los documentos en el formato siguiente:

Si el archivo está disponible públicamente o si su identidad de Microsoft Entra puede acceder a este archivo, debería ver el contenido del archivo mediante la consulta como la que se muestra en los ejemplos siguientes.

Leer archivos JSON

La consulta de ejemplo siguiente lee archivos JSON y JSON delimitados por líneas y devuelve cada documento 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 una fila independiente en el conjunto de resultados. Asegúrese de que puede acceder a este archivo. Si el archivo está protegido con la clave SAS o la identidad personalizada, tendría que configurar las credenciales a nivel de servidor para el inicio de sesión de SQL.

Uso del origen de datos

En 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 de la OPENROWSET función:

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 credenciales con ámbito de base de datos.

En las secciones siguientes, puede ver cómo consultar varios tipos de archivos JSON.

Análisis de documentos JSON

Las consultas de los ejemplos anteriores devuelven cada documento JSON como una sola cadena en una fila independiente del conjunto de resultados. Puede usar funciones JSON_VALUE y OPENJSON analizar los valores en documentos JSON y devolverlos como valores relacionales, como se muestra en el ejemplo siguiente:

Documento JSON de ejemplo

Los ejemplos de consulta leen archivos JSON que contienen documentos con la estructura 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"
}

Consulta de archivos JSON mediante JSON_VALUE

La consulta siguiente muestra cómo usar JSON_VALUE para recuperar valores escalares (date_rep, countries_and_territories, cases) de documentos 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 alias de columna y, opcionalmente, convertir el valor textual en algún tipo.

Consulta de archivos JSON mediante OPENJSON

La consulta siguiente usa OPENJSON. Recuperará las estadísticas de COVID notificadas 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 iguales que los resultados devueltos mediante la JSON_VALUE función . En algunos casos, OPENJSON puede tener ventaja sobre JSON_VALUE:

• En la WITH cláusula puede establecer explícitamente los alias de columna y los tipos de cada propiedad. No es necesario colocar la CAST función en todas las columnas de SELECT la lista.
• OPENJSON podría ser más rápido si devuelves un gran número de propiedades. Si solo devuelve una o dos propiedades, la función OPENJSON podría ser una carga.
• Debe usar la OPENJSON función si necesita analizar la matriz de cada documento y combinarla con la fila primaria.

Pasos siguientes

Los siguientes artículos de esta serie mostrarán cómo:

• Consulta de carpetas y varios archivos
• Creación y uso de vistas