1 資料同步問題

1.1 資料同步的場景

Hive在實際作業中主要用于構建離線資料倉庫，定期的從各種資料源中同步采集資料到Hive中，經過分層轉換提供資料應用，例如，每天需要從MySQL中同步最新的訂單資訊、用戶資訊、店鋪資訊等到資料倉庫中，進行訂單分析、用戶分析，
例如：MySQL中有一張用戶表：tb_user，每個用戶注冊完成以后，就會在用戶表中新增該用戶的資訊，記錄該用戶的id、手機號碼、用戶名、性別、地址等資訊，

每天都會有用戶注冊，產生新的用戶資訊，我們每天都需要將MySQL中的用戶資料同步到Hive資料倉庫中，在做用戶分析時，需要對用戶的資訊做統計分析，例如統計新增用戶的個數、總用戶個數、用戶性別分布、地區分布、運營商分布等指標，

1.2 資料同步的問題

在實作資料倉庫資料同步的程序中，我們必須保證Hive中的資料與MySQL中的資料是一致的，這樣才能確保我們最終分析出來的結果是準確的，沒有問題的，但是在實作同步的程序中，這里會面臨一個問題：如果MySQL中的資料發生了修改，Hive中如何存盤被修改的資料？
例如以下情況

? 2021-01-01：MySQL中有10條用戶資訊

? 2021-01-02：Hive進行資料分析，將MySQL中的資料同步
? 2021-01-02：MySQL中新增2條用戶注冊資料，并且有1條用戶資料發生更新
? 新增兩條用戶資料011和012
? 008的addr發生了更新，從gz更新為sh
? 2021-01-03：Hive需要對2號的資料進行同步更新處理
? 問題：新增的資料會直接加載7.1.3 解決方案
? 方案一：在Hive中用新的addr覆寫008的老的addr，直接更新
到Hive表中，但是更新的資料如何存盤在Hive表中？
? 優點：實作最簡單，使用起來最方便
? 缺點：沒有歷史狀態，008的地址是1月2號在sh，但是1月2號之前是在gz的，如果要查詢008的1月2號之前的addr就無法查詢，也不能使用sh代替

? 方案二：每次資料改變，根據日期構建一份全量的快照表，每天一張表
? 2021-01-02：Hive中有一張表tb_user_2021-01-02

? 2021-01-03：Hive中有一張表tb_user_2021-01-03
? 優點：記錄了所有資料在不同時間的狀態
? 缺點：冗余存盤了很多沒有發生變化的資料，導致存盤的資料量過大

? 方案三：構建拉鏈表，通過時間標記發生變化的資料的每種狀態的時間周期

2 拉鏈表的設計

2.1 功能與應用場景

拉鏈表專門用于解決在資料倉庫中資料發生變化如何實作資料存盤的問題，如果直接覆寫歷史狀態，會導致無法查詢歷史狀態，如果將所有資料單獨切片存盤，會導致存盤大量非更新資料的問題，拉鏈表的設計是將更新的資料進行狀態記錄，沒有發生更新的資料不進行狀態存盤，用于存盤所有資料在不同時間上的所有狀態，通過時間進行標記每個狀態的生命周期，查詢時，根據需求可以獲取指定時間范圍狀態的資料，默認用9999-12-31等最大值來表示最新狀態，

2.2 實作程序

整體實作程序一般分為三步，第一步先增量采集所有新增資料【增加的資料和發生變化的資料】放入一張增量表，第二步創建一張臨時表，用于將老的拉鏈表與增量表進行合并，第三步，最后將臨時表的資料覆寫寫入拉鏈表中，例如：
當前MySQL中的資料：

當前Hive資料倉庫中拉鏈表的資料：
? step1：增量采集變化資料，放入增量表中
? step2：構建臨時表，將Hive中的拉鏈表與臨時表的資料進行合并
? step3：將臨時表的資料覆寫寫入拉鏈表中

3 拉鏈表的實作

3.1 資料準備

? 創建dw層拉鏈表

--創建資料庫
create database db_zipper;
use db_zipper;

--創建拉鏈表
create table dw_zipper(
  userid string,
  phone string,
  nick string,
  gender int,
  addr string,
  starttime string,
  endtime string
) row format delimited fields terminated by '\t';

?	構建模擬資料：vim /export/data/zipper.txt
```sql
001	186xxxx1234	laoda	0	sh	2021-01-01	9999-12-31
002	186xxxx1235	laoer	1	bj	2021-01-01	9999-12-31
003	186xxxx1236	laosan	0	sz	2021-01-01	9999-12-31
004	186xxxx1237	laosi	1	gz	2021-01-01	9999-12-31
005	186xxxx1238	laowu	0	sh	2021-01-01	9999-12-31
006	186xxxx1239	laoliu	1	bj	2021-01-01	9999-12-31
007	186xxxx1240	laoqi	0	sz	2021-01-01	9999-12-31
008	186xxxx1241	laoba	1	gz	2021-01-01	9999-12-31
009	186xxxx1242	laojiu	0	sh	2021-01-01	9999-12-31
010	186xxxx1243	laoshi	1	bj	2021-01-01	9999-12-31

? 加載拉鏈表資料
–加載模擬資料
load data local inpath ‘/export/data/zipper.txt’ into table dw_zipper;

? 查詢資料
select userid,nick,addr,starttime,endtime from dw_zipper;

3.2 增量采集

? 創建ods層增量表

create table ods_zipper_update(
  userid string,
  phone string,
  nick string,
  gender int,
  addr string,
  starttime string,
  endtime string
) row format delimited fields terminated by '\t';

? 創建模擬資料：vim /export/data/update.txt
008 186xxxx1241 laoba 1 sh 2021-01-02 9999-12-31
011 186xxxx1244 laoshi 1 jx 2021-01-02 9999-12-31
012 186xxxx1245 laoshi 0 zj 2021-01-02 9999-12-31

? 加載更新資料
load data local inpath ‘/export/data/update.txt’ into table ods_zipper_update;

? 查詢資料
select userid,nick,addr,starttime,endtime from ods_zipper_update;

3.3 合并資料

?	創建臨時表
create table tmp_zipper(
  userid string,
  phone string,
  nick string,
  gender int,
  addr string,
  starttime string,
  endtime string
) row format delimited fields terminated by '\t';

? 合并拉鏈表與增量表

insert overwrite table tmp_zipper
select
  userid,
  phone,
  nick,
  gender,
  addr,
  starttime,
  endtime
from ods_zipper_update
union all
--查詢原來拉鏈表的所有資料，并將這次需要更新的資料的endTime更改為更新值的startTime
select
  a.userid,
  a.phone,
  a.nick,
  a.gender,
  a.addr,
  a.starttime,
  --如果這條資料沒有更新或者這條資料不是要更改的資料，就保留原來的值，否則就改為新資料的開始時間-1
  if(b.userid is null or a.endtime < '9999-12-31', a.endtime , date_sub(b.starttime,1)) as endtime
from dw_zipper a  left join ods_zipper_update b
on a.userid = b.userid ;

3.4 生成最新拉鏈表

? 覆寫拉鏈表

insert overwrite table dw_zipper
select * from tmp_zipper;