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

Виртуализация данных с помощью базы данных SQL Azure (предварительная версия)

Применимо к: База данных SQL Azure

Функция виртуализации данных базы данных SQL Azure позволяет выполнять запросы Transact-SQL (T-SQL) к файлам, сохраняющим данные в общих форматах данных, таких как CSV (без необходимости использования разделителя текста), Parquet и Delta (1.0). Эти данные можно запросить в Azure Data Lake Storage 2-го поколения или хранилище BLOB-объектов Azure и объединить их с локально хранимыми реляционными данными с помощью соединений. Таким образом, вы можете прозрачно получать доступ к внешним данным (в режиме только для чтения) при сохранении его в исходном формате и расположении , также известном как виртуализация данных.

Обзор

Виртуализация данных предоставляет два способа запроса файлов, предназначенных для различных наборов сценариев:

  • Синтаксис OPENROWSET, оптимизированный для нерегламентированного запроса файлов. Обычно используется для быстрого изучения содержимого и структуры нового набора файлов.
  • Синтаксис CREATE EXTERNAL TABLE — оптимизирован для повторяющегося запроса файлов с использованием идентичного синтаксиса, как если бы данные хранились локально в базе данных. Для внешних таблиц нужно выполнить некоторые действия по подготовке (по сравнению с синтаксисом OPENROWSET), но это позволяет более точно управлять доступом к данным. Внешние таблицы обычно используются для аналитических рабочих нагрузок и отчетов.

В любом случае внешний источник данных должен быть создан с помощью синтаксиса CREATE EXTERNAL DATA SOURCE T-SQL, как показано в этой статье.

Форматы файлов

Форматы файлов Parquet и разделенного текста (CSV) поддерживаются напрямую. Формат файлов JSON поддерживается косвенно путем указания формата файлов CSV, в котором запросы возвращают каждый документ в виде отдельной записи. Кроме того, можно проанализировать строки с помощью JSON_VALUE и OPENJSON.

Типы хранилищ

Файлы можно хранить в Azure Data Lake Storage 2-го поколения или Хранилище BLOB-объектов Azure. Чтобы запросить файлы, необходимо указать расположение в определенном формате и использовать префикс типа расположения, соответствующий типу внешнего источника и конечной точки или протокола, например в следующих примерах:

--Blob Storage endpoint
abs://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet
--or
abs://<storage_account_name>.blob.core.windows.net/<container_name>/

--Data Lake endpoint
adls://<container>@<storage_account>.dfs.core.windows.net/<path>/<file_name>.parquet
--or
adls://<storage_account_name>.dfs.core.windows.net/<container_name>/

Это важно

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

Универсальный https:// префикс поддерживается только для BULK INSERT, но не для других случаев использования, таких как OPENROWSET или EXTERNAL TABLE.

Начало работы

Если вы не знакомы с виртуализацией данных и хотите быстро протестировать функциональные возможности, начните с запроса общедоступных наборов данных, доступных в Открытых наборах данных Azure, таких как набор данных Bing COVID-19, разрешающий анонимный доступ.

Используйте следующие конечные точки для отправки запросов к наборам данных Bing COVID-19:

  • Паркет: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet
  • CSV: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv

Для быстрого запуска выполните этот простой запрос T-SQL, чтобы получить первую информацию о наборе данных. Этот запрос использует OPENROWSET для запроса файла, хранящегося в общедоступной учетной записи хранения:

--Quick query on a file stored in a publicly available storage account:
SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet',
 FORMAT = 'parquet'
) AS filerows;

Вы можете продолжить изучение набора данных, добавив WHERE, GROUP BY и другие предложения на основе результатов первого запроса.

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

Доступ к неопубликованным учетным записям хранения

Пользователь, вошедший в базу данных SQL Azure, должен быть авторизован для доступа к файлам и запросам файлов, хранящихся в учетных записях хранения, не являющихся общедоступными. Шаги авторизации зависят от того, как база данных SQL Azure проверяет подлинность хранилища. Типы проверки подлинности и любые связанные параметры не предоставляются напрямую с каждым запросом. Они инкапсулируются в объект учетных данных в области базы данных, хранящийся в пользовательской базе данных. Учетные данные используются базой данных для доступа к учетной записи хранения в любое время выполнения запроса.

База данных SQL Azure поддерживает следующие типы проверки подлинности:

  • Подпись общего доступа (SAS)
  • Манажируемая идентичность
  • Сквозная проверка подлинности Microsoft Entra с помощью идентификации пользователя

Параметры подписанного доступа (SAS) предоставляют делегированный доступ к файлам в учетной записи хранения. SAS обеспечивает детальный контроль над типом предоставленного доступа, включая интервал действия, предоставленные разрешения и допустимый диапазон IP-адресов. После создания маркера SAS его нельзя отменить или удалить, и он разрешает доступ до истечения срока действия.

  1. Маркер SAS можно получить несколькими способами:

  2. Предоставьте разрешения на чтение и список через SAS для доступа к внешним данным. В настоящее время виртуализация данных с базой данных SQL Azure доступна только для чтения.

  3. Чтобы создать учетные данные базы данных в базе данных SQL Azure, сначала необходимо создать главный ключ базы данных, если он еще не существует. Главный ключ базы данных необходим, если учетные данные требуют SECRET.

    -- Create MASTER KEY if it doesn't exist in the database:
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = '<Some Very Strong Password Here>';

  4. При создании маркера SAS он включает вопросительный знак (?) в начале маркера. Чтобы использовать маркер, необходимо удалить вопросительный знак (?) при создании учетных данных. Рассмотрим пример.

    CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=secret string here';

Доступ к общедоступному хранилищу с помощью анонимных учетных записей

Если требуемый набор данных разрешает общедоступный доступ (также известный как анонимный доступ), учетные данные не требуются, пока служба хранилища Azure правильно настроена, см. статью "Настройка анонимного доступа для чтения для контейнеров и BLOB-объектов".

Внешний источник данных

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

CREATE EXTERNAL DATA SOURCE MyExternalDataSource
WITH (
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest'
);

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

--Create external data source pointing to the file path, and referencing database-scoped credential:
CREATE EXTERNAL DATA SOURCE MyPrivateExternalDataSource
WITH (
    LOCATION = 'abs://<privatecontainer>@privatestorageaccount.blob.core.windows.net/dataset/' 
       CREDENTIAL = [MyCredential]
);

Запрос источников данных с помощью OPENROWSET

Синтаксис OPENROWSET позволяет мгновенно запрашивать нерегламентированные запросы, создавая только минимальное количество необходимых объектов базы данных.

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

Значение параметра DATA_SOURCE автоматически добавляется в начало параметра BULK для формирования полного пути к файлу.

При использовании OPENROWSET укажите формат файла (например, как в следующем примере), который запрашивает один файл:

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'bing_covid-19_data.parquet',
 DATA_SOURCE = 'MyExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

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

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

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

Сначала создайте внешний источник данных:

--Create the data source first:
CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

Теперь мы можем запросить все файлы с расширением PARQUET в папках. Например, здесь мы запросим только те файлы, которые соответствуют шаблону имен:

--Query all files with .parquet extension in folders matching name pattern:
SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

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

Вывод схемы

Автоматический вывод схемы помогает быстро создавать запросы и исследовать данные, если вы не знаете схемы файлов. Вывод схемы работает только с файлами parquet.

Хотя это удобно, выводимые типы данных могут быть больше фактических типов данных, так как в исходных файлах может быть достаточно сведений, чтобы обеспечить использование соответствующего типа данных. Иногда это приводит к снижению производительности запросов. Например, файлы Parquet не включают метаданные о максимальной длине символьного столбца, поэтому экземпляр использует тип varchar(8000).

Используйте хранимую процедуру sp_describe_first_results_set, чтобы проверить результирующие типы данных запроса, например как в следующем примере:

EXEC sp_describe_first_result_set N'
 SELECT
 vendorID, tpepPickupDateTime, passengerCount
 FROM
 OPENROWSET(
  BULK ''yellow/*/*/*.parquet'',
  DATA_SOURCE = ''NYCTaxiExternalDataSource'',
  FORMAT=''parquet''
 ) AS nyc';

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

SELECT TOP 100
 vendorID, tpepPickupDateTime, passengerCount
FROM
OPENROWSET(
 BULK 'yellow/*/*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT='PARQUET'
 )
WITH (
vendorID varchar(4), -- we're using length of 4 instead of the inferred 8000
tpepPickupDateTime datetime2,
passengerCount int
) AS nyc;

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

SELECT TOP 10 id, updated, confirmed, confirmed_change
FROM OPENROWSET(
 BULK 'bing_covid-19_data.csv',
 DATA_SOURCE = 'MyExternalDataSource',
 FORMAT = 'CSV',
 FIRSTROW = 2
)
WITH (
 id int,
 updated date,
 confirmed int,
 confirmed_change int
) AS filerows;

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

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

--Query all files and project file path and file name information for each row:
SELECT TOP 10 filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder],
filerows.filename() as [File_name], filerows.filepath() as [Full_Path], *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet') AS filerows;

При вызове без параметров функция filepath() возвращает путь к файлу, из которого была получена запись. Если DATA_SOURCE используется в OPENROWSET, возвращается путь относительно DATA_SOURCE. В противном случае возвращается полный путь.

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

Функцию filepath() также можно использовать для фильтрации и агрегирования столбцов:

SELECT
 r.filepath() AS filepath
 ,r.filepath(1) AS [year]
 ,r.filepath(2) AS [month]
 ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
DATA_SOURCE = 'NYCTaxiExternalDataSource',
FORMAT = 'parquet'
 ) AS r
WHERE
 r.filepath(1) IN ('2017')
 AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
 r.filepath()
 ,r.filepath(1)
 ,r.filepath(2)
ORDER BY
 filepath;

Создание представления поверх OPENROWSET

Вы можете создавать и использовать представления для заключения запросов OPENROWSET в оболочку, чтобы упростить повторное использование базового запроса:

CREATE VIEW TaxiRides AS
SELECT *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

Также может быть удобно добавить в представление столбцы с данными расположения файлов с помощью функции filepath() для более простой и производительной фильтрации. Представления позволяют сократить число файлов и объем данных, который необходимо считать и обработать запросу над представлением при фильтрации по какому-либо из этих столбцов:

CREATE VIEW TaxiRides AS
SELECT *
 , filerows.filepath(1) AS [year]
 , filerows.filepath(2) AS [month]
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

Представления также позволяют средствам создания отчетов и аналитики (например, Power BI) использовать результаты OPENROWSET.

Внешние таблицы

Внешние таблицы инкапсулируют доступ к файлам, благодаря чему работа с запросами практически не отличается от отправки запросов к локальным реляционным данным в пользовательских таблицах. Для создания внешней таблицы необходим внешний источник данных и внешние объекты формата файлов:

--Create external file format
CREATE EXTERNAL FILE FORMAT DemoFileFormat
WITH (
 FORMAT_TYPE=PARQUET
);

--Create external table:
CREATE EXTERNAL TABLE tbl_TaxiRides(
 vendorID VARCHAR(100) COLLATE Latin1_General_BIN2,
 tpepPickupDateTime DATETIME2,
 tpepDropoffDateTime DATETIME2,
 passengerCount INT,
 tripDistance FLOAT,
 puLocationId VARCHAR(8000),
 doLocationId VARCHAR(8000),
 startLon FLOAT,
 startLat FLOAT,
 endLon FLOAT,
 endLat FLOAT,
 rateCodeId SMALLINT,
 storeAndFwdFlag VARCHAR(8000),
 paymentType VARCHAR(8000),
 fareAmount FLOAT,
 extra FLOAT,
 mtaTax FLOAT,
 improvementSurcharge VARCHAR(8000),
 tipAmount FLOAT,
 tollsAmount FLOAT,
 totalAmount FLOAT
)
WITH (
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = NYCTaxiExternalDataSource,
 FILE_FORMAT = DemoFileFormat
);

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

SELECT TOP 10 *
FROM tbl_TaxiRides;

Как и при использовании OPENROWSET, внешние таблицы позволяют отправлять запросы к нескольким файлам и папкам с использованием подстановочных знаков. Вывод схемы не поддерживается внешними таблицами.

Вопросы, связанные с производительностью

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

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

Данные часто упорядочены в вложенных папках, которые также называются секциями. Вы можете указать запрос на чтение только определенных папок и файлов. Это сокращает количество файлов и объем данных, необходимых для чтения и обработки запроса, что приводит к повышению производительности. Этот тип оптимизации запросов называется очисткой секций или ликвидацией секций. Вы можете исключить секции из выполнения запроса с помощью функции filepath() метаданных в WHERE предложении запроса.

Следующий пример запроса считывает файлы данных NYC Yellow Taxi только за последние три месяца 2017 года:

SELECT
    r.filepath() AS filepath
    ,r.filepath(1) AS [year]
    ,r.filepath(2) AS [month]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'yellow/puYear=*/puMonth=*/*.parquet',
        DATA_SOURCE = 'NYCTaxiExternalDataSource',
        FORMAT = 'parquet'
    )
WITH (
    vendorID INT
) AS [r]
WHERE
    r.filepath(1) IN ('2017')
    AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
    r.filepath()
    ,r.filepath(1)
    ,r.filepath(2)
ORDER BY
    filepath;

Если сохраненные данные не секционированы, рассмотрите возможность секционирования для повышения производительности запросов.

Если вы используете внешние таблицы, а filepath() функции поддерживаются, filename() но не в предложенииWHERE.

Устранение неполадок

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

  • Неверный или неправильно введенный путь к расположению.
  • Срок действия ключа SAS: он может быть истек, содержащий опечатку, начиная с вопросительного знака.
  • Разрешены разрешения ключа SAS: чтение как минимум и список , если используются подстановочные знаки.
  • Заблокирован входящий трафик для учетной записи хранения. Проверьте управление правилами виртуальной сети для службы хранилища Azure.
  • Права доступа к управляемому удостоверению: убедитесь, что управляемое удостоверение базы данных SQL Azure предоставляет права доступа к учетной записи хранения.
  • Уровень совместимости базы данных должен иметь значение 130 или выше, чтобы запросы виртуализации данных работали.

Ограничения

  • В настоящее время статистика внешних таблиц не поддерживается в Базе данных SQL Azure.
  • CREATE EXTERNAL TABLE AS SELECT В настоящее время недоступно в Базе данных SQL Azure.
  • Функция безопасности на уровне строк не поддерживается во внешних таблицах.
  • Правило динамического маскирования данных не может быть определено для столбца во внешней таблице.
  • Управляемое удостоверение не поддерживает сценарии между арендаторами; если ваша учетная запись хранения Azure находится в другом арендаторе, поддерживаемым методом является подпись общего доступа.

Известные проблемы

  • Если параметризация для Always Encrypted включена в SQL Server Management Studio (SSMS), запросы виртуализации данных завершаются ошибкой с Incorrect syntax near 'PUSHDOWN' сообщением об ошибке.

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