DataX数据迁移入门从SQL Server至MySQL

PowehiCoding

已于 2024-03-18 17:35:46 修改

阅读量746

点赞数 1

分类专栏： DataX 文章标签： mysql 数据库数据库架构迁移学习 mssql

于 2023-07-31 15:24:21 首次发布

本文链接：https://blog.csdn.net/powehicoding/article/details/132018918

版权

DataX 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本文介绍了如何使用DataX进行数据库迁移，包括DataX和Python的安装，编写Json脚本来定义数据迁移规则，如字段转换、条件处理等，并提供了自定义SQL查询的使用方法。同时，提到了Windows命令行乱码问题的解决和时间戳、空值、金额转换等特殊情况的处理策略。

摘要由CSDN通过智能技术生成

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"
            ]
	}

PowehiCoding

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
DataX数据迁移入门从SQL Server至MySQL

DataX是阿里巴巴开源的离线数据同步工具，实现了包括主流RDBMS数据库、NoSQL、大数据计算系统在内的多种异构数据源之间高效进行数据同步的功能。本文主要讲解使用DataX迁移数据，从SQL Server 迁至 Mysql，文章通俗易懂，适合小白和有经验的猿友。
复制链接

扫一扫

专栏目录