大数据技术之高频面试题（七）：用户行为数据分析(上篇)

问题导读：
1、如何设计数仓分层架构表？
2、数据仓库有哪些层级？作用是什么？
3、如何设计用户活跃主题表？
4、如何设计用户新增主题表？

上一篇：大数据技术之高频面试题（六）：涉及技术Spark

第5章用户行为数据分析

5.1 数仓分层架构表

分层优点：复杂问题简单化、清晰数据结构(方便管理)、增加数据的复用性、隔离原始数据(解耦)

数仓中各层建的表都是外部表

5.2 埋点行为数据基本格式(基本字段)
公共字段：基本所有安卓手机都包含的字段
业务字段：埋点上报的字段，有具体的业务类型
下面就是一个示例，表示业务字段的上传。
行为数据启动日志/事件日志表关键字段:

{
"ap":"xxxxx",//项目数据来源 app pc
"cm": {  //公共字段
                "mid": "",  // (String) 设备唯一标识
        "uid": "",  // (String) 用户标识
        "vc": "1",  // (String) versionCode，程序版本号
        "vn": "1.0",  // (String) versionName，程序版本名
        "l": "zh",  // (String) 系统语言
        "sr": "",  // (String) 渠道号，应用从哪个渠道来的。
        "os": "7.1.1",  // (String) Android系统版本
        "ar": "CN",  // (String) 区域
        "md": "BBB100-1",  // (String) 手机型号
        "ba": "blackberry",  // (String) 手机品牌
        "sv": "V2.2.1",  // (String) sdkVersion
        "g": "",  // (String) gmail
        "hw": "1620x1080",  // (String) heightXwidth，屏幕宽高
        "t": "1506047606608",  // (String) 客户端日志产生时的时间
        "nw": "WIFI",  // (String) 网络模式
        "ln": 0,  // (double) lng经度
        "la": 0  // (double) lat 纬度
    },
"et":  [  //事件
            {
                "ett": "1506047605364",  //客户端事件产生时间
                "en": "display",  //事件名称  启动和事件日志是根据事件名称的不同
                "kv": {  //事件结果，以key-value形式自行定义
                    "goodsid": "236",
                    "action": "1",
                    "extend1": "1",
"place": "2",
"category": "75"
                }
            }
        ]
}
复制代码

根据事件标签的不同可以分成不同的日志表

5.3 项目经验总结
5.3.1 项目经验之元数据备份
元数据备份（重点，如数据损坏，可能整个集群无法运行，至少要保证每日零点之后备份到其它服务器两个复本）

5.3.2 日期处理函数
1）date_format函数（根据格式整理日期）
2）date_add、date_sub函数（加减日期）
3）next_day函数
4）last_day函数（求当月最后一天日期）
5）collect_set函数
6）get_json_object解析json函数
5.3.3 Union与Union all区别
1）union会将联合的结果集去重，效率较union all差
2）union all不会对结果集去重，所以效率高

5.3.4 Shell中单引号和双引号区别

1）在/home/hadoop/bin创建一个test.sh文件

[hadoop@node1 bin]$ vim test.sh 
在文件中添加如下内容
#!/bin/bash
do_date=$1

echo '$do_date'
echo "$do_date"
echo "'$do_date'"
echo '"$do_date"'
echo `date`
复制代码

2）查看执行结果

[hadoop@node1 bin]$ sh test.sh 2019-02-10
$do_date
2019-02-10
'2019-02-10'
"$do_date"
2019年 05月 02日 星期四 21:02:08 CST
复制代码

3）总结：
（1）单引号不取变量值
（2）双引号取变量值
（3）反引号`，执行引号中命令
（4）双引号内部嵌套单引号，取出变量值
（5）单引号内部嵌套双引号，不取出变量值

5.4 ods层
1）ods_start_log 启动日志表
只有一个字段 line（保存着json），按照日期dt分区，表的格式：lzo
2）ods_event_log 事件日志表（格式同启动日志表）
只有一个字段 line ，按照日期dt 分区，表的格式：lzo

5.5 dwd层
1）dwd_start_log 启动表
关键字段：mid_id，user_id，dt(分区字段，按照日期分区) （其实这是启动表和事件表的公共字段）
从ods_start_log中的line用get_json_object(line，'$.mid') mid_id的方式获取字段

5.5.1 自定义UDF/UDTF（项目中的应用）
自定义UDF函数（解析公共字段，一进一出）
自定义UDTF函数（解析具体事件字段，一进多出）
自定义UDF：继承UDF，重写evaluate方法
自定义UDTF：继承自GenericUDTF，重写3个方法：initialize(自定义输出的列名和类型)，process（将结果返回forward(result)），close
为什么要自定义UDF/UDTF，因为自定义函数，可以自己埋点Log打印日志，出错或者数据异常，方便调试。

5.5.2 事件日志基础明细表
dwd_base_event_log 事件日志基础明细表
1）关键字段：
公共字段：mid_id，user_id，dt(分区字段)以及event_name、event_json、server_time
2）从 ods_event_log的line 中用 UDF 获取公共字段和 server_time，用UDTF 获取 event_name , event_json 。

5.5.3 商品点击表
dwd_display_log 商品点击表

关键字段：公共字段 + 特有字段
从dwd_base_event_log中直接获取公共字段和server_time，从 dwd_base_event_log的 event_json中获取特有字段，where event_name = "display"
get_json_object(event_json,'$.kv.action') action

5.5.4 其他的具体事件明细表
类似
dwd_newsdetail_log 商品详情页表
dwd_loading_log 商品列表页表
dwd_ad_log 广告表
dwd_notification_log 消息通知表
dwd_active_foreground_log 用户前台活跃表
dwd_active_background_log 用户后台活跃表
dwd_comment_log 评论表
dwd_favorites_log 收藏表
dwd_praise_log 点赞表
dwd_error_log 错误日志表
从一张事件基础明细表dwd_base_event_log一共可以获得11张具体事件明细表

5.6 需求一：用户活跃主题
5.6.1 DWS层日活明细表

5.6.2 DWS层周活明细表

5.6.3 DWS层月活明细表

5.6.4 ADS层日周月活跃设备数表

5.7 需求二：用户新增主题
5.7.1 DWS层日新增明细表

5.7.2 ADS层每日新增设备数表