通过

在 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 权限的高权限用户必须创建一个与函数中 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 匹配的服务器级 CREDENTIAL，或者 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 权限的高级用户需要创建 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 令牌或工作区的托管标识来模拟用户。

数据库范围凭证指定如何访问参考数据源上的文件。 具有 CONTROL DATABASE 权限的用户需要创建数据库范围凭据，以便用于访问存储，并创建外部数据源，该外部数据源指定数据源的 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 中的一个
创建外部数据源 ALTER ANY EXTERNAL DATA SOURCE 和 REFERENCES DATABASE SCOPED CREDENTIAL
创建外部表 CREATE TABLE、ALTER ANY SCHEMA、ALTER ANY EXTERNAL FILE FORMAT和 ALTER ANY EXTERNAL DATA SOURCE
从外部表中选择 SELECT TABLE 和 REFERENCES DATABASE SCOPED CREDENTIAL
CETAS 创建表 - CREATE TABLE、 ALTER ANY SCHEMAALTER ANY DATA SOURCE和 ALTER ANY EXTERNAL FILE FORMAT。 读取数据：查询中每个表/视图/函数的 ADMINISTER BULK OPERATIONS 或 REFERENCES CREDENTIAL 或 SELECT TABLE + 对存储的 R/W 权限

后续步骤

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