JSON-Messages in traditioneller Staging-Area verarbeiten

Viele Unternehmen nutzen das Potenzial ihrer Daten aktuell noch nicht aus, doch ist das Bewusstsein und die Einschätzung hinsichtlich der Datenschätze vorhanden – aber wie wir alle wissen, ist es nicht immer einfach, einen Schatz zu finden und diesen anschließend auch zu bergen.

Wir bei in-factory haben die Erfahrung gemacht, dass unsere Kunden sich der Herausforderungen und notwendigen Expertise bewusst sind. Deshalb nehmen sie unsere Services in Anspruch. Wir bringen seit über 25 Jahren die Erfahrung und das Know-How in solchen Projekten mit. Für uns ist klar ersichtlich, dass Unternehmen in Deutschland die Potenziale erkannt haben und im Kontext der digitalen Transformation für die Erreichung ihrer Unternehmensziele ganz bewusst auf Datenintegrationsprojekte setzen.

JavaScript Object Notation (JSON)-Messages sollen im klassischen Staging-Bereich eines Data Warehouse (DWH)s eingelesen werden. Diese Struktur wurde bisher genutzt um Flatfiles zu verarbeiten. Die eingehenden JSON-Messages sollen kontinuierlich eingelesen und die Daten in Tabellen des Staging-Bereichs gespeichert werden.

Die Daten sollen dann per Batch-Load aus dem Staging-Bereich regelmäßig in das Data Warehouse geladen werden.
Die hier genutzten Funktionen sind ab Oracle Database 12c Release 1 (12.1.0.2) verfügbar.

Unser Lösungsansatz

Datenablage

Die Messages werden z.B. aus Kafka als JSON in die Datenbank eingefügt.
Der Payload wird sinnvollerweise in einer Spalte mit dem Datentyp Binary Large Object (BLOB) oder Character Large Object (Clob) gespeichert. Oracle empfiehlt die Nutzung von BLOB, da hier keine Konvertierung nach Character durchgeführt werden muss und je nach Zeichensatz weniger Speicher belegt wird. Die Syntax der JSON kann implizit bereits beim Insert validiert werden. Dazu muss der Constraint „IS JSON“ mit der Option STRICT oder LAX auf die Spalte angewendet werden. Als Default gilt die Option LAX, dies entspricht JavaScript Syntax für Objektfelder, Boolean und Null Values sind nicht case-sensitive.

Basisfunktionen

JSON_TABLE erstellt für jedes Objekt innerhalb eines JSON-Arrays eine Zeile mit relationalen Daten. JSON-Werte aus diesem Objekt werden als einzelne SQL-Spaltenwerte ausgegeben.

JSON_VALUE findet einen angegebenen skalaren JSON-Wert in JSON-Daten und gibt ihn als SQL-Wert zurück.

JSON_QUERY findet einen oder mehrere angegebene JSON-Werte in JSON-Daten und gibt die Werte als Character zurück.

Praktische Umsetzung

Demo Vorbereitung

Für das Beispiel müssen die folgenden Datenbank (DB)-Objekte angelegt werden:
Die Tabelle STG_CTRL_DELIVERY:

CREATE TABLE STG_CTRL_DELIVERY

);

Die View V_STG_CTRL_DELIVERY_CONTACT:

CREATE OR REPLACE VIEW V_STG_CTRL_DELIVERY_CONTACT

WHERE STG.STATUS = 'to-process');

Und die View V_STG_CTRL_DELIVERY_CONTACT_EMAIL:

CREATE OR REPLACE VIEW V_STG_CTRL_DELIVERY_CONTACT_EMAIL

WHERE STG.STATUS = 'to-process';

Außerdem muss der Beispiel-Payload in die Tabelle STG_CTRL_DELIVERY – wie im folgenden Codebeispiel gezeigt – eingefügt werden:

Insert into STG_CTRL_DELIVERY

(1, SYSDATE, 'JSON_DOCUMENT','to-process');

Wobei ‘JSON_DOCUMENT‘ durch den Inhalt der JSON-Datei ersetzt werden muss:

{

],

}
] }
}
}
{

Datenselektion

In dieser Demo werden aus diesem Payload mithilfe von Views Daten aus JSON-Messages ausgelesen.

Diese Messages sind in einer Steuerungs-Tabelle gespeichert. Ein externer Prozess liefert die Messages an und setzt den Status auf „to-process“. Dieser Status ist der Scope der hier verwendeten Views.

Die Views lesen für jede Gruppe im JSON die benötigten Attribute aus. Dazu werden Oracle JSON-Funktionen verwendet. Die Relationen zwischen den Objekten werden über erzeugte IDs dargestellt.

Die Daten der Views können dann in Tabellen übernommen werden und von dort klassisch weiterverarbeitet werden. Der Status muss nach der Übertragung für die verarbeiteten IDs aktualisiert werden um Sie aus dem Scope der View zu entfernen.

Der Beispiel-Payload in der Steuerungs-Tabelle enthält zwei Kontakte mit diversen Attributen, beiden Kontakten sind E-Mail-Adressen zugeordnet.

Der Beispiel-Payload in der Steuerungs-Tabelle enthält zwei Kontakte mit diversen Attributen, beiden Kontakten sind E-Mail-Adressen zugeordnet.

Wichtig ist hier vor allem die Relation zwischen dem Hauptobjekt und dem Unterobjekt.
Kontakt 1 (CON_ROWNUM 1) besitzt eine E-Mail-Adresse (CON_COM_ROWNUM 1).
Kontakt 2 (CON_ROWNUM 2) besitzt zwei E-Mail-Adressen (CON_COM_ROWNUM 1+2).
Auf jeder Ebene wird hier eine ID generiert (CON_ROWNUM + CON_COM_ROWNUM). Dies wird über die Option „ROW_NUMBER FOR ORDINALITY“ realisiert.


CREATE OR REPLACE VIEW V_STG_CTRL_DELIVERY_CONTACT_EMAIL
AS
SELECT STG.DELIVERY_ID,
ChannelType,
ChannelValue,
ROW_NUMBER AS con_rownum,
con_data.row_number_sub AS con_com_rownum
FROM HARSCAX.STG_CTRL_DELIVERY STG,
JSON_TABLE (
stg.JSON_DOCUMENT,
'$."Payload".Data.Contacts.Contact[*]'
COLUMNS (
ROW_NUMBER FOR ORDINALITY,
NESTED PATH '$.CommunicationChannel[*]'
COLUMNS (
row_number_sub FOR ORDINALITY,
ChannelType VARCHAR2 (300) PATH '$.ChannelType',
ChannelValue VARCHAR2 (300) PATH '$.ChannelValue')))
con_data
WHERE STG.STATUS = 'to-process';


Weiterführende Dokumentation ist auf der Oracle-Website verfügbar.