假如有一个web系统，每天生成以下日志文件：
2020年12月21日数据 192.228.33.6,hunter,2020-12-21 10:30:20,/a 192.228.33.7,hunter,2020-12-21 10:30:26,/b 192.228.33.6,jack,2020-12-21 10:30:27,/a 192.228.33.8,tom,2020-12-21 10:30:28,/b 192.228.33.9,rose,2020-12-21 10:30:30,/b 192.228.33.10,julia,2020-12-21 10:30:40,/c 2020年12月22日数据 192.228.33.22,hunter,2020-12-22 10:30:20,/a 192.228.33.18,jerry,2020-12-22 10:30:30,/b 192.228.33.26,jack,2020-12-22 10:30:40,/a 192.228.33.18,polo,2020-12-22 10:30:50,/b 192.228.33.39,nissan,2020-12-22 10:30:53,/b 192.228.33.39,nissan,2020-12-22 10:30:55,/a 192.228.33.39,nissan,2020-12-22 10:30:58,/c 192.228.33.20,ford,2020-12-22 10:30:54,/c 2020年12月23日数据 192.228.33.46,hunter,2020-12-23 10:30:21,/a 192.228.43.18,jerry,2020-12-23 10:30:22,/b 192.228.43.26,tom,2020-12-23 10:30:23,/a 192.228.53.18,bmw,2020-12-23 10:30:24,/b 192.228.63.39,benz,2020-12-23 10:30:25,/b 192.228.33.25,haval,2020-12-23 10:30:30,/c 192.228.33.10,julia,2020-12-23 10:30:40,/c
需求
1.建立一个表，来存储每天新增的数据(分区表)
2.统计每天的活跃用户(日活)（需要用户的ip，用户的账号，用户访问时间最早的一条url和时间）
3.统计每天的新增用户(日新)
实现
建表(存储数据的分区表)
create table t_web_log(ip string,uid string,access_time string,url string) partitioned by (day string) row format delimited fields terminated by ',';
导数据
load data local inpath '/root/hivetest/log.21' into table t_web_log partition (day='2020-12-21'); load data local inpath '/root/hivetest/log.22' into table t_web_log partition (day='2020-12-22'); load data local inpath '/root/hivetest/log.23' into table t_web_log partition (day='2020-12-23');
show partitions t_web_log;(查看分区表)
指标统计
每日活跃用户统计
1.建一个保存日活数据的表
create table t_user_active_day(ip string,uid string,first_access string,url string) partitioned by (day string);
2.从日志表中查出日活数据并插入日活数据表
插入21日数据 insert into table t_user_active_day partition(day='2020-12-21') select ip,uid,access_time,url from ( select ip,uid,access_time,url, row_number() over(partition by uid order by access_time) as rn from t_web_log where day='2020-12-21') tmp where rn=1; 插入22日数据 insert into table t_user_active_day partition(day='2020-12-22') select ip,uid,access_time,url from ( select ip,uid,access_time,url, row_number() over(partition by uid order by access_time) as rn from t_web_log where day='2020-12-22') tmp where rn=1; 插入23日数据 insert into table t_user_active_day partition(day='2020-12-23') select ip,uid,access_time,url from ( select ip,uid,access_time,url, row_number() over(partition by uid order by access_time) as rn from t_web_log where day='2020-12-23') tmp where rn=1;
每日新用户统计
思路——将当日活跃用户跟历史用户表关联，找出那些在历史用户表中尚不存在的用户
1.建历史用户表
create table t_user_history(uid string);
2.建一个存放新用户的表
create table t_user_new_day like t_user_active_day;
3.求出每日的新用户并把数据插入新用户表
21日新用户 insert into table t_user_new_day partition (day='2020-12-21') select ip,uid,first_access,url from ( select a.ip,a.uid,a.first_access,a.url,b.uid as b_uid from t_user_active_day a left join t_user_history b on a.uid=b.uid where a.day='2020-12-21') tmp where tmp.b_uid is null; 22日新用户 insert into table t_user_new_day partition (day='2020-12-22') select ip,uid,first_access,url from ( select a.ip,a.uid,a.first_access,a.url,b.uid as b_uid from t_user_active_day a left join t_user_history b on a.uid=b.uid where a.day='2020-12-22') tmp where tmp.b_uid is null; 23日新用户 insert into table t_user_new_day partition (day='2020-12-23') select ip,uid,first_access,url from ( select a.ip,a.uid,a.first_access,a.url,b.uid as b_uid from t_user_active_day a left join t_user_history b on a.uid=b.uid where a.day='2020-12-23') tmp where tmp.b_uid is null;
4.将每日的新用户插入历史表
21日数据 insert into table t_user_history select uid from t_user_new_day where day='2020-12-21'; 22日数据 insert into table t_user_history select uid from t_user_new_day where day='2020-12-22'; 23日数据 insert into table t_user_history select uid from t_user_new_day where day='2020-12-23';
加餐
清空表
truncate table t_user_active_day;
修改Linux系统时间
date -s '2020-12-20 00:20:00'
统计日新、日活的shell脚本
vi user_etl.sh
#!/bin/bash day_str=`date -d '-1 day'+'%Y-%m-%d'` echo "准备处理$day_str的数据......" hive_exec=/root/apps/hive-1.2.1/bin/hive HQL_user_active_day=" insert into table exercise.t_user_active_day partition(day=\"$day_str\") select ip,uid,access_time,url from ( select ip,uid,access_time,url, row_number() over(partition by uid order by access_time) as rn from t_web_log where day=\"$day_str\") tmp where rn=1; " $hive_exec "$HQL_user_active_day" HQL_user_new_day=" insert into table t_user_new_day partition (day=\"$day_str\") select ip,uid,first_access,url from ( select a.ip,a.uid,a.first_access,a.url,b.uid as b_uid from t_user_active_day a left join t_user_history b on a.uid=b.uid where a.day=\"$day_str\") tmp where tmp.b_uid is null; " $hive_exec "$HQL_user_new_day" HQL_new_to_history=" insert into table t_user_history select uid from t_user_new_day where day=\"$day_str\; " $hive_exec "$HQL_new_to_history"
运行：sh user_etl.sh
