这学期的实训课程用到了hive和hive里的数据库管理程序hql,hql的操作和一般的sql语言基本一致,在做大数据分析的一个需求时遇到需要用循环的情况,但是hql里没有循环的写法,所以用shell脚本来实现这个需求,记录如下。
数据定义:
分析行为日志user_log.csv,日志中的字段定义如下:
1. user_id | 买家id
2. item_id | 商品id
3. cat_id | 商品类别id
4. merchant_id | 卖家id
5. brand_id | 品牌id
6. month | 交易时间:月
7. day | 交易事件:日
8. action | 行为,取值范围{0,1,2,3},0表示点击,1表示加入购物车,2表示购买,3表示收藏
9. age_range | 买家年龄分段:1表示年龄<18,2表示年龄在[18,24],3表示年龄在[25,29],4表示年龄在[30,34],5表示年龄在[35,39],6表示年龄在[40,49],7和8表示年龄>=50,0和NULL则表示未知
10. gender | 性别:0表示女性,1表示男性,2和NULL表示未知
11. province| 省份名字
需求:
统计最近7天内连续三天活跃的用户数
hql查询代码(dws_uv_detail_day是已筛选出的日活跃用户表):
insert into table ads_continuity_uv_count
select
${hiveconf:t} dt,
concat(date_add(cast(${hiveconf:t} AS string),-7),'_',date_add(cast(${hiveconf:t} AS string),-1)),
count(user_id)
from
(
select user_id
from (
select
user_id,
count(diff)
from
(
select
user_id,
dt,
rank,
date_sub(dt,rank) as diff
from
(
select
user_id,
dt,
rank() over(partition by user_id order by dt) as rank
from dws_uv_detail_day
where dt<=date_add(cast(${hiveconf:t} AS string),-1)
and dt>=date_add(cast(${hiveconf:t} AS string),-7)
)t1
)t2
group by user_id,diff
having count(diff)>=3
)t3
group by user_id
)t4;
该代码只能查指定日期(${hiveconf:t}的位置为指定的日期)的数据,如果要查一年所有日期的数据需要用到shell脚本循环执行这段代码。
在服务器上用vi hello.sh建立shell脚本hello.sh,输入如下代码:
#!/bin/bash
first="2020-05-12"
second="2020-11-12"
while [ $first != $second ]
do
echo $first
hive -hivevar start_date=$first -f demo.sql
first=`date -d "-1 days ago ${first}" +%Y-%m-%d`
done
first是开始日期,second是结束日期。demo.sql存放要执行的sql语句。
demo.sql的代码如下:
use shopping;
set t="${start_date}";
insert into table ads_continuity_uv_count
select
${hiveconf:t} dt,
concat(date_add(cast(${hiveconf:t} AS string),-7),'_',date_add(cast(${hiveconf:t} AS string),-1)),
count(user_id)
from
(
select user_id
from (
select
user_id,
count(diff)
from
(
select
user_id,
dt,
rank,
date_sub(dt,rank) as diff
from
(
select
user_id,
dt,
rank() over(partition by user_id order by dt) as rank
from dws_uv_detail_day
where dt<=date_add(cast(${hiveconf:t} AS string),-1)
and dt>=date_add(cast(${hiveconf:t} AS string),-7)
)t1
)t2
group by user_id,diff
having count(diff)>=3
)t3
group by user_id
)t4;
其中t接受外部参数作为日期变量。