談?wù)剶?shù)據(jù)倉庫中的數(shù)據(jù)建模
數(shù)據(jù)建模是創(chuàng)建數(shù)據(jù)及其在組織或系統(tǒng)內(nèi)的關(guān)系的概念表示的過程。維度建模是一種以用戶直觀且易于理解的方式呈現(xiàn)數(shù)據(jù)。它還提供高性能訪問、靈活性和可擴(kuò)展性，以適應(yīng)業(yè)務(wù)需求的變化。
在本文中，我們將深入概述數(shù)據(jù)建模，特別關(guān)注 Kimball 的方法。此外，還將介紹用于以用戶友好且直觀的方式呈現(xiàn)數(shù)據(jù)的其他技術(shù)。現(xiàn)代數(shù)據(jù)倉庫的一項(xiàng)特別有趣的技術(shù)是將數(shù)據(jù)存儲在一個寬表中，盡管這種方法可能并不適合所有查詢引擎。我們還將介紹可在數(shù)據(jù)倉庫、數(shù)據(jù)湖、數(shù)據(jù)湖屋等中使用的技術(shù)。但是，為特定用例和查詢引擎選擇適當(dāng)?shù)姆椒ǚ浅Ｖ匾?/p>
什么是維度建模
每個維度模型都由一個或多個具有多部鍵的表（稱為事實(shí)表）以及一組稱為維度表的表組成。每個維度表都有一個主鍵，該主鍵與事實(shí)表中多部分鍵的組成部分之一精確對應(yīng)。這種獨(dú)特的結(jié)構(gòu)通常稱為星型模式。在某些情況下，可以使用稱為雪花模式的更復(fù)雜的結(jié)構(gòu)，其中維度表連接到較小的維度表
維度建模的好處
維度建模提供了一種實(shí)用且有效的方法來組織和分析數(shù)據(jù)，從而帶來以下好處：
- 業(yè)務(wù)用戶簡單易懂。
- 改進(jìn)的查詢性能可加快數(shù)據(jù)檢索速度。
- 靈活性和可擴(kuò)展性，以適應(yīng)不斷變化的業(yè)務(wù)需求。
- 確保跨多個來源的數(shù)據(jù)一致性和集成。
- 增強(qiáng)的用戶采用率和自助服務(wù)分析。
既然我們已經(jīng)討論了維度建模是什么以及它給組織帶來的價值，那么讓我們探討一下如何有效地利用它。
數(shù)據(jù)和維度建模方法
雖然我打算主要關(guān)注 Kimball 的方法，但在深入探討之前，讓我們簡要介紹一下其他一些流行的技術(shù)。
Inmon方法論
Inmon 建議在數(shù)據(jù)倉庫中使用標(biāo)準(zhǔn)化數(shù)據(jù)模型。該方法支持?jǐn)?shù)據(jù)集市的創(chuàng)建。這些數(shù)據(jù)集市是數(shù)據(jù)倉庫的較小的專門子集，可滿足特定業(yè)務(wù)領(lǐng)域或用戶組的需求。這些旨在為特定業(yè)務(wù)職能或部門提供更加定制和高效的數(shù)據(jù)訪問體驗(yàn)。
數(shù)據(jù)拱頂
Data Vault 是一種專注于可擴(kuò)展性、靈活性和可追溯性的建模方法。它由三個核心組件組成：Hub、Link 和 Satellite。
中心表
中心表是所有不同實(shí)體的集合。例如，帳戶中心將包括帳戶、account_ID、load_date 和 src_name。這使我們能夠跟蹤記錄加載時的原始位置，以及是否需要從業(yè)務(wù)密鑰生成的代理密鑰。
鏈接表
鏈接表在中心表之間建立關(guān)系并捕獲不同實(shí)體之間的關(guān)聯(lián)。它們包含相關(guān)中心表的外鍵，從而能夠創(chuàng)建多對多關(guān)系。
衛(wèi)星表
衛(wèi)星表存儲有關(guān)中心的描述信息，提供附加上下文和屬性。它們包括歷史數(shù)據(jù)、審計信息以及與特定時間點(diǎn)相關(guān)的其他相關(guān)屬性。
Data Vault 的設(shè)計允許靈活且可擴(kuò)展的數(shù)據(jù)倉庫架構(gòu)。它促進(jìn)數(shù)據(jù)可追溯性、可審計性和歷史跟蹤。這使得它適合數(shù)據(jù)集成和敏捷性至關(guān)重要的場景，例如在高度監(jiān)管的行業(yè)或快速變化的業(yè)務(wù)環(huán)境中。
單個大表
大表將數(shù)據(jù)存儲在一張寬表中。使用一張大表或非規(guī)范化表可以簡化查詢、提高性能并簡化數(shù)據(jù)分析。它消除了復(fù)雜連接的需要，簡化了數(shù)據(jù)集成，并且在某些情況下可能是有益的。然而，它可能會導(dǎo)致冗余、數(shù)據(jù)完整性挑戰(zhàn)以及維護(hù)復(fù)雜性增加。在選擇單個大表之前請考慮具體要求。
AND交易AS (
SELECT 1000001 AS order_id, TIMESTAMP ( '2017-12-18 15:02:00' ) AS order_time,
STRUCT( 65401 AS id, 'John Doe' AS name, 'Norway' AS location) AS customer,
[
STRUCT( 'xxx123456' AS sku, 3 AS數(shù)量, 1.3 AS價格),
STRUCT( 'xxx535522' AS sku, 6 AS數(shù)量, 500.4 AS價格),
STRUCT( 'xxx762222' AS sku, 4 AS數(shù)量, 123.6 AS價格)
] AS訂單
UNION ALL
SELECT 1000002 , TIMESTAMP ( '2017-12-16 11:34:00' ),
STRUCT( 74682 , 'Jane Smith' , '波蘭' ) AS客戶,
[
STRUCT( 'xxx635354' , 4 , 345.7 ),
STRUCT( 'xxx828822' , 2 , 9.5 )
] AS訂單
)從交易中選擇 *
對于一張寬表，我們不需要連接表。我們可以只用一張表來匯總數(shù)據(jù)并進(jìn)行分析。此方法可提高 BigQuery 的性能。
從交易 t、UNNEST (t.orders)中選擇customer.name、sum (a.quantity)作為按 customer.name分組。
Kimball方法論
Kimball 方法非常強(qiáng)調(diào)創(chuàng)建稱為數(shù)據(jù)倉庫的集中式數(shù)據(jù)存儲庫。該數(shù)據(jù)倉庫作為單一事實(shí)來源，以一致且結(jié)構(gòu)化的方式集成和存儲來自各種業(yè)務(wù)系統(tǒng)的數(shù)據(jù)。
該方法為設(shè)計、開發(fā)和實(shí)施數(shù)據(jù)倉庫系統(tǒng)提供了一套全面的指南和最佳實(shí)踐。它非常重視創(chuàng)建維度數(shù)據(jù)模型，并優(yōu)先考慮簡單性、靈活性和易用性。現(xiàn)在，讓我們深入研究 Kimball 方法的關(guān)鍵原則和組成部分。
實(shí)體模型到維度模型
在我們的數(shù)據(jù)倉庫中，數(shù)據(jù)源通常位于實(shí)體模型中，這些實(shí)體模型被規(guī)范化為多個表，其中包含應(yīng)用程序的業(yè)務(wù)邏輯。在這種情況下，這可能具有挑戰(zhàn)性，因?yàn)樾枰私獗砗偷讓訕I(yè)務(wù)邏輯之間的依賴關(guān)系。創(chuàng)建分析報告或生成統(tǒng)計數(shù)據(jù)通常需要連接多個表。
要創(chuàng)建維度模型，數(shù)據(jù)需要經(jīng)過提取、轉(zhuǎn)換和加載 (ETL) 過程，將其非規(guī)范化為星型模式或雪花模式。此過程中的關(guān)鍵活動包括識別事實(shí)表和維度表以及定義粒度。粒度決定了事實(shí)表中存儲的詳細(xì)程度。例如，可以每小時或每天聚合交易。
假設(shè)我們有一家銷售自行車和自行車配件的公司。在這種情況下，我們有以下信息：
- 交易
- 商店
- 客戶
- 產(chǎn)品
根據(jù)我們的業(yè)務(wù)知識，我們知道我們需要收集有關(guān)銷售量、一段時間內(nèi)的數(shù)量以及按地區(qū)、客戶和產(chǎn)品細(xì)分的信息。有了這些信息，我們就可以設(shè)計我們的數(shù)據(jù)模型。交易表將充當(dāng)我們的事實(shí)表，商店、客戶和產(chǎn)品表將充當(dāng)維度表。
事實(shí)表
事實(shí)表通常表示業(yè)務(wù)事件或事務(wù)，并包括與該事件關(guān)聯(lián)的度量或度量。這些指標(biāo)可以包含各種數(shù)據(jù)點(diǎn)，例如銷售額、銷售數(shù)量、客戶互動、網(wǎng)站點(diǎn)擊或任何其他可提供業(yè)務(wù)績效洞察的可衡量數(shù)據(jù)。事實(shí)表還包括與維度表建立關(guān)系的外鍵列。
事實(shí)表設(shè)計的最佳實(shí)踐是將所有外鍵放在表的頂部，然后進(jìn)行測量。
事實(shí)表類型
- 事務(wù)事實(shí)表提供了最低級別的粒度，因?yàn)橐恍写硎聞?wù)系統(tǒng)中的一條記錄。數(shù)據(jù)每天或?qū)崟r刷新。
- 定期快照事實(shí)表捕獲某個時間點(diǎn)（例如月底）事實(shí)表的快照。
- 累積快照事實(shí)表總結(jié)了流程開始和結(jié)束之間的可預(yù)測步驟中發(fā)生的測量事件。
- 無事實(shí)事實(shí)表保存有關(guān)發(fā)生的事件的信息，無需任何指標(biāo)。
維度表
維度表是維度建模中的一種表，包含描述性屬性，例如有關(guān)產(chǎn)品、其類別和類型的信息。維度表為事實(shí)表中存儲的定量數(shù)據(jù)提供上下文和視角。
維度表包含一個唯一鍵，用于標(biāo)識表中的每條記錄，稱為代理鍵。該表可以包含業(yè)務(wù)鍵，該業(yè)務(wù)鍵是來自源系統(tǒng)的鍵。一個好的做法是生成代理鍵而不是使用業(yè)務(wù)鍵。
創(chuàng)建代理鍵有多種方法：
- 哈希：可以使用 MD5、SHA256 等哈希函數(shù)（例如 md5(key_1, key_2, key_3) ）生成代理鍵。
- 遞增：使用始終遞增的數(shù)字生成的代理鍵（例如 row_number()、identity）。
- 連接：通過連接唯一鍵列生成的代理鍵（例如 concat(key_1, key_2, key_3) ）。
- -Unique generated：使用生成唯一標(biāo)識符的函數(shù)（例如GENERATE_UUID()）生成的代理鍵
選擇的方法取決于用于處理和存儲數(shù)據(jù)的引擎。它會影響查詢數(shù)據(jù)的性能。
維度表通常包含層次結(jié)構(gòu)。
a) 例如，父子層次結(jié)構(gòu)可用于表示員工與其經(jīng)理之間的關(guān)系。
b) 屬性之間的層次關(guān)系。例如，時間維度可能具有年、季、月、日等屬性，形成層次結(jié)構(gòu)。
維度表的類型
一致維度：
一致維度是可以被多個事實(shí)表使用的維度。例如，區(qū)域表可以由不同的事實(shí)表使用。
退化維度：
當(dāng)屬性存儲在事實(shí)表而不是維度表中時，就會出現(xiàn)退化維度。例如，可以在事實(shí)表中找到交易號。
垃圾維度：
該屬性包含不適合現(xiàn)有維度表的無意義屬性，或者是表示各種狀態(tài)組合的標(biāo)志和二進(jìn)制值的組合。
同一扮演維度：
同一維度鍵在事實(shí)表中包含多個外鍵。例如，日期維度可以引用事實(shí)表中的不同日期，例如創(chuàng)建日期、訂單日期和交貨日期。
靜態(tài)維度：
靜態(tài)維度是通常永不改變的維度。它可以從參考數(shù)據(jù)加載，無需更新。一個例子是公司的分支機(jī)構(gòu)列表。
橋接表：
當(dāng)事實(shí)表和維度表之間存在一對多關(guān)系時，將使用橋接表。
緩慢變化的維度
緩慢變化的維度（SCD）是維度建模中的一個概念。它處理維度表中維度屬性隨時間的變化。SCD 提供了一種機(jī)制，用于在業(yè)務(wù)實(shí)體發(fā)展及其屬性變化時維護(hù)維度表中的歷史和當(dāng)前數(shù)據(jù)。SCD 有六種類型，但最流行的三種是：
- SCD 類型 0：在此類型中，僅將新記錄導(dǎo)入到維度表中，而不進(jìn)行任何更新。
- SCD 類型 1：在此類型中，將新記錄導(dǎo)入維度表，并更新現(xiàn)有記錄。
- SCD 類型 2：在此類型中，導(dǎo)入新記錄，并為更改的屬性創(chuàng)建具有新值的新記錄。
例如，當(dāng) John Smith 搬到另一個城市時，我們使用 SCD Type 2 來保存與倫敦相關(guān)的交易信息。在本例中，我們創(chuàng)建一條新記錄并更新前一條記錄。因此，歷史報告將保留他在倫敦購買的信息。
MERGE INTO client AS tgt
USING (
SELECT
Client_id,
Name,
Surname,
City
GETDATE() AS ValidFrom
' 20199 -01 -01 ' AS ValidTo
from client_stg
) AS src
ON (tgt.Clinet_id = src.Clinet_id AND tgt.iscurrent = 1 )匹配后更新設(shè)置 tgt.iscurrent = 0 , ValidTo = GETDATE()，當(dāng) 不匹配時
INSERT (Client_id, name, Surname, City, ValidFrom, ValidTo, iscurrent)
VALUES (Client_id, name, Surname, City, ValidFrom, ValidTo, 1 );這就是當(dāng)我們將新值和以前的值保留在不同的列中時 SCD 3 的外觀。
星型模式與雪花型模式
設(shè)計數(shù)據(jù)倉庫最流行的方法是使用星型模式或雪花模式。星型模式具有事實(shí)表和與事實(shí)表相關(guān)的維度表。在星型模式中，存在事實(shí)表和與事實(shí)表直接相關(guān)的維度表。另一方面，雪花模式由事實(shí)表、與事實(shí)表相關(guān)的維度表以及與這些維度表相關(guān)的附加維度組成。
這兩種設(shè)計之間的主要區(qū)別在于它們的標(biāo)準(zhǔn)化方法。星型模式保持?jǐn)?shù)據(jù)非規(guī)范化，而雪花模式確保規(guī)范化。星型模式旨在提高查詢性能。雪花模式是專門為處理大維度的更新而定制的。如果您在更新大量維度表時遇到挑戰(zhàn)，請考慮轉(zhuǎn)換到雪花模式。
數(shù)據(jù)加載策略
在我們的數(shù)據(jù)倉庫、數(shù)據(jù)湖和數(shù)據(jù)湖屋中，我們可以有各種加載策略，例如：
全量加載：全量加載策略是將源系統(tǒng)中的所有數(shù)據(jù)加載到數(shù)據(jù)倉庫中。此策略通常用于出現(xiàn)性能問題或缺少可以通知行修改的列的情況。
Incremental Load：增量加載策略涉及僅加載自上次數(shù)據(jù)加載以來的新數(shù)據(jù)。如果源系統(tǒng)中的行無法更改，我們可以根據(jù)唯一標(biāo)識符或創(chuàng)建日期僅加載新記錄。我們需要定義一個“水印”，用于選擇新行。
Delta Load：Delta加載策略側(cè)重于僅加載自上次加載以來已更改或增量的記錄。它與增量加載的不同之處在于它專門針對增量更改而不是所有新記錄。Delta 加載策略在處理大量數(shù)據(jù)更改時非常高效，并可顯著減少所需的處理時間和資源。
加載數(shù)據(jù)的最常見策略是先填充維度表，然后填充事實(shí)表。這里的順序很重要，因?yàn)槲覀冃枰褂檬聦?shí)表中維度表的主鍵來創(chuàng)建表之間的關(guān)系。有一個例外，當(dāng)我們需要在維度表之前加載事實(shí)表時，這種技術(shù)名稱就是延遲到達(dá)維度。
在這種技術(shù)中，我們可以在維度表中創(chuàng)建代理鍵，并在填充事實(shí)表后通過 ETL 過程更新它。
