文章目录
前言
DataX 是一款由阿里巴巴开源的数据同步工具,它提供了丰富的数据源插件,支持包括但不限于 MySQL、Oracle、HDFS 等多种数据源的同步。以下是几种常见同步场景的 JSON 模板示例。
1. HDFS 到 MySQL (hdfsToMysql)
- 当您需要将数据从 HDFS 同步到 MySQL 时,可以使用如下的 JSON 模板:
{
"job": {
"content": [
{
"reader": {
"name": "hdfsreader",
"parameter": {
"path": "/user/hive/warehouse/ads.db/ads_zhy_site_stat_di/date=${dt1}",
"defaultFS": "hdfs://dn22:8020",
"fileType": "parquet",
"skipHeader": false,
"column": [
{"index":"0","type":"string"},
{"index":"1","type":"string"},
{"index":"2","type":"int"},
{"index":"3","type":"int"},
{ "name": "date",
"type": "string",
"value":"${dt1}"
}
]
}
},
"writer": {
"name": "mysqlwriter",
"parameter": {
"writeMode": "insert",
"username": "cnooc_fuse",
"password": "root",
"column": [
"`site_id`",
"`fuel_type`",
"`vehicle_num`",
"`through_vehicle_num`",
"`date`"
],
"connection": [
{
"table": [
"t_site_flow_stat_day"
],
"jdbcUrl": "jdbc:mysql://172.0.0.1:8086/cnooc_fuse?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&dontTrackOpenResources=true"
}
]
}
}
}
]
,
"setting": {
"speed": {
"channel": "2"
}
}
}
}
1.2 HDFS 到 Oracle (hdfsToOracle)
- 将数据从 HDFS 同步到 Oracle 的 JSON 模板如下:
{
"job": {
"content": [
{
"reader": {
"name": "hdfsreader",
"parameter": {
"path": "/user/hive/warehouse/mid.db/hky_veh_run_time/dt=${dt}",
"defaultFS": "hdfs://nn01:8020",
"fileType": "parquet",
"skipHeader": false,
"column": [
{"index":"0","type":"long"},
{"index":"1","type":"int"},
{"index":"2","type":"int"},
{ "name": "dt",
"type": "long",
"value":"${dt}"
}
]
}
},
"writer": {
"name": "oraclewriter",
"parameter": {
"username": "root",
"password": "root",
"column": [
"TRANS",
"AD_CODE",
"RUN_TIME",
"DT"
],
"connection": [
{
"table": [
"HKY_VEH_RUN_TIME"
],
"jdbcUrl": "jdbc:oracle:thin:@172.0.0.1:1521:rdt1"
}
]
}
}
}
],
"setting": {
"speed": {
"channel": "2"
}
}
}
}
1.3 HDFS 到 人大金仓 (hdfsToKingbasees)
- 当您需要将数据从 HDFS 同步到 人大金仓 时,可以使用如下的 JSON 模板:
{
"job": {
"content": [
{
"reader": {
"name": "hdfsreader",
"parameter": {
"path": "/user/hive/warehouse/ads.db/ads_zhy_site_owner_di/date=${dt1}",
"defaultFS": "hdfs://dn22:8020",
"fileType": "parquet",
"skipHeader": false,
"column": [
{
"index": "0",
"type": "string"
},
{
"index": "1",
"type": "string"
},
{
"index": "2",
"type": "int"
},
{
"index": "3",
"type": "string"
},
{
"name": "date",
"type": "string",
"value": "${dt1_str}"
}
]
}
},
"writer": {
"name": "kingbaseeswriter",
"parameter": {
"username": "root",
"password": "root",
"column": [
"`site_id`",
"`owner_id`",
"`vehicle_num`",
"`fuel_type`",
"`date`"
],
"connection": [
{
"table": [
"t_site_flow_owner_day"
],
"jdbcUrl": "jdbc:kingbase8://172.0.0.1:8086/cnooc_fuse"
}
]
}
}
}
],
"setting": {
"speed": {
"channel": "2"
}
}
}
}
1.4 HDFS 到 Doris (hdfsToDoris)
- 当您需要将数据从 HDFS 同步到 Doris 时,可以使用如下的 JSON 模板:
{
"job": {
"content": [
{
"reader": {
"name": "hdfsreader",
"parameter": {
"path": "/user/hive/warehouse/dws.db/dws_zqxk_gnss_vehicle_operation_di/data_time=${dt1}",
"defaultFS": "hdfs://dn22:8020",
"fileType": "parquet",
"skipHeader": false,
"column": [
{
"name": "data_time",
"type": "int",
"value": "${dt1}"
},
{"index":"0","type":"int"},
{"index":"1","type":"int"},
{"index":"2","type":"int"},
{"index":"3","type":"int"},
{"index":"4","type":"int"},
{"index":"5","type":"string"},
{"index":"6","type":"int"},
{"index":"7","type":"string"},
{"index":"8","type":"string"},
{"index":"9","type":"int"},
{"index":"10","type":"int"},
{"index":"11","type":"int"}
]
}
},
"writer": {
"name": "doriswriter",
"parameter": {
"loadUrl": [
"172.0.0.1:8030"
],
"loadProps": {
"format": "json",
"strip_outer_array": true
},
"column": [
"data_time",
"access_province_code",
"strain_code",
"fuel_type",
"emission_standard",
"market_segment",
"access_province_name",
"kingpin_area",
"company_name",
"brand_name",
"run_time",
"run_mileage",
"vehicle_num"
],
"username": "root",
"password": "xxxx",
"postSql": [],
"preSql": [],
"flushInterval": 30000,
"connection": [
{
"jdbcUrl": "jdbc:mysql://172.0.0.0:9030/dws",
"selectedDatabase": "dws",
"table": [
"dws_gnss_vehicle_operation_di"
]
}
]
}
}
}
]
,
"setting": {
"speed": {
"channel": "2"
}
}
}
}
2. MySQL 到 HDFS (mysqlToHdfs)
- 从 MySQL 同步数据到 HDFS 的 JSON 模板示例:
{
"job": {
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "cnooc_fuse",
"password": "root",
"connection": [{
"querySql": [
"SELECT id,site_name,area_type,province,city,site_type,company_name,tel,lon,lat,is_cnooc,address, section_code,road_type,road_name,site_state,
recommend_type, manage_type, remark, note, create_user,create_time, update_user, update_time, is_deleted
FROM t_site_new "
],
"jdbcUrl": [
"jdbc:mysql://172.0.0.1:8086/cnooc_fuse?allowLoadLocalInfile=false&autoDeserialize=false&allowLocalInfile=false&allowUrlInLocalInfile=false"
]
}]
}
},
"writer": {
"name": "hdfswriter",
"parameter": {
"column": [
{
"name": "id",
"type": "int"
},
{
"name": "site_name",
"type": "string"
},
{
"name": "area_type",
"type": "int"
},
{
"name": "province",
"type": "int"
},
{
"name": "city",
"type": "int"
},
{
"name": "site_type",
"type": "int"
},
{
"name": "company_name",
"type": "string"
},
{
"name": "tel",
"type": "string"
},
{
"name": "lon",
"type": "double"
},
{
"name": "lat",
"type": "double"
},
{
"name": "is_cnooc",
"type": "int"
},
{
"name": "address",
"type": "string"
},
{
"name": "section_code",
"type": "string"
},
{
"name": "road_type",
"type": "string"
},
{
"name": "road_name",
"type": "string"
},
{
"name": "site_state",
"type": "string"
},
{
"name": "recommend_type",
"type": "string"
},
{
"name": "manage_type",
"type": "string"
},
{
"name": "remark",
"type": "string"
},
{
"name": "note",
"type": "string"
},
{
"name": "create_user",
"type": "string"
},
{
"name": "update_user",
"type": "string"
},
{
"name": "create_time",
"type": "string"
},
{
"name": "update_time",
"type": "string"
},
{
"name": "is_deleted",
"type": "int"
}
],
"compress": "snappy",
"defaultFS": "hdfs://nn01:8020",
"fieldDelimiter":",",
"fileName": "datax",
"fileType": "parquet",
"path": "/user/hive/warehouse/ods.db/ods_tran_logistics_site/",
"writeMode": "truncate"
}
}
}
],
"setting": {
"speed": {
"channel": "2"
}
}
}
}
3. Oracle 到 Doris (oracleToDoris)
- 将数据从 Oracle 同步到 Doris 的 JSON 模板:
{
"job": {
"setting": {
"speed": {
"channel": 1
},
"errorLimit": {
"record": 0,
"percentage": 0
}
},
"content": [
{
"reader": {
"name": "oraclereader",
"parameter": {
"column": [
"SERIAL_ID",
"CYCLE",
"REGION_CODE",
"TYPE",
"SRC_REGION",
"DST_REGION",
"SRC_TIME",
"HB_TIME",
"STATUS",
"CREATE_TIME"
],
"connection": [
{
"jdbcUrl": [
"jdbc:oracle:thin:@//172.0.0.1:32021/s_tsshprod"
],
"table": [
"HEART_BEAT_INFO"
]
}
],
"password": "root",
"splitPk": "",
"username": "tssh",
"where": "1=1 and CREATE_TIME >= TO_DATE('${dt}', 'YYYY-MM-DD HH24:MI:SS') - INTERVAL '1:10' HOUR TO MINUTE"
}
},
"writer": {
"name": "doriswriter",
"parameter": {
"loadUrl": [
"172.0.0.1:8030"
],
"loadProps": {
"format": "json",
"strip_outer_array": true
},
"column": [
"serial_id",
"cycle",
"region_code",
"type",
"src_region",
"dst_region",
"src_time",
"hb_time",
"status",
"create_time"
],
"username": "yunwei",
"password": "root",
"postSql": [],
"preSql": [],
"flushInterval": 30000,
"connection": [
{
"jdbcUrl": "jdbc:mysql://172.0.0.1:9030/collect",
"selectedDatabase": "collect",
"table": [
"dl_01_heart_beat_info"
]
}
]
}
}
}
]
}
}
3.1 Oracle 到 HDFS (oracleToHDFS)
- 将数据从 Oracle 同步到 HDFS 的 JSON 模板:
{
"job": {
"content": [
{
"reader": {
"name": "oraclereader",
"parameter": {
"column": [
"code",
"parent_code",
"ancestors",
"name",
"province_code",
"province_name",
"city_code",
"city_name",
"district_code",
"district_name",
"town_code",
"town_name",
"village_code",
"village_name",
"region_level",
"sort",
"remark"
],
"connection": [
{
"jdbcUrl": [
"jdbc:oracle:thin:@172.0.0.1:1521:rdt1"
],
"table": [
"BLADE_REGION"
]
}
],
"username": "errtrans",
"password": "errtrans",
"splitPk": "",
"where": "1=1",
"encoding": "UTF-8"
}
},
"writer": {
"name": "hdfswriter",
"parameter": {
"column": [
{
"name": "code",
"type": "string"
},
{
"name": "parent_code",
"type": "string"
},
{
"name": "ancestors",
"type": "string"
},
{
"name": "name",
"type": "string"
},
{
"name": "province_code",
"type": "string"
},
{
"name": "province_name",
"type": "string"
},
{
"name": "city_code",
"type": "string"
},
{
"name": "city_name",
"type": "string"
},
{
"name": "district_code",
"type": "string"
},
{
"name": "district_name",
"type": "string"
},
{
"name": "town_code",
"type": "string"
},
{
"name": "town_name",
"type": "string"
},
{
"name": "village_code",
"type": "string"
},
{
"name": "village_name",
"type": "string"
},
{
"name": "region_level",
"type": "int"
},
{
"name": "sort",
"type": "int"
},
{
"name": "remark",
"type": "string"
}
],
"compress": "snappy",
"defaultFS": "hdfs://dn22:8020",
"fieldDelimiter": ",",
"fileName": "datax",
"fileType": "parquet",
"path": "/user/hive/warehouse/dim.db/dim_region",
"writeMode": "truncate",
"encoding": "UTF-8"
}
}
}
],
"setting": {
"speed": {
"channel": "2"
}
}
}
}
总结
如果此篇文章有帮助到您, 希望打大佬们能
关注
、点赞
、收藏
、评论
支持一波,非常感谢大家!
如果有不对的地方请指正!!!