Flink1.8 批作业实战：读取文本数据， 写入 mysql

1、项目介绍

从 hdfs 读取文本数据，使用 flink table api/api 对数据进行统计处理，然后将结果写入 mysql。

作业的运行周期为T-1，即当日统计昨日数据。

2、pom.xml

<!-- flink table -->
<dependency>
	<groupId>org.apache.flink</groupId>
	<artifactId>flink-table-planner_${scala.binary.version}</artifactId>
	<version>${flink.version}</version>
	<scope>provided</scope>
</dependency>
<dependency>
	<groupId>org.apache.flink</groupId>
	<artifactId>flink-table-api-scala-bridge_${scala.binary.version}</artifactId>
	<version>${flink.version}</version>
	<scope>provided</scope>
</dependency>
<dependency>
	<groupId>org.apache.flink</groupId>
	<artifactId>flink-table-common</artifactId>
	<version>${flink.version}</version>
	<scope>provided</scope>
	<exclusions>
		<exclusion>
			<artifactId>commons-compress</artifactId>
			<groupId>org.apache.commons</groupId>
		</exclusion>
		<exclusion>
			<artifactId>commons-lang3</artifactId>
			<groupId>org.apache.commons</groupId>
		</exclusion>
	</exclusions>
</dependency>
<!-- flink jdbc -->
<dependency>
	<groupId>org.apache.flink</groupId>
	<artifactId>flink-jdbc_2.12</artifactId>
	<version>${flink.version}</version>
	<scope>provided</scope>
</dependency>

3、实现方法

// 读取多个文本数据
DataSet<String> text = env.readTextFile(list.get(0));
for(int i = 1; i < list.size(); i++) {
	text = text.union(env.readTextFile(list.get(i)));
}

// worldId,sourceType,iggid,1
DataSet<Tuple4<Long, String, Long, Integer>> dataStream = text.map(
		// 数据处理
		return new Tuple4<Long, String, Long, Integer>(worldId, sourceType, iggId, 1);
	}
});

// register to table
tEnv.registerDataSet("chatInfo", dataStream, "worldId, sourceType, iggId, cnt");
// 统计各个游戏的聊天用户数和消息记录数
String statRecordsSql = "select substring(cast(worldId as VARCHAR(10)) from 1 for 4) as projectId, worldId, count(distinct iggId) as users, count(*) as messages " +
		" from chatInfo where worldId =" + worldId +
		" group by worldId";
Table recordsTable = tEnv.sqlQuery(statRecordsSql);
DataSet<Row> recordsSet = tEnv.toDataSet(recordsTable, Row.class);

// 结果写入 mysql
recordsSet.output(JDBCOutputFormat
	.buildJDBCOutputFormat()
	.setDrivername(configMap.get("mysql.ad.stat.driver"))
	.setDBUrl(configMap.get("mysql.ad.stat.url"))
	.setUsername(configMap.get("mysql.ad.stat.username"))
	.setPassword(configMap.get("mysql.ad.stat.password"))
	.setQuery("insert into test(a,b,c,d) values (?,?,?,?)")
	.finish());

env.execute();

4、flink 运行环境准备

由于 flink-table 在 flink1.8 中是可选的，需将 flink-table、flink-sql-client 从 opt/ 拷贝到 lib/ 目录下，否则提交到 yarn 会报错，找不到某些类。

5、提交到 yarn 运行

flink run -m yarn-cluster -p 4 -yjm 1024 -ytm 4096 -ynm [application name] -d -c [main method path] [jar path] [args]

6、由 crontab 定时触发批作业

#!/bin/bash

# jdk 路径

export JAVA_HOME=/home/jdk

# yarn 配置文件路径

export YARN_CONF_DIR=/home/hadoop/etc/hadoop

# hadoop 配置文件路径

export HADOOP_CONF_DIR=/home/hadoop/etc/hadoop

# 昨天日期

yesterday=${1:-$(date -d "-1 days" +%Y%m%d)}
echo ${yesterday}

/usr/local/flink-1.8.1/bin/flink run -m yarn-cluster -p 4 -yjm 1024 -ytm 4096 -ynm BatchStat -d -c com.test.batchStat /home/flink/test-1.0.0-SNAPSHOT.jar --userId 123456 --statDate ${yesterday}