CREATE EXTERNAL DATA SOURCE (Transact-SQL)
使用 SQL Server、Azure SQL 数据库、Azure SQL 托管实例、Azure Synapse Analytics、Analytics Platform System (PDW) 或 Azure SQL Edge 创建外部数据源,以用于查询。
本文提供所选任何 SQL 产品的语法、参数、注解、权限和示例。
选择一个产品
在下面的行中,选择你感兴趣的产品名称,系统将只显示该产品的信息。
* SQL Server *
概述:SQL Server 2016
适用于:SQL Server 2016 (13.x) 及更高版本
为 PolyBase 查询创建外部数据源。 外部数据源用于建立连接以及支持以下这些用例:
- 使用 PolyBase 执行数据虚拟化和数据加载
- 使用
BULK INSERT
或OPENROWSET
大容量加载操作
注意
此语法在不同版本的 SQL Server 之间有所不同。 使用版本选择器下拉列表选择适当版本。
若要查看 SQL Server 2019 (15.x) 的功能,请访问 CREATE EXTERNAL DATA SOURCE。
若要查看 SQL Server 2022 (16.x) 的功能,请访问 CREATE EXTERNAL DATA SOURCE。
SQL Server 2016 语法
有关语法约定的详细信息,请参阅 Transact-SQL 语法约定。
CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
( [ LOCATION = '<prefix>://<path>[:<port>]' ]
[ [ , ] CREDENTIAL = <credential_name> ]
[ [ , ] TYPE = { HADOOP } ]
[ [ , ] RESOURCE_MANAGER_LOCATION = '<resource_manager>[:<port>]' )
[ ; ]
参数
data_source_name
指定数据源的用户定义名称。 该名称在 SQL Server 数据库中必须唯一。
LOCATION = '<prefix>://<path[:port]>'
提供连接协议和外部数据源的路径。
外部数据源 | 连接器位置前缀 | 位置路径 | 产品/服务支持的位置 | 身份验证 |
---|---|---|---|---|
Cloudera CDH 或 Hortonworks HDP | hdfs |
<Namenode>[:port] |
SQL Server 2016 (13.x) 到 SQL Server 2019 (15.x) | 匿名或基本身份验证 |
Azure 存储帐户 (V2) | wasb[s] |
<container>@<storage_account>.blob.core.windows.net |
自 SQL Server 2016 (13.x) 起 不支持分层命名空间 |
Azure 存储帐户密钥 |
位置路径:
<Namenode>
= Hadoop 群集中Namenode
的计算机名称、名称服务 URI 或 IP 地址。 PolyBase 必须解析 Hadoop 群集使用的任何 DNS 名称。port
= 外部数据源侦听的端口。 在 Hadoop 中,可以使用fs.defaultFS
配置参数查找该端口。 默认值为 8020。<container>
= 保存数据的存储帐户的容器。 根容器是只读的,数据无法写回容器。<storage_account>
= Azure 资源的存储帐户名称。<server_name>
= 主机名。<instance_name>
= SQL Server 命名实例的名称。 如果在目标实例上运行 SQL Server Browser 服务,则使用此路径。
设置位置时的其他说明和指南:
- 创建对象时,SQL Server 数据库引擎不会验证外部数据源是否存在。 要进行验证,请使用外部数据源创建外部表。
- 查询 Hadoop 时,所有表使用相同的外部数据源,以确保查询语义一致。
- 可选择性地使用
wasbs
,但建议在 SQL Server 2016 (13.x) 中访问 Azure 存储帐户时使用,原因是将使用安全的 TLS/SSL 连接发送数据。 - 要确保在 Hadoop
Namenode
故障转移期间成功进行 PolyBase 查询,请考虑针对 Hadoop 群集的Namenode
使用虚拟 IP 地址。 如果不这样做,请执行 ALTER EXTERNAL DATA SOURCE 命令以指向新位置。
CREDENTIAL = credential_name
指定用于对外部数据源进行身份验证的数据库范围凭据。
只有在数据得到保护的情况下才需要 CREDENTIAL
。 允许匿名访问的数据集不需要 CREDENTIAL
。
要创建数据库范围凭据,请参阅 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)。
TYPE = [ HADOOP ]
指定要配置的外部数据源的类型。 在 SQL Server 2016 中,此参数始终是必需的,只能指定为 HADOOP
。 支持连接到 Cloudera CDH、Hortonworks HDP 或 Azure 存储帐户。 在更高版本的 SQL Server 中,此参数的行为有所不同。
有关使用 TYPE
= HADOOP
Azure 存储 帐户加载数据的示例,请参阅使用 wasb:// 接口创建外部数据源以访问Azure 存储中的数据
RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:port]'
仅当连接到 Cloudera CDH、Hortonworks HDP 或 Azure 存储帐户时,才配置此可选值。 有关受支持的 Hadoop 版本的完整列表,请参阅 PolyBase 连接配置 (Transact-SQL)。
定义 RESOURCE_MANAGER_LOCATION
后,查询优化器做出基于成本的决策,以提高性能。 MapReduce 作业可用于将计算下推到 Hadoop。 指定 RESOURCE_MANAGER_LOCATION
可以显著减少 Hadoop 和 SQL Server 之间传输的数据量,从而提高查询性能。
如果未指定资源管理器,则会为 PolyBase 查询禁用到 Hadoop 的计算下推。 创建外部数据源以引用启用了下推功能的 Hadoop 中提供了具体示例和详细指南。
创建外部数据源时,不会验证 RESOURCE_MANAGER_LOCATION 值。 每次尝试下推时,输入不正确的值都可能会导致查询执行失败,因为提供的值无法解析。
为了使 PolyBase 能够正常访问 Hadoop 外部数据源,以下 Hadoop 群集组件的端口必须处于打开状态:
- HDFS 端口
- Namenode
- DataNode
- 资源管理器
- 作业提交
- 作业历史记录
如果未指定端口,则使用“hadoop 连接”配置的当前设置选择默认值。
Hadoop 连接 | 默认资源管理器端口 |
---|---|
1 | 50300 |
2 | 50300 |
3 | 8021 |
4 | 8032 |
5 | 8050 |
6 | 8032 |
7 | 8050 |
8 | 8032 |
下表显示了这些组件的默认端口。 存在 Hadoop 版本依赖项以及不使用默认端口分配的自定义配置的可能性。
Hadoop 群集组件 | “默认端口” |
---|---|
NameNode | 8020 |
DataNode(数据传输、非特权 IPC 端口) | 50010 |
DataNode(数据传输、特权 IPC 端口) | 1019 |
资源管理器作业提交 (Hortonworks 1.3) | 50300 |
资源管理器作业提交 (Cloudera 4.3) | 8021 |
资源管理器作业提交(Windows 上的 Hortonworks 2.0,Linux 上的 Cloudera 5.x) | 8032 |
资源管理器作业提交(Linux 上的 Hortonworks 2.x、3.0,Windows 上的 Hortonworks 2.1-3) | 8050 |
资源管理器作业历史记录 | 10020 |
权限
需要对 SQL Server 中数据库的 CONTROL
权限。
锁定
对 EXTERNAL DATA SOURCE
对象采用共享锁。
安全性
PolyBase 支持大多数外部数据源的基于代理的身份验证。 创建数据库范围凭据以创建代理帐户。
示例
重要
有关如何安装和启用 PolyBase 的信息,请参阅在 Windows 上安装 PolyBase
A. 创建外部数据源以引用 Hadoop
若要创建外部数据源来引用 Hortonworks HDP 或 Cloudera CDH Hadoop 群集,请指定 Hadoop Namenode
的计算机名称/IP 地址和端口。
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
LOCATION = 'hdfs://10.10.10.10:8050',
TYPE = HADOOP
);
B. 创建外部数据源以引用 Hadoop 并启用下推
指定 RESOURCE_MANAGER_LOCATION
选项以便为 PolyBase 查询启用到 Hadoop 的下推计算。 启用后,PolyBase 会根据成本作出决策,以确定是否应将查询计算下推到 Hadoop。
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
LOCATION = 'hdfs://10.10.10.10:8020',
TYPE = HADOOP,
RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);
C. 创建外部数据源以引用受 Kerberos 保护的 Hadoop
若要验证 Hadoop 群集是否受 Kerberos 保护,请检查 Hadoop core-site.xml 中的 hadoop.security.authentication
属性值。 若要引用受 Kerberos 保护的 Hadoop 群集,必须指定包含 Kerberos 用户名和密码的数据库范围凭据。 数据库主密钥用于加密数据库范围凭据密钥。
-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
-- Create a database scoped credential with Kerberos user name and password.
CREATE DATABASE SCOPED CREDENTIAL HadoopUser1
WITH IDENTITY = '<hadoop_user_name>',
SECRET = '<hadoop_password>';
-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
LOCATION = 'hdfs://10.10.10.10:8050',
CREDENTIAL = HadoopUser1,
TYPE = HADOOP,
RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);
D. 创建外部数据源以使用 wasb:// 接口访问 Azure 存储中的数据
在本示例中,外部数据源是名为 logs
的 Azure V2 存储帐户。 存储容器被称为 daily
。 Azure 存储外部数据源仅用于数据传输。 它不支持谓词下推。 通过 wasb://
接口访问数据时,不支持分层命名空间。
本示例演示如何创建数据库范围凭据以用于对 Azure V2 存储帐户进行身份验证。 在数据库凭据机密中指定 Azure 存储帐户密钥。 可以在数据库范围凭据标识中指定任何字符串,因为在对 Azure 存储进行身份验证的过程中不会使用它。 请注意,通过 WASB[s] 连接器连接到 Azure 存储时,必须使用存储帐户密钥(而不是共享访问签名 (SAS))进行身份验证。
在 SQL Server 2016 (13.x) 中,即使是访问 Azure 存储时,TYPE
也应设置为 HADOOP
。
-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY = '<my_account>',
SECRET = '<azure_storage_account_key>';
-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
CREDENTIAL = AzureStorageCredential,
TYPE = HADOOP
);
后续步骤
概述:SQL Server 2017
适用于:仅限 SQL Server 2017 (14.x)
为 PolyBase 查询创建外部数据源。 外部数据源用于建立连接以及支持以下这些用例:
- 使用 PolyBase 执行数据虚拟化和数据加载
- 使用
BULK INSERT
或OPENROWSET
大容量加载操作
注意
此语法在不同版本的 Linux 上的 SQL Server 之间有所不同。 使用版本选择器下拉列表选择适当版本。
若要查看 SQL Server 2019 (15.x) 的功能,请访问 CREATE EXTERNAL DATA SOURCE。
若要查看 SQL Server 2022 (16.x) 的功能,请访问 CREATE EXTERNAL DATA SOURCE。
注意
此语法在不同版本的 SQL Server 之间有所不同。 使用版本选择器下拉列表选择适当版本。
若要查看 SQL Server 2019 (15.x) 的功能,请访问 CREATE EXTERNAL DATA SOURCE。
若要查看 SQL Server 2022 (16.x) 的功能,请访问 CREATE EXTERNAL DATA SOURCE。
SQL Server 2017 语法
有关语法约定的详细信息,请参阅 Transact-SQL 语法约定。
CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
( [ LOCATION = '<prefix>://<path>[:<port>]' ]
[ [ , ] CREDENTIAL = <credential_name> ]
[ [ , ] TYPE = { HADOOP | BLOB_STORAGE } ]
[ [ , ] RESOURCE_MANAGER_LOCATION = '<resource_manager>[:<port>]' )
[ ; ]
参数
data_source_name
指定数据源的用户定义名称。 该名称在 SQL Server 数据库中必须唯一。
LOCATION = '<prefix>://<path[:port]>'
提供连接协议和外部数据源的路径。
外部数据源 | 连接器位置前缀 | 位置路径 | 产品/服务支持的位置 | 身份验证 |
---|---|---|---|---|
Cloudera CDH 或 Hortonworks HDP | hdfs |
<Namenode>[:port] |
仅限 SQL Server 2016 (13.x) 到 SQL Server 2019 (15.x) | 匿名或基本身份验证 |
Azure 存储帐户 (V2) | wasb[s] |
<container>@<storage_account>.blob.core.windows.net |
自 SQL Server 2016 (13.x) 起 不支持分层命名空间 |
Azure 存储帐户密钥 |
批量操作 | https |
<storage_account>.blob.core.windows.net/<container> |
自 SQL Server 2017 (14.x) 起 | 共享访问签名 (SAS) |
位置路径:
<
Namenode>
= Hadoop 群集中Namenode
的计算机名称、名称服务 URI 或 IP 地址。 PolyBase 必须解析 Hadoop 群集使用的任何 DNS 名称。port
= 外部数据源侦听的端口。 在 Hadoop 中,可以使用fs.defaultFS
配置参数查找该端口。 默认值为 8020。<container>
= 保存数据的存储帐户的容器。 根容器是只读的,数据无法写回容器。<storage_account>
= Azure 资源的存储帐户名称。<server_name>
= 主机名。<instance_name>
= SQL Server 命名实例的名称。 如果在目标实例上运行 SQL Server Browser 服务,则使用此路径。
设置位置时的其他说明和指南:
- 创建对象时,SQL Server 数据库引擎不会验证外部数据源是否存在。 要进行验证,请使用外部数据源创建外部表。
- 查询 Hadoop 时,所有表使用相同的外部数据源,以确保查询语义一致。
- 通过
ODBC
连接时,请指定Driver={<Name of Driver>}
。 - 可选择性地使用
wasbs
,但建议在 SQL Server 2017 (14.x) 中访问 Azure 存储帐户时使用,原因是将使用安全的 TLS/SSL 连接发送数据。 - 要确保在 Hadoop
Namenode
故障转移期间成功进行 PolyBase 查询,请考虑针对 Hadoop 群集的Namenode
使用虚拟 IP 地址。 如果不这样做,请执行 ALTER EXTERNAL DATA SOURCE 命令以指向新位置。
CREDENTIAL = credential_name
指定用于对外部数据源进行身份验证的数据库范围凭据。
创建凭证时的其他说明和指导:
- 只有在数据得到保护的情况下才需要
CREDENTIAL
。 允许匿名访问的数据集不需要CREDENTIAL
。 - 当
TYPE
=BLOB_STORAGE
时,必须使用SHARED ACCESS SIGNATURE
作为标识创建凭据。 TYPE
=BLOB_STORAGE
仅允许用于批量操作;不能使用TYPE
=BLOB_STORAGE
为外部数据源创建外部表。- 请注意,通过 WASB[s] 连接器连接到 Azure 存储时,必须使用存储帐户密钥(而不是共享访问签名 (SAS))进行身份验证。
- 当
TYPE
=HADOOP
时,必须使用存储帐户密钥作为SECRET
创建凭据。
创建共享访问签名的方式有很多种:
可以导航到“Azure 门户”->“<Your_Storage_Account>”->“共享访问签名”->“配置权限”->“生成 SAS 和连接字符串”来创建 SAS 令牌。 有关详细信息,请参阅生成共享访问签名。
可以通过 PowerShell、Azure CLI、.NET 和 REST API 以编程方式创建 SAS。 有关详细信息,请参阅使用共享访问签名 (SAS) 授予对 Azure 存储资源的有限访问权限。
应按如下所示配置 SAS 令牌:
- 生成某个 SAS 令牌时,其开头会包含问号(“?”)。 配置为密码时排除前导
?
。 - 使用有效的有效期(所有日期均采用 UTC 时间)。
- 至少授予对应加载的文件的读取权限(例如
srt=o&sp=r
)。 可以为不同的用例创建多个共享访问签名。 应按如下所示授予权限:
操作 权限 从文件中读取数据 读取 从多个文件和子文件夹读取数据 读取和列出 - 生成某个 SAS 令牌时,其开头会包含问号(“?”)。 配置为密码时排除前导
有关使用具有 SHARED ACCESS SIGNATURE
且 TYPE
= BLOB_STORAGE
的 CREDENTIAL
的示例,请参阅创建外部数据源以执行批量操作并将数据从 Azure 存储检索到 SQL 数据库
要创建数据库范围凭据,请参阅 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)。
TYPE = [HADOOP | BLOB_STORAGE ]
指定要配置的外部数据源的类型。 此参数并非总是必需的,应仅在连接到 Cloudera CDH、Hortonworks HDP、Azure 存储帐户或 Azure Data Lake Storage Gen2 时才指定它。
- 当外部数据源是 Cloudera CDH、Hortonworks HDP、Azure 存储帐户或 Azure Data Lake Storage Gen2 时,使用
HADOOP
。 - 当使用 BULK INSERT 或 OPENROWSET 从 Azure 存储帐户执行批量操作时,请使用
BLOB_STORAGE
。 在 SQL Server 2017 (14.x) 中引入。 当打算针对 Azure 存储创建外部表时使用HADOOP
。
注意
即使在访问 Azure 存储时,TYPE
也应设置为 HADOOP
。
有关使用 TYPE
= HADOOP
Azure 存储 帐户加载数据的示例,请参阅使用 wasb:// 接口创建外部数据源以访问Azure 存储中的数据
RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:port]'
仅当连接到 Cloudera CDH、Hortonworks HDP 或 Azure 存储帐户时,才配置此可选值。 有关受支持的 Hadoop 版本的完整列表,请参阅 PolyBase 连接配置 (Transact-SQL)。
定义 RESOURCE_MANAGER_LOCATION
后,查询优化器将根据成本做出决策以提高性能。 MapReduce 作业可用于将计算下推到 Hadoop。 指定 RESOURCE_MANAGER_LOCATION
可以显著减少 Hadoop 和 SQL Server 之间传输的数据量,从而提高查询性能。
如果未指定资源管理器,则会为 PolyBase 查询禁用到 Hadoop 的计算下推。 创建外部数据源以引用启用了下推功能的 Hadoop 中提供了具体示例和详细指南。
创建外部数据源时,不会验证 RESOURCE_MANAGER_LOCATION 值。 每次尝试下推时,输入不正确的值都可能会导致查询执行失败,因为提供的值无法解析。
为了使 PolyBase 能够正常访问 Hadoop 外部数据源,以下 Hadoop 群集组件的端口必须处于打开状态:
- HDFS 端口
- Namenode
- DataNode
- 资源管理器
- 作业提交
- 作业历史记录
如果未指定端口,则使用“hadoop 连接”配置的当前设置选择默认值。
Hadoop 连接 | 默认资源管理器端口 |
---|---|
1 | 50300 |
2 | 50300 |
3 | 8021 |
4 | 8032 |
5 | 8050 |
6 | 8032 |
7 | 8050 |
8 | 8032 |
下表显示了这些组件的默认端口。 请注意,存在 Hadoop 版本依赖项以及不使用默认端口分配的自定义配置的可能性。
Hadoop 群集组件 | “默认端口” |
---|---|
NameNode | 8020 |
DataNode(数据传输、非特权 IPC 端口) | 50010 |
DataNode(数据传输、特权 IPC 端口) | 1019 |
资源管理器作业提交 (Hortonworks 1.3) | 50300 |
资源管理器作业提交 (Cloudera 4.3) | 8021 |
资源管理器作业提交(Windows 上的 Hortonworks 2.0,Linux 上的 Cloudera 5.x) | 8032 |
资源管理器作业提交(Linux 上的 Hortonworks 2.x、3.0,Windows 上的 Hortonworks 2.1-3) | 8050 |
资源管理器作业历史记录 | 10020 |
权限
需要对 SQL Server 中数据库的 CONTROL
权限。
锁定
对 EXTERNAL DATA SOURCE
对象采用共享锁。
安全性
PolyBase 支持大多数外部数据源的基于代理的身份验证。 创建数据库范围凭据以创建代理帐户。
不支持类型为 HADOOP
的 SAS 令牌。 仅在使用存储帐户访问密钥时,才支持类型为 BLOB_STORAGE
的 SAS 令牌。 尝试创建类型为 HADOOP
的外部数据源和使用 SAS 凭据失败,并显示以下错误:
Msg 105019, Level 16, State 1 - EXTERNAL TABLE access failed due to internal error: 'Java exception raised on call to HdfsBridge_Connect. Java exception message: Parameters provided to connect to the Azure storage account are not valid.: Error [Parameters provided to connect to the Azure storage account are not valid.] occurred while accessing external file.'
示例
重要
有关如何安装和启用 PolyBase 的信息,请参阅在 Windows 上安装 PolyBase
A. 创建外部数据源以引用 Hadoop
若要创建外部数据源来引用 Hortonworks HDP 或 Cloudera CDH Hadoop 群集,请指定 Hadoop Namenode
的计算机名称/IP 地址和端口。
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
LOCATION = 'hdfs://10.10.10.10:8050',
TYPE = HADOOP
);
B. 创建外部数据源以引用 Hadoop 并启用下推
指定 RESOURCE_MANAGER_LOCATION
选项以便为 PolyBase 查询启用到 Hadoop 的下推计算。 启用后,PolyBase 会根据成本作出决策,以确定是否应将查询计算下推到 Hadoop。
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
LOCATION = 'hdfs://10.10.10.10:8020',
TYPE = HADOOP,
RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);
C. 创建外部数据源以引用受 Kerberos 保护的 Hadoop
若要验证 Hadoop 群集是否受 Kerberos 保护,请检查 Hadoop core-site.xml 中的 hadoop.security.authentication
属性值。 若要引用受 Kerberos 保护的 Hadoop 群集,必须指定包含 Kerberos 用户名和密码的数据库范围凭据。 数据库主密钥用于加密数据库范围凭据密钥。
-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
-- Create a database scoped credential with Kerberos user name and password.
CREATE DATABASE SCOPED CREDENTIAL HadoopUser1
WITH IDENTITY = '<hadoop_user_name>',
SECRET = '<hadoop_password>';
-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
LOCATION = 'hdfs://10.10.10.10:8050',
CREDENTIAL = HadoopUser1,
TYPE = HADOOP,
RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);
D. 创建外部数据源以使用 wasb:// 接口访问 Azure 存储中的数据
在本示例中,外部数据源是名为 logs
的 Azure V2 存储帐户。 存储容器被称为 daily
。 Azure 存储外部数据源仅用于数据传输。 它不支持谓词下推。 通过 wasb://
接口访问数据时,不支持分层命名空间。 请注意,通过 WASB[s] 连接器连接到 Azure 存储时,必须使用存储帐户密钥(而不是共享访问签名 (SAS))进行身份验证。
本示例演示如何创建数据库范围凭据以用于对 Azure V2 存储帐户进行身份验证。 在数据库凭据机密中指定 Azure 存储帐户密钥。 可以在数据库范围凭据标识中指定任何字符串,因为在对 Azure 存储进行身份验证的过程中不会使用它。
-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY = '<my_account>',
SECRET = '<azure_storage_account_key>';
-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
CREDENTIAL = AzureStorageCredential,
TYPE = HADOOP
);
示例:批量操作
重要
为批量操作配置外部数据源时,请勿在 LOCATION
URL 的末尾放置尾随 /、文件名或共享访问签名参数。
E. 创建外部数据源以用于从 Azure 存储检索数据的批量操作
适用于:SQL Server 2017 (14.x) 及更高版本。
对使用 BULK INSERT 或 OPENROWSET 的批量操作使用以下数据源。 凭据必须设置 SHARED ACCESS SIGNATURE
作为标识、不应在 SAS 令牌中具有前导 ?
、必须对应加载的文件(例如 srt=o&sp=r
)至少具有读取权限,并且有效期应有效(所有日期均采用 UTC 时间)。 有关共享访问签名的详细信息,请参阅使用共享访问签名 (SAS)。
CREATE DATABASE SCOPED CREDENTIAL AccessAzureInvoices
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
-- Remove ? from the beginning of the SAS token
SECRET = '<azure_storage_account_key>';
CREATE EXTERNAL DATA SOURCE MyAzureInvoices
WITH (
LOCATION = 'https://newinvoices.blob.core.windows.net/week3',
CREDENTIAL = AccessAzureInvoices,
TYPE = BLOB_STORAGE
);
若要查看正在使用的示例,请参阅 BULK INSERT。
后续步骤
概述:SQL Server 2019
适用于: SQL Server 2019 (15.x) 及更高版本
为 PolyBase 查询创建外部数据源。 外部数据源用于建立连接以及支持以下这些用例:
- 使用 PolyBase 执行数据虚拟化和数据加载
- 使用
BULK INSERT
或OPENROWSET
大容量加载操作
注意
此语法在不同版本的 SQL Server 之间有所不同。 使用版本选择器下拉列表选择适当版本。
若要查看 SQL Server 2022 (16.x) 的功能,请访问 CREATE EXTERNAL DATA SOURCE。
注意
此语法在不同版本的 SQL Server 之间有所不同。 使用版本选择器下拉列表选择适当版本。
若要查看 SQL Server 2022 (16.x) 的功能,请访问 CREATE EXTERNAL DATA SOURCE。
SQL Server 2019 语法
有关语法约定的详细信息,请参阅 Transact-SQL 语法约定。
CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
( [ LOCATION = '<prefix>://<path>[:<port>]' ]
[ [ , ] CONNECTION_OPTIONS = '<key_value_pairs>'[,...]]
[ [ , ] CREDENTIAL = <credential_name> ]
[ [ , ] PUSHDOWN = { ON | OFF } ]
[ [ , ] TYPE = { HADOOP | BLOB_STORAGE } ]
[ [ , ] RESOURCE_MANAGER_LOCATION = '<resource_manager>[:<port>]' )
[ ; ]
参数
data_source_name
指定数据源的用户定义名称。 该名称在 SQL Server 数据库中必须唯一。
LOCATION = '<prefix>://<path[:port]>'
提供连接协议和外部数据源的路径。
外部数据源 | 连接器位置前缀 | 位置路径 | 产品/服务支持的位置 | 身份验证 |
---|---|---|---|---|
Cloudera CDH 或 Hortonworks HDP | hdfs |
<Namenode>[:port] |
SQL Server 2016 (13.x) 到 SQL Server 2019 (15.x) | 匿名或基本身份验证 |
Azure 存储帐户 (V2) | wasb[s] |
<container>@<storage_account>.blob.core.windows.net |
自 SQL Server 2016 (13.x) 起 不支持分层命名空间 |
Azure 存储帐户密钥 |
SQL Server | sqlserver |
<server_name>[\<instance_name>][:port] |
自 SQL Server 2019 (15.x) 起 | 仅 SQL 身份验证 |
Oracle | oracle |
<server_name>[:port] |
自 SQL Server 2019 (15.x) 起 | 仅基本身份验证 |
Teradata | teradata |
<server_name>[:port] |
自 SQL Server 2019 (15.x) 起 | 仅基本身份验证 |
MongoDB 或 Cosmos DB API for MongoDB | mongodb |
<server_name>[:port] |
自 SQL Server 2019 (15.x) 起 | 仅基本身份验证 |
泛型 ODBC | odbc |
<server_name>[:port] |
自 SQL Server 2019 (15.x) 起 - 仅限 Windows | 仅基本身份验证 |
批量操作 | https |
<storage_account>.blob.core.windows.net/<container> |
自 SQL Server 2017 (14.x) 起 | 共享访问签名 (SAS) |
Azure Data Lake Storage Gen2 | abfs[s] |
abfss://<container>@<storage _account>.dfs.core.windows.net |
自 SQL Server 2019 (15.x) CU11+ 起。 | 存储访问密钥 |
SQL Server 大数据群集数据池 | sqldatapool |
sqldatapool://controller-svc/default |
仅在 SQL Server 2019 大数据群集中受支持 | 仅基本身份验证 |
SQL Server 大数据群集存储池 | sqlhdfs |
sqlhdfs://controller-svc/default |
仅在 SQL Server 2019 大数据群集中受支持 | 仅基本身份验证 |
位置路径:
<Namenode>
= Hadoop 群集中Namenode
的计算机名称、名称服务 URI 或 IP 地址。 PolyBase 必须解析 Hadoop 群集使用的任何 DNS 名称。port
= 外部数据源侦听的端口。 在 Hadoop 中,可以使用fs.defaultFS
配置参数查找该端口。 默认值为 8020。<container>
= 保存数据的存储帐户的容器。 根容器是只读的,数据无法写回容器。<storage_account>
= Azure 资源的存储帐户名称。<server_name>
= 主机名。<instance_name>
= SQL Server 命名实例的名称。 如果在目标实例上运行 SQL Server Browser 服务,则使用此路径。
设置位置时的其他说明和指南:
- 创建对象时,SQL Server 数据库引擎不会验证外部数据源是否存在。 要进行验证,请使用外部数据源创建外部表。
- 查询 Hadoop 时,所有表使用相同的外部数据源,以确保查询语义一致。
- 可使用
sqlserver
连接器将 SQL Server 2019 (15.x) 连接到另一个 SQL Server 或 Azure SQL 数据库。 - 通过
ODBC
连接时,请指定Driver={<Name of Driver>}
。 - 可选择性地使用
wasbs
或abfss
,但建议在 SQL Server 2019 (15.x) 中访问 Azure 存储帐户时使用,原因是将使用安全的 TLS/SSL 连接发送数据。 - 从 SQL Server 2019 (15.x) CU11 开始,访问 Azure 存储帐户时,支持使用
abfs
或abfss
API。 有关详细信息,请参阅 Azure Blob 文件系统驱动程序 (ABFS)。 - 从 SQL Server 2019 (15.x) CU11+ 开始,支持在 Azure Data Lake Storage Gen2 中通过
abfs[s]
对 Azure 存储帐户 (V2) 使用分层命名空间选项。 如果不满足上述条件,则不支持分层命名空间选项,且此选项应保持禁用。 - 要确保在 Hadoop
Namenode
故障转移期间成功进行 PolyBase 查询,请考虑针对 Hadoop 群集的Namenode
使用虚拟 IP 地址。 如果不这样做,请执行 ALTER EXTERNAL DATA SOURCE 命令以指向新位置。 - 支持在大数据群集的主实例和存储池之间连接
sqlhdfs
和sqldatapool
类型。 对于 Cloudera CDH 或 Hortonworks HDP,请使用hdfs
。 有关使用sqlhdfs
查询 SQL Server 大数据集群存储池的详细信息,请参阅在 SQL Server 2019 大数据集群中查询 HDFS。 - SQL Server 对 HDFS Cloudera (CDP) 和 Hortonworks (HDP) 外部数据源的支持将停用,不会包含在 SQL Server 2022 (16.x) 中。 有关详细信息,请参阅 Microsoft SQL Server 平台上的大数据选项。
CONNECTION_OPTIONS = key_value_pair
针对 SQL Server 2019 (15.x) 及更高版本指定。 通过 ODBC
连接到外部数据源时指定其他选项。 若要使用多个连接选项,请用分号分隔它们。
适用于通用 ODBC
连接,还适用于 SQL Server、Oracle、Teradata、MongoDB 和 Azure Cosmos DB API for MongoDB 的内置 ODBC
连接器。
key_value_pair
是特定连接选项的关键字和值。 哪些关键字和值可用由外部数据源类型决定。 驱动程序的名称是必需的(最基本的要求),但设置其他选项(例如 APP='<your_application_name>'
或 ApplicationIntent= ReadOnly|ReadWrite
)也很有用,可以帮助进行故障排除。
可能的键值对特定于外部数据源供应商的提供程序。 有关每个提供程序的详细信息,请参阅 CREATE EXTERNAL DATA SOURCE (Transact-SQL) CONNECTION_OPTIONS。
从 SQL Server 2019 (15.x) 累积更新 19 开始,引入了其他关键字来支持 Oracle TNS 文件:
- 关键字
TNSNamesFile
指定位于 Oracle 服务器上的tnsnames.ora
文件的文件路径。 - 关键字
ServerName
指定tnsnames.ora
内使用的别名,该别名将用于替换主机名和端口。
Pushdown = 打开 | 关闭
仅为 SQL Server 2019 (15.x) 指定。 说明是否可以将计算下推到外部数据源。 它默认处于打开状态。
在外部数据源级别连接到 SQL Server、Oracle、Teradata、MongoDB、Azure Cosmos DB API for MongoDB 或 ODBC 时,支持使用 PUSHDOWN
。
通过提示实现在查询级别启用或禁用下推。
CREDENTIAL = credential_name
指定用于对外部数据源进行身份验证的数据库范围凭据。
创建凭证时的其他说明和指导:
- 只有在数据得到保护的情况下才需要
CREDENTIAL
。 允许匿名访问的数据集不需要CREDENTIAL
。 - 当
TYPE
=BLOB_STORAGE
时,必须使用SHARED ACCESS SIGNATURE
作为标识创建凭据。TYPE
=BLOB_STORAGE
仅允许用于批量操作;不能使用TYPE
=BLOB_STORAGE
为外部数据源创建外部表。
创建共享访问签名的方式有很多种:
可以导航到“Azure 门户”->“<Your_Storage_Account>”->“共享访问签名”->“配置权限”->“生成 SAS 和连接字符串”来创建 SAS 令牌。 有关详细信息,请参阅生成共享访问签名。
可以通过 PowerShell、Azure CLI、.NET 和 REST API 以编程方式创建 SAS。 有关详细信息,请参阅使用共享访问签名 (SAS) 授予对 Azure 存储资源的有限访问权限。
应按如下所示配置 SAS 令牌:
- 生成某个 SAS 令牌时,其开头会包含问号(“?”)。 配置为密码时排除前导
?
。 - 使用有效的有效期(所有日期均采用 UTC 时间)。
- 至少授予对应加载的文件的读取权限(例如
srt=o&sp=r
)。 可以为不同的用例创建多个共享访问签名。 应按如下所示授予权限:
操作 权限 从文件中读取数据 读取 从多个文件和子文件夹读取数据 读取和列出 - 生成某个 SAS 令牌时,其开头会包含问号(“?”)。 配置为密码时排除前导
有关使用具有 SHARED ACCESS SIGNATURE
且 TYPE
= BLOB_STORAGE
的 CREDENTIAL
的示例,请参阅创建外部数据源以执行批量操作并将数据从 Azure 存储检索到 SQL 数据库
要创建数据库范围凭据,请参阅 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)。
TYPE = [HADOOP | BLOB_STORAGE ]
指定要配置的外部数据源的类型。 此参数并非总是必需的,应仅在连接到 Cloudera CDH、Hortonworks HDP、Azure 存储帐户或 Azure Data Lake Storage Gen2 时才指定它。
- 在 SQL Server 2019 (15.x) 中,请勿指定 TYPE,除非连接到 Cloudera CDH、Hortonworks HDP 或 Azure 存储帐户。
- 当外部数据源是 Cloudera CDH、Hortonworks HDP、Azure 存储帐户或 Azure Data Lake Storage Gen2 时,使用
HADOOP
。 - 当使用 BULK INSERT 或 OPENROWSET 从 Azure 存储帐户对 SQL Server 2017 (14.x) 执行批量操作时,使用
BLOB_STORAGE
。 当打算针对 Azure 存储创建外部表时使用HADOOP
。 - SQL Server 对 HDFS Cloudera (CDP) 和 Hortonworks (HDP) 外部数据源的支持将停用,不会包含在 SQL Server 2022 (16.x) 中。 有关详细信息,请参阅 Microsoft SQL Server 平台上的大数据选项。
有关使用 TYPE
= HADOOP
Azure 存储 帐户加载数据的示例,请参阅使用 wasb:// 接口创建外部数据源以访问Azure 存储中的数据
RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:port]'
在 SQL Server 2019 (15.x) 中,请勿指定 RESOURCE_MANAGER_LOCATION,除非连接到 Cloudera CDH、Hortonworks HDP 或 Azure 存储帐户。
仅当连接到 Cloudera CDH、Hortonworks HDP 或 Azure 存储帐户时,才配置此可选值。 有关受支持的 Hadoop 版本的完整列表,请参阅 PolyBase 连接配置 (Transact-SQL)。
定义 RESOURCE_MANAGER_LOCATION
后,查询优化器做出基于成本的决策,以提高性能。 MapReduce 作业可用于将计算下推到 Hadoop。 指定 RESOURCE_MANAGER_LOCATION
可以显著减少 Hadoop 和 SQL Server 之间传输的数据量,从而提高查询性能。
如果未指定资源管理器,则会为 PolyBase 查询禁用到 Hadoop 的计算下推。 创建外部数据源以引用启用了下推功能的 Hadoop 中提供了具体示例和详细指南。
创建外部数据源时,不会验证 RESOURCE_MANAGER_LOCATION 值。 每次尝试下推时,输入不正确的值都可能会导致查询执行失败,因为提供的值无法解析。
为了使 PolyBase 能够正常访问 Hadoop 外部数据源,以下 Hadoop 群集组件的端口必须处于打开状态:
- HDFS 端口
- Namenode
- DataNode
- 资源管理器
- 作业提交
- 作业历史记录
如果未指定端口,则使用“hadoop 连接”配置的当前设置选择默认值。
Hadoop 连接 | 默认资源管理器端口 |
---|---|
1 | 50300 |
2 | 50300 |
3 | 8021 |
4 | 8032 |
5 | 8050 |
6 | 8032 |
7 | 8050 |
8 | 8032 |
下表显示了这些组件的默认端口。 请注意,存在 Hadoop 版本依赖项以及不使用默认端口分配的自定义配置的可能性。
Hadoop 群集组件 | “默认端口” |
---|---|
NameNode | 8020 |
DataNode(数据传输、非特权 IPC 端口) | 50010 |
DataNode(数据传输、特权 IPC 端口) | 1019 |
资源管理器作业提交 (Hortonworks 1.3) | 50300 |
资源管理器作业提交 (Cloudera 4.3) | 8021 |
资源管理器作业提交(Windows 上的 Hortonworks 2.0,Linux 上的 Cloudera 5.x) | 8032 |
资源管理器作业提交(Linux 上的 Hortonworks 2.x、3.0,Windows 上的 Hortonworks 2.1-3) | 8050 |
资源管理器作业历史记录 | 10020 |
权限
需要对 SQL Server 中数据库的 CONTROL
权限。
锁定
对 EXTERNAL DATA SOURCE
对象采用共享锁。
安全性
PolyBase 支持大多数外部数据源的基于代理的身份验证。 创建数据库范围凭据以创建代理帐户。
连接到 SQL Server 2019 大数据群集中的存储或数据池时,会将用户的凭据传递到后端系统。 在数据池本身中创建登录名以启用直通身份验证。
不支持类型为 HADOOP
的 SAS 令牌。 仅在使用存储帐户访问密钥时,才支持类型为 BLOB_STORAGE
的 SAS 令牌。 尝试创建类型为 HADOOP
的外部数据源和使用 SAS 凭据失败,并显示以下错误:
Msg 105019, Level 16, State 1 - EXTERNAL TABLE access failed due to internal error: 'Java exception raised on call to HdfsBridge_Connect. Java exception message: Parameters provided to connect to the Azure storage account are not valid.: Error [Parameters provided to connect to the Azure storage account are not valid.] occurred while accessing external file.'
示例
重要
有关如何安装和启用 PolyBase 的信息,请参阅在 Windows 上安装 PolyBase
A. 在 SQL Server 2019 中创建外部数据源以引用 Oracle
要创建引用 Oracle 的外部数据源,请确保具有数据库范围凭据。 也可以选择针对此数据源启用或禁用计算下推。
-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL OracleProxyAccount
WITH IDENTITY = 'oracle_username',
SECRET = 'oracle_password';
CREATE EXTERNAL DATA SOURCE MyOracleServer
WITH (
LOCATION = 'oracle://145.145.145.145:1521',
CREDENTIAL = OracleProxyAccount,
PUSHDOWN = ON
);
(可选)Oracle 的外部数据源可以使用代理身份验证提供精细的访问控制。 可以将代理用户配置为,与被模拟的用户相比,具有有限的访问权限。
CREATE DATABASE SCOPED CREDENTIAL [OracleProxyCredential]
WITH IDENTITY = 'oracle_username',
SECRET = 'oracle_password';
CREATE EXTERNAL DATA SOURCE [OracleSalesSrvr]
WITH (
LOCATION = 'oracle://145.145.145.145:1521',
CONNECTION_OPTIONS = 'ImpersonateUser=%CURRENT_USER',
CREDENTIAL = [OracleProxyCredential]
);
或者,可以使用 TNS 身份验证。
从 SQL Server 2019 (15.x) 累积更新 19 开始,CREATE EXTERNAL DATA SOURCE
现在支持在连接到 Oracle 时使用 TNS 文件。
CONNECTION_OPTIONS
参数已扩展,现在使用 TNSNamesFile
和 ServerName
作为变量来浏览 tnsnames.ora
文件并与服务器建立连接。
在下面的示例中,在运行时,SQL Server 将搜索由 TNSNamesFile
指定的 tnsnames.ora
文件位置,并搜索由 ServerName
指定的主机和网络端口。
CREATE EXTERNAL DATA SOURCE [external_data_source_name]
WITH (
LOCATION = N'oracle://XE',
CREDENTIAL = [OracleCredentialTest],
CONNECTION_OPTIONS = N'TNSNamesFile=C:\Temp\tnsnames.ora;ServerName=XE'
);
有关其他数据源(如 MongoDB)的更多示例,请参阅配置 PolyBase 以访问 MongoDB 中的外部数据。
B. 创建外部数据源以引用 Hadoop
若要创建外部数据源来引用 Hortonworks HDP 或 Cloudera CDH Hadoop 群集,请指定 Hadoop Namenode
的计算机名称/IP 地址和端口。
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
LOCATION = 'hdfs://10.10.10.10:8050',
TYPE = HADOOP
);
C. 创建外部数据源以引用 Hadoop 并启用下推
指定 RESOURCE_MANAGER_LOCATION
选项以便为 PolyBase 查询启用到 Hadoop 的下推计算。 启用后,PolyBase 会根据成本作出决策,以确定是否应将查询计算下推到 Hadoop。
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
LOCATION = 'hdfs://10.10.10.10:8020',
TYPE = HADOOP,
RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);
D. 创建外部数据源以引用受 Kerberos 保护的 Hadoop
若要验证 Hadoop 群集是否受 Kerberos 保护,请检查 Hadoop core-site.xml 中的 hadoop.security.authentication
属性值。 若要引用受 Kerberos 保护的 Hadoop 群集,必须指定包含 Kerberos 用户名和密码的数据库范围凭据。 数据库主密钥用于加密数据库范围凭据密钥。
-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
-- Create a database scoped credential with Kerberos user name and password.
CREATE DATABASE SCOPED CREDENTIAL HadoopUser1
WITH IDENTITY = '<hadoop_user_name>',
SECRET = '<hadoop_password>';
-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
LOCATION = 'hdfs://10.10.10.10:8050',
CREDENTIAL = HadoopUser1,
TYPE = HADOOP,
RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);
E. 创建外部数据源以使用 wasb:// 接口访问 Azure 存储中的数据
在本示例中,外部数据源是名为 logs
的 Azure V2 存储帐户。 存储容器被称为 daily
。 Azure 存储外部数据源仅用于数据传输。 它不支持谓词下推。 通过 wasb://
接口访问数据时,不支持分层命名空间。 请注意,通过 WASB[s] 连接器连接到 Azure 存储时,必须使用存储帐户密钥(而不是共享访问签名 (SAS))进行身份验证。
本示例演示如何创建数据库范围凭据以用于对 Azure V2 存储帐户进行身份验证。 在数据库凭据机密中指定 Azure 存储帐户密钥。 可以在数据库范围凭据标识中指定任何字符串,因为在对 Azure 存储进行身份验证的过程中不会使用它。
-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY = '<my_account>',
SECRET = '<azure_storage_account_key>';
-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
CREDENTIAL = AzureStorageCredential,
TYPE = HADOOP
);
F. 创建外部数据源以通过 PolyBase 连接引用 SQL Server 命名实例
适用于:SQL Server 2019 (15.x) 及更高版本
要创建引用 SQL Server 命名实例的外部数据源,请使用 CONNECTION_OPTIONS
指定实例名称。
在下面的示例中,WINSQL2019
是主机名,而 SQL2019
是实例名。 'Server=%s\SQL2019'
是键值对。
CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
LOCATION = 'sqlserver://WINSQL2019',
CONNECTION_OPTIONS = 'Server=%s\SQL2019',
CREDENTIAL = SQLServerCredentials
);
或者,可以使用端口连接到 SQL Server 默认实例。
CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
LOCATION = 'sqlserver://WINSQL2019:58137',
CREDENTIAL = SQLServerCredentials
);
G. 创建外部数据源以引用 Always On 可用性组的可读次要副本
适用于:SQL Server 2019 (15.x) 及更高版本
要创建引用 SQL Server 的可读次要副本的外部数据源,请使用 CONNECTION_OPTIONS
指定 ApplicationIntent=ReadOnly
。
首先,创建数据库限定范围的凭据,为经过身份验证的 SQL 登录名存储凭据。 用于 PolyBase 的 SQL ODBC 连接器仅支持基本身份验证。 创建数据库范围凭据之前,数据库必须具有主密钥用于保护凭据。 有关详细信息,请参阅 CREATE MASTER KEY。 以下示例创建数据库限定范围的凭据,提供自己的登录名和密码。
CREATE DATABASE SCOPED CREDENTIAL SQLServerCredentials
WITH IDENTITY = 'username',
SECRET = 'password';
接下来,创建新的外部数据源。
不需要 ODBC Database
参数,而是在 CREATE EXTERNAL TABLE 语句中通过三部分名称提供数据库名称(在 LOCATION 参数中)。 有关示例,请参阅 CREATE EXTERNAL TABLE。
在下面的示例中,WINSQL2019AGL
是可用性组侦听程序名称,dbname
是要成为 CREATE EXTERNAL TABLE 语句目标的数据库的名称。
CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
LOCATION = 'sqlserver://WINSQL2019AGL',
CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly',
CREDENTIAL = SQLServerCredentials
);
可以通过在系统视图 sys.servers
上指定 ApplicationIntent
并创建外部表来演示可用性组的重定向行为。 在以下示例脚本中,会创建两个外部数据源,并为每个外部数据源创建一个外部表。 使用视图测试哪个服务器在响应连接。 还可以通过只读路由功能实现类似结果。 有关详细信息,请参阅为 Always On 可用性组配置只读路由。
CREATE EXTERNAL DATA SOURCE [DataSource_SQLInstanceListener_ReadOnlyIntent]
WITH (
LOCATION = 'sqlserver://WINSQL2019AGL',
CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly',
CREDENTIAL = [SQLServerCredentials]
);
GO
CREATE EXTERNAL DATA SOURCE [DataSource_SQLInstanceListener_ReadWriteIntent]
WITH (
LOCATION = 'sqlserver://WINSQL2019AGL',
CONNECTION_OPTIONS = 'ApplicationIntent=ReadWrite',
CREDENTIAL = [SQLServerCredentials]
);
GO
在可用性组的数据库内,创建视图以返回 sys.servers
和本地实例的名称,这可帮助确定哪个副本在响应查询。 有关详细信息,请参阅 sys.servers。
CREATE VIEW vw_sys_servers
AS
SELECT [name]
FROM sys.servers
WHERE server_id = 0;
GO
然后,在源实例上创建外部表:
CREATE EXTERNAL TABLE vw_sys_servers_ro (name SYSNAME NOT NULL)
WITH (
DATA_SOURCE = [DataSource_SQLInstanceListener_ReadOnlyIntent],
LOCATION = N'dbname.dbo.vw_sys_servers'
);
GO
CREATE EXTERNAL TABLE vw_sys_servers_rw (name SYSNAME NOT NULL)
WITH (
DATA_SOURCE = [DataSource_SQLInstanceListener_ReadWriteIntent],
LOCATION = N'dbname.dbo.vw_sys_servers'
);
GO
SELECT [name]
FROM dbo.vw_sys_servers_ro;--should return secondary replica instance
SELECT [name]
FROM dbo.vw_sys_servers_rw;--should return primary replica instance
GO
示例:批量操作
重要
为批量操作配置外部数据源时,请勿在 LOCATION
URL 的末尾放置尾随 /、文件名或共享访问签名参数。
H. 创建外部数据源以用于从 Azure 存储检索数据的批量操作
适用于:SQL Server 2017 (14.x) 和 SQL Server 2019 (15.x)
对使用 BULK INSERT 或 OPENROWSET 的批量操作使用以下数据源。 凭据必须设置 SHARED ACCESS SIGNATURE
作为标识、不应在 SAS 令牌中具有前导 ?
、必须对应加载的文件(例如 srt=o&sp=r
)至少具有读取权限,并且有效期应有效(所有日期均采用 UTC 时间)。 有关共享访问签名的详细信息,请参阅使用共享访问签名 (SAS)。
CREATE DATABASE SCOPED CREDENTIAL AccessAzureInvoices
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
-- Remove ? from the beginning of the SAS token
SECRET = '<azure_shared_access_signature>';
CREATE EXTERNAL DATA SOURCE MyAzureInvoices
WITH (
LOCATION = 'https://newinvoices.blob.core.windows.net/week3',
CREDENTIAL = AccessAzureInvoices,
TYPE = BLOB_STORAGE
);
若要查看正在使用的示例,请参阅 BULK INSERT。
I. 创建外部数据源来使用 abfs:// 接口访问 Azure 存储中的数据
适用于:SQL Server 2019 (15.x) CU11 及更高版本
在此示例中,外部数据源是使用 Azure Blob Filesystem 驱动程序 (ABFS) 的 Azure Data Lake Storage Gen2 帐户 logs
。 存储容器被称为 daily
。 Azure Data Lake Storage Gen2 外部数据源仅用于数据传输,不支持谓词下推。
本示例演示如何创建数据库范围的凭据来对 Azure Data Lake Storage Gen2 帐户进行身份验证。 在数据库凭据机密中指定 Azure 存储帐户密钥。 可以在数据库范围凭据标识中指定任何字符串,因为在对 Azure 存储进行身份验证的过程中不会使用它。
-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY = '<my_account>',
SECRET = '<azure_storage_account_key>';
-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
LOCATION = 'abfss://daily@logs.dfs.core.windows.net/',
CREDENTIAL = AzureStorageCredential,
TYPE = HADOOP
);
J. 使用泛型 ODBC 创建到 PostgreSQL 的外部数据源
与前面示例一样,先创建数据库主密钥和数据库范围凭据。 数据库范围的凭据将用于外部数据源。 此示例还假定服务器上安装了适用于 PostgreSQL 的泛型 ODBC 数据提供程序。
在本例中,泛型 ODBC 数据提供程序用于连接到同一网络中的 PostgreSQL 数据库服务器,其中 PostgreSQL 服务器的完全限定域名为 POSTGRES1
,使用的默认端口是 TCP 5432。
CREATE EXTERNAL DATA SOURCE POSTGRES1
WITH (
LOCATION = 'odbc://POSTGRES1.domain:5432',
CONNECTION_OPTIONS = 'Driver={PostgreSQL Unicode(x64)};',
CREDENTIAL = postgres_credential
);
后续步骤
概述:SQL Server 2022
适用于: SQL Server 2022 (16.x) 及更高版本
为 PolyBase 查询创建外部数据源。 外部数据源用于建立连接以及支持以下这些用例:
- 使用 PolyBase 执行数据虚拟化和数据加载
- 使用
BULK INSERT
或OPENROWSET
大容量加载操作
注意
此语法在不同版本的 SQL Server 之间有所不同。 使用版本选择器下拉列表选择适当版本。 此内容适用于 SQL Server 2022 (16.x) 及更高版本。
SQL Server 2022 及更高版本的语法
有关语法约定的详细信息,请参阅 Transact-SQL 语法约定。
CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
( [ LOCATION = '<prefix>://<path>[:<port>]' ]
[ [ , ] CONNECTION_OPTIONS = '<key_value_pairs>'[,...]]
[ [ , ] CREDENTIAL = <credential_name> ]
[ [ , ] PUSHDOWN = { ON | OFF } ]
)
[ ; ]
参数
data_source_name
指定数据源的用户定义名称。 该名称在 SQL Server 数据库中必须唯一。
LOCATION = '<prefix>://<path[:port]>'
提供连接协议和外部数据源的路径。
外部数据源 | 连接器位置前缀 | 位置路径 | 产品/服务支持的位置 | 身份验证 |
---|---|---|---|---|
Azure 存储帐户 (V2) | abs |
abs://<container_name>@<storage_account_name>.blob.core.windows.net/ 或 abs://<storage_account_name>.blob.core.windows.net/<container_name> |
自 SQL Server 2022 (16.x) 起 支持分层命名空间。 |
共享访问签名 (SAS) |
Azure Data Lake Storage Gen2 | adls |
adls://<container_name>@<storage_account_name>.dfs.core.windows.net/ 或 adls://<storage_account_name>.dfs.core.windows.net/<container_name> |
自 SQL Server 2022 (16.x) 起 | 共享访问签名 (SAS) |
SQL Server | sqlserver |
<server_name>[\<instance_name>][:port] |
自 SQL Server 2019 (15.x) 起 | 仅 SQL 身份验证 |
Oracle | oracle |
<server_name>[:port] |
自 SQL Server 2019 (15.x) 起 | 仅基本身份验证 |
Teradata | teradata |
<server_name>[:port] |
自 SQL Server 2019 (15.x) 起 | 仅基本身份验证 |
MongoDB 或 Cosmos DB API for MongoDB | mongodb |
<server_name>[:port] |
自 SQL Server 2019 (15.x) 起 | 仅基本身份验证 |
泛型 ODBC | odbc |
<server_name>[:port] |
自 SQL Server 2019 (15.x) 起 - 仅限 Windows | 仅基本身份验证 |
批量操作 | https |
<storage_account>.blob.core.windows.net/<container> |
自 SQL Server 2017 (14.x) 起 | 共享访问签名 (SAS) |
S3 兼容的对象存储 | s3 |
- S3 兼容: s3://<server_name>:<port>/ - AWS S3: s3://<bucket_name>.S3.amazonaws.com[:port]/<folder> 或 s3://s3.amazonaws.com[:port]/<bucket_name>/<folder> |
自 SQL Server 2022 (16.x) 起 | 基本或直通 (STS) * |
* 必须是 数据库范围的凭据,其中 IDENTITY 硬编码, IDENTITY = 'S3 Access Key'
SECRET 参数采用格式 = '<AccessKeyID>:<SecretKeyID>'
或使用直通 (STS) 授权。 有关详细信息,请参阅配置 PolyBase 以访问 S3 兼容的对象存储中的外部数据。
位置路径:
port
= 外部数据源侦听的端口。 在许多情况下(具体取决于网络配置)是可选的。<container_name>
= 保存数据的存储帐户的容器。 根容器是只读的,数据无法写回容器。<storage_account>
= Azure 资源的存储帐户名称。<server_name>
= 主机名。<instance_name>
= SQL Server 命名实例的名称。 如果在目标实例上运行 SQL Server Browser 服务,则使用此路径。<ip_address>:<port>
= 仅适用于与 S3 兼容的对象存储(从 SQL Server 2022 (16.x) 开始),用于连接到与 S3 兼容的存储的终结点和端口。<bucket_name>
= 仅适用于 S3 兼容的对象存储(从 SQL Server 2022 (16.x)开始),特定于存储平台。<region>
= 仅适用于 S3 兼容的对象存储(从 SQL Server 2022 (16.x)开始),特定于存储平台。<folder>
= 存储 URL 中的存储路径的一部分。
设置位置时的其他说明和指南:
- 创建对象时,SQL Server 数据库引擎不会验证外部数据源是否存在。 要进行验证,请使用外部数据源创建外部表。
- 可使用
sqlserver
连接器将 SQL Server 2019 (15.x) 连接到另一个 SQL Server 或 Azure SQL 数据库。 - 通过
ODBC
连接时,请指定Driver={<Name of Driver>}
。 - 在 SQL Server 2022 (16.x) 中,支持在 Azure Data Lake Storage Gen2 中通过前缀
adls
对 Azure 存储帐户 (V2) 使用分层命名空间选项。
- SQL Server 对 HDFS Cloudera (CDP) 和 Hortonworks (HDP) 外部数据源的支持将停用,不会包含在 SQL Server 2022 (16.x) 中。 SQL Server 2022 (16.x) 中无需使用 TYPE 参数。
- 有关从 SQL Server 2022 (16.x) 开始的 S3 兼容对象存储和 PolyBase 的详细信息,请参阅配置 PolyBase 以访问 S3 兼容对象存储中的外部数据。 有关在 S3 兼容对象存储中查询 parquet 文件的示例,请参阅使用 PolyBase 虚拟化 S3 兼容对象存储中的 parquet 文件。
- 与以前的版本不同,在 SQL Server 2022 (16.x) 中,用于 Azure 存储帐户 (v2) 的前缀已从
wasb[s]
更改为abs
。 - 与以前的版本不同,在 SQL Server 2022 (16.x) 中,用于 Azure Data Lake Storage Gen2 的前缀已从
abfs[s]
更改为adls
。 - 有关使用 PolyBase 在 Azure 存储中直观呈现 CSV 文件的示例,请参阅使用 PolyBase 虚拟化 CSV 文件。
- 有关使用 PolyBase 在 ADLS Gen2 中虚拟化 Delta 表的示例,请参阅使用 PolyBase 虚拟化 Delta 表。
- SQL Server 2022 (16.x) 完全支持 Azure 存储帐户 v2 (
abs
) 和 Azure Data Lake Gen2 (adls
) 的两种 URL 格式。- LOCATION 路径可以使用格式:
<container>@<storage_account_name>..
(建议)或<storage_account_name>../<container>
。 例如:- Azure 存储帐户 v2:
abs://<container>@<storage_account_name>.blob.core.windows.net
(建议)或abs://<storage_account_name>.blob.core.windows.net/<container>
。 - Azure Data Lake Gen2 支持:
adls://<container>@<storage_account_name>.blob.core.windows.net
(建议)或adls://<storage_account_name>.dfs.core.windows.net/<container>
。
- Azure 存储帐户 v2:
- LOCATION 路径可以使用格式:
CONNECTION_OPTIONS = key_value_pair
针对 SQL Server 2019 (15.x) 及更高版本指定。 通过 ODBC
连接到外部数据源时指定其他选项。 若要使用多个连接选项,请用分号分隔它们。
适用于通用 ODBC
连接,还适用于 SQL Server、Oracle、Teradata、MongoDB 和 Azure Cosmos DB API for MongoDB 的内置 ODBC
连接器。
key_value_pair
是特定连接选项的关键字和值。 哪些关键字和值可用由外部数据源类型决定。 驱动程序的名称是必需的(最基本的要求),但设置其他选项(例如 APP='<your_application_name>'
或 ApplicationIntent= ReadOnly|ReadWrite
)也很有用,可以帮助进行故障排除。
可能的键值对特定于驱动程序。 有关每个提供程序的详细信息,请参阅 CREATE EXTERNAL DATA SOURCE (Transact-SQL) CONNECTION_OPTIONS。
从 适用范围开始: SQL Server 2022 (16.x) 累积更新 2,引入了其他关键字以支持 Oracle TNS 文件:
- 关键字
TNSNamesFile
指定位于 Oracle 服务器上的tnsnames.ora
文件的文件路径。 - 关键字
ServerName
指定tnsnames.ora
内使用的别名,该别名将用于替换主机名和端口。
PUSHDOWN = 打开 | 关闭
适用于:SQL Server 2019 (15.x) 及更高版本。 说明是否可以将计算下推到外部数据源。 它默认处于打开状态。
在外部数据源级别连接到 SQL Server、Oracle、Teradata、MongoDB、Azure Cosmos DB API for MongoDB 或 ODBC 时,支持使用 PUSHDOWN
。
通过提示实现在查询级别启用或禁用下推。
CREDENTIAL = credential_name
指定用于对外部数据源进行身份验证的数据库范围凭据。
创建凭证时的其他说明和指导:
- 只有在数据得到保护的情况下才需要
CREDENTIAL
。 允许匿名访问的数据集不需要CREDENTIAL
。 - 访问 Azure 存储帐户 (V2) 或 Azure Data Lake Storage Gen2 时,
IDENTITY
必须为SHARED ACCESS SIGNATURE
。
创建共享访问签名的方式有很多种:
可以导航到“Azure 门户”->“<Your_Storage_Account>”->“共享访问签名”->“配置权限”->“生成 SAS 和连接字符串”来创建 SAS 令牌。 有关详细信息,请参阅生成共享访问签名。
可以通过 PowerShell、Azure CLI、.NET 和 REST API 以编程方式创建 SAS。 有关详细信息,请参阅使用共享访问签名 (SAS) 授予对 Azure 存储资源的有限访问权限。
应按如下所示配置 SAS 令牌:
- 生成某个 SAS 令牌时,其开头会包含问号(“?”)。 配置为密码时排除前导
?
。 - 使用有效的有效期(所有日期均采用 UTC 时间)。
- 至少授予对应加载的文件的读取权限(例如
srt=o&sp=r
)。 可以为不同的用例创建多个共享访问签名。 应按如下所示授予权限:
操作 权限 从文件中读取数据 读取 从多个文件和子文件夹读取数据 读取和列出 使用 Create External Table as Select (CETAS) 读取、创建、列出和写入 - 生成某个 SAS 令牌时,其开头会包含问号(“?”)。 配置为密码时排除前导
对于 Azure Blob 存储和 Azure Data Lake Gen 2:
- 允许的服务:必须选择
Blob
才能生成 SAS 令牌 - 允许的资源类型:必须选择
Container
和Object
才能生成 SAS 令牌
- 允许的服务:必须选择
有关将 CREDENTIAL
与 S3 兼容对象存储和 PolyBase 结合使用的示例,请参阅配置 PolyBase 以访问 S3 兼容对象存储中的外部数据。
要创建数据库范围凭据,请参阅 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)。
权限
需要对 SQL Server 中数据库的 CONTROL
权限。
锁定
对 EXTERNAL DATA SOURCE
对象采用共享锁。
安全性
PolyBase 支持大多数外部数据源的基于代理的身份验证。 创建数据库范围凭据以创建代理帐户。
升级到 SQL Server 2022
从 SQL Server 2022 (16.x) 开始,不再支持 Hadoop 外部数据源。 需要手动重新创建以前使用 TYPE = HADOOP
创建的外部数据源,以及使用此外部数据源的任何外部表。
用户还需要将其外部数据源配置为在连接到 Azure 存储时使用新连接器。
外部数据源 | 源 | 功能 |
---|---|---|
Azure Blob 存储 | wasb[s] | abs |
ADLS Gen2 | abfs[s] | adls |
示例
重要
有关如何安装和启用 PolyBase 的信息,请参阅在 Windows 上安装 PolyBase
A. 在 SQL Server 中创建外部数据源以引用 Oracle
要创建引用 Oracle 的外部数据源,请确保具有数据库范围凭据。 也可以选择针对此数据源启用或禁用计算下推。
-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL OracleProxyAccount
WITH IDENTITY = 'oracle_username',
SECRET = 'oracle_password';
CREATE EXTERNAL DATA SOURCE MyOracleServer
WITH (
LOCATION = 'oracle://145.145.145.145:1521',
CREDENTIAL = OracleProxyAccount,
PUSHDOWN = ON
);
(可选)Oracle 的外部数据源可以使用代理身份验证提供精细的访问控制。 可以将代理用户配置为,与被模拟的用户相比,具有有限的访问权限。
CREATE DATABASE SCOPED CREDENTIAL [OracleProxyCredential]
WITH IDENTITY = 'oracle_username',
SECRET = 'oracle_password';
CREATE EXTERNAL DATA SOURCE [OracleSalesSrvr]
WITH (
LOCATION = 'oracle://145.145.145.145:1521',
CONNECTION_OPTIONS = 'ImpersonateUser=%CURRENT_USER',
CREDENTIAL = [OracleProxyCredential]
);
或者,可以使用 TNS 进行身份验证。
从适用范围开始:SQL Server 2022 (16.x) 累积更新 2,
CREATE EXTERNAL DATA SOURCE
现在支持在连接到 Oracle 时使用 TNS 文件。
CONNECTION_OPTIONS
参数已扩展,现在使用 TNSNamesFile
和 ServerName
作为变量来浏览 tnsnames.ora
文件并与服务器建立连接。
在下面的示例中,在运行时,SQL Server 将搜索由 TNSNamesFile
指定的 tnsnames.ora
文件位置,并搜索由 ServerName
指定的主机和网络端口。
CREATE EXTERNAL DATA SOURCE [external_data_source_name]
WITH (
LOCATION = N'oracle://XE',
CREDENTIAL = [OracleCredentialTest],
CONNECTION_OPTIONS = N'TNSNamesFile=C:\Temp\tnsnames.ora;ServerName=XE'
);
B. 创建外部数据源以通过 PolyBase 连接引用 SQL Server 命名实例
适用于:SQL Server 2019 (15.x) 及更高版本
要创建引用 SQL Server 命名实例的外部数据源,请使用 CONNECTION_OPTIONS
指定实例名称。
首先,创建数据库限定范围的凭据,为经过身份验证的 SQL 登录名存储凭据。 用于 PolyBase 的 SQL ODBC 连接器仅支持基本身份验证。 创建数据库范围凭据之前,数据库必须具有主密钥用于保护凭据。 有关详细信息,请参阅 CREATE MASTER KEY。 以下示例创建数据库限定范围的凭据,提供自己的登录名和密码。
CREATE DATABASE SCOPED CREDENTIAL SQLServerCredentials
WITH IDENTITY = 'username',
SECRET = 'password';
在下面的示例中,WINSQL2019
是主机名,而 SQL2019
是实例名。 'Server=%s\SQL2019'
是键值对。
CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
LOCATION = 'sqlserver://WINSQL2019',
CONNECTION_OPTIONS = 'Server=%s\SQL2019',
CREDENTIAL = SQLServerCredentials
);
或者,可以使用端口连接到 SQL Server 默认实例。
CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
LOCATION = 'sqlserver://WINSQL2019:58137',
CREDENTIAL = SQLServerCredentials
);
C. 创建外部数据源以引用 Always On 可用性组的可读次要副本
适用于:SQL Server 2019 (15.x) 及更高版本
要创建引用 SQL Server 的可读次要副本的外部数据源,请使用 CONNECTION_OPTIONS
指定 ApplicationIntent=ReadOnly
。
首先,创建数据库限定范围的凭据,为经过身份验证的 SQL 登录名存储凭据。 用于 PolyBase 的 SQL ODBC 连接器仅支持基本身份验证。 创建数据库范围凭据之前,数据库必须具有主密钥用于保护凭据。 有关详细信息,请参阅 CREATE MASTER KEY。 以下示例创建数据库限定范围的凭据,提供自己的登录名和密码。
CREATE DATABASE SCOPED CREDENTIAL SQLServerCredentials
WITH IDENTITY = 'username',
SECRET = 'password';
接下来,创建新的外部数据源。
不需要 ODBC Database
参数,而是在 CREATE EXTERNAL TABLE 语句中通过三部分名称提供数据库名称(在 LOCATION 参数中)。 有关示例,请参阅 CREATE EXTERNAL TABLE。
在下面的示例中,WINSQL2019AGL
是可用性组侦听程序名称,dbname
是要成为 CREATE EXTERNAL TABLE 语句目标的数据库的名称。
CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
LOCATION = 'sqlserver://WINSQL2019AGL',
CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly',
CREDENTIAL = SQLServerCredentials
);
可以通过在系统视图 sys.servers
上指定 ApplicationIntent
并创建外部表来演示可用性组的重定向行为。 在以下示例脚本中,会创建两个外部数据源,并为每个外部数据源创建一个外部表。 使用视图测试哪个服务器在响应连接。 还可以通过只读路由功能实现类似结果。 有关详细信息,请参阅为 Always On 可用性组配置只读路由。
CREATE EXTERNAL DATA SOURCE [DataSource_SQLInstanceListener_ReadOnlyIntent]
WITH (
LOCATION = 'sqlserver://WINSQL2019AGL',
CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly',
CREDENTIAL = [SQLServerCredentials]
);
GO
CREATE EXTERNAL DATA SOURCE [DataSource_SQLInstanceListener_ReadWriteIntent]
WITH (
LOCATION = 'sqlserver://WINSQL2019AGL',
CONNECTION_OPTIONS = 'ApplicationIntent=ReadWrite',
CREDENTIAL = [SQLServerCredentials]
);
GO
在可用性组的数据库内,创建视图以返回 sys.servers
和本地实例的名称,这可帮助确定哪个副本在响应查询。 有关详细信息,请参阅 sys.servers。
CREATE VIEW vw_sys_servers AS
SELECT [name] FROM sys.servers
WHERE server_id = 0;
GO
然后,在源实例上创建外部表:
CREATE EXTERNAL TABLE vw_sys_servers_ro (name SYSNAME NOT NULL)
WITH (
DATA_SOURCE = [DataSource_SQLInstanceListener_ReadOnlyIntent],
LOCATION = N'dbname.dbo.vw_sys_servers'
);
GO
CREATE EXTERNAL TABLE vw_sys_servers_rw (name SYSNAME NOT NULL)
WITH (
DATA_SOURCE = [DataSource_SQLInstanceListener_ReadWriteIntent],
LOCATION = N'dbname.dbo.vw_sys_servers'
);
GO
SELECT [name]
FROM dbo.vw_sys_servers_ro;--should return secondary replica instance
SELECT [name]
FROM dbo.vw_sys_servers_rw;--should return primary replica instance
GO
D. 通过 PolyBase 创建外部数据源以查询 S3 兼容对象存储中的 parquet 文件
适用于:SQL Server 2022 (16.x) 及更高版本
以下示例脚本在 SQL Server 的源用户数据库中创建外部数据源 s3_ds
。 外部数据源引用 s3_dc
数据库范围的凭据。
CREATE DATABASE SCOPED CREDENTIAL s3_dc
WITH IDENTITY = 'S3 Access Key', -- for S3-compatible object storage the identity must always be S3 Access Key
SECRET = '<access_key_id>:<secret_key_id>' -- provided by the S3-compatible object storage
GO
CREATE EXTERNAL DATA SOURCE s3_ds
WITH (
LOCATION = 's3://<ip_address>:<port>/',
CREDENTIAL = s3_dc
);
GO
使用 sys.external_data_sources 验证新的外部数据源。
SELECT * FROM sys.external_data_sources;
然后,以下示例演示如何使用 T-SQL 通过 OPENROWSET 查询来查询存储在 S3 兼容的对象存储中的 parquet 文件。 有关详细信息,请参阅使用 PolyBase 虚拟化 S3 兼容对象存储中的 parquet 文件。
SELECT *
FROM OPENROWSET (
BULK '/<bucket>/<parquet_folder>',
FORMAT = 'PARQUET',
DATA_SOURCE = 's3_ds'
) AS [cc];
E. 使用泛型 ODBC 创建到 PostgreSQL 的外部数据源
与前面示例一样,先创建数据库主密钥和数据库范围凭据。 数据库范围的凭据将用于外部数据源。 此示例还假定服务器上安装了适用于 PostgreSQL 的泛型 ODBC 数据提供程序。
在本例中,泛型 ODBC 数据提供程序用于连接到同一网络中的 PostgreSQL 数据库服务器,其中 PostgreSQL 服务器的完全限定域名为 POSTGRES1
,使用的默认端口是 TCP 5432。
CREATE EXTERNAL DATA SOURCE POSTGRES1
WITH (
LOCATION = 'odbc://POSTGRES1.domain:5432',
CONNECTION_OPTIONS = 'Driver={PostgreSQL Unicode(x64)};',
CREDENTIAL = postgres_credential
);
Azure 存储
创建共享访问签名
对于 Azure Blob 存储和 Azure Data Lake Gen2,支持的身份验证方法是共享访问签名 (SAS)。 生成共享访问签名令牌的一种简单方法,请执行以下步骤。 有关详细信息,请参阅凭据。
- 导航到 Azure 门户中的所需存储帐户。
- 导航到“数据存储”菜单下的所需容器。
- 选择“共享访问令牌”。
- 根据所需操作选择适当的权限,如需参考,请使用下表:
操作 | 权限 |
---|---|
从文件中读取数据 | 读取 |
从多个文件和子文件夹读取数据 | 读取和列出 |
使用 Create External Table as Select (CETAS) | 读取、创建和写入 |
- 选择令牌的过期日期。
- 生成 SAS 令牌和 URL。
- 复制 SAS 令牌。
F. 创建外部数据源来使用 abs:// 接口访问 Azure Blob 存储中的数据
适用于:SQL Server 2022 (16.x) 及更高版本
从 SQL Server 2022 (16.x) 开始,为 Azure 存储帐户 v2 使用新前缀 abs
。 abs
前缀支持使用 SHARED ACCESS SIGNATURE
进行身份验证。 abs
前缀替换了以前版本中使用的 wasb
。 HADOOP 不再受支持,因此不再需要使用 TYPE = BLOB_STORAGE
。
不再需要 Azure 存储帐户密钥,而是使用 SAS 令牌,如以下示例所示:
-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
GO
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredentialv2
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', -- to use SAS the identity must be fixed as-is
SECRET = '<Blob_SAS_Token>';
GO
-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
LOCATION = 'abs://<container>@<storage_account_name>.blob.core.windows.net/',
CREDENTIAL = AzureStorageCredentialv2,
);
有关如何访问存储在 Azure Blob 存储中的 CSV 文件的更详细示例,请参阅使用 PolyBase 虚拟化 CSV 文件。
G. 创建外部数据源以访问 Azure Data Lake Gen2 中的数据
适用于:SQL Server 2022 (16.x) 及更高版本
从 SQL Server 2022 (16.x) 开始,为 Azure Data Lake Gen2 使用新前缀 adls
,替换以前版本中使用的 abfs
。 前缀 adls
还支持将 SAS 令牌作为身份验证方法,如此示例所示:
--Create a database scoped credential using SAS Token
CREATE DATABASE SCOPED CREDENTIAL datalakegen2
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<DataLakeGen2_SAS_Token>';
GO
CREATE EXTERNAL DATA SOURCE data_lake_gen2_dfs
WITH (
LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
CREDENTIAL = datalakegen2
);
有关如何访问存储在 Azure Data Lake Gen2 上的差异文件的更详细示例,请参阅使用 PolyBase 虚拟化差异文件。
示例:批量操作
重要
为批量操作配置外部数据源时,请勿在 LOCATION
URL 的末尾放置尾随 /、文件名或共享访问签名参数。
H. 创建外部数据源以用于从 Azure 存储检索数据的批量操作
适用于:SQL Server 2022 (16.x) 及更高版本。
对使用 BULK INSERT 或 OPENROWSET 的批量操作使用以下数据源。 凭据必须设置 SHARED ACCESS SIGNATURE
作为标识、不应在 SAS 令牌中具有前导 ?
、必须对应加载的文件(例如 srt=o&sp=r
)至少具有读取权限,并且有效期应有效(所有日期均采用 UTC 时间)。 有关共享访问签名的详细信息,请参阅使用共享访问签名 (SAS)。
CREATE DATABASE SCOPED CREDENTIAL AccessAzureInvoices
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
-- Remove ? from the beginning of the SAS token
SECRET = '<azure_shared_access_signature>';
CREATE EXTERNAL DATA SOURCE MyAzureInvoices
WITH (
LOCATION = 'abs://<container>@<storage_account_name>.blob.core.windows.net/',
CREDENTIAL = AccessAzureInvoices,
);
后续步骤
* SQL 数据库 *
概述:Azure SQL Database
适用于:Azure SQL 数据库
为弹性查询创建外部数据源。 外部数据源用于建立连接以及支持以下这些用例:
- 使用
BULK INSERT
或OPENROWSET
大容量加载操作 - 使用弹性查询通过 SQL 数据库查询远程 SQL 数据库或 Azure Synapse 实例
- 使用弹性查询查询分片的 SQL 数据库
语法
有关语法约定的详细信息,请参阅 Transact-SQL 语法约定。
CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
( [ LOCATION = '<prefix>://<path>[:<port>]' ]
[ [ , ] CREDENTIAL = <credential_name> ]
[ [ , ] TYPE = { BLOB_STORAGE | RDBMS | SHARD_MAP_MANAGER } ]
[ [ , ] DATABASE_NAME = '<database_name>' ]
[ [ , ] SHARD_MAP_NAME = '<shard_map_manager>' ] )
[ ; ]
参数
data_source_name
指定数据源的用户定义名称。 该名称在 SQL 数据库中必须是唯一的。
LOCATION = '<prefix>://<path[:port]>'
提供连接协议和外部数据源的路径。
外部数据源 | 连接器位置前缀 | 位置路径 | 可用性 |
---|---|---|---|
批量操作 | https |
<storage_account>.blob.core.windows.net/<container> |
|
弹性查询(分片) | 不是必需 | <shard_map_server_name>.database.windows.net |
|
弹性查询(远程) | 不是必需 | <remote_server_name>.database.windows.net |
|
EdgeHub | edgehub |
edgehub:// |
仅在 Azure SQL Edge 中可用。 EdgeHub 始终位于 Azure SQL Edge 实例的本地。 因此,无需指定路径或端口值。 |
Kafka | kafka |
kafka://<kafka_bootstrap_server_name_ip>:<port_number> |
仅在 Azure SQL Edge 中可用。 |
位置路径:
<shard_map_server_name>
= Azure 中托管分片映射管理器的逻辑服务器名称。DATABASE_NAME
参数提供用于托管分片映射的数据库,SHARD_MAP_NAME
用于分片映射本身。<remote_server_name>
= 弹性查询的目标逻辑服务器名称。 使用DATABASE_NAME
参数指定数据库名称。
设置位置时的其他说明和指南:
- 创建对象时,数据库引擎不会验证外部数据源是否存在。 要进行验证,请使用外部数据源创建外部表。
CREDENTIAL = credential_name
指定用于对外部数据源进行身份验证的数据库范围凭据。
创建凭证时的其他说明和指导:
- 若要将数据从 Azure 存储加载到 Azure SQL 数据库,请使用共享访问签名(SAS 令牌)。
- 只有在数据得到保护的情况下才需要
CREDENTIAL
。 允许匿名访问的数据集不需要CREDENTIAL
。 - 当
TYPE
=BLOB_STORAGE
时,必须使用SHARED ACCESS SIGNATURE
作为标识创建凭据。 - 通过 WASB 连接器连接到 Azure 存储时,必须使用存储帐户密钥(而不是共享访问签名 (SAS))进行身份验证。
- 当
TYPE
=HADOOP
时,必须使用存储帐户密钥作为SECRET
创建凭据。 TYPE
=BLOB_STORAGE
仅允许用于批量操作;不能使用TYPE
=BLOB_STORAGE
为外部数据源创建外部表。
创建共享访问签名的方式有很多种:
可以导航到“Azure 门户”->“<Your_Storage_Account>”->“共享访问签名”->“配置权限”->“生成 SAS 和连接字符串”来创建 SAS 令牌。 有关详细信息,请参阅生成共享访问签名。
可以通过 PowerShell、Azure CLI、.NET 和 REST API 以编程方式创建 SAS。 有关详细信息,请参阅使用共享访问签名 (SAS) 授予对 Azure 存储资源的有限访问权限。
应按如下所示配置 SAS 令牌:
- 生成某个 SAS 令牌时,其开头会包含问号(“?”)。 配置为密码时排除前导
?
。 - 使用有效的有效期(所有日期均采用 UTC 时间)。
- 至少授予对应加载的文件的读取权限(例如
srt=o&sp=r
)。 可以为不同的用例创建多个共享访问签名。 应按如下所示授予权限:
操作 权限 从文件中读取数据 读取 从多个文件和子文件夹读取数据 读取和列出 使用 Create External Table as Select (CETAS) 读取、创建和写入 - 生成某个 SAS 令牌时,其开头会包含问号(“?”)。 配置为密码时排除前导
有关使用具有 SHARED ACCESS SIGNATURE
且 TYPE
= BLOB_STORAGE
的 CREDENTIAL
的示例,请参阅创建外部数据源以执行批量操作并将数据从 Azure 存储检索到 SQL 数据库
要创建数据库范围凭据,请参阅 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)。
TYPE = [ BLOB_STORAGE | RDBMS | SHARD_MAP_MANAGER]
指定要配置的外部数据源的类型。 此参数并非总是必需的。
- 使用
RDBMS
通过 SQL 数据库中的弹性查询进行跨数据库查询。 - 连接到分片的 SQL 数据库时,请使用
SHARD_MAP_MANAGER
创建外部数据源。 - 使用 BULK INSERT 或 OPENROWSET 执行批量操作时,请使用
BLOB_STORAGE
。
重要
如果使用任何其他外部数据源,请勿设置 TYPE
。
DATABASE_NAME = database_name
当 TYPE
设置为 RDBMS
或 SHARD_MAP_MANAGER
时,配置此参数。
TYPE | DATABASE_NAME 的值 |
---|---|
RDBMS | 使用 LOCATION 提供的服务器上的远程数据库的名称 |
SHARD_MAP_MANAGER | 作为分片映射管理器运行的数据库的名称 |
有关如何创建 TYPE
= RDBMS
的外部数据源的示例,请参阅创建 RDBMS 外部数据源
SHARD_MAP_NAME = shard_map_name
将 TYPE
参数设置为 SHARD_MAP_MANAGER
时使用,仅用于设置分片映射的名称。
有关如何创建 TYPE
= SHARD_MAP_MANAGER
的外部数据源的示例,请参阅创建分片映射管理器外部数据源
权限
需要对 Azure SQL 数据库中数据库的 CONTROL
权限。
锁定
对 EXTERNAL DATA SOURCE
对象采用共享锁。
示例
A. 创建分片映射管理器外部数据源
若要创建外部数据源来引用 SHARD_MAP_MANAGER
,请指定托管 SQL 数据库中的分片映射管理器或虚拟机上的 SQL Server 数据库的 SQL 数据库服务器名称。
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
CREATE DATABASE SCOPED CREDENTIAL ElasticDBQueryCred
WITH IDENTITY = '<username>',
SECRET = '<password>';
CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc
WITH (
TYPE = SHARD_MAP_MANAGER,
LOCATION = '<server_name>.database.windows.net',
DATABASE_NAME = 'ElasticScaleStarterKit_ShardMapManagerDb',
CREDENTIAL = ElasticDBQueryCred,
SHARD_MAP_NAME = 'CustomerIDShardMap'
);
有关分步教程,请参阅跨扩展云数据库进行报告(预览)。
B. 创建 RDBMS 外部数据源
若要创建外部数据源以引用 RDBMS,请指定 SQL 数据库中的远程数据库的 SQL 数据库服务器名称。
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
CREATE DATABASE SCOPED CREDENTIAL SQL_Credential
WITH IDENTITY = '<username>',
SECRET = '<password>';
CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc
WITH (
TYPE = RDBMS,
LOCATION = '<server_name>.database.windows.net',
DATABASE_NAME = 'Customers',
CREDENTIAL = SQL_Credential
);
有关 RDBMS 的分步教程,请参阅跨数据库查询(纵向分区)入门(预览)。
示例:批量操作
重要
为批量操作配置外部数据源时,请勿在 LOCATION
URL 的末尾放置尾随 /、文件名或共享访问签名参数。
C. 创建外部数据源以用于从 Azure 存储检索数据的批量操作
对使用 BULK INSERT 或 OPENROWSET 的批量操作使用以下数据源。 凭据必须设置 SHARED ACCESS SIGNATURE
作为标识、不应在 SAS 令牌中具有前导 ?
、必须对应加载的文件(例如 srt=o&sp=r
)至少具有读取权限,并且有效期应有效(所有日期均采用 UTC 时间)。 有关共享访问签名的详细信息,请参阅使用共享访问签名 (SAS)。
CREATE DATABASE SCOPED CREDENTIAL AccessAzureInvoices
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
-- Remove ? from the beginning of the SAS token
SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';
CREATE EXTERNAL DATA SOURCE MyAzureInvoices
WITH (
LOCATION = 'https://newinvoices.blob.core.windows.net/week3',
CREDENTIAL = AccessAzureInvoices,
TYPE = BLOB_STORAGE
);
若要查看这一使用中的示例,请参阅 BULK INSERT。
示例:Azure SQL Edge
重要
若要了解如何为 Azure SQL Edge 配置外部数据,请参阅 Azure SQL Edge 中的数据流式处理。
A. 创建外部数据源以引用 Kafka
适用范围:仅限 Azure SQL Edge
在本示例中,外部数据源是 IP 地址为 xxx.xxx.xxx.xxx 且在端口 1900 上进行侦听的 Kafka 服务器。 Kafka 外部数据源仅用于数据流式传输,不支持谓词下推。
-- Create an External Data Source for Kafka
CREATE EXTERNAL DATA SOURCE MyKafkaServer
WITH (LOCATION = 'kafka://xxx.xxx.xxx.xxx:1900');
B. 创建外部数据源以引用 EdgeHub
适用范围:仅限 Azure SQL Edge
在本示例中,外部数据源是在与 Azure SQL Edge 相同的边缘设备上运行的 EdgeHub。 EdgeHub 外部数据源仅用于数据流式传输,不支持谓词向下推送。
-- Create an External Data Source for Kafka
CREATE EXTERNAL DATA SOURCE MyEdgeHub
WITH (LOCATION = 'edgehub://');
后续步骤
* Azure Synapse
Analytics *
概述:Azure Synapse Analytics
为数据虚拟化创建外部数据源。 外部数据源用于建立连接并支持从外部数据源虚拟化数据和加载数据的主要用例。 有关详细信息,请参阅通过 Synapse SQL 使用外部表。
语法
有关语法约定的详细信息,请参阅 Transact-SQL 语法约定。
CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
( [ LOCATION = '<prefix>://<path>[:<port>]' ]
[ [ , ] CREDENTIAL = <credential_name> ]
[ [ , ] TYPE = HADOOP ]
)
[ ; ]
参数
data_source_name
指定数据源的用户定义名称。 该名称在 Azure Synapse Analytics 的 Azure SQL 数据库中必须是唯一的。
LOCATION = '<prefix>://<path>'
提供连接协议和外部数据源的路径。
外部数据源 | 连接器位置前缀 | 位置路径 |
---|---|---|
Data Lake Storage* Gen1 | adl |
<storage_account>.azuredatalake.net |
Data Lake Storage Gen2 | abfs[s] |
<container>@<storage_account>.dfs.core.windows.net |
Azure Blob 存储 | wasbs |
<container>@<storage_account>.blob.core.windows.net |
Azure Blob 存储 | https |
<storage_account>.blob.core.windows.net/<container>/subfolders |
Data Lake Storage Gen1 | http[s] |
<storage_account>.azuredatalakestore.net/webhdfs/v1 |
Data Lake Storage Gen2 | http[s] |
<storage_account>.dfs.core.windows.net/<container>/subfolders |
Data Lake Storage Gen2 | wasb[s] |
<container>@<storage_account>.blob.core.windows.net |
* Microsoft Azure Data Lake Storage Gen1 提供有限的支持,建议将 Gen2 用于所有新开发。
外部数据源 | 连接器位置前缀 | 专用 SQL 池:PolyBase | 专用 SQL 池:本机* | 无服务器 SQL 池 |
---|---|---|---|---|
Data Lake Storage** Gen1 | adl |
否 | No | 是 |
Data Lake Storage Gen2 | abfs[s] |
是 | 是 | 是 |
Azure Blob 存储 | wasbs |
是 | 是*** | 是 |
Azure Blob Storage | https |
否 | 是 | 是 |
Data Lake Storage Gen1 | http[s] |
否 | No | 是 |
Data Lake Storage Gen2 | http[s] |
是 | 是 | 是 |
Data Lake Storage Gen2 | wasb[s] |
是 | 是 | 是 |
* Azure Synapse Analytics 中的无服务器和专用 SQL 池使用不同的代码库执行数据虚拟化。 无服务器 SQL 池支持本机数据虚拟化技术。 专用 SQL 池支持本机和 PolyBase 数据虚拟化。 使用 TYPE=HADOOP
创建 EXTERNAL DATA SOURCE 时,将使用 PolyBase 数据虚拟化。
** Microsoft Azure Data Lake Storage Gen1 提供有限的支持,建议将 Gen2 用于所有新开发。
*** 建议使用更安全的 wasbs
连接器,而不是 wasb
。 只有专用 SQL 池中的本机数据虚拟化(其中 TYPE 不等于 HADOOP)支持wasb
。
位置路径:
<container>
= 保存数据的存储帐户的容器。 根容器是只读的,数据无法写回容器。<storage_account>
= Azure 资源的存储帐户名称。
设置位置时的其他说明和指南:
- 默认选项是在预配 Azure Data Lake Storage Gen2 时使用
enable secure SSL connections
。 如果此选项已启用,必须在选择安全 TSL/SSL 连接时使用abfss
。 请注意,abfss
也适用于不安全的 TLS 连接。 有关详细信息,请参阅 Azure Blob 文件系统驱动程序 (ABFS)。 - 创建对象时,Azure Synapse 不会验证外部数据源是否存在。 要进行验证,请使用外部数据源创建外部表。
- 查询 Hadoop 时,所有表使用相同的外部数据源,以确保查询语义一致。
https:
前缀允许使用路径中的子文件夹。https
并非适用于所有数据访问方法。- 建议使用
wasbs
,因为将使用安全的 TLS 连接发送数据。 - 使用旧的
wasb://
接口访问数据时,Azure V2 存储帐户不支持分层命名空间,但使用wasbs://
支持分层命名空间。
CREDENTIAL = credential_name
可选。 指定用于向外部数据源进行身份验证的数据库范围凭据。 没有凭据的外部数据源可以访问公共存储帐户,或使用调用方Microsoft Entra 标识访问 Azure 存储上的文件。
创建凭证时的其他说明和指导:
- 若要从 Azure 存储或 Azure Data Lake Store (ADLS) Gen2 将数据加载到 Azure Synapse Analytics,请使用 Azure 存储密钥。
- 只有在数据得到保护的情况下才需要
CREDENTIAL
。 允许匿名访问的数据集不需要CREDENTIAL
。
要创建数据库范围凭据,请参阅 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)。
在无服务器 SQL 池中,数据库范围的凭据可以指定工作区托管标识、服务主体名称或共享访问签名(SAS)令牌。 也可在数据库范围的凭据中通过用户标识(也称为 Microsoft Entra 传递)进行访问,就像匿名访问公开可用的存储一样。 有关详细信息,请参阅支持的存储授权类型。
在专用 SQL 池中,数据库范围的凭据可以指定共享访问签名(SAS)令牌、存储访问密钥、服务主体、工作区托管标识或Microsoft Entra 直通。
TYPE = HADOOP
可选但不建议。
只能使用专用 SQL 池指定 TYPE。 HADOOP
是指定时唯一允许的值。 使用 TYPE=HADOOP
的外部数据源仅在专用 SQL 池中可用。
将 HADOOP 用于旧的实现,否则建议使用较新的本机数据访问。 不要指定该 TYPE 参数来使用较新的本机数据访问。
有关使用 TYPE = HADOOP
从 Azure 存储加载数据的示例,请参阅创建外部数据源以使用服务主体引用 Azure Data Lake Store Gen 1 或 Azure Data Lake Store Gen 2。
Azure Synapse Analytics 中的无服务器和专用 SQL 池使用不同的代码库执行数据虚拟化。 无服务器 SQL 池支持本机数据虚拟化技术。 专用 SQL 池支持本机和 PolyBase 数据虚拟化。 使用 TYPE=HADOOP
创建 EXTERNAL DATA SOURCE 时,将使用 PolyBase 数据虚拟化。
权限
需要对数据库拥有 CONTROL
权限。
锁定
对 EXTERNAL DATA SOURCE
对象采用共享锁。
安全性
大多数外部数据源支持使用数据库范围的凭据创建代理帐户,从而执行基于代理的身份验证。
共享访问签名 (SAS) 密钥支持对 Azure Data Lake Store Gen 2 存储帐户进行身份验证。 想要使用共享访问签名进行身份验证的客户必须在 IDENTITY = "Shared Access Signature"
时创建数据库范围的凭据并输入 SAS 令牌作为机密。
如果在 IDENTITY = "Shared Access Signature"
时创建数据库范围的凭据并将存储密钥值用作机密,则会收到以下错误消息:
'HdfsBridge::isDirExist - Unexpected error encountered checking whether directory exists or not: AbfsRestOperationException: Operation failed: "Server failed to authenticate the request. Please refer to the information in the www-authenticate header.", 401, HEAD, [Storage path URL]'
示例
A. 创建外部数据源以使用 wasb:// 接口访问 Azure 存储中的数据
在本示例中,外部数据源是名为 logs
的 Azure 存储帐户 V2。 存储容器被称为 daily
。 Azure 存储外部数据源仅用于数据传输。 它不支持谓词下推。 通过 wasb://
接口访问数据时,不支持分层命名空间。 请注意,通过 WASB[s] 连接器连接到 Azure 存储时,必须使用存储帐户密钥(而不是共享访问签名 (SAS))进行身份验证。
此示例使用基于 Java 的旧 HADOOP 访问方法。 下面的示例演示如何创建数据库范围凭据以用于对 Azure 存储进行身份验证。 在数据库凭据机密中指定 Azure 存储帐户密钥。 可以在数据库范围凭据标识中指定任何字符串,因为在对 Azure 存储进行身份验证的过程中不会使用它。
-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY = '<my_account>',
SECRET = '<azure_storage_account_key>';
-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
CREDENTIAL = AzureStorageCredential,
TYPE = HADOOP
);
B. 创建外部数据源以使用服务主体引用 Azure Data Lake Store Gen 1 或 Azure Data Lake Store Gen 2
Azure Data Lake Store 连接可以基于 ADLS URI 和 Microsoft Entra 应用程序的服务主体。 有关创建此应用程序的文档,可以使用 Microsoft Entra ID 在 Data Lake Store 身份验证中找到。
-- If you do not have a Master Key on your DW you will need to create one.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
-- These values come from your Microsoft Entra application used to authenticate to ADLS
CREATE DATABASE SCOPED CREDENTIAL ADLS_credential
WITH
-- IDENTITY = '<clientID>@<OAuth2.0TokenEndPoint>' ,
IDENTITY = '536540b4-4239-45fe-b9a3-629f97591c0c@https://login.microsoftonline.com/42f988bf-85f1-41af-91ab-2d2cd011da47/oauth2/token',
-- SECRET = '<KEY>'
SECRET = 'BjdIlmtKp4Fpyh9hIvr8HJlUida/seM5kQ3EpLAmeDI=';
-- For Gen 1 - Create an external data source
-- TYPE: HADOOP - PolyBase uses Hadoop APIs to access data in Azure Data Lake Storage.
-- LOCATION: Provide Data Lake Storage Gen 1 account name and URI
-- CREDENTIAL: Provide the credential created in the previous step
CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH (
LOCATION = 'adl://newyorktaxidataset.azuredatalakestore.net',
CREDENTIAL = ADLS_credential,
TYPE = HADOOP
);
-- For Gen2 - Create an external data source
-- TYPE: HADOOP - PolyBase uses Hadoop APIs to access data in Azure Data Lake Storage.
-- LOCATION: Provide Data Lake Storage Gen2 account name and URI
-- CREDENTIAL: Provide the credential created in the previous step
CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH (
-- Note the abfss endpoint when your account has secure transfer enabled
LOCATION = 'abfss://data@newyorktaxidataset.dfs.core.windows.net',
CREDENTIAL = ADLS_credential,
TYPE = HADOOP
);
°C 创建外部数据源,以使用存储帐户密钥引用 Azure Data Lake Store Gen2
-- If you do not have a Master Key on your DW you will need to create one.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
CREATE DATABASE SCOPED CREDENTIAL ADLS_credential
WITH
-- IDENTITY = '<storage_account_name>' ,
IDENTITY = 'newyorktaxidata',
-- SECRET = '<storage_account_key>'
SECRET = 'yz5N4+bxSb89McdiysJAzo+9hgEHcJRJuXbF/uC3mhbezES/oe00vXnZEl14U0lN3vxrFKsphKov16C0w6aiTQ==';
-- Note this example uses a Gen2 secured endpoint (abfss)
CREATE EXTERNAL DATA SOURCE < data_source_name >
WITH (
LOCATION = 'abfss://2013@newyorktaxidataset.dfs.core.windows.net',
CREDENTIAL = ADLS_credential,
TYPE = HADOOP
);
D. 使用 abfs:// 创建 Azure Data Lake Store Gen2 的外部数据源
连接到具有托管标识机制的 Azure Data Lake Store Gen2 帐户时,无需指定密码。
-- If you do not have a Master Key on your DW you will need to create one
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
--Create database scoped credential with **IDENTITY = 'Managed Service Identity'**
CREATE DATABASE SCOPED CREDENTIAL msi_cred
WITH IDENTITY = 'Managed Service Identity';
--Create external data source with abfss:// scheme for connecting to your Azure Data Lake Store Gen2 account
CREATE EXTERNAL DATA SOURCE ext_datasource_with_abfss
WITH (
TYPE = HADOOP,
LOCATION = 'abfss://myfile@mystorageaccount.dfs.core.windows.net',
CREDENTIAL = msi_cred
);
后续步骤
- CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)
- CREATE EXTERNAL FILE FORMAT (Transact-SQL)
- CREATE EXTERNAL TABLE (Transact-SQL)
- CREATE EXTERNAL TABLE AS SELECT (Azure Synapse Analytics)
- CREATE EXTERNAL TABLE AS SELECT (Azure Synapse Analytics)
- sys.external_data_sources (Transact-SQL)
- 使用共享访问签名 (SAS)
* Analytics
Platform System (PDW) *
概述:分析平台系统
适用于:分析平台系统(PDW)
为 PolyBase 查询创建外部数据源。 外部数据源用于建立连接并支持以下用例:使用 PolyBase 执行数据虚拟化和数据加载。
语法
有关语法约定的详细信息,请参阅 Transact-SQL 语法约定。
CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
( [ LOCATION = '<prefix>://<path>[:<port>]' ]
[ [ , ] CREDENTIAL = <credential_name> ]
[ [ , ] TYPE = HADOOP ]
[ [ , ] RESOURCE_MANAGER_LOCATION = '<resource_manager>[:<port>]' )
[ ; ]
参数
data_source_name
指定数据源的用户定义名称。 该名称在 Analytics Platform System (PDW) 中的服务器上必须是唯一的。
LOCATION = '<prefix>://<path[:port]>'
提供连接协议和外部数据源的路径。
外部数据源 | 连接器位置前缀 | 位置路径 |
---|---|---|
Cloudera CDH 或 Hortonworks HDP | hdfs |
<Namenode>[:port] |
Azure 存储帐户 | wasb[s] |
<container>@<storage_account>.blob.core.windows.net |
位置路径:
<Namenode>
= Hadoop 群集中Namenode
的计算机名称、名称服务 URI 或 IP 地址。 PolyBase 必须解析 Hadoop 群集使用的任何 DNS 名称。port
= 外部数据源侦听的端口。 在 Hadoop 中,可以使用fs.defaultFS
配置参数查找该端口。 默认值为 8020。<container>
= 保存数据的存储帐户的容器。 根容器是只读的,数据无法写回容器。<storage_account>
= Azure 资源的存储帐户名称。
设置位置时的其他说明和指南:
- 创建对象时,PDW 引擎不会验证外部数据源是否存在。 要进行验证,请使用外部数据源创建外部表。
- 查询 Hadoop 时,所有表使用相同的外部数据源,以确保查询语义一致。
- 建议使用
wasbs
,因为将使用安全的 TLS 连接发送数据。 - 通过 wasb:// 与 Azure 存储帐户配合使用时,不支持分层命名空间。
- 要确保在 Hadoop
Namenode
故障转移期间成功进行 PolyBase 查询,请考虑针对 Hadoop 群集的Namenode
使用虚拟 IP 地址。 如果不这样做,请执行 ALTER EXTERNAL DATA SOURCE 命令以指向新位置。
CREDENTIAL = credential_name
指定用于对外部数据源进行身份验证的数据库范围凭据。
创建凭证时的其他说明和指导:
- 若要从 Azure 存储将数据加载到 Azure Synapse 或 PDW,请使用 Azure 存储密钥。
- 只有在数据得到保护的情况下才需要
CREDENTIAL
。 允许匿名访问的数据集不需要CREDENTIAL
。
TYPE = [ HADOOP ]
指定要配置的外部数据源的类型。 此参数并非总是必需的。
- 当外部数据源是 Cloudera CDH、Hortonworks HDP 或 Azure 存储时,使用 HADOOP。
有关使用 TYPE
= HADOOP
从 Azure 存储加载数据的示例,请参阅创建外部数据源以引用 Hadoop。
RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:port]'
在 SQL Server 2019 (15.x) 中,请勿指定 RESOURCE_MANAGER_LOCATION,除非连接到 Cloudera CDH、Hortonworks HDP 或 Azure 存储帐户。
仅当连接到 Cloudera CDH、Hortonworks HDP 或 Azure 存储帐户时,才配置此可选值。 有关受支持的 Hadoop 版本的完整列表,请参阅 PolyBase 连接配置 (Transact-SQL)。
定义 RESOURCE_MANAGER_LOCATION
后,查询优化器做出基于成本的决策,以提高性能。 MapReduce 作业可用于将计算下推到 Hadoop。 指定 RESOURCE_MANAGER_LOCATION
可以显着减少 Hadoop 和 SQL 之间传输的数据量,从而提高查询性能。
如果未指定资源管理器,则会为 PolyBase 查询禁用到 Hadoop 的计算下推。 创建外部数据源以引用启用了下推功能的 Hadoop 中提供了具体示例和详细指南。
创建外部数据源时,不会验证 RESOURCE_MANAGER_LOCATION 值。 每次尝试下推时,输入不正确的值都可能会导致查询执行失败,因为提供的值无法解析。
为了使 PolyBase 能够正常访问 Hadoop 外部数据源,以下 Hadoop 群集组件的端口必须处于打开状态:
- HDFS 端口
- Namenode
- DataNode
- 资源管理器
- 作业提交
- 作业历史记录
如果未指定端口,则使用“hadoop 连接”配置的当前设置选择默认值。
Hadoop 连接 | 默认资源管理器端口 |
---|---|
1 | 50300 |
2 | 50300 |
3 | 8021 |
4 | 8032 |
5 | 8050 |
6 | 8032 |
7 | 8050 |
下表显示了这些组件的默认端口。 请注意,存在 Hadoop 版本依赖项以及不使用默认端口分配的自定义配置的可能性。
Hadoop 群集组件 | “默认端口” |
---|---|
NameNode | 8020 |
DataNode(数据传输、非特权 IPC 端口) | 50010 |
DataNode(数据传输、特权 IPC 端口) | 1019 |
资源管理器作业提交 (Hortonworks 1.3) | 50300 |
资源管理器作业提交 (Cloudera 4.3) | 8021 |
资源管理器作业提交(Windows 上的 Hortonworks 2.0,Linux 上的 Cloudera 5.x) | 8032 |
资源管理器作业提交(Linux 上的 Hortonworks 2.x、3.0,Windows 上的 Hortonworks 2.1-3) | 8050 |
资源管理器作业历史记录 | 10020 |
权限
需要对 Analytics Platform System (PDW) 中数据库的 CONTROL
权限。
注意
在以前版本的 PDW 中,创建外部数据源需要 ALTER ANY EXTERNAL DATA SOURCE
权限。
锁定
对 EXTERNAL DATA SOURCE
对象采用共享锁。
安全性
PolyBase 支持大多数外部数据源的基于代理的身份验证。 创建数据库范围凭据以创建代理帐户。
不支持类型为 HADOOP
的 SAS 令牌。 仅在使用存储帐户访问密钥时,才支持类型为 BLOB_STORAGE
的 SAS 令牌。 尝试创建类型为 HADOOP
的外部数据源和使用 SAS 凭据失败,并显示以下错误:
Msg 105019, Level 16, State 1 - EXTERNAL TABLE access failed due to internal error: 'Java exception raised on call to HdfsBridge_Connect. Java exception message: Parameters provided to connect to the Azure storage account are not valid.: Error [Parameters provided to connect to the Azure storage account are not valid.] occurred while accessing external file.'
示例
A. 创建外部数据源以引用 Hadoop
若要创建外部数据源来引用 Hortonworks HDP 或 Cloudera CDH,请指定 Hadoop Namenode
的计算机名称/IP 地址和端口。
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
LOCATION = 'hdfs://10.10.10.10:8050',
TYPE = HADOOP
);
B. 创建外部数据源以引用 Hadoop 并启用下推
指定 RESOURCE_MANAGER_LOCATION
选项以便为 PolyBase 查询启用到 Hadoop 的下推计算。 启用后,PolyBase 会根据成本作出决策,以确定是否应将查询计算下推到 Hadoop。
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
LOCATION = 'hdfs://10.10.10.10:8020',
TYPE = HADOOP,
RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);
C. 创建外部数据源以引用受 Kerberos 保护的 Hadoop
若要验证 Hadoop 群集是否受 Kerberos 保护,请检查 Hadoop core-site.xml 中的 hadoop.security.authentication
属性值。 若要引用受 Kerberos 保护的 Hadoop 群集,必须指定包含 Kerberos 用户名和密码的数据库范围凭据。 数据库主密钥用于加密数据库范围凭据密钥。
-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
-- Create a database scoped credential with Kerberos user name and password.
CREATE DATABASE SCOPED CREDENTIAL HadoopUser1
WITH IDENTITY = '<hadoop_user_name>',
SECRET = '<hadoop_password>';
-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
LOCATION = 'hdfs://10.10.10.10:8050',
CREDENTIAL = HadoopUser1,
TYPE = HADOOP,
RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);
D. 创建外部数据源以使用 wasb:// 接口访问 Azure 存储中的数据
在本示例中,外部数据源是名为 logs
的 Azure V2 存储帐户。 存储容器被称为 daily
。 Azure 存储外部数据源仅用于数据传输。 它不支持谓词下推。 通过 wasb://
接口访问数据时,不支持分层命名空间。 请注意,通过 WASB[s] 连接器连接到 Azure 存储时,必须使用存储帐户密钥(而不是共享访问签名 (SAS))进行身份验证。
此示例演示如何创建数据库范围凭据以用于对 Azure 存储进行身份验证。 在数据库凭据机密中指定 Azure 存储帐户密钥。 可以在数据库范围凭据标识中指定任何字符串,因为在对 Azure 存储进行身份验证的过程中不会使用它。
-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY = '<my_account>',
SECRET = '<azure_storage_account_key>';
-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
CREDENTIAL = AzureStorageCredential,
TYPE = HADOOP
);
后续步骤
* SQL 托管实例 *
概述:Azure SQL 托管实例
适用于:Azure SQL 托管实例
在 Azure SQL 托管实例中创建外部数据源。 有关完整信息,请参阅 Azure SQL 托管实例的数据虚拟化。
Azure SQL 托管实例的数据虚拟化通过 OPENROWSET T-SQL 语法或 CREATE EXTERNAL TABLE T-SQL 语法提供对各种文件格式的外部数据的访问。
语法
有关语法约定的详细信息,请参阅 Transact-SQL 语法约定。
CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
( [ LOCATION = '<prefix>://<path>[:<port>]' ]
[ [ , ] CREDENTIAL = <credential_name> ]
)
[ ; ]
参数
data_source_name
指定数据源的用户定义名称。 该名称在数据库中必须唯一。
LOCATION = '<prefix>://<path[:port]>'
提供连接协议和外部数据源的路径。
外部数据源 | 位置前缀 | 位置路径 |
---|---|---|
Azure Blob 存储 | abs |
abs://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name> |
Azure Data Lake Service Gen2 | adls |
adls://<container>@<storage_account>.dfs.core.windows.net/<path>/<file_name> |
创建对象时,数据库引擎不会验证外部数据源是否存在。 要进行验证,请使用外部数据源创建外部表。
为批量操作配置外部数据源时,请勿在 LOCATION
URL 的末尾放置尾随 /、文件名或共享访问签名参数。
CREDENTIAL = credential_name
指定用于对外部数据源进行身份验证的数据库范围凭据。
创建凭证时的其他说明和指导:
- 若要将数据从 Azure 存储加载到 Azure SQL 托管实例,请使用共享访问签名(SAS 令牌)。
- 只有在数据得到保护的情况下才需要
CREDENTIAL
。 允许匿名访问的数据集不需要CREDENTIAL
。 - 如果需要凭据,则必须使用
Managed Identity
或SHARED ACCESS SIGNATURE
作为 IDENTITY 创建凭据。 要创建数据库范围凭据,请参阅 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)。
对数据库范围的凭据使用托管服务标识:
指定
WITH IDENTITY = 'Managed Identity'
- 使用 Azure SQL 托管实例的系统分配的托管服务标识(如果用于实现此目的,则必须启用该标识)。
将访问必要 Azure Blob 存储容器所需的 Azure RBAC 角色“读者”授予 Azure SQL 托管实例的系统分配的托管服务标识。 例如,若要通过 Azure 门户操作,请参阅使用 Azure 门户分配 Azure 角色。
为数据库范围的凭据创建共享访问签名 (SAS):
指定
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = ...
创建共享访问签名的方式有很多种:
- 可以导航到“Azure 门户”->“<Your_Storage_Account>”->“共享访问签名”->“配置权限”->“生成 SAS 和连接字符串”来获取 SAS 令牌。 有关详细信息,请参阅生成共享访问签名。
- 可使用 Azure 存储资源管理器创建和配置 SAS。
- 可以通过 PowerShell、Azure CLI、.NET 和 REST API 以编程方式创建 SAS。 有关详细信息,请参阅使用共享访问签名 (SAS) 授予对 Azure 存储资源的有限访问权限。
应按如下所示配置 SAS 令牌:
- 生成某个 SAS 令牌时,其开头会包含问号(“?”)。 配置为密码时排除前导
?
。 - 使用有效的有效期(所有日期均采用 UTC 时间)。
- 至少授予对应加载的文件的读取权限(例如
srt=o&sp=r
)。 可以为不同的用例创建多个共享访问签名。 应按如下所示授予权限:
操作 权限 从文件中读取数据 读取 从多个文件和子文件夹读取数据 读取和列出 使用 Create External Table as Select (CETAS) 读取、创建和写入 - 生成某个 SAS 令牌时,其开头会包含问号(“?”)。 配置为密码时排除前导
权限
需要对 Azure SQL 托管实例中数据库的 CONTROL
权限。
锁定
对 EXTERNAL DATA SOURCE
对象采用共享锁。
示例
有关更多示例,请参阅 Azure SQL 托管实例的数据虚拟化。
A. 使用 OPENROWSET 或外部表从 Azure SQL 托管实例查询外部数据
有关更多示例,请参阅创建外部数据源或 Azure SQL 托管实例的数据虚拟化。
创建数据库主密钥(如果不存在)。
-- Optional: Create MASTER KEY if it doesn't exist in the database: CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong Password>' GO
使用 SAS 令牌创建数据库范围的凭据。 还可使用托管标识。
CREATE DATABASE SCOPED CREDENTIAL MyCredential WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = '<KEY>' ; --Removing leading '?' GO
使用凭据创建外部数据源。
--Create external data source pointing to the file path, and referencing database-scoped credential: CREATE EXTERNAL DATA SOURCE MyPrivateExternalDataSource WITH ( LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest', CREDENTIAL = [MyCredential] );
使用 OPENROWSET T-SQL 语法查询外部数据源中的 parquet 数据文件,依靠架构推理快速浏览数据,而无需了解该架构。
--Query data with OPENROWSET, relying on schema inference. SELECT TOP 10 * FROM OPENROWSET ( BULK 'bing_covid-19_data.parquet', DATA_SOURCE = 'MyExternalDataSource', FORMAT = 'parquet' ) AS filerows;
或者,使用 OPENROWSET WITH 子句查询数据,而不是依赖于架构推理,这可能会产生查询执行成本。 在 CSV 中不支持架构推理。
--Or, query data using the WITH clause on a CSV, where schema inference is not supported SELECT TOP 10 id, updated, confirmed, confirmed_change FROM OPENROWSET ( BULK 'bing_covid-19_data.csv', DATA_SOURCE = 'MyExternalDataSource', FORMAT = 'CSV', FIRSTROW = 2 ) WITH ( id INT, updated DATE, confirmed INT, confirmed_change INT ) AS filerows;
或者,创建 EXTERNAL FILE FORMAT 和 EXTERNAL TABLE,以本地表的形式查询数据。
-- Or, create an EXTERNAL FILE FORMAT and an EXTERNAL TABLE --Create external file format CREATE EXTERNAL FILE FORMAT DemoFileFormat WITH (FORMAT_TYPE = PARQUET) GO --Create external table: CREATE EXTERNAL TABLE tbl_TaxiRides ( vendorID VARCHAR(100) COLLATE Latin1_General_BIN2, tpepPickupDateTime DATETIME2, tpepDropoffDateTime DATETIME2, passengerCount INT, tripDistance FLOAT, puLocationId VARCHAR(8000), doLocationId VARCHAR(8000), startLon FLOAT, startLat FLOAT, endLon FLOAT, endLat FLOAT, rateCodeId SMALLINT, storeAndFwdFlag VARCHAR(8000), paymentType VARCHAR(8000), fareAmount FLOAT, extra FLOAT, mtaTax FLOAT, improvementSurcharge VARCHAR(8000), tipAmount FLOAT, tollsAmount FLOAT, totalAmount FLOAT ) WITH ( LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet', DATA_SOURCE = NYCTaxiExternalDataSource, FILE_FORMAT = MyFileFormat\.\./\.\./\.\./azure-sql/ ); GO --Then, query the data via an external table with T-SQL: SELECT TOP 10 * FROM tbl_TaxiRides; GO