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

Статья
10/18/2023

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

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

Пользователь может использовать различные методы проверки подлинности, такие как сквозная проверка подлинности Microsoft Entra (по умолчанию для субъектов Microsoft Entra) и аутентификация SAS (по умолчанию для субъектов SQL).

Запрашивание файлов с помощью OPENROWSET

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

SELECT * FROM
 OPENROWSET(BULK 'https://<storage_account>.dfs.core.windows.net/<container>/<path>/*.parquet', format= 'parquet') as rows

Пользователь может получить доступ к хранилищу на основе следующих правил доступа:

Пользователь Microsoft Entra — OPENROWSET будет использовать удостоверение Пользователя Microsoft Entra для доступа к служба хранилища Azure или хранилищу с анонимным доступом.
Пользователь SQL — OPENROWSET будет обращаться к хранилищу с помощью анонимного доступа или выполнять олицетворение с помощью маркера SAS или управляемого удостоверения рабочей области.

Олицетворение
Прямой доступ

Субъекты SQL также могут использовать OPENROWSET для непосредственного запрашивания файлов, защищенных маркерами SAS или управляемым удостоверением рабочей области. Если пользователь SQL выполняет эту функцию, опытный пользователь с разрешением ALTER ANY CREDENTIAL должен создать учетные данные уровня сервера, соответствующие URL-адресу в функции (с использованием имени хранилища и контейнера) и предоставить разрешения REFERENCES для этих учетных данных вызывающему объекту функции OPENROWSET.

EXECUTE AS somepoweruser

CREATE CREDENTIAL [https://<storage_account>.dfs.core.windows.net/<container>]
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sas token';

GRANT REFERENCES ON CREDENTIAL::[https://<storage_account>.dfs.core.windows.net/<container>] TO sqluser

Если учетные данные уровня сервера, которые соответствуют URL-адресу, отсутствуют или у пользователя SQL нет разрешения REFERENCES для этих учетных данных, будет возвращена ошибка. Субъекты SQL не могут олицетворить с помощью некоторых удостоверений Microsoft Entra.

Примечание.

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

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

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

SELECT * FROM
 OPENROWSET(BULK 'file/path/*.parquet',
 DATA_SOURCE = MyAzureInvoices,
 FORMAT= 'parquet') as rows

У пользователя, выполняющего этот запрос, должен быть доступ к файлам. Пользователи должны быть олицетворены с помощью маркера SAS или управляемого удостоверения рабочей области, если они не могут напрямую получить доступ к файлам с помощью удостоверения Microsoft Entra или анонимного доступа.

Олицетворение
Прямой доступ

DATABASE SCOPED CREDENTIAL определяют способ доступа к файлам в указанном источнике данных (сейчас это SAS и управляемое удостоверение). Опытным пользователям с разрешением CONTROL DATABASE нужно создать DATABASE SCOPED CREDENTIAL для обращения к хранилищу и EXTERNAL DATA SOURCE с определением используемых URL-адреса источника данных и учетных данных:

EXECUTE AS somepoweruser;

-- Create MASTER KEY if it doesn't exists in database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some very strong password';

CREATE DATABASE SCOPED CREDENTIAL AccessAzureInvoices
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '******srt=sco&amp;sp=rwac&amp;se=2017-02-01T00:55:34Z&amp;st=201********' ;

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
 WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>/' ,
 CREDENTIAL = AccessAzureInvoices) ;

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

Одно из разрешений для выполнения OPENROWSET:
- ADMINISTER BULK OPERATIONS позволяет выполнить вход для выполнения функции OPENROWSET.
- ADMINISTER DATABASE BULK OPERATIONS позволяет пользователю базы данных выполнить функцию OPENROWSET.
REFERENCES DATABASE SCOPED CREDENTIAL для учетных данных, на которые указывает ссылка в EXTERNAL DATA SOURCE.

Пользователь может создать ВНЕШНИЙ ИСТОЧНИК ДАННЫХ без УЧЕТНЫХ ДАННЫХ, которые будут ссылаться на хранилище общедоступного доступа или использовать сквозную проверку подлинности Microsoft Entra:

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
 WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>') ;

EXTERNAL TABLE

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

Пользователь с разрешениями на создание внешней таблицы (например, CREATE TABLE и ALTER ANY CREDENTIAL или REFERENCES DATABASE SCOPED CREDENTIAL), может использовать следующий скрипт для создания таблицы на основе источника данных службы хранилища Azure:

CREATE EXTERNAL TABLE [dbo].[DimProductexternal]
( ProductKey int, ProductLabel nvarchar, ProductName nvarchar )
WITH
(
LOCATION='/DimProduct/year=*/month=*' ,
DATA_SOURCE = AzureDataLakeStore ,
FILE_FORMAT = TextFileFormat
) ;

Пользователь, считывающий данные из этой таблицы, должен иметь доступ к файлам. Пользователи должны быть олицетворены с помощью маркера SAS или управляемого удостоверения рабочей области, если они не могут напрямую получить доступ к файлам с помощью удостоверения Microsoft Entra или анонимного доступа.

Олицетворение
Прямой доступ

Учетные данные уровня базы данных определяют способ доступа к файлам в указанном источнике данных. Пользователям с разрешением CONTROL DATABASE нужно создать учетные данные уровня базы данных (DATABASE SCOPED CREDENTIAL) для обращения к хранилищу и внешнему источнику данных (EXTERNAL DATA SOURCE) с определением используемых URL-адреса источника данных и учетных данных:

EXECUTE AS somepoweruser;

-- Create MASTER KEY if it doesn't exists in database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some very strong password';

CREATE DATABASE SCOPED CREDENTIAL cred
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=201********' ;

CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
 WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>' ,
 CREDENTIAL = cred
 ) ;

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
 WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>') ;

Считывание внешних файлов с помощью EXTERNAL TABLE

Структура EXTERNAL TABLE позволяет считывать данные из файлов, на которые указывает источник данных, с помощью стандартной инструкции SQL SELECT:

SELECT *
FROM dbo.DimProductsExternal

Для чтения данных вызывающий объект должен иметь следующие разрешения:

Разрешение SELECT ДЛЯ внешней таблицы.
Разрешение REFERENCES DATABASE SCOPED CREDENTIAL, если у DATA SOURCE есть CREDENTIAL.

Разрешения

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

Query	Необходимые разрешения
OPENROWSET(BULK) без источника данных	`ADMINISTER BULK OPERATIONS`, `ADMINISTER DATABASE BULK OPERATIONS` или имя для входа SQL должны содержать REFERENCES CREDENTIAL::<URL-адрес> для хранилища, защищенного с помощью SAS
OPENROWSET(BULK) с источником данных без учетных данных	`ADMINISTER BULK OPERATIONS` или `ADMINISTER DATABASE BULK OPERATIONS`
OPENROWSET(BULK) с источником данных с учетными данными	`REFERENCES DATABASE SCOPED CREDENTIAL` и либо `ADMINISTER BULK OPERATIONS`, либо `ADMINISTER DATABASE BULK OPERATIONS`
CREATE EXTERNAL DATA SOURCE	`ALTER ANY EXTERNAL DATA SOURCE` и `REFERENCES DATABASE SCOPED CREDENTIAL`.
CREATE EXTERNAL TABLE	`CREATE TABLE`, `ALTER ANY SCHEMA`, `ALTER ANY EXTERNAL FILE FORMAT` и `ALTER ANY EXTERNAL DATA SOURCE`
SELECT FROM EXTERNAL TABLE	`SELECT TABLE` и `REFERENCES DATABASE SCOPED CREDENTIAL`.
CETAS	Для создания таблиц: `CREATE TABLE`, `ALTER ANY SCHEMA`, `ALTER ANY DATA SOURCE` и `ALTER ANY EXTERNAL FILE FORMAT`; для чтения данных: `ADMINISTER BULK OPERATIONS`, `REFERENCES CREDENTIAL` или `SELECT TABLE` для каждых таблицы, представления или функции в запросе, а также разрешение на чтение и запись в хранилище.

Следующие шаги

Теперь вы готовы для работы со следующими руководствами:

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