2021-08-01

1.DBeaver工具的使用

2.向ods层导入数据

2.1创建启动日志表ods_start_log

hive (gmall)> 

drop table if exists ods_start_log;

CREATE EXTERNAL TABLE ods_start_log (`line` string)

PARTITIONED BY (`dt` string)

STORED AS

 INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'

 OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

LOCATION '/warehouse/gmall/ods/ods_start_log';

2.1.1 加载数据

hive (gmall)> 

load data inpath '/origin_data/gmall/log/topic_start/2021-07-24' into table gmall.ods_start_log partition(dt='2021-07-24');

注意：时间格式都配置成YYYY-MM-DD格式，这是Hive默认支持的时间格式

2.1.2查看是否加载成功

hive (gmall)> select * from ods_start_log limit 2;

2.1.3为lzo压缩文件创建索引

hadoop jar /opt/module/hadoop-2.7.2/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer /warehouse/gmall/ods/ods_start_log/dt=2021-07-24

2.2创建事件日志表ods_event_log

hive (gmall)> 

drop table if exists ods_event_log;

CREATE EXTERNAL TABLE ods_event_log(`line` string)

PARTITIONED BY (`dt` string)

STORED AS

 INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'

 OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

LOCATION '/warehouse/gmall/ods/ods_event_log';

2.2.1加载数据

hive (gmall)> 

load data inpath '/origin_data/gmall/log/topic_event/2021-07-24' into table gmall.ods_event_log partition(dt='2021-07-24');

时间格式都配置成YYYY-MM-DD格式，这是Hive默认支持的时间格式

2.2.2查看是否加载成功

hive (gmall)> select * from ods_event_log limit 2;

2.2.3为lzo压缩文件创建索引

hadoop jar /opt/module/hadoop-2.7.2/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer /warehouse/gmall/ods/ods_event_log/dt=2021-07-24

3编写ods层启动脚本

#!/bin/bash
#向ods的两个表中导入每天的数据，为数据创建LZO索引
#接受要导入数据的日期参数,-n可以判断后面的参数是否为赋值，如果赋值，返回true,否则返回false
#为判断的变量名加双引号
if [ -n "$1" ]
then
	 do_date=$1
else
	do_date=$(date -d yesterday +%F)
fi

echo ===日志日期为$do_date===

APP=gmall
sql="
load data inpath '/origin_data/gmall/log/topic_start/$do_date' into table $APP.ods_start_log partition(dt='$do_date');

load data inpath '/origin_data/gmall/log/topic_event/$do_date' into table $APP.ods_event_log partition(dt='$do_date');
"
hive  -e "$sql"

hadoop jar /opt/module/hadoop-2.7.2/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer /warehouse/gmall/ods/ods_start_log/dt=$do_date

hadoop jar /opt/module/hadoop-2.7.2/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer /warehouse/gmall/ods/ods_event_log/dt=$do_date

4 -n参数的介绍

-n可以判断后面的参数是否为赋值，如果赋值，返回true,否则返回false

为判断的变量名加双引号

5 get_json_object函数介绍

get_json_object

get_json_object(json_txt, path) - Extract a json object from path 从json对象中抽取指定的内容
Extract json object from a json string based on json path specified,
and return json string of the extracted json object.
It will return null if the input json string is invalid.

需要指定要抽取的内容的path路径！
如果函数传入的数据不是JSON,此时会返回Null！

A limited version of JSONPath supported:
$ : Root object 代表整个JSON对象
. : Child operator 代表获取JSON对象中子元素(属性)的操作符
[] : Subscript operator for array 获取JSONArray中的某个元素

{“name”:“jack”,“age”:18,“parents”:[{“name”:“oldjack”,“age”:48},{“name”:“jackmom”,“age”:49}]}

获取18
select get_json_object(’{“name”:“jack”,“age”:18,“parents”:
[{“name”:“oldjack”,“age”:48},{“name”:“jackmom”,“age”:48}]}’,’$.age’)

获取49
select get_json_object(’{“name”:“jack”,“age”:18,“parents”:
[{“name”:“oldjack”,“age”:48},{“name”:“jackmom”,“age”:49}]}’,’$.parents[1].age’)

6 函数的需求分析

1581472611770|{“cm”:{“ln”:"-45.5",“sv”:“V2.7.9”,“os”:“8.0.7”,“g”:“2E0719R3@gmail.com”,“mid”:“9”,“nw”:“3G”,“l”:“pt”,“vc”:“13”,“hw”:“640*1136”,“ar”:“MX”,“uid”:“9”,“t”:“1581440366103”,“la”:“24.4”,“md”:“HTC-5”,“vn”:“1.2.3”,“ba”:“HTC”,“sr”:“F”},“ap”:“app”,“et”:[{“ett”:“1581376543852”,“en”:“display”,“kv”:{“goodsid”:“3”,“action”:“1”,“extend1”:“1”,“place”:“3”,“category”:“78”}},{“ett”:“1581440821593”,“en”:“loading”,“kv”:{“extend2”:"",“loading_time”:“10”,“action”:“3”,“extend1”:"",“type”:“2”,“type1”:“433”,“loading_way”:“1”}},{“ett”:“1581436003510”,“en”:“ad”,“kv”:{“entry”:“3”,“show_style”:“4”,“action”:“3”,“detail”:“325”,“source”:“4”,“behavior”:“1”,“content”:“2”,“newstype”:“1”}}]

编写UDF函数（1进1出），a
①取ap属性的值app: a(jsonstr,‘ap’)=app
②取json的1581472611770： a(jsonstr,‘ts’)=1581472611770
③取cm公共部分中的属性，例如：
取cm中的ln的值： a(jsonstr,‘ln’)=-45.5
取cm中的sv的值： a(jsonstr,‘sv’)=V2.7.9

编写UDTF函数（1进多出）,b,返回值为2列N行
b(’[{},{}]’)的返回值如下：
eventname,eventjson
display,{“ett”:“1581376543852”,“en”:“display”,“kv”:{“goodsid”:“3”,“action”:“1”,“extend1”:“1”,“place”:“3”,“category”:“78”}
loading,{“ett”:“1581440821593”,“en”:“loading”,“kv”:{“extend2”:"",“loading_time”:“10”,“action”:“3”,“extend1”:"",“type”:“2”,“type1”:“433”,“loading_way”:“1”}}
ad,{“ett”:“1581436003510”,“en”:“ad”,“kv”:{“entry”:“3”,“show_style”:“4”,“action”:“3”,“detail”:“325”,“source”:“4”,“behavior”:“1”,“content”:“2”,“newstype”:“1”}}

create function base_analizer as ‘com.atguigu.udf.MyUDF’
create function flat_analizer as ‘com.atguigu.udtf.MyUDTF’

7 编写UDF和UDTF函数

8 函数的测试

找一条数据

vim hivetest

hive -d a=$(cat hivetest)

set a;

create function flat_analizer as ‘com.atguigu.udtf.MyUDTF’;

select base_analizer(’$(a)’,‘ts’);

select base_analizer(’${a}’,‘et’);

select flat_analizer(base_analizer(’${a}’,‘et’));

9 解析事件日志基础明细表

9.1建表

9.2 编写dwd_base_event_log脚本

10 编写向事件 表导入数据的脚本

11 DWD层事件表获取

11.1 商品点击表

11.1.1 建表语句

hive (gmall)> 
drop table if exists dwd_newsdetail_log;
CREATE EXTERNAL TABLE dwd_newsdetail_log(
`mid_id` string,
`user_id` string, 
`version_code` string, 
`version_name` string, 
`lang` string, 
`source` string, 
`os` string, 
`area` string, 
`model` string,
`brand` string, 
`sdk_version` string, 
`gmail` string, 
`height_width` string, 
`app_time` string,  
`network` string, 
`lng` string, 
`lat` string, 
`entry` string,
`action` string,
`goodsid` string,
`showtype` string,
`news_staytime` string,
`loading_time` string,
`type1` string,
`category` string,
`server_time` string)
PARTITIONED BY (dt string)
stored as parquet
location '/warehouse/gmall/dwd/dwd_newsdetail_log/'
TBLPROPERTIES('parquet.compression'='lzo');

11.1.2 导入数据

hive (gmall)> 
set hive.exec.dynamic.partition.mode=nonstrict;

insert overwrite table dwd_newsdetail_log
PARTITION (dt='2021-07-24')
select 
mid_id,
user_id,
version_code,
version_name,
lang,
source,
os,
area,
model,
brand,
sdk_version,
gmail,
height_width,
app_time,
network,
lng,
lat,
get_json_object(event_json,'$.kv.entry') entry,
get_json_object(event_json,'$.kv.action') action,
get_json_object(event_json,'$.kv.goodsid') goodsid,
get_json_object(event_json,'$.kv.showtype') showtype,
get_json_object(event_json,'$.kv.news_staytime') news_staytime,
get_json_object(event_json,'$.kv.loading_time') loading_time,
get_json_object(event_json,'$.kv.type1') type1,
get_json_object(event_json,'$.kv.category') category,
server_time
from dwd_base_event_log
where dt='2021-07-29' and event_name='newsdetail';

11.2 商品列表页表

11.2.1 建表语句

11.2.2 导入数据

11.3 商品详情页表

11.3.1 建表语句

11.3.2 导入数据

11.4 广告表

11.4.1 建表语句

11.4.2 导入数据

11.5 消息通知表

11.5.1 建表语句

11.5.2 导入数据

11.6 用户前台活跃表

11.6.1 建表语句

11.6.2 导入数据

11.7 用户后台活跃表

11.7.1 建表语句

11.7.2 导入数据

11.8 评论表

11.8.1 建表语句

11.8.2 导入数据

11.9 收藏表

11.9.1 建表语句

11.9.2 导入数据

11.10 点赞表

11.10.1 建表语句

11.10.2 导入数据

11.11 错误日志表

11.11.1 建表语句

11.11.2 导入数据

11.12 DWD层事件表加载数据脚本

#!/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="
set hive.exec.dynamic.partition.mode=nonstrict;

insert overwrite table "$APP".dwd_display_log
PARTITION (dt='$do_date')
select 
	mid_id,
	user_id,
	version_code,
	version_name,
	lang,
	source,
	os,
	area,
	model,
	brand,
	sdk_version,
	gmail,
	height_width,
	app_time,
	network,
	lng,
	lat,
	get_json_object(event_json,'$.kv.action') action,
	get_json_object(event_json,'$.kv.goodsid') goodsid,
	get_json_object(event_json,'$.kv.place') place,
	get_json_object(event_json,'$.kv.extend1') extend1,
	get_json_object(event_json,'$.kv.category') category,
	server_time
from "$APP".dwd_base_event_log 
where dt='$do_date' and event_name='display';


insert overwrite table "$APP".dwd_newsdetail_log
PARTITION (dt='$do_date')
select 
	mid_id,
	user_id,
	version_code,
	version_name,
	lang,
	source,
	os,
	area,
	model,
	brand,
	sdk_version,
	gmail,
	height_width,
	app_time,
	network,
	lng,
	lat,
	get_json_object(event_json,'$.kv.entry') entry,
	get_json_object(event_json,'$.kv.action') action,
	get_json_object(event_json,'$.kv.goodsid') goodsid,
	get_json_object(event_json,'$.kv.showtype') showtype,
	get_json_object(event_json,'$.kv.news_staytime') news_staytime,
	get_json_object(event_json,'$.kv.loading_time') loading_time,
	get_json_object(event_json,'$.kv.type1') type1,
	get_json_object(event_json,'$.kv.category') category,
	server_time
from "$APP".dwd_base_event_log 
where dt='$do_date' and event_name='newsdetail';


insert overwrite table "$APP".dwd_loading_log
PARTITION (dt='$do_date')
select 
	mid_id,
	user_id,
	version_code,
	version_name,
	lang,
	source,
	os,
	area,
	model,
	brand,
	sdk_version,
	gmail,
	height_width,
	app_time,
	network,
	lng,
	lat,
	get_json_object(event_json,'$.kv.action') action,
	get_json_object(event_json,'$.kv.loading_time') loading_time,
	get_json_object(event_json,'$.kv.loading_way') loading_way,
	get_json_object(event_json,'$.kv.extend1') extend1,
	get_json_object(event_json,'$.kv.extend2') extend2,
	get_json_object(event_json,'$.kv.type') type,
	get_json_object(event_json,'$.kv.type1') type1,
	server_time
from "$APP".dwd_base_event_log 
where dt='$do_date' and event_name='loading';


insert overwrite table "$APP".dwd_ad_log
PARTITION (dt='$do_date')
select 
	mid_id,
	user_id,
	version_code,
	version_name,
	lang,
	source,
	os,
	area,
	model,
	brand,
	sdk_version,
	gmail,
	height_width,
	app_time,
	network,
	lng,
	lat,
	get_json_object(event_json,'$.kv.entry') entry,
	get_json_object(event_json,'$.kv.action') action,
	get_json_object(event_json,'$.kv.content') content,
	get_json_object(event_json,'$.kv.detail') detail,
	get_json_object(event_json,'$.kv.source') ad_source,
	get_json_object(event_json,'$.kv.behavior') behavior,
	get_json_object(event_json,'$.kv.newstype') newstype,
	get_json_object(event_json,'$.kv.show_style') show_style,
	server_time
from "$APP".dwd_base_event_log 
where dt='$do_date' and event_name='ad';


insert overwrite table "$APP".dwd_notification_log
PARTITION (dt='$do_date')
select 
	mid_id,
	user_id,
	version_code,
	version_name,
	lang,
	source,
	os,
	area,
	model,
	brand,
	sdk_version,
	gmail,
	height_width,
	app_time,
	network,
	lng,
	lat,
	get_json_object(event_json,'$.kv.action') action,
	get_json_object(event_json,'$.kv.noti_type') noti_type,
	get_json_object(event_json,'$.kv.ap_time') ap_time,
	get_json_object(event_json,'$.kv.content') content,
	server_time
from "$APP".dwd_base_event_log 
where dt='$do_date' and event_name='notification';


insert overwrite table "$APP".dwd_active_foreground_log
PARTITION (dt='$do_date')
select 
	mid_id,
	user_id,
	version_code,
	version_name,
	lang,
	source,
	os,
	area,
	model,
	brand,
	sdk_version,
	gmail,
	height_width,
	app_time,
	network,
	lng,
	lat,
get_json_object(event_json,'$.kv.push_id') push_id,
get_json_object(event_json,'$.kv.access') access,
	server_time
from "$APP".dwd_base_event_log 
where dt='$do_date' and event_name='active_foreground';


insert overwrite table "$APP".dwd_active_background_log
PARTITION (dt='$do_date')
select 
	mid_id,
	user_id,
	version_code,
	version_name,
	lang,
	source,
	os,
	area,
	model,
	brand,
	sdk_version,
	gmail,
	height_width,
	app_time,
	network,
	lng,
	lat,
	get_json_object(event_json,'$.kv.active_source') active_source,
	server_time
from "$APP".dwd_base_event_log 
where dt='$do_date' and event_name='active_background';


insert overwrite table "$APP".dwd_comment_log
PARTITION (dt='$do_date')
select 
	mid_id,
	user_id,
	version_code,
	version_name,
	lang,
	source,
	os,
	area,
	model,
	brand,
	sdk_version,
	gmail,
	height_width,
	app_time,
	network,
	lng,
	lat,
	get_json_object(event_json,'$.kv.comment_id') comment_id,
	get_json_object(event_json,'$.kv.userid') userid,
	get_json_object(event_json,'$.kv.p_comment_id') p_comment_id,
	get_json_object(event_json,'$.kv.content') content,
	get_json_object(event_json,'$.kv.addtime') addtime,
	get_json_object(event_json,'$.kv.other_id') other_id,
	get_json_object(event_json,'$.kv.praise_count') praise_count,
	get_json_object(event_json,'$.kv.reply_count') reply_count,
	server_time
from "$APP".dwd_base_event_log 
where dt='$do_date' and event_name='comment';


insert overwrite table "$APP".dwd_favorites_log
PARTITION (dt='$do_date')
select 
	mid_id,
	user_id,
	version_code,
	version_name,
	lang,
	source,
	os,
	area,
	model,
	brand,
	sdk_version,
	gmail,
	height_width,
	app_time,
	network,
	lng,
	lat,
	get_json_object(event_json,'$.kv.id') id,
	get_json_object(event_json,'$.kv.course_id') course_id,
	get_json_object(event_json,'$.kv.userid') userid,
	get_json_object(event_json,'$.kv.add_time') add_time,
	server_time
from "$APP".dwd_base_event_log 
where dt='$do_date' and event_name='favorites';


insert overwrite table "$APP".dwd_praise_log
PARTITION (dt='$do_date')
select 
	mid_id,
	user_id,
	version_code,
	version_name,
	lang,
	source,
	os,
	area,
	model,
	brand,
	sdk_version,
	gmail,
	height_width,
	app_time,
	network,
	lng,
	lat,
	get_json_object(event_json,'$.kv.id') id,
	get_json_object(event_json,'$.kv.userid') userid,
	get_json_object(event_json,'$.kv.target_id') target_id,
	get_json_object(event_json,'$.kv.type') type,
	get_json_object(event_json,'$.kv.add_time') add_time,
	server_time
from "$APP".dwd_base_event_log 
where dt='$do_date' and event_name='praise';


insert overwrite table "$APP".dwd_error_log
PARTITION (dt='$do_date')
select 
	mid_id,
	user_id,
	version_code,
	version_name,
	lang,
	source,
	os,
	area,
	model,
	brand,
	sdk_version,
	gmail,
	height_width,
	app_time,
	network,
	lng,
	lat,
	get_json_object(event_json,'$.kv.errorBrief') errorBrief,
	get_json_object(event_json,'$.kv.errorDetail') errorDetail,
	server_time
from "$APP".dwd_base_event_log 
where dt='$do_date' and event_name='error';
"

$hive -e "$sql"

11.12.1 增加脚本权限

chmod 777 dwd_event_log.sh

12 业务知识准备

12.1 业务术语

1.用户
用户以设备为判断标准，在移动统计中，每个独立设备认为是一个独立用户。Android系统根据IMEI号，IOS系统根据OpenUDID来标识一个独立用户，每部手机一个用户。
2.新增用户
首次联网使用应用的用户。如果一个用户首次打开某APP，那这个用户定义为新增用户；卸载再安装的设备，不会被算作一次新增。新增用户包括日新增用户、周新增用户、月新增用户。
3.活跃用户
打开应用的用户即为活跃用户，不考虑用户的使用情况。每天一台设备打开多次会被计为一个活跃用户。
4.周（月）活跃用户
某个自然周（月）内启动过应用的用户，该周（月）内的多次启动只记一个活跃用户。
5.月活跃率
月活跃用户与截止到该月累计的用户总和之间的比例。
6.沉默用户
用户仅在安装当天（次日）启动一次，后续时间无再启动行为。该指标可以反映新增用户质量和用户与APP的匹配程度。
7.版本分布
不同版本的周内各天新增用户数，活跃用户数和启动次数。利于判断APP各个版本之间的优劣和用户行为习惯。
8.本周回流用户
上周未启动过应用，本周启动了应用的用户。
9.连续n周活跃用户
连续n周，每周至少启动一次。
10.忠诚用户
连续活跃5周以上的用户
11.连续活跃用户
连续2周及以上活跃的用户
12.近期流失用户
连续n(2<= n <= 4)周没有启动应用的用户。（第n+1周没有启动过）
13.留存用户
某段时间内的新增用户，经过一段时间后，仍然使用应用的被认作是留存用户；这部分用户占当时新增用户的比例即是留存率。
例如，5月份新增用户200，这200人在6月份启动过应用的有100人，7月份启动过应用的有80人，8月份启动过应用的有50人；则5月份新增用户一个月后的留存率是50%，二个月后的留存率是40%，三个月后的留存率是25%。
14.用户新鲜度
每天启动应用的新老用户比例，即新增用户数占活跃用户数的比例。
15.单次使用时长
每次启动使用的时间长度。
16.日使用时长
累计一天内的使用时间长度。
17.启动次数计算标准
IOS平台应用退到后台就算一次独立的启动；Android平台我们规定，两次启动之间的间隔小于30秒，被计算一次启动。用户在使用过程中，若因收发短信或接电话等退出应用30秒又再次返回应用中，那这两次行为应该是延续而非独立的，所以可以被算作一次使用行为，即一次启动。业内大多使用30秒这个标准，但用户还是可以自定义此时间间隔。

12.2 系统函数

12.2.1 collect_set函数

12.2.2 日期处理函数（datediff）

13 用户活跃主题

13.1 DWS层

13.1.1 每日活跃设备明细

13.1.2 每周活跃设备明细

13.1.3 每月活跃设备明细

13.1.4 DWS层加载数据脚本

#!/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="
  set hive.exec.dynamic.partition.mode=nonstrict;

  insert overwrite table "$APP".dws_uv_detail_day partition(dt='$do_date')
  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 "$APP".dwd_start_log
  where dt='$do_date'  
  group by mid_id;


  insert overwrite table "$APP".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('$do_date','MO'),-7),
    date_add(next_day('$do_date','MO'),-1),
    concat(date_add( next_day('$do_date','MO'),-7), '_' , date_add(next_day('$do_date','MO'),-1) 
  )
  from "$APP".dws_uv_detail_day
  where dt>=date_add(next_day('$do_date','MO'),-7) and dt<=date_add(next_day('$do_date','MO'),-1) 
  group by mid_id; 


  insert overwrite table "$APP".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('$do_date','yyyy-MM')
  from "$APP".dws_uv_detail_day
  where date_format(dt,'yyyy-MM') = date_format('$do_date','yyyy-MM')   
  group by mid_id;
"

$hive -e "$sql"

13.2 ADS层

13.2.1 活跃设备数

13.2.2 ADS层加载数据脚本

14 用户新增主题

14.1 DWS层（每日新增设备明细表）

14.2 ADS层（每日新增设备表）

15 用户留存主题

15.1 需求目标

15.2 DWS层

15.2.1 DWS层（每日留存用户明细表）

15.2.2 DWS层（1,2,3,n天留存用户明细表）

15.2.3 Union与Union all区别

15.3 ADS层

15.3.1 留存用户数

15.3.2 留存用户比率

16 新数据准备

17 沉默用户数

沉默用户：指的是只在安装当天启动过，且启动时间是在一周前

17.1 DWS层

使用日活明细表dws_uv_detail_day作为DWS层数据

17.2 ADS层

17.3 编写脚本

#!/bin/bash

hive=/opt/module/hive/bin/hive
APP=gmall

if [ -n "$1" ];then
	do_date=$1
else
	do_date=`date -d "-1 day" +%F`
fi

echo "-----------导入日期$do_date-----------"

sql="
insert into table "$APP".ads_silent_count
select 
    '$do_date' dt,
    count(*) silent_count
from 
(
    select 
        mid_id
    from "$APP".dws_uv_detail_day
    where dt<='$do_date'
    group by mid_id
    having count(*)=1 and min(dt)<=date_add('$do_date',-7)
)t1;"

$hive -e "$sql"

18 本周回流用户数

本周回流=本周活跃-本周新增-上周活跃

18.1 DWS层

使用日活明细表dws_uv_detail_day作为DWS层数据

18.2 ADS层

18.3 编写脚本

19 流失用户数

流失用户：最近7天未登录我们称之为流失用户

19.1 DWS层

使用日活明细表dws_uv_detail_day作为DWS层数据

19.2 ADS层

19.3 编写脚本

20 最近连续三周活跃用户数

最近3周连续活跃的用户：通常是周一对前3周的数据做统计，该数据一周计算一次。

20.1 DWS层

使用周活明细表dws_uv_detail_wk作为DWS层数据

20.2 ADS层

20.3 编写脚本

21 最近七天内连续三天活跃用户数

说明：最近7天内连续3天活跃用户数

21.1 DWS层

使用日活明细表dws_uv_detail_day作为DWS层数据

21.2 ADS层

21.3 编写脚本

22 每个用户累计访问次数

23 新收藏用户数

24 各个商品点击次数top3的用户

25 统计每日各类别下点击次数top10的商品

26 点击次数最多的10个用户点击的商品次数

27 月活跃率

28 电商业务与数据结构简介

28.1 电商业务流程

28.2 电商常识（SKU、SPU）

SKU=Stock Keeping Unit（库存量基本单位）。现在已经被引申为产品统一编号的简称，每种产品均对应有唯一的SKU号。
SPU(Standard Product Unit)：是商品信息聚合的最小单位，是一组可复用、易检索的标准化信息集合。
比如，咱们购买一台iPhoneX手机，iPhoneX手机就是一个SPU，但是你购买的时候，不可能是以iPhoneX手机为单位买的，商家也不可能以iPhoneX为单位记录库存SKU。必须要以什么颜色什么版本的iPhoneX为单位。比如，你购买的是一台银色、128G内存的、支持联通网络的iPhoneX，商家也会以这个单位来记录库存数。那这个更细致的单位就叫库存单元（SKU）。

28.3 电商表结构

29 数仓理论（面试重点）

29.1 表的分类

29.1.1 实体表

29.1.2 维度表

29.1.3 事务型事实表

29.1.4 周期型事实表

29.2 同步策略

29.3 范式理论

29.3.1 范式概念

关系型数据库设计时，遵照一定的规范要求，目的在于降低数据的冗余性，目前业界范式有：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)、第五范式(5NF)。
范式可以理解为设计一张数据表的表结构，符合的标准级别。
 使用范式的根本目的是：
  1）减少数据冗余，尽量让每个数据只出现一次。
  2）保证数据一致性
 缺点是获取数据时，需要通过Join拼接出最后的数据。

29.3.2 函数依赖

29.3.3 三范式区分

29.4 关系建模与维度建模

29.5 雪花模型、星型模型和星座模型

30 安装sqoop

1.解压

2.改名

3.将sqoop配到环境变量里面