Hive_使用ACID事務特性實現漸變維度SCD2操作

wait4friend發表於2018-06-04
Hive

背景

基於維度模型理論的資料倉儲中，一個特別重要的部分是漸變維度的處理，其中用的最多的方法是對維度表進行SCD2型別拉鍊表操作。具體實現使用upsert模式，即更新舊資料的時間戳，並且插入新資料。

但是在使用Hive作為資料倉儲的場景下，對SCD2的操作就比較麻煩。因為Hive不支援更新操作，所以通常做法是把流程中的各部分資料清洗後單獨儲存為獨立的臨時表，然後通過union all的方式對目標表進行overwrite操作。

不過，通過一些特殊的配置，可以開啟Hive對ACID的支援特性，從而實現對Hive進行update，delete這些操作。

本文以HDP 2.4版本為例，演示一下如何實現這個需求。這裡先簡要描述需要執行的步驟。

  • 開啟hive acid特性（通過Ambari操作）
  • 建立支援acid的hive表（滿足bucket，orc，tblproperties的要求）
  • 演示基本DML操作（insert, update, delete）
  • SCD 2演算法的一個簡單實現
  • acid特性的不足（當前還不支援Spark SQL）

前置條件

Hive全域性配置

首先必須開啟Hive對ACID事務的支援。在Amabri介面修改hive配置，開啟ACID Transactions，如下圖

image-20180604172533329

在開啟這個配置的時候，Amabri會提示是否自動修改一些其他的引數，選擇OK確定即可，如下圖

image-20180604220705377

Hive表格式

一個hive表要支援事務，需要同時滿足下列條件：

  • 使用bucket，注意這裡不是partition，而是bucket；
  • 儲存為ORC格式；
  • 指定表屬性transactional

一個滿足上述條件的建表語句如下

create table if not exists demo(
    area_id bigint,
    area_code string,
    area_name string,
    gmt_create timestamp
)
clustered by (area_id) into 8 buckets
stored as orc 
tblproperties('transactional'='true')
;
複製程式碼

演示資料

本例中使用到三張表：

  • demo_d01 初始化資料
  • demo_d02 變數資料，包括新增的資料，以及對原有資料的修改
  • demo_d00 需要進行SCD2操作的目標表

demo_d01

-- init table
drop table if exists demo_d01;
create table if not exists demo_d01(
    area_id bigint,
    area_code string,
    area_name string,
    gmt_create timestamp
)
row format delimited
fields terminated by ','
lines terminated by '\n'
stored as textfile
;
複製程式碼

初始化資料，樣本如下，是在2015-09-25這天的資料

47498,CHNP001,北京,2015-09-25 11:34:29.0
47499,CHNP002,天津,2015-09-25 11:34:29.0
47500,CHNP003,河北,2015-09-25 11:34:29.0
47501,CHNP004,山西,2015-09-25 11:34:29.0
47502,CHNP005,內蒙古,2015-09-25 11:34:29.0
複製程式碼

demo_d02

-- delta table
drop table if exists demo_d02;
create table if not exists demo_d02(
    area_id bigint,
    area_code string,
    area_name string,
    gmt_create timestamp
)
row format delimited
fields terminated by ','
lines terminated by '\n'
stored as textfile
;
複製程式碼

變數資料，包括新增的資料，以及對原有資料的修改。樣本如下，模擬2015-09-26的變數資料，包括一條新增和一條修改。並且為了簡化後面SCD的演示，使用了提前生成好的代理關鍵字area_id

57502,CHNP005,內蒙古-新,2015-09-26 11:34:29.0
57509,CHNP006,四川,2015-09-26 11:34:29.0
複製程式碼

demo_d00

這個表同時滿足前面提到的幾個條件，可以支援ACID事務操作。

-- target table
drop table if exists demo_d00;
create table if not exists demo_d00(
    area_id bigint,
    area_code string,
    area_name string,
    gmt_create timestamp
)
clustered by (area_id) into 8 buckets
stored as orc 
tblproperties('transactional'='true')
;
複製程式碼

ACID事務操作

這裡先簡單講解一下DML操作的實現，不涉及SCD部分。並且為了方便演示資料夾中的檔案數量變化，這裡僅使用了一個bucket，而不是建表指令碼中的8個。

insert

insert into table demo.demo_d00 select * from demo_d01;
複製程式碼

初始化之後的資料如下

image-20180604175851289

當一個hive表插入了初始化資料後，在對應的檔案目錄下有這些檔案。和普通orc格式的hive表不同，這裡有兩個資料夾，如下圖

image-20180604175922113

update

update demo.demo_d00 set area_code='xxx' where area_id=47501;

select * from demo.demo_d00;
複製程式碼

更新操作後的資料如下，可以看到原來的CHNP004變成了xxx

image-20180604175438593

ORC格式的檔案本身是不支援更新的，所以對hive表的更新並不是直接修改資料檔案，而是通過增加變更資料集(Change Set)的方式完成的。資料夾目錄下增加了新的檔案，這部分新檔案就是本次操作的delta集，如下圖中紅色框：

image-20180604180105293

delete

delete from demo.demo_d00 where area_id=47500;

select * from demo.demo_d00;
複製程式碼

資料如下

image-20180604180238382

檔案如下，可以清楚的看到，不論是update還是delete，實際都是通過delta檔案的方式進行的，並沒有修改原始的檔案。

image-20180604180311001

實現SCD 2

漸變維度SCD 2的處理過程可以簡單的描述為

  1. 修改已經存在維度資料的時間戳；
  2. 把更新的維度資料作為一條新維度插入；
  3. 插入其他新增維度資料；

常規實現

我們用一個簡化的模型來模擬這個SCD 2的更新過程

-- 初始化目標表
truncate table demo.demo_d00;
insert into table demo.demo_d00 select * from demo.demo_d01;

-- SCD2 修改已經存在維度資料的時間戳
update demo.demo_d00 
  set gmt_create = '2015-09-26 00:00:00'
where exists (select 1 from demo.demo_d02 d2 where demo_d00.area_code=d2.area_code);

-- SCD2 把變數維度（包括更新和新增兩部分）資料作為新維度插入
insert into table demo.demo_d00 select * from demo.demo_d02;
複製程式碼

完成後的維度表資料如下，其中CHNP005有兩條資料，一條是時間戳已經截止的，另一條是當前的。在hive環境用這個方法實現SCD 2的更新過程，和普通RDBMS沒有差異。

image-20180604212651239

Merge語法

在HDP 2.6之後，新增的merge into語法可以更方便的實現upsert操作。

因為我們的環境是HDP 2.4，所以這裡不做具體測試，但是我會把語句寫在這裡，有機會的時候可以繼續這個測試。

-- HDP 2.6 提供了merge語法
merge into demo_d00
    using demo_d01 d01 
    on demo_d00.area_code = d01.area_code
when matched then update set
    gmt_create = '2015-09-26 00:00:00'
when not matched then insert
    values(d01.area_id, d01.area_code, d01.area_name, d01.gmt_create)
;
複製程式碼

當前不足

當前我們的測試環境下（HDP 2.4， Spark 2.3.0），只有原生hive才提供了ACID的支援。我們常用的Spark SQL並不支援這種用法。

spark-sql> delete from demo.demo_d00 where area_id=47500;
Error in query:
Operation not allowed: delete from(line 1, pos 0)

== SQL ==
delete from demo.demo_d00 where area_id=47500
^^^

spark-sql>
複製程式碼

相關文章