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

Примечание.

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

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

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

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

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

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

С строкой заголовка и без нее.
Значения с разделителями-запятыми и табуляции.
Окончания строк стиля Windows и Unix.
Не кавычки и кавычки значений и экранирование символов.

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

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	Приспособление	3.9900

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

123,Widget,12.99
124,Gadget,3.99

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

C1	C2	C3
123	Виджет	12.9900
124	Приспособление	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	Приспособление	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, как показано ниже.

доктор
{"product_id":123,"product_name":"Мини-приложение","list_price": 12.99}
{"product_id":124,"product_name":"Гаджет","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
Приспособление	3.99

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

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

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

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
    - /0202020.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, -so параметр 1 является * в имени папки год=*, а параметр 2 — в имени папки месяца=*.

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

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

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

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

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

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

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