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

Tip

Microsoft Fabric Data Warehouse é um armazém relacional de escala empresarial com base de data lake, arquitetura pronta para o futuro, IA integrada e novos recursos. Se você não estiver familiarizado com o data warehouse, comece com Fabric Data Warehouse. As cargas de trabalho existentes de pools de SQL dedicados podem ser atualizadas para Fabric para acessar novos recursos em ciência de dados, análise em tempo real e relatórios.

Neste artigo, você aprenderá a escrever uma consulta usando o pool de SQL sem servidor no Azure Synapse Analytics. O objetivo da consulta é ler 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 documentos JSON são separados com caractere de nova linha. 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 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 será suficiente. Se você tiver um arquivo JSON clássico, precisará definir valores 0x0b para rowterminator. OPENROWSET A função analisará 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 sua identidade Microsoft Entra puder acessar esse arquivo, você deverá 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. Verifique se você pode acessar esse arquivo. Se o arquivo estiver protegido com a chave SAS ou a identidade personalizada, você precisará configurar a credencial no nível do servidor para o logon SQL.

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

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

Documento JSON de exemplo

Os exemplos de consulta leem arquivos json que contêm 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"
}

Note

Se esses documentos forem armazenados como JSON delimitado por linha, você precisará definir FIELDTERMINATOR e FIELDQUOTE 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 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, você pode definir aliases de coluna e, opcionalmente, converter o valor textual em algum tipo.

Consultar arquivos JSON usando OPENJSON

A consulta a seguir usa OPENJSON. Ele recuperará 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 JSON_VALUE função. Em alguns casos, OPENJSON pode ter vantagem sobre JSON_VALUE:

  • WITH Na cláusula, 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 da lista SELECT.
  • OPENJSON pode ser mais rápido se você estiver retornando um grande número de propriedades. Se você estiver retornando apenas uma ou duas propriedades, esta função pode ser desnecessária.
  • Você deve usar a OPENJSON função se precisar analisar a matriz de cada documento e juncioná-la com a linha pai.

Próximas Etapas 

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

  • Last updated on