SQLServerReader__Reader插件_使用手冊_數據集成-阿裏雲

1 快速介紹

SQLServerReader插件實現了從SqlServer讀取數據。在底層實現上，SQLServerReader通過JDBC連接遠程SqlServer數據庫，並執行相應的sql語句將數據從SqlServer庫中SELECT出來。

2 實現原理

簡而言之，SQLServerReader通過JDBC連接器連接到遠程的SqlServer數據庫，並根據用戶配置的信息生成查詢SELECT SQL語句並發送到遠程SqlServer數據庫，並將該SQL執行返回結果使用CDP自定義的數據類型拚裝為抽象的數據集，並傳遞給下遊Writer處理。

對於用戶配置Table、Column、Where的信息，SQLServerReader將其拚接為SQL語句發送到SqlServer數據庫；對於用戶配置querySql信息，SqlServer直接將其發送到SqlServer數據庫。

3 功能說明

3.1 配置樣例

使用Instance配置一個從SqlServer數據庫同步抽取數據到ODPS的作業:

{
    "type": "job",
    "traceId": "您可以在這裏填寫您作業的追蹤ID，建議使用業務名+您的作業ID",
    "version": "1.0",
    "configuration": {
        "reader": {
            "plugin": "sqlserver",
            "parameter": {
                "instanceName": "rds.aliyun.com",
                "username": "username",
                "password": "xxx",
                "splitPk": "id",
                "fetchSize": 512,
                "table": "table_01",
                "column": [
                    "a",
                    "b",
                    "c"
                ],
                "where": "1 = 1"
            }
        },
        "writer": {
            "plugin": "odps",
            "parameter": {
                "accessId": "bazhen.csy",
                "accessKey": "xxx",
                "project": "project",
                "table": "table",
                "column": [
                    "*"
                ],
                "partition": "pt=20140501"
            }
        }
    }
}

使用JDBC配置一個從SqlServer數據庫同步抽取數據到ODPS的作業:

{
    "type": "job",
    "traceId": "您可以在這裏填寫您作業的追蹤ID，建議使用業務名+您的作業ID",
    "version": "1.0",
    "configuration": {
        "reader": {
            "plugin": "sqlserver",
            "parameter": {
                "jdbcUrl": "jdbc:sqlserver://localhost:3433;DatabaseName=dbname",
                "username": "username",
                "password": "xxx",
                "splitPk": "id",
                "fetchSize": 512,
                "table": "table_01",
                "column": ["a", "b", "c"],
                "where": "1 = 1"
            }
        },
        "writer": {
            "plugin": "odps",
            "parameter": {
                "accessId": "bazhen.csy",
                "accessKey": "xxx",
                "project": "project",
                "table": "table",
                "column": ["*"],
                "partition": "pt=20140501"
            }
        }
    }
}

3.2 參數說明

instanceName
- 描述: 阿裏雲RDS實例名稱(Instance名稱)。用戶使用該配置指定RDS的Instance名稱，CDP將翻譯為底層執行的jdbc連接串連接。
  
  instanceName指定的是RDS實例，類似Mysql實例，需要和database配合使用。
- 必選：是
- 默認值：無
jdbcUrl
- 描述：對於CDP部分私有雲場景，數據庫遷移場景等，其本身數據源是普通SqlServer數據庫不是RDS，對於這類場景，用戶可以指定jdbc信息直連。
  
  jdbcUrl和instanceName/database兩類信息概念上是等同的，因此隻能配置其一。如果兩者均配置，CDP默認將使用jdbc信息。
- 必選：是
- 默認值：無
username
- 描述：數據源的用戶名
- 必選：是
- 默認值：無
password
- 描述：數據源指定用戶名的密碼
- 必選：是
- 默認值：無
table
- 描述：所選取的需要同步的表，一個作業隻能支持一個表同步
- 必選：是
- 默認值：無
column
- 描述：所配置的表中需要同步的列名集合，使用JSON的數組描述字段信息。用戶使用代表默認使用所有列配置，例如[""]。
  
  支持列裁剪，即列可以挑選部分列進行導出。
  
  支持列換序，即列可以不按照表schema信息進行導出。
  
  支持常量配置，用戶需要按照JSON格式:
  
  ["id", "[table]", "1", "'bazhen.csy'", "null", "COUNT(*)", "2.3" , "true"]
  
  id為普通列名，[table]為包含保留字的列名，1為整形數字常量，'bazhen.csy'為字符串常量，null為空指針，to_char(a + 1)為表達式，2.3為浮點數，true為布爾值。
  
  column必須用戶顯示指定同步的列集合，不允許為空！
- 必選：是
- 默認值：無
splitPk
- 描述：SQLServerReader進行數據抽取時，如果指定splitPk，表示用戶希望使用splitPk代表的字段進行數據分片，CDP因此會啟動並發任務進行數據同步，這樣可以大大提供數據同步的效能。
  
  推薦splitPk用戶使用表主鍵，因為表主鍵通常情況下比較均勻，因此切分出來的分片也不容易出現數據熱點。
  
  目前splitPk僅支持整形、字符串型數據切分，不支持浮點、日期等其他類型。如果用戶指定其他非支持類型，SQLServerReader將報錯！
- 必選：否
- 默認值：空
where
- 描述：篩選條件，SQLServerReader根據指定的column、table、where條件拚接SQL，並根據這個SQL進行數據抽取。例如在做測試時，可以將where條件指定為limit 10；在實際業務場景中，往往會選擇當天的數據進行同步，可以將where條件指定為gmt_create > $bizdate; where條件可以有效地進行業務增量同步。如果該值為空，代表同步全表所有的信息。
- 必選：否
- 默認值：無
querySql
- 描述：在有些業務場景下，where這一配置項不足以描述所篩選的條件，用戶可以通過該配置型來自定義篩選SQL。當用戶配置了這一項之後，CDP係統就會忽略table，column這些配置型，直接使用這個配置項的內容對數據進行篩選，例如需要進行多表join後同步數據，使用select a,b from table_a join table_b on table_a.id = table_b.id ;當用戶配置querySql時，SQLServerReader直接忽略table、column、where條件的配置。
- 必選：否
- 默認值：無
fetchSize
- 描述：該配置項定義了插件和數據庫服務器端每次批量數據獲取條數，該值決定了CDP和服務器端的網絡交互次數，能夠較大的提升數據抽取性能。注意，該值過大(>2048)可能造成CDP進程OOM。
- 必選：否
- 默認值：1024

3.3 類型轉換

目前SQLServerReader支持大部分SqlServer類型，但也存在部分個別類型沒有支持的情況，請注意檢查你的類型。

下麵列出SQLServerReader針對SqlServer類型轉換列表:

CDP 內部類型	SQLServer 數據類型
Long	bigint, int, smallint, tinyint
Double	float, decimal, real, numeric
String	char,nchar,ntext,nvarchar,text,varchar,nvarchar(MAX),varchar(MAX)
Date	date, datetime, time
Boolean	bit
Bytes	binary,varbinary,varbinary(MAX),timestamp

請注意:

除上述羅列字段類型外，其他類型均不支持。
timestamp類型作為二進製類型。

4 約束限製

4.1 主備同步數據恢複問題

主備同步問題指SqlServer使用主從災備，備庫從主庫不間斷通過binlog恢複數據。由於主備數據同步存在一定的時間差，特別在於某些特定情況，例如網絡延遲等問題，導致備庫同步恢複的數據與主庫有較大差別，導致從備庫同步的數據不是一份當前時間的完整鏡像。

CDP如果同步的是阿裏雲提供RDS，是直接從主庫讀取數據，不存在數據恢複問題。但是會引入主庫負載問題，請注意流控配置。

4.2 一致性約束

SqlServer在數據存儲劃分中屬於RDBMS係統，對外可以提供強一致性數據查詢接口。例如當一次同步任務啟動運行過程中，當該庫存在其他數據寫入方寫入數據時，SQLServerReader完全不會獲取到寫入更新數據，這是由於數據庫本身的快照特性決定的。關於數據庫快照特性，請參看MVCC Wikipedia

上述是在SQLServerReader單線程模型下數據同步一致性的特性，由於SQLServerReader可以根據用戶配置信息使用了並發數據抽取，因此不能嚴格保證數據一致性：當SQLServerReader根據splitPk進行數據切分後，會先後啟動多個並發任務完成數據同步。由於多個並發任務相互之間不屬於同一個讀事務，同時多個並發任務存在時間間隔。因此這份數據並不是完整的、一致的數據快照信息。

針對多線程的一致性快照需求，在技術上目前無法實現，隻能從工程角度解決，工程化的方式存在取舍，我們提供幾個解決思路給用戶，用戶可以自行選擇：

使用單線程同步，即不再進行數據切片。缺點是速度比較慢，但是能夠很好保證一致性。
關閉其他數據寫入方，保證當前數據為靜態數據，例如，鎖表、關閉備庫同步等等。缺點是可能影響在線業務。

4.3 數據庫編碼問題

SQLServerReader底層使用JDBC進行數據抽取，JDBC天然適配各類編碼，並在底層進行了編碼轉換。因此SQLServerReader不需用戶指定編碼，可以自動識別編碼並轉碼。

4.4 增量數據同步

SQLServerReader使用JDBC SELECT語句完成數據抽取工作，因此可以使用SELECT...WHERE...進行增量數據抽取，方式有多種：

數據庫在線應用寫入數據庫時，填充modify字段為更改時間戳，包括新增、更新、刪除(邏輯刪)。對於這類應用，SQLServerReader隻需要WHERE條件跟上一同步階段時間戳即可。
對於新增流水型數據，SQLServerReader可以WHERE條件後跟上一階段最大自增ID即可。

對於業務上無字段區分新增、修改數據情況，SQLServerReader也無法進行增量數據同步，隻能同步全量數據。

4.5 Sql安全性

SQLServerReader提供querySql語句交給用戶自己實現SELECT抽取語句，SQLServerReader本身對querySql不做任何安全性校驗。這塊交由CDP用戶方自己保證。

最後更新：2016-11-23 16:03:59