离线数仓—ODS层设计开发
前言
前面已经完成了数仓开发的准备工作,下面就按照数据仓库的分层依次对每层进行设计和开发。
一、ODS层设计要点
1.数据结构
ODS层的表结构设计依托于从业务系统同步过来的数据结构,因此我们不应该对初始数据进行任何的改变,设计出来的表要完全符合orgin_data路径下业务系统里数据的格式。
2.压缩策略
ODS层要保留的时全部的历史数据,数据量较大,因此应该选择压缩比率较高的压缩方式,此处选择gzip压缩。
3.命名规范
ODS层的表名的命名规范为:ods_表名_单分区增量全量标识(inc/full)
二、日志表的设计与开发
日志数据的格式是JSON格式,对此有两种设计方式。
第一种方式是设计一个只有一个字段的表,这个字段为STRING类型,直接保留一整条日志数据;
第二种方式是建一个JSON表,直接能够跟json数据进行对应。
从建表上来看,第一种方式更为简单,但是从使用难易程度上来看,第二种方式操作起来更简单,因此采用第二种方式。
1.JSON表建表方式
在建表语句后面加上ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
即可(此方式适用于hive3.0版本之后)
在建表的时候要注意设计的表的字段名称要和json文件中的名称一致,否则无法对应上。
2.日志表建表分析
日志数据有两种类型,第一种是页面日志,格式如下:
{
"common": {
-- 环境信息
"ar": "230000", -- 地区编码
"ba": "iPhone", -- 手机品牌
"ch": "Appstore", -- 渠道
"is_new": "1",--是否首日使用,首次使用的当日,该字段值为1,过了24:00,该字段置为0。
"md": "iPhone 8", -- 手机型号
"mid": "YXfhjAYH6As2z9Iq", -- 设备id
"os": "iOS 13.2.9", -- 操作系统
"uid": "485", -- 会员id
"vc": "v2.1.134" -- app版本号
},
"actions": [ --动作(事件)
{
"action_id": "favor_add", --动作id
"item": "3", --目标id
"item_type": "sku_id", --目标类型
"ts": 1585744376605 --动作时间戳
}
],
"displays": [
{
"displayType": "query", -- 曝光类型
"item": "3", -- 曝光对象id
"item_type": "sku_id", -- 曝光对象类型
"order": 1, --出现顺序
"pos_id": 2 --曝光位置
},
{
"displayType": "promotion",
"item": "6",
"item_type": "sku_id",
"order": 2,
"pos_id": 1
},
{
"displayType": "promotion",
"item": "9",
"item_type": "sku_id",
"order": 3,
"pos_id": 3
},
{
"displayType": "recommend",
"item": "6",
"item_type": "sku_id",
"order": 4,
"pos_id": 2
},
{
"displayType": "query ",
"item": "6",
"item_type": "sku_id",
"order": 5,
"pos_id": 1
}
],
"page": {
--页面信息
"during_time": 7648, -- 持续时间毫秒
"item": "3", -- 目标id
"item_type": "sku_id", -- 目标类型
"last_page_id": "login", -- 上页类型
"page_id": "good_detail", -- 页面ID
"sourceType": "promotion" -- 来源类型
},
"err":{
--错误
"error_code": "1234", --错误码
"msg": "***********" --错误信息
},
"ts": 1585744374423 --跳入时间戳
}
第二种日志数据是启动日志,数据的格式如下:
{
"common": {
"ar": "370000",
"ba": "Honor",
"ch": "wandoujia",
"is_new": "1",
"md": "Honor 20s",
"mid": "eQF5boERMJFOujcp",
"os": "Android 11.0",
"uid": "76",
"vc": "v2.1.134"
},
"start": {
"entry": "icon", --icon手机图标 notice 通知 install 安装后启动
"loading_time": 18803, --启动加载时间
"open_ad_id": 7, --广告页ID
"open_ad_ms": 3449, -- 广告总共播放时间
"open_ad_skip_ms": 1989 -- 用户跳过广告时点
},
"err":{
--错误
"error_code": "1234", --错误码
"msg": "***********" --错误信息
},
"ts": 1585744304000
}
因为在orgin_data/log/topic_log里面两种格式的数据是掺杂在一起的,因此我们建的表必须要能够包含两种格式的所有数据,同时也应该能够区分这两种数据。
我们要取这两种类型数据的字段的并集,这样虽然有些字段是null,但是每种类型的数据都能够取全。
同时,我们可以使用start字段是否为null来区分是哪种类型的日志。
3.日志表建表实现
在确定了建json表后,要确定字段的类型,一般情况下,整数字段可以设置为BIGINT,字符串设置为STRING,小数设置为DECIMAL(16,2),其它的类型可以采用复杂类型array、map和struct.
array使用场景:没有key,只有value,数据个数不确定
map使用场景:所有的key的类型一致,所有的value的类型也一致,数据个数可以不确定
struct使用场景:value的类型可以不一致,但是数据个数要确定
根据上面两种日志的格式可以得到:common字段采用STRUCT类型,page采用STRUCT类型,actions采用ARRAY[STRUCT]类型,displays采用ARRAY[STRUCT]类型,start采用STRUCT类型,err采用STRUCT类型,ts采用BIGINT类型,最终建表语句如下:
DROP TABLE IF EXISTS ods_log_inc;
CREATE EXTERNAL TABLE ods_log_inc
(
`common` STRUCT<ar :STRING,ba :STRING,ch :STRING,is_new :STRING,md :STRING,mid :STRING,os :STRING,uid :STRING,vc
:STRING> COMMENT '公共信息',
`page` STRUCT<during_time :STRING,item :STRING,item_type :STRING,last_page_id :STRING,page_id
:STRING,source_type :STRING> COMMENT '页面信息',
`actions` ARRAY<STRUCT<action_id:STRING,item:STRING,item_type:STRING,ts:BIGINT>> COMMENT '动作信息',
`displays` ARRAY<STRUCT<display_type :STRING,item :STRING,item_type :STRING,`order` :STRING,pos_id
:STRING>> COMMENT '曝光信息',
`start` STRUCT<entry :STRING,loading_time :BIGINT,open_ad_id :BIGINT,open_ad_ms :BIGINT,open_ad_skip_ms
:BIGINT> COMMENT '启动信息',
`err` STRUCT<error_code:BIGINT,msg:STRING> COMMENT '错误信息',
`ts` BIGINT COMMENT '时间戳'
) COMMENT '活动信息表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
LOCATION '/warehouse/gmall/ods/ods_log_inc/';
4.日志表数据装载
日志表建表后要进行数据装载,日志表的数据装载每天一次,例如2020-06-14的装载语句:load data inpath '/origin_data/gmall/log/topic_log/2020-06-14' into table ods_log_inc partition(dt='2020-06-14');
每天都要执行一遍装载语句,不同的地方就是日期,因此写一个脚本进行每日数据的装载,脚本内容如下:
#!/bin/bash
# 定义变量方便修改
APP=gmall
# 如果是输入的日期按照取输入日期;如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
echo ================== 日志日期为 $do_date ==================
sql="
load data inpath '/origin_data/$APP/log/topic_log/$do_date' into table ${APP}.ods_log_inc partition(dt='$do_date');
"
hive -e "$sql"
脚本用法:脚本名称 + 日期(不写则为本地系统前一天日期)
三、业务表的设计与开发
业务表也分为两种,一种使用DataX同步的全量表,同步到HDFS里就是一行行的数据;另外一种是Maxwell同步的增量表,同步到HDFS里是一条条JSON数据。
1.全量表设计
全量表在HDFS里就是一行行的数据,