本次是一次实验性操作,Flink版本是1.13.3,mysql-cdc版本是2.0.2,mysql版本是8.0.25。
第一步:使用cdc来读取库中的两张表,这里有一个细节是序列化器我使用的是自定义的序列化器,需要知道如何自定义序列化器可以查看cdc官网:MySQL CDC Connector — CDC Connectors for Apache Flink® documentation
//注册流环境 StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment(); env.setParallelism(1); //注册表环境 StreamTableEnvironment tEnv = StreamTableEnvironment.create(env); //连接数据源 DebeziumSourceFunctionmysql = MySqlSource. builder() .hostname("127.0.0.1") .port(3306) .username("root") .password("root") .databaseList(ConnConfig.DATABASE) .tableList(ConnConfig.TABLE_LIST) .startupOptions(StartupOptions.initial()) .deserializer(new MyCustomerDeserialization()) .build(); //转为Source DataStreamSource mysqlDS = env.addSource(mysql);
第二步:根据mysql中表来定义Pojo类,这里必须要为类定义空参构造器,不然后续会出错。
第三步:定义侧输出流并分流
OutputTagdepartmentTag = new OutputTag ("department") { }; OutputTag employeeTag = new OutputTag ("employee") { }; //分流 SingleOutputStreamOperator
第四步:注册虚拟表环境并编写SQL
//POJO中需要定义空参构造器
Table empTable = tEnv.fromDataStream(employeeStream, $("id"), $("name"), $("age"), $("dept_id"), $("create_time").rowtime());
Table depTable = tEnv.fromDataStream(departmentStream, $("id"), $("name"), $("create_time").rowtime());
tEnv.createTemporaryView("employee", empTable);
tEnv.createTemporaryView("department", depTable);
Table table = tEnv.sqlQuery("select b.name,count(distinct a.name) as num " +
"from employee a left join department b on a.dept_id=b.id " +
"group by b.name");
//Schema schema = Schema.newBuilder().column("name", DataTypes.STRING()).column("num", DataTypes.BIGINT()).build();
DataStream rowDataStream = tEnv.toChangelogStream(table);
第五步:由于是实时流入数据,所以在聚合层面上肯定是有会数据撤回和重流入的操作的,这里我们过滤掉了撤回流数据和空值数据,保证每次都是新结果数据流入。
SingleOutputStreamOperatorresultStream = rowDataStream.filter(x -> { RowKind kind = x.getKind(); //过滤掉null和撤回流操作 return (RowKind.INSERT == kind || RowKind.UPDATE_AFTER == kind) && x.getField("name") != null && x.getField("num") != null; }); //mysql幂等性写入 resultStream.addSink(JdbcSink.sink( "INSERT INTO result " + "VALUES" + "(?,?) ON DUPLICATE KEY UPDATE name=?" + ",num=?", (ps, t) -> { ps.setString(1, Objects.requireNonNull(t.getField(0)).toString()); ps.setDouble(2, Double.parseDouble(Objects.requireNonNull(t.getField(1)).toString())); ps.setString(3, Objects.requireNonNull(t.getField(0)).toString()); ps.setDouble(4, Double.parseDouble(Objects.requireNonNull(t.getField(1)).toString())); }, new JdbcExecutionOptions.Builder() .withBatchSize(1) .build(), new JdbcConnectionOptions.JdbcConnectionOptionsBuilder() .withUrl("jdbc:mysql://127.0.0.1:3306/spider_base?useSSL=false") .withUsername("root") .withPassword("root") .withDriverName(Driver.class.getName()) .build() )); env.execute();



