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) 到 SQL Server 2019 (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 資料庫引擎不會驗證外部資料來源是否存在。 若要驗證,請使用外部資料來源建立外部資料表。
  • 查詢 Hadoop 時,請針對所有資料表使用相同的外部資料來源,以確保查詢語意一致。
  • 對於存取 Azure 儲存體帳戶,wasbs 是選擇性的,但在 SQL Server 2016 (13.x) 中建議使用,因為會使用安全的 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
Resource Manager 工作提交 (Hortonworks 1.3) 50300
Resource Manager 工作提交 (Cloudera 4.3) 8021
Resource Manager 工作提交 (Windows 上的 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

權限

需要有對 SQL Server 中資料庫的 CONTROL 權限。

鎖定

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 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 連接器來連接到 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 資料庫引擎不會驗證外部資料來源是否存在。 若要驗證,請使用外部資料來源建立外部資料表。
  • 查詢 Hadoop 時,請針對所有資料表使用相同的外部資料來源,以確保查詢語意一致。
  • 透過 ODBC 連線時,請指定 Driver={<Name of Driver>}
  • 對於存取 Azure 儲存體帳戶,wasbs 是選擇性的,但在 SQL Server 2017 (14.x) 中建議使用,因為會使用安全的 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 連接器來連接到 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)。 可以針對不同的使用案例建立多個共用存取簽章。 應如下來授與權限:
    動作 權限
    從檔案讀取資料 Read
    從多個檔案和子資料夾讀取資料 讀取和列示

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

若要建立資料庫範圍認證,請參閱 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 INSERTOPENROWSET 從 Azure 儲存體帳戶執行大量作業時,請使用 BLOB_STORAGE。 隨 SQL Server 2017 (14.x) 引進。 當想要針對 Azure 儲存體來 CREATE 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
Resource Manager 工作提交 (Hortonworks 1.3) 50300
Resource Manager 工作提交 (Cloudera 4.3) 8021
Resource Manager 工作提交 (Windows 上的 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

權限

需要有對 SQL Server 中資料庫的 CONTROL 權限。

鎖定

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 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 連接器來連接到 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) 到 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+ 開始。 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 資料庫引擎不會驗證外部資料來源是否存在。 若要驗證,請使用外部資料來源建立外部資料表。
  • 查詢 Hadoop 時,請針對所有資料表使用相同的外部資料來源,以確保查詢語意一致。
  • 您可以使用 sqlserver 連接器來將 SQL Server 2019 (15.x) 連接到另一個 SQL Server 或 Azure SQL Database。
  • 透過 ODBC 連線時,請指定 Driver={<Name of Driver>}
  • 對於存取 Azure 儲存體帳戶,使用 wasbsabfss 是選擇性的,但在 SQL Server 2019 (15.x) 中建議使用,因為會使用安全的 TLS/SSL 連線來傳送資料。
  • 從 SQL Server 2019 (15.x) CU11 開始存取 Azure 儲存體帳戶時,支援 abfsabfss API。 如需詳細資訊,請參閱 Azure Blob 檔案系統驅動程式 (ABFS)
  • 從 SQL Server 2019 (15.x) CU11+ 開始,支援透過 Azure Data Lake Storage Gen2 使用 abfs[s] 的 Azure 儲存體帳戶 (V2) 的階層命名空間選項。 在其他方面不支援階層命名空間選項,此選項應保持停用狀態。
  • 為確保在 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

從 2019 SQL Server 2019 (15.x) 累積更新 19 開始,已引進了其他關鍵字來支援 Oracle TNS 檔案:

  • 關鍵字 TNSNamesFile 指定位於 Oracle 伺服器上 tnsnames.ora 檔案的檔案路徑。
  • 關鍵字 ServerName 指定將用來取代主機名稱和埠的 tnsnames.ora 內所使用的別名。

Pushdown = ON | OFF

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

在外部資料來源層級連接到 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 權杖。 如需詳細資訊,請參閱產生共用存取簽章

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

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

  • SAS 權杖應設定如下:

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

如需搭配 SHARED ACCESS SIGNATURETYPE = BLOB_STORAGE 使用 CREDENTIAL 的範例,請參閱建立外部資料來源以執行大量作業並將資料從 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。
  • 當外部資料來源是 Cloudera CDH、Hortonworks HDP、Azure 儲存體帳戶或 Azure Data Lake Storage Gen2 時,請使用 HADOOP
  • 搭配 SQL Server 2017 (14.x) 使用 BULK INSERTOPENROWSET 執行來自 Azure 儲存體帳戶的大量作業時,請使用 BLOB_STORAGE。 當想要針對 Azure 儲存體來 CREATE 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
Resource Manager 工作提交 (Windows 上的 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

權限

需要有對 SQL Server 中資料庫的 CONTROL 權限。

鎖定

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

或者,您也可以使用 TNS 驗證。

從 SQL Server 2019 (15.x) 累積更新 19 開始,CREATE EXTERNAL DATA SOURCE 現在支援在連接到 Oracle 時使用 TNS 檔案。 CONNECTION_OPTIONS 參數已展開,現在會使用 TNSNamesFileServerName 作為變數來瀏覽 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 連接器來連接到 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) 和 SQL Server 2019 (15.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://<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 Database。
  • 透過 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 引數。
  • 如需從 2022 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 中虛擬化差異資料表的範例,請參閱使用 PolyBase 將差異資料表虛擬化
  • 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>

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 = ON | OFF

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

在外部資料來源層級連接到 SQL Server、Oracle、Teradata、MongoDB、Azure Cosmos DB API for MongoDB 或 ODBC 時,才支援 PUSHDOWN

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

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)。 可以針對不同的使用案例建立多個共用存取簽章。 應如下來授與權限:
    動作 權限
    從檔案讀取資料 Read
    從多個檔案和子資料夾讀取資料 讀取和列示
    使用 Create External Table as Select (CETAS) 讀取、建立、列示和寫入
  • 針對 Azure Blob 儲存體 和 Azure Data Lake Gen 2:

    • 允許的服務: Blob 必須選取才能產生 SAS 令牌
    • 允許的資源類型: Container 必須選取 , Object 才能產生 SAS 令牌

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

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

權限

需要有對 SQL Server 中資料庫的 CONTROL 權限。

鎖定

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

或者,您也可以使用 TNS 來進行驗證。

適用於:SQL Server 2022 (16.x) 累積更新 2 開始,CREATE EXTERNAL DATA SOURCE 現在支援在連接到 Oracle 時使用 TNS 檔案。 CONNECTION_OPTIONS 參數已展開,現在會使用 TNSNamesFileServerName 作為變數來瀏覽 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 參數,會在 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. 根據所需的動作來選擇適當的權限 (參考使用下表):
動作 權限
從檔案讀取資料 Read
從多個檔案和子資料夾讀取資料 讀取和列示
使用 Create External Table as Select (CETAS) 讀取、建立和寫入
  1. 選擇權杖到期日。
  2. 產生 SAS 權杖與 URL。
  3. 複製 SAS 權杖。

F. 使用 abs:// 介面來建立存取 Azure Blob 儲存體中資料的外部資料來源

適用於:SQL Server 2022 (16.x) 和更新版本

從 SQL Server 2022 (16.x) 開始,請為 Azure 儲存體帳戶 v2 使用新前置詞 absabs 前置詞支援使用 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,以取代舊版中所使用的 abfsadls 前置詞也支援 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 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 = 'abs://<container>@<storage_account_name>.blob.core.windows.net/',
    CREDENTIAL = AccessAzureInvoices,
);

後續步驟

* SQL Database *  

 

概觀:Azure SQL Database

適用於:Azure SQL Database

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

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

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

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

CREDENTIAL = credential_name

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

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

  • 若要從 Azure 儲存體將資料載入 Azure SQL Database,請使用共用存取簽章 (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 權杖。 如需詳細資訊,請參閱產生共用存取簽章

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

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

  • SAS 權杖應設定如下:

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

如需搭配 SHARED ACCESS SIGNATURETYPE = BLOB_STORAGE 使用 CREDENTIAL 的範例,請參閱建立外部資料來源以執行大量作業並將資料從 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 以分區對應管理員運作的資料庫名稱

如需示範如何建立外部資料來源 (其中 TYPE = RDBMS) 的範例,請參閱建立 RDBMS 外部資料來源

SHARD_MAP_NAME = shard_map_name

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

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

權限

在 Azure SQL Database 中,需要對資料庫具有 CONTROL 權限。

鎖定

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

在此範例中,外部資料來源是 Kafka 伺服器,其 IP 位址為 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');

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

適用於:Azure Synapse Analytics

建立資料虛擬化的外部資料來源。 外部資料來源可用來建立連線能力,並支援從外部資料來源進行資料虛擬化及資料載入的主要使用案例。 如需詳細資訊,請參閱搭配 Synapse SQL 使用外部資料表

重要

若要建立外部資料來源,以使用 Azure SQL Database 搭配彈性查詢來查詢 Azure Synapse Analytics 資源,請參閱 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>'

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

外部資料來源 連接器位置前置詞 位置路徑
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 Yes
Data Lake Storage Gen2 abfs[s] Yes .是
Azure Blob 儲存體 wasbs 是***
Azure Blob Storage https No .是
Data Lake Storage Gen1 http[s] Yes
Data Lake Storage Gen2 http[s] Yes .是 Yes
Data Lake Storage Gen2 wasb[s] Yes .是 Yes

* Azure Synapse Analytics 中的無伺服器和專用 SQL 集區會使用不同的程式碼基底來進行資料虛擬化。 無伺服器 SQL 集區支援原生資料虛擬化技術。 專用 SQL 集區同時支援原生和 PolyBase 資料虛擬化。 使用 TYPE=HADOOP 建立 EXTERNAL DATA SOURCE 時,會使用 PolyBase 資料虛擬化。

** Microsoft Azure Data Lake Storage Gen1 的支援有限,建議針對所有新的開發使用 Gen2。

*** 建議透過 wasb 使用更安全的 wasbs 連接器。 只有專用 SQL 集區中的原生資料虛擬化 (其中 TYPE 不等於 HADOOP) 支援 wasb

位置路徑:

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

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

  • 預設選項是在佈建 Azure Data Lake Storage Gen2 時使用 enable secure SSL connections。 當啟用此項目時,您必須在選取了安全 TLS/SSL 連線時使用 abfss。 請注意,abfss 也適用於不安全的 TLS 連線。 如需詳細資訊,請參閱 Azure Blob 檔案系統驅動程式 (ABFS)
  • 在建立物件時,Azure Synapse 不會驗證外部資料來源是否存在。 若要驗證,請使用外部資料來源建立外部資料表。
  • 查詢 Hadoop 時,請針對所有資料表使用相同的外部資料來源,以確保查詢語意一致。
  • https: 前置詞可讓您在路徑中使用子資料夾。 https 不適用於所有資料存取方法。
  • 由於資料會使用安全的 TLS 連線傳送,因此建議使用 wasbs
  • 使用舊式的 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) 令牌、自定義應用程式識別、工作區受控識別或記憶體存取密鑰。

TYPE = HADOOP

選用,但不建議使用。

您只能指定具有專用 SQL 集區的 TYPE。 HADOOP 是指定時唯一允許的值。 包含TYPE=HADOOP的外部資料來源只適用於專用 SQL 集區。

針對舊式的實作使用 HADOOP,否則建議使用較新的原生資料存取。 請勿指定 TYPE 引數來使用較新的原生資料存取。

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

Azure Synapse Analytics 中的無伺服器和專用 SQL 集區會使用不同的程式碼基底來進行資料虛擬化。 無伺服器 SQL 集區支援原生資料虛擬化技術。 專用 SQL 集區同時支援原生和 PolyBase 資料虛擬化。 使用 TYPE=HADOOP 建立 EXTERNAL DATA SOURCE 時,會使用 PolyBase 資料虛擬化。

權限

需要資料庫的 CONTROL 權限。

鎖定

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

安全性

大部分的外部資料來源都支援 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 連接器來連接到 Azure 儲存體時,必須使用儲存體帳戶金鑰進行驗證,而不是使用共用存取簽章 (SAS)。

此範例使用舊式的 HADOOP JAVA 型存取方法。 下列範例示範如何建立資料庫有限範圍認證,以便向 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 和 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 存放區 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
);

後續步驟

* Analytics
Platform System (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
Resource Manager 工作提交 (Hortonworks 1.3) 50300
Resource Manager 工作提交 (Cloudera 4.3) 8021
Resource Manager 工作提交 (Windows 上的 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

權限

在 Analytics Platform System (PDW) 中,需要對資料庫具有 CONTROL 權限。

注意

在舊版 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 連接器來連接到 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>

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

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

CREDENTIAL = credential_name

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

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

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

若要針對資料庫有限範圍認證使用受控服務識別,請執行下列動作:

  • 指定 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)。 可以針對不同的使用案例建立多個共用存取簽章。 應如下來授與權限:
    動作 權限
    從檔案讀取資料 Read
    從多個檔案和子資料夾讀取資料 讀取和列示
    使用 Create External Table as Select (CETAS) 讀取、建立和寫入

權限

在 Azure SQL 受控執行個體中,需要對資料庫具有 CONTROL 權限。

鎖定

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

後續步驟