分析

数据来源： 2015082818、2015082819

统计每日数据：        
日期          UV   PV   登录人数      游客人数  平均访问时长  二跳率  独立IP数
20150828    23     891    12            20            2s            ...    
20150829    231     212    209            233            3s            ...
....

** 离线分析
** 周期性统计:  当天自动执行统计分析前一天的数据,得到前一天的结果
** 统计分析的结果,一般会提供给公司产品运营、产品决策等部门
** 根据分析结果做出合理的商业决策和商业策略：推广|下线|吸引|加强|红包

指标含义：
UV：独立访客数
    ** 同一个用户访问多次会产生多条记录,这些只算一个访客，根据cookie生成
    ** count(distinct guid)
PV: 页面访问量page view
    ** 每个页面计数一次，包含重复的
    ** count(url)
登录人数：
    ** 登录网站访问的人数[会员]
    ** count(不为null的endUserId)
游客人数：
    ** 没有登录访问的人数
    ** count(为null的endUserId)
平均访问时长
    ** 每位访客在网站停留的时间: max(trackTime) - min(trackTime)
    ** 再取平均值
二跳率
    ** 每个session，平均浏览2个以上页面(pv>2)的用户数 / 用户总数(UV)
独立IP数
    ** 公网IP    
    ** 正常情况下,分析一天内的数据,同一个用户IP是不会变的
    ** count(distinct ip)

-----------------------------

1、创建分区表track_log

2、创建临时表1，统计每个session的常用指标

create table mydb.session_info_tmp1(
session_id string,
guid string,
user_id string,
pv string,
stay_time string,
min_trackTime string,
ip string,
provinceId string
)
row format delimited fields terminated by "\t";

--插入查询数据      
insert overwrite table mydb.session_info_tmp1
select
sessionid,
max(guid),        --根据cookie算出来的唯一用户ID，如：B6JNYPQMMXXRJWFTVJZGNPCDYM8QUD9MT6TY
max(endUserId),   --用户id号，登录后有值，如：150125290，否则为空字符串''
count(url),       --每个session对应的PV
max(unix_timestamp(tracktime))-min(unix_timestamp(tracktime)),
from_unixtime(min(unix_timestamp(tracktime))),    --session开始时间
max(ip),
max(provinceid)
from track_log where date='20150828'
group by sessionid;

查看
select * from mydb.session_info_tmp1

3、创建临时表2，此表在本例中没有任何作用
create table mydb.session_info_tmp2(
session_id string,
tracktime string,
trackerU string,
landing_url string,
landing_url_ref string
)
row format delimited fields terminated by "\t";

--插入查询数据      
insert overwrite table mydb.session_info_tmp2
select
sessionid,
tracktime,  --请求该url时的系统时间
trackeru,   --网盟用户id，如：1624169
url,
referer
from track_log where date='20150828';

4、创建会话信息表
    ** 是一个中间表
    ** 中间表的字段根据需求来决定

create table mydb.session_info(
session_id string,
guid string,
trackerU string,
landing_url string,
landing_url_ref string,
user_id string,
pv string,
stay_time string,
min_trackTime string,
ip string,
provinceId string
)
partitioned by (date string)
row format delimited fields terminated by "\t";

5、把两个临时表进行join,得出会话信息表数据
insert overwrite table session_info partition(date='20150828')
select
a.session_id,
a.guid,
b.trackerU,
b.landing_url,
b.landing_url_ref,
a.user_id,
a.pv,
a.stay_time,
a.min_trackTime,
a.ip,
a.provinceId
from session_info_tmp1 a join session_info_tmp2 b
on a.session_id=b.session_id and a.min_trackTime=b.tracktime;

6、基于会话信息表统计得出需求结果
日期  UV   PV  登录人数     游客人数  平均访问时长  二跳率  独立IP数

--创建表
create table mydb.rpt_info(
date string,
uv string,
pv string,
login_users string,
visit_users string,
avg_time string,
sec_ratio string,
ip_count string
)
row format delimited fields terminated by "\t";

--插入查询数据
insert overwrite table mydb.rpt_info
select
date,
count(distinct guid),
sum(pv),
count(case when user_id!= '' then user_id else null end),   --null不计数
count(case when user_id = '' then user_id else null end),
avg(stay_time),
count(distinct(case when pv>=2 then guid else null end))/count(distinct guid),
count(distinct ip)
from session_info where date='20150828'
group by date;



思考题：增加一个provinceid维度
    ** 统计每天每个省份这些相关指标的数据
  日期    省份   UV   PV  登录人数     游客人数  平均访问时长 二跳率  独立IP数
20150828  四川   10
20150828  上海   23
20150828  江苏   20

=========================================================

-- 配置更好的硬件，提高运算速度
普通交换机
    ** 网线连接服务器
    ** 常用，比较便宜，数据交换能力一般

光纤交换机
    ** 光纤连接服务器
    ** 比较贵，数据交换能强

    
Hive本质： MapReduce程序           
MapReduce特性：
    ** map --> reduce
    ** 通常情况下,瓶颈通常在网络IO、CPU、memory
    ** 最怕数据倾斜

数据倾斜：
    ** 大量的key被分配到同一个reduce,其他reduce很空闲
    ** 数据倾斜会造成数据热点
数据热点：
    ** 大量的数据集中到某一个点


Hive优化：
    1、explain查询执行计划
       hive (mydb)> explain select count(empno) from emp;
       
    2、大表拆小表    100G
        ** 减小单个job任务的执行时间
        36字段[原表]100G -->  6字段[子表]10G  
        
    3、合理使用分区表
        ** 选择最佳的分区维度
        ** 减小每天查询的数据总量
        
    4、合理设置表的格式和是否使用压缩
        ** 节省磁盘空间,提高查询效率
        ** orc+snappy
        
    5、是否开启中间结果集压缩(map输出压缩)
        ** 有效减小网络IO
        ** 把压力转移给了CPU和内存

    6、开启并行执行和JVM重用
        ** 有效的提高job任务的速度
        ** set hive.exec.parallel=true;
        
    7、合理设置map和reduce个数
        ** 默认一个block就是一个split,产生一个map
        ** Hive中reduce默认只有一个
        
        ** 控制map任务个数
            ** FileInputFormat.setMaxInputSplitSize(job, size);
            ** FileInputFormat.setMinInputSplitSize(job, size);
            
        ** 控制reduce任务个数    
            ** set mapreduce.job.reduces=<number>
            ** job.setNumReduceTasks(tasks);
        
    8、合理设置Join
        ** Join时小表一律放在左边：小表 join 大表
        ** 或者手动指定小表
        select /*+ MAPJOIN(time_dim) */ count(*) from time_dim join store_sales on (ss_sold_time_sk = t_time_sk)
             time_dim：    小表     --不超过25M
             store_sales： 大表     --10T
        ** 自动开启mapJoin转换
            如果表数据小于25M,会自动启用mapjoiin
            set hive.auto.convert.join=true ;
            
    9、避免数据倾斜
    Hive中常用来作为键(key)的情况
        group by 字段
        join on 连接字段
        count(distinct 字段)
        举例： group by session_id
               假设该表10000万行,其中session_id为null的有9000万行
        那么这9000万行数据，因为key是一样的(session_id=null),所以都会被分配给同一
        个reduce处理,这样就会发生了数据倾斜
        解决方案：
            ** 如果不影响统计结果,可以过滤掉
                group by session_id having session_id is not null
            ** 如果影响结果不能过滤,可以考虑给它赋值一个小范围的随机值rand()
               如：从1~5中取一个值，这样就被分为5个key

               
*** 死记硬背