文档

数据引入层(ODS)

更新时间:

基于阿里巴巴OneData方法论最佳实践,ODS层存放您从业务系统获取的最原始的数据,是其他上层数据的源数据。业务数据系统中的数据通常为长期累积的、非常细节的数据,且访问频率很高,是面向应用的数据。

数据引入层表设计

本教程中,在ODS层主要包括的数据有:交易系统订单详情、用户信息详情、商品详情等。这些数据未经处理,是最原始的数据。在逻辑层面上,这些数据都是以二维表的形式存储。严格地说,虽然ODS层不属于数仓建模的范畴,但是合理地规划ODS层并做好数据同步也非常重要。本教程中,使用了6张ODS表:

  • 记录用于拍卖的商品信息:s_auction。

  • 记录用于正常售卖的商品信息:s_sale。

  • 记录用户详细信息:s_users_extra。

  • 记录新增的商品成交订单信息:s_biz_order_delta。

  • 记录新增的物流订单信息:s_logistics_order_delta。

  • 记录新增的支付订单信息:s_pay_order_delta。

说明
  • 表或字段命名尽量和业务系统保持一致,但是需要通过额外的标识来区分增量和全量表。在Dataphin中,di后缀的事实模型为增量表(事务型),df后缀的事实模型为全量表(周期快照型)。

  • 命名时需要特别注意冲突处理。例如,不同业务系统的表可能是同一个名称,为区分两个不同的表,您可以将这两个同名表的来源数据库名称作为后缀或前缀。例如,表中某些字段的名称刚好和关键字重名了,可以通过规范定义后缀添加_col1解决。

建表示例

通过即席查询功能,您可以编写SQL语句创建所需的ODS表。为方便您使用,集中为您提供建表语句如下。

CREATE TABLE IF NOT 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;


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;


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;


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;


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;


CREATE TABLE IF NOT EXISTS s_pay_order_delta
(
    pay_order_id          STRING COMMENT '支付订单ID',
    total_fee             DOUBLE COMMENT '应支付总金额(数量*单价)',
    seller_id             STRING COMMENT '卖家ID',
    buyer_id              STRING COMMENT '买家iD',
    pay_status            BIGINT COMMENT '支付状态:1等待买家付款 2等待卖家发货 3交易成功',
    pay_time              STRING COMMENT '付款时间',
    gmt_create            STRING COMMENT '订单创建时间',
    refund_fee            DOUBLE COMMENT '退款金额(包含运费)',
    confirm_paid_fee      DOUBLE COMMENT '已经确认收货的金额'
)
COMMENT '交易支付订单增量表'
PARTITIONED BY (ds        STRING COMMENT '日期')
LIFECYCLE 7200;

数据同步加载与处理

ODS的数据需要由各数据源系统同步、存储到MaxCompute,才能用于进一步的数据开发。本教程建议您使用Dataphin的数据集成功能完成数据同步。在使用数据引入功能的过程中,建议您遵循以下规范:

  • 一个系统的源表只允许同步一次到MaxCompute,保持表结构的一致性。

  • 数据引入支持全量数据同步、实时增量数据同步(分钟或小时调度实现)两种同步方式。

  • ODS层的表建议以统计日期及时间分区表的方式存储,便于管理数据的存储成本和策略控制,Dataphin中默认时间分区的名字为ds。

  • 数据引入支持手动调整源表和目标表的同步字段。

    • 如果源表字段在目标表中不存在,用户需手动添加目标字段,或删除源表字段。

    • 如果源表字段与目标表字段不匹配,用户需先删除目标字段,然后重新添加与之匹配的字段。