Grundsätzliches zum Datenflow
Nach einer SQL-Aktion mit dem Löschen von Daten muss man einen Warten Step einbauen.Ab einem bestimmten Step kann man in mehrere folgende Steps verzweigen, denen allen der gesamt Flow zur Verfügung steht.
(Methode "Copy" auswählen, nicht "Distribute").
Komplexes Beispiel
siehe unter Aus Excel in Datenbank schreiben / Werte nachtragen (Beispiel erst Kosten- dann Beschäftigungsstellen)
Beispiel db/module/sva/etl/sap_hr/sap_load_stamm_beschstellen.ktr Step Insert sva_inst_neu
Informationen zu einzelnen Steps
Update Step
siehe "Aus Exceldatei in Datenbank schreiben / Aus mehreren Sheets
Filter Step (ähnlich SQL where)
siehe unter "Aus Exceldatei in Datenbank schreiben / Erst bestimme Zeilen rausfiltern und dann Update
Beispiel db/module/sva/etl/sap_hr/sap_load_pgd_eintrittssdatum.ktr
Man kann auch mehrere Bedingungen angeben, dazu klickt man auf das kleine rot umrandete Plus Zeichen.
Group by Step
Max/Min Ermitteln - siehe "Aus Exceldatei in Datenbank schreiben / Max(Spalte) ermitteln und nachtragen (Beispiel db/module/sva/etl/sap_hr/sap_load_pgd_austrittsdatum.ktr)
Kann noch mehr auch ersten/letzen Wert ermitteln, interessant für Gültigkeiten
siehe Bei mehreren Datenzeilen Neuste raussuchen (Gültigkeit) ( Beispiel db/module/sva/etl/sap_hr/sap_load_pgd_wohnort.ktr)
Select Values
Hiermit kann man Feldnamen umbennen oder auch Datentypen ändern im Reiter "Metadata"
siehe auch unter "Aus Exceldatei in Datenbank schreiben / Aus String Jahr "2000" ein Datum machen (1.1.2000)
Beispiel db/module/sva/etl/sap_hr/sap_load_pgd_habil.ktr
User Defined Java Expression
Siehe Beispiel unter "Aus Excel in Datenbank schreiben" / Den kleineren/größeren Wert aus zwei Spalten ermitteln
weiterhin Beispiel unter "Aus Excel in Datenbank schreiben / Feld wird zu Decimalzahl 1020,0 transformiert soll aber String sein
Get Value from Sequence
Kann man z.B. für erzeugen einer künstlichen ID gebrauchen, siehe unter Aus Excel in Datenbank schreiben / Eine Künstliche ID erzeugen
db/module/sva/etl/sap_hr/sap_load_stamm_laufbahn.ktr.
Um die erzeugte ID aus der Datenbank auszulesen nutzt man Database Lookup
Database Lookup
Kann z..B: verwendet werden, um eine ID aus der Datenbank zu holen.
siehe unter Aus Excel in Datenbank schreiben / Eine Künstliche ID erzeugen
db/module/sva/etl/sap_hr/sap_load_pbl.ktr.
Insert / Update Step
Kann verwendet werden, wenn man für bestehende Datensätze einen Update machen möchte und für noch nicht vorhandene Einträge einen Insert.
Aber auch, wenn man gar keine Updates machen will, sondern nur Inserts wenn noch kein Eintrag vorhanden ist.
siehe unter Aus Excel in Datenbank schreiben / Werte nachtragen (Beispiel erst Kosten- dann Beschäftigungsstellen)
Beispiel db/module/sva/etl/sap_hr/sap_load_stamm_beschstellen.ktr Step Insert sva_inst_neu
Aus Exceldatei in Datenbank schreiben
Aus einem Sheet
Um Daten zu löschen legen man einen Step "Execute SQL script" an. Beispielhafter Inhalt delete from sva_pbl_neu;
Als Nächstes "Block until steps finishes"
- Dann "Microsoft Excel Input" -wichtig, Datentypen kontrollieren, S. unter Informationen zu einzelnen Steps / Microsoft Excel Input
Weiterhin war die Erfahrung, dass man bei Fields "Get Fields from Header row" Alle Felder drin lassen sollte, nicht nur die tatsächlich benötigten.
Andernfalls schien Kettle durcheinander zu kommen und meldete z.B. für Tarifart (String) falscher Datentyp (Date), weil er die falsche Spalte gelesen hat.
- Dann Table Output
Beispiel db/module/sva/etl/sap_hr/sap_load_pgd.ktr
Aus mehreren Sheets
Um aus mehreren Sheets zu lesen, muss man pro Sheet eine Transformation anlegen, da sonst im Flow die Felder aus dem ersten Sheet anscheinend nicht vom zweiten Sheet überschrieben werden.
Beispiel db/module/sva/etl/sap_hr/sap_load_pbl.ktr und sap_load_pbl2.ktr.
Die Informationen aus dem zweiten Sheet werden per "Update Step" nachgetragen.
Wenn in dem zweiten Excelsheet nicht definitiv alle primary keys enthalten sind, die im ersten sind, muss man "Skip Lookup" aktivieren, sonst kommt eine Fehlermeldung, dass z.B. für eine Personalnummer keine Amts-/Dienstbzeichnung gefunden wurden.
Entry to update with following key could not be found: 47
Max(Spalte) ermitteln und nachtragen
Im Beispiel soll das austrittsdatum aus einem Excelsheet nachgetragen werden, dazu muss man für jede Personalnummer das max(Ende) ermitteln.
Beispiel db/module/sva/etl/sap_hr/sap_load_pgd_austrittsdatum.ktr
Dies geht in dem man zunächst die Exceldatei einliest.
Dann ist für das "Group by" zunächst ein "Sort Rows" nötig.
Anschließend kommt ein "Group By"-Step
Und zum Schluss ein "Update"-Step - hier wieder "Skip lookup" aktivieren, siehe unter Aus Exceldatei in Datenbank schreiben / Aus mehreren Sheets
Erst bestimme Zeilen rausfiltern und dann Update
Im Beispiel soll das Eintrittsdatum aus einem Excelsheet ermittelt werden, dazu muss man für jede Personalnummer nur die Zeilen mit DaArt=01 lesen und dann das min(Datum) bestimmen.
Beispiel db/module/sva/etl/sap_hr/sap_load_pgd_eintrittssdatum.ktr
Dies geht in dem man zunächst die Exceldatei einliest.
Dann folgt ein "Filter Rows" auf DatAr=01
Dann ist für das "Group by" zunächst ein "Sort Rows" nötig.
Anschließend kommt ein "Group By"-Step
Und zum Schluss ein "Update"-Step - hier wieder "Skip lookup" aktivieren, siehe unter Aus Exceldatei in Datenbank schreiben / Aus mehreren Sheets
Bei mehreren Datenzeilen Neuste raussuchen (Gültigkeit)
Im Beispiel soll zu Personalgrunddaten die aktuelleste Adresse PLZ etc ermittelt werden. Dazu gibt es ein Sheet, das pro Personalnummer mehrere Einträge mit Gültigkeiten (Beginn/Ende) geben kann.
Beispiel db/module/sva/etl/sap_hr/sap_load_pgd_wohnort.ktr
Vorgehen:
Excelsheet auslesen
Im Beispiel zusätzlich Filter auf Art=1 (ständiger Wohnsitz, nicht Zweitwohnsitz)
Sort Rows nach PersNr und Ende
Group By - Step mit Type "Last Value"
Dann wieder "Update Step"
Aus String Jahr "2000" ein Datum machen (1.1.2000)
Als erstes muss man einen FIlter machen mit der Einstellung nur Datensätze where is not null.
Dann "Add Constants" erster_erster 01/01/
Dann einen Concat step, bei der man die Felder erster_erster und das Zielfeld zusammenfügt.
Dann einen "Select Values" hier wählt man Felder aus, die im weiteren Flow berücksichtigt werden müssen und für das umzuwandelnde Feld, beim Reiter Metadata den Datentyp Date mit Format dd/MM/yyyy.
Beispiel db/module/sva/etl/sap_hr/sap_load_pgd_habil.ktr
Den kleineren/größeren Wert aus zwei Spalten ermitteln
Im Beispiel gibt es zwei Felder "Auf Zeit" und "Auf Lebenszeit", das Feld mit dem kleineren Wert in jeder Zeile soll genommen werden.
Dazu nutzt man einen "User Defined Java Expression" Step.
Damit Java die Variablen verarbeiten kann, zunächst einen "Select Values" Step und ändert die Feldnamen auf Namen ohne Leerzeichen, PersNr muss auch übergeben werden, da für Update benötigt.
Als Nächstes ein "User Defined Java Expression" mit der JavaExpression
(lebenszeit==null&&auf_zeit==null)?null:(lebenszeit==null&&auf_zeit!=null)?auf_zeit:(lebenszeit!=null&&auf_zeit==null)?lebenszeit:new Long(Math.min(lebenszeit,auf_zeit))
Also wenn beide Werte null sind, dann null, wenn einer der beiden null ist, dann den anderen und wenn beide gefüllt sind, dann das Minimum.
Wichtig ist, noch dass man bei dem langen Feld für Java Expression nicht übersieht, einen Datentyp auszuwählen.
Anschließend kann wie gewohnt, ein Update Step genutzt werden.
Beispiel db/module/sva/etl/sap_hr/sap_load_pgd_habil.ktr
Feld wird zu Decimalzahl 1020,0 transformiert soll aber String sein
Beim Einlesen von Kostenstellen kam es zu Problem, dass dass Feld zu einer Decimalzahl wie 1020,0 transformiert wurde, umstellen auf Datentyp Integer beim Einlesen oder entsprechender "Select Values" Step mit Meta-Datenänderung auf String brachte nichts.
Abhilfe ein "User Defined Java Step".
Die Java Expression Kostenst==null?null:Kostenst.trim().replaceAll(",0","") entfernte auch Leerzeichen am Anfang.
Beispiel db/module/sva/etl/sap_hr/sap_load_pfi.ktr
Eine Künstliche ID erzeugen
Für das Excelfeld "Laufbahngruppe" gibt es nur Bezeichnungstext, keinen Schlüssel.
Man kann so vorgehen:
FIlter Values - keine Nulls
Sort Values - by Laufbahngruppe
Unique Rows - Laufbahngruppe
Get Value from Sequence - Laufbahngruppe
In Datenbank schreiben.
Beispiel db/module/sva/etl/sap_hr/sap_load_stamm_laufbahn.ktr
Um diese ID dann bei der Verarbeitung der Rohdaten wieder auszulesen nutzt man "Database Lookup"
Beispiel db/module/sva/etl/sap_hr/sap_load_pbl.ktr.
Werte nachtragen (Beispiel erst Kosten- dann Beschäftigungsstellen)
Aufgabe: Zunächst sollen aus einem Sheet alle Kostenstellen eingetragen werden, dann bei den Beschäftigungsstellen diejenigen nachgetragen werden, die noch nicht als Kostenstellen eingetragen wurden.
Dazu kann man einen Insert / Update Step verwenden.
Es sollen keine Updates gemacht werden, die Maske ist zunächst etwas verwirrend. Man wählt "Don't perform any updates" aus.
Bei "Keys to look up" die key_apnr und das Feld im Stream (hier kostenstellenStr).
Dann steht da zwar "Update Fields", aber diese werden eingefügt, wenn noch kein Eintrag für die apnr in der Tabelle sva_inst_neu gefunden wurde.
Sicherheitshalber auch UPdate=N ausgewählt.
Beispiel db/module/sva/etl/sap_hr/sap_load_stamm_beschstellen.ktr Step Insert sva_inst_neu
