Flink流处理将数据写入mysql_Flink从入门到真香(11、Sink自定义数据输出-以写入MySQL为例）...

目标： Flink从txt文件中读取数据，写入到mysql中

环境准备： 如果没有mysql，可以按照下面命令安装一下

wget https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm

yum -y install mysql80-community-release-el7-3.noarch.rpm

yum install mysql-community-server -y

systemctl restart mariadb

查看mysql默认密码

[root@localhost ~]# grep 'temporary password' /var/log/mysqld.log

2020-11-04T02:05:26.432219Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: qCk4b_3iEE;V

修改mysql默认密码

mysql -uroot -p'qCk4b_3iEE;V'

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Mafei@20201104';

Query OK, 0 rows affected (0.03 sec)

允许所有主机连接mysql

mysql> update user set host = '%' where user = 'root';

systemctl restart mysqld

创建mysql的数据库及表

创建test数据库：

create database test default character set utf8mb4 collate utf8mb4_unicode_ci;

创建表

-- ----------------------------

-- Table structure for sensor_temp

-- ----------------------------

DROP TABLE IF EXISTS `sensor_temp`;

CREATE TABLE `sensor_temp` (

`ids` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

`temp` double(10,0) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

SET FOREIGN_KEY_CHECKS = 1;

新建一个scala object 类，JdbcSink

package com.mafei.sinktest

import java.sql.{Connection, DriverManager, PreparedStatement}

import org.apache.flink.configuration.Configuration

import org.apache.flink.streaming.api.functions.sink.RichSinkFunction

import org.apache.flink.streaming.api.scala.{StreamExecutionEnvironment, createTypeInformation}

object JdbcSink {

def main(args: Array[String]): Unit = {

//创建执行环境

val env = StreamExecutionEnvironment.getExecutionEnvironment

val inputStream = env.readTextFile("/opt/java2020_study/maven/flink1/src/main/resources/sensor.txt")

env.setParallelism(1)

inputStream.print()

//先转换成样例类类型

val dataStream = inputStream

.map(data => {

val arr = data.split(",") //按照,分割数据，获取结果

SensorReadingTest5(arr(0), arr(1).toLong, arr(2).toDouble) //生成一个传感器类的数据，参数中传toLong和toDouble是因为默认分割后是字符串类别

})

dataStream.addSink(new MyJdbcSinkFunc())

env.execute()

}

}

class MyJdbcSinkFunc() extends RichSinkFunction[SensorReadingTest5]{

//定义连接、预编译语句

var conn: Connection = _

var insertStmt: PreparedStatement = _

var updateStmt: PreparedStatement = _

override def open(parameters: Configuration): Unit = {

conn = DriverManager.getConnection("jdbc:mysql://10.0.83.82:3306/test","root","Mafei@20201104")

insertStmt = conn.prepareStatement("INSERT INTO `sensor_temp`(`ids`, `temp`) VALUES ( ?, ?)")

updateStmt = conn.prepareStatement("update sensor_temp set temp= ? where ids= ? ")

}

override def invoke(in: SensorReadingTest5): Unit = {

updateStmt.setDouble(1,in.temperature)

updateStmt.setString(2,in.id)

updateStmt.execute()

if (updateStmt.getUpdateCount ==0){

println("执行了插入操作。。。")

insertStmt.setString(1,in.id)

insertStmt.setDouble(2,in.temperature)

insertStmt.execute()

}

}

override def close(): Unit = {

insertStmt.close()

updateStmt.close()

conn.close()

}

}

代码结构及效果：

mysql中数据的效果图：