DataWorks的SQLServer資料同步的能力支援情況 - DataWorks

SQL Server資料來源為您提供讀取和寫入SQL Server雙向通道的功能，本文為您介紹DataWorks的SQL Server資料同步的能力支援情況。

支援的版本

SQL Server Reader使用驅動版本是com.microsoft.sqlserver sqljdbc4 4.0，驅動能力具體請參見官網文檔。該驅動支援的SQL Server版本如下所示：

版本	支援性（是/否）
SQL Server 2016	是
SQL Server 2014	是
SQL Server 2012	是
PDW 2008R2 AU34	是
SQL Server 2008 R2	是
SQL Server 2008	是
SQL Server 2019	否
SQL Server 2018	否
Azure SQL Managed Instance	否
Azure Synapse Analytics	否
Azure SQL Database	是

使用限制

離線同步支援閱讀檢視表。

支援的欄位類型

SQL Server全量的欄位類型請參見SQL Server協助文檔。以下以SQL Server 2016為例，為您列舉常見的欄位類型的支援情況。

sql server 2016欄位類型	SQL Server Reader	SQL Server Writer
bigint	支援	支援
bit	支援	支援
decimal	支援	支援
int	支援	支援
money	支援	支援
numeric	支援	支援
smallint	支援	支援
smallmoney	支援	支援
tinyint	支援	支援
float	支援	支援
real	支援	支援
date	支援	支援
datetime2	支援	支援
datetime	支援	支援
datetimeoffset	不支援	不支援
smalldatetime	支援	支援
time	支援	支援
char	支援	支援
text	支援	支援
varchar	支援	支援
nchar	支援	支援
ntext	支援	支援
nvarchar	支援	支援
binary	支援	支援
image	支援	支援
varbinary	支援	支援
cursor	不支援	不支援
hierarchyid	不支援	不支援
sql_variant	支援	支援
Spatial Geometry Types	不支援	不支援
table	不支援	不支援
rowversion	不支援	不支援
uniqueidentifier	支援	支援
xml	支援	支援
Spatial Geography Types	不支援	不支援

SQL Server Reader和SQL Server Writer針對SQL Server的類型轉換列表，如下所示。

類型分類	SQL Server資料類型
整數類	BIGINT、INT、SMALLINT和TINYINT
浮點類	FLOAT、DECIMAL、REAL和NUMERIC
字串類	CHAR、NCHAR、NTEXT、NVARCHAR、TEXT、VARCHAR、NVARCHAR（MAX）和VARCHAR（MAX）
日期時間類	DATE、DATETIME和TIME
布爾型	BIT
二進位類	BINARY、VARBINARY、VARBINARY（MAX）和TIMESTAMP

建立資料來源

在進行資料同步任務開發時，您需要在DataWorks上建立一個對應的資料來源，操作流程請參見建立並管理資料來源，詳細的配置參數解釋可在配置介面查看對應參數的文案提示。

資料同步任務開發

資料同步任務的配置入口和通用配置流程可參見下文的配置指導。

單表離線同步任務配置指導

操作流程請參見通過嚮導模式配置離線同步任務、通過指令碼模式配置離線同步任務。
指令碼模式配置的全量參數和指令碼Demo請參見下文的附錄：指令碼Demo與參數說明。

整庫離線讀同步任務配置指導

操作流程請參見Data Integration側同步任務配置。

常見問題

主備同步資料恢複問題
主備同步問題指SQL Server使用主從災備，備庫從主庫不間斷通過binlog恢複資料。由於主備資料同步存在一定的時間差，特別在於某些特定情況，例如網路延遲等問題，導致備庫同步恢複的資料與主庫有較大差別，從備庫同步的資料不是一份目前時間的完整鏡像。
一致性約束
SQL Server在資料存放區劃分中屬於RDBMS系統，對外可以提供強一致性資料查詢介面。例如一次同步任務啟動運行過程中，當該庫存在其他資料寫入方寫入資料時，由於資料庫本身的快照特性，SQL Server Reader完全不會擷取到寫入的更新資料。
上述是在SQL Server Reader單執行緒模式下資料同步一致性的特性，SQL Server Reader可以根據您配置的資訊使用並發資料幫浦，因此不能嚴格保證資料一致性。
當SQL Server Reader根據splitPk進行資料切分後，會先後啟動多個並發任務完成資料同步。多個並發任務相互之間不屬於同一個讀事務，同時多個並發任務存在時間間隔，因此這份資料並不是完整的、一致的資料快照資訊。
針對多線程的一致性快照需求，目前在技術上無法實現，只能從工程角度解決。工程化的方式存在取捨，在此提供以下解決思路，您可以根據自身情況進行選擇。
- 使用單線程同步，即不再進行資料切片。缺點是速度比較慢，但是能夠很好保證一致性。
- 關閉其他資料寫入方，保證當前資料為待用資料，例如鎖表、關閉備庫同步等。缺點是可能影響線上業務。
資料庫編碼問題
SQL Server Reader底層使用JDBC進行資料幫浦，JDBC天然適配各類編碼，並在底層進行了編碼轉換。因此SQL Server Reader不需您指定編碼，可以自動擷取編碼並轉碼。
增量資料同步的方式
SQL Server Reader使用JDBC SELECT陳述式完成資料幫浦工作，因此您可以使用SELECT…WHERE…進行增量資料幫浦，方式如下：
- 資料庫線上應用寫入資料庫時，填充modify欄位為更改時間戳記，包括新增、更新、刪除（邏輯刪除）。對於該類應用，SQL Server Reader只需要where條件後跟上一同步階段時間戳記即可。
- 對於新增流水型資料，SQL Server Reader在where條件後跟上一階段最大自增ID即可。
對於業務上無欄位區分新增、修改資料的情況，SQL Server Reader無法進行增量資料同步，只能同步全量資料。
SQL安全性
SQL Server Reader提供querySql語句交給您自己實現SELECT抽取語句，SQL Server Reader本身對querySql不進行任何安全性校正。

附錄：指令碼Demo與參數說明

離線任務指令碼配置方式

如果您配置離線任務時使用指令碼模式的方式進行配置，您需要按照統一的指令碼格式要求，在任務指令碼中編寫相應的參數，詳情請參見通過指令碼模式配置離線同步任務，以下為您介紹指令碼模式下資料來源的參數配置詳情。

Reader指令碼Demo

{
    "type":"job",
    "version":"2.0",//版本號碼。
    "steps":[
        {
            "stepType":"sqlserver",//外掛程式名。
            "parameter":{
                "datasource":"",//資料來源。
                "column":[//欄位。
                    "id",
                    "name"
                ],
                "where":"",//篩選條件。
                "splitPk":"",//如果指定splitPk，表示您希望使用splitPk代表的欄位進行資料分區。
                "table":""//資料表。
            },
            "name":"Reader",
            "category":"reader"
        },
        {
            "stepType":"stream",
            "parameter":{},
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"//錯誤記錄數。
        },
        "speed":{
            "throttle":true,//當throttle值為false時，mbps參數不生效，表示不限流；當throttle值為true時,表示限流。
            "concurrent":1 //作業並發數。
            "mbps":"12",//限流，此處1mbps = 1MB/s。
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}

如果您想使用querySql查詢，Reader部分指令碼程式碼範例如下（SQL Server資料來源是sql_server_source，待查詢的表是dbo.test_table，待查詢的列是name）。

{
    "stepType": "sqlserver",
    "parameter": {
        "querySql": "select name from dbo.test_table",
        "datasource": "sql_server_source",
        "column": [
            "name"
        ],
        "where": "",
        "splitPk": "id"
    },
    "name": "Reader",
    "category": "reader"
},

Reader指令碼參數

參數	描述	是否必選	預設值
datasource	資料來源名稱，指令碼模式支援添加資料來源，該配置項填寫的內容必須要與添加的資料來源名稱保持一致。	是	無
table	選取的需要同步的表名稱，一個作業只能支援一個表同步。	是	無
column	所配置的表中需要同步的列名集合，使用JSON的數組描述欄位資訊。預設使用所有列配置，例如[ * ]。支援列裁剪，即列可以挑選部分列進行匯出。支援列換序，即列可以不按照表schema資訊順序進行匯出。支援常量配置，您需要按照MySQL SQL文法格式，例如`["id", "table","1", "'mingya.wmy'", "'null'", "to_char(a+1)", "2.3" , "true"]` 。 id為普通列名。 table為包含保留字的列名。 1為整型數字常量。 'mingya.wmy'為字串常量（注意需要加上一對單引號）。 'null'為字串。 to_char(a + 1)為函數運算式。 2.3為浮點數。 true為布爾值。 column必須顯示指定同步的列集合，不允許為空白。	是	無
splitPk	SQL Server Reader進行資料幫浦時，如果指定splitPk，表示您希望使用splitPk代表的欄位進行資料分區。資料同步系統因此會啟動並發任務進行資料同步，這樣可以提高資料同步的效能。推薦splitPk使用者使用表主鍵，因為表主鍵通常情況下比較均勻，因此切分出來的分區也不容易出現資料熱點。目前splitPk僅支援整型資料切分，不支援字串、浮點、日期等其他類型。如果您指定其他非支援類型，SQL Server Reader將報錯。	否	無
where	篩選條件，SQL Server Reader根據指定的column、table和where條件拼接SQL，並根據該SQL進行資料幫浦。例如在測試時，可以將where條件指定為limit 10。在實際業務情境中，往往會選擇當天的資料進行同步，將where條件指定為`gmt_create > $bizdate`。 where條件可以有效地進行業務增量同步處理。 where條件為空白，視作同步全表所有的資訊。	否	無
querySql	使用格式：`"querysql" : "查詢statement",`在部分業務情境中，where配置項不足以描述所篩選的條件，您可以通過該配置型來自訂篩選SQL。當配置此項後，資料同步系統就會忽略tables、columns配置項，直接使用這項配置的內容對資料進行篩選，例如需要進行多表join後同步資料，使用`select a,b from table_a join table_b on table_a.id = table_b.id`。當您配置querySql時，SQL Server Reader直接忽略column、table和where條件的配置。	否	無
fetchSize	該配置項定義了外掛程式和資料庫伺服器端每次批量資料擷取條數，該值決定了Data Integration和伺服器端的網路互動次數，能夠提升資料幫浦效能。說明 fetchSize值過大（>2048）可能造成資料同步進程OOM。	否	1024

對於您配置的table、column和where等資訊，SQL Server Reader將其拼接為SQL語句發送至SQL Server資料庫。
對於您配置的querySql資訊，SQL Server直接將其發送至SQL Server資料庫。

Writer指令碼Demo

{
    "type":"job",
    "version":"2.0",//版本號碼。
    "steps":[
        {
            "stepType":"stream",
            "parameter":{},
            "name":"Reader",
            "category":"reader"
        },
        {
            "stepType":"sqlserver",//外掛程式名。
            "parameter":{
                "postSql":[],//執行資料同步任務之後率先執行的SQL語句。
                "datasource":"",//資料來源。
                "column":[//欄位。
                    "id",
                    "name"
                ],
                "table":"",//表名。
                "preSql":[]//執行資料同步任務之前率先執行的SQL語句。
            },
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"//錯誤記錄數。
        },
        "speed":{
            "throttle":true,//當throttle值為false時，mbps參數不生效，表示不限流；當throttle值為true時,表示限流。
            "concurrent":1, //作業並發數。
            "mbps":"12"//限流，此處1mbps = 1MB/s。
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}

Writer指令碼參數

參數	描述	是否必選	預設值
datasource	資料來源名稱，指令碼模式支援添加資料來源，此配置項填寫的內容必須要與添加的資料來源名稱保持一致。	是	無
table	選取的需要同步的表名稱。	是	無
column	目標表需要寫入資料的欄位，欄位之間用英文逗號分隔。例如`"column":["id","name","age"]`。如果要依次寫入全部列，使用表示，例如`"column":[""]`。	是	無
preSql	執行資料同步任務之前率先執行的SQL語句。目前嚮導模式僅允許執行一條SQL語句，指令碼模式可以支援多條SQL語句，例如清除舊資料。	否	無
postSql	執行資料同步任務之後執行的SQL語句。目前嚮導模式僅允許執行一條SQL語句，指令碼模式可以支援多條SQL語句，例如加上某一個時間戳記。	否	無
writeMode	選擇匯入模式，可以支援insert方式。當主鍵/唯一性索引衝突時，Data Integration視為髒資料但保留原有的資料。	否	insert
batchSize	一次性批量提交的記錄數大小，該值可以極大減少資料同步系統與SQL Server的網路互動次數，並提升整體輸送量。如果該值設定過大，會導致資料同步運行進程OOM異常。	否	1,024