Hier sind 3 Lek­tio­nen aus der Pra­xis und 3 Honig­töpfe, in die man bei Unacht­sam­keit leicht hin­ein stap­fen kann.

Lek­tion 1

Bei Daten­ban­ken den­ken wir meist an Tabel­len mit Spal­ten und Zei­len, ein biss­chen wie bei Excel. Dies ent­spricht häu­fig aber nur der logi­schen Sicht. Snow­flake ord­net Zei­len einer Par­ti­tion zu, aber intern spei­chert es die Daten nicht Zeile für Zeile, son­dern ver­wal­tet jede Spalte sepa­rat. Snow­flake ist colum­nar.

Für jede Tabelle und zu jeder Mikro­par­ti­tion wer­den Meta­da­ten gespei­chert, die es Snow­flake ermög­li­chen Daten zum einen bes­ser zu kom­pri­mie­ren und zum ande­ren Abfra­gen zu opti­mie­ren. Query-Per­for­mance durch Pru­nen: Snow­flake ana­ly­siert vor der Aus­füh­rung einer Query, wel­che Par­ti­tio­nen für das Ergeb­nis gar nicht erst ange­schaut wer­den müssen.

Zu die­sen Sta­tis­ti­ken in den Meta­da­ten gehö­ren auch row-counts. Das heisst, wenn ein sel­ect count(*) abge­fragt wird, dann wird nicht die gesamte Tabelle gescannt, wie in vie­len ande­ren Daten­ban­ken, son­dern das Ergeb­nis ist sofort ver­füg­bar, ohne dass irgend eine Berech­nung erfol­gen muss. Es ist dafür kein Ware­house von Nöten. Es ist ein Meta­data-based Result. Nice Feature.

Neh­men wir an myTable1 hat vier Spal­ten. In col2 und col3 gibt es NULL-Werte, z.B wie folgt:

Dann wer­den fol­gende Abfra­gen u.U. überraschen. 

(1) select count(*) from myTable1;
>> 3

(2) select count(col3) from myTable1;
>> 2

(3) select count(t.*) from myTable1 as t;
>> 1

Die Query (3) gibt die Anzahl aller Zei­len zurück, die gar keine NULLs ent­hal­ten. Die­ser Count muss berech­net wer­den, ist also nicht zu ver­wech­seln mit count(*). Die Abfrage (3) kann aller­dings gut ver­wen­det wer­den um fest­zu­stel­len, ob über­haupt NULLs in den Daten vor­han­den sind.

(2) ist auch ein Meta­data-based Result. Das bedeu­tet, es wer­den zu jeder Spalte Meta­da­ten gespei­chert. Hier zäh­len hin­ge­gen bei Snow­flake die NULL-Werte nicht mit.

Lek­tion 2

Snow­flake ver­fügt, wie jede andere Daten­bank auch, über Ver­gleichs-Ope­ra­to­ren, mit denen zwei Werte mit­ein­an­der ver­gli­chen wer­den können: 

• Gleich­heit: =, != oder <>
• Rang: <, >
• Kom­bi­niert: <=, >=

Wann immer einer die­ser Ope­ra­to­ren auf NULL trifft, gibt er NULL zurück, und nicht TRUE oder FALSE. Snow­flake ver­ar­bei­tet hier also nicht binär, son­dern in {TRUE, FALSE, NULL}.

Diese Lek­tion klingt sim­pel und nach­voll­zieh­bar, hat aber Kon­se­quen­zen über die man sich nicht gleich im Kla­ren ist. Im Fol­gen­den werde ich in mei­nen Honig­töp­fen dar­auf noch näher ein­ge­hen. Zum Ein­stim­men bereits einige Beispiele:

Select 1 = 1;         --> TRUE
Select 1 = 0;         --> FALSE
Select 0 < 1;         --> TRUE
Select '' = NULL;     --> NULL
Select 1 > NULL;      --> NULL
Select NULL = NULL    --> NULL

Ins­be­son­dere wird NULL = NULL nicht von allen Daten­ban­ken so gehandhabt. 

Zudem gibt es noch einen wei­te­ren Ope­ra­tor der expli­zit auf NULL tes­tet: is.

Select NULL is NULL        --> TRUE
Select '' is not NULL      --> TRUE
Select NULL is not NULL    --> FALSE

Lek­tion 3

Wenn man weiß, wie es geht, ist vie­les ein­fach. Diese Lek­tion gehört dazu. Snow­flake ver­fügt über eine Snow­flake-spe­zi­fi­sche NULL-safe-Funk­tion: EQUAL_NULL(). Sie ver­bin­det die bei­den Ope­ra­to­ren = und is mit­ein­an­der. NULL wird hier wie etwas Bekann­tes, wie ein Wert wie alle ande­ren, behan­delt. EQUAL_NULL() ist also binär und kann bei­des, auf NULL und auf Gleich­heit testen:

Select equal_null(1,1);        --> TRUE
Select equal_null(NULL, NULL)  --> TRUE
Select equal_null(1, NULL)     --> FALSE 

Soweit so gut. Kom­men wir zu unse­ren Honigtöpfen.

Honig­topf 1

Die­ser Honig­topf zielt auf die Lek­tion 1. Je nach­dem wie die count()-Funk­tion ver­wen­det wird, erhal­ten wir unter­schied­li­che Ergeb­nisse für gewisse Sta­tis­ti­ken. Sta­tis­ti­ken wie der Median, Mit­tel­wert oder die Stan­dard­ab­wei­chung bezie­hen die Anzahl der Zei­len mit ein. Stan­dard­mä­ßig wer­den bei Snow­flake hier nur Nicht-NULL-Werte betrach­tet. Wenn man hin­ge­gen die reine Defi­ni­tion eines Mit­tel­wer­tes sel­ber umge­setzt hat (2), dann kann es zu Irri­ta­tio­nen kommen.

(1) Select avg(col3) from myTable1;
>> 2.5

(2) Select sum(col3)/count(*) from myTable1;
>> 1.666667

(3) Select sum(col3)/count(t.*) from myTable1 as t;
>> 5

(4) Select col2, count(col2) as "#" from myTable1 group by col2;
>> COL2   #
   col2   2
   null   0

Die Query (2) behan­delt NULL als wäre es 0, was viele Daten­ban­ken so hand­ha­ben. Snow­flake dage­gen sieht NULL erst­mal nicht als Wert an. NULL zählt nicht mit. Man kann nun selbst ent­schei­den, was der wahr­haf­ti­gere Mit­tel­wert in sei­nem Fall ist bzw. als was man NULLs bei Nume­ri­schen Daten­ty­pen anse­hen möchte.

Ein wirk­li­cher Honig­topf kann hier das Gleich­set­zen von count(*) und count(alias.*) oder count(column) sein. Die Query (3) hat für count(t.*) genau eine Zeile in der es gar keine NULLs gibt. Daher kommt hier ein­fach die Summe zurück. Die Abfrage (4) gibt für die Anzahl NULLs 0 als Ergeb­nis zurück, weil NULLs wie gesagt nicht zählen.

Honig­topf 2

Manch­mal möchte man in einer Bedin­gung gerade sagen, dass etwas nicht einem Wert oder einer Menge ent­spricht. In SQL schreibt man ent­we­der ein­fach ein NOT vor eine Bedin­gung oder gebraucht den all­täg­li­chen Ungleich-Ope­ra­tor !=. Dies ver­lei­tet aller­dings etwas dazu, in Schwarz-Weiß zu den­ken. Des Wei­te­ren kön­nen Bedin­gun­gen ver­schach­telt und mit­tels AND oder OR mit­ein­an­der ver­knüpft wer­den. Dabei kön­nen ohne­hin bereits ver­wir­rende Kon­strukte ent­ste­hen. Dies trifft erst Recht zu an den Stel­len, wo Snow­flake sich nicht binär ver­hält, NULL eine eigene Kate­go­rie dar­stellt und eine dritte Ergeb­nis-Menge bildet.

Zum Ver­deut­li­chen eine zweite Tabelle: myTable2

(1) Select t1.col3 as t1, t2.col3 as t2  from myTable1 as t1, myTable2 as t2 
where t1.col3 = t2.col3;
>> T1   T2
   3    3

(2) Select t1.col3 as t1, t2.col3 as t2  from myTable1 as t1, myTable2 as t2 
where NOT t1.col3 = t2.col3;
>> T1   T2
   2    3

(3) Select t1.col3 as t1, t2.col3 as t2  from myTable1 as t1, myTable2 as t2 
where (t1.col3 = t2.col3) is NULL
>> T1   T2
   null 3
   null null
   null null
   3    null
   3    null
   2    null
   2    null

(4) Select t1.col3 as t1, t2.col3 as t2, t1=t2 from myTable1 as t1, myTable2 as t2 where (t1.col3 = t2.col3) is NOT NULL;
>> T1   T2   T1=T2
   3    3    TRUE
   2    3    FALSE

Honig­topf 3

Ich bin über einen Com­mu­nity-Bei­trag gestol­pert, bei dem ich mit der „Sel­ec­ted as Best“-Antwort nicht über­ein­stimme: „merge-state­ment-join-expres­sion-con­ta­ins-null-value“ [Link].
Zum einem stimmt darin die Annahme „NULL = NULL always return false“ nicht. Zum ande­ren ist es eben nicht die ein­zige Option, NULLs mit Hilfe von nvl oder coale­sce mit Default-Wer­ten zu erset­zen, damit eine Bedin­gung zutrifft. Das kann u.U. sogar ris­kant sein. Wenn NULL zum Bei­spiel mit 0 ersetzt wird, was mache ich dann, wenn es tat­säch­lich einen Wert 0 gibt?

Das Bei­spiel aus dem Bei­trag eig­net sich aber gut. Es geht dabei um ein Merge State­ment das Dupli­kate pro­du­ziert, weil es Inserts statt Updates macht. Unsere Tabel­len myTable1 und myTable2 sind bereits an das Bei­spiel ange­lehnt. Unter dem Link sind auch create table state­ments zu fin­den, um unser Bei­spiel ein­fach nachzuspielen.

Merge

Merge state­ments sind wie folgt aufgebaut:

MERGE INTO myTable1 t1
USING myTable2 t2   
on t1.col1 = t2.col1   // Join-Bedingung
and t1.col2 = t2.col2 
and t1.col3 = t2.col3
WHEN MATCHED
THEN UPDATE
SET t1.col4 = t2.col4
WHEN NOT MATCHED
THEN INSERT
VALUES (t2.col1, t2.col2, t2.col3, t2.col4);

Der Knack­punkt ist vor allem die Join-Bedin­gung. Wenn die Bedin­gung zutrifft, dann UPDATE, wenn nicht dann INSERT. Hier müss­ten wir bereits hell­hö­rig wer­den. Das klingt nach Schwarz-Weiß-Den­ken. Sobald eine Spalte einen NULL-Wert beinhal­tet, wir mit = ver­glei­chen und mit AND ver­knüp­fen, bekom­men wir NULL für die gesamte Join-Bedin­gung. NOT MATCHED. Folg­lich wird ein INSERT aus­ge­führt. Das ist der Grund, warum Dupli­kate statt Updates pro­du­ziert werden:

Joins

Bei Joins gibt es noch eine Kurz­schreib­weise für Fälle in dei­nen t1 und t2 die glei­chen Spal­ten­na­men und Defi­ni­tio­nen haben: t1 join t2 using(<column_list>). Hier ist aller­dings nicht gleich ersicht­li­che wel­che Ver­gleichs-Ope­ra­to­ren Snow­flake dabei intern anwen­det. Die Doku­men­ta­tion sagt es uns:

Wenn hier eine Liste von Spal­ten über­ge­ben wird, dann wer­den diese mit AND ver­knüpft und wir kön­nen einen ähn­li­chen Fall wie im obi­gen Merge-State­ment haben: NOT MATCHED, weil eine Bedin­gung NULL ergab. In dem Fall kön­nen uns Daten ver­lo­ren gehen.

Wo NULL-Werte in Bedin­gun­gen vor­kom­men kön­nen bedeu­tet das, dass wir bei Joins, und ins­be­son­dere bei Merge State­ments, bei der eine binäre Bedin­gung gefor­dert ist, die binäre EQUAL_NULL()-Funk­tion ver­wen­den sollten. 

MERGE INTO myTable1 t1
USING myTable2 t2   
ON equal_null(t1.col1, t2.col1)
AND equal_null(t1.col2, t2.col2)
AND equal_null(t1.col3, t2.col3)
WHEN MATCHED
THEN UPDATE
SET t1.col4 = t2.col4
WHEN NOT MATCHED
THEN INSERT
VALUES (t2.col1, t2.col2, t2.col3, t2.col4);

Fazit

Zu jeder Daten­ver­ar­bei­tung gehört eine Daten-Vor­ver­ar­bei­tung. Die Ana­lyse, ob und an wel­chen Stel­len NULL-Werte exis­tie­ren und wie ich damit umge­hen möchte, ist davon ein Teil. Snow­flake tut gewisse Dinge anders. So hat es ein eige­nes NULL-Ver­ständ­nis und auch eigene NULL-Hand­ling-Funk­tio­nen. Manch­mal ist zudem nicht gleich ersicht­lich, was Snow­flake intern bereits für eine Logik anwen­det. In den meis­ten Fäl­len aller­dings schreibt der Ent­wick­ler oder Ana­lyst die Abfra­gen. Die­sen sollte daher fol­gen­des bewusst sein. Bei Snowflake

• zäh­len NULLs nicht mit
• sind Ver­glei­che gegen NULL immer NULL
• gibt es eine EQUAL_NULL() Funk­tion, die aus 3 wie­der 2 macht

Erfah­ren Sie hier mehr über Lösun­gen im Bereich Snow­flake oder besu­chen Sie eines unse­rer kos­ten­lo­sen Web­i­nare.