• 数据仓库 用户活跃主题


    ①对某一主题的分析,涉及到DWS数据服务层和ADS数据应用层。

    ②业务术语

    用户:用户以设备为判断标准,在移动统计中,每个独立设备认为是一个独立用户。Android系统根据IMEI号,IOS系统根据OpenUDID来标识一个独立用户,每部手机一个用户。

    活跃用户:打开应用的用户即为活跃用户,不考虑用户的使用情况。每天一台设备打开多次会被计一个活跃用户。

     

    周(月活跃用户):某个自然周(启动过应用的用户,该周(的多次启动只记一个活跃用户。

    ③用户活跃的数据来源于启动日志,具体来说,是DWD数据明细层的启动日志表数据。

     

     DWS层

    一 每日活跃设备明细

    建表语句。

    分析:

    ①最重要的就是设备id,日活的标志。

    ②因为是日活表,需按照日期(年月日)进行分区。

     

    drop table if exists dws_uv_detail_day;
    create external table dws_uv_detail_day
    (
        `mid_id` string COMMENT '设备唯一标识',
        `user_id` string COMMENT '用户标识', 
        `version_code` string COMMENT '程序版本号', 
        `version_name` string COMMENT '程序版本名', 
        `lang` string COMMENT '系统语言', 
        `source` string COMMENT '渠道号', 
        `os` string COMMENT '安卓系统版本', 
        `area` string COMMENT '区域', 
        `model` string COMMENT '手机型号', 
        `brand` string COMMENT '手机品牌', 
        `sdk_version` string COMMENT 'sdkVersion', 
        `gmail` string COMMENT 'gmail', 
        `height_width` string COMMENT '屏幕宽高',
        `app_time` string COMMENT '客户端日志产生时的时间',
        `network` string COMMENT '网络模式',
        `lng` string COMMENT '经度',
        `lat` string COMMENT '纬度'
    )
    partitioned by(dt string)
    stored as parquet
    location '/warehouse/gmall/dws/dws_uv_detail_day'
    ;

     

    导入数据。

    分析:

    ①日活表的数据来源于启动日志表,存在一个设备每天有多条的情况,需要按照设备id去重。

    ②与mysql语法不通,在hive语句中,非group by的字段必须放在聚合函数里。

    ③数据来源于DWD层的启动日志表dwd_start_log。

    insert overwrite table dws_uv_detail_day 
    partition(dt='2019-02-10')
    select  
        mid_id,
        concat_ws('|', collect_set(user_id)) user_id,
        concat_ws('|', collect_set(version_code)) version_code,
        concat_ws('|', collect_set(version_name)) version_name,
        concat_ws('|', collect_set(lang))lang,
        concat_ws('|', collect_set(source)) source,
        concat_ws('|', collect_set(os)) os,
        concat_ws('|', collect_set(area)) area, 
        concat_ws('|', collect_set(model)) model,
        concat_ws('|', collect_set(brand)) brand,
        concat_ws('|', collect_set(sdk_version)) sdk_version,
        concat_ws('|', collect_set(gmail)) gmail,
        concat_ws('|', collect_set(height_width)) height_width,
        concat_ws('|', collect_set(app_time)) app_time,
        concat_ws('|', collect_set(network)) network,
        concat_ws('|', collect_set(lng)) lng,
        concat_ws('|', collect_set(lat)) lat
    from dwd_start_log
    where dt='2019-02-10'
    group by mid_id;

    二 每周活跃设备明细

    建表语句

    分析:

    ①字段与日活表相比,增加了每周的开头结尾,即周一和周日的日期,方便后续的展示。

    ②分区设置为每周的周一和周日的日期。

    drop table if exists dws_uv_detail_wk;
    create external table dws_uv_detail_wk( 
        `mid_id` string COMMENT '设备唯一标识',
        `user_id` string COMMENT '用户标识', 
        `version_code` string COMMENT '程序版本号', 
        `version_name` string COMMENT '程序版本名', 
        `lang` string COMMENT '系统语言', 
        `source` string COMMENT '渠道号', 
        `os` string COMMENT '安卓系统版本', 
        `area` string COMMENT '区域', 
        `model` string COMMENT '手机型号', 
        `brand` string COMMENT '手机品牌', 
        `sdk_version` string COMMENT 'sdkVersion', 
        `gmail` string COMMENT 'gmail', 
        `height_width` string COMMENT '屏幕宽高',
        `app_time` string COMMENT '客户端日志产生时的时间',
        `network` string COMMENT '网络模式',
        `lng` string COMMENT '经度',
        `lat` string COMMENT '纬度',
        `monday_date` string COMMENT '周一日期',
        `sunday_date` string COMMENT  '周日日期' 
    ) COMMENT '活跃用户按周明细'
    PARTITIONED BY (`wk_dt` string)
    stored as parquet
    location '/warehouse/gmall/dws/dws_uv_detail_wk/'
    ;

    导入数据

    分析:

    ①周活的数据,不是直接来源于DWD层,而是来源于和自己同在DWS层的日活表,日活表已经对启动日志进行了日内去重,数据量更小。周活表需要对日活表进行周内去重。

    ②因为分区的格式比较复杂,难以事先给定,需要计算后,放在最后一个字段,以给分区赋值。所以开启了非严格模式,进行动态分区。

    ③在对本周周日的计算上。先算下个星期一,因为这个星期一必定在下周,所以减1就是本周周日。

    ④where条件限定了只有在本周区间内的记录才会被插入进这个分区。

    set hive.exec.dynamic.partition.mode=nonstrict;
    
    insert overwrite table dws_uv_detail_wk partition(wk_dt)
    select  
        mid_id,
        concat_ws('|', collect_set(user_id)) user_id,
        concat_ws('|', collect_set(version_code)) version_code,
        concat_ws('|', collect_set(version_name)) version_name,
        concat_ws('|', collect_set(lang)) lang,
        concat_ws('|', collect_set(source)) source,
        concat_ws('|', collect_set(os)) os,
        concat_ws('|', collect_set(area)) area, 
        concat_ws('|', collect_set(model)) model,
        concat_ws('|', collect_set(brand)) brand,
        concat_ws('|', collect_set(sdk_version)) sdk_version,
        concat_ws('|', collect_set(gmail)) gmail,
        concat_ws('|', collect_set(height_width)) height_width,
        concat_ws('|', collect_set(app_time)) app_time,
        concat_ws('|', collect_set(network)) network,
        concat_ws('|', collect_set(lng)) lng,
        concat_ws('|', collect_set(lat)) lat,
        date_add(next_day('2019-02-10','MO'),-7),
        date_add(next_day('2019-02-10','MO'),-1),
        concat(date_add( next_day('2019-02-10','MO'),-7), '_' , date_add(next_day('2019-02-10','MO'),-1) 
    )
    from dws_uv_detail_day 
    where dt>=date_add(next_day('2019-02-10','MO'),-7) and dt<=date_add(next_day('2019-02-10','MO'),-1) 
    group by mid_id;

    三 每月活跃设备明细

    建表语句

    分析:

    与日活表的字段完全一样,只是需要根据月份来分区。

    drop table if exists dws_uv_detail_mn;
    
    create external table dws_uv_detail_mn( 
        `mid_id` string COMMENT '设备唯一标识',
        `user_id` string COMMENT '用户标识', 
        `version_code` string COMMENT '程序版本号', 
        `version_name` string COMMENT '程序版本名', 
        `lang` string COMMENT '系统语言', 
        `source` string COMMENT '渠道号', 
        `os` string COMMENT '安卓系统版本', 
        `area` string COMMENT '区域', 
        `model` string COMMENT '手机型号', 
        `brand` string COMMENT '手机品牌', 
        `sdk_version` string COMMENT 'sdkVersion', 
        `gmail` string COMMENT 'gmail', 
        `height_width` string COMMENT '屏幕宽高',
        `app_time` string COMMENT '客户端日志产生时的时间',
        `network` string COMMENT '网络模式',
        `lng` string COMMENT '经度',
        `lat` string COMMENT '纬度'
    ) COMMENT '活跃用户按月明细'
    PARTITIONED BY (`mn` string)
    stored as parquet
    location '/warehouse/gmall/dws/dws_uv_detail_mn/'
    ;

    导入数据

    set hive.exec.dynamic.partition.mode=nonstrict;
    
    insert overwrite table dws_uv_detail_mn partition(mn)
    select  
        mid_id,
        concat_ws('|', collect_set(user_id)) user_id,
        concat_ws('|', collect_set(version_code)) version_code,
        concat_ws('|', collect_set(version_name)) version_name,
        concat_ws('|', collect_set(lang)) lang,
        concat_ws('|', collect_set(source)) source,
        concat_ws('|', collect_set(os)) os,
        concat_ws('|', collect_set(area)) area, 
        concat_ws('|', collect_set(model)) model,
        concat_ws('|', collect_set(brand)) brand,
        concat_ws('|', collect_set(sdk_version)) sdk_version,
        concat_ws('|', collect_set(gmail)) gmail,
        concat_ws('|', collect_set(height_width)) height_width,
        concat_ws('|', collect_set(app_time)) app_time,
        concat_ws('|', collect_set(network)) network,
        concat_ws('|', collect_set(lng)) lng,
        concat_ws('|', collect_set(lat)) lat,
        date_format('2019-02-10','yyyy-MM')
    from dws_uv_detail_day
    where date_format(dt,'yyyy-MM') = date_format('2019-02-10','yyyy-MM')
    group by mid_id;

     最后,需要将导入数据的部分做成灵活的脚本,略。

    ADS层

    DWS层准备了用户活跃主题所需的数据,但这些数据还不能直接展示在页面上,还需要ADS层的处理。ADS层的要求是,给出当日、当周、当月的活跃设备数。

    建表语句

    分析:ADS层是统计后的结果,可直接进行展示,数据量较小,不需要分区。

    drop table if exists ads_uv_count;
    create external table ads_uv_count( 
        `dt` string COMMENT '统计日期',
        `day_count` bigint COMMENT '当日用户数量',
        `wk_count`  bigint COMMENT '当周用户数量',
        `mn_count`  bigint COMMENT '当月用户数量',
        `is_weekend` string COMMENT 'Y,N是否是周末,用于得到本周最终结果',
        `is_monthend` string COMMENT 'Y,N是否是月末,用于得到本月最终结果' 
    ) COMMENT '活跃设备数'
    row format delimited fields terminated by '	'
    location '/warehouse/gmall/ads/ads_uv_count/'
    ;

    数据导入脚本。

    分析:

    ①由于有了数仓前几层的处理,这里只需要count即可,注意日期函数的灵活运用。

    ②由于ADS层用户活跃表并不是分区表,因此在导入数据是,不需要指定partition,更不能写成insert overwrite。

    #!/bin/bash
    
    # 定义变量方便修改
    APP=gmall
    hive=/opt/module/hive/bin/hive
    
    # 如果是输入的日期按照取输入日期;如果没输入日期取当前时间的前一天
    if [ -n "$1" ] ;then
            do_date=$1
    else 
            do_date=`date -d "-1 day" +%F`  
    fi 
    
    sql="
    
    insert into table "$APP".ads_uv_count 
    select  
      '$do_date' dt,
       daycount.ct,
       wkcount.ct,
       mncount.ct,
       if(date_add(next_day('$do_date','MO'),-1)='$do_date','Y','N') ,
       if(last_day('$do_date')='$do_date','Y','N') 
    from 
    (
       select  
          '$do_date' dt,
           count(*) ct
       from "$APP".dws_uv_detail_day
       where dt='$do_date'  
    )daycount   join 
    ( 
       select  
         '$do_date' dt,
         count (*) ct
       from "$APP".dws_uv_detail_wk
       where wk_dt=concat(date_add(next_day('$do_date','MO'),-7),'_' ,date_add(next_day('$do_date','MO'),-1) )
    )  wkcount  on daycount.dt=wkcount.dt
    join 
    ( 
       select  
         '$do_date' dt,
         count (*) ct
       from "$APP".dws_uv_detail_mn
       where mn=date_format('$do_date','yyyy-MM')  
    )mncount on daycount.dt=mncount.dt;
    "
    
    $hive -e "$sql"

     

     

  • 相关阅读:
    Windows下查看dll被哪个进程调用
    mfc笔记之string,wstring,CString
    查看用户主目录
    我看Windows 8 RT
    C# 性能优化之斤斤计较篇 一
    曾经生活过的城市
    大型.NET项目的目录、编译和版本管理实践 四
    大型.NET项目的目录、编译和版本管理实践 五
    大型.NET项目的目录、编译和版本管理实践 二
    大型.NET项目的目录、编译和版本管理实践 一
  • 原文地址:https://www.cnblogs.com/noyouth/p/13195868.html
Copyright © 2020-2023  润新知