Flink 程序Sink（数据输出）操作（3）自定义Mysql-Sink

Flink 程序Sink（数据输出）操作（3）自定义Mysql-Sink

自定义sink需要继承RichSinkFunction

ex:

public static class MysqlSink extends RichSinkFunction<IN> {}

必要依赖

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

自定义Mysql-sink

public static class MysqlSink extends RichSinkFunction<VehicleAlarm> {
        Connection conn = null;
        PreparedStatement ps = null;
        String url = "jdbc:mysql://xx:3306/alarm-sc?useUnicode=true&characterEncoding=utf-8&useSSL=false";
        String username = "root";
        String password = "xx";

        /**
         * 首次sink时执行，且创建到销毁只会执行一次
         * @param parameters
         * @throws Exception
         */
        @Override
        public void open(Configuration parameters) throws Exception {
            // 获取mysql 连接
            conn = DriverManager.getConnection(url, username, password);
            // 关闭自定提交
            conn.setAutoCommit(false);
        }

        /**
         * 数据源无数据，sink关闭时执行，且创建到销毁只会执行一次
         * @throws Exception
         */
        @Override
        public void close() throws Exception {
            if (conn != null) {
                conn.close();
            }
            if (ps != null) {
                ps.close();
            }
        }

        /**
         * 数据输出时执行，每一个数据输出时，都会执行此方法
         * @param value
         * @param context
         * @throws Exception
         */
        @Override
        public void invoke(VehicleAlarm value, Context context) throws Exception {
            String sql = "insert into vehicle_alarm_202104 (`id`,`license_plate`,`plate_color`,`device_time`,`zone`) " +
                    "values(?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, value.id);
            ps.setString(2, value.licensePlate);
            ps.setString(3, value.plateColor);
            ps.setLong(4, value.deviceTime);
            ps.setString(5, value.zone);
            // 执行语句
            ps.execute();
            // 提交
            conn.commit();
            log.warn("sink-success:{}", value);
        }
    }

Flink程序步骤

public static void main(String[] args) throws Exception {
    //准备环境
    StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
    env.setRuntimeMode(RuntimeExecutionMode.AUTOMATIC);
    // 加载数据源
    DataStreamSource<VehicleAlarm> streamSource = env.addSource(new MySource());
    //todo  transformation(数据处理)

    // 数据输出 
    streamSource.addSink(new MysqlSink());
    // 程序执行
    env.execute("learn-mysql-sink");
}

附上完整DEMO

package com.leilei;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.flink.api.common.RuntimeExecutionMode;
import org.apache.flink.configuration.Configuration;
import org.apache.flink.streaming.api.datastream.DataStreamSource;
import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.streaming.api.functions.sink.RichSinkFunction;
import org.apache.flink.streaming.api.functions.sink.SinkFunction;
import org.apache.flink.streaming.api.functions.source.SourceFunction;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

/**
 * @author lei
 * @version 1.0
 * @date 2021/3/11 21:09
 * @desc flink 1.12中 sink操作 输出到Mysql数据库
 */
@Slf4j
public class FlinkSink3_Mysql {
    public static void main(String[] args) throws Exception {
        //准备环境
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        env.setRuntimeMode(RuntimeExecutionMode.AUTOMATIC);
        // 加载数据源
        DataStreamSource<VehicleAlarm> streamSource = env.addSource(new MySource());
        //todo  transformation(数据处理)

        // 数据输出
        streamSource.addSink(new MysqlSink());
        // 程序执行
        env.execute("learn-mysql-sink");
    }

    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    public static class VehicleAlarm {
        private String id;
        private String licensePlate;
        private String plateColor;
        private Long deviceTime;
        private String zone;
    }

    /**
     * 自定义数据源
     */
    public static class MySource implements SourceFunction<VehicleAlarm> {
        @Override
        public void run(SourceContext<VehicleAlarm> ctx) throws Exception {
            while (true) {
                long id = System.currentTimeMillis();
                VehicleAlarm vehicleAlarm = new VehicleAlarm(String.valueOf(id), "川A" + id,
                        "绿", System.currentTimeMillis(), "sc");
                ctx.collect(vehicleAlarm);
                Thread.sleep(10000);
            }
        }

        @Override
        public void cancel() {
        }
    }

    /**
     * 自定义mysql sink
     */
    public static class MysqlSink extends RichSinkFunction<VehicleAlarm> {
        Connection conn = null;
        PreparedStatement ps = null;
        String url = "jdbc:mysql://xxx:3306/alarm-sc?useUnicode=true&characterEncoding=utf-8&useSSL=false";
        String username = "root";
        String password = "xxx";

        /**
         * 首次sink时执行，且创建到销毁只会执行一次
         * @param parameters
         * @throws Exception
         */
        @Override
        public void open(Configuration parameters) throws Exception {
            conn = DriverManager.getConnection(url, username, password);
            conn.setAutoCommit(false);
        }

        /**
         * 数据源无数据，sink关闭时执行，且创建到销毁只会执行一次
         * @throws Exception
         */
        @Override
        public void close() throws Exception {
            if (conn != null) {
                conn.close();
            }
            if (ps != null) {
                ps.close();
            }
        }

        /**
         * 数据输出时执行，每一个数据输出时，都会执行此方法
         * @param value
         * @param context
         * @throws Exception
         */
        @Override
        public void invoke(VehicleAlarm value, Context context) throws Exception {
            String sql = "insert into vehicle_alarm_202104 (`id`,`license_plate`,`plate_color`,`device_time`,`zone`) " +
                    "values(?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, value.id);
            ps.setString(2, value.licensePlate);
            ps.setString(3, value.plateColor);
            ps.setLong(4, value.deviceTime);
            ps.setString(5, value.zone);
            ps.execute();
            conn.commit();
            log.warn("sink-success:{}", value);
        }
    }


}