Виртуализация данных с помощью Управляемый экземпляр SQL Azure

Область применения: Управляемый экземпляр SQL Azure

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

Обзор

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

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

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

Форматы файлов 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

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

Важно!

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

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

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

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

  • Parquet: 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, чтобы получить первую информацию о наборе данных:

--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 и другие предложения на основе результирующий набор первого запроса.

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

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

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

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

Управляемое удостоверение, также известное как MSI, — это функция Azure Active Directory (Azure AD), которая предоставляет экземпляры служб Azure, например Управляемый экземпляр SQL Azure, с автоматически управляемым удостоверением в Azure AD. Это удостоверение можно использовать для авторизации запросов на доступ к данным в учетных записях хранения, не являющихся общедоступными.

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

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

-- Optional: Create MASTER KEY if it doesn't exist in the database:
-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Some Very Strong Password Here>'
GO
CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'Managed Identity'

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

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

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 DemoPrivateExternalDataSource
WITH (
	LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest'
    	CREDENTIAL = [MyCredential] 
)

Обращение к источникам данных с помощью OPENROWSET

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

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

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

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

Отправка запросов к нескольким файлам и папкам

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

В следующем примере используется набор данных с записями о поездках такси Нью-Йорка:

--Query all files with .parquet extension in folders matching name pattern:
SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'taxi/year=*/month=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiDemoDataSource',--You need to create the data source first
 FORMAT = 'parquet'
) AS filerows

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

Вывод схемы

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

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

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

EXEC sp_describe_first_result_set N'
 SELECT
 vendor_id, pickup_datetime, passenger_count
 FROM 
 OPENROWSET(
  BULK ''taxi/*/*/*'',
  DATA_SOURCE = ''NYCTaxiDemoDataSource'',
  FORMAT=''parquet''
 ) AS nyc';

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

SELECT TOP 100
 vendor_id, pickup_datetime, passenger_count
FROM
OPENROWSET(
 BULK 'taxi/*/*/*',
 DATA_SOURCE = 'NYCTaxiDemoDataSource',
 FORMAT='PARQUET'
 )
WITH (
vendor_id varchar(4), -- we're using length of 4 instead of the inferred 8000
pickup_datetime datetime2,
passenger_count int
) AS nyc;

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

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'population/population.csv',
 DATA_SOURCE = 'PopulationDemoDataSourceCSV',
 FORMAT = 'CSV')
WITH (
 [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
 [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
 [year] smallint,
 [population] bigint
) 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 'taxi/year=*/month=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiDemoDataSource',
 FORMAT = 'parquet') AS filerows
--List all paths:
SELECT DISTINCT filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder]
FROM OPENROWSET(
 BULK 'taxi/year=*/month=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiDemoDataSource',
 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 'taxi/year=*/month=*/*.parquet',
DATA_SOURCE = 'NYCTaxiDemoDataSource',
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 'taxi/year=*/month=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiDemoDataSource',
 FORMAT = 'parquet'
) AS filerows

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

CREATE VIEW TaxiRides AS
SELECT *
 ,filerows.filepath(1) AS [year]
 ,filerows.filepath(2) AS [month]
FROM OPENROWSET(
 BULK 'taxi/year=*/month=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiDemoDataSource',
 FORMAT = 'parquet'
) AS filerows

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

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

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

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

--Create external table:
CREATE EXTERNAL TABLE tbl_TaxiRides(
 vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2,
 pickup_datetime DATETIME2,
 dropoff_datetime DATETIME2,
 passenger_count INT,
 trip_distance FLOAT,
 fare_amount FLOAT,
 extra FLOAT,
 mta_tax FLOAT,
 tip_amount FLOAT,
 tolls_amount FLOAT,
 improvement_surcharge FLOAT,
 total_amount FLOAT
)
WITH (
 LOCATION = 'taxi/year=*/month=*/*.parquet',
 DATA_SOURCE = DemoDataSource,
 FILE_FORMAT = DemoFileFormat
);
GO

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

SELECT TOP 10 *
FROM tbl_TaxiRides

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

Вопросы производительности

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

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

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

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

SELECT
    r.filepath() AS filepath
    ,r.filepath(1) AS [year]
    ,r.filepath(2) AS [month]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_*-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',        
        FIRSTROW = 2
    )
WITH (
    vendor_id 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;

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

Статистика

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

Автоматическое создание статистики

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

Получаемая вручную статистика OPENROWSET

Статистику по отдельному столбцу для пути OPENROWSET можно создать с помощью хранимой процедуры sp_create_openrowset_statistics, передав выбранный запрос с одним столбцом в качестве параметра:

EXEC sys.sp_create_openrowset_statistics N'
SELECT pickup_datetime
FROM OPENROWSET(
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'',
 FORMAT = ''parquet'') AS filerows
'

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

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

EXEC sys.sp_drop_openrowset_statistics N'
SELECT pickup_datetime
FROM OPENROWSET(
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'',
 FORMAT = ''parquet'') AS filerows
'

Создаваемая вручную статистика по внешней таблице

Синтаксис для создания статистики по внешним таблицам похож на синтаксис для создания статистики по отдельной пользовательской таблице. Чтобы создать статистику по столбцу, укажите имя объекта статистики и имя столбца:

CREATE STATISTICS sVendor
ON tbl_TaxiRides (vendor_id)
WITH FULLSCAN, NORECOMPUTE

Параметры WITH обязательны, а для размера выборки разрешены параметры FULLSCAN и SAMPLE n (в процентах). Чтобы создать статистику по отдельным столбцам для нескольких столбцов, выполните хранимую процедуру для каждого из столбцов. Статистика по нескольким столбцам не поддерживается.

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

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

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

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

  • Подробные сведения о параметрах синтаксиса, доступных в OPENROWSET, см. в статье OPENROWSET T-SQL.
  • Дополнительные сведения о создании внешней таблицы в Управляемом экземпляре SQL см. в статье CREATE EXTERNAL TABLE.
  • Дополнительные сведения о создании внешнего формата файла см. в статье CREATE EXTERNAL FILE FORMAT