Systém ETL

1. Systém ETL Ing. Jan Přichystal, Ph.D.

2. Úvod • ETL = Extrakce, Transformace a Loadování • ETL systém je studnicí dat pro datový sklad • ETL systém je tvůrcem i zhoubou DS • ETL systém je jednoduchý i komplikovaný zároveň • Budování ETL systému je výzva – tvůrce čelí tlakům ze strany „zákazníka“ i vedení • ETL systém je tvořen dvěma proudy – Architektura a Data flow

3. Úvod

4. Architektura ETL

5. Získávání dat • Zdroje dat • většinou relační DB, pro přenos do místa přípravy nutno konvertovat na dočasný soubor • Dočasné soubory • pro každý zdroj dat je jeden soubor, tyto soubory se poté slučují do jednoho • Místo přípravy dat • soubory ve formátu vhodném pro nahrávání do datového skladu • relační DB (jednodušší manipulace s daty)

6. Získávání dat • Extrakce dat • nalezení zdrojů dat, „filtrování“ jednotlivých zdrojů • generování dočasných souborů s daty • transport souborů z různých platforem • přeformátování vstupů z vnějších zdrojů, popř. z jednotlivých databází v rámci podniku • generování aplikačního kódu pro extrakci dat • řešení nekonzistencí mezi jednotlivými zdroji dat

7. Získávání dat • Transformace dat • mapování vstupních dat na data v datovém skladu • čištění dat, odstranění duplicit, sloučení dat • denormalizace dat ze vstupních relačních DB, dle požadavků datového skladu • konverze datových typů • výpočet a odvození hodnot atributů • kontrola referenční integrity • shromažďování dat dle potřeby • vyřešení chybějících hodnot

8. Získávání dat • Příprava dat • záloha dat a zotavení z chyb databáze • řazení a slučování souborů • vytvoření nového souboru při změně dimenzí • vytvoření záznamu o přiřazení jednotlivých datových položek datového skladu k souboru • vytvoření primárních a cizích klíčů pro nahrávaná data

9. Uložení dat • Uložení dat • většinou se využívá relačních databází • datový sklad • většinou se vychází z ER modelu • datové trhy • většinou se vychází z dimenzionálního modelu • Data z místa přípravy dat • soubory nebo tabulky pro iniciální, popř. inkrementální nahrávání dat

10. Uložení dat • Mnohé funkce z oblasti získání dat • Nahrání dat pro iniciální naplnění datového skladu • Podpora nahrávání dat do více tabulek na detailní a sumarizační úrovni • Optimalizace procesu nahrávání dat • Záloha dat a zotavení při chybách • Bezpečnost dat • Monitorování a nastavování databáze • Periodická archivace dat v databázi na základě nastavení

11. Infrastruktura • Zahrnuje funkční elementy potřebné k tomu, aby byla architektura implementována • Operační infrastruktura • Lidé potřební pro údržbu datového skladu, procedury, školení, SW pro správu • Fyzická infrastruktura • HW prostředky, operační systémy, databázové systémy, síťový software

12. SW nástroje pro datový sklad • Extrakce dat • extrakce dat pro iniciální nebo inkrementální nahrávání dat • výběr nástroje závisí především na vstupních datech • Transformace dat • transformace dat do požadované formy • poskytnutí implicitních hodnot dle specifikace • operace: rozdělení polí, standardizace, zrušení duplicit,...

13. SW nástroje pro datový sklad • Nahrávání dat • nahrání upravených dat do datového skladu ve formě snímků dat • Vytvoření primárních klíčů pro přenášené tabulky • Zajištění kvality dat • asistence při nalezení a odstranění chyb • řešení datových nekonzistencí v datových snímcích • lze použít v místě přípravy dat nebo ve zdrojových datech

14. Provoz datového skladu • Cíl: Manažeři by měli mít vždy přístup k aktuálním údajům • Správa a načítání dat tedy většinou probíhá v noci (etapa ETL, přepočet krychlí, vygenerování sestav,…)

15. Data flow ETL

16. Příprava údajů – etapy ETL • Klíčová úloha správy datového skladu

17. Etapy ETL • Extrakce – výběr dat různými metodami • Transformace – ověření, čištění, integrace a časové označení dat • Loading – přesun dat do datového skladu • Hlavní cíl: centralizace údajů • nutné především proto, aby v datovém skladu byla dostatečně kvalitní data • Nikdy nekončící proces (neustále nutnost aktualizovat).

18. Hlavní úkoly ETL procesu • Určit data, která mají být uložena v datovém skladu • Určit zdroje dat, interní i externí • Příprava mapování mezi zdrojovými a cílovými daty • Stanovení pravidel pro extrakci dat • Určit pravidla pro transformaci a čištění dat • Plán pro agregaci tabulek • Návrh oblasti přípravy dat • Napsat procedury pro nahrávání dat • ETL pro tabulky dimenzí a faktů

19. Oblast přípravy dat • Mezistupeň mezi vstupní databází a datovým skladem • Dvě možnosti, kde provádět etapu ETL • Lokální vynášení: transformace se provádí ve vstupní databázi a pak se data přenáší do cílové databáze • Vzdálené vynášení: původní data se nejprve přenesou a jsou transformována v oblasti přípravy dat

20. Extrakce • Zdroj: Data z nehomogenního operačního prostředí, popř. z archivních dat • Různé možnosti extrakce • Iniciální extrakce – provádí se především z archivních dat • Periodická extrakce – z interních zdrojů • Občasná extrakce – z externích zdrojů (např. Internet, statistické ročenky,…) • Existují různé nástroje pro extrakci

21. Extrakce – součásti procesu • Identifikace zdrojů (struktury a aplikace) • Stanovení metody extrakce pro každý zdroj • manuální – napíši si sám SQL příkazy • s využitím integrovaných nástrojů • Frekvence extrakcí pro každý zdroj – většinou se liší pro různé zdroje • Stanovení časového okna pro extrakci – kdy ji provádět • Paralelní vs. sériová extrakce pro jednotlivé zdroje dat • Zpracování výjimek při extrakci

22. Extrakce – identifikace zdrojů • Výpis všech datových položek potřebných v tabulce faktů • Výpis všech dimenzí • Pro každou cílovou položku najdi zdroj a jeho položku • Je-li více zdrojů pro jednu cílovou položku, vyber preferovaný zdroj • Identifikace vícenásobných zdrojů pro jeden cíl – stanovení konsolidačních pravidel • Identifikace vícenásobných cílů na jeden zdroj – stanovení dělících pravidel • Určení implicitních hodnot • Zjištění chybějících hodnot ve zdrojových datech

23. Extrakce – metody extrakce • Metoda extrakce statických dat • Vytvoření obrazu zdrojové databáze na výstupu • Používá se při iniciálním nahrávání dat do skladu • Metody extrakce při aktualizaci dat • Metody přímé extrakce • Metody odložené extrakce

24. Metody přímé extrakce • Liší se způsobem zachycení změn v DB od posledního nahrání • Zachycení pomocí log souborů (vytvořených databází) • Zachycení pomocí databázových triggerů • Při každé změně se spustí trigger, který zapíše změnu do souboru • Zachycení pomocí samotných databázových aplikací • Editace aplikace tak, aby ukládala záznamy o provedených změnách v DB

25. Metody odložené extrakce • Nezachycují změny při jejich vzniku, ale až při nahrávání se porovnává zdrojová a cílová DB • Zachycení pomocí časových razítek • Razítky jsou označeny záznamy, které byly přidány nebo editovány – ty se pak při nahrávání dat naleznou (problém s mazáním) • Zachycení pomocí porovnávání souborů • Vytvoří se soubor s kopií dat ve stavu současném a včerejším, pak se soubory porovnají (velmi neefektivní)

26. Tipy a triky • Indexace sloupců ve WHERE klauzuli • Vybírejte pouze nezbytná data • Nepoužívejte DISTINCT, UNION, MINUS, INTERSECT • Vyhněte se používání “<>” a NOT • Minimalizujte počet funkcí ve WHERE klauzuli

27. Transformace • Cílem je zvýšit kvalitu vstupních dat a zvýšit jejich použitelnost pro cílového uživatele • Někdy je kvalita vstupních dat velmi proměnlivá → čištění dat (odstranění nekvalitních dat) • Např. atribut Adresa – 3 vs. 1 hodnota • Často je potřeba odstranit tzv. „anomálie“, které v klasických databázích běžně vznikají

28. Transformace (II.) • Příklady anomálií: • Přechod z OS Unix na MS Windows – např. kódování češtiny • Lidský faktor – různé překlepy, pravopisné chyby • Potřeba rozdělení složených atributů na atomické

29. Transformace – časté problémy • Nejednoznačnost údajů • Např. různě uložená informace o pohlaví zákazníka (M, muž, Muž,…) • Chybějící hodnoty • Tyto hodnoty je potřeba doplnit, popř. ignorovat nebo označit nějakým příznakem • Duplicitní hodnoty • Většinou není příliš velký problém je odstranit, někdy je to však časově náročné

30. Transformace – časté problémy • Konvence názvů pojmů a objektů • je nutné sjednotit terminologii požívanou různými zdroji dat • Různé peněžní měny • problém vznikne např. při přechodu z CZK na Euro • Formáty čísel a textových řetězců • použití různých datových typů pro ukládání čísel (např. řetězce)

31. Transformace – časté problémy • Referenční integrita • Neustálé změny v reálném světě zkreslují data – např. i po zrušení oddělení firmy zůstanou v DB údaje o jeho zaměstnancích • Chybějící datum • Časový aspekt je v datových skladech velmi důležitý, ve vstupních datech však čas často chybí – často je nutné jej doplnit

32. Cíle návrhu • Důkladnost – správnost a kompletnost dat • Správnost – kvalita dat i zpětně do provozních systémů • Rychlost – velký tlak na rychlé zpracování dat • Transparentnost – nalezené problémy ve zdrojových systémech je nutné řešit nikoliv skrývat

33. Transformace – typické úkoly • Selekce • Výběr vhodných atributů pro cílový sklad • Rozdělování/spojování • Rozdělení záznamu (datum, adresa,…), spojování více záznamů z různých zdrojů • Konverze • konverze záznamů (standardizace různých zdrojů, lepší použitelnost a srozumitelnost) • Sumarizace • místo detailních dat je vhodnější je sumarizovat • Obohacení • vytvoření lepšího pohledu na data na základě různých zdrojů

34. Hlavní typy transformace • Revize formátu dat • Pro tentýž atribut mohou být hodnoty např. ukládány numericky nebo jako řetězce • Dekódování polí • Různé kódování např. pohlaví,měny,… • Výpočet odvozených polí (sumarizace) • Rozdělení polí na části • datum, jméno a příjmení • Sloučení informací • Informace o jednom produktu mohou být ve více zdrojových tabulkách

35. Hlavní typy transformace • Konverze znakových sad • Konverze měrných jednotek • Konverze formátu data/času • Sumarizace • Restrukturalizace klíčů • V rámci datového skladu musí být jednotné • Deduplikace • Odstranění duplicitních řádků v tabulce způsobených především chybami

36. Přenos dat • Přesun údajů a jejich uložení do tabulek datového skladu • Pokud možno by měl probíhat automatizovaně • Rozlišujeme podle periody přesunů • závisí především na požadavcích aplikace • většinou jde o časově náročnou operaci, především u iniciálního přenosu

37. Tři typy nahrávání dat • Iniciální nahrávání • Nahrávání všech dat do prázdného skladu • Inkrementální nahrávání • promítnutí změn v DB do datového skladu (provádí se periodicky) • Přepis dat • kompletní smazání obsahu skladu a nahrání aktuálních dat

38. Módy nahrávání dat • Nahrání (Load) • Pokud cílová tabulka obsahuje data, pak jsou smazána a nahrazena aktuálními • Přidání (Append) • Přidání nových dat ke stávajícím, při duplicitě může uživatel zvolit další postup • Destruktivní sloučení • Stejné jako přidání, při stejných klíčích se přepíše hodnota daného řádku • Konstruktivní sloučení • Při stejných klíčích se přidá nový prvek a označí se jako nový, starý v datovém skladu zůstane

39. Faktové tabulky • Zrušit indexy před loadováním a následně znovu přegenerovat • Oddělit UPDATE a INSERT dat • Používat BULK load • Používat partitions podle datumů • Paralelizace loadu • Minimalizovat updaty – často lepší smazat a nahrát novou verzi • Vyřešit rollback log – často zbytečné

40. Problémy fáze ETL • Je nutné zkontrolovat správnost dat v datovém skladu • Dochází k chybám v HW i SW, výpadkům spojení • Problémy mohou vzniknout při změně formátu vstupních dat

41. Děkuji za pozornostDotazy?