ADF 第六篇：Copy Data Activity詳解

悅光陰發表於2020-12-23

在Azure 資料工程中，可以使用Copy Data 活動把資料從on-premises 或雲中複製到其他儲存中。Copy Data 活動必須在一個IR（Integration Runtime）上執行，對於把儲存在on-premises中的資料複製到其他儲存時，必須建立一個self-hosted Integration Runtime。

一，認識Copy Data Activity

建立一個Pipeline，從Activities列表中找到“Copy data”，拖放到Pipeline畫布中，如下圖所示：

在General選項卡中，設定Activity的常規屬性

  • Name：為Activity命名
  • Timeout：設定Activity的超時時間
  • Retry：重試次數
  • Retry interval：重試一次間隔的時間，單位是second
  • Secure output：安全輸出，如果勾選，那麼該Activity的輸出不會記錄
  • Secure input：安全入，如果勾選，那麼該Activity的輸入不會記錄

二，設定源屬性

Source選項卡用於設定Copy data Activity的源屬性，

1，Source 的常規設定

Source dataset：設定源的dataset

use query：Table選項表示整個表作為一個資料來源，Query或 Store procedure選項表示使用查詢語句或儲存過程來獲取資料來源。

Query timeout(minutes)：表示查詢超時的時間

Isolation level：設定查詢隔離級別，作用於資料來源。

2，Partition option

指定從SQL Server載入資料的分割槽選項，當啟用分割槽選項時（不是None），從SQL Server 同時載入資料的併發度由Copy data Activity的Degree of copy parallelism屬性設定。Physical Partitions Of Table表示資料工廠根據原始表的分割槽定義來確定分割槽列和分割槽機制；當選擇Dynamic range選項時，使用者還需要設定Partition column name、Partition upper bound 和Partition lower bound三項，手動設定分割槽列和分割槽機制。

3，Additional columns

新增額外的列，Value由三種型別：Add dynamic content、$$COLUMN和Custom。

$$COLUMN：表示把源的指定列複製為另一列

Custom：表示新增一列，列指是常量

Add dynamic content，表示新增動態上下文（Dynamic Content），動態上下文是指資料工廠的上下文，這些動態上下文由系統變數（System variables）來提供：

三，設定Sink

Sink是Copy Data Activity複製資料的目標資料集，Data Factory 使用 Sink dataset來設定目標。

1，Store procedure name

從Sink dataset中選擇儲存過程，該儲存過程定義瞭如何把後設資料應用於目標表。該儲存過程每個batch呼叫一次，對於僅執行一次且與源資料無關的操作，請使用 Pre-copy script 屬性。

如果使用Pre-copy script 屬性，通常意味著資料是全量更新，重寫整個表，比如以下指令碼：

truncate table staging_table

Copy data activity的執行過程是：每次執行Copy data activity，資料工廠首先執行Pre-copy script，然後使用最新的資料插入資料到target table。

如果使用儲存過程，通常是對資料進行增量更新，要實現增量更新，實際上是把資料集作為引數傳遞給儲存過程，這就意味著儲存過程的一個引數必須是表變數型別，儲存過程的程式碼實現如下指令碼所示，

CREATE PROCEDURE spOverwriteMarketing 
　　@Marketing [dbo].[MarketingType] READONLY
　　, @category varchar(256)
AS
BEGIN
MERGE [dbo].[Marketing] AS target
USING @Marketing AS source
　　ON (target.ProfileID = source.ProfileID and target.Category = @category)
WHEN MATCHED THEN
    UPDATE SET State = source.State
WHEN NOT MATCHED THEN
    INSERT (ProfileID, State, Category)
    VALUES (source.ProfileID, source.State, source.Category);
END

2，Table option

如果設定為Auto create table，那麼當目標表不存在時，資料工廠根據Source 的後設資料自動建立目標表。

3，常規設定

Write batch timeout：每個batch資料寫入的超時時間

Write batch size：每個batch的資料行數量

Max concurrent connections：訪問資料儲存的最大的併發連線數量

四，設定Mapping

在Mapping選項卡中，主要設定Source 和 Sink之間的列對映

1，Type conversion settings用於設定型別轉換

  • Allow data truncation： 在把source資料轉換到sink時，如果欄位的型別不同，允許資料截斷。
  • Treat boolean as number：把bool值作為數值來看待，把true看作1，把false看作1
  • DateTime format：DateTime型別的格式
  • DateTimeOffset format：資料間隔的格式
  • TimeSpan format：TimeSpan的格式
  • Culture：locale

2，列對映

設定列與列之間的對映關係，使用者需要點選“Import schemas”來匯入架構後設資料。

五，設定Settings

配置Copy data Activity的設定

1，常規的設定

  • Data integration unit：資料整合的單元
  • Degree of copy parallelism：指定資料載入時併發度
  • Data consistency verification：當勾選時，Copy data Activity會在資料移動之後，對資料進行一致性檢查
  • Enable logging：啟用日誌，記錄複製的檔案，跳過的資料行和檔案
  • Enable staging：指定是否要通過臨時儲存來複制資料

2，設定Fault tolerance

當設定Fault tolerance （錯誤容忍）之後，使用者可以忽略在複製資料過程中出現的一些錯誤，可以忽略的錯誤型別主要有三個：

  • Skip incompatible rows：跳過不相容的行
  • Skip missing files：跳過缺失的檔案
  • Skip forbidden files：跳過禁止的檔案

六，資料更新的全量更新和增量更新

資料更新的方式主要有：全量更新、追加資料、增量更新。

1，資料的全量更新和追加更新

如果使用Pre-copy script 屬性，通常意味著資料是全量更新和追加更新。

在插入資料之前，如果先清空目標表，再向目標表插入資料，這種方式是全量更新；如果不清空目標表，只是向目標表插入新的資料，那麼就是追加更新，前提是保證資料是無重複的新資料。

2，通過儲存過程來實現Copy data Activity的增量更新

如果Sink屬性使用儲存過程，那麼是對資料進行增量更新。實現資料的增量更新，實際上是把資料集作為引數傳遞給儲存過程，這就意味著儲存過程的一個引數必須是表變數型別。

由於儲存過程在連線表變數時，效能較差，建議對分batch插入，每個batch進行一次插入操作。

建立一個表型別，作為儲存過程的引數，表的架構和輸入資料的架構相同：

CREATE TYPE [dbo].[MarketingType] AS TABLE
(
    [ProfileID] [varchar](256) NOT NULL,
    [State] [varchar](256) NOT NULL,
    [Category] [varchar](256) NOT NULL
)

建立儲存過程，第一個變數是表變數，該儲存過程的作用是把表變數的資料更新到Sink指定的target table中。

CREATE PROCEDURE spOverwriteMarketing 
　　@Marketing [dbo].[MarketingType] READONLY
　　, @category varchar(256)
AS
BEGIN
MERGE [dbo].[Marketing] AS target
USING @Marketing AS source
ON (target.ProfileID = source.ProfileID and target.Category = @category)
WHEN MATCHED THEN
    UPDATE SET State = source.State
WHEN NOT MATCHED THEN
    INSERT (ProfileID, State, Category)
    VALUES (source.ProfileID, source.State, source.Category);
END

3，使用臨時表來實現增量更新

先把資料載入到臨時表，通過merge語句把臨時資料歸併到product table。

 

參考文件：

Copy data to and from SQL Server by using Azure Data Factory

Copy activity in Azure Data Factory

相關文章