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

Tip

Microsoft Fabric Data Warehouse是数据湖基础上的企业规模关系仓库，具有未来就绪的体系结构、内置 AI 和新功能。 如果不熟悉数据仓库，请从Fabric Data Warehouse开始。 现有的指定 SQL 池工作负荷可以升级到 Fabric，以跨数据科学、实时分析和报告访问新功能。

• 启动Fabric免费试用版。
• Fabric 数据仓库迁移助手。

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

• 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 匹配的凭据（基于存储名称和容器定义），并向 OPENROWSET 函数的调用者授予该凭据的 REFERENCES 权限。

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 匹配的服务器级 CREDENTIAL，或者 SQL 用户没有此凭据的引用权限，将返回错误。 SQL 主体无法使用某个 Microsoft Entra 标识进行模拟。

直接访问

无需进行额外设置，Microsoft Entra 用户即可使用其身份访问文件。 任何用户都可以访问允许匿名访问的Azure存储（不需要其他设置）。

注释

此版本的 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&sp=rwac&se=2017-02-01T00:55:34Z&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

具有读取表权限的用户可以使用在一组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 = AzureDataLakeStore ,
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 AzureDataLakeStore
 WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>' ,
 CREDENTIAL = cred
 ) ;

直接访问

用户可以创建不带 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 允许使用标准 SQL SELECT 语句从通过数据源引用的文件读取数据：

SELECT *
FROM dbo.DimProductsExternal

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

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

Permissions

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

Query	所需的权限
不带数据源的 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 权限

后续步骤

您已经准备好继续阅读以下操作方法文章：

• 在存储上查询数据

• 查询 CSV 文件

• 查询 Parquet 文件

• 查询 JSON 文件

• 查询文件夹和多个文件

• 使用分区和元数据函数

• 查询嵌套类型

• 创建和使用视图