将数据加载到 Azure Synapse Analytics 中的专用 SQL 池的最佳做法

在本文中，你将找到用于加载数据的建议和性能优化。

若要最大程度地减少延迟，请将存储层和专用 SQL 池并置。

将数据导出到 ORC 文件格式时，当存在大型文本列时，可能会出现 Java 内存不足错误。 若要解决此限制方面的问题，请仅导出列的一个子集。

PolyBase 无法加载数据超过 1,000,000 字节的行。 将数据放入 Azure Blob 存储或 Azure 数据湖存储中的文本文件时，数据量必须少于 1,000,000 字节。 无论表架构如何，此字节限制都是真实的。

所有文件格式都具有不同的性能特征。 若要获得最快的加载速度，请使用压缩分隔的文本文件。 UTF-8 和 UTF-16 性能之间的差异最小。

将大型压缩文件拆分为较小的压缩文件。

为了提高加载速度，一次只运行一个加载作业。 如果这不可行，请同时运行最少数量的负载。 如果预期有大型加载作业，请考虑在加载之前纵向扩展专用 SQL 池。

若要使用适当的计算资源运行负载，请创建为运行负载指定的加载用户。 将每个加载用户分配到特定的资源类或工作负荷组。 若要运行负载，请以加载用户之一身份登录，然后运行负载。 负载使用用户的资源类运行。 此方法比尝试更改用户的资源类来适应当前资源类的需求更简单。

此示例创建分类到特定工作负荷组的加载用户。 第一步是 连接到 master 并创建登录名。

   -- Connect to master
   CREATE LOGIN loader WITH PASSWORD = 'a123STRONGpassword!';

连接到专用 SQL 池并创建用户。 以下代码假定已连接到名为 mySampleDataWarehouse 的数据库。 它演示如何创建名为 loader 的用户，并向用户授予使用 COPY 语句创建表和加载的权限。 然后将用户分类为具有最大资源的 DataLoads 工作负荷组。

   -- Connect to the dedicated SQL pool
   CREATE USER loader FOR LOGIN loader;
   GRANT ADMINISTER DATABASE BULK OPERATIONS TO loader;
   GRANT INSERT ON <yourtablename> TO loader;
   GRANT SELECT ON <yourtablename> TO loader;
   GRANT CREATE TABLE TO loader;
   GRANT ALTER ON SCHEMA::dbo TO loader;
   
   CREATE WORKLOAD GROUP DataLoads
   WITH ( 
       MIN_PERCENTAGE_RESOURCE = 0
       ,CAP_PERCENTAGE_RESOURCE = 100
       ,REQUEST_MIN_RESOURCE_GRANT_PERCENT = 100
	);

   CREATE WORKLOAD CLASSIFIER [wgcELTLogin]
   WITH (
	     WORKLOAD_GROUP = 'DataLoads'
       ,MEMBERNAME = 'loader'
   );

若要使用加载工作负荷组的资源运行负载，请以加载程序身份登录并运行负载。

通常需要让多个用户将数据加载到数据仓库中。 使用 CREATE TABLE AS SELECT（Transact-SQL） 进行加载需要数据库的 CONTROL 权限。 CONTROL 权限授予对所有架构的控制访问权限。 你可能不希望所有加载用户对所有架构具有控制访问权限。 若要限制权限，请使用 DENY CONTROL 语句。

例如，考虑为部门 A 使用数据库架构 schema_A，为部门 B 使用 schema_B；让数据库用户 user_A 和 user_B 分别作为部门 A 和 B 中加载的 PolyBase 用户。 它们都被授予 CONTROL 数据库权限。 架构 A 和 B 的创建者现在使用 DENY 锁定其架构：

   DENY CONTROL ON SCHEMA :: schema_A TO user_B;
   DENY CONTROL ON SCHEMA :: schema_B TO user_A;

User_A和user_B现在被锁定在其他部门架构之外。

若要实现将数据移到数据仓库表中的最快加载速度，将数据加载到临时表中。 将临时表定义为堆，并将轮循机制用于分发选项。

可以认为，加载通常是一个两步的过程：首先将数据加载到临时表中，然后将数据插入生产数据仓库表中。 如果生产表使用哈希分布，则如果将暂存表也定义为哈希分布，加载和插入的总时间可能会更快。 加载到临时表需要的时间较长，但第二步将行插入到生产表中不会导致数据跨分布区移动。

列存储索引需要大量的内存来将数据压缩为高质量的行组。 为了获得最佳压缩和索引效率，列存储索引需要将最多 1,048,576 行压缩到每个行组中。 出现内存压力时，列存储索引可能无法达到最大压缩率。 这会影响查询性能。 有关深入探讨，请参阅 列存储内存优化。

• 若要确保加载用户有足够的内存来实现最大压缩率，请使用作为中型或大型资源类成员的加载用户。
• 加载足够数量的行，以完全填满新的行组。 在大容量加载期间，数据会以 1,048,576 行为一个完整的行组直接压缩到列存储中。 不到 102,400 行的加载会将行发送到增量存储中以 B 树索引的形式保存。 如果加载的行太少，这些行可能会全部进入增量存储中，不会立即压缩成列存储格式。

使用 COPY 语句进行加载将为专用 SQL 池提供最高吞吐量。 如果无法使用 COPY 加载，并且必须使用 SqLBulkCopy API 或 bcp，应考虑增加批大小以提高吞吐量。

使用外部表的负载可能会失败，并出现错误“查询中止--从外部源读取时达到最大拒绝阈值”。 此消息指示您的外部数据包含错误记录。 如果数据类型和列数与外部表的列定义不匹配，或者数据不符合指定的外部文件格式，则数据记录被视为脏记录。

若要修复脏记录，请确保外部表和外部文件格式定义正确，并且外部数据符合这些定义。 如果外部数据记录的子集是脏的，则可以选择使用 “CREATE EXTERNAL TABLE” 中的拒绝选项拒绝这些查询记录。

可以使用 INSERT 语句将数据一次性加载到小型表中，甚至可以使用 INSERT INTO MyLookup VALUES (1, 'Type 1') 之类的语句定期重新加载某个查找。 但是，单独插入的效率不如执行大容量加载的效率。

如果一天中有成千上万的单个插入，可将插入成批进行大容量加载。 开发你们的流程，以将单个插入项追加到一个文件中，然后创建另一项定期载入文件的流程。

为了提高查询性能，必须在第一次加载后针对所有表的所有列创建统计信息，或者在数据中发生重大更改。 可以手动创建统计信息，也可以启用 自动创建统计信息。

有关统计信息的详细说明，请参阅 统计信息。 以下示例演示如何在Customer_Speed表的五列上手动创建统计信息。

create statistics [SensorKey] on [Customer_Speed] ([SensorKey]);
create statistics [CustomerKey] on [Customer_Speed] ([CustomerKey]);
create statistics [GeographyKey] on [Customer_Speed] ([GeographyKey]);
create statistics [Speed] on [Customer_Speed] ([Speed]);
create statistics [YearMeasured] on [Customer_Speed] ([YearMeasured]);

好的安全做法是定期更改 Blob 存储的访问密钥。 Blob 存储帐户有两个存储密钥，可用于转换密钥。

若要轮换 Azure 存储帐户密钥，请执行以下作：

对于每个已更改密钥的存储帐户，请发出 ALTER DATABASE SCOPED CREDENTIAL 命令。

示例：

创建原始密钥

CREATE DATABASE SCOPED CREDENTIAL my_credential WITH IDENTITY = 'my_identity', SECRET = 'key1'

将密钥从密钥 1 切换为密钥 2

ALTER DATABASE SCOPED CREDENTIAL my_credential WITH IDENTITY = 'my_identity', SECRET = 'key2'

无需对基础外部数据源进行其他更改。

• 若要详细了解 PolyBase 并设计提取、加载和转换（ELT）过程，请参阅 Azure Synapse Analytics 的设计 ELT。
• 有关加载教程， 请使用 PolyBase 将数据从 Azure Blob 存储加载到 Azure Synapse Analytics。
• 若要监视数据加载，请参阅 使用 DMV 监视工作负荷。