浙江省建设工程招投标网站,如何开发小程序商城,学校英语网站栏目名称,沧州科盛网络技术有限公司ODS层的设计要点如下#xff1a;
#xff08;1#xff09;ODS层的表结构设计依托于从业务系统同步过来的数据结构。
#xff08;2#xff09;ODS层要保存全部历史数据#xff0c;故其压缩格式应选择压缩比较高的#xff0c;此处选择gzip。
#xff08;3#xff09;…ODS层的设计要点如下
1ODS层的表结构设计依托于从业务系统同步过来的数据结构。
2ODS层要保存全部历史数据故其压缩格式应选择压缩比较高的此处选择gzip。
3ODS层表名的命名规范为ods_表名_单分区增量全量标识inc/full。
同样的需要将用户行为数据表和业务数据表放到ODS层。 日志表
DROP TABLE IF EXISTS ods_log_inc;
CREATE EXTERNAL TABLE ods_log_inc
(common STRUCTar :STRING,ba :STRING,ch :STRING,is_new :STRING,md :STRING,mid :STRING,os :STRING,uid :STRING,vc:STRING COMMENT 公共信息,page STRUCTduring_time :STRING,item :STRING,item_type :STRING,last_page_id :STRING,page_id:STRING,source_type :STRING COMMENT 页面信息,actions ARRAYSTRUCTaction_id:STRING,item:STRING,item_type:STRING,ts:BIGINT COMMENT 动作信息,displays ARRAYSTRUCTdisplay_type :STRING,item :STRING,item_type :STRING,order :STRING,pos_id:STRING COMMENT 曝光信息,start STRUCTentry :STRING,loading_time :BIGINT,open_ad_id :BIGINT,open_ad_ms :BIGINT,open_ad_skip_ms:BIGINT COMMENT 启动信息,err STRUCTerror_code:BIGINT,msg:STRING COMMENT 错误信息,ts BIGINT COMMENT 时间戳
) COMMENT 活动信息表PARTITIONED BY (dt STRING)ROW FORMAT SERDE org.apache.hadoop.hive.serde2.JsonSerDeLOCATION /warehouse/gmall/ods/ods_log_inc/;
创建一个外部表防止误操作
common、page、actions、displays、start、err、ts 是表的列名STRUCT 是一种复合数据类型用于表示多个字段的组合。例如common 列使用 STRUCT 类型其中包含了多个字段ARRAY 是一种用于表示数组的数据类型。例如actions 和 displays 列使用 ARRAY 类型分别包含了多个结构化的元素。PARTITIONED BY 指定了表的分区列这里使用 dt 列作为分区列。ROW FORMAT SERDE 指定了数据的序列化和反序列化方式这里使用 JsonSerDe。LOCATION 指定了外部表的存储位置
但是为什么创建这几个类? 因为当初咱们的日志格式是这样的
页面日志
{common: { -- 环境信息ar: 15, -- 省份IDba: iPhone, -- 手机品牌ch: Appstore, -- 渠道is_new: 1, -- 是否首日使用首次使用的当日该字段值为1过了24:00该字段置为0。md: iPhone 8, -- 手机型号mid: YXfhjAYH6As2z9Iq, -- 设备idos: iOS 13.2.9, -- 操作系统sid: 3981c171-558a-437c-be10-da6d2553c517 -- 会话iduid: 485, -- 会员idvc: v2.1.134 -- app版本号},actions: [{ -- 动作(事件)action_id: favor_add, -- 动作iditem: 3, -- 目标iditem_type: sku_id, -- 目标类型ts: 1585744376605 -- 动作时间戳}],displays: [{ -- 曝光displayType: query, -- 曝光类型item: 3, -- 曝光对象iditem_type: sku_id, -- 曝光对象类型order: 1, -- 出现顺序pos_id: 2 -- 曝光位置pos_seq: 1 -- 曝光序列号同一坑位多个对象的编号},{displayType: promotion,item: 6,item_type: sku_id,order: 2,pos_id: 1pos_seq: 1},{displayType: promotion,item: 9,item_type: sku_id,order: 3,pos_id: 3pos_seq: 1},{displayType: recommend,item: 6,item_type: sku_id,order: 4,pos_id: 2pos_seq: 1},{displayType: query ,item: 6,item_type: sku_id,order: 5,pos_id: 1pos_seq: 1}],page: { -- 页面信息during_time: 7648, -- 持续时间毫秒item: 3, -- 目标iditem_type: sku_id, -- 目标类型last_page_id: login, -- 上页IDpage_id: good_detail, -- 页面IDfrom_pos_id:999, -- 来源坑位ID
from_pos_seq:999, -- 来源坑位序列号
refer_id:2, -- 外部营销渠道IDsourceType: promotion -- 来源类型}, err: { --错误error_code: 1234, --错误码msg: *********** --错误信息}, ts: 1585744374423 --跳入时间戳
} JSON格式最外层JSON对象的属性作为表的字段。重点是属性是什么类型 第一个common对象用map或者struct都行因为个数确定用struct更好 第二个action有中括号用数组arraystruct因为里面的数组元素类型不统一ARRAYSTRUCTaction_id:STRING,item:STRING,item_type:STRING,ts:BIGINT 同理第三个action也大同小异.... 启动日志
{common: {ar: 370000,ba: Honor,ch: wandoujia,is_new: 1,md: Honor 20s,mid: eQF5boERMJFOujcp,
os: Android 11.0,
sid:a1068e7a-e25b-45dc-9b9a-5a55ae83fc81uid: 76,vc: v2.1.134},start: {
entry: icon, --icon手机图标 notice 通知 install 安装后启动loading_time: 18803, --启动加载时间open_ad_id: 7, --广告页IDopen_ad_ms: 3449, -- 广告总共播放时间open_ad_skip_ms: 1989 -- 用户跳过广告时点},
err:{ --错误
error_code: 1234, --错误码msg: *********** --错误信息
},ts: 1585744304000
} 这些这里面commonts什么都是一样的只有start不一样start也是结构体。
数据装载
load data inpath /origin_data/gmall/log/topic_log/2020-06-14 into table ods_log_inc partition(dt2020-06-14);
每日数据装载脚本
1在hadoop102的/home/atguigu/bin目录下创建hdfs_to_ods_log.sh
#!/bin/bash# 定义变量方便修改
APPgmall# 如果是输入的日期按照取输入日期如果没输入日期取当前时间的前一天
if [ -n $1 ] ;thendo_date$1
elsedo_datedate -d -1 day %F
fiecho 日志日期为 $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 这个脚本我们需要传入一个日期参数。首先定义APP一个外部变量gmallif [ -n $1 ]是判定传入的第一个参数是否为空如果不为空则将传入的参数赋给do_date否则do_date赋为今天的日期减一天 随后拼接sql语句load data数据装载语句inpath 后面跟着的是数据存放的路径into后面跟着我们新创建的ODS层的表名 拼接完sql语句hive -e $sql相当于执行sql语句。Bash脚本特有的执行sql语句的语法。 业务表 这里表较多全量和增量各取一张表做个简单说明 活动信息表全量表
DROP TABLE IF EXISTS ods_activity_info_full;
CREATE EXTERNAL TABLE ods_activity_info_full
(id STRING COMMENT 活动id,activity_name STRING COMMENT 活动名称,activity_type STRING COMMENT 活动类型,activity_desc STRING COMMENT 活动描述,start_time STRING COMMENT 开始时间,end_time STRING COMMENT 结束时间,create_time STRING COMMENT 创建时间
) COMMENT 活动信息表PARTITIONED BY (dt STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY \tNULL DEFINED AS LOCATION /warehouse/gmall/ods/ods_activity_info_full/; 我们当时全量表数据的同步是靠DataX同步过来的传输过来的是Tsv格式我们要尽量保持格式不变。 这是当时的活动信息表的样式 这段代码用于创建一个外部表 ods_activity_info_full。该表包含了多个列其中每个列都有对应的数据类型和注释。
id、activity_name、activity_type、activity_desc、start_time、end_time、create_time 是表的列名。STRING 是表示字符串类型的数据类型。COMMENT 用于为列添加注释描述列的含义。PARTITIONED BY 指定了表的分区列这里使用 dt 列作为分区列。ROW FORMAT DELIMITED FIELDS TERMINATED BY \t 指定了行格式数据以制表符分隔。NULL DEFINED AS 指定了空值的表示方式这里将空值定义为空字符串。LOCATION 指定了外部表的存储位置。 购物车表增量表:
DROP TABLE IF EXISTS ods_cart_info_inc;
CREATE EXTERNAL TABLE ods_cart_info_inc
(type STRING COMMENT 变动类型,ts BIGINT COMMENT 变动时间,data STRUCTid :STRING,user_id :STRING,sku_id :STRING,cart_price :DECIMAL(16, 2),sku_num :BIGINT,img_url :STRING,sku_name:STRING,is_checked :STRING,create_time :STRING,operate_time :STRING,is_ordered :STRING,order_time:STRING,source_type :STRING,source_id :STRING COMMENT 数据,old MAPSTRING,STRING COMMENT 旧值
) COMMENT 购物车增量表PARTITIONED BY (dt STRING)ROW FORMAT SERDE org.apache.hadoop.hive.serde2.JsonSerDeLOCATION /warehouse/gmall/ods/ods_cart_info_inc/; 我们当时全量表数据的同步是靠maxwell同步过来的传输过来的是JSON格式。JSON最外层JSON对象的属性作为表的字段。而且我们设计的表要考虑到下面三种不同的操作。但是里面的字段并不是全要的比如database字段对我们的统计分析没用table也没用。Type有用因为它可以帮我们区分三种不同的操作。xid和commit也用不上因此我们分析比对只需要typetsdateold四个字段 typeStringtstimastampdate用结构体oldmap格式不确定是几个。 这是当时的购物车表的样式
在hadoop102的/home/atguigu/bin目录下创建hdfs_to_ods_db.sh
编写如下内容
#!/bin/bashAPPgmallif [ -n $2 ] ;thendo_date$2
else do_datedate -d -1 day %F
fiload_data(){sqlfor i in $*; do#判断路径是否存在hadoop fs -test -e /origin_data/$APP/db/${i:4}/$do_date#路径存在方可装载数据if [[ $? 0 ]]; thensql$sqlload data inpath /origin_data/$APP/db/${i:4}/$do_date OVERWRITE into table ${APP}.$i partition(dt$do_date);fidonehive -e $sql
}case $1 inods_activity_info_full)load_data ods_activity_info_full;;ods_activity_rule_full)load_data ods_activity_rule_full;;ods_base_category1_full)load_data ods_base_category1_full;;ods_base_category2_full)load_data ods_base_category2_full;;ods_base_category3_full)load_data ods_base_category3_full;;ods_base_dic_full)load_data ods_base_dic_full;;ods_base_province_full)load_data ods_base_province_full;;ods_base_region_full)load_data ods_base_region_full;;ods_base_trademark_full)load_data ods_base_trademark_full;;ods_cart_info_full)load_data ods_cart_info_full;;ods_coupon_info_full)load_data ods_coupon_info_full;;ods_sku_attr_value_full)load_data ods_sku_attr_value_full;;ods_sku_info_full)load_data ods_sku_info_full;;ods_sku_sale_attr_value_full)load_data ods_sku_sale_attr_value_full;;ods_spu_info_full)load_data ods_spu_info_full;;ods_cart_info_inc)load_data ods_cart_info_inc;;ods_comment_info_inc)load_data ods_comment_info_inc;;ods_coupon_use_inc)load_data ods_coupon_use_inc;;ods_favor_info_inc)load_data ods_favor_info_inc;;ods_order_detail_inc)load_data ods_order_detail_inc;;ods_order_detail_activity_inc)load_data ods_order_detail_activity_inc;;ods_order_detail_coupon_inc)load_data ods_order_detail_coupon_inc;;ods_order_info_inc)load_data ods_order_info_inc;;ods_order_refund_info_inc)load_data ods_order_refund_info_inc;;ods_order_status_log_inc)load_data ods_order_status_log_inc;;ods_payment_info_inc)load_data ods_payment_info_inc;;ods_refund_payment_inc)load_data ods_refund_payment_inc;;ods_user_info_inc)load_data ods_user_info_inc;;all)load_data ods_activity_info_full ods_activity_rule_full ods_base_category1_full ods_base_category2_full ods_base_category3_full ods_base_dic_full ods_base_province_full ods_base_region_full ods_base_trademark_full ods_cart_info_full ods_coupon_info_full ods_sku_attr_value_full ods_sku_info_full ods_sku_sale_attr_value_full ods_spu_info_full ods_cart_info_inc ods_comment_info_inc ods_coupon_use_inc ods_favor_info_inc ods_order_detail_inc ods_order_detail_activity_inc ods_order_detail_coupon_inc ods_order_info_inc ods_order_refund_info_inc ods_order_status_log_inc ods_payment_info_inc ods_refund_payment_inc ods_user_info_inc;;
esac 此脚本首先定义一个外部变量APPgmall随后再判断此脚本的第二个参数是否为空如果是输入的日期就传入输入日期如果没输入日期取当前日期的前一天。 后面定义了一个load_data函数sql赋予一个空字符串 for i in $*这行代码使用 for 循环遍历load_data函数的所有参数其实就一个参数也就是表名 hadoop fs -test -e判断此路径是否存在/origin_data/$APP/db/${i:4}/$do_date${i:4}表示从传入的参数的第四个字符后开始读比如这个ods_activity_info_full前面的ods跳过后面的参数表示表名 如果路径存在 sql$sqlload data inpath /origin_data/$APP/db/${i:4}/$do_date OVERWRITE into table ${APP}.$i partition(dt$do_date);数据装载 数据装载之后如下所示
