数仓模型设计

将业务需要用到的数据表通过Sqoop抽取到ODS层。根据业务我们在数仓中构建“用户主题”，具体数据分层如下：

以上各个表之间的数据流转关系如下：

在Hive中构建ODS层对应的表：

1. TO_YCAK_USR_D：微信用户日全量表

   物理模型名称	TO_YCAK_USR_D	DATA_TYPE	COMMENT
   逻辑模型名称	微信用户全量表	数据类型	注释
   用户ID	UID	int(11)
   注册机器ID	REG_MID	int(11)
   性别	GDR	varchar(1)
   生日	BIRTHDAY	varchar(16)
   手机号码	MSISDN	varchar(32)
   地区ID	LOC_ID	int(11)
   注册登录模式	LOG_MDE	int(1)	1免费，2付费，默认为0
   注册时间	REG_TM	varchar(32)
   用户当前经验值	USR_EXP	varchar(32)
   累计积分	SCORE	int(11)
   用户等级	LEVEL	int(11)
   微信ID	WX_ID	varchar(32)

   CREATE EXTERNAL TABLE `TO_YCAK_USR_D`(
    `UID` int, 
    `REG_MID` int, 
    `GDR` string, 
    `BIRTHDAY` string,
    `MSISDN` string,
    `LOC_ID` int,
    `LOG_MDE` int,
    `REG_TM` string,
    `USR_EXP` string,
    `SCORE` int,
    `LEVEL` int,
    `WX_ID` string
    )
   ROW FORMAT DELIMITED  FIELDS TERMINATED BY '\t' 
   LOCATION 'hdfs://mycluster/user/hive_remote/warehouse/data/user/TO_YCAK_USR_D';
   

2. TO_YCAK_USR_ALI_D：支付宝用户日全量表

   物理模型名称	TO_YCAK_USR_ALI_D	DATA_TYPE	COMMENT
   逻辑模型名称	支付宝用户全量表	数据类型	注释
   用户ID	UID	int(11)
   注册机器ID	REG_MID	int(11)
   性别	GDR	varchar(1)
   生日	BIRTHDAY	varchar(16)
   手机号码	MSISDN	varchar(32)
   地区ID	LOC_ID	int(11)
   注册登录模式	LOG_MDE	int(1)	1免费，2付费，默认为0
   注册时间	REG_TM	varchar(32)
   用户当前经验值	USR_EXP	varchar(32)
   累计积分	SCORE	int(11)
   用户等级	LEVEL	int(11)
   用户类型，1公司账户，2个人账户	USR_TYPE	varchar(64)	用户类型，1公司账户，2个人账户
   实名认证	IS_CERT	varchar(1)	T通过，已实名 F未实名
   是否学生	IS_STDNT	varchar(1)	T是，F不是
   支付宝ID	ALY_ID	varchar(64)

   xxxxxxxxxx
   CREATE EXTERNAL TABLE `TO_YCAK_USR_ALI_D`(
    `UID` int, 
    `REG_MID` int, 
    `GDR` string, 
    `BIRTHDAY` string,
    `MSISDN` string,
    `LOC_ID` int,
    `LOG_MDE` int,
    `REG_TM` string,
    `USR_EXP` string,
    `SCORE` int,
    `LEVEL` int,
    `USR_TYPE` string,
    `IS_CERT` string,
    `IS_STDNT` string,
    `ALY_ID` string 
    )
   ROW FORMAT DELIMITED  FIELDS TERMINATED BY '\t' 
   LOCATION 'hdfs://mycluster/user/hive_remote/warehouse/data/user/TO_YCAK_USR_ALI_D';
   

3. TO_YCAK_USR_QQ_D： QQ用户日全量表

   物理模型名称	TO_YCAK_USR_QQ_D	DATA_TYPE	COMMENT
   逻辑模型名称	QQ用户日全量表	数据类型	注释
   用户ID	UID	int(11)
   注册机器ID	REG_MID	int(11)
   性别	GDR	varchar(1)
   生日	BIRTHDAY	varchar(16)
   手机号码	MSISDN	varchar(32)
   地区ID	LOC_ID	int(11)
   注册登录模式	LOG_MDE	int(1)	1免费，2付费，默认为0
   注册时间	REG_TM	varchar(32)
   用户当前经验值	USR_EXP	varchar(32)
   累计积分	SCORE	int(11)
   用户等级	LEVEL	int(11)
   QQID	QQID	varchar(64)

   xxxxxxxxxx
   CREATE EXTERNAL TABLE `TO_YCAK_USR_QQ_D`(
    `UID` int, 
    `REG_MID` int, 
    `GDR` string, 
    `BIRTHDAY` string,
    `MSISDN` string,
    `LOC_ID` int,
    `LOG_MDE` int,
    `REG_TM` string,
    `USR_EXP` string,
    `SCORE` int,
    `LEVEL` int,
    `QQID` string 
    )
   ROW FORMAT DELIMITED  FIELDS TERMINATED BY '\t' 
   LOCATION 'hdfs://mycluster/user/hive_remote/warehouse/data/user/TO_YCAK_USR_QQ_D';
   

4. TO_YCAK_USR_APP_D ：APP用户信息日全量表

   物理模型名称	TO_YCAK_USR_APP_D	DATA_TYPE
   逻辑模型名称	APP用户信息日全量表	数据类型
   用户ID	UID	int(11)
   注册机器ID	REG_MID	int(11)
   性别	GDR	varchar(1)
   生日	BIRTHDAY	varchar(16)
   手机号码	MSISDN	varchar(32)
   地区ID	LOC_ID	int(11)
   注册时间	REG_TM	varchar(32)
   用户当前经验值	USR_EXP	varchar(32)
   用户等级	LEVEL	int(11)
   用户在app系统的id	APP_ID	varchar(32)

   xxxxxxxxxx
   CREATE EXTERNAL TABLE `TO_YCAK_USR_APP_D`(
    `UID` int, 
    `REG_MID` int, 
    `GDR` string, 
    `BIRTHDAY` string,
    `MSISDN` string,
    `LOC_ID` int,
    `REG_TM` string,
    `USR_EXP` string,
    `LEVEL` int,
    `APP_ID` string 
    )
   ROW FORMAT DELIMITED  FIELDS TERMINATED BY '\t' 
   LOCATION 'hdfs://mycluster/user/hive_remote/warehouse/data/user/TO_YCAK_USR_APP_D';
   

5. TO_YCAK_USR_LOGIN_D ： 用户登录数据日增量表

   物理模型名称	TO_YCAK_USR_LOGIN_D	DATA_TYPE	COMMENT
   逻辑模型名称	用户登录数据表日增量	数据类型	注释
   自增唯一主键	ID	int	自增唯一主键
   用户ID	UID	int	用户ID
   机器ID	MID	int	机器ID
   登录时间	LOGIN_TM	datetime	登录时间
   登出时间	LOGOUT_TM	datetime	登出时间，默认值0000-00-00 00：00：00
   登录模式	MODE_TYPE	int	玩家登录模式1为免费2为付费默认为0
   数据日期	data_dt	varchar(32)	分区字段

   xxxxxxxxxx
   CREATE EXTERNAL TABLE `TO_YCAK_USR_LOGIN_D`(
    `ID` int, 
    `UID` int, 
    `MID` int, 
    `LOGIN_TM` string,
    `LOGOUT_TM` string,
    `MODE_TYPE` int
    )
   PARTITIONED BY (`data_dt` string)
   ROW FORMAT DELIMITED  FIELDS TERMINATED BY '\t' 
   LOCATION 'hdfs://mycluster/user/hive_remote/warehouse/data/user/TO_YCAK_USR_LOGIN_D';
   

6. TW_USR_BASEINFO_D :活跃用户基础信息日增量表

   物理模型名称	TW_USR_BASEINFO_D	DATA_TYPE	COMMENT
   逻辑模型名称	活跃用户基础信息日增量表	数据类型	注释
   用户ID	UID	int(11)
   注册机器ID	REG_MID	int(11)
   注册渠道	REG_CHNL	varchar(1)	0：未知，1：微信，2：支付宝，3：QQ，4：APP
   渠道ID	REF_UID	varchar(1)
   性别	GDR	varchar(1)	1：男，2：女，0：不明
   生日	BIRTHDAY	varchar(16)
   手机号码	MSISDN	varchar(32)
   地区ID	LOC_ID	int(11)
   注册登录模式	LOG_MDE	varchar(1)	1免费，2付费，默认为0
   注册日期	REG_DT	varchar(32)
   注册时间	REG_TM	varchar(32)
   用户当前经验值	USR_EXP	varchar(32)
   累计积分	SCORE	int(11)
   用户等级	LEVEL	int(11)
   用户类型，1公司账户，2个人账户	USR_TYPE	varchar(64)	用户类型，1公司账户，2个人账户
   实名认证	IS_CERT	varchar(1)	T通过，已实名 F未实名
   是否学生	IS_STDNT	varchar(1)	T是，F不是
   数据日期	data_dt	varchar(32)	分区字段

   xxxxxxxxxx
   CREATE EXTERNAL TABLE `TW_USR_BASEINFO_D`(
    `UID` int, 
    `REG_MID` int, 
    `REG_CHNL` string, 
    `REF_UID` string,
    `GDR` string,
    `BIRTHDAY` string,
    `MSISDN` string,
    `LOC_ID` int,
    `LOG_MDE` string,
    `REG_DT` string,
    `REG_TM` string,
    `USR_EXP` string,
    `SCORE` int,
    `LEVEL` int,
    `USR_TYPE` string,
    `IS_CERT` string,
    `IS_STDNT` string
    )
   PARTITIONED BY (`data_dt` string)
   ROW FORMAT DELIMITED  FIELDS TERMINATED BY '\t' 
   LOCATION 'hdfs://mycluster/user/hive_remote/warehouse/data/user/TW_USR_BASEINFO_D';
   

以上表模型中，最终获取7日用户活跃信息从EDS层TW_USR_BASEINFO_D表统计得到，这里将统计到的7日活跃用户情况存放在DM层，这里通过SparkSQL直接将结果存放在user_7days_active表中，提供查询展示。