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 |
資料同步任務開發
SQL Server資料同步任務的配置入口和通用配置流程指導可參見下文的配置指導,詳細的配置參數解釋可在配置介面查看對應參數的文案提示。
建立資料來源
在進行資料同步任務開發時,您需要在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參數和writer參數,指令碼模式的統一要求請參見通過指令碼模式配置離線同步任務,以下為您介紹指令碼模式下的資料來源的Reader參數和Writer參數的指導詳情。
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的數組描述欄位資訊 。預設使用所有列配置,例如[ * ]。
| 是 | 無 |
splitPk | SQL Server Reader進行資料幫浦時,如果指定splitPk,表示您希望使用splitPk代表的欄位進行資料分區。資料同步系統因此會啟動並發任務進行資料同步,這樣可以提高資料同步的效能。
| 否 | 無 |
where | 篩選條件,SQL Server Reader根據指定的column、table和where條件拼接SQL,並根據該SQL進行資料幫浦。例如在測試時,可以將where條件指定為limit 10。在實際業務情境中,往往會選擇當天的資料進行同步,將where條件指定為
| 否 | 無 |
querySql | 使用格式: | 否 | 無 |
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 | 目標表需要寫入資料的欄位,欄位之間用英文逗號分隔。例如 | 是 | 無 |
preSql | 執行資料同步任務之前率先執行的SQL語句。目前嚮導模式僅允許執行一條SQL語句,指令碼模式可以支援多條SQL語句,例如清除舊資料。 | 否 | 無 |
postSql | 執行資料同步任務之後執行的SQL語句。目前嚮導模式僅允許執行一條SQL語句,指令碼模式可以支援多條SQL語句,例如加上某一個時間戳記。 | 否 | 無 |
writeMode | 選擇匯入模式,可以支援insert方式。 當主鍵/唯一性索引衝突時,Data Integration視為髒資料但保留原有的資料。 | 否 | insert |
batchSize | 一次性批量提交的記錄數大小,該值可以極大減少資料同步系統與SQL Server的網路互動次數,並提升整體輸送量。如果該值設定過大,會導致資料同步運行進程OOM異常。 | 否 | 1,024 |