設定外部資料來源，以便搭配 PolyBase 使用

<meta <="" content="This page describes how to configure external data sources for use with PolyBase and your SQL Server 適用的 Cloud SQL instances." name="description" />

MySQL   |  PostgreSQL   |  SQL Server

本頁面說明如何設定及管理外部資料來源，以便搭配 PolyBase 和 SQL Server 適用的 Cloud SQL 執行個體使用。

管理外部物件的存取權

Cloud SQL 僅支援啟用和停用 PolyBase 的作業。使用者必須使用 T-SQL 陳述式，手動管理 SQL Server 實體。

手動管理 SQL Server 實體前，請先參閱下列 Microsoft 資源：

• 使用 T-SQL 建立 MASTER KEY
• 設定 PolyBase，存取 Oracle 中的外部資料
• 設定 PolyBase，以便存取與 S3 相容的物件儲存空間中的外部資料

建立外部資料來源

下列步驟說明如何為 SQL Server 適用的 Cloud SQL 執行個體建立外部資料來源。

SQL Server 會將外部資料來源定義為具有連線資訊的資源，例如伺服器名稱、資料庫名稱和連線字串。

SQL Server 會將外部資料表定義為 SQL Server 內的中繼資料物件，指向外部資料來源中的資料。這些物件會定義外部資料的結構定義，因為這些資料會顯示在 SQL Server 中。

這個程序需要為外部資料來源建立資料庫範圍憑證 (DSC)。

DSC 會儲存連線至外部資料來源所需的驗證資訊。這些憑證會以資料庫主金鑰 (DMK) 加密。

DMK 是資料庫內加密階層的根，可保護資料庫範圍憑證中的密碼。這項對稱金鑰會由服務主金鑰 (SMK) 和使用者提供的密碼加密。DMK 不會自動建立，使用者必須使用 T-SQL 明確管理。

SMK 是特定 SQL Server 執行個體的加密階層根層級。SQL Server 首次啟動時，會自動產生這個對稱式金鑰，並用於保護其他安全物件，例如 DMK 和登入密碼。

Oracle

在 SQL Server 中完成下列步驟。

1. 為 Oracle 建立資料庫範圍憑證 (DSC)。

   1. 如果沒有，請切換至使用者資料庫，建立主金鑰：

      USE USER_DB;
      CREATE MASTER KEY ENCRYPTION BY PASSWORD='MK_PASSWORD';
      

      更改下列內容：

      • USER_DB：您要用來建立主金鑰的使用者資料庫。 例如：polybasedb。
      • MK_PASSWORD：要使用的主金鑰密碼。請務必儲存主金鑰的密碼，以備日後使用。

   2. 為 Oracle 資料來源建立資料庫範圍的認證。

      CREATE DATABASE SCOPED CREDENTIAL DB_CREDENTIAL_NAME WITH IDENTITY = 'EXTERNAL_DATABASE_USERNAME', SECRET = 'EXTERNAL_DATABASE_PASSWORD';
      

      更改下列內容：

      • DB_CREDENTIAL_NAME：您要用於外部資料庫的憑證名稱。
      • EXTERNAL_DATABASE_USERNAME：您要用來存取外部資料庫的使用者名稱。
      • EXTERNAL_DATABASE_PASSWORD：外部資料庫的密碼。外部資料來源憑證會在輪替時更新，且必須由您手動管理。

2. 為 Oracle 建立外部資料來源。

   CREATE EXTERNAL DATA SOURCE DATA_SOURCE_NAME
   WITH (
       LOCATION = 'oracle://SERVER_IP:PORT',
       PUSHDOWN = PUSHDOWN,
       CREDENTIAL = DB_CREDENTIAL_NAME
   );
   

   更改下列內容：

   • DATA_SOURCE_NAME：您要用於外部資料庫的名稱。
   • SERVER_IP：伺服器的 IP 或主機名稱。
   • PORT：要使用的通訊埠。
   • PUSHDOWN：必填，下推是 SQL Server 提供的一項功能，必須進行設定。視用途而定，將值設為 ON 或 OFF。
   • DB_CREDENTIAL_NAME：您剛建立的憑證。

3. 建立外部資料表。

   定義外部資料的結構定義和結構：

   • 建立外部資料表定義。結構定義必須與遠端資料表相符。
   • 使用適當的 SQL Server 資料類型和定序定義資料欄。
   • 位置是遠端資料表路徑。

   CREATE EXTERNAL TABLE EXTERNAL_TABLE_NAME(
       COLUMN_NAME_1 DATA_TYPE_1 COLLATE COLLATION_1,
       COLUMN_NAME_2 DATA_TYPE_2 COLLATE COLLATION_2,
       ...
   )
   WITH(
       LOCATION = 'REMOTE_DATABASE.REMOTE_USERNAME.REMOTE_TABLE_NAME',
       DATA_SOURCE = DB_DATA_SOURCE_NAME
   );
   

   更改下列內容：

   • EXTERNAL_TABLE_NAME：要建立的外部資料表名稱。
   • COLUMN_NAME_1：資料表第一欄的名稱。
   • DATA_TYPE_1：第一個資料欄的資料類型。
   • COLLATION_1：要用於第一個資料欄的對照順序。
   • COLUMN_NAME_2：資料表的第二欄名稱。
   • DATA_TYPE_2：第二欄的資料類型。
   • COLLATION_2：要用於第二個資料欄的對照順序。
   • REMOTE_DATABASE：遠端資料庫的名稱。
   • REMOTE_USERNAME：遠端使用者的使用者名稱。
   • REMOTE_TABLE_NAME：遠端資料表的名稱。
   • DB_DATA_SOURCE_NAME：外部資料來源的名稱。

4. 查詢您建立的外部 Oracle 資料表。

   SELECT TOP 10 * FROM EXTERNAL_TABLE_NAME;
   

   更改下列內容：

   • EXTERNAL_TABLE_NAME：要查詢的外部資料表名稱。

Cloud Storage

在 SQL Server 中完成下列步驟。

1. 為 Cloud Storage 建立資料庫範圍的憑證。

   1. 如果沒有，請切換至使用者資料庫，建立主金鑰：

      USE USER_DB;
      CREATE MASTER KEY ENCRYPTION BY PASSWORD='MK_PASSWORD';
      

      更改下列內容：

      • USER_DB：您要用來建立主金鑰的使用者資料庫。 例如：polybasedb。
      • MK_PASSWORD：您要用於主鍵的密碼。請務必儲存主金鑰的密碼，以備日後使用。

   2. Cloud Storage 需要存取金鑰 ID 和私密金鑰 ID：

      CREATE DATABASE SCOPED CREDENTIAL STORAGE_CREDENTIAL_NAME WITH IDENTITY = 'S3 Access Key', SECRET = 'ACCESS_KEY_ID:SECRET_KEY_ID';
      

      更改下列內容：

      • STORAGE_CREDENTIAL_NAME：要使用的 Cloud Storage 憑證名稱。
      • ACCESS_KEY_ID：存取金鑰 ID。
      • SECRET_KEY_ID：私密金鑰 ID。

      系統僅支援基本驗證。

2. 建立外部資料來源。

   CREATE EXTERNAL DATA SOURCE STORAGE_DATA_SOURCE_NAME
   WITH (
       LOCATION = 's3://storage.googleapis.com/',
       CREDENTIAL = STORAGE_CREDENTIAL_NAME
   );
   

   更改下列內容：

   • STORAGE_DATA_SOURCE_NAME：Cloud Storage 中的外部資料來源名稱。
   • STORAGE_CREDENTIAL_NAME：外部儲存空間資源的憑證名稱。

3. 為 Cloud Storage 建立外部資料表。

   定義外部資料的格式和結構：

   • 建立外部檔案。結構定義必須與檔案結構相符。
   • 使用適當的 SQL Server 資料類型和定序定義資料欄。
   • 位置是資料來源中的路徑。

   CREATE EXTERNAL FILE FORMAT FILE_FORMAT_NAME
   WITH (
       FORMAT_TYPE = FORMAT_TYPE,
       FORMAT_OPTIONS (
           FIELD_TERMINATOR = 'FIELD_TERMINATOR',
           STRING_DELIMITER = 'DELIMITER',
           FIRST_ROW = FIRST_ROW
       )
   );
   
   CREATE EXTERNAL TABLE FILE_EXTERNAL_TABLE_NAME (
       COLUMN_NAME_1 DATA_TYPE_1,
       COLUMN_NAME_2 DATA_TYPE_2,
       ...
   )
   WITH (
       LOCATION = 'PATH_TO_BUCKET/FILENAME',
       DATA_SOURCE = STORAGE_DATA_SOURCE_NAME,
       FILE_FORMAT = FILE_FORMAT_NAME
   );
   

   更改下列內容：

   • FILE_FORMAT_NAME：要建立的外部檔案格式名稱。
   • FORMAT_TYPE：要使用的格式類型，例如 DELIMITEDTEXT。 如需支援的值清單，請參閱「支援的檔案格式」。
   • FIELD_TERMINATOR：要使用的欄位分隔符號。
   • DELIMITER：要使用的字串分隔符號。
   • FIRST_ROW：要開始讀取的資料列。
   • FILE_EXTERNAL_TABLE_NAME：要建立的外部資料表名稱。
   • COLUMN_NAME_1：資料表第一欄的名稱。
   • DATA_TYPE_1：第一個資料欄的資料類型。
   • COLUMN_NAME_2：資料表的第二欄名稱。
   • DATA_TYPE_2：第二欄的資料類型。
   • PATH_TO_BUCKET：Cloud Storage bucket 的完整路徑。
   • FILENAME：Cloud Storage bucket 中的檔案名稱。
   • STORAGE_DATA_SOURCE_NAME：Cloud Storage 中的外部資料來源名稱。
   • FILE_FORMAT_NAME：外部檔案格式的名稱。

4. 查詢您建立的外部 Cloud Storage 資料表。

   SELECT * FROM FILE_EXTERNAL_TABLE_NAME;
   

   更改下列內容：

   • FILE_EXTERNAL_TABLE_NAME：要查詢的外部資料表名稱。

在資料庫還原作業後管理資料庫主金鑰

用於存取外部資料來源的資料庫範圍憑證，會使用資料庫主金鑰 (DMK) 加密。

DMK 會使用來源執行個體的服務主金鑰 (SMK) 和使用者提供的 DMK 密碼加密。在來源執行個體上，SQL Server 會使用 SMK 靜態開啟 DMK。

如果您還原 SQL Server 適用的 Cloud SQL 執行個體，由於 SMK 發生變更，DMK 不會在新的執行個體上自動開啟。

因此，您必須先使用建立 DMK 時設定的使用者密碼解密，再以目標執行個體 SMK 重新加密。

詳情請參閱下列 Microsoft 資源：

• 使用密碼開啟資料庫主金鑰
• 輪替資料庫主金鑰

記錄

Logs Explorer提供下列 PolyBase 記錄：

• Polybase_Dms_errors.log
• Polybase_Dms_movement.log
• Polybase_DWEngine_errors.log
• Polybase_DWEngine_movement.log
• Polybase_DWEngine_server.log

詳情請參閱 Microsoft 說明文件，瞭解如何排解 PolyBase 問題。

後續步驟

• SQL Server 適用的 Cloud SQL 支援 PolyBase
• 啟用及停用 PolyBase