Hochschuleigene Laderoutinen

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

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

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:

Implementierung der Laderoutine

Entscheidung über die Technik des Ladens

Unload aus dem Vorsystem

Laden der CSV-Datei

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

Einbinden in das Bewerbungen Datenblatt

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 Laderoutine kann Entladeschritte, Ladeschritte und Transformationsschritte (SQL) enthalten.

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

etl_step_type

Kontrollieren Sie, ob die BI-Tabelle etl_step_type mit fünf Datensätzen gefüllt ist.

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');

ETL-Makros zur Generierung

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.

Definition von Sonderladeroutinen

Als uniquename muss eine eindeutige Kennung gewählt werden, die auf "special" endet und bei systeminfo, 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. etl_job_params können leer bleiben.

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

<#assign    etl_job_params  =  [
    ]  />

Beispiel Entladen/Laden-Schritte

In einem einfachen Fall will man bestimmte Zusatzfelder entladen. Dazu legt man zwei ETL-Steps an.

Das Attribut "etl_job" verweist auf den ETL-JOB ("fin_inst_special") zu dem die Steps gehören sollen.

<#assign    etl_steps  =  [
{"etl_job":"fin_inst_special",  "uniquename":"unload_fin_inst",  "name":"Zusatzfelder  Inst  entladen",  "type":"UNLOAD",  "parent":""},
{"etl_job":"fin_inst_special",  "uniquename":"upload_fin_inst",  "name":"Zusatzfelder  Inst  laden",  "type":"LOAD",  "parent":""  }
    ]  />

Parameter für UNLOAD-Steps

Folgende Parameter müssen für einen UNLOAD-Step hinterlegt werden datasource,sql und unlFile.

Das attribut "datasource" gibt die Quelldatenbankverbindung in HisInOne an. (hier im Beispiel mbs).

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"  }

    ]  />

Die folgenden Parameter 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

Parameter für LOAD-Steps

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"  }
  ]  />

Die folgenden Parameter 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"  },

DOSQL-Step

Innerhalb der etl_steps Definition mach 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

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

Die folgenden Parameter 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":"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

DOQUERY-Step

Einen DOQUERY-Step, der einen einzelnen SQL-Befehli ausführt legt man folgendermaßen an:

Innerhalb der etl_steps Definition mach man einen Eintrag mit dem etl_job und einem eindeutigen uniquename, der später die SQL-Datei referenziert, 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

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

Die folgenden Parameter 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

MSG-Step

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/>

Fehlermeldungen FAQ

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.

Hochschuleigene Laderoutinen

Übersicht

Lade-Techniken

Beispielanforderung Mathenote

Ziel der Laderoutine

Implementierung der Laderoutine

Entscheidung über die Technik des Ladens

Unload aus dem Vorsystem

Laden der CSV-Datei

Transformation in finale Tabelle

Automatisierung

Einbinden in das Bewerbungen Datenblatt

ETL Sonderladeroutinen

Voraussetzungen

etl_step_type

ETL-Makros zur Generierung

Definition von Sonderladeroutinen

Beispiel Entladen/Laden-Schritte

Parameter für UNLOAD-Steps

Parameter für LOAD-Steps

DOSQL-Step

DOQUERY-Step

MSG-Step

komplettes Beispiel

Fehlermeldungen FAQ