回覆列表
  • 1 # 種豆大叔

    1 緩慢變化維

    說到歷史拉鍊表,首先得說下緩慢變化維。

    在現實世界中,維度的屬性並不是靜態的,而是隨著時間的變化而變化,這也體現了資料倉庫的特點之一,是反映歷史變化的。相對於資料增長較為快速的事實表,維度的變化是相對緩慢的。

    在維度建模理論中,處理緩慢變化維有三種方式:

    新的維度屬性直接覆蓋舊的維度屬性,不保留歷史資料;

    增加新的維度行(需要生成代理鍵來支援),維度變化前的事實關聯變化前的維度值,維度變化後的事實關聯變化後的維度值。缺點是無法歸一為變化前的維度值或者變化後的維度值進行統計;

    增加維度列,即針對維度的某一屬性時,在設計表時需要至少包含兩列,新屬性和舊屬性。優點是可以根據業務需求進行不同的歸一化處理,缺點是擴充套件性不好,保留的維度歷史資料有限。

    2 歷史拉鍊表

    而歷史拉鍊儲存恰恰是對第二種方式的一種升級,同樣是以增加新的維度行來實現,不同的是使用時間鍵來代替代理鍵。時間鍵包含兩個欄位,開始時間和結束時間,一般以天為粒度保留變更的維度資料。

    2.1 查詢方式

    查詢當前最新狀態維度資料:select * from table_name where end_day = ‘30001231’

    查詢某一天的維度狀態資料:select * from table_name where start_day <= ‘20200201’ and end_day > "20200201"

    2.2 加工方式

    假設商品歷史拉鍊表(goods_hist)有如下5個欄位:goods_id(商品編號)、price(商品價格)、is_on_sale(商品是否在售)、start_day(開始日期)、end_day(結束日期)

    商品最新全量快照表(goods_cur)有如下3個欄位:goods_id(商品編號)、price(商品價格)、is_on_sale(商品是否在售),快照日期為20200201

    則SQL加工語句為:

    WITH hist AS

    (

    SELECT goods_id,

    price,

    is_on_sale,

    start_day

    FROM goods_hist

    WHERE end_day = 30001231

    ),

    cur AS

    (

    SELECT nvl(goods_id,-1) AS goods_id,

    nvl(price,-1) AS price,

    nvl(is_on_sale,-1) AS is_on_sale

    FROM goods_cur

    )

    SELECT nvl(cur.goods_id,hist.goods_id) AS goods_id,

    nvl(cur.price,hist.price) AS price,

    nvl(cur.is_on_sale,hist.is_on_sale) AS is_on_sale,

    nvl(hist.start_day,20200201) AS start_day,

    CASE

    WHEN cur.goods_id IS NULL THEN 20200201

    ELSE 30001231

    END AS end_day

    FROM cur

    FULL OUTER JOIN hist

    ON cur.goods_id = hist.goods_id

    AND cur.price = hist.price

    AND cur.is_on_sale = hist.is_on_sale

    SQL語句輸出的結果包括兩部分:

    end_day=30001231的最新狀態維度資料

    end_day=20200201的已失效的維度資料

    2.3 分割槽方式

    方式1(使用start_day作為分割槽鍵):缺點是查詢最新資料無法走分割槽;查詢某一天資料時end_day限制條件無法走分割槽;加工歷史拉鍊表資料時,end_day=30001231的結果資料不方便入庫

    方式2(使用end_day作為分割槽鍵):缺點是查詢某一天資料時start_day限制條件無法走分割槽;優點是加工歷史拉鍊表資料時,結果資料入庫方便,直接insert overwrite覆蓋分割槽30001231和20200201即可

    方式3(使用start_day和end_day作為聯合分割槽鍵,start_day為父分割槽):查詢最新資料時需要改變下SQL語句,不然無法走分割槽(比如當前日期是20200401,SQL語句需改為select * from table_name where start_day <= ‘20200401’ and end_day > "20200401",即查詢某一天資料的寫法);缺點是加工歷史拉鍊表資料時,end_day=30001231和end_day=20200201的結果資料都不方便入庫;而且分割槽數會越來越多,一年下來最多可能產生365*364/2=66430個分割槽;優點是查詢資料時start_day和end_day的限制條件都可以走分割槽

    方式4(使用start_day和end_day作為聯合分割槽鍵,end_day為父分割槽):缺點同方式3,但加工歷史拉鍊表資料時,結果資料入庫相對方便(首先將結果資料存入臨時表,然後清空拉鍊表的分割槽end_day=30001231和end_day=20200201,最後將臨時表資料以insert into方式入庫);優點同方式3

    綜上所述,分割槽方式可在2和4中選擇。

    選擇方式4,需要考慮隨著時間的推移,分割槽數量會越來越多。可考慮定期重構歷史拉鍊表,比如在每個月月初強制重新開始做歷史拉鍊表(比如在20200401時,先將end_day=30001231的資料修改為end_day=20200401,再基於最新全量快照表生成一份start_day=20200401,end_day=30001231的資料)。

    2.4 注意點

    設計歷史拉鍊表時,需要移除變化頻率高的維度屬性,不然生成新拉鍊的機率會很高,導致無法達到節省儲存的目的。

  • 2 # 雲計算競品分析

    拉鍊表是針對資料倉庫設計中表儲存資料的方式而定義的,顧名思義,所謂拉鍊,就是記錄歷史。記錄一個事物從開始,一直到當前狀態的所有變化的資訊。

    拉鍊表如果資料量過多導致效能降低,可以考慮分表。

  • 中秋節和大豐收的關聯?
  • 鐳射打標的優點?