使用 Synapse SQL 安全地加载数据

本文重点介绍 COPY 语句的安全身份验证机制，并提供示例。 COPY 语句是在 Synapse SQL 中批量加载数据的最灵活且安全的方法。

支持的身份验证机制

下表介绍了每种文件类型和存储帐户所支持的身份验证方法。 此信息适用于源存储位置和错误文件位置。

1：此身份验证方法需要在您的外部路径中使用 .blob 终结点 (.blob.core.chinacloudapi.cn)。

2：对于此身份验证方法，外部位置路径中必须包含 .dfs 终结点 (.dfs.core.chinacloudapi.cn)。

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.chinacloudapi.cn/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)
)

B. 使用 CRLF 作为行终止符的共享访问签名 (SAS)（Windows 样式新行）

COPY INTO target_table
FROM 'https://adlsgen2account.dfs.core.chinacloudapi.cn/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.
)

C. 托管标识

将存储帐户附加到 VNet 时，需要使用托管身份进行身份验证。

先决条件

1. 安装 Azure PowerShell。 请参阅安装 PowerShell。
2. 如果有常规用途 v1 或 Blob 存储帐户，则必须先升级到常规用途 v2 帐户。 请参阅升级到常规用途 v2 存储帐户。
3. 必须在 Azure 存储帐户的“防火墙和虚拟网络”设置菜单下启用“允许受信任的 Microsoft 服务访问此存储帐户”。 请参阅配置 Azure 存储防火墙和虚拟网络。

步骤

1. 如果你有独立的专用 SQL 池，请使用 PowerShell 向 Microsoft Entra ID 注册 SQL Server：

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

   对于 Synapse 工作区中的专用 SQL 池，不需要此步骤。 工作区的系统分配托管标识 (SA-MI) 是 Synapse 管理员角色的成员，因此对工作区的专用 SQL 池拥有更高的权限。

2. 创建常规用途 v2 存储帐户。 有关详细信息，请参阅创建存储帐户。

   注意

   • 如果有常规用途 v1 或 Blob 存储帐户，则必须先升级到 v2 帐户。 有关详细信息，请参阅升级到常规用途 v2 存储帐户。
   • 有关 Azure Data Lake Storage Gen2 的已知问题，请参阅 Azure Data Lake Storage Gen2 的已知问题。

3. 在存储帐户下，选择“访问控制(IAM)”。

4. 选择添加>后，再选择添加角色分配，即可打开“添加角色分配”页面。

5. 分配以下角色。 有关详细步骤，请参阅使用 Azure 门户分配 Azure 角色。

   设置	值
   角色	存储 Blob 数据参与者
   将访问权限分配到	SERVICEPRINCIPAL
   成员	托管使用 Microsoft Entra ID 注册的专用 SQL 池的服务器或工作区

   

   注意

   只有具有“所有者”特权的成员能够执行此步骤。 有关各种 Azure 内置角色，请参阅 Azure 内置角色。

   重要

   指定存储 Blob 数据“所有者”、“参与者”或“读取者”Azure 角色。 这些角色不同于所有者、参与者和读取者 Azure 内置角色。

   

6. 现在可以运行指定“托管标识”的 COPY 语句：

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

D. Microsoft Entra 身份验证

步骤

1. 在存储帐户下，选择“访问控制(IAM)”。

2. 选择添加>后，再选择添加角色分配，即可打开“添加角色分配”页面。

3. 分配以下角色。 有关详细步骤，请参阅使用 Azure 门户分配 Azure 角色。

   设置	值
   角色	存储 Blob 数据所有者、参与者或读取者
   将访问权限分配到	USER
   成员	Microsoft Entra 用户

   

   重要

   指定存储 Blob 数据“所有者”、“参与者”或“读取者”Azure 角色。 这些角色不同于所有者、参与者和读取者 Azure 内置角色。

   

4. 配置 Microsoft Entra 身份验证。 请参阅如何使用 Azure SQL 配置和管理 Microsoft Entra 身份验证。

5. 使用 Active Directory 连接到 SQL 池，现在可以在其中运行 COPY 语句，而无需指定任何凭据：

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

后续步骤

• 若要了解详细语法，请查看 COPY 语句一文
• 若要了解加载的最佳做法，请查看数据加载概述一文