Hive：用SQL对数据进行操作，导入数据、清洗脏数据、统计数据订单、优化结果输出等等

转x33g5p2x  于2021-11-21 转载在 Hive  
字(9.0k)|赞(0)|评价(0)|浏览(1479)

① Hive 数据管理、内外表、安装模式操作

② Hive：用SQL对数据进行操作，导入数据、清洗脏数据、统计数据订单

③ Hive：多种方式建表，需求操作

④ Hive：分区原因、创建分区、静态分区 、动态分区

⑤ Hive：分桶的简介、原理、应用、创建

⑥ Hive：优化 Reduce，查询过程；判断数据倾斜，MAPJOIN

1、准备数据

百度网盘链接：https://pan.baidu.com/s/1QDlf7SoGPWliagV2ettMOQ
提取码：3jcn

2、了解数据

head -10 orders.csv

  1. order_id:订单号
  2. user_id:用户id
  3. eval_set:订单的行为（历史产生的或者训练所需要的）
  4. order_number：用户购买订单的先后顺序
  5. order_dow：order day of week ,订单在星期几进行购买的（0-6）
  6. order_hour_of_day：订单在哪个小时段产生的（0-23）
  7. days_since_prior_order：表示后一个订单距离前一个订单的相隔天数

head -10 order_products__train.csv

  1. order_id:订单号
  2. product_id:商品ID
  3. add_to_cart_order：加入购物车的位置
  4. reordered：这个订单是否重复购买（1 表示是 0 表示否）

head -10 products.csv （数据仓库定位：商品维度表）

  1. product_id：商品ID
  2. product_name：商品名称
  3. aisle_id：货架id
  4. department_id:该商品数据属于哪个品类，日用品，或者生活用品等

  1. departments.csv（品类维度表）
  2. department_id：部门id, 品类id
  3. department: 品类名称
  5. department_id,department
  6. 1,frozen
  7. 2,other
  8. 3,bakery
  10. order_products__prior.csv（用户历史行为数据）
  12. order_id,product_id,add_to_cart_order,reordered
  13. 2,33120,1,1
  14. 2,28985,2,1
  15. 2,9327,3,0

3、将数据导入hive

对于orders.csv，我们指导他是以‘,’ 作为字段分割符，行与行之间数据是‘\n’是分割。

在hive数据库创建orders表

  1. create table badou.orders(
  2. order_id string
  3. ,user_id string
  4. ,eval_set string
  5. ,order_number string
  6. ,order_dow string
  7. ,order_hour_of_day string
  8. ,days_since_prior_order string
  9. )
  10. row format delimited fields terminated by ','
  11. lines terminated by '\n';

可以得知，创建orders表成功；接下来我们要把orders.csv数据加载到orders表中。

加载数据到hive，有两种方式：

  • 加载本地数据到Hive，overwrite 覆盖， into 追加
  1. load data local inpath 'day3/orders.csv' overwrite into table orders;
  • HDFS数据加载到Hive (没有 local，要保证HDFS有数据)
  1. load data inpath 'day3/orders.csv' 
  2. overwrite into table orders;

我们这里选择从本地加载数据。

  1. load data local inpath 'day3/orders.csv' overwrite into table orders;
  3. select * from orders limit 10;

可以发现，第一行数据是脏数据。

我们要自动显示下每个数据的字段名称。
进入 hive-site.xml，在(master)进行配置：

  1. vi hive-site.xml
  2. <!--Hive第一行显示列名称-->
  3. <property>
  4.   <name>hive.cli.print.header</name>
  5.    <value>true</value>
  6.   <description>显示列名称</description>
  7. </property>

exit; 退出hive，再重新进入hive。

  1. use badou;
  2. select * from orders limit 10;

创建trains表，加载order_products__train.csv也是如此操作。

4、如何清洗第一行的脏数据？

4.1 方式一：shell命令

思想：在load加载数据之前，针对异常数据进行处理，用 sed '1d' orders.csv。

  1. 测试：
  2. head -10 orders.csv > tmp.csv
  3. cat tmp.csv
  4. sed '1d' tmp.csv > tmp_res.csv
  5. cat tmp_res.csv

4.2 方式二：HQL (hive sql)

  1. insert overwrite table orders
  2. select * from orders where order_id !='order_id';

4.3 方式三：更新表，过滤首行(个人建议用这个SQL命令)

  1. alter table trains set tblproperties('skip.header.line.count'='1');

5、每个用户有多少个订单？ (分组)

我们知道，user_id为用户ID，order_id为订单ID，count(order_id) 统计订单数。

  1. select user_id, count(distinct order_id) as order_cnt
  2. from orders group by user_id
  3. limit 20;

distinct ：表示去重
as：把count的结果作为order_cnt

对order_cnt进行从大到小排序的话。

  1. select user_id, count(distinct order_id) as order_cnt from orders
  2.  group by user_id
  3.  order by order_cnt desc
  4. limit 10;

6、每个用户一个订单平均是多少商品？

先创建priors表，加载order_products__prior.csv数据。

  1. create table priors(
  2. order_id string,
  3. product_id string,
  4. add_to_cart_order string,
  5. reordered string
  6. )
  7. row format delimited fields terminated by ','
  8. lines terminated by '\n';
  10. load data local inpath 'day3/order_products__prior.csv' 
  11. overwrite into table priors;
  13. 过滤脏数据
  14. alter table priors set tblproperties('skip.header.line.count'='1');

6.1 一个订单有多少个商品？

在priors 表对订单order_id分组，我们选择订单ID和产品数量，产品数量as pro_cnt。

  1. select order_id,count(distinct product_id) as pro_cnt from priors 
  2. group by order_id 
  3. limit 10;

6.2 一个用户有多少商品？

在orders表有用户ID，priors表有商品ID，这两个表同时有 订单ID order_id，可以把表连接起来，把7.1的结果 as t，在t表我们就有 order_id 和 pro_cnt 两个字段。

  1. (select order_id, count(distinct product_id) 
  2. as pro_cnt from priors
  3.     group by order_id
  4.     limit 10000
  5. ) as t
  1. select user_id,pro_cnt from orders as od
  2. inner join t 
  3. on od.order_id=t.order_id
  4. limit 10;

完整SQL语句：

  1. select od.user_id, t.pro_cnt from orders as od
  2. inner join (
  3.     select 
  4.     order_id, count(distinct product_id) as pro_cnt
  5.     from priors
  6.     group by order_id
  7.     limit 10000
  8. ) as t
  9. on od.order_id=t.order_id
  10. limit 10;

as 是可以省略的；
结果应该有用户ID与商品数量 od.user_id, t.pro_cnt。

6.3 针对步骤6.2，进行用户对应的商品数量 sum求和。

对商品数量t.pro_cnt进行求和，即sum(t.pro_cnt)。

【注意】: 使用聚合函数（count、sum、avg、max、min ）的时候要结合group by 进行使用。
从 7.2代码进行修改：

  1. select od.user_id, sum(t.pro_cnt) as sum_prods from orders od
  2. inner join (
  3.     select order_id, count(distinct product_id) as pro_cnt
  4.     from priors
  5.     group by order_id
  6.     limit 10000
  7. ) as t
  8. on od.order_id=t.order_id
  9. group by od.user_id
  10. limit 10;

我们，修改 sum(t.pro_cnt) as sum_prods 后，需要进行group by操作，即group by od.user_id。显示结果有 od.user_id sum_prods。

6.4 一个用户平均一个订单有多少个商品？

即 用户的商品数量 / 用户的订单数量 == sum(t.pro_cnt)/count(t.order_id) as sc_prod,
也可以用平均产品数量avg，avg(pro_cnt) as avg_prod

  1. select od.user_id
  2. ,sum(t.pro_cnt)/count(t.order_id) as sc_prod
  3. ,avg(pro_cnt) as avg_prod 
  4. from orders od inner join (
  5.     select 
  6.     order_id, count(distinct product_id) as pro_cnt
  7.     from priors
  8.     group by order_id
  9.     limit 10000
  10. ) t
  11. on od.order_id=t.order_id
  12. group by od.user_id
  13. limit 10;

6.5 每个用户在一周中的购买订单的分布?

在 orders表中，的列order_dow，代表购买订单是在一周的星期几，0-6 代表周一到周日。

  1. 查询:
  2. head -30 orders.csv
  4. select * from orders limit 30;

思路： 要输出的是用户id，与星期几一天的总订单量。即需要sum(星期几订单)，判断if order_dow='0'/ '1'/ '2'/ '3'/ '4'/ '5'/ '6' --> true 为1, false 为0，把结果sum，则得出：星期几一天的总订单量。例如：

  1. user_id order_dow  
  2. 	1   	 0   sum=0+1=1
  3. 	1   	 0   sum=1+1=2
  4. 	1   	 1   sum=0+1=1
  5. 	2   	 1	 sum=0+1=1
  6. 由上面数据可知，用户id为1，星期一(0),有2个订单;星期二(1),有1个订单;
  7. 用户id为2，星期二(1),有1个订单;

【注意】： 实际开发中，一定是最先开始使用小批量数据进行验证，验证代码逻辑的正确性，然后全量跑！！，提高工作效率。

方式一：用case when 语句

  1. select user_id
  2. , sum(case when order_dow='0' then 1 else 0 end) dow0
  3. , sum(case when order_dow='1' then 1 else 0 end) dow1
  4. , sum(case when order_dow='2' then 1 else 0 end) dow2
  5. , sum(case when order_dow='3' then 1 else 0 end) dow3
  6. , sum(case when order_dow='4' then 1 else 0 end) dow4
  7. , sum(case when order_dow='5' then 1 else 0 end) dow5
  8. , sum(case when order_dow='6' then 1 else 0 end) dow6
  9. from orders
  10. where user_id in ('1','2','3')
  11. group by user_id;

方式二：用 if 语句

  1. select user_id
  2. , sum(if(order_dow='0',1,0)) as dow0
  3. , sum(if(order_dow='1',1,0)) dow1
  4. , sum(if(order_dow='2',1,0)) dow2
  5. , sum(if(order_dow='3',1,0)) dow3
  6. , sum(if(order_dow='4',1,0)) dow4
  7. , sum(if(order_dow='5',1,0)) dow5
  8. , sum(if(order_dow='6',1,0)) dow6
  9. from orders
  10. where user_id in ('1','2','3')
  11. group by user_id;

我们验证数据：从上下图可知: 用户id为1: 星期一没有订单，星期二有3个订单。从下图可知验证正确。。小批量验证完成，我们可以取消 where语句，进行全部量操作。

6.6 查看 在12点时间段每个用户购买了哪些商品？

  1. 分析：需要 用户ID:user_id, 商品ID:product_id, 时间：order_hour_of_day;
  2. orders表有 : order_id, user_id;
  3. trains表有：order_id, product_id;
  4. 把两个表连接起来 inner join。

SQL语句：

  1. select od.user_id, tr.product_id from 
  2. 	orders od inner join trains tr
  3. 	on od.order_id=tr.order_id
  4. 	where order_hour_of_day = '12'
  5. 	limit 10;

由图可知，在12点，‘21’ 用户 购买了 ‘25740、12683、44632、10957、32645、16615’ 等商品。

7、一个用户平均每个购买天中，购买的商品数量

orders 表中的 days_since_prior_order 等价于购买天数。
我们要用户ID，商品数量，购买天数。
orders 表与priors表 连接可以得到 “用户ID，商品数量，购买天数。”

  • 6.1中可以求得一个订单有多少个商品，结果保存为pri 表；
  • 从orders表把 order_id, user_id, days_since_prior_order 结果存储为 ord；
  • 用 join 连接，查找出select user_id, sum(商品数量) / count(购买天数) from ord join pri on ord.order_id=pri.order_id group by ord.user_id 输出结果
  1. -- ord表,对购买天数的数值需要进行空处理
  2. (select order_id, user_id, if(days_since_prior_order='','-1',days_since_prior_order) as days_since_prior_order
  3. from orders
  4. where eval_set='prior' limit 10000
  5. ) ord
  7. -- pro_cnt 为商品数量
  8. select order_id, count(1) as pro_cnt from priors
  9. group by order_id 
  10. limit 10000
  11. ) pri

完整代码：

  1. select ord.user_id, sum(pri.pro_cnt) / count(distinct days_since_prior_order)  avg_prod
  2. from
  3. ( select order_id, user_id, 
  4. if(days_since_prior_order='','-1',days_since_prior_order) as days_since_prior_order
  5. from orders
  6. where eval_set='prior' limit 10000
  7. ) ord
  8. join (
  9. select order_id, count(1) as pro_cnt from priors
  10. group by order_id 
  11. limit 10000
  12. ) pri
  13. on ord.order_id=pri.order_id
  14. group by ord.user_id
  15. limit 10;

由图结果可知：由用户ID，平均一天商品购买数量。

8、每个用户最喜爱购买的三个商品product是什么？

最喜欢购买就是 购买的商品最多。
因此要先求 每个用户购买的商品的次数，再对次数排序，前三个。

8.1 每个用户购买的商品的次数

  1. select ord.user_id, pri.product_id, count(1) as top_cnt
  2. from 
  3. (select * from orders where eval_set='prior'
  4. ) ord
  5. join (
  6. select * from priors limit 1000
  7. ) pri
  8. on ord.order_id=pri.order_id
  9. group by ord.user_id,pri.product_id
  10. limit 20;
  12. -- 可以 用 partition by 替换 group by。
  13. select ord.user_id, pri.product_id, 
  14. count(1) over(partition by ord.user_id,pri.product_id) as top_cnt
  15. from 
  16. (select * from orders where eval_set='prior'
  17. ) ord
  18. join (
  19. select * from priors limit 1000
  20. ) pri
  21. on ord.order_id=pri.order_id
  23. -- group by ord.user_id,pri.product_id
  24. limit 20;

8.2 对购买的商品次数进行rank

  1. --用with关键字 把每个用户购买的商品的次数结果为rank_tmp
  2. with rank_tmp as (select ord.user_id, pri.product_id, 
  3. count(1) over(partition by ord.user_id,pri.product_id) as top_cnt
  4. from 
  5. (select * from orders where eval_set='prior'
  6. ) ord
  7. join (
  8. select * from priors limit 1000
  9. ) pri
  10. on ord.order_id=pri.order_id
  11. )

在操作排名，排序常用函数 row_number() ，用于给数据库表中的记录进行标号，在使用的时候，其后还跟着一个函数 over()，而函数 over() 的作用是将表中的记录进行分组和排序。

  1. row_number() over() --分组排序功能;
  2. --语法格式：
  3. row_number() over(partition by 分组列 order by 排序列 desc)

在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。

这里我们要：row_number(), 是对用户id分组，对商品次数进行降序排序。

  1. select * from 
  2. (
  3. select user_id, product_id,
  4. row_number() over(partition by user_id order by top_cnt desc) as row_num 
  5. from rank_tmp
  6. -- 排序结果保存为t表 
  7. ) t 
  8. --得出排序结果，where取前三个
  9. where row_num <= 3
  10. limit 20;

可以发现，每个用户最喜爱购买的三个商品。

8.3 优化输出结果格式

要的显示结果：user1 [100_3,101_2,102_1]

  1. user1 [100_3,101_2,102_1]
  2. 100代表商品id，_3 中的3是第三个喜欢的意思
  3. 像 101_2，2是第二个喜欢的商品。
  5. 把转为列表形式用collect_list()函数;
  6. concat_ws('-',列名1,列名2) = 列名1-列名2
  7. 如果列名2是整型要cast为string类型。
  8. --即：
  9. collect_list(concat_ws('_',product_id, cast(row_num as string))) as pro_top3

我们要以上面的列表格式化输出，列表里面的数据为 最喜欢的前三名商品。
同样把上面8.2的结果用 with 进行保存为 rank_result。

  1. with rank_tmp as (select ord.user_id, pri.product_id, 
  2. count(1) over(partition by ord.user_id,pri.product_id) as top_cnt
  3. from 
  4. (select * from orders where eval_set='prior'
  5. ) ord
  6. join (
  7. select * from priors limit 1000
  8. ) pri
  9. on ord.order_id=pri.order_id
  10. ),
  11. -- 要with嵌套，注意这里用逗号分割
  12. rank_result as ( -- 注意这里没有with
  13. select user_id, product_id,
  14. row_number() over(partition by user_id order by top_cnt desc) as row_num 
  15. from rank_tmp
  16. )
  17. -- 正式查询语句，与上面的with as直接没有符号分割
  18. select user_id, collect_list(concat_ws('_',product_id, cast(row_num as string))) as pro_top3,
  19. -- size 返回列表中元素的个数
  20. size(collect_list(product_id)) as top_size
  21. from rank_result
  22. where row_num <= 3
  23. group by user_id
  24. limit 20;

发现上图结果满足，用户的最喜欢商品以列表形式返回，商品id后的‘_1’ 代表 第一个喜欢商品，size返回列表商品个数。

版权说明 : 本文为转载文章, 版权归原作者所有 版权申明

原文链接 : https://blog.csdn.net/weixin_44775255/article/details/121254645

内容来源于网络，如有侵权，请联系作者删除！

sqlhadoopHive大数据 linux

相关文章

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新文章

    更多

    目录

    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com