Na´vrh syste´mu na automatickou tvorbu datove´ho skladu Martin Sˇa´rfy 23. za´rˇ´ı 2008 Abstrakt Business intelligence na´stroje se na´stupem levny´ch rˇesˇenı´ sta´vajı´ ˇ BD integrujı´ jednodudostupne´ i pro male´ a strˇednı´ firmy. Vy´robci SR che´ ETL a OLAP technologie prˇ´ımo do svy´ch produktu˚, k dispozici je i pomeˇrneˇ sˇiroka´ sˇka´la open-source software rˇesˇenı´. Jistou slabinou vsˇak porˇa´d zu˚sta´va´ problematika tvorby samotne´ho modelu datove´ho skladu. Tento cˇla´nek se snazˇ´ı navrhnout syste´m na automatickou tvorbu modelu datove´ho skladu na za´kladeˇ exportu dat z produkcˇnı´ch databa´zı´, vcˇetneˇ na´stroje na jeho plneˇnı´. V prvnı´m kroku jsou v datech rozezna´ny statisticky zajı´mave´ u´daje, na za´kladeˇ ktery´ch je v dalsˇ´ım kroku navrzˇen model datove´ho skladu a transformacˇnı´ schema. Tuto definici pak mu˚zˇe uzˇivatel upravit a vy´slednou podobu prˇedlozˇit ETL stroji, ktery´ zajistı´ extrakci, transformaci a nahra´nı´ dat do datove´ho skladu. V cˇla´nku je takovy´to ETL engine podrobneˇ popsa´n, vcˇetneˇ na´vrhu˚ na dalsˇ´ı rozsˇirˇova´nı´. Celkoveˇ tak navrzˇeny´ syste´m umozˇnˇuje intuitivnı´ a jednoduchou tvorbu datove´ho skladu. Klı´cˇova´ slova: ETL, ETL engine, data integration platform, schema mining, schema discovery, OLAP, data warehousing, business intelligence.
1
Obsah 1
´ vod, motivace U
3
2
Architektura syste´mu.
4
3
Analy´za exportu dat 3.1 Rekonstrukce datove´ho modelu produkcˇnı´ databa´ze 3.1.1 Import vstupnı´ch dat z CSV souboru˚. . . . . . 3.1.2 Detekce datovy´ch typu˚ sloupcu˚ . . . . . . . . 3.1.3 Nalezenı´ prima´rnı´ch klı´cˇu˚ . . . . . . . . . . . . 3.1.4 Nalezenı´ cizı´ch klı´cˇu˚ . . . . . . . . . . . . . . . 3.2 Urcˇenı´ sche´matu datove´ho skladu . . . . . . . . . . .
. . . . . .
6 6 6 8 9 9 11
Definice ETL sche´matu. 4.1 Transformace . . . . . . . . . . . . . 4.2 Spojenı´ transformacı´ . . . . . . . . . 4.3 Spusˇteˇnı´ ETL sche´matu . . . . . . . . 4.4 Knihovna standardnı´ch transformacı´ 4.5 Mozˇnosti optimalizace . . . . . . . .
. . . . .
12 12 13 13 14 17
4
5
Za´veˇr
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
19
2
1
´ vod, motivace U
Nejna´kladneˇjsˇ´ı cˇa´stı´ zava´deˇnı´ business intelligence technologiı´ je tradicˇneˇ tvorba datove´ho skladu ([S29], [S8], [S14]). V poslednı´ch letech se tyto technologie sta´vajı´ dostupny´mi i pro male´ a strˇednı´ firmy, a to dı´ky integraci jednoduchy´ch ETL a OLAP ˇ BD prˇ´ımo do svy´ch produktu˚ bud’ zdarma nebo technologiı´ vy´robci SR za nepatrneˇ vysˇsˇ´ı cenu, cˇi dı´ky dostupnosti pouzˇitelny´ch Open-Source rˇesˇenı´. Klı´cˇovy´ proble´m, ktere´mu cˇelı´ spolecˇnosti prˇi zava´deˇnı´ teˇchto technologiı´, je schopnost integrace dat ze svy´ch produkcˇnı´ch syste´mu˚ (v pru˚zkumu [DIRR05] z listopadu 2005 prˇes 69 % respondentu˚ tento proble´m oznacˇilo jako velky´ nebo velmi velky´). Jesˇteˇ palcˇiveˇjsˇ´ı je tento proble´m u maly´ch a strˇedneˇ velky´ch firem, ktere´ typicky nemajı´ persona´l vyhrazeny´ pouze na tento u´kol. Pozˇadavkem na integracˇnı´ software je tedy maxima´lnı´ intuitivnost. Na´stroj, ktery´ ted’ prˇedstavı´me, se snazˇ´ı by´t pru˚vodcem uzˇivatele prˇi tvorbeˇ datove´ho skladu. Zapada´ do modernı´ho trendu nabı´zet software jako webovou sluzˇbu, kterou v oblasti OLAP technologiı´ v tuto chvı´li nabı´zejı´ zejme´na na´sledujı´cı´ spolecˇnosti: • SEA-Tab Software. Produkt Pivot-Link, bez ETL na´stroje. • Oco, Inc. Komplexnı´ BI software, pouze jednoduchy´ ETL na´stroj. • GoodData, Corp. Modernı´ webovy´ OLAP na´stroj, bez ETL cˇa´sti. Zatı´m pouze Beta verze. • LucidEra. OLAP na´stroj zalozˇeny´ na OSS Pentaho Mondrian, bez ETL na´stroje. Tyto syste´my prˇedpokla´dajı´ vytvorˇenı´ vhodny´ch struktur uzˇivatelem pouzˇitı´m externı´ho ETL na´stroje. Na´mi navrzˇeny´ syste´m se pokusı´ zaplnit tuto mezeru a nabı´dne intuitivnı´ zpu˚sob tvorby v prˇ´ıpadeˇ jednoduchy´ch datovy´ch skladu˚. Soucˇasneˇ vsˇak nabı´dne i mechanizmus pro tvorbu slozˇity´ch datovy´ch skladu˚, a to formou graficke´ho modelova´nı´ ETL transformacı´. Za´kladem pro samotny´ ETL engine na´m bude teoreticka´ pra´ce ARKTOS II [SG02], da´le Open-Source na´stroj Talend Open Studio a funkcˇnı´ prototyp The Bee Project [MS03].
3
2
Architektura syste´mu.
Software navrhujeme jako vzda´leneˇ dostupnou sluzˇbu (software as a service). V tomto prostrˇedı´ lze pouze obtı´zˇneˇ zı´skat prˇ´ımy´ prˇ´ıstup do produkcˇnı´ databa´ze (kvu˚li firemnı´ politice, nastavenı´ firewallu˚ cˇi proprieta´rnı´m databa´zovy´m ovladacˇu˚m), pragmaticky´m rˇesˇenı´m vsˇak jsou pravidelne´ CSV exporty dat. Na´mi navrzˇeny´ syste´m tedy prˇedpokla´da´ na´sledujı´cı´ work-flow: 1. Export dat z produkcˇnı´ch databa´zı´. V prvnı´m kroku uzˇivatel zajistı´ CSV export dat z produkcˇnı´ch databa´zı´. 2. Analy´za exportovany´ch dat. Syste´m v datech identifikuje datovy´ model (tabulky, typy sloupcu˚, prima´rnı´ a cizı´ klı´cˇe,..) a na´sledneˇ v neˇm pokusı´ rozeznat fakta, atributy, metriky, look-up tabulky, dimenze, apod. 3. Prˇedgenerova´nı´ ETL sche´matu. Na za´kladeˇ teˇchto u´daju˚ navrhne model datove´ho skladu a ETL sche´ma (graf transformacı´) pro jeho (periodicke´) plneˇnı´. ´ prava sche´matu. V dalsˇ´ım kroku ma´ uzˇivatel mozˇnost opravit 4. U chybne´ odhady syste´mu prˇ´ıpadneˇ doplnit dalsˇ´ı transformace, a to pomocı´ graficke´ho na´stroje pro editaci ETL sche´matu. 5. ETL engine. Takto upravene´ sche´ma je pak vstupem pro ETL engine, zajisˇtujı´cı´ (periodicke´) provedenı´ prˇ´ıslusˇny´ch transformacı´. Vy´stupem ETL enginu je naplneˇny´ datovy´ sklad. Implementace tohoto syste´mu tedy spocˇ´ıva´ v na´sledujı´cı´ch cˇa´stech: 1. Rekonstrukce databa´zove´ho schematu. 2. Rozezna´nı´ OLAP prvku˚, vytvorˇenı´ modelu datove´ho skladu a tvorba prˇ´ıslusˇne´ho ETL schematu. 3. Na´stroj na vizua´lnı´ opravu ETL sche´matu. 4. ETL engine. Vy´sledny´ syste´m by meˇl by´t pouzˇitelny´ pro intuitivnı´ tvorbu jednoduchy´ch sche´mat, nicme´neˇ porˇa´d dostatecˇneˇ mocny´ (u´plny´) i pro na´rocˇneˇjsˇ´ı prˇ´ıpady.
4
ETL schema
DWH DSA
DB1
DB2
CSV
Obra´zek 1: Architektura navrzˇene´ho syste´mu.
5
3
Analy´za exportu dat
Nejcˇasteˇjsˇ´ım forma´tem pro prˇenos databa´zovy´ch dat je (v dnesˇnı´ dobeˇ uzˇ mı´rneˇ zastaraly´) forma´t CSV (comma separated values). Je podporova´n sˇiroky´m spektrem vy´robcu˚, a to zejme´na pro jeho jednoduchou implementaci a nı´zkou rezˇii. Jeho za´sadnı´m proble´mem je nedostatek metadat, ktere´ s sebou nese. Nasˇteˇstı´, jak si da´l uka´zˇeme, lze statisticky vy´znamne´ u´daje s pomeˇrneˇ vysokou mı´rou u´speˇsˇnosti identifikovat automaticky. Proble´m pochopenı´ exportu dat rozdeˇlı´me na dveˇ fa´ze - na detekci relacˇnı´ho modelu produkcˇnı´ databa´ze a na´sledneˇ identifikaci prvku˚ vy´znamny´ch pro tvorbu datove´ho skladu, cˇili rozezna´nı´ kandida´tu˚ na faktove´ tabulky, detekce sloupcu˚ obsahujı´cı´ metricke´ u´daje, atributy, da´le propojenı´ vy´cˇtovy´ch hodnot na jejich look-up tabulky, detekci cˇasovy´ch i jiny´ch dimenzı´ cˇi hierarchiı´ dimenzı´. Vesˇkere´ odhady syste´mu se deˇjı´ pod dohledem uzˇivatele, ktery´ ma´ za u´kol schvalovat cˇi opravovat rozhodnutı´ syste´mu. Dı´ky tomu chybny´ odhad syste´mu nenı´ kriticky´m nedostatkem, konecˇne´ rozhodnutı´ zu˚sta´va´ v rukou uzˇivatele.
3.1 Rekonstrukce datove´ho modelu produkcˇnı´ databa´ze Na´mi navrzˇeny´ algoritmus pro detekci databa´zove´ho sche´matu ze vstupnı´ch dat ma´ neˇkolik kroku˚ ([DK08]): 1. Import vstupnı´ch dat z CSV souboru˚. 2. Detekce datovy´ch typu˚ sloupcu˚. 3. Nalezenı´ prima´rnı´ch klı´cˇu˚. 4. Nalezenı´ cizı´ch klı´cˇu˚. 5. Export vy´sledne´ho sche´matu. Jak si uka´zˇeme, zˇa´dny´ z teˇchto kroku˚ nelze prove´st zcela automaticky, po kazˇde´m je nutny´ (nebo alesponˇ vhodny´) dialog s uzˇivatelem pro potvrzenı´ cˇi uprˇesneˇnı´ odhadu.
3.1.1
Import vstupnı´ch dat z CSV souboru˚.
Forma´t CSV nema´ prˇesnou specifikaci. Acˇkoliv jej doporucˇenı´ RFC4180 popisuje jednoznacˇneˇ, jednotlive´ implementace se mezi sebou
6
lisˇ´ı, a to zejme´na v na´sledujı´cı´m: • pouzˇity´ znak pro oddeˇlovacˇ: kromeˇ cˇa´rky se uzˇ´ıva´ tabula´tor, strˇednı´k, roura cˇi jiny´ ma´lo se vyskytujı´cı´ znak. • textove´ hodnoty mu˚zˇou ale nemusı´ by´t zapsa´ny v uvozovka´ch1 . • zpu˚sob za´pisu zvla´sˇtnı´ch znaku˚: znaky mu˚zˇou by´t zapsa´ny v uvozovka´ch nebo pouzˇitı´m escape-sekvencı´. • pouzˇita´ znakova´ sada a za´pis znaku nove´ho rˇa´dku (CRLF, LF, CR). • u polı´ fixnı´ de´lky zachova´va´nı´ pocˇa´tecˇnı´ch cˇi ukoncˇovacı´ch mezer. • nepovinny´ za´pis na´zvu˚ sloupcu˚ na prvnı´m rˇa´dku tabulky. Tyto zpu˚soby za´pisu lze algoritmicky odlisˇit s pomeˇrneˇ velkou mı´rou u´speˇsˇnosti (za prˇedpokladu, zˇe tabulka obsahuje alesponˇ neˇkolik rˇa´dku˚), schva´lenı´ odhadu cˇi jeho korekce uzˇivatelem je vsˇak prˇesto du˚lezˇita´.
Obra´zek 2: Tradicˇnı´ dialog pro import CSV souboru. Pro urcˇenı´ znakove´ sady lze pouzˇ´ıt postupy beˇzˇneˇ uzˇ´ıvane´ naprˇ. ve webovy´ch prohlı´zˇecˇ´ıch [SK01]. Ma´loktera´ tabulka neobsahuje zˇa´dne´ cˇ´ıslo; pokud vsˇechny hodnoty na prvnı´m rˇa´dku zacˇ´ınajı´ pı´smenem, resp. tvorˇ´ı platny´ SQL identifika´tor, jde o za´hlavı´ tabulky a lze z neˇj vyta´hnout i na´zvy sloupcu˚. Jako pocˇa´tecˇnı´ jme´no tabulky lze pouzˇ´ıt na´zev souboru bez prˇ´ıpony. 1
RFC-4180 doporucˇuje uvozovkovat alesponˇ textove´ hodnoty obsahujı´cı´ cˇa´rku, uvozovku nebo znak nove´ho rˇa´dku.
7
3.1.2
Detekce datovy´ch typu˚ sloupcu˚
V tomto kroku urcˇ´ıme datove´ typy jednotlivy´ch sloupcu˚ a taky porovna´me vyskytujı´cı´ se hodnoty se znalostnı´ ba´zı´ vzoru˚ a pokusı´me se urcˇit i hlubsˇ´ı se´mantiku neˇktery´ch sloupcu˚. Prˇ´ıkladem vzoru˚ znalostnı´ ba´ze mu˚zˇou by´t ko´dy PSCˇ (ZIP), e-mailove´ adresy cˇi telefonnı´ cˇ´ısla, cozˇ jsou vzory, ktere´ lze v datech lehce identifikovat. Algoritmus projde vsˇechny za´znamy a pro kazˇdy´ sloupec vytvorˇ´ı souhrnnou tabulku obsahujı´cı´: • pocˇet celocˇ´ıselny´ch hodnot s u´vodnı´mi nulami, • pocˇet celocˇ´ıselny´ch hodnot bez u´vodnı´ch nul, • histogram de´lky hodnoty ve znacı´ch, • histogram pocˇtu slov (pro detekci ko´du˚), • pocˇet pra´zdny´ch hodnot, • pocˇet hodnot podobny´ch neˇjake´ formeˇ za´pisu data, • pocˇty hodnot vyhovujı´cı´ ru˚zny´m regula´rnı´m vy´razu˚m znalostnı´ ba´ze, • pocˇet unika´tnı´ch hodnot ve sloupci. Z teˇchto souhrnny´ch u´daju˚ mu˚zˇeme pak vyvodit na´sledujı´cı´ za´veˇry: • SQL typ je jeden z: INTEGER(N), FLOAT(N,M), CHAR(N) cˇi VARCHAR(N), a to dle prˇevla´dajı´cı´ vlastnosti. Maxima´lnı´ de´lku rˇeteˇzce cˇi pocˇet platny´ch cifer algoritmus urcˇuje s rezervou. • pokud histogram de´lek hodnot obsahuje pouze jednu hodnotu, ma´ sloupec tuto fixnı´ de´lku. • volitelneˇ: sloupec je ENUM pokud obsahuje neˇkolik ma´lo ru˚zny´ch necˇ´ıselny´ch hodnot (shodne´ de´lky). • volitelneˇ: cˇ´ıselny´ sloupec je NOT NULL pokud neobsahuje pra´zdne´ hodnoty. Jestlizˇe vy´znamna´ veˇtsˇina za´znamu˚ vyhovuje neˇktere´mu regula´rnı´mu vy´razu ze znalostnı´ ba´ze, urcˇ´ıme podle neˇj i se´manticky´ typ tohoto sloupce.
8
3.1.3
Nalezenı´ prima´rnı´ch klı´cˇu˚
Trivia´lnı´ algoritmus na nalezenı´ jednoduche´ho prima´rnı´ho klı´cˇe spocˇ´ıva´ v testova´nı´ unika´tnı´ch hodnot ve sloupci. Algoritmus je rovneˇzˇ schopen hledat slozˇene´ klı´cˇe testova´nı´m vsˇech kombinacı´ sloupcu˚, zde vsˇak uzˇ nara´zˇ´ıme na vy´pocˇetnı´ na´rocˇnost takove´ho algoritmu. V praxi nenı´ obvykle´ pouzˇ´ıvat prima´rnı´ klı´cˇ slozˇeny´ z vı´ce nezˇ dvou sloupcu˚, algoritmus je tedy omezen tak, zˇe hleda´ klı´cˇe slozˇene´ z nanejvy´sˇ dvou sloupcu˚.
3.1.4
Nalezenı´ cizı´ch klı´cˇu˚
Cizı´ klı´cˇe jsou za´kladem pro modelova´nı´ struktury objektu˚. Databa´zove´ sche´ma zna´ pouze jediny´ typ cizı´ho klı´cˇe, ktery´ je pouzˇ´ıva´n pro modelova´nı´ vsˇech myslitelny´ch typu˚ struktur. Mezi beˇzˇneˇ uzˇ´ıvane´ typy vazeb mezi objekty patrˇ´ı: • objekt obsahujı´cı´ vı´ce podobjektu˚, • objekty s volnou vazbou - cˇ´ıselnı´ky, ko´dovnı´ky • struktura ukazujı´cı´ sama na sebe - hierarchie, stromy, lesy, grafy • N:M vazby cˇi vztahy prˇ´ımo nesouvisejı´cı´ch objektu˚, • zdeˇdeˇne´ trˇ´ıdy - odvozena´ tabulka obsahuje id a dalsˇ´ı atributy pu˚vodnı´ho objektu. Ve vsˇech teˇchto prˇ´ıpadech odkazuje cizı´ klı´cˇ na neˇjaky´ prima´rnı´ klı´cˇ. Acˇkoliv jde o jednoduche´ zjisˇteˇnı´, nenı´ mi zna´ma´ pra´ce, ktera´ by tohoto faktu vyuzˇ´ıvala. Algoritmus pro hleda´nı´ cizı´ch klı´cˇu˚ tedy funguje na´sledovneˇ: Pro kazˇdy´ prima´rnı´ klı´cˇ (nalezeny´ v prˇedesˇle´m kroku nebo zadany´ uzˇivatelem) slozˇeny´ z N sloupcu˚ algoritmus hleda´ ve vsˇech tabulka´ch vsˇechny kombinace sloupcu˚ rˇa´du N. U kazˇde´ kombinace vypocˇte na´sledujı´cı´ charakteristiky pokrytı´ oboru hodnot prima´rnı´ho klı´cˇe (PK) a zkoumane´ho kandida´ta na cizı´ klı´cˇ (FK): • platnost - procento hodnot FK, ktere´ majı´ svu˚j vzor v PK. • pokrytı´ - procento hodnot PK, ktere´ majı´ svu˚j obraz v mnozˇineˇ FK. Zatı´mco plnou shodu u obou parametru˚ lze ocˇeka´vat pouze u prvnı´ho typu vazeb (tj. u objektu˚ obsahujı´cı´ch vı´ce podobjektu˚), u ostatnı´ch musı´me s pozˇadavkem na procento pokrytı´ slevit.
9
Obra´zek 3: Detekce cizı´ch klı´cˇu˚, vyja´drˇenı´ pravdeˇpodobnosti.
Na rea´lny´ch datech je videˇt, zˇe pokud integritnı´ omezenı´ nehlı´da´ samotna´ databa´ze (cozˇ je u produkcˇnı´ch syste´mu˚ kupodivu celkem beˇzˇne´), nenı´ dobre´ pozˇadovat ani 100 % platny´ch hodnot. Jako vhodny´ kandida´t na cizı´ klı´cˇ se empiricky zdajı´ sloupce, ktere´ majı´ pomeˇr platnosti nad 95 % a pomeˇr pokrytı´ nad 20 %. Pokud sloupec odkazuje na vı´ce prima´rnı´ch klı´cˇu˚, je jako spra´vny´ vybra´n ten, ktery´ obsahuje nejveˇtsˇ´ı pokrytı´. Pokud databa´zove´ sche´ma obsahuje slozˇene´ prima´rnı´ klı´cˇe, je potrˇeba hledat i slozˇeny´ cizı´ klı´cˇ. To se ukazuje jako vy´pocˇetneˇ neu´nosneˇ slozˇite´, nasˇteˇstı´ se vsˇak slozˇene´ prima´rnı´ klı´cˇe v praxi moc nepouzˇ´ıvajı´ a na´sˇ obecny´ algoritmus hleda´nı´ cizı´ch klı´cˇu˚ tak veˇtsˇinou hleda´ pouze jednoduche´ klı´cˇe. Da´le algoritmus prˇi hleda´nı´ cizı´ho klı´cˇe bere do u´vahy pouze sloupce, ktere´ majı´ datove´ typy v souladu s prima´rnı´m klı´cˇem, konkre´tneˇ bud’ jsou oba cˇ´ıselne´ nebo oba majı´ stejnou fixnı´ de´lku. Cˇastou optimalizacı´ databa´zove´ho sche´matu je spojenı´ neˇkolika sloupcu˚ fixnı´ de´lky do jednoho. Z toho du˚vodu se (pokud ma´ prima´rnı´ klı´cˇ fixnı´ de´lku) da´le testujı´ i podrˇeteˇzce sloupcu˚, a sice zacˇa´tek a konec kandida´tnı´ho sloupce o de´lce odpovı´dajı´cı´ de´lce prima´rnı´ho klı´cˇe.
10
Obra´zek 4: Zobrazenı´ rozeznane´ho a opravene´ho sche´matu (zkra´ceno).
3.2
Urcˇenı´ sche´matu datove´ho skladu
V tomto kroku se pokusı´me urcˇit neˇktere´ charakteristiky datove´ho modelu, ktere´ na´m umozˇnı´ vytvorˇit prˇedpokla´dany´ model datove´ho skladu. Datovy´ model produkcˇnı´ databa´ze tvorˇ´ı (orientovany´) graf tabulek pospojovany´ch cizı´mi klı´cˇi. Neˇktere´ tabulky obsahujı´ fakta, stanou se tedy za´kladem faktovy´ch tabulek v datove´m skladeˇ, kolem ktery´ch se vytvorˇ´ı hveˇzda cˇi vlocˇka tvorˇena´ tabulkami dimenzı´ z ostatnı´ch propojeny´ch tabulek. Vhodny´m kandida´tem pro faktovou tabulku jsou „dlouhe´“ tabulky obsahujı´cı´ sloupec (cˇi vı´c sloupcu˚) s cˇasovy´m u´dajem. Ten je vhodny´m indika´torem, zˇe tabulka se v cˇase meˇnı´, a tedy bude pro uzˇivatele zajı´mava´. Uvedeny´ postup je pouze nekompletnı´m na´stinem rˇesˇenı´ problematiky, cˇekajı´cı´ na hlubsˇ´ı analy´zu a prototypova´nı´. V tuto chvı´li autor neumı´ celkovou u´speˇsˇnost algoritmu odhadnout, zdu˚razneˇme vsˇak, zˇe chybne´ odhady nejsou kriticke´. Vy´stupem tohoto kroku by fina´lneˇ meˇla by´t XML definice modelu datove´ho skladu, vcˇetneˇ ETL schema pro jeho naplneˇnı´.
11
4
Definice ETL sche´matu.
Pro prˇevod vstupnı´ch dat z produkcˇnı´ databa´ze do vy´sledne´ho datove´ho skladu je pouzˇito ETL sche´ma, jezˇ je vstupem pro ETL stroj. Nynı´ si popisˇme, co vlastneˇ ETL sche´matem a ETL strojem v nasˇem kontextu myslı´me. ETL sche´ma nema´ pru˚myslovy´ standard, naprˇ´ıcˇ literaturou neexistuje dokonce ani usta´lena´ terminologie. Prˇesto se da´ rˇ´ıct, zˇe za´kladnı´ koncepce kolem ETL modelu˚ je stejna´ - jde o orientovany´ graf, kde uzly reprezentujı´ transformace (akce, operace, komponenty, aktivity) a hrany toky dat (data-flow, rˇa´dky), prˇ´ıpadneˇ specifikujı´ hranice a porˇadı´ jednotlivy´ch u´loh (job, task,. . . ). Pro nasˇe potrˇeby nadefinujeme jednoduchy´ ale rozsˇirˇitelny´ a sˇiroce pouzˇitelny´ ETL model. Tento model bude vycha´zet z pracı´ ARKTOS II (dizertacˇnı´ pra´ce [Sim04]), OSS syste´mu Talend Open-Studio a funkcˇnı´ho prototypu [MS03], ktere´ vsˇak mı´rneˇ rozsˇ´ırˇ´ı.
4.1
Transformace
Transformacı´ rozumı´me n-tici: • name - jedinecˇne´ jme´no transformace. • inputs - seznam mozˇny´ch vstupnı´ch datovy´ch toku˚. • outputs - seznam mozˇny´ch vy´stupnı´ch datovy´ch toku˚. • params - parametry transformace. • flow - model chova´nı´ transformace (main, input, output, filter, lookup). • code - trˇ´ıda v knihovneˇ implementujı´cı´ ko´d transformace. • node - uzel v sı´ti, na ktere´m ma´ by´t transformace spusˇteˇna. Transformace obecneˇ mu˚zˇe mı´t libovolny´ pocˇet vstupnı´ch i vy´stupnı´ch datovy´ch toku˚, omezenı´ za´visı´ na hodnoteˇ flow, podle ktere´ho ETL stroj „orchestruje“ spousˇteˇnı´ jednotlivy´ch transformacı´: • input transformace zajisˇt’uje extrakci dat, nema´ tedy zˇa´dny´ vstup, pouze generuje vy´stup (t.j. implementuje samotne´ cˇtenı´, naprˇ. transformace MySQLSelectInput). • output transformace implementuje ulozˇenı´ vstupnı´ch dat, nema´ tedy vy´stup, pouze vstup (naprˇ. transformace ExcelFileOutput cˇi ConsoleOutput).
12
• filter transformace transformuje vstupnı´ data na vy´stupnı´. • lookup transformace vracı´ hodnotu asociovanou s klı´cˇem. • main transformace – o jejı´m chova´nı´ vu˚cˇi vstupu˚m a vy´stupu˚m nelze nic prˇedpokla´dat, ETL stroj ji spousˇtı´ v samostatne´m vla´kneˇ. Pouzˇitı´ sı´t’ove´ho uzlu pro provedenı´ transformace prˇedpokla´da´ instalaci potrˇebne´ho softwarove´ho vybavenı´ a beˇh sluzˇby zajisˇtujı´cı´ nahra´nı´ a spusˇteˇnı´ ko´du. Implementacˇneˇ i na u´drzˇbu nejjednodusˇsˇ´ı je na UNIX-ovy´ch uzlech vyuzˇitı´ syste´move´ sluzˇby Secure Shell (ssh). Sluzˇba ssh by´va´ dostupna´ prakticky na vsˇech strojı´ch, navı´c umozˇnˇuje zabezpecˇeny´ prˇenos citlivy´ch metadatovy´ch u´daju˚ (naprˇ. hesla do databa´ze apod.). Vzda´lene´ vola´nı´ je typicky vyuzˇito pro prˇ´ıstup k loka´lnı´m datovy´m zdroju˚m, da´le pro optimalizaci prˇenosu dat po sı´ti cˇi vyuzˇitı´ procesorove´ho vy´konu. Knihovu transformacı´ tvorˇ´ı za´kladnı´ transformace obecne´ho charakteru, nicme´neˇ lze ji take´ doplnˇovat uzˇivatelsky´m ko´dem.
4.2
Spojenı´ transformacı´
Hrana spojujı´cı´ dveˇ transformace mu˚zˇe by´t na´sledujı´cı´ho typu: • stream - datovy´ tok mezi dveˇma transformacemi. • trigger - umozˇnˇuje spustit cı´lovou transformaci prˇi uda´losti (OnDone nebo OnError). • loop - na´sledujı´cı´ transformace je spousˇteˇna opakovaneˇ v cyklu (naprˇ. pro kazˇdy´ vstupnı´ soubor,. . . ), prˇicˇemzˇ data mu˚zˇe prˇedat ve formeˇ parametru˚. Spojenı´ trigger a loop prˇina´sˇ´ı mozˇnost postupne´ho, sekvencˇnı´ho, spousˇteˇnı´ transformacı´. Cele´ ETL sche´ma tedy deˇlı´ na jake´si cˇa´stecˇne´ u´koly (job, task), a to rozkladem grafu podle teˇchto spojenı´.
4.3
Spusˇteˇnı´ ETL sche´matu
ETL stroj, ktery´ ma´ za u´kol vykonat samotne´ ETL, dosta´va´ na sve´m vstupu ETL sche´ma a prova´dı´ na´sledujı´cı´ operace: 1. Rozklad na u´koly. V prvnı´m kroku provede rozklad grafu na u´koly podle trigger a loop spojenı´. Vzniknuvsˇ´ı graf u´kolu˚, pospojovany´
13
trigger a loop spojenı´m, nynı´ dynamicky vyhodnocuje a spousˇtı´ u´koly, ktere´ jsou schopny beˇhu, t.j. takove´, ktere´ bud’ nemajı´ trigger nebo nastala uda´lost, ktera´ je spousˇtı´. 2. Spusˇteˇnı´ u´kolu. Pomocı´ sı´t’ove´ infrastruktury jsou obecneˇ na ru˚zny´ch uzlech vytvorˇeny objekty reprezentujı´cı´ transformace, ktere´ jsou na´sledneˇ propojeny stream spojenı´mi datovy´ch toku˚. Porˇadı´ inicializace je da´no orientacı´ spojenı´, zacˇ´ına´ se u transformacı´ bez vstupu˚, pokracˇuje transformacemi, ktere´ majı´ vsˇechny vstupy prˇipojene´, azˇ do vytvorˇenı´ vsˇech transformacı´. Po inicializaci vsˇech transformacı´ v u´kolu je mozˇne´ u´kol spustit. Transformace beˇzˇ´ı navza´jem paralelneˇ, na vı´ce uzlech, ve vı´ce vla´knech cˇi procesech, cˇtou sve´ vstupnı´ datove´ toky, prova´deˇjı´ prˇ´ıslusˇnou operaci a vy´sledek posı´lajı´ na vy´stupnı´ toky. Nejsou-li beˇhem vy´pocˇtu na vstupnı´m datove´m toku prˇipravena zˇa´dna´ data, je transformace operacˇnı´m syste´mem pozastavena do doby, nezˇ se na datove´m toku data objevı´. Konec vy´pocˇtu je indikova´n uzavrˇenı´m spojenı´ a ukoncˇenı´m vsˇech procesu˚ transformace.
4.4
Knihovna standardnı´ch transformacı´
Dalsˇ´ı klı´cˇovou vlastnostı´ dobre´ho ETL na´stroje je sˇiroka´ paleta standardnı´ch transformacı´. K za´kladnı´m transformacı´m patrˇ´ı podpora cˇtenı´ a za´pisu databa´zove´ tabulky, cˇili transformace: • SQLInput - vykona´ SQL dotaz, rˇa´dky posı´la´ na vy´stup. • SQLOutput - zapı´sˇe, prˇepı´sˇe, prˇida´ nebo smazˇe rˇa´dek specifikovany´ klı´cˇem, prˇi spousˇteˇnı´ tabulku volitelneˇ vytvorˇ´ı, vymazˇe nebo zahodı´ a znova vytvorˇ´ı. Du˚lezˇitou skupinou transformacı´ jsou procesnı´ transformace ovlivnˇujı´cı´ porˇadı´ vykona´va´nı´ dalsˇ´ıch transformacı´ a u´kolu˚: • ForeachFileLoop - vyhleda´ soubory urcˇene´ maskou a pro kazˇdy´ zavola´ na´sledujı´cı´ transformaci. • IterateToFlow - spustı´ na´sledujı´cı´ transformaci opakovaneˇ pro kazˇdy´ rˇa´dek vstupnı´ tabulky. • Replicate - duplikuje vstupnı´ data, cˇ´ımzˇ umozˇnˇuje prove´st ru˚zne´ operace nad stejny´mi daty.
14
• Unite - spojuje data z vı´ce zdroju˚ na za´kladeˇ zadane´ho klı´cˇe (vhodne´ pro spojenı´ dat z ru˚zny´ch zdroju˚). • Map - mapuje data z jednoho cˇi vı´ce zdroju˚ do jednoho cˇi vı´ce vy´stupu˚, a to na za´kladeˇ mapovacı´ho schematu. • Die - ukoncˇ´ı prova´deˇnı´ u´kolu, s prˇ´ıpadny´m vypsa´nı´m zpra´vy. Pro vytva´rˇenı´ datovy´ch skladu˚ jsou mimorˇa´dneˇ potrˇebne´ transformace zajisˇt’ujı´cı´ databa´zovy´ JOIN a look-up v tabulce: • Join - nad zadany´mi sloupci tabulek provede zadany´ typ JOIN dotazu, vra´tı´ sloupce dle sche´matu. • Lookup - pro klı´cˇe cˇtene´ na vstupu vracı´ hodnoty z look-up tabulky. Tı´m vy´cˇet mozˇny´ch transformacı´ samozrˇejmeˇ nekoncˇ´ı, uzˇitecˇne´ jsou i na´sledujı´cı´ transformace: • Sort - zadany´m zpu˚sobem setrˇ´ıdı´ vstupnı´ datovy´ tok. • Aggregate - u setrˇ´ızene´ho vstupu agreguje sloupce dle zadane´ agregacˇnı´ funkce (MIN, MAX, COUNT,. . . ). • Denormalize - spojı´ hodnoty, u ktery´ch se opakuje klı´cˇ. • SystemExec - spustı´ syste´movy´ prˇ´ıkaz, vy´stup prˇ´ıkazu posı´la´ jako datovy´ tok. • ConsoleOutput - posı´lany´ vstup vypı´sˇe uzˇivateli na obrazovku. • SendMail - posı´lany´ vstup odesˇle spolu se zadanou zpra´vou na e-mail. • HTTPFetch - sta´hne soubor ze zadane´ webove´ adresy. • MsgBox - vypı´sˇe uzˇivateli dialogove´ okno. Produkcˇnı´ ETL na´stroje zvyknou obsahovat stovky transformacı´ (viz. Tab. 1), tyto vsˇak tvorˇ´ı jakousi minima´lnı´ mnozˇinu, ktera´ je dostatecˇna´ na veˇtsˇinu i na´rocˇneˇjsˇ´ıch u´kolu˚. Uzˇivatel samozrˇejmeˇ mu˚zˇe do knihovny doplnit vlastnı´ transformace, prˇ´ımo urcˇene´ pro jeho specificke´ potrˇeby.
15
Business Intelligence Business Custom Code Data Quality
Database
Database utilities ELT File
Internet
Logs & Errors Misc group
Orchestration Processing
System XML
[DB2|Ingres|Mondrian|MSSql|MySQL|Oracle|Sybase]SCD [Centric|Salesforce|Sugar|Vtiger]CRM[Input|Output] Java AddCRCRow, FuzzyMatch, IntervalMatch, ReplaceList, UniqRow, SchemaComplianceCheck [Access|AS400|DB2|Firebird|HSQLDb| Informix|Ingres|Interbase|JavaDB|JDBC| LDAP|MSSql|MSSqlBulk|MySQL|MySQLBulk| Oracle|Postgresql|SQLite|Sybase|Teradata][Input|Output|BulkInput|BulkOutput| Row|Commit|Connection] CreateTable, ParseRecordSet ELT[Oracle|Teradata|Mysql] [Input|Map|Output] File[Compare|Copy|Delete|Fetch|List], FileInput[Delimited|Positional|Regex|XML], FileOutput[Excel|LDIF|XML|Delimited], FileUnarchive, PivotOutputDelimited FileFetch, FTP, Mom[Input|Output], SendMail, Socket[Input|Output], WebServiceInput, XMLRPC Die, FlowMeter, FlowMeterCatcher, LogCatcher, LogRow, StatCatcher, Warn BufferOutput, ContextDump, ContextLoad, Loop, IterateToFlow, MsgBox, RowGenerator, Sleep, WaitForFile, WaitForSqlData FileList, IterateToFlow, Loop, Replicate, Sleep, Unite, WaitForFile, WaitForSqlData AggregateRow, AggregateSortedRow, Denormalize, ExternalSortRow, FilterColumn, FilterRow, Map, Normalize, Replace, Replicate, SortRow, Unite RunJob, SSH, System AdvancedFileOutputXML, DTDValidator, File[Input|Output]XML, XSDValidator, XSLT
Tabulka 1: Kompletnı´ vy´cˇet transformacı´ syste´mu Talend Open Studio.
16
4.5
Mozˇnosti optimalizace
Navrhnuty´ mechanizmus je implementacˇneˇ dostatecˇneˇ jednoduchy´, nicme´neˇ pro rea´lne´ pouzˇitı´ je vhodne´ integrovat i neˇktere´ optimalizacˇnı´ prvky, ktere´ vy´razny´m zpu˚sobem urychlı´ vykona´nı´ ETL sche´matu. Pozˇadavek na rychlost je u ETL kriticky´, beˇzˇneˇ je totizˇ potrˇeba zpracovat stovky tisı´c cˇi miliony za´znamu˚ beˇhem relativneˇ male´ho cˇasove´ho okna. Uvedeme zde proto neˇkolik vy´razny´ch optimalizacı´, ktere´ podstatny´m zpu˚sobem zkracujı´ dobu exekuce ETL sche´matu: 1. Vyuzˇitı´ flow charakteru transformace. Jednoducha´ implementace spousˇtı´ vsˇechny transformace v samostatny´ch prova´deˇcı´ch tocı´ch (vla´knech cˇi procesech), data jsou prˇeda´va´na pomocı´ meziprocesove´ komunikace (IPC) – sı´t’ovy´m spojenı´m cˇi UNIX sokety. Veˇtsˇina transformacı´ je vsˇak z hlediska chova´nı´ ke vstupu˚m a vy´stupu˚m jednoducha´ a lze je prˇeve´st na proste´ vola´nı´ funkce. Kuprˇ´ıkladu input transformace cˇtoucı´ z databa´ze mu˚zˇe implementovat pouze funkci read, kterou bude na´sledujı´cı´ transformace volat pro nacˇtenı´ dat. Stejny´ mechanizmus platı´ pro ukla´da´nı´ dat u output transformacı´. Rovneˇzˇ z rˇady propojeny´ch filtru˚ lze poskla´dat rˇeteˇzec vola´nı´ transformacˇnı´ch funkcı´. Zvla´sˇtnı´ zrˇetel je vsˇak potrˇeba´ da´t na transformace meˇnı´cı´ pocˇet za´znamu˚ mezi vstupem a vy´stupem, u ktery´ch uvedeny´ mechanizmus pouzˇ´ıt nelze. 2. Pouzˇitı´ UNIX soketu˚ a sdı´lene´ pameˇti. Jsou-li propojene´ transformace pousˇteˇny na stejne´m uzlu, lze k prˇenosu dat pouzˇ´ıt mı´sto TCP/IP spojenı´ UNIX sokety, ktere´ oproti TCP/IP dosahujı´ neˇkolikana´sobneˇ vysˇsˇ´ıch prˇenosovy´ch rychlostı´. Dalsˇ´ı mozˇnostı´ je implementace prˇenosu datove´ho toku vytvorˇenı´m sdı´lene´ pameˇti, fungujı´cı´ jako FIFO fronta mezi dveˇma transformacemi, cˇ´ımzˇ se usˇetrˇ´ı prˇesuny dat mezi adresnı´m prostorem procesu a ja´dra operacˇnı´ho syste´mu. 3. Optimalizace cˇtenı´ a ukla´da´nı´ dat do databa´zove´ho stroje. Standardnı´ zpu˚sob pro cˇtenı´ a za´pis dat z a do databa´zove´ tabulky je dvojice SQL prˇ´ıkazu˚ SELECT a INSERT. Databa´zove´ stroje vsˇak cˇasto poskytujı´ i mozˇnosti prˇ´ıme´ho cˇtenı´ cˇi za´pisu, cozˇ je oproti obecny´m prˇ´ıkazu˚m mnohem rychlejsˇ´ı. Prˇ´ıkladem mu˚zˇe by´t prˇ´ıkaz LOAD DATA INFILE u MySQL databa´ze, ktery´ umozˇnˇuje nahra´t data do tabulky z CSV souboru. Tı´mto souborem mu˚zˇe by´t
17
rovneˇzˇ UNIX soket, spojeny´ s transformacı´. Kuprˇ´ıkladu takova´ MySQLInsertOutput transformace tedy mu˚zˇe by´t optimalizova´na pro vy´razne´ urychlenı´ za´pisu rˇa´dku˚, beˇzˇ´ı-li databa´ze na stejne´m uzlu a tento zpu˚sob ukla´da´nı´ dat je povolen. 4. Volba uzlu transformace dle za´teˇzˇe. Dı´ky distribuovane´mu prostrˇedı´, ktere´ navrzˇena´ architektura vyuzˇ´ıva´, je mozˇne´ zvolit uzel, na ktere´m transformace pobeˇzˇ´ı, dynamicky podle aktua´lnı´ potrˇeby. 5. SSH Mastering. Tvorba jednou´cˇelovy´ch transformacı´ ma´ za na´sledek, zˇe i logicky jednoduche´ sche´ma obsahuje desı´tky azˇ stovky transformacı´. Narozdı´l od vlastnı´ch datovy´ch toku˚ sice metadata potrˇebna´ pro spra´vu takove´ho mnozˇstvı´ vzda´leny´ch ssh procesu˚ nejsou objemna´, jsou vsˇak citliva´ na dobu odezvy. Noveˇjsˇ´ı verze ssh protokolu umozˇnˇuje tzv. ssh mastering, sdı´lenı´ jednoho nava´zane´ho spojenı´ vı´ce procesy. To mu˚zˇe vy´razneˇ urychlit dobu spousˇteˇnı´ vzda´leny´ch transformacı´. 6. Komprese a sˇifrova´nı´ dat na vzda´leny´ch sı´tı´ch. Zatı´mco na dnesˇnı´ch vysokorychlostnı´ch loka´lnı´ch sı´tı´ch komprese dat zbytecˇneˇ zateˇzˇuje procesor, ve fa´zi extrakce dat z produkcˇnı´ch databa´zı´ cˇasto nara´zˇ´ıme na potrˇebu prˇenosu dat ze vzda´leny´ch pobocˇek, ktere´ jsou prˇipojene´ pomaly´m internetovy´m prˇipojenı´m. Komprese dat je v teˇchto prˇ´ıpadech potrˇebna´, v tuto chvı´li je rovneˇzˇ vhodne´ vyuzˇ´ıt vytvorˇenou sı´t’ovou infrastrukturu k prˇenosu sdı´lene´ho symetricke´ho klı´cˇe k sˇifrova´nı´ datove´ho toku mezi pobocˇkami. 7. Paralelnı´ iterace. U transformacı´ generujı´cı´ch loop uda´losti na porˇadı´ iteracı´ cˇasto neza´lezˇ´ı (naprˇ. ForeachFileLoop) a cı´lovy´ u´kol je mozˇne´ pustit paralelneˇ v neˇkolika instancı´ch. To mu˚zˇe dı´ky optima´lnı´mu vyuzˇitı´ vı´ce procesoru˚ cˇi jiny´ch zdroju˚ dramaticky zkra´tit celkovou dobu prova´deˇnı´ ETL sche´matu.
18
5
Za´veˇr
Navrhovany´ syste´m, bude-li implementova´n, se snazˇ´ı zaplnit pra´zdne´ mı´sto mezi na´stroji pro podporu datove´ integrace. Na´vrh ETL stroje vycha´zı´ z neˇkolika funkcˇnı´ch na´stroju˚, unika´tnı´ je rezˇim vytva´rˇenı´ modelu datove´ho skladu uzˇivatelsky´m pru˚vodcem. Aktua´lnı´ stav implementace pokry´va´ rekonstrukci databa´zove´ho schematu z exportu˚ dat, rovneˇzˇ je hotov prototyp ETL stroje, ktery´ oproti beˇzˇny´m na´stroju˚m umozˇnˇuje distribuovany´ beˇh na vı´ce sı´t’ovy´ch uzlech. Zby´vajı´cı´ cˇa´stı´ je algoritmus na rozezna´nı´ OLAP prvku˚ v produkcˇnı´ databa´zi, vytvorˇenı´ modelu datove´ho skladu a tvorba prˇ´ıslusˇne´ho ETL sche´matu. Scha´zı´ take´ graficke´ prostrˇedı´ pro tvorbu a u´pravu ETL sche´matu. Celek by vsˇak ve vy´sledku meˇl tvorˇit komplexnı´ ETL prostrˇedı´ s rˇadou unika´tnı´ch prvku˚.
19
Pouzˇita´ literatura [RAL06] Rizzi, S., Abello´, A., Lechtenbo¨rger, J., and Trujillo, J. 2006. Research in data warehouse modeling and design: dead or alive?. In Proceedings of the 9th ACM international Workshop on Data Warehousing and OLAP (Arlington, Virginia, USA, November 10 - 10, 2006). DOLAP ’06. ACM, New York, NY, 3-10. [S8] M. Demarest.: The politics of data warehousing. Dostupne´ z: http://www.hevanet.com/demarest/marc/dwpol.html [TDWI03] Wayne Eckerson: The Evolution of ETL. Evaluating ETL and Data Integration Platforms. The Data Warehousing Institute (TDWI) 2003 Report Series. [S14] B. Inmon.: The Data Warehouse Budget. DM Review Magazine, January 1997. Dostupne´ z: http://www.dmreview.com/master.cfm?NavID=55&EdID=1315 [S29] C. Shilakes, J. Tylman.: Enterprise Information Portals. Enterprise Software Team. Dostupne´ z: http://www.sagemaker.com/company/downloads/eip/indepth.pdf [SG02] P. Vassiliadis, A. Simitsis, S. Skiadopoulos: Modeling ETL activities as graphs. In Proc. DMDW (Toronto, Canada, May 2002), pp. 52–61. [MS03] Martin Sˇa´rfy: Na´stroj pro extrakci a transformaci dat. Diplomova´ pra´ce. Masarykova univerzita, Fakulta informatiky. 2003. [Sim04] A. Simitsis.: Modeling and Optimization of Extraction-Transformation-Loading (ETL) Processes in Data Warehouse Environments. PhD Thesis, Athens, Greece, 2004. [SK01] Shanjian Li, Katsuhiko Momoi: A composite approach to language/encoding detection. 19th International Unicode Conference, San Jose. 2001. [DK08] Martin Sˇa´rfy: Semi-automaticka´ rekonstrukce databa´zove´ho sche´matu na za´kladeˇ dat z tabulek. To appear in proc.: Datakon 2008, Brno, Cˇeska´ republika, 2008. [DIRR05] Colin White: Data Integration: Using ETL, EAI, and EII Tools to Create an Integrated Enterprise. The Data Warehousing Institute (TDWI) 2005 Report Series. A 101communications Publications. November 2005.
20
