数据开发/数仓工程师上手指南(四)ODS层搭建规范及流程

前言

此系列的前三篇文章已经将整个数据仓库的所有构建逻辑流程讲的十分清晰，等于是我们已经把框架搭建好了，接下来就是填充框架内各个组件层级的内容了。我们已经将数据仓库分为三层，分别是ODS数据引入层、CDM数据公共层和ADS数据应用层，现在我们需要根据业务来逐渐将这三个层面给丰富起来。首先由下到上需要先构建ODS层，那么本章内容我们就来了解ODS数据引入层的搭建规范和对应需求业务的搭建流程。

数据引入层（ODS）

在前面的文章已经将ODS这一层的具体概念和框架都讲得十分清晰了，ODS（Operational Data Store）层存放从业务系统获取的最原始的数据，是其他上层数据的源数据。业务数据系统中的数据通常为非常细节的数据，经过长时间累积，且访问频率很高，是面向应用的数据。

ODS层设计规范

表命名规范

表命名规则：ODS_业态简称+系统入仓序号_源系统数据库表名_加工频率+抽取方式

实例表明	实例表说明
ods_ads01_bill_df	ods为模型层次、ads为业态、01为业态下的系统入仓序号、bill代表数据源表名、d代表加工频率、f代表全量抽取方式

其中需要理解的是加工频率和抽取方式，加工频率也可以说是聚合计算一次周期时长，一般来说我们默认采用简写字段来代表：

聚合粒度以及加工频率字段说明

字段中文	字段	字段全称	说明
日	d	day	每天
周	w	week	每周
月	m	month	每月
年	y	year	每年
小时	h	hour	每小时
半小时	hh	halfhour	每半小时

抽取字段则为是不是全量、增量还是是否有分区限制抽取：

抽取方式	字段	字段全称
分区增量表	i	incremental
分区全量表	f	full
非分区全量表	a	all
拉链表	c	chain

这里可以留意一下分区信息，一般拉取的原表都是一张大表没有分区，拉到ODS的时候一般都需要和之前的源表做一致的分区处理，使得切换无感。为了满足历史数据分析需求，可以在ODS层表中添加时间维度作为分区字段。实际应用中，可以选择采用增量、全量存储或拉链存储的方式。

增量存储

以天为单位的增量存储，以业务日期作为分区，每个分区存放日增量的业务数据。举例如下：

1月1日，用户A访问了A公司电商店铺B，A公司电商日志产生一条记录t1。1月2日，用户A又访问了A公司电商店铺C，A公司电商日志产生一条记录t2。采用增量存储方式，t1将存储在1月1日这个分区中，t2将存储在1月2日这个分区中。
1月1日，用户A在A公司电商网购买了B商品，交易日志将生成一条记录t1。1月2日，用户A又将B商品退货了，交易日志将更新t1记录。采用增量存储方式，初始购买的t1记录将存储在1月1日这个分区中，更新后的t1将存储在1月2日这个分区中。

交易、日志等事务性较强的ODS表适合增量存储方式。这类表数据量较大，采用全量存储的方式存储成本压力大。此外，这类表的下游应用对于历史全量数据访问的需求较小（此类需求可通过数据仓库后续汇总后得到）。例如，日志类ODS表没有数据更新的业务过程，因此所有增量分区UNION在一起就是一份全量数据。

全量存储

以天为单位的全量存储，以业务日期作为分区，每个分区存放截止到业务日期为止的全量业务数据。例如，1月1日，卖家A在A公司电商网发布了B、C两个商品，前端商品表将生成两条记录t1、t2。1月2日，卖家A将B商品下架了，同时又发布了商品D，前端商品表将更新记录t1，同时新生成记录t3。采用全量存储方式，在1月1日这个分区中存储t1和t2两条记录，在1月2日这个分区中存储更新后的t1以及t2、t3记录。

对于小数据量的缓慢变化维度数据，例如商品类目，可直接使用全量存储。

拉链存储

拉链存储通过新增两个时间戳字段（start_dt和end_dt），将所有以天为粒度的变更数据都记录下来，通常分区字段也是这两个时间戳字段。

拉链存储举例如下。

商品	start_dt	end_dt	卖家	状态
B	20160101	20160102	A	上架
C	20160101	30001231	A	上架
B	20160102	30001231	A	下架

这样，下游应用可以通过限制时间戳字段来获取历史数据。例如，用户访问1月1日数据，只需限制start_dt<=20160101并且 end_dt>20160101。

数据存储及生命周期管理规范

数据表类型	存储方式	最长存储保留策略
ODS流水型全量表	按天分区	不可再生情况下，永久保存。日志（数据量非常大，例如一天数据量大于100 GB）数据保留24个月。自主设置是否保留历史月初数据。自主设置是否保留特殊日期数据。
ODS镜像型全量表	按天分区	重要的业务表及需要保留历史的表视情况保存。ODS全量表的默认生命周期为2天，支持通过`ds=max_pt(tablename)`方式访问数据。
ODS增量表	按天分区	有对应全量表，最多保留最近14天分区数据。无对应全量表，需要永久保留数据。
ODS ETL过程临时表	按天分区	最多保留最近7天分区。
DBSync非去重数据	按天分区	由应用通过中间层保留历史数据，默认ODS层不保留历史数据。

数据引入层表设计

那么我们采用电商数据这类我们较为熟悉的数据来进行构建，在ODS层主要包括的数据有：交易系统订单详情、用户信息详情、商品详情等。这些数据未经处理，是最原始的数据。逻辑上，这些数据都是以二维表的形式存储。虽然严格的说ODS层不属于数仓建模的范畴，但是合理的规划ODS层并做好数据同步也非常重要。

ODS层数据不能直接被应用层任务引用。如果DWD和DWS层没有沉淀的ODS层数据，则通过ODS层创建视图的方式访问。命名规范遵从DWD或者DWS的命名规范，视图必须使用调度程序进行封装，保持视图的可维护性与可管理性。

记录用于拍卖的商品信息：s_auction。
记录用于正常售卖的商品信息：s_sale。
记录用户详细信息：s_users_extra。
记录新增的商品成交订单信息：s_biz_order_delta。
记录新增的物流订单信息：s_logistics_order_delta。
记录新增的支付订单信息：s_pay_order_delta。

表或字段命名尽量和业务系统保持一致，但是需要通过额外的标识来区分增量和全量表。其中使用maxcompute进行展示，MaxCompute表的生命周期（Lifecycle），指表（分区）数据从最后一次更新的时间算起，在经过指定的时间后没有变动，则此表（分区）将被MaxCompute自动回收，这个指定的时间就是生命周期。生命周期回收为每天定时启动，扫描全量分区。

建表示例

s_auction：

CREATE TABLE IF NPT EXISTS s_auction
(id	STRING COMMENT '商品ID',title	STRING COMMENT '商品名',gmt_modified                   STRING COMMENT '商品最后修改日期',price                          DOUBLE COMMENT '商品成交价格，单位元',starts                         STRING COMMENT '商品上架时间',minimum_bid                    DOUBLE COMMENT '拍卖商品起拍价，单位元',duration                       STRING COMMENT '有效期，销售周期，单位天',incrementnum                   DOUBLE COMMENT '拍卖价格的增价幅度',city                           STRING COMMENT '商品所在城市',prov                           STRING COMMENT '商品所在省份',ends                           STRING COMMENT '销售结束时间',quantity                       BIGINT COMMENT '数量',stuff_status                   BIGINT COMMENT '商品新旧程度 0 全新 1 闲置 2 二手',auction_status                 BIGINT COMMENT '商品状态 0 正常 1 用户删除 2 下架 3 从未上架',cate_id                        BIGINT COMMENT '商品类目ID',cate_name                      STRING COMMENT '商品类目名称',commodity_id                   BIGINT COMMENT '品类ID',commodity_name                 STRING COMMENT '品类名称',umid                           STRING COMMENT '买家umid'
)
COMMENT '商品拍卖ODS'
PARTITIONED BY (ds         STRING COMMENT '格式：YYYYMMDD')
LIFECYCLE 400;

s_sale：

CREATE TABLE IF NOT EXISTS s_sale
(id                             STRING COMMENT '商品ID',title                          STRING COMMENT '商品名',gmt_modified                   STRING COMMENT '商品最后修改日期',starts                         STRING COMMENT '商品上架时间',price                          DOUBLE COMMENT '商品价格，单位元',city                           STRING COMMENT '商品所在城市',prov                           STRING COMMENT '商品所在省份',quantity                       BIGINT COMMENT '数量',stuff_status                   BIGINT COMMENT '商品新旧程度 0 全新 1 闲置 2 二手',auction_status                 BIGINT COMMENT '商品状态 0 正常 1 用户删除 2 下架 3 从未上架',cate_id                        BIGINT COMMENT '商品类目ID',cate_name                      STRING COMMENT '商品类目名称',commodity_id                   BIGINT COMMENT '品类ID',commodity_name                 STRING COMMENT '品类名称',umid                           STRING COMMENT '买家umid'
)
COMMENT '商品正常购买ODS'
PARTITIONED BY (ds      STRING COMMENT '格式：YYYYMMDD')
LIFECYCLE 400;

s_users_extra:

CREATE TABLE IF NOT EXISTS s_users_extra
(id                STRING COMMENT '用户ID',logincount        BIGINT COMMENT '登录次数',buyer_goodnum     BIGINT COMMENT '作为买家的好评数',seller_goodnum    BIGINT COMMENT '作为卖家的好评数',level_type        BIGINT COMMENT '1 一级店铺 2 二级店铺 3 三级店铺',promoted_num      BIGINT COMMENT '1 A级服务　2 B级服务　3 C级服务',gmt_create        STRING COMMENT '创建时间',order_id          BIGINT COMMENT '订单ID',buyer_id          BIGINT COMMENT '买家ID',buyer_nick        STRING COMMENT '买家昵称',buyer_star_id     BIGINT COMMENT '买家星级 ID',seller_id         BIGINT COMMENT '卖家ID',seller_nick       STRING COMMENT '卖家昵称',seller_star_id    BIGINT COMMENT '卖家星级ID',shop_id           BIGINT COMMENT '店铺ID',shop_name         STRING COMMENT '店铺名称'
)
COMMENT '用户扩展表'
PARTITIONED BY (ds       STRING COMMENT 'yyyymmdd')
LIFECYCLE 400;

s_biz_order_delta：

CREATE TABLE IF NOT EXISTS s_biz_order_delta
(biz_order_id         STRING COMMENT '订单ID',pay_order_id         STRING COMMENT '支付订单ID',logistics_order_id   STRING COMMENT '物流订单ID',buyer_nick           STRING COMMENT '买家昵称',buyer_id             STRING COMMENT '买家ID',seller_nick          STRING COMMENT '卖家昵称',seller_id            STRING COMMENT '卖家ID',auction_id           STRING COMMENT '商品ID',auction_title        STRING COMMENT '商品标题 ',auction_price        DOUBLE COMMENT '商品价格',buy_amount           BIGINT COMMENT '购买数量',buy_fee              BIGINT COMMENT '购买金额',pay_status           BIGINT COMMENT '支付状态 1 未付款  2 已付款 3 已退款',logistics_id         BIGINT COMMENT '物流ID',mord_cod_status      BIGINT COMMENT '物流状态 0 初始状态 1 接单成功 2 接单超时3 揽收成功 4揽收失败 5 签收成功 6 签收失败 7 用户取消物流订单',status               BIGINT COMMENT '状态 0 订单正常 1 订单不可见',sub_biz_type         BIGINT COMMENT '业务类型 1 拍卖 2 购买',end_time             STRING COMMENT '交易结束时间',shop_id              BIGINT COMMENT '店铺ID'
)
COMMENT '交易成功订单日增量表'
PARTITIONED BY (ds       STRING COMMENT 'yyyymmdd')
LIFECYCLE 7200;

s_logistices_order_delta：

CREATE TABLE IF NOT EXISTS s_logistics_order_delta
(logistics_order_id STRING COMMENT '物流订单ID ',post_fee           DOUBLE COMMENT '物流费用',address            STRING COMMENT '收货地址',full_name          STRING COMMENT '收货人全名',mobile_phone       STRING COMMENT '移动电话',prov               STRING COMMENT '省份',prov_code          STRING COMMENT '省份ID',city               STRING COMMENT '市',city_code          STRING COMMENT '城市ID',logistics_status   BIGINT COMMENT '物流状态
1 - 未发货
2 - 已发货
3 - 已收货
4 - 已退货
5 - 配货中',consign_time       STRING COMMENT '发货时间',gmt_create         STRING COMMENT '订单创建时间',shipping           BIGINT COMMENT '发货方式
1，平邮
2，快递
3，EMS',seller_id          STRING COMMENT '卖家ID',buyer_id           STRING COMMENT '买家ID'
)
COMMENT '交易物流订单日增量表'
PARTITIONED BY (ds                 STRING COMMENT '日期')
LIFECYCLE 7200;