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

Neste artigo, você aprenderá a escrever uma consulta usando o pool SQL sem servidor no Azure Synapse Analytics. O objetivo da consulta é ler arquivos JSON usando OPENROWSET.

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

Ler documentos JSON

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

documento
{"date_rep":"2020-07-24","dia":24,"mês":7,"ano":2020,"casos":3,"mortes":0,"geo_id":"AF"}
{"date_rep":"2020-07-25","dia":25,"mês":7,"ano":2020,"casos":7,"mortes":0,"geo_id":"AF"}
{"date_rep":"2020-07-26","dia":26,"mês":7,"ano":2020,"casos":4,"mortes":0,"geo_id":"AF"}
{"date_rep":"2020-07-27","dia":27,"mês":7,"ano":2020,"casos":8,"mortes":0,"geo_id":"AF"}

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

Ler arquivos JSON

A consulta de exemplo a seguir lê arquivos JSON e JSON delimitados por linha e retorna cada documento 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. Certifique-se de que consegue aceder a este ficheiro. Se o seu ficheiro estiver protegido por uma chave SAS ou uma identidade personalizada, terá de configurar a credencial de nível de servidor para o login 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 o local 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 uma identidade personalizada, pode configurar a fonte de dados utilizando uma credencial com escopo de base de dados.

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

Analisar documentos JSON

As consultas nos exemplos anteriores retornam cada documento JSON como uma única 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 exemplo a seguir:

date_rep	casos	ID de geolocalização
2020-07-24	3	AF
2020-07-25	7	AF
2020-07-26	4	AF
2020-07-27	8	AF

Exemplo de documento JSON

Os exemplos de consulta leem arquivos json contendo documentos com a seguinte estrutura:

{
    "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 delimitado por linha, você precisará definir FIELDTERMINATOR e FIELDQUOTE como 0x0b. Se você tiver o 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 um 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

Depois de extrair propriedades JSON de um documento JSON, pode-se definir aliases de coluna e, opcionalmente, converter o valor textual para outro tipo.

Consultar arquivos JSON usando OPENJSON

A consulta a seguir usa OPENJSON. Ele recuperará as estatísticas 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. Não é necessário colocar a função CAST em todas as colunas da lista SELECT.
OPENJSON pode 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 pode estar sobrecarregada.
Você deve usar a função OPENJSON se precisar analisar a matriz de cada documento e associá-la à linha pai.

Próximos passos

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

Comentários

Esta página foi útil?

Last updated on 2025-04-01