Поделиться через

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

  • Статья

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

Дополнительные сведения о том, что в настоящее время поддерживается или не поддерживается, можно найти в обзоре бессерверного пула 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. Примеры использования см. в файлах Query Delta Lake (v1).

Схема файла

Язык 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. Примеры см. в разделе "Чтение CSV-файлов" без указания всех столбцов.

Вывод схемы

Исключив условие 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.

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

Функция filepath

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

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

Для получения дополнительной информации прочтите раздел Filepath в статье Запрос определенных файлов.

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

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

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

Проецировать вложенные или повторяющиеся данные

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

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

Для получения дополнительной информации см. раздел Проецирование вложенных или повторяющихся данных статьи Запрос типов Query Parquet.

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

Чтобы получить доступ к вложенным элементам из вложенного столбца, такой как структура, используйте точечную нотацию для объединения имен полей в путь. Укажите путь как 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. Элементы, содержащиеся на разных уровнях вложенности в одном исходном файле Parquet, можно получить, используя WITH условие.

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

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

Примеры запросов см. в разделе "Чтение свойств из столбцов вложенных объектов" статьи "Типы данных Parquet: вложенные объекты".

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

Чтобы получить доступ к элементам из повторяющегося столбца, например элемента массива или карты, используйте функцию 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]

Вы можете найти примеры запросов для доступа к элементам из повторяющихся столбцов в статье "Типы вложенных структур Parquet".

Связанный контент

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