1.安装
1.1 DataX
下载,解压到本地目录即可,依赖Java。
提取码:y1ek
1.2 Python
安装Python2或者3都可以。
提取码:sik8
2.使用
2.1 编写Json脚本
以 Role 表为例:
{
"job": {
"setting": {
"speed": {
"channel": 5,
"byte": -1,
"record": -1
},
"errorLimit": {
"record": 0
}
},
"core": {
"transport": {
"channel": {
"speed": {
"channel": 5,
"byte": 1048576,
"batchSize": 2048,
"record": 1000
}
}
}
},
"content": [
{
"reader": {
"name": "sqlserverreader",
"parameter": {
"username": "username",
"password": "password",
"connection": [
{
"jdbcUrl": [
"jdbc:sqlserver://xx.xxx.xx.xx:1433;DatabaseName=xx;characterEncoding=UTF-8"
],
"table": [
"role"
]
}
],
"column": [
"role_id",
"role_name",
"founder",
"state",
"create_time",
"update_time",
"level",
"show_state"
],
"where": "1=1"
}
},
"writer": {
"name": "mysqlwriter",
"parameter": {
"writeMode": "insert",
"username": "username",
"password": "password",
"connection": [
{
"jdbcUrl": "jdbc:mysql://xx.xxx.xxx.xx:3306/xxx?allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8",
"table": [
"role"
]
}
],
"dateFormat": "YYYY-MM-dd hh:mm:ss",
"column": [
"role_id",
"role_name",
"founder",
"state",
"create_time",
"update_time",
"level",
"show_state"
],
"session": [
"set session sql_mode='ANSI'"
]
}
}
}
]
}
}
需要手动调整的参数:
- 源表所在数据库的信息:用户名、密码、Url
- 源表要导出的字段列表(因为是基于目标表的字段生成,所以可能与源表字段不匹配)
- 源表导出时的where条件
- 源表名称
- 目标表所在数据库的信息:用户名、密码、Url
- 目标表要导入的字段列表(需要与上面源表字段列表顺序一致)
- 目标表名称
注意:目标表导入的字段列表需要和源表字段顺序一致,否则数据会错乱,DataX根据顺序逐个匹配
2.2 运行脚本
假设datax目录在:D:\datax
脚本目录在:D:\script
运行某个json脚本:
python D:\datax\bin\datax.py D:\script\role.json
2.3 Windows命令行乱码
执行:
chcp 65001
3.说明
3.1 时间戳
若源库中时间都是long类型的时间戳,目标库中都是datetime类型。
datax可以自动转换,不需要额外处理。
3.2 源表Where条件
默认生成的是:
"where": "1=1"
可以添加自定义条件,如:
"where": "state='xxx'"
3.3 新字段默认值
假设是新字段,源表中没有,并且需要填入默认值,如:delete_flag,默认为0,那么可以在源表column中添加:
"column": [
......
"0 as delete_flag"
],
注意:需要添加as标识字段名。
示例:
会员表,默认platform_group_num=1,db_num=1
"column": [
......
"1 as platform_group_num",
"1 as db_num"
],
注意:
如果新字段,非必填项,并且不需要默认值,那么直接从源表字段列表、目标表字段列表中移除即可,代表根本不需要处理这个字段。
3.4 源表可空,目标表不为空
如果源表字段可以为空,并且有为空的数据,但是目标表不能为空,那么在源表column列中进行处理(SqlServer函数):
"column": [
......
"case when len(trade_no)=0 then '' else trade_no end as trade_no"
],
trade_no字段在源表中可以为空,目标表不允许,那么进行一个处理,如果为null,则替换为空字符串“”。
注意:
DataX运行时,会将Column中的列拼接成Sql,所以可以写合法的源数据库函数等等。
3.5 金额元变分
源库中金额相关都是浮点数,单位是元;
目标库统一都改成了整型,单位是分。
可以在源表column列中进行处理(SqlServer函数):
"column": [
......
"round(balance*100, 0) as balance"
],
先乘100,再调用SqlServer的round函数,四舍五入,小数点后保留0位。
假设旧数据有小于1分的数据,那么乘100后四舍五入,最多偏差几厘,可以忽略。
注意:需要添加as标识计算后的字段名。
3.6 字段拼接
若目标表 字段为 源表中两个字段拼接而成。
示例:
源表字段:id,age
新表字段:id_age
"column": [
"concat(id, age) as id_age",
......
],
3.7 生成Id字段
有的表,新增了id字段,源表没有,此时可以使用SqlServer的row_number()函数生成。
示例:
若字段Id为新增字段,源表不存在(SqlServer函数):
"column": [
"row_number() over(order by sn) as id",
......
],
可以指定起始值:
"column": [
"1000+row_number() over(order by sn) as id",
......
],
id从1001开始。
注意:
- 如果是一次性导入全部数据,使用上面方法即可。
- 如果是根据不同的复杂条件,需要join才能查询出正确的数据,那么使用第4章节的Sql类型脚本,编写复杂的查询条件,同时结合row_number来使用。
4.自定义SQL类型脚本
4.1 源表查询Sql
DataX脚本支持直接写源表查询Sql。
如果表字段较多,那么尽量避免使用此种方式,因为Sql必须写在一个""中,在json文件里没法换行,不方便检查Sql正确性。
以迁移role表为例:
{
"job": {
"setting": {
"speed": {
"channel": 5,
"byte": -1,
"record": -1
},
"errorLimit": {
"record": 0
}
},
"core": {
"transport": {
"channel": {
"speed": {
"channel": 5,
"byte": 1048576,
"batchSize": 2048,
"record": 1000
}
}
}
},
"content": [
{
"reader": {
"name": "sqlserverreader",
"parameter": {
"username": "xx",
"password": "xxx",
"connection": [
{
"jdbcUrl": [ "jdbc:sqlserver://xx.xx.xx.xx:1433;DatabaseName=xxx;characterEncoding=UTF-8"
],
"queryUrl": [
"select role_id, role_name, ..... from role"
]
}
]
}
},
"writer": {
"name": "mysqlwriter",
"parameter": {
"writeMode": "insert",
"username": "xxx",
"password": "xxx_",
"connection": [
{
"jdbcUrl": "jdbc:mysql://xx.xx.xx.xx:3306/xxx?allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8",
"table": [
"role"
]
}
],
"dateFormat": "YYYY-MM-dd hh:mm:ss",
"column": [
"role_id",
"role_name",
"founder",
"state",
"create_time",
"update_time",
"level",
"show_state"
],
"session": [
"set session sql_mode='ANSI'"
]
}
}
}
]
}
}
需要手工调整:
- 源表所在数据库的信息:用户名、密码、Url
- 源表查询Sql
- 目标表所在数据库的信息:用户名、密码、Url
- 目标表要导入的字段列表(需要与上面源表Sql查询中字段列表顺序一致)
- 目标表名称
Sql注意:
- 可以编写复杂的Sql,如join等等
- 需要显示指定导出的列名,不能使用“*”
4.2 目标表数据导入前Sql
可以在目标表数据导入前,执行自定义Sql。
示例:
在导入数据前,清除目标表中platform_sn='abc‘的数据
......
"writer": {
"name": "mysqlwriter",
"parameter": {
"writeMode": "insert",
"username": "xx",
"password": "xxx",
"connection": [
{
"jdbcUrl": "jdbc:mysql://127.0.0.1:3306/xxx",
"table": [
"member"
]
}
],
"dateFormat": "YYYY-MM-dd hh:mm:ss",
"column": [
......
],
"session": [
"set session sql_mode='ANSI'"
],
"preSql": [
"delete from member where platform_sn='abc'"
]
}
}
4.3 目标表数据导入后Sql
可以在目标表数据导入后,执行自定义Sql。
示例:
在导入数据后,填充platform_sn字段:
......
"writer": {
"name": "mysqlwriter",
"parameter": {
"writeMode": "insert",
"username": "xx",
"password": "xxx",
"connection": [
{
"jdbcUrl": "jdbc:mysql://127.0.0.1:3306/xxx",
"table": [
"member_role"
]
}
],
"dateFormat": "YYYY-MM-dd hh:mm:ss",
"column": [
......
],
"session": [
"set session sql_mode='ANSI'"
],
"postSql": [
"UPDATE member_role a LEFT JOIN member b ON a.sn = b.sn SET a.platform_sn = b.platform_sn"
]
}