CREATE EXTERNAL DATA SOURCE (Transact-SQL)

使用 SQL Server、Azure SQL Database、Azure SQL 受控執行個體、Azure Synapse Analytics、Analytics Platform System (PDW) 或 Azure SQL Edge,建立用於查詢的外部資料源。

本文提供適用於您所選擇之 SQL 產品的語法、引數、備註、權限和範例。

選取產品

在下列資料列中,選取您感興趣的產品名稱,只顯示該產品的資訊。

概觀:SQL Server 2016

適用于:SQL Server 2016 (13.x) 及更新版本

建立 PolyBase 查詢的外部資料來源。 外部資料來源會用來建立連線能力,並支援這些主要使用案例:

  • 使用 PolyBase 來執行資料虛擬化和資料載入
  • 使用 BULK INSERTOPENROWSET 的大量載入作業

注意

此語法會因不同版本的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) 至 201 (9 SQL Server 15.x) 匿名或基本驗證
Azure 儲存體帳戶 (V2) wasb[s] <container>@<storage_account>.blob.core.windows.net 從 SQL Server 2016 (13.x) 開始
不支援階層命名空間
Azure 儲存體帳戶金鑰

位置路徑:

  • <Namenode> = Hadoop 叢集中電腦名稱、名稱服務 URI 或 Namenode 的 IP 位址。 PolyBase 必須解析 Hadoop 叢集所使用的任何 DNS 名稱。
  • port = 外部資料來源正在接聽的連接埠。 在 Hadoop 中,此連接埠可使用 fs.defaultFS 設定參數來尋找。 預設值為 8020。
  • <container> = 保留資料的儲存體帳戶容器。 根容器是唯讀的,因此無法將資料寫回至容器。
  • <storage_account> = Azure 資源的儲存體帳戶名稱。
  • <server_name> = 主機名稱。
  • <instance_name> = SQL Server 具名執行個體的名稱。 在目標執行個體上執行 SQL Server Browser 服務時使用。

設定位置時的其他注意事項和指引:

  • 建立物件時,SQL Server Database Engine 不會驗證外部資料源是否存在。 若要驗證,請使用外部資料來源建立外部資料表。
  • 查詢 Hadoop 時,請針對所有資料表使用相同的外部資料來源,以確保查詢語意一致。
  • wasbs是選擇性的,但建議在 SQL Server 2016 (13.x) 中存取 Azure 儲存體帳戶,因為資料將會使用安全的 TLS/SSL 連線傳送。
  • 為確保在 Hadoop Namenode 容錯移轉期間能成功進行 PolyBase 查詢,請考慮使用虛擬 IP 位址作為 Hadoop 叢集的 Namenode。 若未這樣做,請執行 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 之間傳輸的資料量,這可能會導致查詢效能改善。

若未指定 Resource Manager,系統會針對 PolyBase 查詢停用將計算推送到 Hadoop。 在已啟用下推的情況下,建立參考 Hadoop 的外部資料來源提供具體範例及進一步指引。

當您建立外部資料來源時,不會驗證 RESOURCE_MANAGER_LOCATION 值。 輸入不正確的值可能會導致每次嘗試下推就在執行時間發生查詢失敗,因為無法解析所提供的值。

為了讓 PolyBase 能夠正確運作 Hadoop 外部資料源,必須開啟下列 Hadoop 叢集元件的埠:

  • HDFS 連接埠
    • Namenode
    • Datanode
  • Resource Manager
    • 作業提交
    • 作業歷程記錄

若未指定連接埠,系統會使用 'hadoop connectivity' 設定的目前設定選擇預設值。

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 (Hortonworks 1.3) Resource Manager作業提交 50300
cloudera 4.3 (Resource Manager作業提交) 8021
Resource Manager (Linux 上的 Hortonworks 2.0、Linux 上的 Cloudera 5.x) 8032
Resource Manager工作提交 (Linux 上的 Hortonworks 2.x、3.0、Windows) 上的 Hortonworks 2.1-3 8050
Resource Manager作業歷程記錄 10020

權限

CONTROL需要 SQL Server 中資料庫的許可權。

鎖定

EXTERNAL DATA SOURCE 物件採取共用鎖定。

安全性

PolyBase 支援大多數外部資料來源的 Proxy 驗證。 建立資料庫範圍認證以建立 Proxy 帳戶。

範例

重要

如需如何安裝及啟用 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.security.authentication Hadoop 中的 屬性值core-site.xml。 若要參考由 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) 中, TYPE 即使存取 Azure 儲存體,也應該設定為 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 INSERTOPENROWSET 的大量載入作業

注意

此語法會因不同版本的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 Database Engine 不會驗證外部資料源是否存在。 若要驗證,請使用外部資料來源建立外部資料表。
  • 查詢 Hadoop 時,請針對所有資料表使用相同的外部資料來源,以確保查詢語意一致。
  • 透過 ODBC 連線時,請指定 Driver={<Name of Driver>}
  • wasbs是選擇性的,但建議在 SQL Server 2017 (14.x) 中存取 Azure 儲存體帳戶,因為資料將會使用安全的 TLS/SSL 連線來傳送。
  • 為確保在 Hadoop Namenode 容錯移轉期間能成功進行 PolyBase 查詢,請考慮使用虛擬 IP 位址作為 Hadoop 叢集的 Namenode。 若未這樣做,請執行 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 權杖。 如需詳細資訊,請參閱 產生共用存取簽章

  • 您可以使用Azure 儲存體總管 來建立和設定 SAS

  • 您可以透過 PowerShell、Azure CLI、.NET 和 REST API,以程式設計方式建立 SAS。 如需詳細資訊,請參閱使用共用存取簽章 (SAS) 對 Azure 儲存體資源授與有限存取權

  • SAS 權杖應設定如下:

    • 產生 SAS 權杖時,權杖的開頭會包含問號 ('?')。 在設定為 SECRET 時排除前置 ? 詞。
    • 使用有效的到期時間 (所有日期都是 UTC 時間)。
    • 至少授與應該載入 (檔案的讀取權限,例如 srt=o&sp=r) 。 您可以針對不同的使用案例建立多個共用存取簽章。 應該授與許可權,如下所示:
    動作 權限
    從檔案讀取資料 讀取
    從多個檔案和子資料夾讀取資料 讀取和列出
    使用 [建立外部資料表] 作為 [選取] (CETAS) 讀取、建立和寫入

如需搭配 SHARED ACCESS SIGNATUREBLOB_STORAGETYPE = 使用 CREDENTIAL 的範例,請參閱建立外部資料源來執行大量作業,並將資料從 Azure 儲存體擷取到SQL Database

若要建立資料庫範圍認證,請參閱 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)

TYPE = [ HADOOP | BLOB_STORAGE ]

指定要設定的外部資料來源類型。 此參數不一定是必要參數,只有在連線到 Cloudera CDH、Hortonworks HDP、Azure 儲存體帳戶或Azure Data Lake Storage Gen2時才指定。

  • HADOOP當外部資料源為 Cloudera CDH、Hortonworks HDP、Azure 儲存體帳戶或Azure Data Lake Storage Gen2時使用。
  • BLOB_STORAGE使用BULK INSERTOPENROWSET從 Azure 儲存體帳戶執行大量作業時使用 。 SQL Server 2017 (14.x) 引進。 當想要針對 Azure 儲存體建立 EXTERNAL TABLE 時,請使用 HADOOP

注意

TYPE 即使存取 Azure 儲存體,也應該設定為 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 之間傳輸的資料量,這可能會導致查詢效能改善。

若未指定 Resource Manager,系統會針對 PolyBase 查詢停用將計算推送到 Hadoop。 在已啟用下推的情況下,建立參考 Hadoop 的外部資料來源提供具體範例及進一步指引。

當您建立外部資料來源時,不會驗證 RESOURCE_MANAGER_LOCATION 值。 輸入不正確的值可能會導致每次嘗試下推就在執行時間發生查詢失敗,因為無法解析所提供的值。

為了讓 PolyBase 能夠正確運作 Hadoop 外部資料源,必須開啟下列 Hadoop 叢集元件的埠:

  • HDFS 連接埠
    • Namenode
    • Datanode
  • Resource Manager
    • 作業提交
    • 作業歷程記錄

若未指定連接埠,系統會使用 'hadoop connectivity' 設定的目前設定選擇預設值。

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 (Hortonworks 1.3) Resource Manager作業提交 50300
cloudera 4.3 (Resource Manager作業提交) 8021
Resource Manager (Linux 上的 Hortonworks 2.0、Linux 上的 Cloudera 5.x) 8032
Resource Manager工作提交 (Linux 上的 Hortonworks 2.x、3.0、Windows) 上的 Hortonworks 2.1-3 8050
Resource Manager作業歷程記錄 10020

權限

CONTROL需要 SQL Server 中資料庫的許可權。

鎖定

EXTERNAL DATA SOURCE 物件採取共用鎖定。

安全性

PolyBase 支援大多數外部資料來源的 Proxy 驗證。 建立資料庫範圍認證以建立 Proxy 帳戶。

不支援類型 HADOOP 為的 SAS 權杖。 只有在改為使用儲存體帳戶存取金鑰時,才支援類型 = BLOB_STORAGE 。 嘗試使用 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.security.authentication Hadoop 中的 屬性值core-site.xml。 若要參考由 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 INSERTOPENROWSET 的大量作業,請使用下列資料來源。 認證必須將 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 INSERTOPENROWSET 的大量載入作業

注意

此語法會因不同版本的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) 至 201 (9 SQL Server 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 或適用于 MongoDB 的 Cosmos DB API 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+ 開始。 Storage Access Key (儲存體存取金鑰)
SQL Server 巨量資料叢集資料集區 sqldatapool sqldatapool://controller-svc/default 僅支援 SQL Server 2019 巨量資料叢集 僅限基本驗證
SQL Server 巨量資料叢集存放集區 sqlhdfs sqlhdfs://controller-svc/default 僅支援 SQL Server 2019 巨量資料叢集 僅限基本驗證

位置路徑:

  • <Namenode> = Hadoop 叢集中電腦名稱、名稱服務 URI 或 Namenode 的 IP 位址。 PolyBase 必須解析 Hadoop 叢集所使用的任何 DNS 名稱。
  • port = 外部資料來源正在接聽的連接埠。 在 Hadoop 中,此連接埠可使用 fs.defaultFS 設定參數來尋找。 預設值為 8020。
  • <container> = 保留資料的儲存體帳戶容器。 根容器是唯讀的,因此無法將資料寫回至容器。
  • <storage_account> = Azure 資源的儲存體帳戶名稱。
  • <server_name> = 主機名稱。
  • <instance_name> = SQL Server 具名執行個體的名稱。 在目標執行個體上執行 SQL Server Browser 服務時使用。

設定位置時的其他注意事項和指引:

  • 建立物件時,SQL Server Database Engine 不會驗證外部資料源是否存在。 若要驗證,請使用外部資料來源建立外部資料表。
  • 查詢 Hadoop 時,請針對所有資料表使用相同的外部資料來源,以確保查詢語意一致。
  • 您可以使用 sqlserver 連接器將 2019 SQL Server 2019 (15.x) 連線到另一個SQL Server或Azure SQL資料庫。
  • 透過 ODBC 連線時,請指定 Driver={<Name of Driver>}
  • 使用 wasbsabfss 是選擇性的,但建議在 SQL Server 2019 (15.x) 中存取 Azure 儲存體帳戶,因為資料將會使用安全的 TLS/SSL 連線來傳送。
  • abfs從 SQL Server 2019 (15.x) CU11 開始存取 Azure 儲存體帳戶時,支援 或 abfss API。 如需詳細資訊,請參閱 Azure Blob 檔案系統驅動程式 (ABFS)
  • 從 2019 SQL Server 2019 (15.x) CU11+ 開始,Azure Data Lake Storage Gen2 支援使用 (V2) abfs[s] 的 Azure 儲存體帳戶階層命名空間選項。 不支援階層命名空間選項,而且此選項應該保持 停用狀態
  • 為確保在 Hadoop Namenode 容錯移轉期間能成功進行 PolyBase 查詢,請考慮使用虛擬 IP 位址作為 Hadoop 叢集的 Namenode。 若未這樣做,請執行 ALTER EXTERNAL DATA SOURCE 命令以指向新位置。
  • sqlhdfssqldatapool 類型支援在巨量資料叢集的主要實例和存放集區之間進行連線。 針對 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

下推 = ON |OFF

僅針對 SQL Server 2019 (15.x) 指定。 指出是否可以將計算下推到外部資料來源。 預設為 ON

PUSHDOWN連線到外部資料源層級的 SQL Server、Oracle、Teradata、MongoDB、適用于 MongoDB 的 Azure Cosmos DB API 或 ODBC 時,支援 。

透過提示可啟用或停用查詢層級的下推。

CREDENTIAL = credential_name

指定資料庫範圍的認證,以便向外部資料來源進行驗證。

建立認證時的其他注意事項和指引:

  • 只有在資料受到保護時才需要 CREDENTIAL。 允許匿名存取的資料集不需要 CREDENTIAL
  • 當 為 TYPE = BLOB_STORAGE 時,必須使用 作為身分識別來建立 SHARED ACCESS SIGNATURE 認證。
    • TYPE = BLOB_STORAGE只允許大量作業;您無法使用 TYPE = BLOB_STORAGE 建立外部資料源的外部資料表。

有多種方式可以建立共用存取簽章:

  • 您可以流覽至Azure 入口網站 - >< Your_Storage_Account > - 共用存取簽章 - 設定許可權 - >>> 產生 SAS 和連接字串,以建立 SAS 權杖。 如需詳細資訊,請參閱 產生共用存取簽章

  • 您可以使用Azure 儲存體總管 來建立和設定 SAS

  • 您可以透過 PowerShell、Azure CLI、.NET 和 REST API 以程式設計方式建立 SAS。 如需詳細資訊,請參閱使用共用存取簽章 (SAS) 對 Azure 儲存體資源授與有限存取權

  • SAS 權杖應該設定如下:

    • 產生 SAS 權杖時,權杖的開頭會包含問號 ('?')。 當設定為 SECRET 時,請排除前置 ? 詞。
    • 使用有效的到期時間 (所有日期都是 UTC 時間)。
    • 至少授與應該載入 (檔案的讀取權限,例如 srt=o&sp=r) 。 您可以針對不同的使用案例建立多個共用存取簽章。 應授與許可權,如下所示:
    動作 權限
    從檔案讀取資料 讀取
    從多個檔案和子資料夾讀取資料 讀取和列出
    使用 [建立外部資料表] 作為 [選取] (CETAS) 讀取、建立和寫入

如需搭配 和 BLOB_STORAGETYPE = 使用 CREDENTIALSHARED ACCESS SIGNATURE 的範例,請參閱建立外部資料源以執行大量作業並從 Azure 儲存體擷取資料到 SQL Database

若要建立資料庫範圍認證,請參閱 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) 中,除非連線到 Cloudera CDH、Hortonworks HDP、Azure 儲存體帳戶,否則請勿指定 TYPE。
  • HADOOP當外部資料源是 Cloudera CDH、Hortonworks HDP、Azure 儲存體帳戶或Azure Data Lake Storage Gen2時使用。
  • BLOB_STORAGE使用BULK INSERTOPENROWSET從 Azure 儲存體帳戶執行大量作業時使用 SQL Server 2017 (14.x) 。 當想要針對 Azure 儲存體建立 EXTERNAL TABLE 時,請使用 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) 中,除非連線到 Cloudera CDH、Hortonworks HDP、Azure 儲存體帳戶,否則請勿指定RESOURCE_MANAGER_LOCATION。

僅連線到 Cloudera CDH、Hortonworks HDP 或 Azure 儲存體帳戶時,請設定此選擇性值。 如需支援 Hadoop 版本的完整清單,請參閱 PolyBase 連線設定 (Transact-SQL)

定義 RESOURCE_MANAGER_LOCATION 時,查詢最佳化工具會制訂成本型決策以改善效能。 MapReduce 作業可用於將計算下推到 Hadoop。 指定 RESOURCE_MANAGER_LOCATION 可大幅減少 Hadoop 與 SQL Server 之間傳輸的資料量,這可能會導致查詢效能改善。

若未指定 Resource Manager,系統會針對 PolyBase 查詢停用將計算推送到 Hadoop。 在已啟用下推的情況下,建立參考 Hadoop 的外部資料來源提供具體範例及進一步指引。

當您建立外部資料來源時,不會驗證 RESOURCE_MANAGER_LOCATION 值。 輸入不正確的值可能會導致每次嘗試下推就在執行時間發生查詢失敗,因為無法解析所提供的值。

為了讓 PolyBase 能夠正確地使用 Hadoop 外部資料源運作,必須開啟下列 Hadoop 叢集元件的埠:

  • HDFS 連接埠
    • Namenode
    • Datanode
  • Resource Manager
    • 作業提交
    • 作業歷程記錄

若未指定連接埠,系統會使用 'hadoop connectivity' 設定的目前設定選擇預設值。

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
Resource Manager作業提交 (Hortonworks 1.3) 50300
Resource Manager作業提交 (Cloudera 4.3) 8021
windows 上的 Hortonworks 2.0、Linux 上的 Cloudera 5.x) Resource Manager作業 (提交 8032
Resource Manager (Windows) 上的 Hortonworks 2.x、3.0、Windows) 上的 Hortonworks 2.1-3 8050
Resource Manager作業歷程記錄 10020

權限

CONTROL需要SQL Server中資料庫的許可權。

鎖定

EXTERNAL DATA SOURCE 物件採取共用鎖定。

安全性

PolyBase 支援大多數外部資料來源的 Proxy 驗證。 建立資料庫範圍認證以建立 Proxy 帳戶。

當您在 SQL Server 2019 巨量資料叢集中連線到儲存體或資料集區時,使用者的認證會傳遞至後端系統。 在資料集區本身中建立登入以啟用傳遞驗證。

不支援類型 HADOOP 為的 SAS 權杖。 只有在改用儲存體帳戶存取金鑰時,才支援類型 = BLOB_STORAGE 。 嘗試使用 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 的外部資料源可以使用 Proxy 驗證來提供精細的存取控制。 相較於模擬的使用者,Proxy 使用者可以設定為具有有限的存取權。

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]);

如需 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.security.authentication Hadoop 中的 屬性值core-site.xml。 若要參考由 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 參數,而是在 LOCATION 參數內,透過 CREATE EXTERNAL TABLE 語句中的三部分名稱來提供資料庫名稱。 如需範例,請參閱 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) 及更新版本。

針對使用 BULK INSERTOPENROWSET 的大量作業,請使用下列資料來源。 認證必須將 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 Data Lake Storage Gen2帳戶 logs ,使用Azure Blob 檔案系統驅動程式 (ABFS) 。 儲存體容器稱為 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 INSERTOPENROWSET 的大量載入作業

注意

此語法會因不同版本的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://<storage_account_name>.blob.core.windows.net/<container_name> 從 SQL Server 2022 (16.x) 開始
支援階層命名空間。
共用存取簽章 (SAS)
Azure Data Lake Storage Gen2 adls 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 或適用于 MongoDB 的 Cosmos DB API 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://<server_name>:<port>/ 從 SQL Server 2022 (16.x) 開始 *

* 必須是 資料庫範圍認證,其中 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 相容儲存體的端點和埠。

設定位置時的其他注意事項和指引:

  • 建立物件時,SQL Server Database Engine 不會驗證外部資料源是否存在。 若要驗證,請使用外部資料來源建立外部資料表。
  • 您可以使用 sqlserver 連接器將 SQL Server 2019 (15.x) 連接到另一個SQL Server或Azure SQL Database。
  • 透過 ODBC 連線時,請指定 Driver={<Name of Driver>}
  • SQL Server 2022 (16.x (16.x) 支援使用 Azure Data Lake Storage Gen2前置 adls 詞的 Azure 儲存體帳戶 (V2) 階層命名空間選項。

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

PUSHDOWN = ON | OFF

適用于:SQL Server 2019 (15.x) 及更新版本。 指出是否可以將計算下推到外部資料來源。 預設為開啟。

PUSHDOWN連線到外部資料源層級的 SQL Server、Oracle、Teradata、MongoDB、適用于 MongoDB 的 Azure Cosmos DB API 或 ODBC 時,支援 。

透過提示可啟用或停用查詢層級的下推。

CREDENTIAL = credential_name

指定資料庫範圍的認證,以便向外部資料來源進行驗證。

建立認證時的其他注意事項和指引:

有多種方式可以建立共用存取簽章:

  • 您可以流覽至Azure 入口網站 - >< Your_Storage_Account > - 共用存取簽章 - 設定許可權 - >>> 產生 SAS 和連接字串,以建立 SAS 權杖。 如需詳細資訊,請參閱 產生共用存取簽章

  • 您可以使用Azure 儲存體總管 來建立和設定 SAS

  • 您可以透過 PowerShell、Azure CLI、.NET 和 REST API,以程式設計方式建立 SAS。 如需詳細資訊,請參閱使用共用存取簽章 (SAS) 對 Azure 儲存體資源授與有限存取權

  • SAS 權杖應設定如下:

    • 產生 SAS 權杖時,權杖的開頭會包含問號 ('?')。 在設定為 SECRET 時排除前置 ? 詞。
    • 使用有效的到期時間 (所有日期都是 UTC 時間)。
    • 至少授與應該載入 (檔案的讀取權限,例如 srt=o&sp=r) 。 您可以針對不同的使用案例建立多個共用存取簽章。 應該授與許可權,如下所示:
    動作 權限
    從檔案讀取資料 讀取
    從多個檔案和子資料夾讀取資料 讀取和列出
    使用 [建立外部資料表] 作為 [選取] (CETAS) 讀取、建立和寫入
  • 當 為 TYPE = BLOB_STORAGE 時,必須使用 作為身分識別來建立 SHARED ACCESS SIGNATURE 認證。 此外,SAS 權杖應設定如下:

    • 在設定為秘密時排除前置 ? 詞。
    • 至少要載入 (檔案的讀取權限,例如 srt=o&sp=r) 。
    • 使用有效的到期時間 (所有日期都是 UTC 時間)。
    • TYPE = BLOB_STORAGE只允許大量作業;您無法使用 TYPE = BLOB_STORAGE 建立外部資料源的外部資料表。

如需搭配 S3 相容物件儲存體和 PolyBase 使用 CREDENTIAL 的範例,請參閱 設定 PolyBase 以存取 S3 相容物件儲存體中的外部資料

若要建立資料庫範圍認證,請參閱 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)

權限

CONTROL需要 SQL Server 中資料庫的許可權。

鎖定

EXTERNAL DATA SOURCE 物件採取共用鎖定。

安全性

PolyBase 支援大多數外部資料來源的 Proxy 驗證。 建立資料庫範圍認證以建立 Proxy 帳戶。

升級至 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 的外部資料源可以使用 Proxy 驗證來提供精細的存取控制。 相較於模擬的使用者,Proxy 使用者可以設定為具有有限的存取權。

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]);

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 參數,而是在 LOCATION 參數內,透過 CREATE EXTERNAL TABLE 語句中的三部分名稱來提供資料庫名稱。 如需範例,請參閱 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) 。 產生共用存取簽章權杖的其中一個簡單方式,請遵循下列步驟。 如需詳細資訊,請參閱 CREDENTIAL

  1. 流覽至Azure 入口網站,以及所需的儲存體帳戶。
  2. 流覽至您想要的 [ 資料儲存體 ] 功能表下的 [容器]。
  3. 選取 [共用存取權杖]。
  4. 根據所需的動作選擇適當的許可權,以參考使用資料表鈴鐺:
動作 權限
從檔案讀取資料 讀取
從多個檔案和子資料夾讀取資料 讀取和列出
使用 [建立外部資料表] 作為 [選取] (CETAS) 讀取、建立和寫入
  1. 選擇權杖到期日。
  2. 產生 SAS 權杖和 URL。
  3. 複製 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://<storage_account_name>.blob.core.windows.net/<container>' ,
    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://<storage_account>.dfs.core.windows.net'
,CREDENTIAL = datalakegen2
)

如需有關如何存取 Azure Data Lake Gen2 上儲存的差異檔案的詳細範例,請參閱 使用 PolyBase 將差異資料表虛擬化

範例:大量作業

重要

設定大量作業的外部資料來源時,請不要在 LOCATION URL 的結尾處新增尾端 / 、檔案名稱或共用存取簽章參數。

H. 針對從 Azure 儲存體擷取資料的大量作業,建立外部資料來源

適用于:SQL Server 2017 (14.x) 及更新版本。

針對使用 BULK INSERTOPENROWSET 的大量作業,請使用下列資料來源。 認證必須將 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
  ) ;

下一步

* SQL Database *  

 

概觀:Azure SQL Database

適用于:Azure SQL資料庫

建立彈性查詢的外部資料來源。 外部資料來源會用來建立連線能力,並支援這些主要使用案例:

  • 使用 BULK INSERTOPENROWSET 的大量載入作業
  • 使用 SQL Database 搭配彈性查詢,查詢遠端 SQL Database 或 Azure Synapse 執行個體
  • 使用彈性查詢來查詢分區化SQL Database

Syntax

如需語法慣例的詳細資訊,請參閱 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 Database 的資料庫內必須是唯一的。

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 引數來指定。

設定位置時的其他注意事項和指引:

  • 建立物件時,Database Engine 不會驗證外部資料源是否存在。 若要驗證,請使用外部資料來源建立外部資料表。

CREDENTIAL = credential_name

指定資料庫範圍的認證,以便向外部資料來源進行驗證。

建立認證時的其他注意事項和指引:

  • 若要將資料從 Azure 儲存體載入Azure SQL資料庫,請使用共用存取簽章 (SAS 權杖) 。
  • 只有在資料受到保護時才需要 CREDENTIAL。 允許匿名存取的資料集不需要 CREDENTIAL
  • TYPEBLOB_STORAGE = 當 為 時,必須使用 作為身分識別來建立 SHARED ACCESS SIGNATURE 認證。
  • 透過 WASB[s] 連接器連線到 Azure 儲存體時,必須使用儲存體帳戶金鑰進行驗證,而不是使用共用存取簽章 (SAS) 。
  • 當必須使用儲存體帳戶金鑰作為 建立認證時 TYPEHADOOP = 。 SECRET
  • TYPE = BLOB_STORAGE僅允許進行大量作業;您無法使用 建立外部資料源 TYPE = BLOB_STORAGE 的外部資料表。

有多種方式可以建立共用存取簽章:

  • 您可以流覽至Azure 入口網站 - >< Your_Storage_Account > - > 共用存取簽章 - > 設定許可權 - 產生 SAS 和連接字串, >以建立 SAS 權杖。 如需詳細資訊,請參閱 產生共用存取簽章

  • 您可以使用Azure 儲存體總管 來建立和設定 SAS

  • 您可以透過 PowerShell、Azure CLI、.NET 和 REST API 以程式設計方式建立 SAS。 如需詳細資訊,請參閱使用共用存取簽章 (SAS) 對 Azure 儲存體資源授與有限存取權

  • SAS 權杖應該設定如下:

    • 產生 SAS 權杖時,權杖的開頭會包含問號 ('?')。 當設定為 SECRET 時,請排除前置 ? 詞。
    • 使用有效的到期時間 (所有日期都是 UTC 時間)。
    • 至少授與應該載入 (檔案的讀取權限,例如 srt=o&sp=r) 。 您可以針對不同的使用案例建立多個共用存取簽章。 應授與許可權,如下所示:
    動作 權限
    從檔案讀取資料 讀取
    從多個檔案和子資料夾讀取資料 讀取和列出
    使用 [建立外部資料表] 作為 [選取] (CETAS) 讀取、建立和寫入

如需搭配 和 BLOB_STORAGETYPE = 使用 CREDENTIALSHARED ACCESS SIGNATURE 的範例,請參閱建立外部資料源以執行大量作業並從 Azure 儲存體擷取資料到 SQL Database

若要建立資料庫範圍認證,請參閱 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)

TYPE = [ BLOB_STORAGE | RDBMS | SHARD_MAP_MANAGER]

指定要設定的外部資料來源類型。 不一定需要此參數。

  • 使用 RDBMS 處理使用 SQL Database 彈性查詢的跨資料庫查詢。
  • 建立外部資料來源以連線到共用 SQL Database 時,請使用 SHARD_MAP_MANAGER
  • 使用 BULK INSERTOPENROWSET 執行大量作業時,請使用 BLOB_STORAGE

重要

如果使用任何其他外部資料來源,請不要設定 TYPE

DATABASE_NAME = database_name

TYPE 設定為 RDBMSSHARD_MAP_MANAGER 時,請設定此引數。

TYPE DATABASE_NAME 的值
RDBMS 使用 LOCATION 所提供伺服器上的遠端資料庫名稱
SHARD_MAP_MANAGER 以分區對應管理員運作的資料庫名稱

如需範例,示範如何建立參考建立 RDBMS 外部資料源的外部資料源 TYPE = RDBMS

SHARD_MAP_NAME = shard_map_name

僅限在 TYPE 引數設定為 SHARD_MAP_MANAGER 時用於設定分區對應的名稱。

如需示範如何建立外部資料源的範例,請參閱 TYPE = SHARD_MAP_MANAGER建立分區對應管理員外部資料源

權限

CONTROL需要 Azure SQL Database 中資料庫的許可權。

鎖定

EXTERNAL DATA SOURCE 物件採取共用鎖定。

範例

A. 建立分區對應管理員外部資料來源

若要建立要參考 SHARD_MAP_MANAGER 的外部資料源,請指定裝載SQL Database或虛擬機器上SQL Server資料庫的分區對應管理員SQL Database伺服器名稱。

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 Database 中指定遠端資料庫的 SQL Database 伺服器名稱。

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 INSERTOPENROWSET 的大量作業,請使用下列資料來源。 認證必須將 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 位址的 Kafka 伺服器,xxx.xxx.xxx.xxx 並接聽埠 1900。 Kafka 外部資料源僅適用于資料流程,且不支援述詞下推。

-- Create an External Data Source for Kafka
CREATE EXTERNAL DATA SOURCE MyKafkaServer WITH (
    LOCATION = 'kafka://xxx.xxx.xxx.xxx:1900'
)
GO

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://'
)
go

下一步

* Azure Synapse
分析*
 

 

概觀:Azure Synapse Analytics

適用于:Azure Synapse分析

建立 PolyBase 的外部資料來源。 外部資料源可用來建立連線能力,並支援使用 PolyBase進行資料虛擬化和資料載入的主要使用案例。

重要

若要建立外部資料源,以使用Azure SQL資料庫搭配彈性查詢來查詢Azure Synapse分析資源,請參閱SQL Database

Syntax

如需語法慣例的詳細資訊,請參閱 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 Database 內必須是唯一的。

LOCATION = '<prefix>://<path[:port]>'

提供連線通訊協定和路徑給外部資料來源。

外部資料來源 連接器位置前置詞 位置路徑
Azure Data Lake Store Gen 1 adl <storage_account>.azuredatalake.net
Azure Data Lake Store Gen2 abfs[s] <container>@<storage_account>.dfs.core.windows.net
Azure V2 儲存體帳戶 wasb[s] <container>@<storage_account>.blob.core.windows.net

位置路徑:

  • <container> = 保留資料的儲存體帳戶容器。 根容器是唯讀的,因此無法將資料寫回至容器。
  • <storage_account> = Azure 資源的儲存體帳戶名稱。

設定位置時的其他注意事項和指引:

  • 布建Azure Data Lake Storage Gen2時,預設選項會使用 enable secure SSL connections 。 當啟用此項目時,您必須在選取了安全 TLS/SSL 連線時使用 abfss。 請注意,abfss 也適用於不安全的 TLS 連線。 如需詳細資訊,請參閱 Azure Blob 檔案系統驅動程式 (ABFS)
  • 在建立物件時,Azure Synapse 不會驗證外部資料來源是否存在。 若要驗證,請使用外部資料來源建立外部資料表。
  • 查詢 Hadoop 時,請針對所有資料表使用相同的外部資料來源,以確保查詢語意一致。
  • 由於資料會使用安全的 TLS 連線傳送,因此建議使用 wasbs
  • 使用 wasb://介面透過 PolyBase 存取資料時,不支援搭配 Azure V2 儲存體帳戶使用階層命名空間。

CREDENTIAL = credential_name

指定資料庫範圍的認證,以便向外部資料來源進行驗證。

建立認證時的其他注意事項和指引:

  • 若要將資料從 Azure 儲存體或 Azure Data Lake Store (ADLS) Gen2 載入至 Azure Synapse Analytics,請使用 Azure 儲存體金鑰。
  • 只有在資料受到保護時才需要 CREDENTIAL。 允許匿名存取的資料集不需要 CREDENTIAL

若要建立資料庫範圍認證,請參閱 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)

TYPE = HADOOP

指定要設定的外部資料來源類型。 不一定需要此參數。

當外部資料源為 Azure 儲存體、ADLS Gen 1 或 ADLS Gen2 時,請使用 HADOOP。

如需使用 TYPE = HADOOP 從 Azure 儲存體載入資料的範例,請參閱使用服務主體建立外部資料源以參考 Azure Data Lake Store Gen 1 或 2

權限

需要資料庫的 CONTROL 權限。

鎖定

EXTERNAL DATA SOURCE 物件採取共用鎖定。

安全性

PolyBase 支援大多數外部資料來源的 Proxy 驗證。 建立資料庫範圍認證以建立 Proxy 帳戶。

支援對 Azure Data Lake Store Gen 2 儲存體帳戶進行驗證的共用存取簽章 (SAS) 金鑰。 想要使用共用存取簽章進行驗證的客戶必須建立資料庫範圍認證,並在其中 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) 。

此範例示範如何建立資料庫範圍認證,以便向 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 或 2

Azure Data Lake Store 連線能力以您的 ADLS URI 與 Azure Active Directory 應用程式服務主體為基礎。 說明如何建立此應用程式的文件可以在使用 Azure Active Directory 的 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 Azure Active Directory 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
  -- Please 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 的 PolyBase 連線

當連線至具有受控識別機制的 Azure Data Lake 存放區 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
  ) ;

下一步

*分析
平臺系統 (PDW) *
 

 

概觀:分析平台系統

適用于:Analytics Platform System (PDW)

建立 PolyBase 查詢的外部資料來源。 外部資料源可用來建立連線能力,並支援下列使用案例:使用 PolyBase的資料虛擬化和資料載入。

Syntax

如需語法慣例的詳細資訊,請參閱 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 叢集中電腦名稱、名稱服務 URI 或 Namenode 的 IP 位址。 PolyBase 必須解析 Hadoop 叢集所使用的任何 DNS 名稱。
  • port = 外部資料來源正在接聽的連接埠。 在 Hadoop 中,此連接埠可使用 fs.defaultFS 設定參數來尋找。 預設值為 8020。
  • <container> = 保留資料的儲存體帳戶容器。 根容器是唯讀的,因此無法將資料寫回至容器。
  • <storage_account> = Azure 資源的儲存體帳戶名稱。

設定位置時的其他注意事項和指引:

  • 在建立物件時,PDW 引擎不會驗證外部資料來源是否存在。 若要驗證,請使用外部資料來源建立外部資料表。
  • 查詢 Hadoop 時,請針對所有資料表使用相同的外部資料來源,以確保查詢語意一致。
  • 由於資料會使用安全的 TLS 連線傳送,因此建議使用 wasbs
  • 透過 wasb:// 搭配 Azure 儲存體帳戶使用時,不支援階層命名空間。
  • 為確保在 Hadoop Namenode 容錯移轉期間能成功進行 PolyBase 查詢,請考慮使用虛擬 IP 位址作為 Hadoop 叢集的 Namenode。 若未這樣做,請執行 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) 中,除非連線到 Cloudera CDH、Hortonworks HDP、Azure 儲存體帳戶,否則請勿指定RESOURCE_MANAGER_LOCATION。

僅連線到 Cloudera CDH、Hortonworks HDP 或 Azure 儲存體帳戶時,請設定此選擇性值。 如需支援 Hadoop 版本的完整清單,請參閱 PolyBase 連線設定 (Transact-SQL)

定義 RESOURCE_MANAGER_LOCATION 時,查詢最佳化工具會制訂成本型決策以改善效能。 MapReduce 作業可用於將計算下推到 Hadoop。 指定 RESOURCE_MANAGER_LOCATION 可大幅降低在 Hadoop 與 SQL 之間傳輸的資料量,而導致查詢效能獲得改善。

若未指定 Resource Manager,系統會針對 PolyBase 查詢停用將計算推送到 Hadoop。 在已啟用下推的情況下,建立參考 Hadoop 的外部資料來源提供具體範例及進一步指引。

當您建立外部資料來源時,不會驗證 RESOURCE_MANAGER_LOCATION 值。 輸入不正確的值可能會導致每次嘗試下推就在執行時間發生查詢失敗,因為無法解析所提供的值。

為了讓 PolyBase 能夠正確運作 Hadoop 外部資料源,必須開啟下列 Hadoop 叢集元件的埠:

  • HDFS 連接埠
    • Namenode
    • Datanode
  • Resource Manager
    • 作業提交
    • 作業歷程記錄

若未指定連接埠,系統會使用 'hadoop connectivity' 設定的目前設定選擇預設值。

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 (Hortonworks 1.3) Resource Manager作業提交 50300
cloudera 4.3 (Resource Manager作業提交) 8021
Resource Manager (Linux 上的 Hortonworks 2.0、Linux 上的 Cloudera 5.x) 8032
Resource Manager工作提交 (Linux 上的 Hortonworks 2.x、3.0、Windows) 上的 Hortonworks 2.1-3 8050
Resource Manager作業歷程記錄 10020

權限

CONTROL需要 Analytics Platform System (PDW) 中資料庫的許可權。

注意

在舊版 PDW 中,建立外部資料來源需要有 ALTER ANY EXTERNAL DATA SOURCE 權限。

鎖定

EXTERNAL DATA SOURCE 物件採取共用鎖定。

安全性

PolyBase 支援大多數外部資料來源的 Proxy 驗證。 建立資料庫範圍認證以建立 Proxy 帳戶。

不支援類型 HADOOP 為的 SAS 權杖。 只有在改用儲存體帳戶存取金鑰時,才支援類型 = BLOB_STORAGE 。 嘗試使用 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 語法,存取各種檔案格式的外部資料。

Syntax

如需語法慣例的詳細資訊,請參閱 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>

建立物件時,Database Engine 不會驗證外部資料源是否存在。 若要驗證,請使用外部資料來源建立外部資料表。

設定大量作業的外部資料來源時,請不要在 LOCATION URL 的結尾處新增尾端 / 、檔案名稱或共用存取簽章參數。

CREDENTIAL = credential_name

指定資料庫範圍的認證,以便向外部資料來源進行驗證。

建立認證時的其他注意事項和指引:

  • 若要將資料從 Azure 儲存體載入Azure SQL 受控執行個體,請使用共用存取簽章 (SAS 權杖) 。
  • 只有在資料受到保護時才需要 CREDENTIAL。 允許匿名存取的資料集不需要 CREDENTIAL
  • 如果需要認證,則必須使用 Managed IdentitySHARED ACCESS SIGNATURE 作為 IDENTITY 來建立認證。 若要建立資料庫範圍認證,請參閱 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)
  • TYPE = BLOB_STORAGE僅允許進行大量作業;您無法使用 建立外部資料源 TYPE = BLOB_STORAGE 的外部資料表。

若要針對資料庫範圍認證使用受控服務識別:

  • 指定 WITH IDENTITY = 'Managed Identity'

    • 使用系統指派的受控服務識別Azure SQL 受控執行個體,如果系統指派的受控服務識別用於此用途,則必須啟用此識別。
  • 讀取者Azure RBAC 角色授與Azure SQL 受控執行個體的系統指派受控服務識別給必要的Azure Blob 儲存體容器。 例如,透過Azure 入口網站,請參閱使用Azure 入口網站指派 Azure 角色

若要為資料庫範圍認證建立共用存取簽章 (SAS) :

  • 指定 WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = ...

  • 有多種方式可以建立共用存取簽章:

  • SAS 權杖應該設定如下:

    • 產生 SAS 權杖時,權杖的開頭會包含問號 ('?')。 當設定為 SECRET 時,請排除前置 ? 詞。
    • 使用有效的到期時間 (所有日期都是 UTC 時間)。
    • 至少授與應該載入 (檔案的讀取權限,例如 srt=o&sp=r) 。 您可以針對不同的使用案例建立多個共用存取簽章。 應授與許可權,如下所示:
    動作 權限
    從檔案讀取資料 讀取
    從多個檔案和子資料夾讀取資料 讀取和列出
    使用 [建立外部資料表] 作為 [選取] (CETAS) 讀取、建立和寫入

權限

CONTROL需要資料庫在 Azure SQL 受控執行個體 的許可權。

鎖定

EXTERNAL DATA SOURCE 物件採取共用鎖定。

範例

如需更多範例,請參閱使用 Azure SQL 受控執行個體 進行資料虛擬化

A. 使用 OPENROWSET 或外部資料表從 Azure SQL 受控執行個體查詢外部資料

如需更多範例,請參閱建立外部資料源,或參閱使用 Azure SQL 受控執行個體 進行資料虛擬化

  1. 如果資料庫主要金鑰不存在,請建立它。

    -- Optional: Create MASTER KEY if it doesn't exist in the database:
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong Password>'
    GO
    
  2. 使用 SAS 權杖建立資料庫範圍認證。 您也可以使用受控識別。

    CREATE DATABASE SCOPED CREDENTIAL MyCredential
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = '<KEY>' ; --Removing leading '?'
    GO
    
  3. 使用認證建立外部資料源。

    --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]
    )
    GO
    
  4. 使用 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
    
  5. 或者,使用 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
    
  6. 或者,建立 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
    

下一步