通过

如何在复制活动中配置 Azure SQL 数据库

  • 项目

本文概述了如何使用数据管道中的复制活动从/向 Azure SQL 数据库复制数据。

支持的配置

有关复制活动下每个选项卡的配置，请分别转到以下各部分。

常规

若要配置“常规”设置选项卡，请参阅“常规”设置指导。

源

复制活动的“源”选项卡下的 Azure SQL 数据库支持以下属性。

显示“源”选项卡和属性列表的屏幕截图。

需要以下属性：

  • 数据存储类型：选择“外部”。
  • 连接：从连接列表中选择 Azure SQL 数据库连接。 如果连接不存在，则通过选择“新建”创建新的 Azure SQL 数据库连接。
  • 连接类型：选择“Azure SQL数据库”。
  • 表：从下拉列表中选择数据库中的表。 或者，选中“编辑”以手动输入表名称。
  • 预览数据：选择“预览数据”以预览表中的数据。

在“高级”下，可以指定以下字段：

  • 使用查询：可以选择“表”、“查询”或“存储过程”。 以下列表介绍了每个设置的配置：

    • 表：如果选择此按钮，则从表中指定的表中读取数据。

    • 查询：指定使用自定义 SQL 查询读取数据。 示例为 select * from MyTable。 或选择铅笔图标以在代码编辑器中编辑。

      显示选择查询的屏幕截图。

    • 存储过程：使用从源表读取数据的存储过程的名称。 最后一个 SQL 语句必须是存储过程中的 SELECT 语句。

      • 存储过程名称：选中“编辑”框以从源表读取数据时，选择存储过程或手动指定存储过程名称。

      • 存储过程参数：指定存储过程参数的值。 允许的值为名称或值对。 参数的名称和大小写必须与存储过程参数的名称和大小写匹配。

        显示存储过程设置的屏幕截图。

  • 查询超时（分钟）：指定查询命令执行的超时，默认值为 120 分钟。 如果为此属性设置了参数，则允许的值是时间跨度，例如“02:00:00”（120 分钟）。

    显示查询超时设置的屏幕截图。

  • 隔离级别：指定 SQL 源的事务锁定行为。 允许的值包括：None、ReadCommitted、ReadUncommitted、RepeatableRead、Serializable 或 Snapshot。 如果未指定，则会使用 None 隔离级别。 有关更多详细信息，请参阅 IsolationLevel Enum。

    显示隔离级别设置的屏幕截图。

  • 分区选项：指定用于从 Azure SQL 数据库加载数据的数据分区选项。 允许的值为：None（默认）、表的物理分区 和 动态范围。 启用分区选项时（即该选项不为 None），用于从 Azure SQL 数据库并行加载数据的并行度由复制活动上的并行复制设置控制。

    显示分区选项设置的屏幕截图。

    • None: 选择此设置则不使用分区。

    • 表的物理分区：使用物理分区时，将根据物理表定义自动确定分区列和机制。

    • 动态范围：使用启用了并行的查询时，则需要范围分区参数 (?DfDynamicRangePartitionCondition)。 示例查询：SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition。

      • 分区列名称：以整数类型、日期类型或日期/时间类型（int、smallint、bigint、date、smalldatetime、datetime、datetime2 或 datetimeoffset）指定源列的名称，范围分区将使用它进行并行复制。 如果未指定，系统会自动检测表的索引或主键并将其用作分区列。
      • 分区上限：指定分区范围拆分的分区列的最大值。 此值用于决定分区步幅，不用于筛选表中的行。 将对表或查询结果中的所有行进行分区和复制。
      • 分区下：指定分区范围拆分的分区列的最小值。 此值用于决定分区步幅，不用于筛选表中的行。 将对表或查询结果中的所有行进行分区和复制。

  • 其他列：将更多数据列添加到存储源文件的相对路径或静态值。 后者支持表达式。 有关详细信息，请转到在复制期间添加其他列。

目标

复制活动的“目标”选项卡下的 Azure SQL 数据库支持以下属性。

显示“目标”选项卡的屏幕截图。

需要以下属性：

  • 数据存储类型：选择“外部”。
  • 连接：从连接列表中选择 Azure SQL 数据库连接。 如果连接不存在，则通过选择“新建”创建新的 Azure SQL 数据库连接。
  • 连接类型：选择“Azure SQL数据库”。
  • 表：从下拉列表中选择数据库中的表。 或者，选中“编辑”以手动输入表名称。
  • 预览数据：选择“预览数据”以预览表中的数据。

在“高级”下，可以指定以下字段：

  • 写入行为：定义以基于文件的数据存储中的文件为源时的写入行为。 可以选择“插入”、“插入”或“存储过程”。

    写入行为选项卡的屏幕截图。

    • 插入：如果源数据有插入，请选择此选项。

    • 更新插入：如果源数据同时包含插入和更新，请选择此选项。

      • 使用 tempdb：指定是使用全局临时表还是物理表作为更新插入临时表。 默认情况下，服务使用全局临时表作为临时表，并会选中此复选框。

        显示选择“使用 TempDB”的屏幕截图

      • 选择用户 DB 架构：未选中“使用 TempDB”复选框时，如果使用的是物理表，请指定用于创建临时表的临时架构。

        注意

        必须具有创建和删除表的权限。 默认情况下，临时表将与目标表共享相同的架构。

        显示未选择“使用 TempDB”的屏幕截图

      • 键列：指定唯一行标识的列名称。 可使用单个键，也可使用一系列键。 如果未指定，将使用主键。

    • 存储过程：使用定义如何将源数据应用于目标表的存储过程的名称。 此存储过程由每个批处理调用。

      • 存储过程名称：选中“编辑”框以从源表读取数据时，选择存储过程或手动指定存储过程名称。

      • 存储过程参数：指定存储过程参数的值。 允许的值为名称或值对。 参数的名称和大小写必须与存储过程参数的名称和大小写匹配。

        显示存储过程设置的屏幕截图。

  • 大容量插入表锁：选择“是”或“否”。 使用此设置可提高在不包含多个客户端索引的表上执行大容量插入操作期间的复制性能。 有关详细信息，请转到 BULK INSERT (Transact SQL)

  • 表选项：指定在基于源架构的表不存在时是否 自动创建目标 表。 选择“无” 或“自动创建表”。 当目标指定存储过程时，不支持自动创建表。

  • 复制前脚本：指定每次运行时，复制活动将数据写入到目标表之前要执行的脚本。 可以使用此属性清除预加载的数据。

  • 写入批超时：指定超时前等待批插入操作完成的时间。允许的值为 timespan。 默认值为“00:30:00”（30 分钟）。

  • 写入批处理大小：指定每个批要插入到 SQL 表中的行数。 允许的值为 integer（行数）。 默认情况下，该服务根据行大小动态确定适当的批大小。

  • 最大并发连接：指定活动运行期间与数据存储建立的并发连接的上限。 仅在要限制并发连接时指定一个值。

  • 禁用性能指标分析：此设置用于收集复制性能优化的指标（如 DTU、DWU、RU 等）和建议。 如果对此行为有所担忧，请选中此复选框。

映射

对于“映射”选项卡配置，如果不将具有自动创建表的 Azure SQL 数据库应用为目的地，请转到映射。

如果将具有自动创建表的 Azure SQL 数据库应用为目的地，则除了映射中的配置外，可以编辑目的地列的类型。 选择“导入架构”后，可以在目标中指定列类型。

例如，源中 ID 列的类型为 int，当映射到目的地列时，你可以将其更改为浮点类型。

映射目标列类型的屏幕截图。

设置

对于“设置”选项卡配置，请转到“设置”选项卡下的“配置其他设置”。

从 Azure SQL 数据库进行并行复制

复制活动中的 Azure SQL 数据库连接器提供内置的数据分区，用于并行复制数据。 可以在复制活动的“源”表中找到数据分区选项。

启用分区复制时，复制活动将对 Azure SQL 数据库源运行并行查询，以按分区加载数据。 并行度由复制活动“设置”选项卡中的复制并行度控制。例如，如果将复制并行度设置为 4，则该服务会根据指定的分区选项和设置并行生成并运行 4 个查询，每个查询从 Azure SQL 数据库检索一部分数据。

建议同时启用并行复制和数据分区，尤其是从 Azure SQL 数据库加载大量数据时。 下面是适用于不同方案的建议配置。 将数据复制到基于文件的数据存储中时，建议将数据作为多个文件写入文件夹（仅指定文件夹名称），在这种情况下，性能优于写入单个文件。

方案 建议的设置
从包含物理分区的大型表进行完整加载。 分区选项：表的物理分区。

在执行期间，该服务将自动检测物理分区并按分区复制数据。

若要检查表是否有物理分区，可参考此查询。
从不包含物理分区但包含用于数据分区的整数或日期时间列的大型表进行完整加载。 分区选项：动态范围分区。
分区列（可选）：指定用于对数据进行分区的列。 如果未指定，将使用索引或主键列。
分区上限和分区下限（可选） ：指定是否要确定分区步幅。 这不适用于筛选表中的行，表中的所有行都将进行分区和复制。 如果未指定，复制活动会自动检测这些值。

例如，如果分区列“ID”的值范围为 1 至 100，并且将此值的下限设置为 20、上限设置为 80，并行复制设置为 4，服务将按 4 个分区（分区的 ID 范围分别为 <=20、[21, 50]、[51, 80] 和 >=81）检索数据。
使用自定义查询从不包含物理分区但包含用于数据分区的整数或日期/日期时间列的表加载大量数据。 分区选项：动态范围分区。
查询：SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause>。
分区列：指定用于对数据进行分区的列。
分区上限和分区下限（可选） ：指定是否要确定分区步幅。 这不适用于筛选表中的行，查询结果中的所有行都将进行分区和复制。 如果未指定，复制活动会自动检测该值。

例如，如果分区列“ID”的值范围为 1 至 100，并且将此值的下限设置为 20、上限设置为 80，并行复制设置为 4，服务将按 4 个分区（分区的 ID 范围分别为 <=20、[21, 50]、[51, 80] 和 >=81）检索数据。

下面是针对不同场景的更多示例查询：
• 查询整个表：
SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition
• 使用列选择和附加的 where 子句筛选器从表中查询：
SELECT <column_list> FROM <TableName> WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause>
• 使用子查询进行查询：
SELECT <column_list> FROM (<your_sub_query>) AS T WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause>
• 在子查询中使用分区查询：
SELECT <column_list> FROM (SELECT <your_sub_query_column_list> FROM <TableName> WHERE ?DfDynamicRangePartitionCondition) AS T

使用分区选项加载数据的最佳做法：

  • 选择独特的列作为分区列（如主键或唯一键），以避免数据倾斜。
  • 如果表具有内置分区，请使用名为“表的物理分区”分区选项来提升性能。

检查物理分区的示例查询

SELECT DISTINCT s.name AS SchemaName, t.name AS TableName, pf.name AS PartitionFunctionName, c.name AS ColumnName, iif(pf.name is null, 'no', 'yes') AS HasPartition
FROM sys.tables AS t
LEFT JOIN sys.objects AS o ON t.object_id = o.object_id
LEFT JOIN sys.schemas AS s ON o.schema_id = s.schema_id
LEFT JOIN sys.indexes AS i ON t.object_id = i.object_id 
LEFT JOIN sys.index_columns AS ic ON ic.partition_ordinal > 0 AND ic.index_id = i.index_id AND ic.object_id = t.object_id 
LEFT JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id 
LEFT JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id 
LEFT JOIN sys.partition_functions pf ON pf.function_id = ps.function_id 
WHERE s.name='[your schema]' AND t.name = '[your table name]'

如果表具有物理分区，你可看到“HasPartition”为“是”，如下所示。

SQL 查询结果。

表摘要

下表包含有关 Azure SQL 数据库中复制活动的详细信息。

源

名称 描述 值 必选 JSON 脚本属性
数据存储类型 你的数据存储类型。 外部 是 /
Connection 与源数据存储的连接。 <你的连接> 是 连接
连接类型 你的连接类型。 选择“Azure SQL 数据库”。 Azure SQL 数据库 是 /
表 源数据表。 <目标表的名称> 是 架构
表
使用查询 自定义 SQL 查询读取数据。 • 无
• 查询
• 存储过程		 否

• sqlReaderQuery
• sqlReaderStoredProcedureName, storedProcedureParameters
查询超时 查询命令执行的超时，默认值为 120 分钟。 timespan 否 queryTimeout
隔离级别 指定 SQL 源的事务锁定行为。 • None
• ReadCommitted
• ReadUncommitted
• RepeatableRead
• 可序列化
• Snapshot		 否 isolationLevel
分区选项 用于从 Azure SQL 数据库加载数据的数据分区选项。 • 无
• 表的物理分区。
• 动态范围		 否 partitionOption:
• PhysicalPartitionsOfTable
• DynamicRange
其他列 将更多数据列添加到存储源文件的相对路径或静态值。 后者支持表达式。 • 姓名
• 值		 否 additionalColumns:
• 名称
• 值

目标

名称 描述 值 必选 JSON 脚本属性
数据存储类型 你的数据存储类型。 外部 是 /
Connection 与目标数据存储的连接。 <你的连接> 是 连接
连接类型 你的连接类型。 选择“Azure SQL 数据库”。 Azure SQL 数据库 是 /
表 目标数据表。 <目标表的名称> 是 架构
表
写入行为 定义以基于文件的数据存储中的文件为源时的写入行为。 • 插入
• 更新插入
• 存储过程		 否 writeBehavior:
• 插入
• 更新插入
• sqlWriterStoredProcedureName, sqlWriterTableType, storedProcedureParameters
大容量插入表锁 使用此设置可提高在不包含多个客户端索引的表上执行大容量插入操作期间的复制性能。 是或否 否 sqlWriterUseTableLock:
true 或 false
表选项 指定如果目标表不存在，是否根据源模式自动创建目标表。 • 无
• 自动创建表		 否 tableOption:
• autoCreate
复制前脚本 指定每次运行时，复制活动将数据写入到目标表之前要执行的脚本。 可以使用此属性清除预加载的数据。 <复制前脚本>
（字符串）		 否 preCopyScript
写入批处理超时 超时前等待批插入操作完成的时间。允许的值为 timespan。 默认值为“00:30:00”（30 分钟）。 timespan 否 writeBatchTimeout
写入批大小 每批要插入到 SQL 表中的行数。 默认情况下，该服务根据行大小动态确定适当的批大小。 <行数>
（整数）		 否 writeBatchSize
最大并发连接数 活动运行期间与数据存储建立的并发连接的上限。 仅在要限制并发连接时指定一个值。 <并发连接的上限>
（整数）		 否 maxConcurrentConnections
禁用性能指标分析 此设置用于收集复制性能优化的指标（如 DTU、DWU、RU 等）和建议。 如果对此行为有所担忧，请选中此复选框。 选择或取消选择 否 disableMetricsCollection：
True 或 False

相关内容