第三章 Greenplum 實戰(zhàn)
本章叫介紹兩個完整的例子：數據倉庫拉鏈記歷史和網頁瀏覽日志分析。在這兩個例子中撵渡，會結合 Greenplum 的一些特性加以描述，之后會介紹使用 Greenplum
中要注意的一些特性，以及這些特性對性能的影響岔霸。
[TOC]
3.1 歷史拉鏈表
數據倉庫是一個面向主題的、集成的俯渤、相對穩(wěn)定的呆细、反映歷史變化的數據集合，用于支持管理決策八匠。優(yōu)于需要反映歷史變化絮爷，數據倉庫中的數據通常包含歷史信息趴酣，系統(tǒng)記錄了企業(yè)從過去某一時點（如開始應用數據倉庫的時點）到目前的各個階段的信息，通過這些信息坑夯，可以對企業(yè)的發(fā)展歷程和未來趨勢做出定量分析和預測岖寞。
歷史拉鏈表是一種數據模型，主要是針對數據倉庫設計中標存儲數據的方式而定義的柜蜈，就是記錄一個事務從開始一直到當前狀態(tài)的所有變化的信息仗谆。
拉鏈表可以避免按每一天存儲所有記錄造成的海量存儲問題，同時也是出了緩慢變化數據的一種常見方式淑履。
3.1.1 應用場景描述
拉鏈算法存儲：每日只向歷史表中添加新增和變化的數據量
3.1.2 原理及步驟
在拉鏈表中隶垮，每一條數據都有一個生效日期（dw_beg_date）和失效日期（dw_end_date）
如果要查詢最新的數據，那么只要查詢失效時間為 3000-12-31 的數據即可鳖谈，如果要查詢 12 月 1 號的歷史數據岁疼，則篩選生效時間 2011-12-01 并且失效時間 > 2011-12-01 的數據即可。如果查詢的是 12 月 2 號的數據缆娃，那么篩選條件則是生效時間
2-11-12-02 并且失效時間 > 2011-12-02.
在gp中捷绒，則可以利用分區(qū)表按照 dw_end_date 保存時間，這樣在查詢的時候可以利用gp的分區(qū)裁剪贯要，從而減少IO消耗暖侨。下面講解拉鏈表刷新的步驟，連線代表數據流向崇渗，線上的編號就是步驟編號：
首先介紹每個表的用途：
- member_fatdt0 ：表示 member 的事實表字逗，其中 P30001231 保存的是最新數據，每個分區(qū)保留的都是歷史已失效的數據
- member_delta：當天的數據庫變更數據宅广，action 字段表示該數據為新增（I）葫掉，更新（U）、刪除（D）
- member_tmp0：刷新過程中的臨時表跟狱，這個表有兩個分區(qū)俭厚，分別記錄歷史數據，即當天失效數據驶臊，另一個分區(qū)例句的是當前數據
- member_tmp1：同樣是刷新過程中的臨時表挪挤，主要是在交換分區(qū)的時候使用
刷新過程簡單來說，就是講前一天的全量數據（分區(qū) P30001231）與當前的增量數據進行關聯关翎，并對不同的變更類型（action）進行相應的處理扛门，最終生成最新數據，以及當天發(fā)生變更的歷史數據纵寝。
3.1.3 表結構
1. 拉鏈表（member_fatdt0）結構
member——fatdt0 使用 member_id 作為分部件论寨，使數據盡量打散在每個機器上，通過 with(appendonly=true,compresslevel=5) 指定該表為壓縮表，可以減少 IO 操作政基，將 dw_end_date 作為分區(qū)字段贞铣。建表語句如下：
2.增量表（member_delta）結構
建表語句如下：
3.臨時表0（member_tmp0）結構
dtype 為分區(qū)字段，H 表示歷史數據沮明，C 表示當前數據辕坝，建表語句如下：
4.臨時表1（member_tmp1）結構
表結構與 member_tmp0、member_fatdt0 一模一樣荐健，建表語句如下：
3.1.4 Demo 數據準備
（1）增量表數據
12 月 2 號增量數據酱畅，新增、刪除江场、更新各有一條記錄：
12 月 3 號增量數據纺酸，新增、刪除址否、更新各有一條數據：
（2）歷史表初始數據
初始數據為 12 月 1 號餐蔬，失效日期為 3000 年 12 月 31 號：
3.1.5 數據加載
gp數據加載主要包括標準sql的 insert 、copy佑附、外部表樊诺、gpload、web external table 等
1.insert
效率最差音同，只適合加載極少量數據
2.copy
copy 源于 PostgreSQL词爬，較 SQL 的insert 方式效率大大提升，但數據仍然需通過 Master 節(jié)點权均，無法實現并行高效數據加載
3.外部表
4.gpload
gpload 是對外部表的一層封裝顿膨。語法：
首先，編寫 gpload 控制文件 member.yml 叽赊，代碼如下：
其次恋沃，執(zhí)行數據加載：
最后，驗證數據：
5.可執(zhí)行外部表
（6.3.4）基于操作系統(tǒng)命令讀取數據文件的方式如下必指，用法跟普通外部表類似囊咏，不用啟動 gpfdist 服務，下面的外部表只在 Master 上執(zhí)行：
清空 member_delta 表并插入數據：
3.1.6 數據刷新
1.拉鏈表刷新
Step1：對事實表中最新數據（分區(qū) P30001231）與 member_delta 表中的更新取劫、刪除數據進行左外連接，關聯上則說明該數據已發(fā)生過變更研侣，需要將該數據的失效時間更新為當天谱邪，并插入到 member_tmp0 表中的歷史數據分區(qū)中，關聯不上則說明沒有發(fā)生過變更庶诡，需要將該數據插入到 member_tmp0 表中的當前數據分區(qū)中惦银。gp 會根據 dtype 的數據自動學則額對應的分區(qū)。
初始全量數據為 2011-12-01 號，在 12 月 3 號刷新 12 月 2 號增量數據扯俱，代碼如下：
Step2：將 member_delta 的新增书蚪、更新數據插入到 member_tmp0 表的當前數據分區(qū)中：
Step3：將 member_fatdt0 表中的對應分區(qū)（P20121201）與 member_tmp0 表的歷史數據分區(qū)交換：
Step4：將 member_fatdt0 表中的當前數據分區(qū)（P30001231）與 member_tmp0 表的當前數據分區(qū)交換：
至此，拉鏈表數據刷新完成迅栅，數據驗證如下：
2.歷史數據查詢
基于拉鏈表殊校，可以回溯到歷史上任意一天的數據狀態(tài)。
（1）12 月 1 號數據读存，如圖：
（2）12 月 2 號數據为流，如圖：
（3）12 月 3 號數據，如圖：
3.1.7 分區(qū)裁剪
下面通過查看執(zhí)行計劃來介紹gp的分區(qū)表的功能让簿。
全表掃描的執(zhí)行計劃如下：
通過執(zhí)行計劃可以看出敬察，gp掃描了所有的分區(qū)，當加入篩選條件 dw_end_date=‘3000-12-31‘ 時尔当，執(zhí)行計劃如下：
這時莲祸，分區(qū)裁剪發(fā)生了作用，只掃描了 P30001231 這個分區(qū)
3.1.8 數據導出
gp 在處理大數據量數據導出時常用的方式主要有
- 并行導出（可寫外部表）
- 非并行導出（COPY）
可寫外部表導出：
1）創(chuàng)建可寫外部表：
WRITABLE 關鍵字表示該外部表是可寫外部表椭迎；Like 語句表示創(chuàng)建的外部表的表結構與 member_tmp1 表結構一樣锐帜；LOCATION 指定 gpfdist 的機器名跟端口，還有保存的文件名侠碧；FORMAT 為導出文件格式定義抹估。
2）執(zhí)行數據導出：
3）驗證生成的文件：
3.2 日志分析
3.2.1 應用場景描述
- 分析全網站每分鐘的PV、UV弄兜，并導出到 Excel 中药蜻，畫出折線圖
- 解析 URL，獲取 URL 中的參數列表
- 通過 URL 取得 member_id替饿，然后統(tǒng)計當前瀏覽次數的用戶分布语泽，如瀏覽次數在 1-5,6-10,11-50,51-100 及 100 次以上的這五個區(qū)間段分別有多少個用戶
3.2.1 數據 Demo
建表語句及字段描述如下：
Demo 數據如下：
3.2.3 日志分析實戰(zhàn)
1.PV、UV 分布
cookie_id 可以視為唯一的用戶表示视卢，故 UV 可視為去重后的 cookie_id 數踱卵。sql 如下：
SELECT TO_CHAR(log_time,'yyyy-mm-dd HH24:mi:00')
,COUNT(1) pv
,COUNT(DISTINCT cookie_id) uv
FROM log_path
GROUP BY 1
ORDER BY 1;
這里只是較少的樣例數據，結果如下：
將數據導出成csv格式据过，在 excel 中展現惋砂， copy 命令的語法如下：
testDB=# copy log_pv_uv_result to '/tmp/log_pv_uv.csv' csv;
COPY 8
在 Excel 中打開并畫圖，結果如下：
2.解析 URL 參數
解析 URL 绳锅，是指通過 substring 對 URL 進行正則表達式匹配西饵，將域名取出，例如對于下面的URL：
正則表達式 \w+://([\w.]+) 可以將域名匹配出來
同樣的鳞芙，可以將參數后面的關鍵字（member_id 或 memberId）的值獲取出來眷柔，作為字段 member_id期虾。
split_part 函數可以將字符串按照某個字符串分割，然后獲取其中一個子串
regexp_split_to_array 函數可以將字符串按照某個字符串分割驯嘱，然后轉換成數組變量：
數據 Demo 樣例數據解析后結果如下：
3.用戶瀏覽次數區(qū)間分析
要計算瀏覽次數的分布镶苞，首先按照 cookie_id 做聚合，計算出每個 cookie_id 的瀏覽次數鞠评，之后再用 case when 對數據進行分區(qū)茂蚓，再聚合，sql如下：
結果如下：
3.3 數據分布
3.3.1 數據分散情況查看
首先谢澈，利用 generate_series 和 repeat 函數生成一些測試數據煌贴，代碼如下：
500 萬數據分散在 6 個數據節(jié)點，利用下面這個 sql 可以查詢數據的分布情況
3.3.2 數據加載速度影響
接下來將通過實驗來測試在分布鍵不同的情況下數據加載的速度
（1） 數據傾斜狀態(tài)下的數據加載
1）測試數據準備锥忿，將測試數據導出
2）簡歷測試表牛郑，以 flag 字段為分布鍵：
3）執(zhí)行數據導入：
4）由于分布鍵 flag 取值只有 0 和 1，因此數據只能分散到兩個數據節(jié)點敬鬓，如下：
5）由于數據分布在 2 和 3 節(jié)點淹朋，對應 Primary Segment 在 dell3 Mirror 節(jié)點 dell4 上，可通過以下 sql 查詢 gp_segment_configuration 獲得：
在執(zhí)行數據導入期間钉答，greenplum performance monitor 頁面可監(jiān)控到础芍；僅有 dell3 和 dell4 兩臺服務器有磁盤和 cpu 消耗，如圖：
（2）數據分布均勻狀態(tài)下的數據加載
1）建立測試表数尿，以 id 字段為分布鍵：
2）執(zhí)行數據導入：
3）由于分布鍵 id 取值順序分布仑性，因此數據可均勻分散至所有數據節(jié)點，如下：
在執(zhí)行數據導入期間右蹦，greenplum performance monitor 頁面可監(jiān)控到：3臺服務器的所有節(jié)點都有磁盤和 cpu 消耗诊杆，可見，在數據均勻的情況下何陆，可以利用更多的機器進行工作晨汹，性能也比較高，如圖：
3.3.3 數據查詢速度影響
（1）數據傾斜狀態(tài)下的數據查詢
由于數據分布在 2 和 3 節(jié)點上贷盲，即對應 dell3 和相應的 mirror 節(jié)點 dell4 上淘这，但是數據查詢只需要 primary 節(jié)點，故只有 dell3 節(jié)點有磁盤消耗巩剖，如圖：
（2）數據分布均勻狀態(tài)下的數據查詢
由于數據分布在所有節(jié)點上铝穷，故所有服務器都有磁盤消耗，從而大大提升了數據查詢的性能
3.4 數據壓縮
3.4.1 數據加載速度影響
基于 table_distribute_4 表創(chuàng)建一個普通的表佳魔。從 greenplum performance monitor 頁面可看到曙聂，在 dell3 和 dell4 上有大量磁盤可操作，如圖：
基于 table_distribute_4 表創(chuàng)建一個壓縮表吃引。由于數據壓縮比很大筹陵，從 greenplum performance monitor 頁面可看到，在 dell3 和 dell4 上基本沒有磁盤寫操作镊尺，只有讀操作朦佩，如圖，（建表語句）
3.4.2 數據查詢速度影響
（1） 普通表的數據查詢
磁盤消耗較大庐氮，如圖：
（2）壓縮表的數據查詢
由于數據經過壓縮语稠，占用存儲空間很小，從greenplum performance monitor頁面可看出弄砍，幾乎沒有磁盤讀操作仙畦，
3.5 索引
gp 支持 b-tree、bitmap音婶、函數索引等慨畸，簡單介紹一下 b-tree 索引：
接下來我們在 flag 字段上創(chuàng)建 bitmap 索引：
在此查看執(zhí)行計劃，采用了索引掃描衣式，如下：
劍豪索引后寸士，在此執(zhí)行上面的查詢語句，有索引的情況下碴卧，用了23毫秒弱卡，相比未創(chuàng)建索引時2606毫秒，有了質的提升住册。
另外婶博，表關聯字段上的索引和 appen-only 壓縮表上的索引都能帶來較大的性能提升。
3.6 小結
本章簡單介紹了基于 gp 數據庫實現數據庫數據模型刷新的過程荧飞，包括典型的需求場景分析凡人、物理模型定義、數據加載垢箕、數據刷新划栓、數據訪問、數據導出等条获。另外也講解了 gp 典型的特性忠荞，比如數據分布策略、數據壓縮帅掘、統(tǒng)計信息委煤、表分區(qū)、列存儲修档、索引等碧绞。
