Flink 读写mysql(Java)——批处理

@羲凡——只为了更好的活着

Flink 读写mysql(Java)——批处理

1.前期准备

a.添加maven依赖

<dependency>
	<groupId>org.apache.flink</groupId>
	<artifactId>flink-jdbc_2.12</artifactId>
	<version>${flink.version}</version>
</dependency>
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.48</version>
</dependency>

b.创建mysql表

DROP TABLE aaron.staff
CREATE TABLE IF NOT EXISTS aaron.staff(
NAME VARCHAR(20),
age BIGINT,
PRIMARY KEY (`name`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

2.上代码

import org.apache.flink.api.common.functions.MapFunction;
import org.apache.flink.api.common.typeinfo.BasicTypeInfo;
import org.apache.flink.api.java.DataSet;
import org.apache.flink.api.java.ExecutionEnvironment;
import org.apache.flink.api.java.io.jdbc.JDBCInputFormat;
import org.apache.flink.api.java.io.jdbc.JDBCOutputFormat;
import org.apache.flink.api.java.operators.DataSource;
import org.apache.flink.api.java.tuple.Tuple2;
import org.apache.flink.api.java.typeutils.RowTypeInfo;
import org.apache.flink.types.Row;

public class FlinkReadWriteMysql {
    public static void main(String[] args) throws Exception {
        String driverClass = "com.mysql.jdbc.Driver";
        String dbUrl = "jdbc:mysql://192.168.242.101:3306/aaron";
        String userNmae = "root";
        String passWord = "123456";
        String insertSql = "insert into aaron.staff values('张辽',227)";

        ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
        String filePath = "file:///D:/aaron/project/boyong01/data/staff.txt";
        DataSet<Row> write = env.readCsvFile(filePath).fieldDelimiter("  ").types(String.class, Integer.class)
                .map(new MapFunction<Tuple2<String, Integer>, Row>() {
                    @Override
                    public Row map(Tuple2<String, Integer> stringIntegerTuple2) throws Exception {
                        Row row = new Row(2);
                        row.setField(0, stringIntegerTuple2.f0.getBytes("UTF-8"));
                        row.setField(1, stringIntegerTuple2.f1);
                        return row;
                    }
                });
        write.output(
                JDBCOutputFormat.buildJDBCOutputFormat()
                        .setDrivername(driverClass)
                        .setDBUrl(dbUrl)
                        .setUsername(userNmae)
                        .setPassword(passWord)
                        .setQuery("insert into staff (name, age) values (?,?)")
                        .finish()
        );
        env.execute();
        System.out.println("数据写入mysql成功");
        Thread.sleep(5000);
        DataSource<Row> read = env.createInput(JDBCInputFormat.buildJDBCInputFormat()
                .setDrivername(driverClass)
                .setDBUrl(dbUrl)
                .setUsername(userNmae)
                .setPassword(passWord)
                .setQuery("select name,age from staff")
                .setRowTypeInfo(new RowTypeInfo(BasicTypeInfo.STRING_TYPE_INFO, BasicTypeInfo.INT_TYPE_INFO))
                .finish()
        );
        System.out.println("读取mysql的数据如下");
        read.map(new MapFunction<Row, Tuple2<String, Integer>>() {
            @Override
            public Tuple2<String, Integer> map(Row row) throws Exception {
                Integer integer = Integer.valueOf(row.getField(1).toString());
                return new Tuple2<>(row.getField(0).toString(), Integer.valueOf(row.getField(1).toString()));
            }
        }).print();
        System.out.println("===============Succeed==============");
    }
}

3.注意细节

a.数据写入mysql的DataSet泛型要求是row，需要转换
b.数据读取的结果也是row类型，不能直接print，需要转换
c.数据写入后一定要加上env.execute()，否则在下面的查询中查不到数据
d.涉及到中文的，需要转换成UTF-8

====================================================================

@羲凡——只为了更好的活着

若对博客中有任何问题，欢迎留言交流