Запрос ФАЙЛОВ JSON с помощью бессерверного пула SQL в Azure Synapse Analytics

2025-04-03

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

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

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

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

DOC
{"date_rep":"2020-07-24","day":24,"month":7,"year":2020,"cases":3,"смертей":0,"geo_id":"AF"}
{"date_rep":"2020-07-25","day":25,"month":7,"year":2020,"cases":7,"смертей":0,"geo_id":"AF"}
{"date_rep":"2020-07-26","day":26,"month":7"year":2020,"cases":4,"смертей":0,"geo_id":"AF"}
{"date_rep":"2020-07-27","day":27,"month":7,"year":2020,"cases":8,"смертей":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 и возвращать их в виде реляционных значений, как показано в следующем примере:

дата_представление	случаи	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 , если необходимо проанализировать массив из каждого документа и присоединить его к родительской строке.

Дальнейшие действия

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