datax数据传输实践

1，参考文档

https://zhuanlan.zhihu.com/p/515541286

https://github.com/alibaba/DataX/blob/master/introduction.md

https://github.com/alibaba/DataX

2，sqlserver_2_clickhouse datax脚本

（*）注意这里：sqlserverreader,clickhousewriter 是固定写法

{
  "job": {
    "setting": {
      "speed": {
        "channel":1
      }
    },
    "content": [
      {
        "reader": {
          "name": "sqlserverreader",
          "parameter": {
            "username": "root",
            "password": "123123",
            "connection": [
              {
                "querySql": [
                  "select id,user_name,money from xiaoming.dbo.zy_user;"
                ],
                "jdbcUrl": [
                  "jdbc:sqlserver://xxx.rds.aliyuncs.com:3433"
                ]
              }
            ]
          }
        },
        "writer": {
          "name": "clickhousewriter",
          "parameter": {
            "writeMode": "insert",
            "username": "default",
            "password": "123123",
            "column": [
              "id",
              "user_name",
              "money"
            ],
            "session": [
              "set session sql_mode='ANSI'"
            ],
            "preSql": [
              "truncate table bigdata.ods_zy_user;"
            ],
            "connection": [
              {
                "jdbcUrl":"jdbc:clickhouse://localhost:8123/bigdata?useUnicode=true&characterEncoding=utf-8",
                "table": [
                  "ods_zy_user"
                ]
              }
            ]
          }
        }
      }
    ]
  }
}

{
"job": {
    "setting": {
      "speed": {
        "channel": 1
      }
    },
    "content": [
      {
        "reader": {
          "name": "sqlserverreader",
          "parameter": {
            "username": "your_db_name",
            "password": "your_pwd",
            "column": [
              "[FCUSTID]",
              "[FNAME]",
              "[FDocumentStatus]",
              "[FForbidStatus]",
              "[FCreateOrgId]",
              "[FUseOrgId]",
              "[FCreateDate]",
              "[FModifyDate]",
              "[F_QFES_Text8]",
              "[F_QFES_Text10]",
              "[FDescription]",
              "[C1FDATAVALUE]",
              "[C2FDATAVALUE]",
              "[C3FDATAVALUE]",
              "[FNUMBER]"
            ],
            "splitPk": "sale_id",
            "connection": [
              {
                "table": [
                  "your_tabname"
                ],
                "jdbcUrl": [
                  "jdbc:sqlserver://localhost:3433;DatabaseName=xxdbname"
                ]
              }
            ]
          }
        },
        "writer": {
          "name": "clickhousewriter",
          "parameter": {
            "username": "root",
            "password": "123123",
            "column": [
              "fcustid",
              "fname",
              "fdocumentstatus",
              "forbidstatus",
              "fcreate_orgid",
              "fuser_orgid",
              "fcreate_date",
              "fmodify_date",
              "f_qfes_text8",
              "f_qfes_text10",
              "fdescription",
              "c1_fdata_value",
              "c2_fdata_value",
              "c3_fdata_value",
              "fnumber"
            ],
            "connection": [
              {
                "table": [
                  "your_table_name"
                ],
                "jdbcUrl": "jdbc:clickhouse://localhost:8123/table_name"
              }
            ]
          }
        }
      }
    ]
  }
}

3，datax json格式脚本运行

# Linux运行指令
cd /opt/datax/bin
python datax.py -r streamreader -w streamwriter
python datax/bin/datax.py ./sqlserver_2_ch.json