hdfs加载数据到hive

hive强制删除数据库
    drop database applogsdb cascade;
    
修改虚拟机时间
    sudo date -s 2020-05-08
    sudo date -s 19:18:00

初始化hive数据
    bin/schematool -initSchema -dbType mysql

定时任务
    创建
        crontab -e
        例如:  * * * * * echo 123 > /home/test/test.txt
    展示
        crontab -l

从flume导到hdfs上的数据 需要json-serde-1.3.8-jar-with-dependencies.jar才能去导入到hive表中

hive导入创建表的sql文件
    ./hive -f applogs_create_table.sql 

定时加载hdfs数据到hive
    13 * * * * source /etc/profile;/opt/module/apache-hive-2.3.7-bin/bin/app_logs.sh

创建UDF工具函数
    create function getdaybegin AS 'com.bw.app.hive.DayBeginUDF';
    create function getweekbegin AS 'com.bw.app.hive.WeekBeginUDF';
    create function getmonthbegin AS 'com.bw.app.hive.MonthBeginUDF';
    create function formattime AS 'com.bw.app.hive.FormatTimeUDF';

活跃用户
select
count(distinct deviceid)
from ext_startup_logs
where appid = 'sdk34734'
and createdatms >= getdaybegin() and createdatms < getdaybegin(1);

新增用户
select count(0) from 
(
    select deviceId, min(create_time) min_create_time
     from ext_startup_logs 
    where app=xxx 
    group by deviceId
    having min_create_time =>2020-05-07 00:00:00 and min_create_time <2020-05-08 00:00:00
)  t;

过去一周内的每天的新增用户

select   formattime(mintime,'yyyy-mm-dd'), count(0) from
(
select deviceid，min(createdatms) mintime
from ext_startup_logs
where appid = 'sdk34734'
group by deviceid
having mintime >= getweekbegin(-1) and mintime < getweekbegin()
) t
group by formattime(mintime,'yyyy-mm-dd');