flinksql 读取cdc如何攒批插入目标表

最新推荐文章于 2024-05-23 16:50:41 发布

渊渟岳峙1

最新推荐文章于 2024-05-23 16:50:41 发布

阅读量423

点赞数 4

分类专栏： flink 文章标签： flink

本文链接：https://blog.csdn.net/weixin_44049857/article/details/135102710

版权

flink 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

背景：一张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 ");