一、FlinkSQL接入Kafka数据 插入到MySQL

1、概述

1）版本说明

名称	版本
flink-connector-jdbc_2.12	1.13.6
flink-connector-kafka_2.12	1.13.6
mysql-connector-java	8.0.25

2）实现

1.从 Kafka 接入数据，设置水位线

2.分别使用窗口、自定义函数、业务逻辑处理对数据进行加工

3.插入数据到 MySQL

2、代码实现

import org.apache.flink.table.annotation.DataTypeHint;
import org.apache.flink.table.api.*;
import org.apache.flink.table.expressions.TimeIntervalUnit;
import org.apache.flink.table.functions.ScalarFunction;

import java.io.Serializable;
import java.time.LocalDateTime;
import java.time.temporal.ChronoUnit;

import static org.apache.flink.table.api.Expressions.*;

public class KafkaSourceToMysqlSink implements Serializable {
    public static void main(String[] args) throws Exception {
        EnvironmentSettings settings = EnvironmentSettings.newInstance()
                .inStreamingMode()
                .build();

        TableEnvironment tEnv = TableEnvironment.create(settings);

        tEnv.executeSql("CREATE TABLE transactions (\n" +
                "    account_id  BIGINT,\n" +
                "    amount      BIGINT,\n" +
                "    transaction_time TIMESTAMP(3),\n" +
                "    WATERMARK FOR transaction_time AS transaction_time - INTERVAL '5' SECOND\n" +
                ") WITH (\n" +
                "    'connector' = 'kafka',\n" +
                "    'topic'     = 'transactions',\n" +
                "    'properties.bootstrap.servers' = 'xxx.xxx.xxx.x:9092',\n" +
                "    'properties.group.id' = 'test' ,\n" +
                "    'format'    = 'csv'\n" +
                ")");

        tEnv.executeSql("CREATE TABLE spend_report (\n" +
                "    account_id BIGINT,\n" +
                "    log_ts     TIMESTAMP(3),\n" +
                "    amount     BIGINT\n," +
                "    PRIMARY KEY (account_id, log_ts) NOT ENFORCED" +
                ") WITH (\n" +
                "   'connector'  = 'jdbc',\n" +
                "   'url'        = 'jdbc:mysql://localhost:3306/flink-sql?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false',\n" +
                "   'table-name' = 'spend_report',\n" +
                "   'driver'     = 'com.mysql.jdbc.Driver',\n" +
                "   'username'   = 'root',\n" +
                "   'password'   = 'root'\n" +
                ")");

        Table transactions = tEnv.from("transactions");

        report(transactions).executeInsert("spend_report");

        reportWithUdf(transactions).executeInsert("spend_report");

        reportWithWindow(transactions).executeInsert("spend_report");
    }

    /**
     * 添加业务处理逻辑
     */
    public static Table report(Table transactions) {
        return transactions.select(
                        $("account_id"),
                        $("transaction_time").floor(TimeIntervalUnit.HOUR).as("log_ts"),
                        $("amount"))
                .groupBy($("account_id"), $("log_ts"))
                .select(
                        $("account_id"),
                        $("log_ts"),
                        $("amount").sum().as("amount"));
    }

    /**
     * 添加窗口处理逻辑
     */
    public static Table reportWithWindow(Table transactions) {
        return transactions
                .window(Tumble.over(lit(1).hour()).on($("transaction_time")).as("log_ts"))
                .groupBy($("account_id"), $("log_ts"))
                .select(
                        $("account_id"),
                        $("log_ts").start().as("log_ts"),
                        $("amount").sum().as("amount"));

    }

    /**
     * 添加自定义函数处理逻辑
     */
    public static Table reportWithUdf(Table transactions) {
        return transactions.select(
                        $("account_id"),
                        call(MyFloor.class, $("transaction_time")).as("log_ts"),
                        $("amount"))
                .groupBy($("account_id"), $("log_ts"))
                .select(
                        $("account_id"),
                        $("log_ts"),
                        $("amount").sum().as("amount"));
    }
}

/**
 * 添加用户自定义函数
 */
class MyFloor extends ScalarFunction implements Serializable {
    public @DataTypeHint("TIMESTAMP(3)") LocalDateTime eval(
            @DataTypeHint("TIMESTAMP(3)") LocalDateTime timestamp) {
        return timestamp.truncatedTo(ChronoUnit.HOURS);
    }
}