共用方式為

教學課程：使用受控識別載入外部數據

  • 發行項

本文說明如何使用受控識別，從 Azure Data Lake Storage （ADLS） Gen2 帳戶建立外部數據表或內嵌數據。

必要條件

完成本教學課程需要下列資源：

  • Azure Data Lake Storage （ADLS） Gen2 帳戶
  • Azure Synapse Analytics 工作區和專用 SQL 集區

為工作區身分識別提供記憶體帳戶的存取權

每個 Azure Synapse Analytics 工作區都會自動建立受控識別，以協助您設定從工作區安全地存取外部數據。 若要深入瞭解 Azure Synapse Analytics 的受控識別，請瀏覽 適用於 Azure Synapse Analytics 的受控服務識別。

若要讓受控識別存取 ADLS Gen2 帳戶上的數據，您必須提供來源帳戶的身分識別存取權。 若要授與適當的許可權，請遵循下列步驟：

  1. 在 Azure 入口網站 中，尋找您的記憶體帳戶。
  2. 選取 [資料記憶體 -> 容器]，然後流覽至外部數據表需要存取之源數據的資料夾。
  3. 選取 [存取控制 (IAM)]。
  4. 選取 [新增 -> 新增角色指派]。
  5. 在作業函式角色清單中，選取 [記憶體 Blob 數據參與者 ]，然後選取 [ 下一步]。
  6. 在 [ 新增角色指派 ] 頁面中，選取 [ + 選取成員]。 [ 選取成員 ] 窗格隨即開啟。
  7. 輸入工作區身分識別的名稱。 工作區身分識別與您的工作區名稱相同。 顯示時，挑選您的工作區身分識別，然後 選取。
  8. 在 [ 新增角色指派 ] 頁面中，確定 [成員] 列表包含您所需的Microsoft Entra ID 帳戶。 驗證之後，選取 [ 檢閱 + 指派]。
  9. 在確認頁面中，檢閱變更，然後選取 [ 檢閱 + 指派]。

您的工作區身分識別現在是記憶體 Blob 數據參與者角色的成員，而且可以存取源資料夾。

注意

這些步驟也適用於設定為限制公用存取的安全 ADLS Gen2 帳戶。 若要深入瞭解如何保護您的 ADLS Gen2 帳戶，請參閱設定 Azure 儲存體 防火牆和虛擬網路。

使用 COPY INTO 內嵌數據

T-SQL COPY INTO 語句提供彈性、高輸送量的數據擷取至數據表，是將數據內嵌到專用 SQL 集區數據表的主要策略。 COPY INTO 可讓使用者從外部位置擷取數據，而不需要建立外部數據表所需的任何額外資料庫物件。

COPY INTO若要使用工作區受控識別來執行 語句以進行驗證，請使用下列 T-SQL 命令：

COPY INTO <TableName>
FROM 'https://<AccountName>.dfs.core.windows.net/<Container>/<Folder>/ '
WITH
(
    CREDENTIAL = (IDENTITY = 'Managed Identity'),
    [<CopyIntoOptions>]
);

其中：

  • <TableName> 是將數據內嵌至其中之數據表的名稱
  • <AccountName> 是您的 ADLS Gen2 帳戶名稱
  • <Container> 是記憶體帳戶中儲存源數據之容器的名稱
  • <Folder> 是儲存在您容器內源資料的資料夾 （或路徑） 。 如果直接指向單一檔案，您也可以提供檔名。
  • <CopyIntoOptions> 是您想要提供給 COPY INTO 語句的任何其他選項清單。

若要深入瞭解並探索 COPY INTO 的完整語法，請參閱 COPY INTO （Transact-SQL） 。

使用外部數據表查詢 ADLS Gen2 上的資料

外部數據表可讓使用者查詢來自 Azure Data Lake Storage （ADLS） Gen2 帳戶的數據，而不需要先內嵌數據。 使用者可以建立指向 ADLS Gen2 容器上檔案的外部數據表，並查詢它就像一般用戶數據表一樣。

下列步驟說明使用受控識別進行驗證，建立指向 ADLS Gen2 數據的新外部數據表的程式。

建立必要的資料庫物件

外部資料表需要建立下列物件：

  1. 加密資料庫範圍認證秘密的資料庫主要金鑰
  2. 使用工作區身分識別的資料庫範圍認證
  3. 指向源資料夾的外部資料來源
  4. 定義來源檔案格式的外部檔案格式
  5. 用於查詢的外部資料表定義

若要遵循這些步驟，請使用 Azure Synapse 工作區中的 SQL 編輯器，或連線至專用 SQL 集區的慣用 SQL 用戶端。 讓我們詳細查看這些步驟。

建立資料庫主要金鑰

資料庫主要金鑰是對稱金鑰，用來保護資料庫記憶體的憑證和非對稱密鑰的私鑰，以及資料庫範圍認證中的秘密。 如果資料庫中已經有主要金鑰，就不需要建立新的金鑰。 以安全密碼取代 <Secure Password> 。 此密碼是用來加密資料庫中的主要金鑰。

若要建立主要密鑰，請使用下列 T-SQL 命令：

-- Replace <Secure Password> with a secure password
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Secure Password>';

若要深入了解資料庫主要密鑰，請參閱 CREATE MASTER KEY （Transact-SQL）。

建立資料庫範圍認證

資料庫範圍認證會使用您的工作區身分識別，而且每當外部數據表需要存取源數據時，都需要存取外部位置。

若要建立資料庫範圍認證，請使用下列命令。 將取代 <CredentialName> 為您想要用於資料庫範圍認證的名稱。

CREATE DATABASE SCOPED CREDENTIAL <CredentialName> WITH IDENTITY = 'Managed Service Identity';

若要深入瞭解資料庫範圍認證，請參閱 CREATE DATABASE SCOPED CREDENTIAL （Transact-SQL）。

建立外部資料來源

下一個步驟是建立外部數據源，指定外部數據表所使用的源數據所在的位置。

若要建立外部數據源，請使用下列 T-SQL 命令：

CREATE EXTERNAL DATA SOURCE <ExternalDataSourceName>
WITH (
    TYPE = HADOOP,
    LOCATION = 'abfss://<Container>@<AccountName>.dfs.core.windows.net/<Folder>/',
    CREDENTIAL = <CredentialName>
);

其中：

  • <ExternalDataSourceName> 是您要用於外部數據來源的名稱。
  • <AccountName> 是您的 ADLS Gen2 帳戶名稱。
  • <Container> 是記憶體帳戶中儲存源數據之容器的名稱。
  • <Folder> 是儲存在您容器內源資料的資料夾 （或路徑） 。 如果直接指向單一檔案，您也可以提供檔名。
  • <Credential>是您稍早建立的資料庫範圍認證名稱。

若要深入瞭解外部數據源，請參閱 CREATE EXTERNAL DATA SOURCE （Transact-SQL）。

建立外部檔案格式

下一個步驟是建立外部文件格式。 它會指定外部數據表所參考數據的實際配置。

若要建立外部檔案格式，請使用下列 T-SQL 命令。 將取代 <FileFormatName> 為您想要用於外部檔案格式的名稱。

CREATE EXTERNAL FILE FORMAT <FileFormatName>
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (
        FIELD_TERMINATOR = ',',
        STRING_DELIMITER = '"',
        FIRST_ROW = 2,
        USE_TYPE_DEFAULT = True
    )
);

在此範例中，請根據您的源數據，視需要調整參數，例如 FIELD_TERMINATOR、STRING_DELIMITERFIRST_ROW、 和其他參數。 如需更多格式設定選項，並深入瞭解 EXTERNAL FILE FORMAT，請參閱 CREATE EXTERNAL FILE FORMAT。

建立外部資料表

現在已建立保存元數據以安全地存取外部數據的所有必要對象，現在可以建立外部數據表。 若要建立外部數據表，請使用下列 T-SQL 命令：

-- Adjust the table name and columns to your desired name and external table schema
CREATE EXTERNAL TABLE <ExternalTableName> (
    Col1 INT,
    Col2 NVARCHAR(100),
    Col4 INT
)
WITH
(
    LOCATION = '<Path>',
    DATA_SOURCE = <ExternalDataSourceName>,
    FILE_FORMAT = <FileFormatName>
);

其中：

請務必將數據表名稱和架構調整為所需的名稱和來源檔案中數據的架構。

查詢外部數據表

此時，會建立存取外部數據表所需的所有元數據。 若要測試外部數據表，請使用下列 T-SQL 範例之類的查詢來驗證您的工作：

SELECT TOP 10 Col1, Col2 FROM <ExternalTableName>;

如果一切都已正確設定，您應該會看到源數據中的數據，因為此查詢的結果。

若要深入瞭解並探索 的完整語法CREATE EXTERNAL TABLE，請參閱 CREATE EXTERNAL TABLE （Transact-SQL）。

相關內容