資料倉儲的累加式更新

by adonis 25. 九月 2014 16:08

前言

最近在課堂上被問到的一個問題,而這個問題是在講資料倉儲「累加式更新」時所被問到的,當下覺得這是一個還蠻有挑戰性的問題,所以本期就以累加式更新為例,舉例如果無法在 OLTP資料庫中做資料結構異動時,我們該如何進行累加式更新。

 

累加式更新



在講本期之前,先簡單的介紹一下累加式更新這個東西,會需要累加式更新,主要在於 OLTP資料庫不適合進行彙總運算,即便你設定了大量的索引,對於需要大量彙總運算的決策支援系統而言,一點幫助也沒有,所以,如果想要你的現有系統在決策支援中很快速的提供使用者報表、圖表,勢必要把資料庫的結構稍微做一些修改,使用原本第三正規化的 OLTP資料庫因為資料表關聯過多,不但關聯花時間,大量的彙總運算更是效能低落,它唯一的好處是,它會有非常即時的資料。

所以,資料倉儲的課程,一開始並不介紹如何建立 Cube、 維度這些主題,而是要想辦法先將原本的 OLTP正規化資料庫,先轉成反正規化的資料倉儲資料來源,如下圖:

image

其中, OLTP與 OLAP資料來源中有一個 staging資料庫,用來暫存資料,因為這整個流程不是只做一次, OLTP會天天有新資料進來,如何篩選哪些資料新增,哪些資料修改與刪除,就必需是我們這種 ETL開發人員做的事情,而新增、修改、刪除的暫存資料都將會先放在 staging資料庫之中以便待會再匯入到資料倉儲的資料來源。

現在的問題就出現在這裡,在課程中會教有哪些方式可以達到「累加式更新」的境界,畢竟不太可能每次都打掉重來,如果有100萬筆資料,不可能每次都重新再匯一次吧?書上有介紹幾種方式,分別如下:

1.    儲存 Primary key與 checksum:但書上沒有舉例。
2.    使用「high water mark」,也就是高水位線法,例用一個 log資料表記錄上次 ETL的時間,以及資料表在設計時會多一個 LastModifiedate的欄位,當資料有異動時,利用 trigger順便異動 LastModifiedate的欄位,這些就可以利用 where子句只將異動的資料匯入到 staging資料庫即可,此種方法只能知道資料的新增與修改,無法知道資料的刪除。

3.    使用 Change Data Capture:這個是 SQL Server企業版的功能,方法還是使用高水位線法,但是可以知道某段時間資料的新增、修改以及刪除情況。

4.    使用 Change Tracking,這個也是 SQL Server的功能,利用版本控制的方式,每當資料有修改的時候,版本控制就會多1號版本,也是使用高水位線的方式取出第 N個版本到第 M個版本之間的資訊差異,不過同樣也只能得知資料的新增與修改,無法知道資料的刪除。

其實無法知道資料是否被刪除並不是個很重要的是,因為資料刪除一般在實務上的資料庫未必會真的將資料刪掉,例如員工離職也不會把員工資料刪除,而是把「員工狀態」改成離職,而一般採取的方法是邏輯刪除,也就是設計一個 deleted的欄位,預設值為0,如果該資料要刪除的話,就把 deleted的欄位值由0改成1代表資料已經被刪除,換句話說,資料刪除其實就是資料修改。

好的,大概了解這個累加式刪除後,有個學員就問了,他的資料庫不是 SQL Server,也不能更動資料表原本的結構,在這樣的情況之下,要如何知道該資料有被異動?

這個條件老實說太嚴格了,說這個資料庫是外包 balabala一堆理由,所以結構無法修改,如果是我是不會管這麼多的,我只知道,如果要解決問題,必需要用最簡單的方式,因為 OLTP效能調教主要的關鍵在於下好的 SQL指令,而 OLAP資料倉儲效能調教的關鍵在於如何快速的找到異動的資料,並加以匯入到 staging資料庫,並且在資料倉儲資料來源中快速的處理成 Cube以供使用者使用。

如果不用現成的方式來做,硬說資料庫中的資料表連一個欄位都無法增加的話,勢必會造成資料量一大,效能就會變慢的問題,這點是我們在想解決方案之前不得不注意的問題。

方法到底有沒有呢?我們可以從新增以及修改兩個角度來看,刪除我們就視為是邏輯刪除,不直接把資料刪掉。

資料的新增

如何知道資料被新增,如果你有按照我們正常設計資料庫的方式,將 Primary key 設定成 identity的話,則每列資料都是不重複的數字,這樣你就可以建立一個資料表,然後記錄上次上傳到哪個數字,下次則是把新增加進來的數字上傳即可,如下圖,就是建立一個資料表:

image

以上圖為例,表示資料表 Product,上次匯進的資料到 606號,而目前 Product資料表裡的資料如下:

image

對的,目前資料為 606,所以並沒有新的資料被產生,而如果產品有新增時,productkey將會變成607,和原本 Log資料表比對,就知道這個資料是新增進來的。

所以,以新增的角度來說,是比較容易處理的。

資料的修改

那麼,資料的修改該怎麼辦呢?
其實這個也不難,但是整體的效能我就無法保證,因為它將會是每一筆記錄都會去比較是否有異動,資料量愈大,愈有可能產生效能的問題,我可以預想到,接下來就是會問我「該如何解決效能問題」了…

SQL Server有個函數叫做 checksum,或許可以利用這個函數來判斷資料行是否有修改,你可以使用下面的句子來判斷:

image

使用 checksum(*)來判斷其值,若任一欄位值有被修改,則 checksum(*)的結果都會被修改,例如上圖,productkey為210,其 checksum值為 -620887203,如果我們隨意修改任何一個欄位的話,它的值就會被修改,如下圖:

image

比對一下,值變成了 -620887491,是不是有改變呢?

只要比較一下,有相同的 primary key,但是checksum的值不同,就表示這筆資料是有異動了,但到底異動了哪了欄位,或是你要對這筆異動的資料列做什麼處理,就是後續要做的事情了,此舉並沒有更改到資料表的任何結構。

當然,有些人對新增可能有點意見,認為新增一個 log資料表有更改到資料表結構,其實那個資料表你不一定要加在原來的資料庫中,你可以另外建立一個資料庫,專門記錄這個 log資料表的資料,無法更改資料表的結構,那新增一個資料庫總可以了吧?如果連新增資料庫另外記錄上次匯入的 key值都不行的話,我只能回答:roll your egg(滾你的蛋)!

此外,由於是使用 checksum這個函數得到資料是否有無被修改,所以變成每筆資料列都要被檢查,資料量大的效能我可不敢測試…這就是標準的,為了要迎合情境,必需徹底犧牲效能的最佳例子。

LAST

Tags:

評論 (49) -

cours de theatre paris
cours de theatre paris United States
2017/9/30 上午 11:19:15 #

A big thank you for your post.Really thank you! Want more.

回覆

nghi duong vung tau
nghi duong vung tau United States
2017/10/7 上午 01:54:39 #

Thanks for the article.Thanks Again. Want more.

回覆

can ho bien vung tau
can ho bien vung tau United States
2017/10/9 下午 07:42:55 #

I really enjoy the blog article.Really thank you! Cool.

回覆

buy hacklink google
buy hacklink google United States
2017/10/12 下午 10:21:17 #

I cannot thank you enough for the blog.Really looking forward to read more. Keep writing.

回覆

i was reading this
i was reading this United States
2017/10/14 下午 05:37:14 #

I value the post.Much thanks again. Will read on...

回覆

dragon city hack mod
dragon city hack mod United States
2017/10/15 下午 05:08:19 #

Looking forward to reading more. Great article post.Much thanks again. Awesome.

回覆

learn the facts here now
learn the facts here now United States
2017/10/17 下午 04:26:29 #

I truly appreciate this blog article. Want more.

回覆

sletrokor
sletrokor United States
2017/10/17 下午 09:58:01 #

Major thanks for the article post. Will read on...

回覆

VigRx Plus
VigRx Plus United States
2017/10/19 上午 09:01:37 #

Very neat article post. Keep writing.

回覆

click here for more
click here for more United States
2017/10/19 下午 08:07:41 #

Say, you got a nice blog.Really looking forward to read more. Will read on...

回覆

carte grise en ligne
carte grise en ligne United States
2017/10/21 上午 09:06:32 #

I really liked your blog.Really thank you! Really Cool.

回覆

phenocal
phenocal United States
2017/11/1 下午 09:48:20 #

Say, you got a nice blog article.Much thanks again. Really Cool.

回覆

phentaslim review
phentaslim review United States
2017/11/3 下午 02:53:12 #

Im thankful for the post.Really thank you! Cool.

回覆

can sciatica pain return after surgery
can sciatica pain return after surgery United States
2017/11/15 上午 06:41:46 #

Thanks for sharing, this is a fantastic post.Thanks Again. Cool.

回覆

criminel avocat
criminel avocat United States
2017/11/16 下午 05:17:00 #

Thanks a lot for the post. Fantastic.

回覆

This is one awesome article post.Really thank you! Really Cool.

回覆

garrys mod hamachi server
garrys mod hamachi server United States
2017/11/22 上午 01:49:12 #

Very informative post.Thanks Again. Keep writing.

回覆

bikinis
bikinis United States
2017/11/23 下午 08:42:34 #

Looking forward to reading more. Great blog.Thanks Again. Will read on...

回覆

Adwords Management Auckland
Adwords Management Auckland United States
2017/11/25 下午 05:41:30 #

Im obliged for the blog. Much obliged.

回覆

Chad Boonswang and Jeffrey Goodman
Chad Boonswang and Jeffrey Goodman United States
2017/11/26 下午 04:52:27 #

Thank you for your post.Really thank you!

回覆

fake car wreckers
fake car wreckers United States
2017/11/29 下午 03:16:53 #

I truly appreciate this blog article.Really looking forward to read more. Want more.

回覆

can ho go vap
can ho go vap United States
2017/11/29 下午 09:50:19 #

Im grateful for the article post.Really looking forward to read more. Really Great.

回覆

small business loans
small business loans United States
2017/11/30 下午 11:43:18 #

I value the blog post.Thanks Again. Really Cool.

回覆

porno
porno United States
2017/12/1 下午 03:23:06 #

I really like and appreciate your blog.Really looking forward to read more. Great.

回覆

primary trade lines
primary trade lines United States
2017/12/3 上午 03:41:38 #

Wow, great article.Thanks Again. Really Cool.

回覆

mobile hentai
mobile hentai United States
2017/12/5 上午 08:28:17 #

Hey, thanks for the blog.Thanks Again. Really Great.

回覆

A big thank you for your blog post.Much thanks again. Fantastic.

回覆

george laws
george laws United States
2017/12/10 下午 06:06:55 #

Major thanks for the post.Thanks Again.

回覆

Brian Stoudmire
Brian Stoudmire United States
2017/12/14 上午 08:14:13 #

I really liked your blog post.

回覆

resource
resource United States
2017/12/14 下午 03:05:07 #

wow, awesome article.Really looking forward to read more. Want more.

回覆

Hanukkah
Hanukkah United States
2017/12/14 下午 09:35:17 #

Really informative article post. Really Cool.

回覆

canon drivers
canon drivers United States
2017/12/16 下午 04:48:07 #

Very neat blog article.Really looking forward to read more.

回覆

tips lose weight
tips lose weight United States
2017/12/16 下午 11:02:59 #

Thanks again for the article post.Thanks Again.

回覆

Leadership
Leadership United States
2017/12/17 下午 04:30:22 #

Great blog post.Thanks Again. Keep writing.

回覆

college degree fake
college degree fake United States
2017/12/17 下午 11:31:18 #

Appreciate you sharing, great article post.Thanks Again.

回覆

money from clicking
money from clicking United States
2017/12/20 下午 05:40:39 #

I truly appreciate this post.Really thank you! Keep writing.

回覆

driver canon
driver canon United States
2017/12/23 上午 09:20:34 #

This is one awesome article.Thanks Again. Cool.

回覆

Darwin Horan
Darwin Horan United States
2017/12/23 下午 07:48:56 #

Thank you for your blog post.Much thanks again.

回覆

driver hp
driver hp United States
2017/12/25 下午 09:18:13 #

This is one awesome blog.Really looking forward to read more. Cool.

回覆

I loved your post.Really thank you! Much obliged.

回覆

آینه
آینه United States
2017/12/26 上午 09:08:48 #

Thanks-a-mundo for the post.Really looking forward to read more. Really Cool.

回覆

SOCCER HIGHLIGHTS
SOCCER HIGHLIGHTS United States
2017/12/26 下午 06:34:08 #

Say, you got a nice blog. Much obliged.

回覆

printer drivers
printer drivers United States
2018/1/3 下午 01:29:45 #

Thanks for the article post.

回覆

best bitcoin casino
best bitcoin casino United States
2018/1/5 上午 12:24:46 #

I cannot thank you enough for the blog post.Really looking forward to read more. Keep writing.

回覆

FBA
FBA United States
2018/1/6 上午 10:37:30 #

Major thanks for the article post. Will read on...

回覆

colocation chicago
colocation chicago United States
2018/1/10 上午 10:17:45 #

Really enjoyed this article.Really thank you! Great.

回覆

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List