flinksql实时统计mysql数据，写入mysql中以及对应的maven依赖

原创已于 2024-07-16 10:58:35 修改 · 919 阅读

21 ·

CC 4.0 BY-SA版权

文章标签：

#flink #mysql #大数据

于 2024-07-16 10:57:00 首次发布

代码：

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

import org.apache.flink.api.java.tuple.Tuple2;
import org.apache.flink.types.Row;
import org.apache.flink.streaming.api.datastream.DataStream;
import org.apache.flink.api.common.functions.MapFunction;
public class Test {
    public static void main(String[] args) throws Exception {
        // Set up the streaming execution environment
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        StreamTableEnvironment tableEnv = StreamTableEnvironment.create(env);
        env.setParallelism(1);

        // Define the source table using MySQL CDC connector
        String sourceDDL = "CREATE TABLE test (" +
                " id INT," +
                " name VARCHAR," +
                " PRIMARY KEY (id) NOT ENFORCED" +
                ") WITH (" +
                " 'connector' = 'mysql-cdc'," +
                " 'hostname' = '127.0.0.1'," +
                " 'port' = '3306'," +
                " 'username' = 'root'," +
                " 'password' = '123456'," +
                " 'database-name' = 'test'," +
                " 'table-name' = 'test'" +
                ")";

        // Define the sink table
        String sinkDDL = "CREATE TABLE test_res (" +
                " name VARCHAR," +
                " num bigint," +
                " PRIMARY KEY (name) NOT ENFORCED" +
                ") WITH (" +
                " 'connector' = 'jdbc'," +
                " 'url' = 'jdbc:mysql://localhost:3306/test'," +
                " 'table-name' = 'test_res'," +
                " 'username' = 'root'," +
                " 'password' = '123456'," +
                " 'driver' = 'com.mysql.jdbc.Driver'" +
                ")";

        // Register the source and sink tables
        tableEnv.executeSql(sourceDDL);
        tableEnv.executeSql(sinkDDL);

        //3.查询数据并转换为流输出
        Table table = tableEnv.sqlQuery("SELECT name, COUNT(name) AS num_count FROM test GROUP BY name");
        DataStream<Tuple2<Boolean, Row>> retractStream = tableEnv.toRetractStream(table, Row.class);
        retractStream.map(new MapFunction<Tuple2<Boolean, Row>, String>() {
            @Override
            public String map(Tuple2<Boolean, Row> value) throws Exception {
                return value.f1.toString();
            }
        }).print();

        // Insert the result into the sink table
        tableEnv.executeSql("INSERT INTO test_res SELECT name, COUNT(name) AS num_count FROM test GROUP BY name");

        // Execute the Flink job
        env.execute("Flink CDC Example");
    }
}

maven依赖：

<properties>
  <flink-version>1.13.0</flink-version>
</properties>

<dependencies>
  <dependency>
    <groupId>org.apache.flink</groupId>
    <artifactId>flink-java</artifactId>
    <version>${flink-version}</version>
  </dependency>
  <dependency>
    <groupId>org.apache.flink</groupId>
    <artifactId>flink-connector-jdbc_2.11</artifactId>
    <version>${flink-version}</version>
  </dependency>

  <dependency>
    <groupId>org.apache.flink</groupId>
    <artifactId>flink-streaming-java_2.12</artifactId>
    <version>${flink-version}</version>
  </dependency>

  <dependency>
    <groupId>org.apache.flink</groupId>
    <artifactId>flink-clients_2.12</artifactId>
    <version>${flink-version}</version>
  </dependency>

  <dependency>
    <groupId>org.apache.hadoop</groupId>
    <artifactId>hadoop-client</artifactId>
    <version>3.1.3</version>
  </dependency>

  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.16</version>
  </dependency>

  <dependency>
    <groupId>org.apache.flink</groupId>
    <artifactId>flink-table-planner-blink_2.12</artifactId>
    <version>${flink-version}</version>
  </dependency>

  <dependency>
    <groupId>com.ververica</groupId>
    <artifactId>flink-connector-mysql-cdc</artifactId>
    <version>2.2.1</version>
  </dependency>

  <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.75</version>
  </dependency>
</dependencies>