使用 Synapse SQL 安全地載入資料

  • 發行項

本文強調並提供 COPY 語句 安全驗證機制的 範例。 COPY 語句是 Synapse SQL 中大量載入資料的最彈性且最安全的方式。

支援的驗證機制

下列矩陣描述每個檔案類型和儲存體帳戶支援的驗證方法。 這適用于來源儲存位置和錯誤檔案位置。

CSV Parquet ORC
Azure Blob 儲存體 SAS/MSI/SERVICE PRINCIPAL/KEY/AAD SAS/KEY SAS/KEY
Azure Data Lake Gen2 SAS/MSI/SERVICE PRINCIPAL/KEY/AAD SAS (blob1)/MSI (dfs2)/SERVICE PRINCIPAL/KEY/AAD SAS (blob1)/MSI (dfs2)/SERVICE PRINCIPAL/KEY/AAD

1：此驗證方法需要外部位置路徑中的 .blob 端點 （ .blob.core.windows.net ）。

2：此驗證方法需要外部位置路徑中的 .dfs 端點 （ .dfs.core.windows.net ）。

A. 儲存體以 LF 作為資料列結束字元的帳戶金鑰 （Unix 樣式的新行）

--Note when specifying the column list, input field numbers start from 1
COPY INTO target_table (Col_one default 'myStringDefault' 1, Col_two default 1 3)
FROM 'https://adlsgen2account.dfs.core.windows.net/myblobcontainer/folder1/'
WITH (
    FILE_TYPE = 'CSV'
    ,CREDENTIAL=(IDENTITY= 'Storage Account Key', SECRET='<Your_Account_Key>')
    --CREDENTIAL should look something like this:
    --CREDENTIAL=(IDENTITY= 'Storage Account Key', SECRET='<secret>'),
    ,ROWTERMINATOR='0x0A' --0x0A specifies to use the Line Feed character (Unix based systems)
)

重要

  • 使用十六進位值 （0x0A） 來指定換行字元/分行符號。 請注意，COPY 語句會將字串解譯 \n 為 \r\n （歸位字元分行符號）。

B. 以 CRLF 作為資料列結束字元的共用存取簽章 （SAS） （Windows 樣式新行）

COPY INTO target_table
FROM 'https://adlsgen2account.dfs.core.windows.net/myblobcontainer/folder1/'
WITH (
    FILE_TYPE = 'CSV'
    ,CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='<Your_SAS_Token>')
    --CREDENTIAL should look something like this:
    --CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='?sv=2018-03-28&ss=bfqt&srt=sco&sp=rl&st=2016-10-17T20%3A14%3A55Z&se=2021-10-18T20%3A19%3A00Z&sig=IEoOdmeYnE9%2FKiJDSFSYsz4AkN'),
    ,ROWTERMINATOR='\n'-- COPY command automatically prefixes the \r character when \n (newline) is specified. This results in carriage return newline (\r\n) for Windows based systems.
)

重要

請勿將 指定 ROWTERMINATOR 為 '\r\n'，這會解譯為 '\r\r\n'，而且可能會導致剖析問題。 當指定 \n （newline） 時，COPY 命令會自動在 \r 字元前面加上前置詞。 這會導致 Windows 型系統的歸位分行符號分行符號 （\r\n）。

C. 受控識別

當您的儲存體帳戶連結至 VNet 時，需要受控識別驗證。

必要條件

  1. 安裝 Azure PowerShell。 請參閱安裝 PowerShell 。
  2. 如果您有一般用途 v1 或 Blob 儲存體帳戶，您必須先升級至一般用途 v2。 請參閱升級至一般用途 v2 儲存體帳戶 。
  3. 您必須在 [Azure 儲存體 帳戶防火牆和虛擬網路 設定] 功能表下開啟 [允許信任Microsoft 服務存取此儲存體帳戶 。 請參閱設定Azure 儲存體防火牆和虛擬網路 。

步驟

  1. 如果您有獨立的專用 SQL 集區，請使用 PowerShell 向 Microsoft Entra ID 註冊 SQL 伺服器：

    Connect-AzAccount
Select-AzSubscription -SubscriptionId <subscriptionId>
Set-AzSqlServer -ResourceGroupName your-database-server-resourceGroup -ServerName your-SQL-servername -AssignIdentity

    Synapse 工作區內的專用 SQL 集區不需要此步驟。 工作區的系統指派受控識別 （SA-MI） 是 Synapse 管理員istrator 角色的成員，因此已提高工作區專用 SQL 集區的許可權。

  2. 建立一 般用途 v2 儲存體 帳戶 。 如需詳細資訊，請參閱 建立儲存體帳戶 。

    注意

  3. 在您的儲存體帳戶下，選取 [ 存取控制][IAM]。

  4. 選取 [新增 > 角色指派 ] 以開啟 [新增角色指派] 頁面。

  5. 指派下列角色。 如需詳細步驟，請參閱使用 Azure 入口網站指派 Azure 角色。

    設定 值
    角色 儲存體 Blob 資料參與者
    存取權指派對象 SERVICEPRINCIPAL
    成員 裝載您已向 Microsoft Entra ID 註冊的專用 SQL 集區的伺服器或工作區

    Add role assignment page in Azure portal.

    注意

    僅有具備「擁有者」權限的成員才能執行此步驟。 如需了解各種 Azure 內建角色，請參閱 Azure 內建角色。

    重要

    指定 儲存體 Blob 資料 擁有者、參與者或讀者 Azure 角色。 這些角色與擁有者、參與者和讀者的 Azure 內建角色不同。

    Granting Azure RBAC permission to load

  6. 您現在可以執行指定「受控識別」的 COPY 語句：

    COPY INTO dbo.target_table
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/*.txt'
WITH (
    FILE_TYPE = 'CSV',
    CREDENTIAL = (IDENTITY = 'Managed Identity'),
)

D. Microsoft Entra 驗證

步驟

  1. 在您的儲存體帳戶下，選取 [ 存取控制][IAM]。

  2. 選取 [新增 > 角色指派 ] 以開啟 [新增角色指派] 頁面。

  3. 指派下列角色。 如需詳細步驟，請參閱使用 Azure 入口網站指派 Azure 角色。

    設定 值
    角色 儲存體 Blob 資料擁有者、參與者或讀取者
    存取權指派對象 USER
    成員 Microsoft Entra 使用者

    Add role assignment page in Azure portal.

    重要

    指定 儲存體 Blob 資料 擁有者、參與者或讀者 Azure 角色。 這些角色與擁有者、參與者和讀者的 Azure 內建角色不同。

    Granting Azure RBAC permission to load

  4. 設定 Microsoft Entra 驗證。 請參閱 使用 Azure SQL 設定及管理 Microsoft Entra 驗證。

  5. 使用 Active Directory 連線 SQL 集區，您現在可以執行 COPY 語句，而不需要指定任何認證：

    COPY INTO dbo.target_table
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/*.txt'
WITH (
    FILE_TYPE = 'CSV'
)

E. 服務主體驗證

步驟

  1. 建立 Microsoft Entra 應用程式 。

  2. 取得應用程式識別碼 。

  3. 取得驗證金鑰 。

  4. 取得 V1 OAuth 2.0 權杖端點 。

  5. 將讀取、寫入和執行許可權指派給儲存體帳戶上的 Microsoft Entra 應用程式 。

  6. 您現在可以執行 COPY 語句：

    COPY INTO dbo.target_table
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder0/*.txt'
WITH (
    FILE_TYPE = 'CSV'
    ,CREDENTIAL=(IDENTITY= '<application_ID>@<OAuth_2.0_Token_EndPoint>' , SECRET= '<authentication_key>')
    --CREDENTIAL should look something like this:
    --,CREDENTIAL=(IDENTITY= '92761aac-12a9-4ec3-89b8-7149aef4c35b@https://login.microsoftonline.com/72f714bf-86f1-41af-91ab-2d7cd011db47/oauth2/token', SECRET='juXi12sZ6gse]woKQNgqwSywYv]7A.M')
)

重要

使用 OAuth 2.0 權杖端點的 V1 版本

下一步