Flink之flie和mysql之间的相互读取和写入

最新推荐文章于 2024-01-23 13:14:38 发布

原创最新推荐文章于 2024-01-23 13:14:38 发布 · 252 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#flink #jdbc #mysql

本文介绍如何使用 Apache Flink 实现文件到 MySQL 的数据流转，包括从文件读取数据、转换处理并最终写入 MySQL 的全过程。通过示例代码详细展示了 Flink 在不同场景下的应用，如文件到文件、MySQL 到文件以及文件到 MySQL 的数据处理流程。

file_2_file

package com.cmbyc.sink;

import org.apache.flink.api.common.RuntimeExecutionMode;
import org.apache.flink.core.fs.FileSystem;
import org.apache.flink.streaming.api.datastream.DataStream;
import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;

/**
 * @program: 
 * @author: 
 * @create: 2021-05-17 16:12
 * @decsription:
 **/

public class file_2_file {
    public static void main(String[] args) throws Exception {
        //1 env 准备环境
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        env.setRuntimeMode(RuntimeExecutionMode.AUTOMATIC);
        //2 source 加载数据
        DataStream<String> text = env.readTextFile("XXX\words.txt");

        //3 transformation 数据转换处理

        //4 sink 输出数据
        text.print();
        text.writeAsText("xxx/test2", FileSystem.WriteMode.OVERWRITE).setParallelism(2);
        //5 execute 执行
        env.execute();
    }
}

这部分没啥技术含量基本就是读取和写入

FileSystem.WriteMode.OVERWRITE

这部分是覆盖写入

MySQL_2_file

package com.cmbyc.Source;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.flink.configuration.Configuration;
import org.apache.flink.core.fs.FileSystem;
import org.apache.flink.streaming.api.datastream.DataStreamSource;
import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.streaming.api.functions.source.RichParallelSourceFunction;
import org.apache.flink.table.api.bridge.java.StreamTableEnvironment;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.concurrent.TimeUnit;

/**
 * @program: 
 * @author: 
 * @create: 2021-05-14 14:27
 * @decsription:
 **/

public class JDBC {
    public static void main(String[] args) throws Exception {
        //1 env 准备环境
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        StreamTableEnvironment tenv = StreamTableEnvironment.create(env);

        //2 source 加载数据
        DataStreamSource<Student> studentDS = env.addSource(new MysqlSource()).setParallelism(1);

        //3 transformation 数据处理
        //4 sink 输出数据
        studentDS.print();
        studentDS.writeAsText("路径\\test2", FileSystem.WriteMode.OVERWRITE).setParallelism(2);
        //5 execute 执行
        env.execute();
    }

    //样例类
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class Student {
        private Integer id;
        private String name;
        private Integer age;
    }

    // 连接mysql
    public static class MysqlSource extends RichParallelSourceFunction<Student> {
        private Connection conn = null;
        private PreparedStatement ps = null;

        @Override
        public void open(Configuration parameters) throws Exception {
            //加载驱动 连接mysql
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/cd_mysql", "账号", "密码");
            String sql = "select * from student";
            ps = conn.prepareStatement(sql);
        }

        private boolean flag = true;

        @Override
        public void run(SourceContext<Student> ctx) throws Exception {
            while (flag) {
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {
                    int id = rs.getInt("id");
                    String name = rs.getString("name");
                    int age = rs.getInt("age");
                    ctx.collect(new Student(id, name, age));
                }
                TimeUnit.SECONDS.sleep(1);
                flag=false;
            }
        }


        @Override
        public void cancel() {
            flag = false;
        }

        @Override
        public void close() throws Exception {
            if (conn != null) conn.close();
            if (ps != null) ps.close();
        }
    }

}

Mysql_2_mysql

package com.cmbyc.JDBC;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.flink.configuration.Configuration;
import org.apache.flink.streaming.api.datastream.DataStream;
import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.streaming.api.functions.sink.RichSinkFunction;
import org.apache.flink.streaming.api.functions.source.RichParallelSourceFunction;

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

/**
 * @program: 
 * @author: 
 * @create: 2021-05-17 15:07
 * @decsription:
 **/

public class MySql_2_MySql {
    public static void main(String[] args) throws Exception {
        //1 env 准备环境
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();

        //2 source 加载数据
        DataStream<Student> DS = env.addSource(new MySQLSource()).setParallelism(1);

        //3 transformation 数据处理转换
        //4 sink 输出数据
        DS.addSink(new MySQLSink());
        //5 execute 执行
        env.execute();
    }
    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    public static class Student {
        private Integer id;
        private String name;
        private Integer age;
    }



    public static class MySQLSink extends RichSinkFunction<Student>{
        private Connection conn=null;
        private PreparedStatement ps=null;

        @Override
        public void open(Configuration parameters) throws Exception {
            conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/cd_mysql","账号","密码");
            String sql="INSERT INTO `student1`(`id`,`name`,`age`)VALUES (null ,?,?)";
            ps=conn.prepareStatement(sql);
        }

        @Override
        public void invoke(Student value, Context context) throws Exception {
            //获取 具体值
           // ps.setInt(0,value.getId());
            ps.setString(1,value.getName());
            ps.setInt(2,value.getAge());
            //执行sql
            ps.executeUpdate();
        }
        @Override
        public void close() throws Exception {
            if (conn != null) conn.close();
            if (ps != null) ps.close();
        }

    }



    public static class MySQLSource extends RichParallelSourceFunction<Student>{
        private Connection conn=null;
        private PreparedStatement ps=null;

        @Override
        public void open(Configuration parameters) throws Exception {
            conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/cd_mysql","账号","密码");
            String sql="select id,name,age from student";
            ps=conn.prepareStatement(sql);
        }

        private boolean flag=true;
        @Override
        public void run(SourceContext<Student> ctx) throws Exception {
            while (flag){
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {
                    int id = rs.getInt("id");
                    String name = rs.getString("name");
                    int age = rs.getInt("age");
                    ctx.collect(new Student(id, name, age));
                }
                flag=false;
            }

        }

        @Override
        public void cancel() {

        }

        @Override
        public void close() throws Exception {
            if(conn!=null)conn.close();
            if (ps!=null)ps.close();
        }
    }
}

其中 source 的继承的是

RichParallelSourceFunction<Student>

sink继承的是

RichSinkFunction<Student>

一定要注意区分

File_2_mysql

package com.cmbyc.sink;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.flink.api.common.functions.MapFunction;
import org.apache.flink.configuration.Configuration;
import org.apache.flink.streaming.api.datastream.DataStreamSource;
import org.apache.flink.streaming.api.datastream.SingleOutputStreamOperator;
import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.streaming.api.functions.sink.RichSinkFunction;

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

/**
 * @program: 
 * @author: 
 * @create: 2021-05-17 16:36
 * @decsription:
 **/

public class file_2_mysql {
    public static void main(String[] args) throws Exception {
        //1 env 准备环境
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();

        //2 source 加载数据
        DataStreamSource<String> txtDS = env.readTextFile("文件路径\\test2");

        //3 transformation
        SingleOutputStreamOperator<Student> studentDS = txtDS.map(new MapFunction<String, Student>() {
            @Override
            public Student map(String value) throws Exception {
                String[] splits = value.split(", ");
                Student student = new Student();
                //student.setId(Integer.parseInt(splits[0]));
                student.setName(splits[1]);
                student.setAge(Integer.parseInt(splits[2]));
                return student;
            }
        });
        //4 sink
       // txtDS.print();
        studentDS.addSink(new MySQLSink());
        //5 execute
        env.execute();
    }

    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class Student {
        private int id;
        private String name;
        private int age;
    }

    public static class MySQLSink extends RichSinkFunction<Student> {
        static Connection conn = null;
        PreparedStatement ps = null;

        //获取数据库信息
        private static Connection getConn() {
            try {
                //将mysql驱动注册到DriverManager中去
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/cd_mysql", "账号", "密码");

            } catch (ClassNotFoundException | SQLException e) {
                e.printStackTrace();
            }
            return conn;
        }

        @Override
        public void open(Configuration parameters) throws Exception {
            //super.open(parameters);
            getConn();
            String sql = "insert into student3(id,name,age) value(null,?,?)";
            ps = conn.prepareStatement(sql);
        }


        @Override
        public void invoke(Student value, Context context) throws Exception {
            //ps.setInt(0, value.getId());
            ps.setString(1,value.getName());
            ps.setInt(2, value.getAge());

            ps.executeUpdate();
        }

        @Override
        public void close() throws Exception {
            if (conn != null) conn.close();
            if (ps != null) ps.close();
        }
    }


}