实际生产环境中,我们要处理的数据来自可能各个地方,业务数据库,爬虫数据库,日志文件,api网关买入数据等。
本次黑马头条推荐项目中,业务数据存储在mysql中,用户行为数据存储在日志中,因此采用两种技术手段将业务数据和日志数据传输到Hadoop中。
一、迁移数据库
业务数据存储在mysql中,为了避免直接操作业务数据,利用sqoop导入到hive表中(底层数据就是存储在HDFS上)
业务数据不是一次导入就结束,每天都会产生很多新的业务数据,因此这里就涉及到利用sqoop导入数据到hive的方式。
Sqoop(发音:skup)是一款开源的工具,主要用于在Hadoop(Hive)与传统的数据库(mysql、postgresql…)间进行数据的传递,可以将一个关系型数据库(例如 : MySQL ,Oracle ,Postgres等)中的数据导进到Hadoop的HDFS中,也可以将HDFS的数据导进到关系型数据库中。
【Sqoop支持两种方式的数据导入,即全量数据导入和增量数据导入】。
1、sqoop导数据的方式一:全量数据导入
(1)概念:如同名字那样,全量数据导入就是一次性将所有需要的数据,从关系型数据库一次性导入到Hadoop生态中(可以是HDFS,Hive,Hbase等)。
(2)适用场景:一次性离线分析场景
(3)代码实现:用sqoop import命令,具体如下:
# 全量数据导入
Sqoop import
1. --connect jdbc:mysql://192.168.15.111/toutiao \
2. --username root \
3. --password 123456 \
4. --table $table_name \
5. --query ‘select * from $table_name where $conditions ’ \
6. --m 5 \
7. --hive-import \
8. --hive-home /usr/local/hive \
9. --hive -drop-import-delims \
10. --target-dir /user/hive/warehouse \
11. --create-hive-table \
12. --hive-table toutiao.$table_name \
2、Sqoop导数据的方式二:增量数据导入
(1)使用场景:实际生产环境中,不断有业务相关的数据产生到关系型数据库,系统需要定期从数据库向hadoop导入数据,导入数仓后,继续进行离线分析。我们不可能将所有的数据重新导一遍,此时就需要用sqoop的增量数据导入模式。
增量数据导入分两种,一是基于递增列的增量数据导入(Append),二是基于时间序列的增量数据导入(LastModified)
2.1基于递增列的增量数据导入(Append)
举个栗子,有一个订单表,里面每个订单有一个唯一标识自增列ID,在关系型数据库中以主键形式存在。之前已经将id在0~5201314之间的编号的订单导入到Hadoop中了(这里为HDFS),一段时间后我们需要将近期产生的新的订单数据导入Hadoop中(这里为HDFS),以供后续数仓进行分析。此时我们只需要指定–incremental 参数为append,–last-value参数为5201314即可,表示只从id大于5201314后开始导入。
1. sqoop import \
2. --connect jdbc:mysql://192.168.15.111:3306/test \ #连接到指定数据库
3. --username root \
4. --password 123456 \
5. --table order_table \ # 指定数据库的指定表
6. --target-dir /user/mysql_to_hdfs \
7. --m 3 \
8. –-hive import \
9. –-incremental append \ # 指明模式
10. –-check-column order_id # 指明用于增量导入的参考列
11. –-last-value 5201314 \ # 指定参考列上次导入的最大值
参数 | 说明 |
---|---|
–incremental append | 基于递增列的增量导入(将递增列值大于阈值的所有数据增量导入Hadoop) |
–check-column | 递增列(int) |
–last-value | 阈值(int) |
2.2基于时间序列的增量数据导入(LastModified)
此方式要求原有表中有time字段,它能指定一个时间戳,让Sqoop把该时间戳之后的数据导入至Hadoop(这里为HDFS)。比如我的头条业务数据库中,某篇文章的点赞数增加或减少了,变成了一个新的数据,在我指定的时间戳后面作为新的业务数据导入到了Hadoop(这里指HDFS),我们可以指定给merge-key参数,例如是article_id,表示将后续的新的记录与原有的记录合并。
代码:
sqoop import \
1. --connect jdbc:mysql://192.168.15.111/toutiao \
2. --username root \
3. --password password \
4. --table article_basic \ # 指定数据表导入Hadoop
5. --m 4 \
6. --target-dir /user/hive/warehouse/toutiao.db/article_basic \
7. --incremental lastmodified \
8. --check-column update_time \
9. --merge-key article_id \
10. --last-value '2012-02-01 11:0:00'
重要参数
参数 | 说明 |
---|---|
–incremental lastmodified | 基于时间列的增量导入(将时间列大于等于阈值的所有数据增量导入Hadoop) |
–check-column | 时间列(int) |
–last-value | 阈值(int) |
–merge-key | 合并列(主键,合并键值相同的记录) |
这里注意-incremental lastmodified 模式不支持用sqoop直接导入到hive中,需要先导入到hdfs,然后建立hive表关联。
那怎么才能实现mysql数据迁移到hive上呢?
Sqoop将mysql数据导入到HDFS上,指定位置:
–target-dir /user/hive/warehouse/toutiao.db/
然后进入hive交互界面,在toutiao.db数据库中建表,表名和传上来的数据文件一样,hive就能自动将数据映射到hive表中。
【导入的过程总有一些坑,这里提供一些避坑指南】
1、注意:sqoop将数据导出的hdfs分片数据,默认用‘ ,’分割,而hive默认的分隔符是’ 001’。所以在hive中创建表的时候要指定分隔符。
2、原mysql中某些字段存在特定字符,如,、\t \n 都会导致导入到hadoop被hive读取失败,解
析时会认为是另一条数据,或者多一个字段。
///解决办法///:
导入时,加入query参数,选择特定字段,过滤相应内容,使用replace,char替换字符
3、mysql数据库里面字段是tinyint类型,通过sqool导入到hdfs,hive建表映射数据后,该字段却显示True,False,这是因为jdbc会把tinyint认为是java.sql.Types.BIT,然后hive就转为Boolean类