POSTGRESQL在數倉方面的應用博森瑞---石佛博森瑞-石佛應用統計學碩士[M.A.S]、高級數據分析師。曾在NTT(日本本部)與NEC(高級解決方案部門)工作。擅長使用POSTGRESQLORACLEHADOOP-HIVE等數據庫。并針對業務設計過多種數據倉庫、MPP架構的數據處理解決方案。演講概要：?數據倉庫入門?PostgreSQL在數據倉庫方面的優勢?從工作中說起PART01數據倉庫DATAWAREHOUSE數據倉庫數據倉庫（DataWarehouse）：數據倉庫是來自一個或多個不同源的數據的集中中央存儲庫。數據倉庫將當前和歷史數據存儲在一起，用于為整個企業的員工創建分析報告并進行相關的數據挖掘與數據分析?？梢赃@么說：它能滿足并提供相關的業務指導和改進方案讓公司得到更多的收益。在現代被視為商業智能的核心組件之一。那么從更廣義上講：數據倉庫是容納一個面向主題的，一致的，干凈的，集成了來自各種操作系統、各種文件和數據庫之間的數據組織形式。他的最大作用就是產生相關的數據報表與分析結果。這里我們需要特別注意的是：數據倉庫是在企業管理和決策中面向主題的、集成的、與時間相關的、不可修改的數據集合。數據修改將會為整體決策帶來不穩定性和更多的不確定性。一個簡單的數倉架構：ETL報表與分析數據倉庫的特點集成性：這里主要是通過ETL具體實現的。它未必是某種特定的工具，主要能對數據進行提取、凈化、轉換和裝載等操作即可。面向主題：針對具體業務和某個主題領域：交易產品客戶分析與相關的財務管理非易失性：數據載入后就可以進行相關的訪問。但數據倉庫里的數據不進行具體的更新（修改）操作。我們需要注意的是，時間會讓數據增多。我們應該謹慎的處理好數據與空間的關系。數據倉庫和數據庫的區別比較操作型（OLTP）分析性（OLAP/DSS）關注每個數據的本身數據集合的整體性模型實體–關系（E-R）星型構型或雪花構型操作SQL-DML操作SELECT

數據插入

謹慎的刪除操作粒度以數據和事務為粒度以表為粒度場景面向事務面向分析與挖掘數據量中等量或少量大量或海量需求實時事務操作實時與T+1的業務決策業務方向用戶日常操作企業整體分析數據倉庫的基本架構基礎層（數據整合層）：數據倉庫中的數據來源地。通過ETL手段或者直接向數倉中傳遞數據。ODS層（操作型數據存儲）：這一層主要是數據在進倉前做相關的更新操作。需按照具體業務去進行相關的數據更改，可以看成數倉的第二層數據清洗。因為，數倉不能進行相關更新操作，并且從基礎層傳遞上來的數據也未必符合相關的數據要求，雖然做了ETL但是有些數據未必更新的足夠充分，所以往往需要進行第二層業務級的補充清洗。DW層（數據倉庫層）：將數據按照業務邏輯進行分類存儲，由于有第一層和第二層的雙層清洗，所以這層的數據都是相對一致、準確、完整的。我們可以直接用這些倉內數據進行相關的報表級和分析級的操作。去指導整個公司的戰略決策。報表展示與決策層：這一層中主要產生相關的報表與進行企業級決策。一個進化的數倉架構ODSDATAWAREHOUSEDBFILEJSONXMLETL服務DBFILEJSONXMLETL服務ODS層數據服務層ODS層ODS庫DATAWAREHOUSE報表展示層決策層數據倉庫層元數據（METADATA）所謂的元數據指的是我們在數據倉庫建設中所產生的有關于數據的源定義，目標定義與源到目標的相關轉換規則的關鍵數據。而且元數據也往往包含了重要的商業與相關業務的關鍵信息。元數據主要分為：

--商業/業務元數據：專為管理或業務分析人員服務與使用，從業務或商業的角度去描述相關數據。主要包括一些重要的行業術語與行業規則。--技術元數據：專為技術人員服務。主要包含數據倉庫在開發、管理、維護中的描述數據。比如說：權限、對象描述與對象結構（二維表的列）和約束（主外鍵、唯一約束）等。并且所有的元數據在存儲中都要以數據庫與數據集合為主。數據倉庫建模注意事項：數據倉庫的建模其實和普通的數據庫建模在概念上相差無幾。但是我們需要注意以下幾點：1我們需要除去純粹用于操作型環境的數據，將這些數據交由ODS或者更前期的ETL層服務層進行處理。2在關鍵位置上我們需要增加諸如類似時間或者唯一事件的元素，增強數據倉庫的數據分層性。3

增強數據倉庫中數據的導入和導出功能。這部分主要是加強數據的相關吞吐?？梢栽黾訑祿}庫整體的實時性。4在創建表的時候盡可能的將冷熱數據按照信息生命周期分層。一個思考：在數據倉庫設計中我們是否還需要遵守三范式？!少年，關于三范式建議你盡量一定要遵守。而且在數倉中會有更多的范式規則在等待你?。?！干就完了！維度-Dimensional維度就是觀察問題的角度，即從哪個方面去分析問題。他有個重要的度量單位我們稱之為指標。所謂的指標就是從維度的基礎上去衡量與計算這個維度中的某個值，通常指標是一個聚合后的結果。比如說最大值最小值平均值均差方差求和等。而且基本都是連續的。維度一般是一個離散的值，比如時間維度上每一個獨立的日期或地域，因此在計算上我們才需要做聚合運算。

一個簡單的維度立方體事實表和維度表事實表和維度表是在設計數據倉庫過程中需要考慮的。

事實表是指存儲有事實記錄的表，如系統的日志、銷售記錄、用戶訪問日志等信息，事實表的記錄是動態的增長的，所以體積是大于維度表。

維度表也稱為查找表，是與事實表相對應的表，這個表保存了維度的屬性值，可以跟事實表做關聯，相當于是將事實表中經常重復的數據抽取、規范出來用一張表管理，常見的有日期（日、周、月、季度等屬性）、地區表等，所以維度表的變化通常不會太大。??維度表的存在縮小了事實表的大小，便于維度的管理和CURD維度的屬性，不必對事實表的大量記錄進行改動，并且可以給多個事實表重用。數據模型的建立業務模型：業務分解和程序化，確定好業務的邊界及業務流程，如訂單、支付都是一個單獨的業務模塊

領域模型：業務概念的抽象、分組，整理分組之間的關聯，比如用戶購物的業務，抽成一個更大的模型，這個模型一般相對于行業。

邏輯建模：領域模型中的業務概念實體化，并考慮實體的具體屬性及實體與實體之間的關系，比如訂單（訂單號、付款人…）和支付（金額、支付時間…）的關系。

物理模型：解決實際應用的落地開發、上線等問題，及性能等一些具體的技術問題。維度建模法-最常用的建模方式

維度建模法的特點在于需要進行大量的數據預處理、預計算，因此會導致大量的數據處理工作，而且業務發生變化，需要重新進行維度的定義時，往往需要重新進行維度數據的預處理、預計算，使用時直接調用計算好的結果，進而導致大量的數據冗余，最大的作用就是解決了性能的問題。

事

實

表維度表1維度表2維度表3維度表4維度表5維度表6星型模型架構

星型模型架構是一種非正規化的結構，特點是有一張事實表，多張維度表，是不存在漸變維度的，事實表和維度表通過主外鍵相關聯，維度表之間是沒有關聯，因為維度表的數據冗余，所以統計查詢時不需要做過多外部連接。雪花模型架構

雪花模型架構就是將星型模型中的某些維度表抽取成更細粒度的維度表，然后讓維度表之間也進行關聯，通過最大限度的減少數據存儲量以及聯合較小的維度表來改善查詢性能。

PART02POSTGRESQL和

數據倉庫理由一：從業務層面上來講1POSRTGRESQL完美的支持了ORACLE的存儲過程，這個從現有基于ORACLE的系統上遷移至POSTGRESQL上成了最大的便利。很多代碼無需修改就可以直接在POSTGRESQL上運行，就算是有些代碼需要改動，但是改動幅度和工作量并不是很大，并且我司已經開發出了代碼轉換工具。只需要放到轉換工具上很輕松的就能做到ORACLE到POSTGRESQL上的代碼平移，全部交由轉換工具自己完成。非常便利2POSTGRESQL相對于ORACLE的SQL來說，雖然存在方言性。但是正如第一點所說。兩種數據庫的語句的平移過度也相對比較輕松。雖然在個別語句方面還是存在一定的問題，但是基本滿足了大量語句即拿即用的需求。理由二：強大的JSON處理功能我只能說josnb太棒了！雖然在存儲數據時還有一些優化空間。但是在使用時只能用一個字來形容那就是爽！尤其是在v9.5以后他又增加了很多JSON的操作符，讓我們從數據庫或者數據倉庫層到報表層、數據展示層的速度與便捷性又增加了一個量級。兩個字好用?。?！注：具體POSTGRESQL的JSON新特性或者JSON/JSONB相關使用的帖子,可以查看中文社區或者官方手冊里的一些資料內容。理由三：強大的擴展性無論是POSTGRE的分布式集群擴展，或者只是單單的POSTGRESQL的主從復制類型擴展，都可以讓我們在現有的企業框架中具備更好的靈活性和更多的選擇性，而不是再依靠單單的RAC

DGADGOGG的這樣的付費性擴展組件或中間件來實現我們的擴展需求。這里我要重點的說一下citus+postgresql的方案。從2018年年底到2020年，我們已經在大量的OLAP上使用了這套組合。并且穩定運行至今。讓我們節省了大量的運維成本和服務器投入成本。并且他也解決了我們之前最擔心的問題就是分布式事務的問題。由于citus比較獨立。所以，在新數據庫版本支持方面我們也不用擔心。理由四：和HADOOP的友好共生這里用一個圖大家就懂了！都是這么萌（猛）！其他理由大數據集、文本數據的靈活索引（GIN，GiST，SP-GiST和BRIN）支持主流編程語言豐富的數據類型和跨平臺性較好的存儲結構但是最重要的是他----免費且開原（地主家的余量也不多?。。㏄ART03一個屬于雨天的故事！??！那年那天那個雨夜與那個叫武藏小杉的地鐵站一個失敗的設計：DATAWAREHOUSEDBFILEJSONXMLETL服務DBFILEJSONXMLETL服務ODS層數據服務層DATAWAREHOUSE

HADOOP-HIVE報表展示層決策層數據倉庫層少了點什么？？？千葉站邊的那個小酒館?。。∫粋€改良的設計V1ODSDATAWAREHOUSEDBFILEJSONXMLETL服務DBFILEJSONXMLETL服務ODS層數據服務層ODS層ODS庫（POSTGRESQL）DATAWAREHOUSE報表