Lasst uns in die Snowflake abtauchen | Umzug eines DWH auf eine Snowflake - Teil 5

In den ersten Teilen der Blogreihe haben wir beschrieben, wie du DataStage Jobs von (beispielsweise) Oracle auf Snowflake umstellen kannst. In diesem Teil wollen wir uns auf Besonderheiten und Probleme stürzen, die bei einem Schwenk von Oracle zu Snowflake auftreten können und die sich als „Schätzchen“ in den Jobs und manuellem SQL verstecken können.

„Standards, wo wir herkommen, hält sich keiner an Standards“

Es gibt zwar SQL-Standards und die Hersteller rühmen sich auch, den einen oder anderen Standard einzuhalten. Beispielsweise ein Auszug aus der Snowflake Dokumentation:

Snowflake supports standard SQL, including a subset of ANSI SQL:1999 and the SQL:2003 analytic extensions. Snowflake also supports common variations for a number of commands where those variations do not conflict with each other.

https://docs.snowflake.com/en/user-guide/querying.html

Aber: Hand aufs Herz, welcher Entwickler hat sich in seinen Statements wirklich nur auf Standard-SQL beschränkt?

Für einige Beispiele im weiteren Verlauf arbeiten wir mit einer Testtabelle, um das funktionale Verhalte der Snowflake einfach zu illustrieren und nachvollziehen zu können. Du kannst dieses Beispiel sowohl unter Oracle als auch unter Snowflake mal ausprobieren.

CREATETABLE TEST

( COL1 CHAR(20)

, COL2 VARCHAR(20)

);

SYSDATE, TIMESTAMP und Zeitzonen

SYSDATE ist eine Variable bei Oracle, die den aktuellen Tag und die aktuelle Uhrzeit ausgibt. Bei Snowflake gibt es eine vergleichbare Funktion SYSDATE(), aber die liefert immer Tag und die aktuelle Uhrzeit in der UTC-Zeitzone aus!

CURRENT_TIMESTAMP bei Snowflake wäre eine mögliche Ersetzung, aber das Ergebnis ist ein TIMESTAMP_LTZ, also auch mit Zeitzonen-Information. Wenn ein TIMESTAMP_LTZ gelesen wird (insbesondere aus Java heraus) haben wir schwer nachzuvollziehende Zeitzonen Transformation gesehen, wenn das Ziel keine Zeitzone enthält.

Eine mögliche Umsetzung, um die aktuelle Uhrzeit ohne Zeitzone zu bekommen, wäre:


to_timestamp_ntz(current_timestamp())

Im JobManipulator haben wir eine Funktion eingebaut, um in SQL Statements Text oder den Aufruf von SQL-Funktionen zu ersetzen.

CHAR() und VARCHAR()

Bei einer Snowflake ist der Datentyp CHAR() nur ein Synonym des Datentyps VARCHAR().

Egal, ob du eine Spalte als CHAR() oder VARCHAR() anlegst, das Verhalten entspricht immer einer als VARCHAR() deklarierten Spalte. Damit werden in dieser Spalte immer Zeichenketten variabler Länge gespeichert!

Insofern findet bei Snowflake beim Schreiben in eine als CHAR() angelegte Spalte nie ein Padding mit Leerzeichen statt. Gleiches gilt für das Lesen der Spalte und die Verwendung in einem Vergleich.

Beispiel

Diese beiden INSERTs schreiben je eine Zeile in die Tabelle.

INSERTINTO TEST (COL1, COL2) VALUES ( 'TEXT', 'TEXT');

INSERTINTO TEST (COL1, COL2) VALUES ( 'TEXT2 ', 'TEXT2');

Wenn du dir die Daten per SQL anzeigst, kannst du sehen, dass sie genauso eingefügt wurden, wie du es beim INSERT angegeben hast.

select length(col1), col1, length(col2), col2

from test

;

LENGTH(COL1) COL1 LENGTH(COL2) COL2

4 TEXT 4 TEXT

10 TEXT2 5 TEXT2

Bei TEXT2 gab es am Ende einige Leerzeichen, die auch genauso übernommen werden. Es findet aber im Gegensatz zu Oracle kein Padding mit Leerzeichen statt!

Auch beim Auswerten z. B. in einer WHERE-Bedingung gibt es kein automatisches Auffüllen mit Leerzeichen. Das folgende SQL liefert kein Ergebnis!

select*from test

where col1 ='TEXT2'

Darstellung in einem DataStage Job

DataStage macht (wie Oracle oder Db2) eine Unterscheidung zwischen CHAR() und VARCHAR() Spalten.

Wird ein Job migriert, in dem die ConnectorStages ausgetauscht werden, bleiben die Datentypen auf den Links so, wie sie bei Nutzung der Oracle waren. DataStage kann automatisch ein Padding durchführen, wenn beim Lesen eine VARCHAR()-Datenbankspalte auf eine CHAR()-Linkspalte gemappt wird. Die Umgebungsvariable APT_STRING_PADCHAR sollte auf ein Leerzeichen gesetzt sein; der Default ist leider Hex-Null.

Folgerung

Die Migration der Daten und die Migration des ETL müssen hier zueinander passen. Die Lage kann sehr unübersichtlich werden, wenn das Projektteam die Textspalten in der Daten-Migration und der ETL-Migration unterschiedlich handhaben, beispielsweise:

NULL-Handling
Datentyp inkl. Längen
Padding

Auch manuelle SQL-Befehle, um Tabellen zu bearbeiten, müssen daraufhin überprüft werden, inwieweit sie darauf vertrauen, dass durch die Datenbank ein Padding auf die volle Länge erfolgt.

Im JobManipulator führen wir keine Veränderung der Datentypen durch. Unsere Empfehlung ist, bei einem Job nur die Stages zu konvertieren und die Daten ohne TRIM() zu migrieren. In einem späteren Refactoring Projekt können die Daten und die Jobs umgearbeitet werden.

Ein Leerstring und NULL sind unterschiedlich!

In Oracle wird ein Leerstring (also ein String der Länge Null) wie NULL behandelt und andersherum. Eine Snowflake macht das nicht. Das führt zu einem besonderen Problem, wenn mit den Daten gearbeitet wird.

Unabhängig vom verwendeten Datenbanksystem sorgt NULL immer wieder für Überraschungen. Wer da tiefer einsteigen möchte, findet hier eine gute Beschreibung, wie NULL im relationalen Modell (eigentlich) behandelt wird:

https://en.wikipedia.org/wiki/Null_(SQL)

Warum die Gleichbehandlung von NULL und Leerstring eine Herausforderung für die Migration von Oracle nach Snowflake ist, wollen wir hier an einem Beispiel zeigen.

Beispiel

Nehmen wir wieder die Tabelle TEST, fügen diesmal die folgenden Zeilen ein:

INSERTINTO TEST (COL1, COL2) VALUES ( T1, 'T2');

INSERTINTO TEST (COL1, COL2) VALUES ( NULL, 'T2');

INSERTINTO TEST (COL1, COL2) VALUES ( 'T1', NULL);

Ergebnis

COL1 | COL2

-----+-----

T1 | T2

NULL| T2

T1 |NULL

Jetzt lesen wir die Tabelle, verketten aber beide Spalten:

SELECT COL1, COL2, COL1 ||'_'|| COL2 AS RES
FROM TAB1

Das Ergebnis bei Oracle:

COL1 | COL2 | RES

-----+------+------

T1 | T2 | T2_T2

NULL| T2 | _T2

T1 |NULL| T1_

In einer Snowflake sieht das Ergebnis aber so aus.

COL1 | COL2 | RES

-----+------+------

T1 | T2 | T2_T2

NULL| T2 |NULL

T1 |NULL|NULL

Da Oracle zwischen einem Leerstring und NULL nicht unterscheidet, funktioniert der ||-Operator in Oracle so, als ob NULL-Werte ein Leerstring sind. Deswegen hat der ||-Operator in diesem Fall ein nicht-NULL-Ergebnis.

Snowflake behandelt den ||-Operator wie jeden anderen binären Operator.

Um das gleiche Ergebnis in einer Snowflake zu bekommen, muss das SQL lauten:

SELECT COL1, COL2, NVL(COL1, '') ||'_'|| NVL(COL2, ' ') AS RES
FROM TAB1

Im JobManipulator haben wir Funktionen eingebaut, um das SQL zu interpretieren und dieses korrekt zu migrieren. Das hat uns im realen Projekteinsatz sehr geholfen!

Tücken des TO_DATE()

Und dann gibt es noch ein besonderes Schätzchen. TO_DATE() ist in Oracle sehr freizügig bei der Formatangabe. Sie ist optional (nicht verwerflich), als Default wird dann eine Einstellung gezogen, die per SESSION-Parameter vorgegeben ist.

Aber die Interpretation der Formatangabe ist auch dann noch sehr freizügig und kann so definitiv nicht auf die Snowflake übernommen werden.

Als Übung versuche mal das folgende SQL in Oracle und in der Snowflake auszuführen:

select'STR long'

, to_date('2009-10-11')

, to_date('2009/10/11')

, to_date('2009.10.11')

, to_date('2009_10_11')

, to_date('2009_10_11', 'YYYY-MM-DD')

, 'INT'

, to_date(20091011)

, to_date('20091011')

, 'STR SHORT'

, to_date('09-10-11')

, to_date('09/10/11')

, to_date('09-10.11')

, to_date('09_10_11')

, to_date('09_10_11', 'YY-MM-DD')

from dual;

Wir fanden das Ergebnis in Oracle sehr überraschend. Auch wenn bei einem Formatstring ein Trennzeichen angegeben wird, erlaubt Oracle trotzdem andere Trennzeichen in den Daten. Die Snowflake ist da restriktiv und das SQL ist so nicht ausführbar (und das ist auch ganz gut und richtig so). Das kannst du nur beim Testen herausfinden, wenn echte Daten mit dem SQL verarbeitet werden.

Verrückte Fehlermeldungen

Die Snowflake führt eine Historie über ausgeführte SQL-Befehle. Da in diesen SQL-Befehlen potenziell Passwörter oder Secrets enthalten sein können, gibt es eine automatische Maskierung von potenziellen Geheimnissen. Diese Maskierung kann aber manchmal zu aggressiv sein. Und dann wird der eigentliche Fehler überschattet.

Beispiel

SELECT $1 colkey, $2 col1

FROM @ CHAGEN_TEST.DSTAGE_☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺_ORA_UT_UpdateORA_Update (file_format => DSTAGE_MERGEJB_ORA_UT_UpdateORA_Update_ff )

;

SQL compilation error: syntax error line 2at position 20 unexpected '.'.

Solche Befehle und ähnliche Befehle werden auch durch DataStage erzeugt, wenn Daten geladen werden. In Snowflake werden INTERNAL STAGES über @ und den Stage-Namen adressiert.

Findest du den Fehler? Irritiert dich auch dieser Abschnitt im Text?

☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺☺

OK, der Fehler ist, dass zwischen dem @ und dem Stage-Namen ein Leerzeichen steht. Eine INTERNAL STAGE muss mit @NAME (ohne Leerzeichen!) adressiert werden. Das führt dazu, dass der SQL-Parser durcheinanderkommt, den Fehler aber erst beim Punkt zwischen Schema-Namen und Stage-Namen erkennt. Dann gibt es einen langen Identifier, der durch die Maskierungsregeln mit Smileys maskiert wird.

Das Leerzeichen kommt aus den Einstellungen der ConnectorStage, wenn vor dem Stage-Namen eventuell ein Leerzeichen eingefügt wurde. DataStage übernimmt diese Angaben direkt, ohne Leerzeichen zu entfernen. Da im Designer eine Proportionalschrift verwendet wird, ist das Leerzeichen nicht sofort ersichtlich. Das erste Mal, als wir diesen Fehler hatten, haben wir schon ein paar Stunden gesucht, um ihn zu finden.

Hier findest du die anderen Teile dieser Blogserie

1 - Umzug eines DWH auf eine Snowflake

2 - Überblick und Grundlagen

3 - Wie bindet man DataStage an?

4 - Wie erfolgt die Migration von DataStage ETL-Jobs

5 - Lasst uns in die Snowflake abtauchen

6 - Wichtige Details bei der Anbindung von DataStage an Snowflake