FlinkSQL_1.12_用DDL实现Kafka到MySQL的数据传输_实现按照条件进行过滤写入MySQL

1.FlinkSQL_用DDL实现Kafka到MySQL的数据传输_实现按照条件进行过滤写入MySQL

package com.atguigu.day10;

import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.table.api.Table;
import org.apache.flink.table.api.bridge.java.StreamTableEnvironment;

/**
 * @author dxy
 * @date 2021/4/21 20:58
 */

//TODO 用DDL实现Kafka到MySQL的数据传输
public class FlinkSQL15_SQL_DDL_Kafka_MySQL {
    public static void main(String[] args) throws Exception {
        //1.获取执行环境
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        env.setParallelism(1);
        StreamTableEnvironment tableEnv = StreamTableEnvironment.create(env);

        //2.使用DDL的方式加载数据--注册SourceTable
        tableEnv.executeSql("create table source_sensor(id string,ts bigint,vc double)" +
                "with (" +
                "'connector.type' = 'kafka'," +
                "'connector.version' = 'universal'," +
                "'connector.topic' = 'test'," +
                "'connector.properties.bootstrap.servers' = 'hadoop102:9092'," +
                "'connector.properties.group.id' = 'bigdata1109'," +
                "'format.type' = 'csv'"
                + ")");

        Table table = tableEnv.sqlQuery("select * from source_sensor where id = 'ws_001'");

        //3.注册SinkTable:Mysql
        tableEnv.executeSql("create table sink_sensor(id string,ts bigint,vc double)" +
                "with (" +
                "'connector' = 'jdbc'," +
                "'url' = 'jdbc:mysql://hadoop102:3306/test',"+
                "'table-name' = 'sink_table',"+
                "'username' = 'root',"+
                "'password' = '123456'"
                + ")");

/*        //4.执行查询kafka数据
        Table source_sensor = tableEnv.from("source_sensor");

        //5.将数据写入Mysql
        source_sensor.executeInsert("sink_sensor");*/

        table.executeInsert("sink_sensor");

        //6.执行任务
        env.execute();
    }
}

2.测试

[atguigu@hadoop102 ~]$ kafka-console-producer.sh --broker-list hadoop102:9092 --topic test
>ws_001,1577844001,45
>ws_002,1577844001,45
>ws_001,1577844001,45

IDEA控制台

报错但是不会影响程序运行。

查看MySQL中数据

在运行之前需要先把表创建，不然没法测试，不会自动帮我们在mysql中创建表。

-- flinksql测试把kafka数据写入mysql
create table sink_table
(id VARCHAR(255),
ts BIGINT,
vc DOUBLE
);

结合数据，我们可以再继续实现mysql一个特性，有则更新，无则插入原则。