PostgreSQL在數(shù)據(jù)倉庫方面的優(yōu)勢與實(shí)踐+-+石佛

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

數(shù)據(jù)插入

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

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

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

一個簡單的維度立方體事實(shí)表和維度表事實(shí)表和維度表是在設(shè)計(jì)數(shù)據(jù)倉庫過程中需要考慮的。

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

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

領(lǐng)域模型：業(yè)務(wù)概念的抽象、分組，整理分組之間的關(guān)聯(lián)，比如用戶購物的業(yè)務(wù)，抽成一個更大的模型，這個模型一般相對于行業(yè)。

邏輯建模：領(lǐng)域模型中的業(yè)務(wù)概念實(shí)體化，并考慮實(shí)體的具體屬性及實(shí)體與實(shí)體之間的關(guān)系，比如訂單（訂單號、付款人…）和支付（金額、支付時(shí)間…）的關(guān)系。

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

維度建模法的特點(diǎn)在于需要進(jìn)行大量的數(shù)據(jù)預(yù)處理、預(yù)計(jì)算，因此會導(dǎo)致大量的數(shù)據(jù)處理工作，而且業(yè)務(wù)發(fā)生變化，需要重新進(jìn)行維度的定義時(shí)，往往需要重新進(jìn)行維度數(shù)據(jù)的預(yù)處理、預(yù)計(jì)算，使用時(shí)直接調(diào)用計(jì)算好的結(jié)果，進(jìn)而導(dǎo)致大量的數(shù)據(jù)冗余，最大的作用就是解決了性能的問題。

事

實(shí)

表維度表1維度表2維度表3維度表4維度表5維度表6星型模型架構(gòu)

星型模型架構(gòu)是一種非正規(guī)化的結(jié)構(gòu)，特點(diǎn)是有一張事實(shí)表，多張維度表，是不存在漸變維度的，事實(shí)表和維度表通過主外鍵相關(guān)聯(lián)，維度表之間是沒有關(guān)聯(lián)，因?yàn)榫S度表的數(shù)據(jù)冗余，所以統(tǒng)計(jì)查詢時(shí)不需要做過多外部連接。雪花模型架構(gòu)

雪花模型架構(gòu)就是將星型模型中的某些維度表抽取成更細(xì)粒度的維度表，然后讓維度表之間也進(jìn)行關(guān)聯(lián)，通過最大限度的減少數(shù)據(jù)存儲量以及聯(lián)合較小的維度表來改善查詢性能。

PART02POSTGRESQL和

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

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

HADOOP-HIVE報(bào)表展示層決策層數(shù)據(jù)倉庫層少了點(diǎn)什么？？？千葉站邊的那個小酒館！?。∫粋€改良的設(shè)計(jì)V1ODSDATAWAREHOUSEDBFILEJSONXMLETL服務(wù)DBFILEJSONXMLETL服務(wù)ODS層數(shù)據(jù)服務(wù)層ODS層ODS庫（POSTGRESQL）DATAWAREHOUSE報(bào)表