Consultar arquivos JSON usando o pool de SQL sem servidor no Azure Synapse Analytics

Neste artigo, você aprenderá a escrever uma consulta usando o pool de SQL sem servidor no Azure Synapse Analytics. A consulta tem como objetivo a leitura de arquivos JSON usando OPENROWSET.

• Arquivos JSON padrão em que vários documentos JSON são armazenados como uma matriz JSON.
• Arquivos JSON delimitados por linha, em que os documentos JSON são separados por caractere de nova linha. As extensões comuns para esses tipos de arquivos são jsonl, ldjson e ndjson.

Ler documentos JSON

A maneira mais fácil de ver o conteúdo do arquivo JSON é fornecer a URL do arquivo para a função OPENROWSET, especificar csv FORMAT e definir valores 0x0b para fieldterminator e fieldquote. Se você precisar ler arquivos JSON delimitados por linha, isso será suficiente. Se você tiver um arquivo JSON clássico, precisará definir valores 0x0b para rowterminator. A função OPENROWSET analisará o JSON e retornará todos os documentos no seguinte formato:

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"}

Se o arquivo estiver disponível publicamente ou se a sua identidade do Microsoft Entra puder acessar esse arquivo, você deverá ver o conteúdo do arquivo usando a consulta como a mostrada nos exemplos a seguir.

Leitura de arquivos JSON

A consulta de exemplo a seguir lê os arquivos JSON e arquivos JSON delimitados por linha e retorna todos os documentos como uma linha separada.

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

O documento JSON na consulta de exemplo anterior inclui uma matriz de objetos. A consulta retorna cada objeto como uma linha separada no conjunto de resultados. Verifique se você pode acessar este arquivo. Se o arquivo estiver protegido com uma chave SAS ou identidade personalizada, você precisará configurar a credencial no nível do servidor para logon do SQL.

Uso da fonte de dados

O exemplo anterior usa o caminho completo para o arquivo. Como alternativa, você pode criar uma fonte de dados externa com a localização que aponta para a pasta raiz do armazenamento e usar essa fonte de dados e o caminho relativo para o arquivo na função 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

Se uma fonte de dados estiver protegida com uma chave SAS ou identidade personalizada, você poderá configurar a fonte de dados com a credencial no escopo do banco de dados.

Nas seções a seguir, você poderá ver como consultar vários tipos de arquivos JSON.

Analisar documentos JSON

As consultas nos exemplos anteriores retornam cada documento JSON como uma cadeia de caracteres em uma linha separada do conjunto de resultados. Você pode usar funções JSON_VALUE e OPENJSON para analisar os valores em documentos JSON e retorná-los como valores relacionais, como é mostrado no seguinte exemplo:

date_rep	cases	geo_id
24/07/2020	3	AF
2020-07-25	7	AF
2020-07-26	4	AF
2020-07-27	8	AF

Documento JSON de exemplo

Os exemplos de consulta leem arquivos json que contêm documentos com a estrutura a seguir:

{
    "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"
}

Observação

Se esses documentos forem armazenados como JSON delimitados por linha, você precisará definir FIELDTERMINATOR e FIELDQUOTE como 0x0B. Se você tiver um formato JSON padrão, precisará definir ROWTERMINATOR como 0x0b.

Consultar arquivos JSON usando JSON_VALUE

A consulta abaixo mostra como 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

Depois de extrair as propriedades JSON de um documento JSON, você poderá definir aliases de coluna e, opcionalmente, converter o valor textual em algum tipo.

Consultar arquivos JSON usando OPENJSON

A consulta a seguir usa OPENJSON. Ela recuperará as estatísticas de COVID relatadas na Sérvia:

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;

Os resultados são funcionalmente iguais aos resultados retornados usando a função JSON_VALUE. Em alguns casos, OPENJSON pode ter vantagem sobre JSON_VALUE:

• Na cláusula WITH, você pode definir explicitamente os aliases de coluna e os tipos para cada propriedade. Você não precisa colocar a função CAST em todas as colunas na lista SELECT.
• OPENJSON poderá ser mais rápido se você estiver retornando um grande número de propriedades. Se você estiver retornando apenas 1-2 propriedades, a função OPENJSON poderá ser sobrecarga.
• Você deverá usar a função OPENJSON se precisar analisar a matriz de cada documento e associá-la à linha pai.

Próximas etapas

Os próximos artigos desta série demonstrarão como:

• Consultar pastas e arquivos múltiplos
• Criar e usar modos de exibição