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

10 мин

Бессерверный пул SQL можно использовать для запроса файлов данных в различных распространенных форматах файлов, в том числе:

Текст с разделителями, например файлы данных с разделителями-запятыми (CSV).
Файлы нотации объектов JavaScript (JSON).
Файлы Parquet.

Базовый синтаксис запросов одинаков для всех этих типов файлов и основан на функции OPENROWSET SQL; создает табличный набор строк из данных в одном или нескольких файлах. Например, следующий запрос можно использовать для извлечения данных из CSV-файлов.

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv') AS rows

Функция OPENROWSET включает дополнительные параметры, определяющие такие факторы, как:

Схема результирующего набора строк
Дополнительные параметры форматирования для текстовых файлов с разделителями.

Совет

Полный синтаксис функции OPENROWSET можно найти в документации по Azure Synapse Analytics.

Выходные данные OPENROWSET — это набор строк, которому необходимо назначить псевдоним. В предыдущем примере строки псевдонимов используются для присвоения результирующего набора строк.

Параметр BULK содержит полный URL-адрес расположения в озере данных, содержащего файлы данных. Это может быть отдельный файл или папка с подстановочными знаками для фильтрации типов файлов, которые должны быть включены. Параметр FORMAT указывает на тип запрашиваемых данных. В приведенном выше примере текст с разделителями считывается из всех файлов .csv в папке файлов .

Примечание.

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

Как показано в предыдущем примере, можно использовать подстановочные знаки в параметре BULK для включения файлов в запрос или исключения из него. В следующем списке показаны несколько примеров того, как это можно использовать:

https://mydatalake.blob.core.windows.net/data/files/file1.csv: включите файл file1.csv только в папку files.
https://mydatalake.blob.core.windows.net/data/files/file*.csv: все файлы .csv в папке files с именами, начинающимися на "file".
https://mydatalake.blob.core.windows.net/data/files/*: все файлы в папке files.
https://mydatalake.blob.core.windows.net/data/files/**: все файлы в папке files и во вложенных папках.

Кроме того, в параметре BULK можно указать несколько путей к файлам, разделяя каждый путь запятой.

Запрос текстовых файлов с разделителями

Текстовые файлы с разделителями — это распространенный формат файлов во многих компаниях. Конкретное форматирование, используемое в файлах с разделителями, может отличаться, например:

Со строкой заголовка и без нее.
Значения, разделенные запятыми и табуляцией.
Завершение строк в стиле Windows и UNIX.
Значения, не заключенные в кавычки и заключенные в кавычки, и символы экранирования.

Независимо от типа используемого файла с разделителями можно считывать данные из них с помощью функции OPENROWSET с параметром CSV FORMAT и с другими параметрами, необходимыми для обработки конкретной информации о форматировании данных. Например:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    FIRSTROW = 2) AS rows

PARSER_VERSION используется для определения того, как запрос интерпретирует кодировку текста, используемую в файлах. Версия 1.0 используется по умолчанию и поддерживает широкий спектр кодировок файлов, а версия 2.0 поддерживает меньше кодировок, но обеспечивает лучшую производительность. Параметр FIRSTROW используется для пропуска строк в текстовом файле, чтобы исключить неструктурированный текст или пропустить строку, содержащую заголовки столбцов.

При работе с текстовыми файлами с разделителями могут потребоваться дополнительные параметры:

FIELDTERMINATOR — символ, используемый для разделения значений полей в каждой строке. Например, файл с разделителями-табуляции разделяет поля символом TAB (\t). По умолчанию признаком конца поля считается запятая (,).
ROWTERMINATOR — символ, используемый для обозначения конца строки данных. Например, в стандартном текстовом файле Windows используется сочетание символа возврата каретки (CR) и символа перевода строки (LF), который обозначается кодом \n; в то время как в текстовых файлах в стиле UNIX используется один символ перевода строки, который можно указать с помощью кода 0x0a.
FIELDQUOTE — символ, используемый для заключения строковых значений в кавычки. Например, чтобы убедиться, что запятая в поле адреса 126 Main St, apt 2 не распознается как разделитель полей, можно заключить все значение поля в кавычки следующим образом: "126 Main St, apt 2". Двойная кавычка (") — это символ кавычки поля по умолчанию.

Совет

Дополнительные параметры при работе с текстовыми файлами с разделителями см. в документации по Azure Synapse Analytics.

Указание схемы набора строк

Обычно текстовые файлы с разделителями включают имена столбцов в первую строку. Функция OPENROWSET может использовать эту функцию для определения схемы результирующего набора строк и автоматического вывода типов данных столбцов на основе значений, которые они содержат. Например, рассмотрим следующий текст с разделителями:

product_id,product_name,list_price
123,Widget,12.99
124,Gadget,3.99

Данные состоят из следующих трех столбцов:

product_id (целое число)
product_name (строка)
list_price (десятичное число)

Следующий запрос можно использовать для извлечения данных с правильными именами столбцов и соответствующим образом выведенных типов данных SQL Server (в данном случае INT, NVARCHAR и DECIMAL)

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE) AS rows

Параметр HEADER_ROW (доступен только при использовании средства синтаксического анализа версии 2.0), предписывает обработчику запросов использовать первую строку данных в каждом файле для получения имен столбцов, как показано ниже:

product_id	product_name	list_price
123	Мини-приложение	12,9900
124	Gadget	3,9900

Теперь рассмотрим следующие данные:

123,Widget,12.99
124,Gadget,3.99

В этом варианте файл не содержит имена столбцов в первой строке; поэтому, хотя типы данных по-прежнему можно определить, имена столбцов будут иметь значение C1, C2, C3 и т. д.

C1	C2	C3
123	Мини-приложение	12,9900
124	Gadget	3,9900

Чтобы явно указать имена столбцов и типы данных, можно переопределить имена столбцов по умолчанию и вывести типы данных, предоставив определение схемы в предложении WITH:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0')
WITH (
    product_id INT,
    product_name VARCHAR(20) COLLATE Latin1_General_100_BIN2_UTF8,
    list_price DECIMAL(5,2)
) AS rows

Этот запрос выводит ожидаемые результаты:

product_id	product_name	list_price
123	Мини-приложение	12,99
124	Gadget	3,99

Совет

При работе с текстовыми файлами может возникнуть некоторая несовместимость с данными в кодировке UTF-8 и параметрами сортировки, используемыми в базе данных master для бессерверного пула SQL. Чтобы решить эту проблему, можно указать в схеме совместимые параметры сортировки для отдельных столбцов VARCHAR. Дополнительные сведения см. в руководстве по устранению неполадок.

Запрос по JSON-файлам

JSON — это популярный формат для веб-приложений, которые обмениваются данными через интерфейсы REST или используют хранилища данных NoSQL, такие как Azure Cosmos DB. Данные не редко сохраняются в виде документов JSON в файлах в озере данных для анализа.

Например, JSON-файл, определяющий отдельный продукт, может выглядеть следующим образом:

{
    "product_id": 123,
    "product_name": "Widget",
    "list_price": 12.99
}

Чтобы вернуть данные продукта из папки с несколькими JSON-файлами в этом формате, можно использовать следующий SQL-запрос:

SELECT doc
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

OPENROWSET не имеет определенного формата для JSON-файлов, поэтому необходимо использовать формат CSV с параметром FIELDTERMINATOR, FIELDQUOTE и ROWTERMINATOR, равным 0x0b, и схеме, которая содержит один столбец NVARCHAR(MAX). Результатом этого запроса будет набор строк, содержащий один столбец документов JSON, как показано ниже:

doc
{"product_id":123,"product_name":"Widget","list_price": 12.99}
{"product_id":124,"product_name":"Gadget","list_price": 3.99}

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

SELECT JSON_VALUE(doc, '$.product_name') AS product,
           JSON_VALUE(doc, '$.list_price') AS price
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

Этот запрос вернет следующий набор строк:

продукт	цена
Мини-приложение	12,99
Gadget	3,99

Запрос по файлам Parquet

Parquet — это распространенный формат для обработки больших данных в распределенном хранилище файлов. Это эффективный формат данных, оптимизированный для сжатия и аналитического запроса.

В большинстве случаев схема данных внедряется в файл Parquet, поэтому необходимо только указать параметр BULK с путем к файлам, которые требуется прочитать, и параметр FORMAT формата parquet; например:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.*',
    FORMAT = 'parquet') AS rows

Запрос по секционированным данным

Обычно в озере данных секционирование данных путем разделения на несколько файлов во вложенных папках в соответствии с критериями секционирования. Это позволяет распределенным системам обработки работать параллельно с несколькими секциями данных или легко устранять операции чтения данных из определенных папок на основе критериев фильтрации. Например, предположим, что вам нужно эффективно обрабатывать данные заказа на продажу, и часто нужно фильтровать их по годам и месяцам, в которых были размещены заказы. Вы можете секционировать данные с помощью папок, как показано ниже:

/Заказы
- /year=2020
  - /month=1
    - /01012020.parquet
    - /02012020.parquet
    - ...
  - /month=2
    - /01022020.parquet
    - /02022020.parquet
    - ...
  - ...
- /year=2021
  - /month=1
    - /01012021.parquet
    - /02012021.parquet
    - ...
  - ...

Чтобы создать запрос, который фильтрует результаты для получения заказов только за январь и февраль 2020 г., можно использовать следующий код:

SELECT *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/orders/year=*/month=*/*.*',
    FORMAT = 'parquet') AS orders
WHERE orders.filepath(1) = '2020'
    AND orders.filepath(2) IN ('1','2');

Нумерованные параметры пути к файлам в предложении WHERE ссылаются на подстановочные знаки в именах папок в пути BULK, поэтому параметр 1 — это * (звездочка) в имени папки year=* и параметр 2 — * (звездочка) в имени папки month=*.

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

Запрос текстовых файлов с разделителями

Указание схемы набора строк

Запрос по JSON-файлам

Запрос по файлам Parquet

Запрос по секционированным данным

Обратная связь