Запрос файлов JSON с помощью бессерверного пула SQL в Azure Synapse Analytics

Статья
04/27/2023

В этой статье вы узнаете, как написать запрос с помощью бессерверного пула SQL в Azure Synapse Analytics. Цель запроса — чтение JSON-файлов с помощью OPENROWSET.

Стандартные файлы JSON, в которых несколько документов JSON хранятся в виде массива JSON.
Разделенные строками файлы JSON, в которых документы JSON разделены символом новой строки. Распространенными расширениями этих типов файлов являются jsonl, ldjson и ndjson.

Чтение документов JSON

Самый простой способ просмотреть содержимое файла JSON — предоставить URL-адрес файла для функции OPENROWSET, указать csv FORMAT и задать значения 0x0b для fieldterminator и fieldquote. Этого достаточно для чтения файлов JSON, разделенных строками. При наличии классического файла JSON необходимо задать переменной rowterminator значение 0x0b. Функция OPENROWSET будет анализировать файл JSON и возвращать каждый документ в следующем формате:

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

Если файл доступен в общедоступном виде или если удостоверение Microsoft Entra может получить доступ к этому файлу, вы увидите содержимое файла с помощью запроса, как показано в следующих примерах.

Чтение файлов JSON

Следующий пример запроса считывает файлы JSON и файлы JSON с разделителями строк и возвращает каждый документ в виде отдельной строки.

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

Документ JSON в предыдущем примере запроса содержит массив объектов. Запрос возвращает каждый объект в виде отдельной строки в результирующем наборе. Убедитесь, что у вас есть доступ к этому файлу. Если файл защищен с помощью ключа SAS или пользовательского удостоверения, необходимо настроить учетные данные на уровне сервера для входа в SQL.

Использование источника данных

В предыдущем примере используется полный путь к файлу. Вместо этого можно создать внешний источник данных с информацией о расположении корневой папки хранилища и указать этот источник данных вместе с относительным путем к файлу в функции 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

Если источник данных защищен с помощью ключа SAS или пользовательского удостоверения, можно настроить источник данных с учетными данными для базы данных.

В следующих разделах показано, как выполнять запросы к различным типам файлов JSON.

Анализ документов JSON

Запросы в предыдущих примерах возвращают каждый документ JSON в виде одной строки в отдельной строке результирующего набора. Функции JSON_VALUE и OPENJSON можно использовать для анализа значений в документах JSON и их возврата в виде реляционных значений, как показано в следующем примере.

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

Пример документа JSON

Примеры запроса считывают файлы json, содержащие документы со следующей структурой:

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

Примечание.

Если эти документы сохранены в виде файла JSON с разделителями строк, переменным FIELDTERMINATOR и FIELDQUOTE нужно задать значение 0x0b. Если используется стандартный формат JSON, переменной ROWTERMINATOR нужно задать значение 0x0b.

Запрашивайте файлы JSON с помощью JSON_VALUE

В следующем запросе показано, как использовать функцию JSON_VALUE для получения скалярных значений (date_rep, countries_and_territories, cases) из документов 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

После извлечения свойств JSON из документа JSON, можно будет определить псевдонимы столбцов и, по желанию, привести текстовое значение к какому-либо типу.

Запрашивание файлов JSON с помощью OPENJSON

В следующем запросе используется OPENJSON. Будут получены статистические данные по COVID, опубликованные в Сербии.

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;

Функционально результаты не отличаются от результатов, возвращаемых с помощью функции JSON_VALUE. В некоторых случаях OPENJSON может иметь преимущество перед JSON_VALUE:

В предложении WITH можно явно задать псевдонимы столбцов, а также типы для каждого свойства. Функцию CAST не нужно помещать в каждый столбец в списке SELECT.
При возврате большого количества свойств, OPENJSON может выполняться быстрее. Если вы возвращаете только 1-2 свойства, функция OPENJSON может оказаться излишней.
Функцию OPENJSON следует использовать если вам нужно проанализировать массив из каждого документа и присоединить его к родительской строке.

Следующие шаги

В следующих статьях этой серии показано: