Отправка запросов к файлам хранилища с помощью бессерверного пула SQL в Azure Synapse Analytics

  • Статья

Бессерверный пул SQL позволяет запрашивать данные в озере данных. Предоставляемая контактная зона для запросов T-SQL позволяет запрашивать частично структурированные и неструктурированные данные. Возможность запрашивания данных поддерживает следующие аспекты T-SQL:

  • Полная контактная зона инструкции SELECT, включая большинство функций и операторов SQL.
  • CREATE EXTERNAL TABLE AS SELECT (CETAS) создает внешнюю таблицу, а затем в параллельном режиме экспортирует результаты инструкции SELECT Transact-SQL в службу хранилища Azure.

Дополнительные сведения о поддерживаемых и неподдерживаемых возможностях см. в статье с общими сведениями о бессерверном пуле SQL или следующих статьях:

Обзор

Чтобы оптимизировать процесс запрашивания данных, размещенных в файлах службы хранилища Azure, бессерверный пул SQL использует функцию OPENROWSET со следующими дополнительными возможностями:

Запрашивание файлов PARQUET

Для запроса исходных данных Parquet используйте FORMAT = 'PARQUET':

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

См. примеры запросов к файлам в формате PARQUET.

Запросы к CSV-файлам

Чтобы запросить исходные данные в формате CSV, используйте параметр FORMAT = 'CSV'. Вы можете указать схему CSV-файла в функции OPENROWSET при запрашивании CSV-файлов:

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.csv', FORMAT = 'CSV', PARSER_VERSION='2.0') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

Для настройки правил синтаксического анализа пользовательских файлов CSV можно использовать некоторые дополнительные параметры:

  • ESCAPE_CHAR = 'char'. Определяет символ в файле, который используется для экранирования самого себя и всех значений разделителей. Если за escape-символом следует значение, отличное от него самого или какого-либо из значений разделителей, при считывании этого значения escape-символ пропускается. Параметр ESCAPE_CHAR будет применяться независимо от того, включен ли параметр FIELDQUOTE. Он не будет использоваться для экранирования символа кавычек. Символ кавычек нужно экранировать другим символом кавычек. Такой символ может использоваться в значении столбца только в том случае, если значение инкапсулировано с помощью символов кавычек.
  • FIELDTERMINATOR ='field_terminator'. Определяет используемый признак конца поля. По умолчанию признаком конца поля считается запятая ( , ).
  • ROWTERMINATOR ='row_terminator'. Определяет используемый признак конца строки. По умолчанию признаком конца строки считается символ новой строки (\r\n).

Формат запроса DELTA LAKE

Для запроса исходных данных Delta Lake, используйте FORMAT = 'DELTA' и укажите корневую папку, содержащую ваши файлы Delta Lake.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder', FORMAT = 'DELTA') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

Корневая папка должна содержать подпапку с именем _delta_log. Ознакомьтесь с примерами использования в статье Запрос формата Delta Lake.

Схема файла

Язык SQL в Synapse SQL позволяет определить схему файла как часть функции OPENROWSET и считывать все столбцы или их подмножество. Либо служба попытается автоматически определить типы столбцов из файла с помощью вывода схемы.

Считывание выбранного подмножества столбцов

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

  • Если используются CSV-файлы данных, для считывания всех столбцов укажите имена и типы данных столбцов. Если нужно считать подмножество столбцов, используйте порядковые номера, чтобы выбрать столбцы из исходных файлов данных по порядковому номеру. Столбцы будут привязаны к порядковым обозначениям.
  • Если используются файлы данных PARQUET, укажите имена столбцов, совпадающие с именами столбцов в исходных файлах данных. Столбцы будут привязаны к именам.
SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (
      C1 int, 
      C2 varchar(20),
      C3 varchar(max)
) as rows

Для каждого столбца необходимо указать его имя и тип в предложении WITH. См. примеры в разделе Возвращение подмножества столбцов.

Вывод схемы

Если вы не указываете предложение WITH в инструкции OPENROWSET, служба попытается автоматически определить (вывести) схему из базовых файлов.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET')

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

Запрашивание нескольких файлов или папок

Чтобы выполнить запрос T-SQL набора файлов в папке или набора папок в качестве одной сущности или набора строк, укажите путь к папке или шаблон пути (с подстановочными знаками) к набору файлов или папок.

Применяются следующие правила.

  • Шаблоны можно использовать как часть пути к папке или в имени файла.
  • В одной части пути к каталогу или имени файла могут использоваться несколько шаблонов.
  • Если указано несколько подстановочных знаков, в результирующий набор файлов будут включены все файлы, связанные со всеми соответствующими путями.
SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/myroot/*/mysubfolder/*.parquet', FORMAT = 'PARQUET' ) as rows

См. примеры запросов к нескольким файлам и папкам.

Функции метаданных файлов

Функция filename

Эта функция возвращает имя файла, из которого получена строка.

Для запрашивания определенных файлов используйте инструкции из раздела Filename.

Тип возвращаемых данных — nvarchar(1024). Для оптимальной производительности всегда приводите результат функции filename к соответствующему типу данных. Если используется символьный тип данных, убедитесь, что используется соответствующая длина.

Функция filepath

Эта функция возвращает полный путь или часть пути:

  • При вызове без параметра она возвращает полный путь, из которого была получена строка.
  • При вызове с параметром она возвращает ту часть пути, которая соответствует подстановочному знаку в позиции, определенной в этом параметре. Например, при значении параметра 1 возвращается часть пути, соответствующая первому подстановочному знаку.

См. сведения в разделе Filepath.

Тип возвращаемых данных — nvarchar(1024). Для оптимальной производительности всегда приводите результат функции filepath к соответствующему типу данных. Если используется символьный тип данных, убедитесь, что используется соответствующая длина.

Работа со сложными типами, а также вложенными и повторяющимися структурами данных

Чтобы оптимизировать работу с данными, которые хранятся во вложенных или повторяющихся типах данных (например, в файлах Parquet), в бессерверный пул SQL добавлены указанные ниже расширения.

Вложенные или повторяющиеся данные в проекте

Для проецирования данных выполните инструкцию SELECT для файлов PARQUET, которые содержат столбцы вложенных типов данных. При выводе вложенные значения сериализуются в формат JSON и возвращаются с типом данных SQL varchar(8000).

    SELECT * FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

См. сведения о запрашивании вложенных типов данных в разделе Вложенные или повторяющиеся данные в проекте.

Доступ к элементам из вложенных столбцов

Чтобы получить доступ к вложенным элементам из вложенных столбцов, таким как Struct, используйте точечную нотацию для конкатенации имен полей в строку пути. Передайте полученный путь в параметре column_name предложения WITH функции OPENROWSET.

Ниже приводится пример фрагмента с таким синтаксисом.

    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    WITH ({'column_name' 'column_type',})
    [AS alias]
    'column_name' ::= '[field_name.] field_name'

По умолчанию функция OPENROWSET сопоставляет имя исходного поля и путь к нему с именами столбцов в предложении WITH. С помощью предложения WITH можно получить доступ к элементам на разных уровнях вложенности в пределах одного исходного файла PARQUET.

Возвращаемые значения

  • Функция возвращает скалярное значение, например int, decimal и varchar, из указанного элемента и по указанному пути для всех типов Parquet, которые не находятся в группе вложенных типов.
  • Если путь указывает на элемент вложенного типа, функция возвращает фрагмент JSON, который начинается с верхнего элемента указанного пути. Этот фрагмент JSON имеет тип varchar(8000).
  • Если свойство не удается найти по указанному значению column_name, функция возвращает ошибку.
  • Если свойство не удается найти по указанному значению column_path, в зависимости от настроенного режима PATH, функция возвращает ошибку (строгий режим) или значение NULL (нестрогий режим).

См. примеры таких запросов в разделе Доступ к элементам из вложенных столбцов.

Доступ к элементам из повторяющихся столбцов

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

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

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

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

Фрагмент такого синтаксиса приводится ниже.

    SELECT
       { JSON_VALUE (column_name, path_to_sub_element), }
       { JSON_QUERY (column_name [ , path_to_sub_element ]), )
    FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

См. примеры запросов для получения доступа к элементам из повторяющихся столбцов.

Примеры запросов

Чтобы узнать больше о том, как запрашивать данные различных типов, воспользуйтесь примерами запросов.

Инструменты

Инструменты, необходимые для отправки запросов: - Azure Synapse Studio - Azure Data Studio - SQL Server Management Studio

Настройка демонстрации

Для начала создайте базу данных, в которой будут выполняться запросы. Затем инициализируйте объекты, выполнив скрипт настройки для этой базы данных.

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

Примечание

Базы данных используются только для просмотра метаданных, а не для реальных данных. Запишите имя используемой базы данных, ведь оно потребуется позже.

CREATE DATABASE mydbname;

Предоставление демонстрационных данных

Демонстрационные данные содержат указанные ниже наборы данных.

  • NYC Taxi - Yellow Taxi Trip Records (Такси Нью-Йорка. Записи поездок такси желтого цвета) — часть общедоступного набора данных по Нью-Йорку в формате CSV и Parquet
  • Набор данных по населению в формате CSV
  • Примеры файлов Parquet со вложенными столбцами:
  • Книги в формате JSON
Путь к папке Описание
/csv/ Родительская папка для данных в формате CSV.
/csv/population/
/csv/population-unix/
/csv/population-unix-hdr/
/csv/population-unix-hdr-escape
/csv/population-unix-hdr-quoted		 Папки с файлами данных о населении в разных форматах CSV.
/csv/taxi/ Папка с общедоступными файлами данных по Нью-Йорку в формате CSV.
/parquet/ Родительская папка для данных в формате Parquet.
/parquet/taxi Общедоступные файлы данных по Нью-Йорку в формате Parquet, секционированные по годам и месяцам с использованием схемы секционирования Hive на платформе Hadoop.
/parquet/nested/ Примеры файлов Parquet со вложенными столбцами:
/json/ Родительская папка для данных в формате JSON.
/json/books/ Файлы JSON с данными книг.

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

Сведения о запрашивании файлов разных типов, о создании и использовании представлений см. в следующих статьях: