Hive:多种方式建表,需求操作

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

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

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

③ Hive:多种方式建表,需求操作

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

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

⑥ Hive:优化 Reduce,查询过程;判断数据倾斜,MAPJOIN

数据链接https://pan.baidu.com/s/1cbVd7HhluIMiizQ8mSz5tA
提取码:4dg4

1、认识数据:u.data

u.data是完整的数据集,有943个用户ID,1682个商品ID的10万条数据,时间戳为关键字。
每个用户至少评分了20部电影,用户和项目是从1开始连续编号,这些数据是随机分布的。
字段之间用'\t',区分,行与行用'\t'区分。

`user_id` string, 用户id
`item_id` string, 商品id
`rating` string, 评分
`timestamp` string, 时间戳

2、创建udata表,加载数据

--创建表
CREATE TABLE `udata`(
  `user_id` string, 
  `item_id` string, 
  `rating` string, 
  `timestamp` string)
row format delimited fields terminated by '\t'
lines terminated by '\n';

--加载数据
load data local inpath 'day3/hive-data/u.data' 
overwrite into table udata;

创建表也是有几种方式:

第一种是直接创建表,字段类型,字段备注,数据存储格式的自定义

2、抽取(as)建表:
create table udata_as as select * from udata;
场景:中间逻辑处理的时候,进行建表,直接复制表的数据和结构。

3、like建表:
create table udata_like like udata;
场景:只关注表结构,不需要数据。

抽取(as)建表:

like建表

在udata表通过timestamp进行先后顺序的标记区分,

想知道距离现在最近或者最远的时间是什么时候?
对时间戳进行min,max操作。

select max(`timestamp`) max_timestamp, min(`timestamp`) min_timestamp
from udata;

时间戳可以转为年月日形式,百度一下时间戳即可。
max_timestamp   min_timestamp
893286638   874724710

3、Hive执行顺序

在hive的执行语句当中的执行查询的顺序:
select … from … where … group by … having … order by …
执行顺序: from … where …group by … having …select … order by …

其实总结hive的执行顺序也是总结mapreduce的执行顺序:

MR程序的执行顺序:
map阶段:

  1. 执行from加载,进行表的查找与加载;
  2. 执行where过滤,进行条件过滤与筛选;
  3. 执行select查询:进行输出项的筛选;
  4. map端文件合并:map端本地溢出写文件的合并操作,每个map最终形成一个临时文件, 然后按列映射到对应的的reduceReduce阶段。

Reduce阶段:

  1. group by:对map端发送过来的数据进行分组并进行计算;
  2. having:最后过滤列用于输出结果;
  3. order by 排序后进行结果输出到HDFS文件。

所以通过上面的例子我们可以看到,在进行select之后我们会形成一张表,在这张表当中进行分组排序操作。

4、需求:得到某一个用户具体的评论时间。

从评论时间可以推出该用户是否比较活跃。

  • ① 用户确实很活跃;
  • ② 用户可能存在刷单的情况,刷评价。

我们要的结果是:user_id [‘2020-12-19’,‘2020-12-18’,…] 格式。
用select collect_list(‘参数’),可以实现。
例如:select collect_list('1,2,3');

collect_list(): 不去重,将所有的user_id进行收集;
cast()函数: 将一个类型的数据转换成另一个数据类型;
语法:cast(value AS TYPE)。

一个用户具体的评论时间:

select user_id, 
(cast(893286638 as bigint)-cast(`timestamp` as bigint))/(24*60*60)*rating as days
from udata

把上面的结果存到临时表t,在 t 中 选择 user_id, collect_list(cast(days as int)) 列表形式输出每个用户的活跃度。

select user_id, 
collect_list(cast(days as int)) as day_list
from (select user_id, 
(cast(893286638 as bigint)-cast(`timestamp` as bigint))/(24*60*60)*rating as days
from udata
) t
group by user_id
limit 20;

由上图可知,每个用户的所有评论时间。

5、需求: 用户购买的商品数量大于100的有哪些用户?

整合 orders+priors, orders+trains;
orders:order_id, user_id
priors:order_id, product_id
trains:order_id, product_id

对订单数据与历史数据进行 union all,这样就不会出现数据缺失的情况,保持数据完整性。

-- 订单训练数据
select a.user_id, b.product_id from 
orders as a left join trains b
on a.order_id=b.order_id

union all
-- 订单历史数据
select a.user_id, b.product_id from 
orders as a left join priors b
on a.order_id=b.order_id
limit 15;

把union all 的结果 存到 t 临时表中,在t表,select user_id, count(distinct product_id)group by user_id
having pro_cnt >= 100 。可以得出:用户购买的商品数量大于100的有哪些用户?

方式一:

select user_id, 
count(distinct product_id) pro_cnt from
(
-- 订单训练数据
select a.user_id, b.product_id from 
orders as a left join trains b
on a.order_id=b.order_id

union all
-- 订单历史数据
select a.user_id, b.product_id from 
orders as a left join priors b
on a.order_id=b.order_id
) t
group by user_id
having pro_cnt >= 100
limit 15;

可以发现,上图结果 用户购买的商品都是大于100的。

方式二:引入 with关键字

作用:涉及到逻辑很复杂,嵌套关系特别多使用,提高代码阅读性,便于排查问题。
通过with修饰的可以理解为临时表或者临时数据集。

with user_pro_cnt_tmp as (
select * from 
(-- 订单训练数据
select 
a.user_id,b.product_id
from orders as a
left join trains b
on a.order_id=b.order_id

union all
-- 订单历史数据
select 
a.user_id,b.product_id
from orders as a
left join priors b
on a.order_id=b.order_id
) t
)
select user_id
, count(distinct product_id) pro_cnt
from user_pro_cnt_tmp
group by user_id
having pro_cnt >= 100
limit 15;

结果也是一样,而且两个方式的时间,差不多,用with 是 少了10s。

相关文章