Flume将MySQL表数据存入到HDFS

2019独角兽企业重金招聘Python工程师标准>>>

Flume将MySQL表数据存入到HDFS

一、创建MySQL表

-- ----------------------------
-- Table structure for t_name
-- ----------------------------
DROP TABLE IF EXISTS `t_name`;
CREATE TABLE `t_name`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sip` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `dip` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `sport` int(11) NULL DEFAULT NULL,
  `dport` int(11) NULL DEFAULT NULL,
  `protocol` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `flowvalue` int(11) NULL DEFAULT NULL,
  `createtime` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_name
-- ----------------------------
INSERT INTO `t_name` VALUES (1, '76.58.692.49', '23.28.380.27', 53, 17, 'TCP', 10, '2019-03-05 12:25:47');
INSERT INTO `t_name` VALUES (2, '36.30.754.95', '21.19.847.60', 92, 61, 'TCP', 56, '2019-03-05 12:25:47');
INSERT INTO `t_name` VALUES (3, '29.65.205.81', '61.41.360.21', 79, 44, 'TCP', 45, '2019-03-05 12:25:47');
INSERT INTO `t_name` VALUES (4, '69.65.715.32', '90.60.887.73', 82, 89, 'TCP', 25, '2019-03-05 12:25:47');
INSERT INTO `t_name` VALUES (5, '92.51.427.29', '86.42.538.10', 98, 96, 'TCP', 11, '2019-03-05 12:25:47');
INSERT INTO `t_name` VALUES (6, '10.43.459.69', '42.16.826.51', 77, 32, 'TCP', 53, '2019-03-05 12:25:47');
INSERT INTO `t_name` VALUES (7, '40.52.822.52', '37.87.208.90', 79, 77, 'TCP', 12, '2019-03-05 12:25:47');
INSERT INTO `t_name` VALUES (8, '99.49.363.76', '53.13.402.25', 81, 90, 'TCP', 30, '2019-03-05 12:25:47');
INSERT INTO `t_name` VALUES (9, '94.90.526.47', '80.29.188.65', 29, 62, 'TCP', 62, '2019-03-05 12:25:47');
INSERT INTO `t_name` VALUES (10, '37.84.816.99', '31.64.935.94', 27, 45, 'TCP', 30, '2019-03-05 12:25:47');

二、创建相关状态文件与HDFS目标目录

1. 创建状态文件

mkdir /home/lwenhao/flume
cd /home/lwenhao/flume
touch sql-source.status
chmod -R 777 /home/lwenhao/flume

2. 创建HDFS目录

hdfs fs -mkdir /flume/mysql
hdfs fs -chmod -R 777 /flume/mysql

三、导入JAR包

我安装的是MySQL5.7版本需要flume-ng-sql-source-1.x.x.jar 与mysql-connector-java-5.x.x-bin.jar

下载完成之后，把这两个jar包复制到/apache-flume-1.9.0-bin/lib/目录下

四、配置Flume

# Channel名称
agent.channels = ch1

# Sink名称
agent.sinks = HDFS

# Source名称
agent.sources = sql-source

# Agent的channel类型
agent.channels.ch1.type = memory

# Source对应的channel名称
agent.sources.sql-source.channels = ch1

# Source类型
agent.sources.sql-source.type = org.keedio.flume.source.SQLSource

# 数据库URL
agent.sources.sql-source.hibernate.connection.url = jdbc:mysql://192.168.1.69:3306/t_hadoop

# 数据库用户名
agent.sources.sql-source.hibernate.connection.user = root

# 数据库密码
agent.sources.sql-source.hibernate.connection.password = root

# 数据库表名
agent.sources.sql-source.table = t_name

# 查询的列
agent.sources.sql-source.columns.to.select = *

# 查询的列
agent.sources.sql-source.incremental.column.name = id

# 增量初始值
agent.sources.sql-source.incremental.value = 0

# 发起查询的时间间隔，单位是毫秒
agent.sources.sql-source.run.query.delay=5000

# 状态文件路径
agent.sources.sql-source.status.file.path = /home/lwenhao/flume

# 状态文件名称
agent.sources.sql-source.status.file.name = sql-source.status

# Sink对应的channel名称
agent.sinks.HDFS.channel = ch1

# Sink类型
agent.sinks.HDFS.type = hdfs

# Sink路径
agent.sinks.HDFS.hdfs.path = hdfs://192.168.1.67:9001/flume/mysql

# 流数据的文件类型
agent.sinks.HDFS.hdfs.fileType = DataStream

# 数据写入格式
agent.sinks.HDFS.hdfs.writeFormat = Text

# 目标文件轮转大小，单位是字节
agent.sinks.HDFS.hdfs.rollSize = 268435456

# hdfs sink间隔多长将临时文件滚动成最终目标文件，单位是秒；如果设置成0，则表示不根据时间来滚动文件
agent.sinks.HDFS.hdfs.rollInterval = 0

# 当events数据达到该数量时候，将临时文件滚动成目标文件；如果设置成0，则表示不根据events数据来滚动文件
agent.sinks.HDFS.hdfs.rollCount = 0

五、 启动flume

 bin/flume-ng agent --conf conf/ --name agent --conf-file conf/flume-conf.conf

六、 效果

转载于:https://my.oschina.net/lwenhao/blog/3018117