In den ersten Teilen der Blogreihe haben wir beschrieben, wie du DataStage Jobs von (beispielweise) 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. 

Achtung

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. 

  
          CREATE TABLE 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()) 
  
  
Job Manipulator

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. 

  
          INSERT INTO TEST (COL1, COL2) VALUES ( 'TEXT', 'TEXT'); 

INSERT INTO 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.  

Achtung

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. 

Achtung

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: 

Link 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: 

  
          INSERT INTO TEST (COL1, COL2) VALUES ( T1, 'T2'); 

INSERT INTO TEST (COL1, COL2) VALUES ( NULL, 'T2');

INSERT INTO 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
Job Manipulator

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 2 at 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. 

Neuen Kommentar schreiben

Ihre E-Mail Adresse wird nicht veröffentlicht.

NEUESTE BEITRÄGE

ITGAIN UX UI
08.09.2022

Wie wir deinen Benutzer glücklich machen können

Lea Brauner

„UI is the sadle, the stirrups and the reins. UX is the feeling you get being able to ride the horse“, hat Dain Miller, ein Web Developer, einmal gesagt. In anderen Worten heißt das, dass man zwar ein User Interface braucht, um ein Programm oder eine Software zu bedienen, das aber noch nicht heißt, dass es mit diesem User Interface auch Spaß macht, die Software zu bedienen. Denn beim User Interface, der Benutzeroberfläche, handelt es sich lediglich um die Schnittstelle zwischen dem Benutzer und der Maschine, die ihn zum Ziel navigiert. Über die Optik oder Benutzbarkeit sagt das jedoch noch nichts aus.

Applications
Lasttests mit Shift-Left-Verschiebung
31.08.2022

Lasttests mit Shift-Left-Verschiebung

Sören Schmock

Lasttests gehören gemäß ISTQB® zu den nicht funktionalen Tests, mit denen Zeitverhalten, Ressourcenverbrauch, Stabilität und Skalierbarkeit einer Software oder Webseite (im folgenden „Anwendung“ genannt) überprüft werden. Der Lasttest wird durch Testspezialisten durchgeführt, um unter anderem zu bestimmen, ob die Anwendung einer erwarteten oder für die Zukunft geplanten Anzahl von Benutzern in der Performance (z. B. Anzeigegeschwindigkeit) noch Rechnung trägt. Schließlich möchte niemand auf die lange Rückmeldung einer Anwendung warten müssen.

Expertise
Wie erfolgt die Migration von DataStage ETL-Jobs?
30.08.2022

Wie erfolgt die Migration von DataStage ETL-Jobs? | Umzug eines DWH auf eine Snowflake - Teil 4

Christian Hagen

In Teil 3 haben wir uns angesehen, wie DataStage an eine Snowflake angebunden werden kann. In diesem Teil wollen wir uns ansehen, wie ein Job umgestellt wird und wie der ITGAIN JobManipulator das vereinfacht.