DataWorks的SQLServer数据同步的能力支持情况_大数据开发治理平台 DataWorks(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与参数说明。

整库离线读同步任务配置指导

操作流程请参见数据集成侧同步任务配置。

常见问题

主备同步数据恢复问题
主备同步问题指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	该配置项定义了插件和数据库服务器端每次批量数据获取条数，该值决定了数据集成和服务器端的网络交互次数，能够提升数据抽取性能。说明 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方式。当主键/唯一性索引冲突时，数据集成视为脏数据但保留原有的数据。	否	insert
batchSize	一次性批量提交的记录数大小，该值可以极大减少数据同步系统与SQL Server的网络交互次数，并提升整体吞吐量。如果该值设置过大，会导致数据同步运行进程OOM异常。	否	1,024