資料引入層（ODS）

ODS（Operational Data Store）層存放您從業務系統擷取的最原始的資料，是其他上層資料的源資料。業務資料系統中的資料通常為非常細節的資料，經過長時間累積，且通路頻率很高，是面向應用的資料。

資料引入層表設計

本教程中，在ODS層主要包括的資料有：交易系統訂單詳情、使用者資訊詳情、商品詳情等。這些資料未經處理，是最原始的資料。邏輯上，這些資料都是以二維表的形式存儲。雖然嚴格的說ODS層不屬于數倉模組化的範疇，但是合理的規劃ODS層并做好資料同步也非常重要。本教程中，使用了6張ODS表：

記錄用于拍賣的商品資訊：s_auction。
記錄用于正常售賣的商品資訊：s_sale。
記錄使用者詳細資訊：s_users_extra。
記錄新增的商品成交訂單資訊：s_biz_order_delta。
記錄新增的物流訂單資訊：s_logistics_order_delta。
記錄新增的支付訂單資訊：s_pay_order_delta。

說明

表或字段命名盡量和業務系統保持一緻，但是需要通過額外的辨別來區分增量和全量表。例如，我們通過_delta來辨別該表為增量表。
命名時需要特别注意沖突處理，例如不同業務系統的表可能是同一個名稱。為區分兩個不同的表，您可以将這兩個同名表的來源資料庫名稱作為字尾或字首。例如，表中某些字段的名稱剛好和關鍵字重名了，可以通過添加_col1字尾解決。

建表示例

為友善您使用，集中提供建表語句如下。

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層表中添加時間次元作為分區字段。實際應用中，您可以選擇采用增量、全量存儲或拉鍊存儲的方式。

增量存儲以天為機關的增量存儲，以業務日期作為分區，每個分區存放日增量的業務資料。舉例如下：
- 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

。

緩慢變化次元

MaxCompute不推薦使用代理鍵，推薦使用自然鍵作為次元主鍵，主要原因有兩點：

MaxCompute是分布式計算引擎，生成全局唯一的代理鍵工作量非常大。當遇到大資料量情況下，這項工作就會更加複雜，且沒有必要。
使用代理鍵會增加ETL的複雜性，進而增加ETL任務的開發和維護成本。

在不使用代理鍵的情況下，緩慢變化次元可以通過快照方式處理。

快照方式下資料的計算周期通常為每天一次。基于該周期，處理次元變化的方式為每天一份全量快照。

例如商品次元，每天保留一份全量商品快照資料。任意一天的事實表均可以取到當天的商品資訊，也可以取到最新的商品資訊，通過限定日期，采用自然鍵進行關聯即可。該方式的優勢主要有以下兩點：

處理緩慢變化次元的方式簡單有效，開發和維護成本低。
使用友善，易于理解。資料使用方隻需要限定日期即可取到當天的快照資料。任意一天的事實快照與任意一天的次元快照通過次元的自然鍵進行關聯即可。

該方法的弊端主要是存儲空間的極大浪費。例如某次元每天的變化量占總體資料量比例很低，極端情況下，每天無變化，這種情況下存儲浪費嚴重。該方法主要實作了通過犧牲存儲擷取ETL效率的優化和邏輯上的簡化。請避免過度使用該方法，且必須要有對應的資料生命周期制度，清除無用的曆史資料。

簡單搞定數倉搭建:數倉模型(ODS)資料引入層（ODS）

資料引入層（ODS）

資料引入層表設計

建表示例

資料引入層存儲

緩慢變化次元

繼續閱讀

pyspark調用spark以及執行帶in語句參數的hql示例

用寫sql的思路寫 pyspark

pyspark學習(一)—pyspark的安裝與基礎文法一 Pysaprk的安裝二：pyspark的簡單文法END

【Spark Mllib】K-均值聚類——電影類型K-均值聚類資料特征提取

一篇文章讓你精通Java JSP規範

世界因大資料而改變

Spark的RDD轉換算子-雙value型Spark的RDD轉換算子-雙value型

SparkSQL項目練習1 準備資料2 需求：各區域熱門商品Top3

延雲行業搜尋資料庫在大資料生态中位置和重要性大資料的挑戰大資料技術的現狀延雲行業搜尋資料庫

Spark在windows環境裡跑時報錯找不到org.apache.hadoop.fs.FSDataInputStream

Spark流式分析系統實作流式實時日志分析系統

Scala和Java二種方式實戰Spark Streaming開發

Spark基礎:Spark簡介及特點,運作模式,安裝Spark,Driver與Executor,Local模式,Standalone模式,Yarn模式,Mesos模式,WordCount案例,HA配置第1章 Spark概述第2章 Spark運作模式第3章案例實操

Spark實作wordcount

大資料排錯SparkSpark叢集啟動時候，JAVA_HOME is not sethadoop叢集，某台伺服器jps無任何輸出IDEAkafkahadoopspark sqlfile permissionsIDEA本地測試 - OutOfMemoryError: GC overhead limit exceededhdfs負載均衡

spark/scala關于【資源檔案】加載方法概述外部檔案加載方案測試資源檔案打包入jar包中小結