Hive实战

本篇主要是通过一个综合性的案例,看看离线处理在生产上面是如何使用的。

需求:统计各个城市所属区域下最受欢迎的Top n产品

大数据处理:离线、实时

input :HDFS、mysql、Hbase......                              处理之前肯定有数据输入进来

处理(分布式) MapReduce/Hive/Spark/Flink            处理的方式有很多种

output :mysql、HDFS.....                                          处理之后肯定要输出到某个地方

本次案例中,从hdfs和mysql拿数据,然后用hive进行处理,然后输入到mysql中去。

本次需求:统计各个城市所属区域下最受欢迎的Top 3产品

在电商网站上,比如京东天猫上,点击某件商品时,后台都会记录日志信息,(在浏览器的工具里有个开发者工具,可以找到相关的商品信息,比如说某个log.gif文件,经过URLDecode转码工具转了之后,可以看到商品信息等)

那么现在我们要去 统计各个城市所属区域下最受欢迎的Top n产品?

就需要从日志里面去找到对应商品以及对应城市的信息,挖掘出有用的信息。

日志是存放在HDFS上的。

日志里面有商品信息,比如商品ID,还有城市的ID,但是没有城市的名称。

我们需要的是商品信息,这个是可以从日志里获取出来,但是城市的名称在日志里没有。

这些城市区域是存放在MySQL里的。

这些信息不怎么变化的,一般放在关系型数据库里面。还有产品的信息也会放在MySQL里。

MySQL:
       城市区域对应信息
       产品信息表

Hive:用户点击行为日志表

MySQL里有两张表,city_info城市信息表、product_info产品信息表:

city_info城市信息表:

product_info产品信息表:

在hdfs上有user_click.txt日志信息,如下图:

在user_click.txt中有5列,第一列是用户id,第二列是session  id 不需要关心,第三列是访问的时间,第四列是城市的id,第五列是产品的id。

现在用hive创建一张  用户点击行为日志表 :

create table user_click(
user_id int,
session_id string,
action_time string,
city_id int,
product_id int
) partitioned by (day string)
row format delimited fields terminated by ',';

然后加载数据进去:
load data local inpath '/home/hadoop/data/topn/user_click.txt' overwrite into table user_click partition(day='2016-05-05');

就是说现在有三张表:city_info城市信息表、product_info产品信息表(MySQL),user_click用户点击行为日志表(hive)

现在需要在hive中操作者三张表,所以需要把MySQL中的两张表想办法弄到hive上来。在Hive里面完成我们的业务逻辑统计操作,在hive中处理过后再把处理的结果输出到MySQL中去。在这里就需要一个工具:Sqoop。

Sqoop也是个顶级项目。网址:http://sqoop.apache.org/

Sqoop:关系型数据库 <==> Hadoop 

Sqoop是一个工具,可以在hadoop和关系型数据库之间高效的批量转移数据。就是把hadoop的数据传输到关系型数据库,或者把关系型数据库的数据传输到hadoop上。

Sqoop有两个版本 :一和二

Sqoop: 1.4.7
Sqoop2:  1.99.7 (用的很少,不好用)

下载Sqoop软件:

wget http://archive.cloudera.com/cdh5/cdh/5/sqoop-1.4.6-cdh5.7.0.tar.gz

解压:

tar -zxvf sqoop-1.4.6-cdh5.7.0.tar.gz 

解压之后把MySQL的驱动拷贝到lib目录下

cp mysql-connector-java.jar  sqoop-1.4.6-cdh5.7.0/lib/

配置:进入到/home/hadoop/app/sqoop-1.4.6-cdh5.7.0/conf  目录:

vi sqoop-env.sh 编辑一下:

配置完后:

配置一下环境变量:[hadoop@10-9-140-90 ~]$ vi .bash_profile  加入这两行:

export SQOOP_HOME=/home/hadoop/app/sqoop-1.4.6-cdh5.7.0
export PATH=$SQOOP_HOME/bin:$PATH

source生效一下。

然后就可以启动sqoop了:

用 sqoop help  可以查看sqoop的用法:

导入导出的出发点是Hadoop
导入(import): MySQL ==> Hadoop
导出(export): Hadoop ==> MySQL

可以  sqoop import --help   查看import的具体用法。

现在需要把MySQL上的城市区域信息和产品信息导入到hive中。

 MySQL ==> Hive

需要先在hive中建立两张表:

create table city_info(
city_id int,
city_name string,
area string
)row format delimited fields terminated by '\t';

create table product_info(
product_id int,
product_name string,
extend_info string
)row format delimited fields terminated by '\t';

然后用sqoop工具把数据导入进来:

sqoop import --help 看一下如何导入的。

sqoop import \
--connect jdbc:mysql://localhost:3306/ruozedata \
--username root --password 123456 \
--delete-target-dir \
--table city_info \
--hive-import \
--hive-table city_info \
--hive-overwrite \
--fields-terminated-by '\t' \
--lines-terminated-by '\n' \
--split-by city_id \
-m 2

看上面的语句,总结下来就是,前面先是连接到数据库、用户名、密码、表名,后面是 导入到hive、导入到hive的表名、重写、字段分隔符、行分隔符。

加delete-target-dir 是因为跑MapReduce的时候,如果指定的目录已经存在会报错,所以加上这个,如果目录存在就删除。

加split-by 是因为  在Sqoop里面默认的mapper数是4,它处理会以主键id进行区分,比如如果有40条记录,那么就是按照主键id进行分配,每个mapper处理10条数据。但是我们的表是没有主键的,所以在这里要手动指定一下。

-m 2    指定map的数量为2    m不指定的话就是4

导入的时候报错了:

Exception in thread "main" java.lang.NoClassDefFoundError: org/json/JSONObject

这是由于sqoop缺少java-json.jar包 导致的,下载这个jar包,然后放到/home/hadoop/app/sqoop-1.4.6-cdh5.7.0/lib/下即可。

下载:weget http://www.java2s.com/Code/JarDownload/java-json/java-json.jar.zip

下载后需要解压一下。

然后再运行import语句,发现还有错误:

这是因为缺少hive下的hive-exec-1.1.0-cdh5.7.0.jar这个jar包导致的,需要把

/home/hadoop/app/hive-1.1.0-cdh5.7.0/lib/hive-exec-1.1.0-cdh5.7.0.jar

拷贝到   ~/app/sqoop-1.4.6-cdh5.7.0/lib/  下。

然后再运行语句,运行成功了,但是去d6_test数据库查还是没有,

仔细检查上面那个语句发现少了个 --hive-database d6_test \    ,然后发现数据导入到default数据库里面了。

sqoop import \
--connect jdbc:mysql://localhost:3306/ruozedata \
--username root --password 123456 \
--delete-target-dir \
--table city_info \
--hive-import \
--hive-database d6_test \
--hive-table city_info \
--hive-overwrite \
--fields-terminated-by '\t' \
--lines-terminated-by '\n' \
--split-by city_id \
-m 2

然后再导入就有了:

然后再导入产品信息表:

sqoop import \
--connect jdbc:mysql://localhost:3306/ruozedata \
--username root --password 123456 \
--delete-target-dir \
--table product_info \
--hive-import \
--hive-database d6_test \
--hive-table product_info \
--hive-overwrite \
--fields-terminated-by '\t' \
--lines-terminated-by '\n' \
--split-by product_id \
-m 2

然后就可以看到了:

到现在 前期的准备工作已经做完,‘

Hive中已经有三张表: city_info  product_info  user_click

下面要做的就是统计分析。

先做一张   商品基础信息 表
create table tmp_product_click_basic_info
as
select u.product_id, u.city_id, c.city_name, c.area
from
(select product_id, city_id from user_click where day='2016-05-05' ) u
join
(select city_id, city_name,area from city_info) c
on u.city_id = c.city_id ;

再创建一张  各区域下各商品的访问次数  的表:
create table tmp_area_product_click_count 
as 
select 
product_id, area, count(1) click_count 
from 
tmp_product_click_basic_info 
group by 
product_id, area ;

再创建一张   获取完整的商品信息的各区域的访问次数  的表:

create table tmp_area_product_click_count_full_info
as
select 
a.product_id, b.product_name, a.area, a.click_count
from 
tmp_area_product_click_count a join product_info b
on a.product_id = b.product_id

 最后需要一个窗口函数,根据区域进行分组,然后过滤:

这个就是所要的结果:

create table area_product_click_count_top3 as
select t.*,'2016-05-05' as day 
from (
select 
product_id, product_name,area, click_count,
row_number() over(partition by area order by click_count desc) rank 
from tmp_area_product_click_count_full_info
) t where t.rank <=3;

最后把统计结果输出到MySQL 用 sqoop export。

在MySQL中创建一张一样的表:

create table area_product_click_count_top3 
(product_id int(11),
product_name varchar(255),
area varchar(255),
click_count int(11),
rank int(11),
day varchar(255))
ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后用 sqoop  export 把hive的这张表的数据导入进来。

用下面语句:

sqoop export \
--connect jdbc:mysql://localhost:3306/ruozedata \
--username root --password 123456 \
--table area_product_click_count_top3 \
--columns product_id,product_name,area,click_count,rank,day \
--export-dir /user/hive/warehouse/d6_test.db/area_product_click_count_top3 \
--input-fields-terminated-by '\001' \
-m 2

运行成功后,去mysql里看一下:

再重新运行一下上面的sqoop export 语句,再看:

发现重复了。这时需要加上两行 去重:(下面红色标注)

sqoop export \
--connect jdbc:mysql://localhost:3306/ruozedata \
--username root --password 123456 \
--table area_product_click_count_top3 \
--columns product_id,product_name,area,click_count,rank,day \
--export-dir /user/hive/warehouse/d6_test.db/area_product_click_count_top3 \
--update-key product_id \
--update-mode updateonly \

--input-fields-terminated-by '\001' \
-m 2
 

到此为止,完成。

另外:上面的过程如果每天需要处理,不可能每次都要像上面一样处理,需要把上面的整个过程放在shell脚本里面,每天凌晨去执行处理昨天的数据,这个就是离线处理。离线处理的时间一般都比较久,比如几小时、十几个小时等。

另外,上面很多group by 这可能会导致数据倾斜,那么数据倾斜应该怎么去解决???(面试必问)

  • 4
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值