Oracle SQL MATCH_RECOGNIZE

Ein sehr mächtiges und effizientes Konstrukt gibt es seit der Oracle Version 12C: MATCH_RECOGNIZE. Vereinfacht gesagt lassen sich mit MATCH_RECOGNIZE regular expressions auf Zeilen anwenden, um so bestimmte Muster zu erkennen. Beispielsweise lassen sich somit in der Finanzwelt bestimmte Aktienkurs-Verläufe erkennen. Dieser Blogbeitrag soll dazu dienen, sich diesem Thema anhand einfacher und fiktiver Beispiele langsam anzunähern.
Grundlegender Aufbau:

 

 

PATTERN_PARTITION_CLAUSE

Diese ist optional. Hier kann man die Daten sortieren (ORDER BY) und in Gruppen aufteilen (PARTITION BY). Um bei jeder Ausführung der Query konsistente Ergebnisse zu erhalten, sollte zumindest ein ORDER BY eingebaut werden.

PATTERN_MEASURES_CLAUSE

Dieser Abschnitt definiert die Spalten, welche im SELECT Teil ausgegeben werden. Es gibt einige inbuilt functions, welche hier verwendet werden können. Dazu später mehr.

PATTERN_DEF_DUR_CLAUSE

Hier wird die eigentliche magic definiert. In diesem Abschnitt werden die patterns definiert, welche über jede Zeile ausgeführt werden. Gibt es ein match, sprich wird genau dieses Muster identifiziert, wird die Zeile ausgegeben.

 

Wir schauen uns anhand einiger Beispiele die einzelnen Abschnitte näher an. Zu diesem Zwecke habe ich eine Tabelle mit Aktiendaten erstellt, welche wie folgt definiert ist (Das Skript kann am Ende des Artikels heruntergeladen werden):

 

 

 

 

 

 

Die Tabelle beinhaltet Aktienkurse eines fiktiven Unternehmens. Wir starten mit einer simplen Abfrage, anhand welcher ich auf die Struktur näher eingehen möchte: Alle Handelstage, an denen es einen oder mehrere Handelstage gibt, welche einen Startkurs größer als 17 haben:

 

 

 

 

 

 

 

 

 

Nachfolgend eine Erklärung der einzelnen Codeteile:

PARTITION BY: Unterteilt die Daten je nach Titel in einzelne Gruppen. In unserem Fall haben wir nur einen Titel in unseren Testdaten, wodurch es sowieso nur eine Gruppe gibt, nämlich die der „Datenbank AG“.

ORDER BY: Sortiert die Daten in der jeweiligen Gruppe je nach Handelstag aufsteigend.

MEASURES: Definiert, welche Spalten im result set zusätzlich ausgegeben werden. Hier sehen zwei inbuilt functions: CLASSIFIER() und MATCH_NUMBER(). CLASSIFIER () gibt an, welche pattern variable für diese Zeile gematched hat. MATCH_NUMBER () vergibt für gematchte Gruppe eine eigene Nummer, welche bei 1 startet und sich für jede neue Gruppe um 1 erhöht.

ALL ROWS PER MATCH: Definiert, dass bei aufeinanderfolgenden matches jede Zeile ausgegeben wird. Das pendant dazu wäre ONE ROW PER MATCH, welches auch der Default Wert ist. Hier wird dann nur die erste Zeile der jeweiligen Gruppe ausgegeben. In unserem Beispiel sehen wir, dass für Gruppe 4 jede einzelne Zeile ausgegeben wird; würden wir stattdessen ONE ROW PER MATCH verwenden, würden wir für Gruppe 4 nur die erste Zeile sehen. Das Ganze hat dann auch Auswirkungen auf die oberen drei Bereiche: Bei ALL ROWS PER MATCH, so wie wir es verwenden, könnte ich den den PARTITION BY, ORDER BY und MEASURES Bereich ganz weglassen, da sowieso alle Spalten im result set ausgegeben werden. Verwende ich hingegen ONE ROW PER MATCH, muss ich entweder Spalten im MEASURES Bereich angeben und/oder die ORDER BY / PARTITION BY Klausel angeben. Das Prinzip ist dem GROUP BY also sehr ähnlich.

PATTERN: Hier liste ich die einzelnen Variablen auf, welche im DEFINE Bereich definiert sind und auf die jeweilige Zeile zutreffen müssen. In unserem Beispiel ist das die Variable „start_1“ mit einem regular expression chracter „+“. Dies bedeutet, dass es einen oder mehrere Handelstage geben muss, an denen der Startkurs höher als 17 ist.

DEFINE: Hier werden die Variablen mit ihren conditions definiert, welche im PATTERN Bereich verwendet werden können.

 

 

 

 

 

 

 

Wie können wir das Ergebnis interpretieren?

Auf die Handelstage 01.12.2022, 03.12.2022 und 09.12.2022 folgen jeweils Tage, an denen der Startkurs kleiner als 17 ist. Der 13.12. ist der erste Tag einer Reihe von Handelstagen, wo der Startkurs höher als 17 ist.

Im nächsten Beispiel wollen wir alle Handelstage ausgeben, an denen exakt zwei Mal in Folge der Startkurs höher ist als der Schlusskurs am Vortag.

 

 

 

 

 

 

 

Hier sehen wir, dass das SQL im Prinzip ähnlich aufgebaut ist als im Beispiel eins. Im DEFINE Bereich ist unsere pattern Variable mit der dazugehörigen condition definiert. Diese besagt, dass der Startkurs der jeweiligen Zeile höher sein muss als der Schlusskurs der darüberliegenden Zeile. Hierzu verwende ich die Funktion PREV(), welche dann genau auf die darüberliegende Zeile verweist. An diesem Beispiel sehen wir auch, dass es Sinn macht, die Daten zu sortieren, da wir nicht immer wissen, in welcher Reihenfolge die Daten aus der Datenbank abgefragt werden (das Ergebnis ist also nicht deterministisch). Im PATTERN Bereich holen wir uns die Variable und wandeln diese nun mit „{2}“ zu einem regulären Ausdruck um. Dies bedeutet, dass wir zwei Handelstage in Folge haben wollen, auf welche die condition zutrifft. Zur Kontrolle definieren wir im MEASURES Bereich die Spalte „schlusskurs_vortag“. Da MATCH_RECOGNIZE wie eine INLINE VIEW zuerst ausgeführt wird, können wir diese im SELECT Teil verwenden und ausgeben.

 

 

 

 

 

Wie können wir das Ergebnis interpretieren?

Wir sehen nun immer zwei Handelstage in Folge, an denen der Startkurs höher ist als der Schlusskurs am Vortag.

Für das nächste Beispiel überlegen wir uns folgendes Muster: An Handelstag eins soll der Startkurs zwischen 16 und 17 sein, an Handelstag zwei soll der Startkurs größer als 17 sein, an Handelstag drei soll der Startkurs wieder zwischen 16 und 17 sein.

 

 

 

 

 

 

 

 

Dieses SQL enthält eine Erweiterung, nämlich AFTER MATCH SKIP TO LAST. Diese Syntax bedeutet, dass wenn es ein match gibt, die Suche nach einem neuen Match bei der letzten pattern Variable beginnt.

 

 

 

 

 

Wie können wir das Ergebnis interpretieren?

Wir sehen nun alle Handelstage, die genau unserem Muster entsprechen. Würden wir AFTER MATCH SKIP TO LAST weglassen, würden nur die ersten drei Handelstage im result set erscheinen, da die Suche erst am 22.12.2022 weitergehen würde.

In unserem letzten Beispiel wollen wir eine typische V-Formation erkennen. Dies bedeutet, dass der Aktienkurs zunächst einbricht und sich nach Erreichen der Talsohle wieder erholt.

 

 

 

 

 

 

 

 

 

 

 

 

Hier haben wir im DEFINE Bereich drei pattern Variablen definiert, wobei man die Definition der ersten Variable „strt“ auch weglassen könnte, da sie sowieso jede Zeile matched. Diese dient für uns lediglich als Einstiegspunkt in unsere Mustersuche.

 

 

 

 

 

 

 

Wie können wir das Ergebnis interpretieren?

Wir sehen in unserem result set anhand der Gruppennummer, welche Handelstage eine V-Formation bilden und somit zu einer Gruppe gehören. Das Ende der jeweiligen V-Formation ist zugelich der Beginn einer neuen Kursbewegung nach unten.

Wir haben uns nun anhand einiger Beispiel angesehen, was MATCH_RECOGNIZE ist und was für Abfragen wir damit durchführen können.

 

 

Blogpost ODI.jpg

ODI // Getting an OdiInstance

Bisher musste man, wenn man bei der Ausführung einer ODI-Procedure der Technologie „Groovy“ am Agent die SDK ansprechen wollte, manuell im Sourcecode eine Connection zum Master- und Workrepository herstellen. Das macht es notwendig, dass die Connect-Info in einer Konfiguration hinterlegt werden musste.
Seit dem ODI der Version 12.2 gibt es aber eine odiRef-Funktion, die all diesen Aufwand obsolet macht und eine Instanz der Klasse OdiInstance direkt verfügbar macht.
Im Sourcecode schaut der Unterschied folgendermaßen aus:

pre ODI 12.2:

// Master Respository
String masterRepositoryJdbcDriver = "oracle.jdbc.OracleDriver";
masterRepositoryJndiName = null; // if agent is running on WLS you could use a JDNI data source for repo connection
MasterRepositoryDbInfo mRepDbInfo = null 
if (masterRepositoryJndiName)
  mRepDbInfo = new MasterRepositoryDbInfo(masterRepositoryJndiName, new PoolingAttributes()); 
else {
  masterRepositoryJdbcUrl      = "jdbc:oracle:thin:@host:port/service";
  masterRepositoryJdbcUser     = "ODI_REPO_SCHEMA";
  masterRepositoryJdbcPassword = "*****";
  mRepDbInfo = new MasterRepositoryDbInfo(masterRepositoryJdbcUrl, masterRepositoryJdbcDriver, masterRepositoryJdbcUser, masterRepositoryJdbcPassword.toCharArray(), new PoolingAttributes());
}

// Work Repository
workRepositoryName = "WORKREP";
WorkRepositoryDbInfo wRepDbInfo= new WorkRepositoryDbInfo(workRepositoryName, new PoolingAttributes());

// ODI Instance
OdiInstance odiInstance = OdiInstance.createInstance(new OdiInstanceConfig(mRepDbInfo, wRepDbInfo));

// Authentication
odiUser = "SUPERVISOR";
odiPassword = "*****";
Authentication auth = odiInstance.getSecurityManager().createAuthentication(odiSupervisorUser, odiSupervisorPassword.toCharArray());
odiInstance.getSecurityManager().setCurrentThreadAuthentication(auth);

// Transaction
ITransactionDefinition txnDef = new DefaultTransactionDefinition();
ITransactionManager tm = odiInstance.getTransactionManager();
tme = odiInstance.getTransactionalEntityManager();
ITransactionStatus txnStatus = tm.getTransaction(txnDef);

//
// ... place your SDK code here ...
//

tm.commit(txnStatus); // or tm.rollback(txnStatus);
auth.close();
odiInstance.close();

ab ODI 12.2:

OdiInstance odiInstance = odiRef.getOdiInstance();

// Transaction
ITransactionDefinition txnDef = new DefaultTransactionDefinition();
ITransactionManager tm = odiInstance.getTransactionManager();
tme = odiInstance.getTransactionalEntityManager();
ITransactionStatus txnStatus = tm.getTransaction(txnDef);

//
// ... place your SDK code here ...
//

tm.commit(txnStatus); // or tm.rollback(txnStatus);
odiInstance.close();

 

Audit-Spalten mit dem Oracle SQL Developer Data Modeler automatisieren

In vielen Projekten erlebe ich es, dass Entwickler Audit-Spalten verwenden um entsprechende Informationen leicht zugänglich zu haben: Wer hat eine Datenzeile wann angelegt – und wann wurde dieselbe Zeile zuletzt von wem wieder bearbeitet? Wurden Tabellen früher noch direkt per DDL-Script erzeugt (CREATE TABLE my_test (id NUMBER NOT NULL, etc.), kommen hier heutzutage eher Modellierungstools wie der Oracle SQL Developer Data Modeler zum Einsatz:

 

Über das Icon “Spalte am Ende der Liste hinzufügen“ plus wurden hier nacheinander die vier Audit-Spalten (CREATED_BY, CREATED_ON, UPDATED_BY, UPDATED_ON) hinzugefügt und die Spalteneigenschaften definiert.

Die wenigsten Projekte bestehen aber aus nur einer einzigen Tabelle – bei drei oder sieben Tabellen mag das manuelle Hinzufügen der einzelnen Spalten und die ebenfalls manuelle Konfiguration derselben ja noch funktionieren. Aber was, wenn der Entwickler zwei oder drei Dutzend Tabellen modellieren will?

Q: Geht das nicht einfacher?

A: Natürlich geht das – über die Icons „Ausgewählte Spalten kopieren“  und „Kopierte Spalten am Ende einfügen“. Hiermit werden die ganzen Spalten samt ihrer vollständigen Definition kopiert und in der Ziel-Tabelle eingefügt.

Allerdings stößt auch diese Variante ab einer gewissen Tabellenanzahl an ihre Grenzen. Bei mehreren Hundert Tabellen werden wahrscheinlich die wenigsten Entwickler die Geduld aufbringen, bei jeder Tabelle die Icons drücken zu wollen. Hoffnungslos aufwändig wird es, wenn dann nicht neue Spalten hinzugefügt werden sollen – sondern beispielsweise der Datentyp bereits definierter Spalten geändert werden soll, etwa weil [CREATED|MODIFIED]_BY künftig bis zu 100 Zeichen speichern können soll.

Q: Geht das nicht einfacher?

A: Natürlich geht das – viel einfacher sogar. 😉

Hier kommen im Data Modeler die sogenannten Transformationen ins Spiel – im Menü unter Extras > Entwurfsregeln und Transformationen > Transformationen zu finden:

Speziell die Transformation mit dem Namen „Table template – uses column name“ ist hier sehr nützlich:

Um die Wartung von Audit-Spalten zu automatisieren, muss nun eine Tabelle angelegt werden, die nur die gewünschten Audit-Spalten enthält und deren Name in der Transformation referenziert wird, im Beispiel also MY_JOURNAL_TEMPLATE:

Beim Anwenden der Transformation werden dann alle im Model vorhandenen Tabellen durchgelooped und bezüglich der Spalten an die Definition in der Template-Tabelle angepasst. Gibt es die Audit-Spalten noch nicht, werden sie neu hinzugefügt. Gibt es sie bereits, werden ihre Attribute bei Bedarf auf die Werte der Template-Tabelle aktualisiert. So ist es dann beispielsweise auch möglich mit wenigen Klicks eine Datentyp-Änderung für ein Projekt mit mehreren Hundert Tabellen auszuführen. Um zu überprüfen ob eine Spalte aus einem Template stammt, kann man sich in den „Spalteneigenschaften“ die „Dynamischen Eigenschaften“ anschauen. Wenn die Spalte aus einem Template heraus erstellt oder angepasst wurde, wird im Rahmen der Transformation der unter p_name angegebene Wert (in diesem Beispiel also ctemplateID) nämlich als Schlüssel in die Eigenschaften eingetragen.