HgIS

Správa a analýza dat o životním prostředí
Environmental data management and analysis

Uživatelské nástroje

Nástroje pro tento web


cs:cheatsheet

Tahák pro Pentaho Data Integration

Toto je návod pro práci s Pentaho Data Integration (PDI) – zejména s vývojovým prostředím Spoon. Nejprve si přečtěne Obecné informace o PDI a dalších komponentách platformy Pentaho.

Jak začít

  1. Nainstalujte si Javu (64-bit)1).
  2. Rozbalte archiv ZIP do libovolné složky.
  3. Spusťte Spoon.bat2)
  4. Přejděte na záložku Design.
  5. Přetáhněte položky ze složek vlevo na pracovní plochu vpravo (drag and drop).

Níže je seznam některých kroků (steps) transformací a dále totéž pro jobs (úlohy vytvořené z více transformací). Jsou zmíněny i triky a tipy, které mi nebyly z dokumentace zřejmé.

Některé často používané kroky v transformacích

Symbol Název Popis
Text file input Čtení textových souborů. Používat i pro soubory CSV (nikoliv CSV file input, který neumí zpracovat jednoduše celou složku). Pěkně umí fixed text. Pokud vyberu Number of header lines, tak budu asi potřebovat definovat sloupce ručně, protože uvažuje jen první řádek a ostatní vynechává. Nebo raději pro vývoj upravit záhlaví na jednořádkové a pro produkci použít původní soubory.
Další kroky umožňují načítat data z (a ukládat do) nejrůznějších databází, zdrojů (e-mail, lokální počítač, FTP, HTTP) a souborů (MS Excel, ESRI SHP, JSON, YAML, RSS, ZIP atd.)
Text file output Vytvoření textových souborů. Může definovat obrovskou length, díky které nahlásí chybu s pamětí – stačí smazat.
Table output Vkládání dat do SQL databáze. Obsahuje průvodce pro vytvoření připojení k databázi. Dále je třeba vybrat cílové schéma a tabulku v databázi. Dokáže také vrátit automaticky generované id záznamu.
Microsoft Excel Writer Ukládá data do dokumentu ve formátu Microsoft Excel.
Filter rows Filtrování dat řádků pomocí vybraných podmínek. Datový tok je dále rozdělen na dvě větve podle splnění/nesplnění podmínky. Pokud se mají řádky rozdělit do více než dvou větví, použij Switch-Case.
Formula Možno použít pro jednoduché výpočty (např.: sloupec_1 * sloupec_2), pro jednoduché logické podmínky či spojování řetězců. Mé příklady vzorců dole na této stránce.
Calculator Vytvoření nového pole pomocí předdefinovaných vzorců. Je rychlejší než Formula (o řádek výše), ale méně obecný.
Group by Umožňuje počítat hodnoty pro skupiny dat. V nastavení se vyberou sloupce, které definují skupinu. Počítat se může například průměr, suma, minimum, maximum, atd.
Select values Vybírání, odstraňování a přejmenovávání sloupců. Umožňuje měnit datový typ, nastavovat délku textových řetězců, počty desetinných míst u reálných čísel a další formáty dat.
Sort rows Seřadí data podle vybraného sloupce nebo několika sloupců. Podle nastavení řadí vzestupně nebo sestupně. Zaškrtnutím Only pass unique rows? nevyřadí duplicity na základě řadících kritérií. Klíč je něco jiného. Proč? Teď mi to fungovalo!!!
Replace in string Nahradí vybrané znaky/slova v textových řetězcích. Lze použít regulárních výrazů nebo přímo vyhledávací funkce, kterou tato komponenta obsahuje. Nahrazení hledané části je možné za jiný textový řetězec, nebo za hodnotu některého ze sloupců.
Split Fields Rozdělí textový řetězec v jednom sloupci do více sloupců podle zvoleného oddělovacího znaku či textového řetězce.
Stream lookup Join: propojení dvou streamů (tabulek – např. číselníku) aniž by bylo třeba mít záznamy seřazené.
Row Normaliser Type field (název nového sloupce kategorií)
Fieldname (vstupní záhlaví)
Type (hodnoty výstupních kategorií)
New field (výstupní záhlaví hodnot) – nefungovalo mi nastavit řádky různě
Row denormaliser Key označuje vstupní kategorie.
The key field (název vstupního sloupce s kategoriemi),
Group field (co identifikuje celý budoucí řádek – např. filename),
Target fieldname = Key value (jednotlivé kategorie),
Value fieldname (název vstupního sloupce s hodnotami)
Více
Set Variables Nastav proměnnou. V ostatních transformacích je možno je volat jako proměnné i jako parametry. U parametrů je možno definovat implicitní hodnotu. Parametr může být definován např. pomocí proměnné, ale má i defaultní hodnotu, která se uplatní, pokud proměnná není naplněna. Dříve byla v PDI proměnná, později parametr.
ETL Metadata Injection Řízení transformací ze streamu. Pokud chci spustit s různými parametry, je třeba kombinovat s Transformation Executor (níže).
Best practices.
Matt Casters: Parse nasty XLS with dynamic ETL
Na konci článku je příklad včetně zdrojových souborů.
Transformation Executor Spustí pro každý řádek novou transformaci. Předává proměnné, které se v odkazované transformaci volají ${takto}.
Add Constants Přidání jedné nebo více hodnot (konstant) do polí.
Analytic Query Možnost zohlednit předchozí/nadcházející hodnoty. Data musí být seřazena. Slouží k přístupu k datům v jiných řádcích. Můžeme tak data z několika řádků převést do jednoho řádku s několika novými sloupci. Data je možné seskupit podle vybraných sloupců, aby se slučovala pouze data, která mají něco společného.
Concat Fields Slučování více polí do jednoho.
Copy rows to result Zajišťuje převod dat do další transformace v rámci „jobu“. Pro vývoj netřeba spojovat jobem.
Get data from XML Načítá data z XML souborů. Je možné přímo zvolit datové typy vstupních dat a jejich formát (např. u desetinných čísel). Obsahuje funkci pro automatické načtení sloupců podle tagů v XML souboru. Pokud požadovaná vstupní data nejsou textem mezi počátečním a ukončovacím tagem, ale například atributem tagu, je nutné přidat je ručně.
Get rows From result Zajišťuje načtení dat z transformace, na kterou je tato napojena v rámci „jobu“. Aby bylo možné data načíst, musí být předchozí transformace obsahovat komponentu „Copy rows to result“. V této komponentě se pak nastavují názvy sloupců a jejich datové typy. Pro vývoj netřeba spojovat jobem.
Mail Umožňuje posílat email na předem definované adresy. Je nutné nastavit SMTP server, kterým může být například Gmail a ověření. Text emailu musí být také předem definovaný. K emailu je možné přidat přílohu, jejíž adresa (v souborovém systému) se nastavuje přímo v této komponentě.
Modified Java Script Value Umožňuje vytvářet javascriptové výrazy. Ty mohou být použity například pro upravování textových řetězců (cesty k souborům …). Ale má i spoustu dalších použití.
User Defined Java Expression Vlastní výraz napsaný v jazyce Java. Mé příklady.
Pentaho Reporting Output Umožňuje vytvářet reporty, podle předem definované šablony vytvořené v PRD. Cesta k šabloně a k souboru, který má být z této šablony vytvořen, musí být dopředu definována. V nastavení komponenty se vybírá formát výstupního reportu (např.: PDF, HTML, atd.).
Unique rows Odstraní duplicitní řádky. Nastavuje se sloupec, nebo sloupce, při jejichž shodné hodnotě u více řádků se zachová pouze první záznam. Před tímto krokem musí být data seřazena pomocí kroku Sort rows.
Microsoft Access Input Čte data ze souborů ve formátu Microsoft Access.
Microsoft Access Output Ukládá data do tabulky databáze Microsoft Access.
Merge Join Spojí řádky dvou načítaných vstupů pomocí vybraného klíče do jednoho výstupu. Vstupy musí být před spojením seřazeny podle vybraného klíče.
Row flattener Převod sloupce na řádek
Add sequence Inkrementace hodnot (indexace řádků apod.)
XBase input Čte soubory databázového typu dbf
Get file names Získá cestu/jméno ke všem souborům ve vybrané složce
Regex evaluation Vlastní regulární výraz. Mé příklady dole na této stránce: Regulární výrazy.
Block this step until steps finish
Dummy (do nothing) Spojování větví; zobrazení výsledků (např. Filter rows)

Další kroky jsou dostupné na Marketplace jako doplňky pro PDI:

  • Google Spreadsheet Input/Output
    • Extrahuje data např. z PDF či HTML.
    • Využívá Apache Tika, který podporuje různé formáty – pouze metadata k: DWG, GDAL, HDF, NetCDF, Matlab a nějakým geografickým formátům.
    • Pro PDF používá Apache PDFBox, který umí vyplňovat formuláře, dělit a slučovat soubory PDF atd.
  • Machine Intelligence (blog post) využívá volitelně různé enginy: R, Python, Weka či elegantně v Javě s využitím GPU Nvidia Deeplearning4j.

Některé často užívané entries v jobs

Symbol Název Popis
START Možnosti spouštění jobu.
Transformation Spuštění transformace
Success Možné ukončení jobu. Ignoruje chyby a vynutí úspěšné ukončení jobu.
Get a file with FTP
Mail
Unzip file
Truncate tables

Oficiální zdroj uvádí všechny kroky transformací a entries jobů.

Regulární výrazy

Regulárních výrazů je více typů. Zde jsou uvedeny tak, jak fungují v PDI.

Tabulka Výběr vstupních souborů (regulární výraz odpovídá názvu souborů)

Popis Regulární výraz
Soubory s danou příponou .*\.xlsx
Libovolné soubory.*
Soubory začínající na facts facts.*

Tabulka Výběr z textového řetězce

Popis Regulární výraz Vstup Výstup
Mezi závorkami
.*\((.*)\).*
stanice: ČK (9 m n.m) 9 m n.m
Po mezeře před závorkou
[]* (.*) \(.*
stanice: ČK (9 m n.m) ČK
Do znaku čárka, středník či tečka([^\s?:(?!;).]+).*
Do znaku čárka, středník či tečka apod.([^\s]+).*

Tabulka Validace textového řetězce

Popis Regulární výraz Detail
Validace čísla
napsaného jako text
s ne více než dvěma
desetinnými místy
[+\-]?\d+(\,\d{1,2})?
s desetinnou čárkou
Zdroj
[+\-]?\d+(\.\d{1,2})?
s desetinnou tečkou
Zdroj
6 až 8 číslic
^[0-9]{6,8}$
Zdroj
Dvě verze téhož jména
(Bill|William) Turner
Zdroj
Jména políček v šachu
např. A6
[A-H][1-8]
Zdroj
Cokoliv začínající nulou.
[0-9].*
Skutečně? Ve „Filter Rows“ je to možno udělat přímo bez regulárních výrazů.
Číslo s desetinnou tečkou (může být i záporné)^-?(\d+\.\d*)$
Číslo s tečkou na konci^(\d+.)$

Formula

Dokumentace
Názvy sloupců se zadávají v hranatých závorkách.

year([datum]) – Z plnohodnotného datumu v sloupečku datum vezme rok.
[rok]&"-"&[měsíc]&"-15" – Z textu odpovídajícímu roku a měsíci vytvoří datum (15. den)
IF(AND([nazev_veliciny]="souřadnice:"; [sloupec]="1");"x";"") – To jsem nepoužil, protože je to lepší udělat pomocí Lookup tabulky.
IF([x]>0;-1*[y];[x]) – Když jsou zamíchány souřadnice S-JTSK kladné a záporné.
IF([bottom_depth]>[predchozi];[predchozi];0) – Výpočet top_depth z bottom_depth. Sloupec predchozi se vypočítá pomocí Analytic Query.

Dílčí tipy

  • Nenechat prázdné řádky ve vstupním dialogu (smaž)
  • Chyby odvádět do samostatných větví. První krok v transformaci však nemůže někam poslat chybové řádky, protože řádky ještě nejsou v PDI.
  • Využívat krok ETL metadata injection (viz výše)
  • Využívat proměnné a parametry (viz výše)
  • Relativní cesty (${Internal.Entry.Current.Directory})
  • spojovat pomocí jména souboru,
  • Skoky (šipičky – spojení dvou kroků) je možno deaktivovat (ale někdy je to způsobí nefunkčnost zdrojového kroku
  • Ve vzdálené databázi musí být povolen přístup (JDBC) z jiného počítače
  • Zaškrtněte: Date Format Lenient, resp. Lenient number conversion, aby bylo rozpoznávání datumu resp. čísla dostatečně benevolentní.
  • Klávesové zkratky atd.
    • ctrl + šipka nahoru = zarovná podle horní hrany; posune řádek nahoru
    • zmáčknutím mezerníku na stepu se ukážou atributy, dvojklik prostředním tlačítkem → description
    • označit krok, na který chci navázat + dvojklik na nový krok v nabídce vlevo → nový krok se přidá do transformace a vytvoří se skok (spojující šipka).
    • málo užitečný oficiální zdroj
  • Dobrá praxe
    • ukládat vstupní a výstupní soubory odděleně (složky in, out), samostatně si uložit původní zdrojová data (složka ZD)
    • Dokumentovat za pomoci poznámek
  • Pro export do SQLite je třeba za pomoci SQL vytvořit tabulku a pak do ní až dávat data (možno však ve stejné transformaci, protože skritpy jsou souštěny jako první). Více kniha Pentaho Solutions 2010.
  • kettle-cookbook Automaticky generovaná dokumentace transformací3). Lepší než ta, která je přímo v PDI.
  • Best Practices – podrobné pdf
  • video o historii PDI (nelze stáhnout)
  • PBA (Pentaho Server) je možno využít jako repozitář PDI (od v.7)
    • When I start Spoon.bat in a Windows environment nothing happens. How can I solve it?
      • Edit the Spoon.bat file and:
        • Replace in the last line start javaw with only java.
        • Add a pause in the next line.
        • Save and try it again.
    • How to use JNDI?
      • If you look inside the PDI main directory you'll see a sub-directory called simple-jndi, which contains a file named jdbc.properties. You should change this file so the JNDI information matches the one you use in your application server.

Literatura

ROLDÁN, María Carina, 2017. Learning Pentaho Data Integration 8 CE : Third Edition. Packt Publishing. ISBN 978-1-78829-007-4.

1)
Je třeba 64bit! Měl jsem 32bitový Firefox, který mi tedy stahoval 32bitvou Javu – ta mi nefungovala, protože spouštěcí skript PDI vyhradil více paměti než 32bitová Java může alokovat. Pokud máte 32bitový operační systém, upravte alokovanou paměť ve spouštěcím skriptu.
Pokud potřebujete open-source verzi, použijte https://jdk.java.net/12/.
Pokud nemůžete instalovat, použijte portable verzi.
2)
tento skript také nastaví prostředí spuštěním dávky set-pentaho-env.bat – netřeba spouštět samostatně
3)
novější verze než upravoval David K.
Tato stránka využívá cookies pro analýzu provozu. Používáním stránky souhlasíte s ukládáním těchto cookies na vašem počítači.Více informací
cs/cheatsheet.txt · Poslední úprava: 2019-09-15

Nástroje pro stránku