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 OPENROWSET função, 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 function analisará JSON e retornará todos os documentos no seguinte formato:

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 arquivo estiver protegido com chave SAS ou identidade personalizada, você precisará configurar a credencial no nível do servidor para 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 OPENROWSET função:

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 chave SAS ou identidade personalizada, você poderá configurar a fonte de dados com credencial de escopo de banco 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 analisar os valores em documentos JSON e OPENJSON retorná-los como valores relacionais, como é mostrado no exemplo a seguir:

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

Consultar arquivos JSON usando JSON_VALUE

A consulta abaixo mostra como usar JSON_VALUE para recuperar valores escalares (date_rep, , countries_and_territoriescases) 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 propriedades JSON de um documento JSON, você pode definir aliases de coluna e, opcionalmente, converter o valor textual para algum 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 JSON_VALUE função. 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 CAST função em todas as colunas da SELECT lista.
• 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 OPENJSON função pode estar sobrecarregada.
• Você deve usar a função se precisar analisar a OPENJSON matriz de cada documento e associá-la à linha pai.

Próximos passos

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

• Consultando pastas e vários arquivos
• Criar e utilizar vistas