用脚本执行hive的hql代码

这学期的实训课程用到了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接受外部参数作为日期变量。

完成需求的hql代码参考自http://www.xunyucs.com/2020/04/05/%E3%80%90%E6%95%B0%E4%BB%93%E9%A1%B9%E7%9B%AE%E3%80%91%E4%B8%8D%E7%A0%B4%E6%A5%BC%E5%85%B0%E7%BB%88%E4%B8%8D%E5%9B%9E-%E5%B8%B8%E7%94%A8%E5%87%BD%E6%95%B0%E6%80%BB%E7%BB%93/