devbox
凡人多烦事01
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

3.用户行为数据仓库

作者：凡人多烦事01 | 2024-05-08 13:22:39

赞

踩

3.用户行为数据仓库

用户行为数据仓库

数据分层

在数据仓库中需要对于数据进行分层，原因如下

  • 用空间换时间，通过大量的预处理来提升应用系统的用户体验（效率），因此数据仓库会存在大量冗余的数据；硬盘便宜，计算量贵
  • 如果不分层的话，如果源业务系统的业务规则发生变化将会影响整个数据清洗过程，工作量巨大
  • 不论是数据的异常还是数据的敏感性，使真实数据和统计数据解耦，防止原始数据的变化导致整个集群发生大的变动。

在这个数仓项目中将数据分为四层

  • ODS层：原始数据层，存放原始数据，直接加载原始日志，数据，数据保持原样不做更改。
  • DWD层：结构和原始数据表保持一致，是对ODS的数据进行清洗。
  • DWS层：以DWD为基础，进行轻度汇总。
  • ADS层：为各种应用提供数据。

在各个层次的数据前面加入层次名。

Hive仓库

Hive的安装基于mysql，在安装hive前需要安装mysql,

Mysql的安装

配置mysql的HA和主从。

Hive的安装

安装即可使用，需要将hive元数据配置到Mysql

  1. <?xml version="1.0"?>
  2. <?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
  3. <configuration>
  4. 	<property>
  5. 	  <name>javax.jdo.option.ConnectionURL</name>
  6. 	  <value>jdbc:mysql://192.168.6.100:3306/metastore?createDatabaseIfNotExist=true</value>
  7. 	  <description>JDBC connect string for a JDBC metastore</description>
  8. 	</property>
  9.  
  10. 	<property>
  11. 	  <name>javax.jdo.option.ConnectionDriverName</name>
  12. 	  <value>com.mysql.jdbc.Driver</value>
  13. 	  <description>Driver class name for a JDBC metastore</description>
  14. 	</property>
  16. 	<property>
  17. 	  <name>javax.jdo.option.ConnectionUserName</name>
  18. 	  <value>root</value>
  19. 	  <description>username to use against metastore database</description>
  20. 	</property>
  22. 	<property>
  23. 	  <name>javax.jdo.option.ConnectionPassword</name>
  24. 	  <value>123456</value>
  25. 	  <description>password to use against metastore database</description>
  26. 	</property>
  27. </configuration>

Hive配置Tez,下载Tez的依赖包，将该包上传到HDFS的/tez目录下，mapreduce提供了一个分布式缓存，该缓存会在第一次运行时读入到各自的task之中。hadoop101上解压缩到tar -zxvf apache-tez-0.9.1-bin.tar.gz -C /opt/module。

ODS层的搭建

ODS：原始数据层，用于存放原始数据，直接加载原始日志、数据，数据保持原貌不变

在搭建ODS层时，ODS层的数据不会经常被运算，且数据量是最大的，所以可以配置ODS层为压缩。在这次项目中，配置有LZO压缩。

在配置压缩后建表：

启动日志表

  1. CREATE EXTERNAL TABLE ods_start_log (`line` string)
  2. PARTITIONED BY (`dt` string)
  3. STORED AS
  4.   INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  5.   OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  6. LOCATION '/warehouse/gmall/ods/ods_start_log';

建表后直接从hdfs上加载即可

load data inpath '/origin_data/gmall/log/topic_start/2019-12-21' into table gmall.ods_start_log partition(dt='2019-12-21');

事件日志表

  1. CREATE EXTERNAL TABLE ods_event_log(`line` string)
  2. PARTITIONED BY (`dt` string)
  3. STORED AS
  4.   INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  5.   OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  6. LOCATION '/warehouse/gmall/ods/ods_event_log';
load data inpath '/origin_data/gmall/log/topic_event/2019-12-21' into table gmall.ods_event_log partition(dt='2019-12-21');

DWD层

对ODS层进行了简单的清洗，去除空值，脏数据等操作后可以提取出ODS层

启动日志表

  1. CREATE EXTERNAL TABLE dwd_start_log(
  2. `mid_id` string,
  3. `user_id` string, 
  4. `version_code` string, 
  5. `version_name` string, 
  6. `lang` string, 
  7. `source` string, 
  8. `os` string, 
  9. `area` string, 
  10. `model` string,
  11. `brand` string, 
  12. `sdk_version` string, 
  13. `gmail` string, 
  14. `height_width` string,  
  15. `app_time` string,
  16. `network` string, 
  17. `lng` string, 
  18. `lat` string, 
  19. `entry` string, 
  20. `open_ad_type` string, 
  21. `action` string, 
  22. `loading_time` string, 
  23. `detail` string, 
  24. `extend1` string
  25. )
  26. PARTITIONED BY (dt string)
  27. stored as parquet
  28. location '/warehouse/gmall/dwd/dwd_start_log/'
  29. TBLPROPERTIES('parquet.compression'='lzo')

建表之后导入数据,启动日志表中的每一行都是一个json,因此此处使用了get_json_object函数，函数是单进单出，第二个参数类似于正则中的匹配项，$代表整行，.mid表示一个属性

  1. insert overwrite table dwd_start_log
  2. PARTITION (dt='2019-12-21')
  3. select 
  4.     get_json_object(line,'$.mid') mid_id,
  5.     get_json_object(line,'$.uid') user_id,
  6.     get_json_object(line,'$.vc') version_code,
  7.     get_json_object(line,'$.vn') version_name,
  8.     get_json_object(line,'$.l') lang,
  9.     get_json_object(line,'$.sr') source,
  10.     get_json_object(line,'$.os') os,
  11.     get_json_object(line,'$.ar') area,
  12.     get_json_object(line,'$.md') model,
  13.     get_json_object(line,'$.ba') brand,
  14.     get_json_object(line,'$.sv') sdk_version,
  15.     get_json_object(line,'$.g') gmail,
  16.     get_json_object(line,'$.hw') height_width,
  17.     get_json_object(line,'$.t') app_time,
  18.     get_json_object(line,'$.nw') network,
  19.     get_json_object(line,'$.ln') lng,
  20.     get_json_object(line,'$.la') lat,
  21.     get_json_object(line,'$.entry') entry,
  22.     get_json_object(line,'$.open_ad_type') open_ad_type,
  23.     get_json_object(line,'$.action') action,
  24.     get_json_object(line,'$.loading_time') loading_time,
  25.     get_json_object(line,'$.detail') detail,
  26.     get_json_object(line,'$.extend1') extend1
  27. from ods_start_log 
  28. where dt='2019-12-21';

事件日志表不是一个单纯的json。因此需要自定义UDF函数。

自定义UDF函数

UDF

  1. /*
  2. 解析公共字段
  3. TODO	将传入的line,用“|”切割，取出服务器时间serverTime和json数据
  4.  根据传入的key，获取对应的value值
  5. */

UDTF函数：

  1. /*
  2. 解析具体事件字段
  3. TODO	需要继承GenericUDTF。
  4. */

在本次项目中只分析一个用户的主题

DWS层

DWS层主要的目的是根据DWD层的数据建立一个宽表，使用宽表去分析，避免大量join查询，而宽表的难以修改的缺陷再大数据领域体现不多

此次业务是获取当日、当周、当月活跃设备数，活不活跃只需要看启动日志就行了~

  1. create external table dws_uv_detail_day
  2. (
  3.     `mid_id` string COMMENT '设备唯一标识',
  4.     `user_id` string COMMENT '用户标识', 
  5.     `version_code` string COMMENT '程序版本号', 
  6.     `version_name` string COMMENT '程序版本名', 
  7.     `lang` string COMMENT '系统语言', 
  8.     `source` string COMMENT '渠道号', 
  9.     `os` string COMMENT '安卓系统版本', 
  10.     `area` string COMMENT '区域', 
  11.     `model` string COMMENT '手机型号', 
  12.     `brand` string COMMENT '手机品牌', 
  13.     `sdk_version` string COMMENT 'sdkVersion', 
  14.     `gmail` string COMMENT 'gmail', 
  15.     `height_width` string COMMENT '屏幕宽高',
  16.     `app_time` string COMMENT '客户端日志产生时的时间',
  17.     `network` string COMMENT '网络模式',
  18.     `lng` string COMMENT '经度',
  19.     `lat` string COMMENT '纬度'
  20. )
  21. partitioned by(dt string)
  22. stored as parquet
  23. location '/warehouse/gmall/dws/dws_uv_detail_day';
  1. --<同一个mid的多个用户需要去重，但是不能简单的将数据取出，因为DWS层还是需要有数据的一些明细信息，为了进一步处理>
  2. --<所以在此处我使用collect_set（去重合并）然后通过concat_ws()拼接为字符串>
  3. insert overwrite table dws_uv_detail_day 
  4. partition(dt='2019-12-21')
  5. select  
  6.     mid_id,
  7.     concat_ws('|', collect_set(user_id)) user_id,
  8.     concat_ws('|', collect_set(version_code)) version_code,
  9.     concat_ws('|', collect_set(version_name)) version_name,
  10.     concat_ws('|', collect_set(lang))lang,
  11.     concat_ws('|', collect_set(source)) source,
  12.     concat_ws('|', collect_set(os)) os,
  13.     concat_ws('|', collect_set(area)) area, 
  14.     concat_ws('|', collect_set(model)) model,
  15.     concat_ws('|', collect_set(brand)) brand,
  16.     concat_ws('|', collect_set(sdk_version)) sdk_version,
  17.     concat_ws('|', collect_set(gmail)) gmail,
  18.     concat_ws('|', collect_set(height_width)) height_width,
  19.     concat_ws('|', collect_set(app_time)) app_time,
  20.     concat_ws('|', collect_set(network)) network,
  21.     concat_ws('|', collect_set(lng)) lng,
  22.     concat_ws('|', collect_set(lat)) lat
  23. from dwd_start_log
  24. where dt='2019-12-21'
  25. group by mid_id;

一周用户活跃明细，安装group 问题？如何判断为同一周，项目中使用的方法是创建额外字段，本日期对应的周一和周日日期

  1. drop table if exists dws_uv_detail_wk;
  2. create external table dws_uv_detail_wk( 
  3.     `mid_id` string COMMENT '设备唯一标识',
  4.     `user_id` string COMMENT '用户标识', 
  5.     `version_code` string COMMENT '程序版本号', 
  6.     `version_name` string COMMENT '程序版本名', 
  7.     `lang` string COMMENT '系统语言', 
  8.     `source` string COMMENT '渠道号', 
  9.     `os` string COMMENT '安卓系统版本', 
  10.     `area` string COMMENT '区域', 
  11.     `model` string COMMENT '手机型号', 
  12.     `brand` string COMMENT '手机品牌', 
  13.     `sdk_version` string COMMENT 'sdkVersion', 
  14.     `gmail` string COMMENT 'gmail', 
  15.     `height_width` string COMMENT '屏幕宽高',
  16.     `app_time` string COMMENT '客户端日志产生时的时间',
  17.     `network` string COMMENT '网络模式',
  18.     `lng` string COMMENT '经度',
  19.     `lat` string COMMENT '纬度',
  20.     `monday_date` string COMMENT '周一日期',
  21.     `sunday_date` string COMMENT  '周日日期' 
  22. ) COMMENT '活跃用户按周明细'
  23. PARTITIONED BY (`wk_dt` string)
  24. stored as parquet
  25. location '/warehouse/gmall/dws/dws_uv_detail_wk/';

按照如下插入数据，下一个周一提前7天就是当前所在周的周一，date_add(next_day('2019-12-21','MO'),-7)，周日同理 week字段就可以用concat(date_add( next_day('2019-12-21','MO'),-7), '_' , date_add(next_day('2019-12-21','MO'),-1) 唯一标识

  1. insert overwrite table dws_uv_detail_wk partition(wk_dt)
  2. select  
  3.     mid_id,
  4.     concat_ws('|', collect_set(user_id)) user_id,
  5.     concat_ws('|', collect_set(version_code)) version_code,
  6.     concat_ws('|', collect_set(version_name)) version_name,
  7.     concat_ws('|', collect_set(lang)) lang,
  8.     concat_ws('|', collect_set(source)) source,
  9.     concat_ws('|', collect_set(os)) os,
  10.     concat_ws('|', collect_set(area)) area, 
  11.     concat_ws('|', collect_set(model)) model,
  12.     concat_ws('|', collect_set(brand)) brand,
  13.     concat_ws('|', collect_set(sdk_version)) sdk_version,
  14.     concat_ws('|', collect_set(gmail)) gmail,
  15.     concat_ws('|', collect_set(height_width)) height_width,
  16.     concat_ws('|', collect_set(app_time)) app_time,
  17.     concat_ws('|', collect_set(network)) network,
  18.     concat_ws('|', collect_set(lng)) lng,
  19.     concat_ws('|', collect_set(lat)) lat,
  20.     date_add(next_day('2019-12-21','MO'),-7),
  21.     date_add(next_day('2019-12-21','MO'),-1),
  22.     concat(date_add( next_day('2019-12-21','MO'),-7), '_' , date_add(next_day('2019-12-21','MO'),-1) 
  23. )
  24. from dws_uv_detail_day 
  25. where dt>=date_add(next_day('2019-12-21','MO'),-7) and dt<=date_add(next_day('2019-12-21','MO'),-1) 
  26. group by mid_id;

ADS

ADS只要继续在DWD层上处理就可以实现我们的需求。

  1. create external table ads_uv_count( 
  2.     `dt` string COMMENT '统计日期',
  3.     `day_count` bigint COMMENT '当日用户数量',
  4.     `wk_count`  bigint COMMENT '当周用户数量',
  5.     `mn_count`  bigint COMMENT '当月用户数量',
  6.     `is_weekend` string COMMENT 'Y,N是否是周末,用于得到本周最终结果',
  7.     `is_monthend` string COMMENT 'Y,N是否是月末,用于得到本月最终结果' 
  8. ) COMMENT '活跃设备数'
  9. row format delimited fields terminated by '\t'
  10. location '/warehouse/gmall/ads/ads_uv_count/';

插入语句

  1. insert into table ads_uv_count 
  2. select  
  3.   '2019-12-21' dt,
  4.    daycount.ct,
  5.    wkcount.ct,
  6.    mncount.ct,
  7.    if(date_add(next_day('2019-12-21','MO'),-1)='2019-12-21','Y','N') ,
  8.    if(last_day('2019-12-21')='2019-12-21','Y','N') 
  9. from 
  10. (
  11.    select  
  12.       '2019-12-21' dt,
  13.        count(*) ct
  14.    from dws_uv_detail_day
  15.    where dt='2019-12-21'  
  16. )daycount join 
  17. ( 
  18.    select  
  19.      '2019-12-21' dt,
  20.      count (*) ct
  21.    from dws_uv_detail_wk
  22.    where wk_dt=concat(date_add(next_day('2019-12-21','MO'),-7),'_' ,date_add(next_day('2019-12-21','MO'),-1) )
  23. ) wkcount on daycount.dt=wkcount.dt
  24. join 
  25. ( 
  26.    select  
  27.      '2019-12-21' dt,
  28.      count (*) ct
  29.    from dws_uv_detail_mn
  30.    where mn=date_format('2019-12-21','yyyy-MM')  
  31. )mncount on daycount.dt=mncount.dt;
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/凡人多烦事01/article/detail/554777
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号