共用方式為

在複製活動中設定 SQL 資料庫 （預覽）

本文概述如何使用管線中的複製活動，將資料從 SQL 資料庫複製到 SQL 資料庫。

支援的組態

如需設定複製活動下的每個標籤，請依次進入以下各節。

一般

請參閱一般設定指南，以設定一般設定的標籤。

來源

複製活動之 [ 來源 ] 索引標籤下的 SQL 資料庫支援下列屬性。

螢幕擷取畫面，其中顯示 [來源] 索引標籤和屬性清單。

以下是必要的屬性：

  • 聯機：選取參考本文步驟的現有 SQL 資料庫。

  • 使用查詢：可以選擇 [資料表]、[查詢] 或 [預存程序]。 下列清單描述每個設定的組態：

    • 數據表：指定要讀取資料的 SQL 資料庫名稱。 從下拉式清單中選擇現有的數據表，或 選取 [手動輸入 ] 以輸入架構和數據表名稱。

    • 查詢：指定要讀取數據的自定義 SQL 查詢。 例如 select * from MyTable。 或選取鉛筆圖示以在程式碼編輯器中編輯。

      顯示選擇查詢的螢幕快照。

    • 預存程式：從下拉式清單中選取預存程式。

在 [進階] 下，您可以指定下列欄位：

  • 查詢逾時（分鐘）：指定查詢命令執行的逾時，預設值為 120 分鐘。 如果為此屬性設定參數，允許的值是時間範圍，例如 “02：00：00” （120 分鐘）。

    顯示查詢逾時設定的螢幕快照。

  • 隔離等級：指定 SQL 來源的交易鎖定行為。 允許的值包括：讀取已提交、讀取未提交、可重複讀、可序列化或快照。 如需詳細資訊，請參閱 IsolationLevel Enum。

    顯示隔離等級設定的螢幕快照。

  • 數據分割選項：指定用來從 SQL 資料庫載入資料的數據分割選項。 允許的值為：None （預設值）、資料表的實體分割區，以及 Dynamic range。 啟用數據分割選項時（也就是非 無），從 SQL 資料庫同時載入數據的平行處理原則程度是由複製活動設定索引標籤標的 複製平行處理原則程度 所控制。

    • None：選擇此設定來不使用分割區。

    • 資料表的實體分割：使用實體分割時，分割欄位和機制會根據您的實體資料表定義自動決定。

    • 動態範圍：使用已啟用平行的查詢時，需要範圍分割參數（?DfDynamicRangePartitionCondition）。 範例查詢：SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition。

      • 分區欄位名稱：指定用於範圍分區並進行平行複製的源欄位名稱，該欄位類型必須為 整數或日期/日期時間 類型（int、smallint、bigint、date、smalldatetime、datetime、datetime2或 datetimeoffset）。 如果未指定，則會自動偵測數據表的索引或主鍵，並當做分區欄位使用。

        如果您使用查詢來擷取源數據，請在 WHERE 子句中連結 ?DfDynamicRangePartitionCondition。 如需範例，請參閱從 SQL 資料庫平行複製一節。

      • 分割區上限：指定分割欄位範圍拆分的最大值。 這個值用來決定數據分割的步幅，而不是用於篩選數據表中的數據列。 數據表或查詢結果中的所有數據列都會進行分割和複製。 如果未指定，複製活動會自動偵測值。 如需範例，請參閱從 SQL 資料庫平行複製一節。

      • 分割區下限：指定用於分割範圍的分割區列的最小值。 這個值用來決定數據分割的步幅，而不是用於篩選數據表中的數據列。 數據表或查詢結果中的所有數據列都會進行分割和複製。 如果未指定，複製活動會自動偵測值。 如需範例，請參閱從 SQL 資料庫平行複製一節。

  • 其他資料行：新增更多資料行以儲存來源檔案的相對路徑或靜態值。 表達式受到後者的支持。 如需詳細資訊，請參閱＜在複製期間新增其他資料行＞。

目的地

複製活動的 [ 目的地 ] 索引標籤下，SQL 資料庫支援下列屬性。

螢幕擷取畫面，其中顯示 [目的地] 索引標籤。

以下是必要的屬性：

  • 聯機：選取參考本文步驟的現有 SQL 資料庫。

  • 資料表選項：從 [使用現有 ] 或 [ 自動建立資料表] 中選取 。

    • 如果您選擇 [使用現有的：

      • 數據表：指定要寫入資料的 SQL 資料庫名稱。 從下拉式清單中選擇現有的數據表，或 選取 [手動輸入 ] 以輸入架構和數據表名稱。

    • 如果您選取 [自動建立數據表]：

      • 數據表：它會自動在來源架構中建立數據表（如果不存在），當預存程式做為寫入行為時，不支援此數據表。

在 [進階] 下，您可以指定下列欄位：

  • 寫入行為：當來源是檔案型數據存放區中的檔案時，定義寫入行為。 可以選擇 [插入]、[Upsert] 或 [預存程序]。

    寫入行為索引標籤的螢幕快照。

    • 插入：如果您的來源資料有插入，請選擇此選項。

    • Upsert：如果您的來源資料同時有插入和更新，請選擇此選項。

      • 使用 TempDB：指定是否要使用全域臨時表或實體數據表作為 upsert 的臨時表。 根據預設，服務會使用全域暫存資料表做為中介資料表，此核取方塊為已選取狀態。
        如果您將大量數據寫入 SQL 資料庫，請取消勾選此選項，並指定資料架構名稱，Data Factory 會在其中建立臨時表以載入上游數據，並在完成時自動清理。 請確定使用者已在資料庫中建立數據表許可權，並變更架構的許可權。 如果未指定，則會使用全域臨時表作為暫存用途。

        顯示選取 [使用 TempDB] 的螢幕快照。

      • 選取使用者資料庫架構：未選取 [使用 TempDB ] 時，請指定架構名稱，Data Factory 會建立臨時表以載入上游數據，並在完成時自動清除它們。 請確定您的資料庫中具有建立資料表的權限，以及改變結構描述的權限。

        備註

        您必須擁有建立和刪除資料表的許可權。 根據預設，過渡數據表會共用與目的地數據表相同的架構。

        螢幕快照顯示未選取「使用 TempDB」。

      • 關鍵欄位：選擇哪個欄位用來判斷來源的資料列是否符合目的地的資料列。

    • 預存程式名稱：從下拉式清單中選取預存程式。

  • 大量插入資料表鎖定：選擇「是」 或「否」。 使用此設定可改善在無索引的資料表上，從多個用戶端進行大量插入操作時的複製效能。 如需詳細資訊，請參閱 BULK INSERT （Transact-SQL）

  • 預拷貝腳本：指定一個腳本用於在每次執行時將數據寫入目的地表之前執行複製活動。 您可以使用此屬性來清除預先載入的資料。

  • 寫入批次逾時：指定批次插入作業在超過時間限制之前完成的等候時間。允許的值是時間長度。 預設值為 "00:30:00" (30 分鐘)。

  • 寫入批次大小：指定每個批次要插入 SQL 資料表的數據列數目。 允許的值是整數（數據列數目）。 根據預設，服務會根據數據列大小動態決定適當的批次大小。

  • 並行連線數上限：指定在活動執行期間，與資料存放區建立的並行連線數上限。 僅在想要限制並行連線時，才需要指定值。

映射

針對 [ 對應 ] 標籤配置，如果您沒有將自動建立資料表的 SQL Database 套用為目的地，請移至 [ 對應]。

如果您選擇使用自動建立資料表的 SQL 資料庫作為終點，那麼除了在對應中的設定之外，您還可以編輯終點欄位的類型。 選取匯入結構描述之後，您可以在目的地指定欄位類型。

例如，來源中 標識碼 數據行的類型為 int，而且當對應至目的地數據行時，您可以將它變更為 float 類型。

對應目的地欄位類型的截圖。

設定

如需 [設定] 索引標籤配置，請移至 [設定] 索引標籤下方設定其他選項。

從 SQL 資料庫平行複製

複製活動中 SQL 資料庫連接器提供內建的數據分割，以平行複製數據。 您可以在複製活動的 [來源] 索引標籤上找到資料分割選項。

當您啟用資料分割複製時，複製活動會針對 SQL 資料庫來源執行平行查詢，以依數據分割載入數據。 平行度是由複製活動設定索引標籤中的 [複製平行處理原則程度 ] 控制。例如，如果您將 複製平行處理原則的程度 設定為四個，服務會根據指定的分割區選項和設定同時產生和執行四個查詢，而每個查詢都會從 SQL 資料庫擷取部分數據。

建議您啟用與數據分割的平行複製，特別是當您從 SQL 資料庫載入大量數據時。 以下是針對不同案例的建議組態。 將數據複製到檔案型數據存放區時，建議以多個檔案的形式寫入資料夾（僅指定資料夾名稱），在此情況下效能會優於寫入單一檔案。

情境 建議的設定
從大型數據表中進行完整載入，並使用物理分區。 分區選項：表格的實體分區。

在執行期間，服務會自動偵測實體分割區，並依分割區複製數據。

若要檢查資料表是否有實體分割區，您可以參考 此查詢。
從大型數據表全量加載，沒有實體分區，但有用於數據分區的整數或日期時間列。 分區選項：動態範圍分區。
資料分割資料行（選擇性）：指定用來分割數據的數據行。 如果未指定，則會使用索引列或主鍵列。
分割區上限 和 分割區下限（選擇性）：指定是否要判斷分割區步幅。 這不適用於篩選資料表中的資料列，資料表中的所有資料列都會分割並複製。 如果未指定，複製活動會自動偵測值，而且可能需要很長的時間，視 MIN 和 MAX 值而定。 建議提供上限和下限。

例如，如果您的分割區數據行 「ID」 的值範圍從 1 到 100，而您將下限設定為 20，並將上限設定為 80，且平行複製為 4，則服務會依 4 個分割區擷取數據 - 範圍中的標識符 <=20、[21、50]、[51、80]和 >=81。
使用自定義查詢載入大量數據，不使用實體分區，但使用整數或日期/日期時間欄進行數據分割。 分區選項：動態範圍分區。
查詢：SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause>。
分割欄位：指定用於分割資料的欄位。
分割區上限 和 分割區下限（選擇性）：指定是否要判斷分割區步幅。 這不適用於篩選資料表中的資料列，查詢結果中的所有資料列都會分割並複製。 如果未指定，複製活動會自動偵測值。

例如，如果您的分割區欄位「ID」的值範圍是從 1 到 100，您將下界設為 20，上界設為 80，並且設定平行處理為 4，則服務會分別以 4 個分割區來擷取數據，範圍分別為：<= 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」 為 「yes」，如下所示。

SQL 查詢結果的螢幕快照。

數據表摘要

下表包含 SQL 資料庫中複製活動的詳細資訊。

來源

名稱 說明 價值 為必填項目 JSON 指令碼屬性
連線 您與源資料存放區的連線。 <您的連線> 是的 連接
使用查詢 讀取資料的方式。 套用 [資料表] 以從指定的資料表讀取資料，或套用 [查詢] 以使用 SQL 查詢讀取資料。 • 資料表
• 查詢
• 預存程式		 是的 /
適用於 [資料表]
模式名稱 架構的名稱。 < 您的架構名稱 > 否 結構描述
資料表名稱 數據表的名稱。 < 您的資料表名稱 > 否 表格
查詢
查詢 指定要讀取數據的自訂 SQL 查詢。 例如： SELECT * FROM MyTable 。 < SQL 查詢 > 否 sqlReaderQuery
針對 預存程式
預存程式名稱 預存程序的名稱。 < 預存程式名稱 > 否 SQL讀取器儲存過程名稱
查詢逾時（分鐘） 查詢命令執行的逾時，預設值為 120 分鐘。 如果為此屬性設定參數，允許的值是時間範圍，例如 “02：00：00” （120 分鐘）。 時間範圍 否 queryTimeout
隔離等級 指定 SQL 來源的交易鎖定行為。 • 讀已提交
• 讀取未認可
• 可重複讀取
•序列化
•快照		 否 隔離層級 (isolationLevel)：
• 已提交讀取
• ReadUncommitted（未提交讀取）
• 可重複讀取
•序列化
•快照
分割區選項 用來從 SQL 資料庫載入資料的數據分割選項。 • 無
• 資料表的實體分區
•動態範圍		 否 partitionOption：
• 資料表的實體分區
• 動態範圍
針對 動態範圍
分割欄名稱 在 整數或日期/日期時間 類型的源數據欄位名稱（int、smallint、bigint、date、smalldatetime、datetime、datetime2或 datetimeoffset）中，該名稱用於範圍分割以進行平行複製。 如果未指定，則會自動偵測數據表的索引或主鍵，並當做分區欄位使用。 如果您使用查詢來擷取源數據，請在 WHERE 子句中連結 ?DfDynamicRangePartitionCondition。 < 分割區欄名稱 > 否 分區欄位名稱
分割區上限 分區範圍分割的分區列最大值。 這個值用來決定數據分割的步幅，而不是用於篩選數據表中的數據列。 數據表或查詢結果中的所有數據列都會進行分割和複製。 如果未指定，複製活動會自動偵測值。 < 分割區上限 > 否 partitionUpperBound
分割區下限 用於分割範圍的分割列的最小值。 這個值用來決定數據分割的步幅，而不是用於篩選數據表中的數據列。 數據表或查詢結果中的所有數據列都會進行分割和複製。 如果未指定，複製活動會自動偵測值。 < 分割區下限 > 否 partitionLowerBound
新增欄位 新增更多資料行以儲存來源檔案的相對路徑或靜態值。 表達式受到後者的支持。 • 名稱
• 價值		 否 額外欄位：
•名字
•價值

目的地

名稱 說明 價值 為必填項目 JSON 指令碼屬性
連線 您與目標資料存放區的連線。 <您的連線> 是的 連接
資料表選項 您的目的地數據表。 從 [使用現有 ] 或 [ 自動建立資料表] 中選取 。 • 使用現有的
• 自動建立數據表		 是的 圖式
表格
寫入行為 定義來源是來自檔案型資料存放區的檔案時的寫入行為。 •插入
• 更新插入
• 預存程式		 否 writeBehavior：
•插入
更新插入
• sqlWriterStoredProcedureName
批量插入表鎖 使用此設定可改善在無索引的資料表上，從多個用戶端進行大量插入操作時的複製效能。 是或否 （預設值） 否 sqlWriterUseTableLock：
true 或 false （預設值）
針對 Upsert
使用TempDB 是否要使用全域臨時表或實體數據表作為 upsert 的臨時表。 已選擇（預設）或未選擇 否 useTempDB：
true （預設值） 或 false
重要欄位 選擇哪一個數據行可用來判斷來源的數據列是否符合目的地的數據列。 < 您的關鍵欄位> 否 鑰匙
針對 預存程式
預存程式名稱 這個屬性是從源數據表讀取數據的預存程式名稱。 最後一個 SQL 語句必須是預存程式中的 SELECT 語句。 < 預存程式名稱 > 否 sqlWriterStoredProcedureName
預先複製腳本 在每次執行中，為複製活動準備的指令碼會在將資料寫入目的地資料表之前執行。 您可以使用此屬性來清除預先載入的資料。 <複製前指令碼>
（字串）		 否 preCopyScript
寫入批次逾時 在逾時前等待批次插入作業完成的時間。允許的值為時間範圍。 預設值為 "00:30:00" (30 分鐘)。 時間範圍 否 writeBatchTimeout
寫入批次大小 每個批次要插入 SQL 資料表的數據列數目。 根據預設，服務會根據數據列大小動態決定適當的批次大小。 < >個資料列的數目
（整數）		 否 writeBatchSize
並行連線數上限 在活動運行期間，與資料存放區建立的並行連線的上限值。 僅在想要限制並行連線時，才需要指定值。 <並行連線數上限>
（整數）		 否 最大並發連接數

相關內容

  • Last updated on