思路:
查看日志,分成了多少个tasks
2021-11-17 05:32:36.654 [job-0] INFO JobContainer - DataX Reader.Job [oraclereader] splits to [51] tasks.
2021-11-17 05:32:36.654 [job-0] INFO JobContainer - DataX Writer.Job [mysqlwriter] splits to [51] tasks.
然后看看分段
select "PSN_CLCT_DETL_ID","EMP_CLCT_DETL_ID","CLCT_BILL_ID","EMP_NO","PSN_NO","CASHYM","ACCRYM_BEGN","ACCRYM_END","INSUTYPE","PSN_TYPE","PSN_IDET_TYPE","INSU_IDET","EMP_CLCTSTD","PSN_CLCTSTD","WAG","QUOT_CLCT_FLAG","EMP_CLCT_PARAVAL","EMP_INTO_PARAVAL","EMP_CLCT_AMT","EMP_CLCT_INTO_ACCT_AMT","PSN_CLCT_PARAVAL","PSN_INTO_PARAVAL","PSN_CLCT_AMT","PSN_CLCT_INTO_ACCT_AMT","OTH_CLCT_TRAF_AMT","OTH_CLCT_AMT","FINSUBS_AMT","FINSUBS_TRAF_AMT","TRAF_SUMAMT","CLCT_SUMAMT","INTE","LATEFEE","CLCT_FLAG","CLCT_TYPE","CLCT_TIME","ARVLER","ARVL_BCHNO","REVS_FLAG","TRAFER","TRAF_TIME","INTSURY_TIME","URSN_TIME","DCLA_PRD","ELEC_TAXRPT_NO","PEAWKR_FLAG","POOLAREA_NO","INSUTYPE_RETR_FLAG","TAXDEPT_CODE","PLAN_BCHNO","PSN_INSU_RLTS_ID","CLCTSTD_CRTF_RULE_CODG","CLCT_RULE_TYPE_CODG","INSU_CLCT_MONS","INIT_PSN_NO","INIT_PSN_CLCT_DETL_ID","ACCTER","CRTE_OPTINS_NO","CRTER_ID","CRTER_NAME","CRTE_TIME","OPTINS_NO","OPTER_ID","OPTER_NAME","OPT_TIME","RID","UPDT_TIME","BILL_FLAG","TRAF_FLAG" from STAF_PSN_CLCT_DETL_D where (INSUTYPE in ('310')) and ('150000202111141816040068648282' <= RID AND RID < '150000202111141816480072686711')
。。。。
整理出来所有
b (INSUTYPE in ('310')) and ('150000202111141816040068648282' <= RID AND RID < '150000202111141816480072686711')
b (INSUTYPE in ('310')) and ('150000202111141816480072686711' <= RID AND RID < '150000202111141818410079517748')
b (INSUTYPE in ('310')) and ('150000202111141818410079517748' <= RID AND RID < '150000202111141820570086267923')
b (INSUTYPE in ('310')) and ('150000202111141820570086267923' <= RID AND RID < '150000202111141823020092502583')
b (INSUTYPE in ('310')) and ('150000202111141823020092502583' <= RID AND RID < '150000202111141823140093105051')
x (INSUTYPE in ('310')) and ('150000202111141823140093105051' <= RID AND RID < '150000202111141836290032288915')
b (INSUTYPE in ('310')) and ('150000202111141836290032288915' <= RID AND RID < '150000202111141837380035554718')
b (INSUTYPE in ('310')) and ('150000202111141837380035554718' <= RID AND RID < '150000202111141838590039379617')
b (INSUTYPE in ('310')) and ('150000202111141838590039379617' <= RID AND RID < '150000202111141842040048301370')
a (INSUTYPE in ('310')) and ('150000202111141842040048301370' <= RID AND RID < '150000202111141844320055457938')
x (INSUTYPE in ('310')) and ('150000202111141844320055457938' <= RID AND RID < '150000202111141849170068916149')
b (INSUTYPE in ('310')) and ('150000202111141849170068916149' <= RID AND RID < '150000202111141849380069887006')
b (INSUTYPE in ('310')) and ('150000202111141849380069887006' <= RID AND RID < '150000202111141849400069987234')
x (INSUTYPE in ('310')) and ('150000202111141849400069987234' <= RID AND RID < '150000202111141850060071112917')
b (INSUTYPE in ('310')) and ('150000202111141850060071112917' <= RID AND RID < '150000202111141858090092201128')
b (INSUTYPE in ('310')) and ('150000202111141858090092201128' <= RID AND RID < '150000202111141858090092213329')
b (INSUTYPE in ('310')) and ('150000202111141858090092213329' <= RID AND RID < '150000202111141859380096099132')
b (INSUTYPE in ('310')) and ('150000202111141859380096099132' <= RID AND RID < '150000202111141901250000652263')
x (INSUTYPE in ('310')) and ('150000202111141901250000652263' <= RID AND RID < '150000202111141911280025548754')
b (INSUTYPE in ('310')) and ('150000202111141911280025548754' <= RID AND RID < '150000202111141917350040445532')
b (INSUTYPE in ('310')) and ('150000202111141917350040445532' <= RID AND RID < '150000202111141918590043454385')
b (INSUTYPE in ('310')) and ('150000202111141918590043454385' <= RID AND RID < '150000202111141919270044198126')
b (INSUTYPE in ('310')) and ('150000202111141919270044198126' <= RID AND RID < '150000202111141921020046212640')
x (INSUTYPE in ('310')) and ('150000202111141921020046212640' <= RID AND RID < '150000202111141932010063110436')
x (INSUTYPE in ('310')) and ('150000202111141932010063110436' <= RID AND RID < '150000202111141936300083264192')
b (INSUTYPE in ('310')) and ('150000202111141936300083264192' <= RID AND RID < '150000202111141939380097209616')
b (INSUTYPE in ('310')) and ('150000202111141939380097209616' <= RID AND RID < '150000202111141942380010692106')
a (INSUTYPE in ('310')) and ('150000202111141942380010692106' <= RID AND RID < '150000202111141954400065964500')
b (INSUTYPE in ('310')) and ('150000202111141954400065964500' <= RID AND RID < '150000202111141956480075731953')
b (INSUTYPE in ('310')) and ('150000202111141956480075731953' <= RID AND RID < '150000202111141957050077017772')
b (INSUTYPE in ('310')) and ('150000202111141957050077017772' <= RID AND RID < '150000202111142000200091227087')
b (INSUTYPE in ('310')) and ('150000202111142000200091227087' <= RID AND RID < '150000202111142004200007864399')
x (INSUTYPE in ('310')) and ('150000202111142004200007864399' <= RID AND RID < '150000202111142010210032105719')
x (INSUTYPE in ('310')) and ('150000202111142010210032105719' <= RID AND RID < '150000202111142020210069962674')
b (INSUTYPE in ('310')) and ('150000202111142020210069962674' <= RID AND RID < '150000202111142024000084103761')
b (INSUTYPE in ('310')) and ('150000202111142024000084103761' <= RID AND RID < '150000202111142027180095847236')
b (INSUTYPE in ('310')) and ('150000202111142027180095847236' <= RID AND RID < '150000202111142030110006573311')
b (INSUTYPE in ('310')) and ('150000202111142030110006573311' <= RID AND RID < '150000202111142044140057633722')
x (INSUTYPE in ('310')) and ('150000202111142044140057633722' <= RID AND RID < '150000202111142057180006022373')
b (INSUTYPE in ('310')) and ('150000202111142057180006022373' <= RID AND RID < '150000202111142059190014102556')
b (INSUTYPE in ('310')) and ('150000202111142059190014102556' <= RID AND RID < '150000202111142101590022496265')
x (INSUTYPE in ('310')) and ('150000202111142101590022496265' <= RID AND RID < '150000202111142107570046710236')
b (INSUTYPE in ('310')) and ('150000202111142107570046710236' <= RID AND RID < '150000202111142111380061698072')
b (INSUTYPE in ('310')) and ('150000202111142111380061698072' <= RID AND RID < '150000202111142113100068120046')
b (INSUTYPE in ('310')) and ('150000202111142113100068120046' <= RID AND RID < '150000202111142122140004349472')
b (INSUTYPE in ('310')) and ('150000202111142122140004349472' <= RID AND RID < '150000202111142125060015622749')
a (INSUTYPE in ('310')) and ('150000202111142125060015622749' <= RID AND RID < '150000202111142137530061457315')
b (INSUTYPE in ('310')) and ('150000202111142137530061457315' <= RID AND RID < '150000202111142141390074617059')
b (INSUTYPE in ('310')) and ('150000202111142141390074617059' <= RID AND RID <= '150000202111142147380095865377')
b (INSUTYPE in ('310')) and ((RID < '150000202111141816040068648282') OR ('150000202111142147380095865377' < RID))
b (INSUTYPE in ('310')) and RID IS NULL
在日志内找那些完成了
2021-11-17 05:44:35.623 [taskGroup-0] INFO TaskGroupContainer - taskGroup[0] taskId[45] is successed, used[718797]ms
其他未完成的都delete半截记录
delete FROM staf_psn_clct_detl_d where CRTER_ID='NEUSOFT_HET' and (INSUTYPE in ('310')) and ('150000202111141823140093105051' <= RID AND RID < '150000202111141836290032288915') ;
delete FROM staf_psn_clct_detl_d where CRTER_ID='NEUSOFT_HET' and (INSUTYPE in ('310')) and ('150000202111141844320055457938' <= RID AND RID < '150000202111141849170068916149') ;
delete FROM staf_psn_clct_detl_d where CRTER_ID='NEUSOFT_HET' and (INSUTYPE in ('310')) and ('150000202111141849400069987234' <= RID AND RID < '150000202111141850060071112917') ;
delete FROM staf_psn_clct_detl_d where CRTER_ID='NEUSOFT_HET' and (INSUTYPE in ('310')) and ('150000202111141901250000652263' <= RID AND RID < '150000202111141911280025548754') ;
delete FROM staf_psn_clct_detl_d where CRTER_ID='NEUSOFT_HET' and (INSUTYPE in ('310')) and ('150000202111141921020046212640' <= RID AND RID < '150000202111141932010063110436') ;
delete FROM staf_psn_clct_detl_d where CRTER_ID='NEUSOFT_HET' and (INSUTYPE in ('310')) and ('150000202111141932010063110436' <= RID AND RID < '150000202111141936300083264192') ;
delete FROM staf_psn_clct_detl_d where CRTER_ID='NEUSOFT_HET' and (INSUTYPE in ('310')) and ('150000202111142004200007864399' <= RID AND RID < '150000202111142010210032105719') ;
delete FROM staf_psn_clct_detl_d where CRTER_ID='NEUSOFT_HET' and (INSUTYPE in ('310')) and ('150000202111142010210032105719' <= RID AND RID < '150000202111142020210069962674') ;
delete FROM staf_psn_clct_detl_d where CRTER_ID='NEUSOFT_HET' and (INSUTYPE in ('310')) and ('150000202111142044140057633722' <= RID AND RID < '150000202111142057180006022373') ;
delete FROM staf_psn_clct_detl_d where CRTER_ID='NEUSOFT_HET' and (INSUTYPE in ('310')) and ('150000202111142101590022496265' <= RID AND RID < '150000202111142107570046710236') ;
然后开始重新传输,按照分片键
python ..\..\bin\datax.py _PSN_CLCT_DETL_D150000202111141823140093105051.json
python ..\..\bin\datax.py _PSN_CLCT_DETL_D150000202111141842040048301370.json
python ..\..\bin\datax.py _PSN_CLCT_DETL_D150000202111141844320055457938.json
python ..\..\bin\datax.py _PSN_CLCT_DETL_D150000202111141849400069987234.json
python ..\..\bin\datax.py _PSN_CLCT_DETL_D150000202111141901250000652263.json
python ..\..\bin\datax.py _PSN_CLCT_DETL_D150000202111141921020046212640.json
python ..\..\bin\datax.py _PSN_CLCT_DETL_D150000202111141932010063110436.json
python ..\..\bin\datax.py _PSN_CLCT_DETL_D150000202111141942380010692106.json
python ..\..\bin\datax.py _PSN_CLCT_DETL_D150000202111142004200007864399.json
python ..\..\bin\datax.py _PSN_CLCT_DETL_D150000202111142010210032105719.json
python ..\..\bin\datax.py _PSN_CLCT_DETL_D150000202111142044140057633722.json
python ..\..\bin\datax.py _PSN_CLCT_DETL_D150000202111142101590022496265.json
python ..\..\bin\datax.py _PSN_CLCT_DETL_D150000202111142125060015622749.json
转换完毕核对总数即可
附job.json
{
"job": {
"content": [
{
"reader": {
"name": "oraclereader",
"parameter": {
"column": [
"\"PSN_CLCT_DETL_ID\"",
"\"EMP_CLCT_DETL_ID\"",
"\"CLCT_BILL_ID\"",
"\"EMP_NO\"",
"\"PSN_NO\"",
"\"CASHYM\"",
"\"ACCRYM_BEGN\"",
"\"ACCRYM_END\"",
"\"INSUTYPE\"",
"\"PSN_TYPE\"",
"\"PSN_IDET_TYPE\"",
"\"INSU_IDET\"",
"\"EMP_CLCTSTD\"",
"\"PSN_CLCTSTD\"",
"\"WAG\"",
"\"QUOT_CLCT_FLAG\"",
"\"EMP_CLCT_PARAVAL\"",
"\"EMP_INTO_PARAVAL\"",
"\"EMP_CLCT_AMT\"",
"\"EMP_CLCT_INTO_ACCT_AMT\"",
"\"PSN_CLCT_PARAVAL\"",
"\"PSN_INTO_PARAVAL\"",
"\"PSN_CLCT_AMT\"",
"\"PSN_CLCT_INTO_ACCT_AMT\"",
"\"OTH_CLCT_TRAF_AMT\"",
"\"OTH_CLCT_AMT\"",
"\"FINSUBS_AMT\"",
"\"FINSUBS_TRAF_AMT\"",
"\"TRAF_SUMAMT\"",
"\"CLCT_SUMAMT\"",
"\"INTE\"",
"\"LATEFEE\"",
"\"CLCT_FLAG\"",
"\"CLCT_TYPE\"",
"\"CLCT_TIME\"",
"\"ARVLER\"",
"\"ARVL_BCHNO\"",
"\"REVS_FLAG\"",
"\"TRAFER\"",
"\"TRAF_TIME\"",
"\"INTSURY_TIME\"",
"\"URSN_TIME\"",
"\"DCLA_PRD\"",
"\"ELEC_TAXRPT_NO\"",
"\"PEAWKR_FLAG\"",
"\"POOLAREA_NO\"",
"\"INSUTYPE_RETR_FLAG\"",
"\"TAXDEPT_CODE\"",
"\"PLAN_BCHNO\"",
"\"PSN_INSU_RLTS_ID\"",
"\"CLCTSTD_CRTF_RULE_CODG\"",
"\"CLCT_RULE_TYPE_CODG\"",
"\"INSU_CLCT_MONS\"",
"\"INIT_PSN_NO\"",
"\"INIT_PSN_CLCT_DETL_ID\"",
"\"ACCTER\"",
"\"CRTE_OPTINS_NO\"",
"\"CRTER_ID\"",
"\"CRTER_NAME\"",
"\"CRTE_TIME\"",
"\"OPTINS_NO\"",
"\"OPTER_ID\"",
"\"OPTER_NAME\"",
"\"OPT_TIME\"",
"\"RID\"",
"\"UPDT_TIME\"",
"\"BILL_FLAG\"",
"\"TRAF_FLAG\""
],
"where": "(INSUTYPE in ('310')) and ('150000202111141844320055457938' <= RID AND RID < '150000202111141849170068916149') ",
"splitPk": "",
"password": "17648110166",
"username": "whm_sjzh",
"connection": [
{
"table": [
"STAF_PSN_CLCT_DETL_D"
],
"jdbcUrl": [
"jdbc:oracle:thin:@//10.55.141.10/sjzlhetdb"
]
}
]
}
},
"writer": {
"name": "mysqlwriter",
"parameter": {
"column": [
"`PSN_CLCT_DETL_ID`",
"`EMP_CLCT_DETL_ID`",
"`CLCT_BILL_ID`",
"`EMP_NO`",
"`PSN_NO`",
"`CASHYM`",
"`ACCRYM_BEGN`",
"`ACCRYM_END`",
"`INSUTYPE`",
"`PSN_TYPE`",
"`PSN_IDET_TYPE`",
"`INSU_IDET`",
"`EMP_CLCTSTD`",
"`PSN_CLCTSTD`",
"`WAG`",
"`QUOT_CLCT_FLAG`",
"`EMP_CLCT_PARAVAL`",
"`EMP_INTO_PARAVAL`",
"`EMP_CLCT_AMT`",
"`EMP_CLCT_INTO_ACCT_AMT`",
"`PSN_CLCT_PARAVAL`",
"`PSN_INTO_PARAVAL`",
"`PSN_CLCT_AMT`",
"`PSN_CLCT_INTO_ACCT_AMT`",
"`OTH_CLCT_TRAF_AMT`",
"`OTH_CLCT_AMT`",
"`FINSUBS_AMT`",
"`FINSUBS_TRAF_AMT`",
"`TRAF_SUMAMT`",
"`CLCT_SUMAMT`",
"`INTE`",
"`LATEFEE`",
"`CLCT_FLAG`",
"`CLCT_TYPE`",
"`CLCT_TIME`",
"`ARVLER`",
"`ARVL_BCHNO`",
"`REVS_FLAG`",
"`TRAFER`",
"`TRAF_TIME`",
"`INTSURY_TIME`",
"`URSN_TIME`",
"`DCLA_PRD`",
"`ELEC_TAXRPT_NO`",
"`PEAWKR_FLAG`",
"`POOLAREA_NO`",
"`INSUTYPE_RETR_FLAG`",
"`TAXDEPT_CODE`",
"`PLAN_BCHNO`",
"`PSN_INSU_RLTS_ID`",
"`CLCTSTD_CRTF_RULE_CODG`",
"`CLCT_RULE_TYPE_CODG`",
"`INSU_CLCT_MONS`",
"`INIT_PSN_NO`",
"`INIT_PSN_CLCT_DETL_ID`",
"`ACCTER`",
"`CRTE_OPTINS_NO`",
"`CRTER_ID`",
"`CRTER_NAME`",
"`CRTE_TIME`",
"`OPTINS_NO`",
"`OPTER_ID`",
"`OPTER_NAME`",
"`OPT_TIME`",
"`RID`",
"`UPDT_TIME`",
"`BILL_FLAG`",
"`TRAF_FLAG`"
],
"password": "4VbEw8Zo!sHJ5gn",
"username": "clctcent",
"connection": [
{
"table": [
"staf_psn_clct_detl_d"
],
"jdbcUrl": "jdbc:mysql://192.168.7.137:5066/clctcent_db?serverTimezone=GMT%2B8"
}
]
}
}
}
],
"setting": {
"speed": {
"byte": -1,
"channel": 10
},
"errorLimit": {
"record": 10000,
"percentage": 1
}
}
}
}