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

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

文章目录

• • 1、认识数据：u.data
  • 2、创建udata表，加载数据
  • 3、Hive执行顺序
  • 4、需求：得到某一个用户具体的评论时间。
  • 5、需求： 用户购买的商品数量大于100的有哪些用户？
  • • 方式一：
    • 方式二：引入 with关键字

---

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

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

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

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

---

---

数据链接：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。