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

Запрос файлов хранилища с помощью бессерверного пула 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

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

Для запрашивания определенных файлов используйте инструкции из раздела 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 предложения.

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

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

Примеры запросов см . в разделе "Чтение свойств из столбцов вложенных объектов" статьи вложенных типов Query 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]

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

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

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