背景:一张3000多万的表数据想同步到一张新表中,先要进行初始化然后增量同步,但是根据官网修改攒批参数后不起作用,数据写入的速度没有明显的加快
参考帖子【实战02】flinksql mysql真正的批量插入_flink 批量插入mysql-CSDN博客
结果代码
需要jdbc协议中的参数中加入rewriteBatchedStatements。 于是最后我的flinksql改成了下面的样子,问题得到解决。
//cdc读取gzzx_8717_jc_yljcxxb 表数据
String sourcesql = "CREATE TABLE `gzzx_8717_jc_yljcxxb` (\n" +
" `czdm` string ,\n" +
" `czmc` string ,\n" +
" `sj` timestamp,\n" +
" `jyl` decimal(26,2) ,\n" +
" `yllx` string,\n" +
" `ssdw` string,\n" +
" `swm` string,\n" +
" `jxsj` timestamp ,\n" +
" `rksj` timestamp ,\n" +
" `bwfssj` timestamp ,\n" +
" `gzjssj` timestamp ,\n" +
" PRIMARY KEY (`czdm`,`sj`,`yllx`) NOT ENFORCED\n" +
") WITH (\n" +
" 'connector' = 'mysql-cdc',\n" +
" 'hostname' = 'xxx',\n" +
" 'port' = '15020',\n" +
" 'username'='sjzx',\n" +
" 'password'='xxx',\n" +
" 'database-name' = 'ads_yw',\n" +
" 'table-name' = 'gzzx_8717_jc_yljcxxb'\n" +
")\n";
tableEnv.executeSql(sourcesql);
//创建写入表连接
tableEnv.executeSql("CREATE TABLE `sw_js_jc_yljcxxb_1` (\n" +
" `czdm` string,\n" +
" `czmc` string,\n" +
" `sj` timestamp ,\n" +
" `dqjyl` decimal(22,2) ,\n" +
" `yllx` string,\n" +
" `jslljz` decimal(22,2) ,\n" +
" `wfzsdyl` decimal(22,2) ,\n" +
" `lydw` string ,\n" +
" `js_src` string,\n" +
" `js_dt` timestamp ,\n" +
" PRIMARY KEY (`czdm`,`sj`,`yllx`) NOT ENFORCED \n" +
")WITH (\n" +
" 'connector' = 'jdbc',\n" +
" 'url' = 'jdbc:mysql://xxx:15020/tmp?rewriteBatchedStatements=true',\n" +
" 'username'='sjzx',\n" +
" 'password'='xxx',\n" +
" 'driver'='com.mysql.cj.jdbc.Driver',\n" +
" 'table-name' = 'sw_js_jc_yljcxxb_1',\n" +
" 'sink.buffer-flush.max-rows' = '20000',\n" +
" 'sink.buffer-flush.interval' = '3000',\n" +
" 'sink.parallelism' = '4',\n" +
")");
tableEnv.executeSql("insert into sw_js_jc_yljcxxb_1(czdm,czmc,sj,dqjyl,yllx,lydw,js_src)\n" +
"select czdm,czmc,sj,jyl,yllx,ssdw,'gzzx_8717_jc_yljcxxb' from gzzx_8717_jc_yljcxxb ");