Hochschuleigene Laderoutinen

Übersicht

Eigene Laderoutinen sind öfter gewünscht, wenn bestimmte Daten von den bisherigen Modulen (noch) nicht übernommen werden. Als Beispiele könnten sein

• erfasste Mathenote von Bewerbern
• neue Tabelle zu Lehrbeauftragten
• bestimmte Zusatzfelder in MBS.inst

Dabei geht es darum, die Daten zunächst aus dem Quellsystem zu entladen (CSV), sie dann in SuperX/BI einzuspielen und ggfs. noch zu transformieren.

Lade-Techniken

Folgende Lade-Techniken sind möglich:

• Shellscripte (DOQUERY, sx_upload_table.x )
• Kettle
• ETL-Sonderladeroutinen

Beispielanforderung Mathenote

Die Technische Universität Hamburg (TUHH) erfasst die Mathematiknoten, welche Bewerber auf ihrem bisherigen Bildungsweg erreichten. Diese Mathematiknoten sollen zukünftig in Auswertungen einbezogen werden.

Ziel der Laderoutine

Das Ziel der Laderoutine ist das Befüllen einer neuen Tabelle mit Bewerbernummer und Mathematiknote. Diese neue Tabelle wird über das Feld 'Weitere Tabellen' im "Bewerbungen und Zulassungen Datenblatt" erreichbar sein. Ggf. ist noch ein Zeitraum anzugeben.
Tabellenstruktur:

• Tabellenname: zul_bew_mathenote
• Felder:
  • Bewerbernummer (INT)
  • Mathenote (FLOAT)
• Zusätzlich zum Hochladen noch eine "_neu"-Tabelle, die quasi als Zwischenlager dient.

Implementierung der Laderoutine

Entscheidung über die Technik des Ladens

• Shellscripte (DOQUERY, sx_upload_table.x )
• Kettle
• BI Sonderladeroutine

Unload aus dem Vorsystem

Entladen der Tabelle application_content, im Ergebnis eine CSV Datei

Laden der CSV-Datei

Hochladen mit Kettle oder Shellscript in _neu-Tabelle

Transformation in finale Tabelle

Aus der _neu-Tabelle in die finale Tabelle schreiben, ggf. noch Tranformation, z.B. von Noten-Punkten zur Note Dezimal (z.B. 11="gut")

Automatisierung

Nächtliches Laden mit Shellscript oder Kettle

Einbinden in das Bewerbungen Datenblatt

• sx_tables: Einfügen der Tabellennamen
• sx_fields: Einfügen der Spaltennamen inkl. Relationen (Fremdschlüssel-Beziehung von Bewerber-Mathenote zu Bewerbung Datenblatt (zul_bew_mathenote.bewnr = zul_antr_aggr.bewnr)

ETL Sonderladeroutinen

Im ETL-Modul gibt es die Möglichkeit, eigene Sonderladeroutinen zu entwerfen. Diese können dann auch in der HISinOne-BI ab Version 2024.12 in der neuen Komponentenverwaltung genutzt werden.

Voraussetzung dafür ist, dass das ETL-Modul aus dem Downloadbereich installiert ist.

Eine Sonderladeroutine besteht aus einem ETL-Job mit mehreren ETL-Schritten. Die einzelnen Schritte können beispielsweise Daten aus einer Datenbank entladen, UNL-Dateien in Tabellen laden, SQL-Befehle ausführen oder vorhandene SQL-Skripte starten.

Damit lassen sich sowohl kleine Datenübernahmen als auch umfangreichere und komplexere Ladeprozesse individuell zusammenstellen.

Die Installation einer Sonderladeroutine läuft über ein komplexes Script, dass mit Freemarker-Variablen gefüllt wird und dann später zu reinem SQL transformiert wird, der die Installation ausführt.

Voraussetzungen

Kontrollieren Sie, ob die BI-Tabelle etl_step_type mit fünf Datensätzen gefüllt ist.
Falls diese bei Ihnen leer ist, fügen Sie folgende Step-Types ein

INSERT INTO etl_step_type (tid,uniquename,caption,handler) 
VALUES
  (1,'LOAD','Tabelle hochladen','de.superx.elt.ActionHandler.EtlActionHandlerUploadRecords'),
  (2,'DOSQL','SQL-Script ausführen','de.superx.elt.ActionHandler.EtlActionHandlerDosql'),
  (3,'DOQUERY','SQL-Query ausführen','de.superx.elt.ActionHandler.EtlActionHandlerDoquery'),
  (4,'UNLOAD','SQL-Ergebnis entladen','de.superx.elt.ActionHandler.EtlActionHandlerUnloadRecords'),
  (5,'MSG','Logausgabe','de.superx.elt.ActionHandler.EtlActionHandlerMsg');

Um fertigen SQL zu generieren muss in der verwendeten Datenbank in der Tabelle fm_templates die ETL_MAKRO aus mind. ETL-Modul 0.5 installiert sein. Damit ist nur die Generierung von Installations-SQL gemeint, zur Verwendung der Steps ist dies nicht nötig.

Definitionsdatei erstellen

Für eine neue Sonderladeroutine wird am besten eine vorhandene Vorlage kopiert und ausschließlich der Definitionsbereich am Anfang der Datei angepasst. Am Anfang der Definitionsdatei befinden sich die FreeMarker-Definitionen der Sonderladeroutine:

• etl_jobs
• etl_job_params
• etl_steps
• etl_step_properties
• testfaelle

Dieser obere Bereich wird bei der Erstellung oder Anpassung einer Sonderladeroutine bearbeitet. Darunter beginnt das erste Makro, beispielsweise <#macro etl_steps_def_check>. Ab diesem ersten Makro sollte die Datei nicht mehr verändert werden. Am Ende der Datei wird die Verarbeitung mit <@ETL_STEPS_FUELLEN/> gestartet.

Definition von Sonderladeroutinen

Ausgangspunkt ist die Definition einer (oder mehrerer) Sonderladeroutinen. Als uniquename muss eine eindeutige Kennung gewählt werden, die auf "special" endet und bei systeminfo_id, die ID zu welchem Teilbereich (wie Finanzen,Personal oder Studierende) entsprechend der Tabelle systeminfo die Sonderladeroutine gehört und unter deren Hauptkonnektor sie erscheinen soll.

logfile ist ein optionales Feld für eine Logdatei. Für normale Sonderladeroutinen kann dieser Wert leer bleiben: "logfile":"" Die bei der Ausführung erzeugten Jobprotokolle befinden sich normalerweise unter: webapps/superx/WEB-INF/logs/jobs/ Für einen Job wird dort eine Logdatei nach dem Schema JOBNAME.log erzeugt. Für die Demo-Sonderladeroutine wäre dies beispielsweise: webapps/superx/WEB-INF/logs/jobs/kern_special_demo_special.log Das Jobprotokoll ist insbesondere bei Fehlern hilfreich, da dort die Ausführung der einzelnen ETL-Schritte und mögliche Fehlermeldungen protokolliert werden.

<#assign  etl_jobs = [
  {"uniquename":"fin_inst_special", "name":"Zusatzfelder Inst und Proj aus MBS übertragen", "systeminfo_id":3 ,"logfile":""}
   ] />

Datenbankverbindungen

Bei einer Sonderladeroutine können unterschiedliche Datenbankverbindungen verwendet werden. Je nach Step-Typ heißen die entsprechenden Properties beispielsweise:

• datasource
• dataSource
• database
• sqlVarSource
• sqlVarDataSource

Wichtig ist dabei, dass hier nicht zwingend der physische Name der Datenbank angegeben wird. Verwendet wird der in SuperX konfigurierte Name der Datenbankverbindung. Die Datenbankverbindungen werden in webapps/superx/WEB-INF/conf/his1_databases.properties konfiguriert. Dort kann beispielsweise eine Verbindung unter dem Namen eduetl definiert sein. Dieser Name wird dann auch in der Sonderladeroutine verwendet:
{"prop_name":"dataSource", "prop_value":"eduetl"}
oder bei einem LOAD-Schritt:
{"prop_name":"database", "prop_value":"eduetl"}
Der physische Datenbankname kann davon abweichen. Für die Sonderladeroutine ist daher der in his1_databases.properties vergebene Name der Datenbankverbindung maßgeblich. Die genaue Konfiguration der Datei his1_databases.properties wird separat beschrieben.

ETL-Jobparameter

Über etl_job_params können Parameter für einen Ladejob definiert werden. Wenn keine Parameter benötigt werden, bleibt die Definition leer:
<#assign etl_job_params = [ ] />
Ein Parameter besitzt drei wesentliche Angaben:

• param_name
• name
• param_default

Beispiel:

<#assign etl_job_params = [
{"etl_job":"kern_special_demo_special",
 "param_name":"semester",
 "name":"Semester",
 "param_default":"20262"}
] />

Dabei bedeutet:

Attribut	Funktion
etl_job	Der Job, zu dem der Parameter gehört.
param_name	Technischer Name des Parameters, im Beispiel semester.
name	Bezeichnung des Parameters, im Beispiel Semester.
param_default	Defaultwert, der verwendet beziehungsweise bei der Ausführung vorbelegt werden kann, im Beispiel 20262.

Weitere denkbare Parameter wären beispielsweise:

• startdatum
• dateiname
• nur_pruefen

mit Defaultwerten wie:

• 2026-01-01
• daten.unl
• false

Damit können Werte, die sich zwischen einzelnen Jobausführungen ändern sollen, als Jobparameter definiert werden, anstatt sie fest in der Laderoutine zu hinterlegen. Welche Parameter tatsächlich sinnvoll sind und wie sie innerhalb eines konkreten ETL-Schrittes verwendet werden, hängt vom jeweiligen Step-Typ und dessen Handler ab. Für einfache Sonderladeroutinen werden Jobparameter normalerweise nicht benötigt.

ETL-Schritte

Die einzelnen Verarbeitungsschritte werden in etl_steps definiert. Beispiel:

<#assign etl_steps = [
{"etl_job":"kern_special_demo_special",
 "uniquename":"kern_special_demo_unload",
 "name":"Demo-Daten entladen",
 "type":"UNLOAD",
 "parent":""},

{"etl_job":"kern_special_demo_special",
 "uniquename":"kern_special_demo_create",
 "name":"Demo-Zieltabelle anlegen",
 "type":"DOQUERY",
 "parent":""},

{"etl_job":"kern_special_demo_special",
 "uniquename":"kern_special_demo_load",
 "name":"Demo-Daten laden",
 "type":"LOAD",
 "parent":""}
] />

Die wichtigsten Angaben sind:

Attribut	Funktion
etl_job	Technischer Name des Jobs, zu dem der Step gehört.
uniquename	Technisch eindeutiger Name des ETL-Schrittes.
name	Bezeichnung des Schrittes.
type	Art des auszuführenden ETL-Schrittes.
parent	Optionaler übergeordneter Step. Bei einem einfachen linearen Ablauf bleibt dieser Wert leer.

Die Reihenfolge der Einträge in etl_steps bestimmt die Ausführungsreihenfolge. Das Template unterstützt folgende Step-Typen:

Typ	Funktion
UNLOAD	Ergebnis einer SQL-Abfrage in eine Datei schreiben
LOAD	Datei in eine Datenbanktabelle laden
DOQUERY	SQL-Befehl direkt ausführen
DOSQL	SQL-Skriptdatei ausführen
MSG	Ausgabe beziehungsweise Nachricht innerhalb der Verarbeitung

Die Eigenschaften der einzelnen ETL-Schritte werden über etl_step_properties definiert. Der allgemeine Aufbau lautet:

<#assign  etl_step_properties = [
{"etl_step":"NAME_DES_SCHRITTES",
 "prop_name":"PROPERTY",
 "prop_value":"WERT"}
] />

etl_step verweist dabei auf den uniquename eines zuvor definierten ETL-Schrittes. Je nach Step-Typ stehen unterschiedliche Properties zur Verfügung. Dazu mehr in den folgenden Abschnitten.

Properties für UNLOAD-Steps

Ein UNLOAD-Schritt führt eine SQL-Abfrage auf einer Datenbank aus und schreibt deren Ergebnis in eine Datei. Folgende Parameter müssen für einen UNLOAD-Step hinterlegt werden: datasource, sql und unlFile. Im Script darf es für alle step_properties nur eine Definition mit <#assign etl_step_properties= .. > geben, weitere StepProperties müssen in dieser Aufzählung ergänzt werden.

<#assign  etl_step_properties = [
{"etl_step":"unload_fin_inst","prop_name":"datasource", "prop_value":"mbs" },
{"etl_step":"unload_fin_inst","prop_name":"sql", "prop_value":"select inst_nr, key_ext,lehr,freitext01,freitext02,freitext03,freitext04,freitext05,freitext06,freitext07,freitext08,freitext09,freitext10 from  cob.inst; " },
{"etl_step":"unload_fin_inst","prop_name":"unlFile", "prop_value":"$FIN_LOAD_PATH/unl/fin_inst_plus.unl" }
] />

Wichtige Properties:

Property	Funktion
datasource	Datenbankverbindung, auf der die SQL-Abfrage ausgeführt wird.
sqlVarSource	Datenbankverbindung für die Verarbeitung von SQL-Variablen. In der Regel wird hier dieselbe Datenbankverbindung wie bei datasource verwendet.
sql	SQL-Abfrage, deren Ergebnis entladen werden soll.
unlFile	Datei, in die das Ergebnis geschrieben wird.
sourceVersions	Optionale Einschränkung nach Versionen des Vorsystems.

Enthält das SQL selbst einfache Hochkommata, müssen diese verdoppelt werden. Statt: select 1, 'Demo' wird innerhalb von prop_value geschrieben: select 1, ''Demo''

Bei Dateien sollten möglichst die vorhandenen SuperX- beziehungsweise Modulvariablen verwendet werden. Bspw. $KERN_LOAD_PFAD/unl/datei.unl oder $SVA_PFAD/schluesseltabellen/script.sql

Die folgenden Properties werden vom Script automatisch mit defaultwerten gefüllt, könnten bei Bedarf aber zusätzlich definiert werden.

{"etl_step":"unload_fin_inst","prop_name":"active", "prop_value":"true" },
{"etl_step":"unload_fin_inst","prop_name":"custom", "prop_value":"true" },
{"etl_step":"unload_fin_inst","prop_name":"sourceVersions", "prop_value":"" },
{"etl_step":"unload_fin_inst","prop_name":"sqlVarSource", "prop_value":"" },
{"etl_step":"unload_fin_inst","prop_name":"systemInfoId", "prop_value":"xx" } -- des Jobs

Properties für LOAD-Steps

Mit LOAD wird eine Datei in eine Datenbanktabelle geladen. Die Anzahl und Reihenfolge der Felder in der Datei müssen zur Zieltabelle passen. Folgende Parameter müssen für einen LOAD-Step hinterlegt werden. Das Attribut "tableName" (hier im Beispiel "fin_inst_plus") gibt die Zieltabelle an, in die vorher entladenen Daten eingespielt werden sollen. Im Script darf es für alle step_properties nur eine Definition mit <#assign etl_step_properties= .. > geben, weitere StepProperties müssen in dieser Aufzählung ergänzt werden.

<#assign  etl_step_properties = [
{"etl_step":"upload_fin_inst","prop_name":"unlFilePath", "prop_value":"$FIN_LOAD_PATH/unl/fin_inst_plus.unl" },
{"etl_step":"upload_fin_inst","prop_name":"tableName", "prop_value":"fin_inst_plus" }
 ] />

Wichtige Properties:

Property	Funktion
database	Datenbankverbindung der Zieltabelle.
unlFilePath	Pfad der einzulesenden Datei.
tableName	Zieltabelle.
delimiter	Feldtrenner. Standard ist ^.
header	Gibt an, ob die Datei eine Kopfzeile enthält. Standard ist false.
refresh	Legt fest, ob die Tabelle vor dem Laden geleert wird. Standard ist true.
usePostgresCopyApi	Verwendet bei PostgreSQL die COPY-basierte Ladefunktion. Standard ist true.

Bei Dateien sollten möglichst die vorhandenen SuperX- beziehungsweise Modulvariablen verwendet werden. Bspw. $KERN_LOAD_PFAD/unl/datei.unl oder $SVA_PFAD/schluesseltabellen/script.sql

Die folgenden Properties werden vom Script automatisch mit defaultwerten gefüllt, könnten bei Bedarf aber zusätzlich definiert werden, insbesondere header true könnte interessant sein.

{"etl_step":"upload_fin_inst","prop_name":"database", "prop_value":"eduetl" },
{"etl_step":"upload_fin_inst","prop_name":"delimiter", "prop_value":"^" },
{"etl_step":"upload_fin_inst","prop_name":"custom", "prop_value":"true" },
{"etl_step":"upload_fin_inst","prop_name":"refresh", "prop_value":"true" },
{"etl_step":"upload_fin_inst","prop_name":"header", "prop_value":"false" },
{"etl_step":"upload_fin_inst","prop_name":"active", "prop_value":"true" },
{"etl_step":"upload_fin_inst","prop_name":"systemInfoId", "prop_value":"xx" }, -- vom Job
{"etl_step":"upload_fin_inst","prop_name":"usePostgresCopyApi", "prop_value":"true" },

Properties für DOSQL-Step

Mit DOSQL wird eine vorhandene SQL-Skriptdatei ausgeführt. Dies ist sinnvoll, wenn die Verarbeitung umfangreicher ist oder bereits ein SQL-Skript vorhanden ist. Kurze SQL-Befehle können direkt über DOQUERY definiert werden. Einen DOSQL-Step, der eine SQL-Datei ausführt legt man folgendermaßen an: Innerhalb der etl_steps Definition macht man einen Eintrag mit dem etl_job und einem eindeutigen uniquename, der später die SQL-Datei referenziert, Typ ist "DOSQL".

<#assign  etl_steps = [
{"etl_job":"meinJob", "uniquename":"update_fin_zusatzmerkmale", "name":"Aktualisierung Schlüsseltabelle fin_zusatzmerkmale", "type":"DOSQL", "parent":"" }  
] />

Innerhalb der etl_step_properties muss für den DOSQL-Step der folgende Eintrag definiert werden

<#assign  etl_step_properties = [
{"etl_step":"update_fin_zusatzmerkmale","prop_name":"sqlScriptPath", "prop_value":"$FIN_PFAD/schluesseltabellen/update_fin_zusatzmerkmale.sql" }
] />

Es sollte nicht mit SUPERX_DIR gearbeitet werden, denn bei Ausführung im Browser funktioniert das, per Shellscript ist es aber anders definiert.

Bei Dateien sollten möglichst die vorhandenen SuperX- beziehungsweise Modulvariablen verwendet werden. Bspw. $KERN_LOAD_PFAD/unl/datei.unl oder $SVA_PFAD/schluesseltabellen/script.sql

Wichtige Properties sind:

• sqlScriptPath
• dataSource
• sqlVarDataSource
• databaseSystem
• limitDebugStmts

{"etl_step":"update_fin_zusatzmerkmale","prop_name":"active", "prop_value":"true" },
{"etl_step":"update_fin_zusatzmerkmale","prop_name":"custom", "prop_value":"true" },
{"etl_step":"update_fin_zusatzmerkmale","prop_name":"databaseSystem", "prop_value":"postgres" },
{"etl_step":"update_fin_zusatzmerkmale","prop_name":"dataSource", "prop_value":"eduetl" },
{"etl_step":"update_fin_zusatzmerkmale","prop_name":"limitDebugStmts", "prop_value":"-1" },
{"etl_step":"update_fin_zusatzmerkmale","prop_name":"sqlVarDataSource", "prop_value":"eduetl" },
{"etl_step":"update_fin_zusatzmerkmale","prop_name":"systemInfoId", "prop_value":"xx" }, -- vom Job

Properties für DOQUERY-Step

Mit DOQUERY kann ein SQL-Befehl direkt ausgeführt werden. Für kurze SQL-Anweisungen ist DOQUERY meist die einfachste Möglichkeit. Einen DOQUERY-Step, der einen einzelnen SQL-Befehl ausführt legt man folgendermaßen an: Innerhalb der etl_steps Definition macht man einen Eintrag mit dem etl_job und einem eindeutigen uniquename, Typ ist "DOQUERY".

<#assign  etl_steps = [
{"etl_job":"meinJob", "uniquename":"update_test", "name":"Schlüsseltabelle fin_zusatzmerkmale aktiv schalten", "type":"DOQUERY", "parent":"" }
] />

Innerhalb der etl_step_properties müssen für den DOSQL-Step der folgende Eintrag definiert werden

<#assign  etl_step_properties = [
{"etl_step":"update_test","prop_name":"sql", "prop_value":"update fin_zusatzmerkmale set aktiv=1;" }
] />

Wichtige Properties:

Property	Funktion
dataSource	Datenbankverbindung, auf der der SQL-Befehl ausgeführt wird.
sqlVarDataSource	Datenbankverbindung für die Verarbeitung von SQL-Variablen.
sql	Auszuführender SQL-Befehl.
databaseSystem	Verwendetes Datenbanksystem, standardmäßig postgres.
sourceVersions	Optionale Einschränkung nach Quellversionen.

Enthält das SQL selbst einfache Hochkommata, müssen diese verdoppelt werden. Statt: select 1, 'Demo' wird innerhalb von prop_value geschrieben: select 1, ''Demo''

Die folgenden Properties werden vom Script automatisch mit defaultwerten gefüllt, könnten bei Bedarf aber zusätzlich definiert werden

{"etl_step":"update_fin_zusatzmerkmale","prop_name":"active", "prop_value":"true" },
{"etl_step":"update_fin_zusatzmerkmale","prop_name":"custom", "prop_value":"true" },
{"etl_step":"update_fin_zusatzmerkmale","prop_name":"databaseSystem", "prop_value":"postgres" },
{"etl_step":"update_fin_zusatzmerkmale","prop_name":"dataSource", "prop_value":"eduetl" },
{"etl_step":"update_fin_zusatzmerkmale","prop_name":"sqlVarDataSource", "prop_value":"eduetl" },
{"etl_step":"update_fin_zusatzmerkmale","prop_name":"systemInfoId", "prop_value":"xx" }, -- vom Job

Properties für MSG-Step

wird von HisInOne 2024.12 noch nicht unterstützt

Unterschiedliche Datenbank-Properties

Je nach Step-Typ wird für die Datenbankverbindung ein unterschiedlicher Property-Name verwendet.

Step-Typ	Datenbank-Property
UNLOAD	datasource
DOQUERY	dataSource
DOSQL	dataSource
LOAD	database

Die Groß- und Kleinschreibung sollte exakt übernommen werden.

Zusätzlich gibt es für die SQL-Variablenverarbeitung sqlVarSource beziehungsweise sqlVarDataSource.

Automatische Standardwerte

Das Template ergänzt verschiedene Properties automatisch, wenn sie nicht ausdrücklich definiert wurden. Für alle Steps werden unter anderem folgende Werte gesetzt:

• systemInfoId
• active = true
• custom = true

Je nach Step-Typ werden weitere Standardwerte ergänzt. Beispielsweise bei LOAD:

• database = eduetl
• delimiter = ^
• header = false
• refresh = true
• usePostgresCopyApi = true

Bei DOQUERY werden unter anderem verwendet:

• databaseSystem = postgres
• dataSource = eduetl
• sqlVarDataSource = eduetl

Damit können einfache Sonderladeroutinen relativ kompakt definiert werden. Wenn vom Standard abgewichen werden soll, wird das entsprechende Property ausdrücklich in etl_step_properties gesetzt.

Testfälle

Die Definitionsdatei bietet zusätzlich die Möglichkeit, einfache Testfälle anzugeben. Wenn keine Tests benötigt werden: <#assign testfaelle = [ ] /> Ein Testfall besteht aus:

Attribut	Funktion
testcase	Name des Testfalls.
assertion	Erwarteter Wert.
sql	SQL-Abfrage, die einen einzelnen Vergleichswert zurückliefert.

Das Template vergleicht den erwarteten Wert mit dem Ergebnis der SQL-Abfrage. Ein einfacher Test kann beispielsweise prüfen, ob der definierte ETL-Job nach dem Einspielen vorhanden ist:

<#assign testfaelle = [
{"testcase":"Demo-Job wurde angelegt",
 "assertion":"1",
 "sql":"select count(*) from etl_job where uniquename='kern_special_demo_special'"}
] />

Diese Testfälle werden beim Einspielen der Definitionsdatei ausgeführt. Sie testen damit die Installation beziehungsweise den Zustand unmittelbar nach dem Einspielen der Definition und nicht automatisch das Ergebnis einer späteren Ausführung des ETL-Jobs.

komplettes Beispiel

--Freemarker Template
<#assign  etl_jobs = [
  {"uniquename":"fin_inst_proj_special", "name":"Zusatzfelder Inst und Proj aus MBS übertragen", "systeminfo_id":3 ,"logfile":""}
  ] />
  
<#assign  etl_job_params = [
  ] />
<#assign  etl_steps = [
{"etl_job":"fin_inst_proj_special", "uniquename":"unload_fin_inst", "name":"Zusatzfelder Inst entladen", "type":"UNLOAD", "parent":""},
{"etl_job":"fin_inst_proj_special", "uniquename":"unload_fin_proj", "name":"Zusatzfelder Proj entladen", "type":"UNLOAD", "parent":""},
{"etl_job":"fin_inst_proj_special", "uniquename":"upload_fin_inst", "name":"Zusatzfelder Inst laden", "type":"LOAD", "parent":"" },
{"etl_job":"fin_inst_proj_special", "uniquename":"upload_fin_proj", "name":"Zusatzfelder Proj laden", "type":"LOAD", "parent":"" },
{"etl_job":"fin_inst_proj_special", "uniquename":"update_fin_zusatzmerkmale", "name":"Aktualisierung Schlüsseltabelle fin_zusatzmerkmale", "type":"DOSQL", "parent":"" }
  ] />

 <#assign  etl_step_properties = [
{"etl_step":"unload_fin_inst","prop_name":"datasource", "prop_value":"mbs" },
{"etl_step":"unload_fin_inst","prop_name":"sql", "prop_value":"select inst_nr, key_ext,lehr,freitext01,freitext02,freitext03,freitext04,freitext05,freitext06,freitext07,freitext08,freitext09,freitext10 from  cob.inst; " },
{"etl_step":"unload_fin_inst","prop_name":"unlFile", "prop_value":"$FIN_LOAD_PATH/unl/fin_inst_plus.unl" },

{"etl_step":"unload_fin_proj","prop_name":"datasource", "prop_value":"mbs" },
{"etl_step":"unload_fin_proj","prop_name":"sql", "prop_value":"select projnr,freitext01,freitext02,freitext03,freitext04,freitext05,freitext06,freitext07,freitext08,freitext09,freitext10 from  cob.proj; " },
{"etl_step":"unload_fin_proj","prop_name":"unlFile", "prop_value":"$FIN_LOAD_PATH/unl/fin_proj_plus.unl" },

{"etl_step":"upload_fin_inst","prop_name":"unlFilePath", "prop_value":"$FIN_LOAD_PATH/unl/fin_inst_plus.unl" },
{"etl_step":"upload_fin_inst","prop_name":"tableName", "prop_value":"fin_inst_plus" },

{"etl_step":"upload_fin_proj","prop_name":"unlFilePath", "prop_value":"$FIN_LOAD_PATH/unl/fin_proj_plus.unl" },
{"etl_step":"upload_fin_proj","prop_name":"tableName", "prop_value":"fin_proj_plus" },

{"etl_step":"update_fin_zusatzmerkmale","prop_name":"sqlScriptPath", "prop_value":"$SUPERX_DIR/db/module/etl/potsdam/update_fin_zusatzmerkmale.sql" }


  ] />
  
 
<#assign testfaelle  = [
 ] />
 
 <@ETL_STEPS_FUELLEN/>

Einspielen der Sonderladeroutine

Die fertige Definitionsdatei wird in einer geladenen SuperX-Umgebung mit DOSQL eingespielt. Beispiel:
DOSQL kern_sonderladeroutine_demo.sql
Dabei werden Job, ETL-Steps, Properties, Jobparameter und die Beziehungen zwischen den Steps erzeugt beziehungsweise aktualisiert. Anschließend steht der Job als Sonderladeroutine zur Verfügung, wenn sein uniquename auf _special endet.

Ausführung per Shellscript

Für Ausführung per HisInOne SHellscript muss ein Job das Attribut custom_job = 1 haben (0= HIS Auslieferung, 1= hochschulspezfisch,2 Memtext AUslieferung)
Das Makro ETL erzeugt derzeit 2 Memtext Auslieferung, also für Ausführung per Shellscript noch nachbearbeiten:

UPDATE etl_job SET custom_job = 1 WHERE uniquename='fin_inst_proj_special';

Allgemeine Hinweise zum Ausführen von Laderoutinen per Script finden sich im HisWiki.
Kopieren Sie unter webapps/superx/scripts/shell die Datei component_admin_cli.sh.sam nach component_admin_cli.sh und passen darin die Variable SUPERX_DIR gemäß Ihrer Installation an.
Dann geben Sie der Datei Ausführungsrechte. Eine Sonderladeroutine kann man ausführen mit

 component_admin_cli.sh -e UNIQUENAME

z.B.

 component_admin_cli.sh -e fin_inst_proj_special

Es erscheinen eine Menge Logausgaben wie

2025-06-17 09:31:00,558 [main] INFO  org.springframework.batch.core.launch.support.SimpleJobLauncher - No TaskExecutor has been set, defaulting to synchronous executor.
2025-06-17 09:31:00,613 [main] WARN  org.springframework.data.convert.CustomConversions - Registering converter from class java.lang.Boolean to class org.springframework.data.jdbc.core.convert.JdbcValue as writing converter although it doesn't convert to a store-supported type! You might want to check your annotation setup at the converter implementation.
2025-06-17 09:31:01,098 [main] WARN  org.springframework.batch.core.configuration.annotation.DefaultBatchConfigurer - No transaction manager was provided, using a DataSourceTransactionManager
2025-06-17 09:31:01,111 [main] INFO  org.springframework.batch.core.repository.support.JobRepositoryFactoryBean - No database type set, using meta data indicating: POSTGRES
2025-06-17 09:29:41,741 [main] INFO  de.superx.rest.EtlJobApi - Job not registered yet: Zusatzfelder Inst und Proj aus MBS übertragen
2

Die hier aufgeführten WARN Meldungen kann man ignorieren.Andererseits sind Meldungen, die sich konkret auf den Job beziehen wichtig. Im folgenden Beispiel wird ein Script nicht gefunden

 2025-06-17 09:31:02,641 [main] WARN  de.superx.spring.service.BatchJobDescriptionAdapter - SQL Skriptfile doesn't exist: $FIN_PFAD/potsdam/7950_update_fin_zusatzmerkmale.sql

oder hier ein allgemeiner ERROR-Meldung

 2025-06-17 09:37:16,188 [main] ERROR de.superx.bin.ComponentAdminCLI - ERROR executing job fin_inst_proj_special

Der Exitstatus des Script scheint in HisInOne2024.12 auch bei Fehlern noch teilweise 0 zurückzuliefern.
Man sollte also auf etwaige Fehler ggfs. manuell die Logdateien prüfen.
Diese liegt unter webapps/superx/WEB-INF/logs/jobs/JOBNAME.log

Demo-Sonderladeroutine

Als Vorlage steht eine bewusst klein gehaltene Demo-Sonderladeroutine zur Verfügung.

Die Demo-Datei befindet sich im Git-Repository:

BI_Maintenance

Sie kann als Ausgangspunkt für eigene Sonderladeroutinen verwendet werden. Dabei sollte nur der Definitionsbereich am Anfang der Datei angepasst werden. Der darunterliegende Makrobereich bleibt unverändert.

Die Demo zeigt einen möglichst kleinen vollständigen Ablauf.

Der erste Step entlädt einen einfachen Datensatz:

select 1 as id, 'Demo' as name;

Das Ergebnis wird in eine UNL-Datei geschrieben.

Anschließend erzeugt ein DOQUERY-Step eine kleine Zieltabelle:

create table if not exists tmp_kern_special_demo
(
    id integer,
    name varchar(20)
);

Der abschließende LOAD-Step lädt die zuvor erzeugte UNL-Datei in diese Tabelle.

Der Ablauf ist damit:

Datenbank
  ↓
UNLOAD
  ↓
UNL-Datei
  ↓
LOAD
  ↓
Zieltabelle

Die Demo zeigt damit die grundlegende Arbeitsweise einer Sonderladeroutine, ohne dass komplexes SQL vom Aufbau der ETL-Schritte ablenkt.

Hinweise

• Eine vorhandene Vorlage kopieren.
• Nur die FreeMarker-Definitionen am Anfang der Datei bearbeiten.
• Ab dem ersten Makro den Inhalt der Vorlage unverändert lassen.
• Der uniquename des ETL-Jobs muss auf _special enden.
• Für Datenbanken den in his1_databases.properties definierten Verbindungsnamen verwenden.
• Der Verbindungsname muss nicht mit dem physischen Datenbanknamen übereinstimmen.
• Die Property-Namen einschließlich Groß- und Kleinschreibung exakt übernehmen.
• Bei SQL innerhalb von prop_value einfache Hochkommata verdoppeln.
• Bei LOAD darauf achten, dass Aufbau der Datei und Aufbau der Zieltabelle zusammenpassen.
• Für kurze SQL-Befehle eignet sich DOQUERY, für umfangreichere vorhandene SQL-Dateien DOSQL.
• Bei Problemen das Jobprotokoll unter WEB-INF/logs/jobs/JOBNAME.log prüfen.

Fehlermeldungen FAQ

Cannot invoke "javax.sql.DataSource.unwrap(java.lang.Class)" because the return value of  de.superx.spring.batch.reader.JdbcUnloadReader.getDataSource()" is null

bedeutet,dass bei einem UnloadStep als dataSource z.B. "mbs" angegeben wurde, aber in der databases.xml (oder Spezialversion davon) keine Datenquelle "mbs" definiert ist.

ERROR:  null value in column "step_type_id" of relation "etl_step" violates not-null constraint

Kontrollieren Sie, ob die Tabelle etl_step_type gefüllt ist (s.o.).

 [main] ERROR de.superx.bin.ComponentAdminCLI - ERROR executing job fin_inst_proj_special org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT tid, uniquename, caption, systeminfo_id, custom_job FROM etl_job WHERE uniquename like ?]; nested exception is org.postgresql.util.PSQLException: Cannot cast to boolean: "2.0"

Für Ausführung per HisInOne SHellscript muss ein Job das Attribut custom_job = 1 haben (0= HIS Auslieferung, 1= hochschulspezfisch,2 Memtext AUslieferung)
Das Makro ETL erzeugt derzeit 2 Memtext Auslieferung, also für Ausführung per Shellscript noch nachbearbeiten:

UPDATE etl_job SET custom_job = 1 WHERE uniquename='fin_inst_proj_special';