通过

在 Azure Synapse Analytics 中使用无服务器 SQL 池访问外部存储

  • 项目

本文介绍用户如何从无服务器 SQL 池的 Azure 存储中存储的文件读取数据。 用户具有以下用于访问存储的选项：

  • OPENROWSET 函数，可对 Azure 存储中的文件进行即席查询。
  • 外部表，它是基于一组外部文件生成的预定义数据结构。

用户可以使用不同的身份验证方法，例如 Microsoft Entra 直通身份验证（Microsoft Entra 主体的默认方法）和 SAS 身份验证（SQL 主体的默认方法）。

使用 OPENROWSET 查询文件

借助 OPENROWSET，用户可以查询 Azure 存储中的外部文件，前提是他们有权访问该存储。 连接到无服务器 SQL 池的用户应使用以下查询来读取 Azure 存储中文件的内容：

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

用户可以使用以下访问规则来访问存储：

  • Microsoft Entra 用户 - OPENROWSET 将使用调用方的 Microsoft Entra 标识来访问 Azure 存储或通过匿名访问来访问存储。
  • SQL 用户 - OPENROWSET 将通过匿名访问来访问存储；或者可使用 SAS 令牌或工作区的托管标识模拟它。

SQL 主体也可以使用 OPENROWSET 直接查询受 SAS 令牌保护的文件或工作区的托管标识。 如果 SQL 用户执行此函数，则具有 ALTER ANY CREDENTIAL 权限的 Power User 必须创建服务器范围的凭据，该凭据与该函数（使用存储名称和容器）中的 URL 匹配，并向 OPENROWSET 函数的调用方授予此凭据的 REFERENCES 权限：

EXECUTE AS somepoweruser

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

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

如果没有与 URL 匹配的服务器级凭据，或 SQL 用户没有此凭据的引用权限，则会返回错误。 SQL 主体无法使用某个 Microsoft Entra 标识进行模拟。

注意

此版本的 OPENROWSET 旨在使用默认的身份验证快速轻松地浏览数据。 若要利用模拟或托管标识，请将 OPENROWSET 与下一部分中所述的 DATA_SOURCE 一起使用。

使用 OPENROWSET 查询数据源

借助 OPENROWSET，用户可以查询放置在某些外部数据源上的文件：

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

执行此查询的用户必须能够访问文件。 如果用户无法通过其 Microsoft Entra 标识或匿名访问来直接访问文件，则必须使用 SAS 令牌或工作区的托管标识来模拟用户。

DATABASE SCOPED CREDENTIAL 指定如何访问参考数据源（当前为 SAS 和托管标识）上的文件。 具有 CONTROL DATABASE 权限的 Power User 将需要创建 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.chinacloudapi.cn/<container>/<path>/' ,
 CREDENTIAL = AccessAzureInvoices) ;

调用方必须具有以下权限之一才能执行 OPENROWSET 函数：

  • 执行 OPENROWSET 所需的权限之一：
    • ADMINISTER BULK OPERATIONS，使登录名可以执行 OPENROWSET 函数。
    • ADMINISTER DATABASE BULK OPERATIONS，使数据库范围内的用户可以执行 OPENROWSET 函数。
  • EXTERNAL DATA SOURCE 中引用的凭据的 REFERENCES DATABASE SCOPED CREDENTIAL。

EXTERNAL TABLE

具有读取表权限的用户可以使用基于一组 Azure 存储文件夹和文件创建的 EXTERNAL TABLE 来访问外部文件。

具有创建外部表权限（例如 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 = AzureDataLakeStorage ,
FILE_FORMAT = TextFileFormat
) ;

从此表中读取数据的用户必须能够访问文件。 如果用户无法通过其 Microsoft Entra 标识或匿名访问来直接访问文件，则必须使用 SAS 令牌或工作区的托管标识来模拟用户。

DATABASE SCOPED CREDENTIAL 指定如何访问引用的数据源上的文件。 拥有 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 AzureDataLakeStorage
 WITH ( LOCATION = 'https://<storage_account>.dfs.core.chinacloudapi.cn/<container>/<path>' ,
 CREDENTIAL = cred
 ) ;

使用 EXTERNAL TABLE 读取外部文件

借助 EXTERNAL TABLE，你可以读取使用标准 SQL SELECT 语句通过数据源引用的文件中的数据：

SELECT *
FROM dbo.DimProductsExternal

调用方必须具有以下权限才能读取数据：

  • 对外部表的 SELECT 权限
  • REFERENCES DATABASE SCOPED CREDENTIAL 权限（如果 DATA SOURCE 具有 CREDENTIAL）

权限

下表列出了上面列出的操作所需的权限。

查询 所需的权限
OPENROWSET(BULK)，不包含数据源 ADMINISTER BULK OPERATIONS、ADMINISTER DATABASE BULK OPERATIONS 或 SQL 登录名必须对受 SAS 保护的存储具有 REFERENCES CREDENTIAL::<URL>
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 + 对存储的 R/W 权限

后续步骤

现在，你已准备好继续学习以下操作指南文章：