工作中需要用到将hive的数据导一份到mysql中,需求是这样的:hive每天会产生一份用户标签(tag)发生变化的结果表user_tag,这份结果同步到mysql中,并且保持一份全量表,存储当前用户的标签。最初打算是在mysql建立一个分区表,按照天存储user_tag,研究了一下mysql的分区表,最终没有使用,既然学习了就做一下笔记。
mysql按照时间分区
mysql的分区方法分为好几种range,list,hash,key等方法,具体可以自行搜索一下,这里要讲的是range方法,下面是建表语句
CREATE TABLE `histdata` (
`uid` char(36) NOT NULL,
`date` date NOT NULL,
`cate` int(11) DEFAULT NULL
)
PARTITION BY RANGE COLUMNS(`date`)
(PARTITION histdata20180101 VALUES LESS THAN ('2018-01-02'),
PARTITION histdata20180102 VALUES LESS THAN ('2018-01-03'))
mysql分区表建表的时候需要指定好分区,与hive的分区有很大的不同,并且分区的名字也需要注意,因为这里面使用的是LESS THAN,那么如果每天都需要添加新的分区呢?那就需要修改表了
alter table histdata add PARTITION (PARTITION histdata20180103 VALUES LESS THAN ('2018-01-04'));
手动添加分区太麻烦了,那么能不能自动添加分区呢? 当然可以,使用存储过程。上学那会经常写java web,一些业务就直接写成了触发器、能够省下不少的代码,下面是存储过程代码:
DELIMITER $$
USE `test_db`$$ -- database name
DROP PROCEDURE IF EXISTS `histdata_add_partition`$$
CREATE DEFINER=`root`@`%` PROCEDURE `histdata_add_partition`()
BEGIN
SELECT REPLACE(partition_name,'histdata','') INTO @partition_name FROM INFORMATION_SCHEMA.PARTITIONS
WHERE table_name='histdata' ORDER BY partition_ordinal_position DESC LIMIT 1;
SET @head_date= DATE(DATE_ADD(@partition_name, INTERVAL 1 DAY))+0;
SET @rear_date= DATE(DATE_ADD(@partition_name, INTERVAL 2 DAY));
SET @s1=CONCAT('ALTER TABLE histdata ADD PARTITION (PARTITION histdata',@head_date,' VALUES LESS THAN (''',DATE(@rear_date),'''))');
SELECT @s1;
PREPARE stmt2 FROM @s1;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
COMMIT ;
END$$
DELIMITER ;
上面的作用是从当前分区中找到最大的一个,然后进行时间累加,再创建新的分区,再定义一个事件触发器
DELIMITER $$
CREATE EVENT histdata_event
ON SCHEDULE
EVERY 1 day STARTS '201x-0x-2x 23:59:59'
DO
BEGIN
CALL `test_db`.`histdata_add_partition`;
END $$
DELIMITER ;
show create table看一下效果:
CREATE TABLE `histdata` (
`uid` char(36) NOT NULL,
`date` date NOT NULL,
`cate` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS(`date`)
(PARTITION histdata20180101 VALUES LESS THAN ('2018-01-02') ENGINE = InnoDB,
PARTITION histdata20180102 VALUES LESS THAN ('2018-01-03') ENGINE = InnoDB,
PARTITION histdata20180103 VALUES LESS THAN ('2018-01-04') ENGINE = InnoDB,
PARTITION histdata20180104 VALUES LESS THAN ('2018-01-05') ENGINE = InnoDB,
PARTITION histdata20180105 VALUES LESS THAN ('2018-01-06') ENGINE = InnoDB,
PARTITION histdata20180106 VALUES LESS THAN ('2018-01-07') ENGINE = InnoDB)
可以看到效果很不错,添加了分区。不过由于分区表需要修改表添加分区,涉及到权限问题,最终并没有使用。
使用sqoop同步
下面是所修要用到表的信息,需要先将hive中的数据同步到user_tag中,然后再讲user_tag的内容insert or update 到user_tag_all中,注意到user_tag_all这个表是有主键的
全量表:user_tag_all
用于存储最新的用户Tag,mid为用户id,tag为用户标签,dt为更新日期
CREATE TABLE `user_tag_all` (
`mid` int(15) NOT NULL,
`tag` tinyint(4) NOT NULL,
`dt` date NOT NULL,
PRIMARY KEY (`mid`)
) ;
每日同步表:user_tag
每日从hive中同步到mysql中,为用户的历史标签信息
CREATE TABLE `user_tag` (
`mid` int(15) NOT NULL,
`tag` tinyint(4) NOT NULL,
`dt` date NOT NULL
);
容错表:user_tag_staging
用于sqoop容错
CREATE TABLE `user_tag_staging` (
`mid` int(15) NOT NULL,
`tag` tinyint(4) NOT NULL,
`dt` date NOT NULL
);
使用的sqoop 导出命令,相关教程网上较多请自行查找
sqoop export -D mapreduce.job.queuename=sqoop
--connect jdbc:mysql://localhost:3306/dbname
--username root
--password root_pwd
--table user_tag
--staging-table user_tag_staging
--clear-staging-table
-m 1
--export-dir /user/hadoop/user_tag/dt=2018-03-27
--null-string '\\N'
--null-non-string '\\N'
--fields-terminated-by \001
值得注意的是staging-table这个是一个与目标表结构一样的表,是一个中间表,目的是用于对sqoop export进行容错,clear-staging-table将表清空。在操作的过程中自己遇到的的一个问题是mysql表比hive中的表多了dt字段,导致同步的时候一直报错,最终在hive中添加了一个字段用于存储时间(名字任意),实际上sqoop同步中它先从mysql中查询一条数据,解析出数据的类型,生成一个java文件,再对hdfs文件进行解析,并将数据逐条插入到mysql中,全程与hive无关。接下来就就是插入或者更新user_tag_all这个表了
insert into user_tag_all(mid,tag,dt) select mid,tag,dt from user_tag where dt='2018-03-16' ON DUPLICATE KEY UPDATE tag=VALUES(tag),dt=VALUES(dt);
总结####
本篇文章虽然任务不大,但是从中有许多值得借鉴的知识点
mysql分区表的使用,以及存储过程的编写
mysql的不存在则插入、存在则更新的使用
sqoop命令的使用,中间表的作用
ref###