Kapitel 4. Snowflake SQL-Befehle, Datentypen und Funktionen kennenlernen

Diese Arbeit wurde mithilfe von KI übersetzt. Wir freuen uns über dein Feedback und deine Kommentare: translation-feedback@oreilly.com

Wie wir in den vorangegangenen Kapiteln gelernt haben, wurde Snowflake entwickelt, um Daten in einem optimierten, komprimierten, spaltenförmigen Format in einer relationalen Datenbank zu speichern. Die Endnutzer von Snowflake müssen auf die gespeicherten Daten zugreifen können und in der Lage sein, Anweisungen zu geben, um Aufgaben auszuführen, Funktionen aufzurufen und Abfragen an den Daten durchzuführen. Die Art und Weise, wie das erreicht werden kann, ist mit der Standardprogrammiersprache für relationale Datenbanken, Structured Query Language (SQL). Snowflake unterstützt SQL:ANSI, die am weitesten verbreitete standardisierte Version von SQL. Neben der SQL-Unterstützung für strukturierte Daten bietet Snowflake auch native Unterstützung für semi-strukturierte Datenformate wie JSON und XML. Snowflake unterstützt auch unstrukturierte Daten.

Der Schwerpunkt dieses Kapitels liegt auf dem Erlernen der Grundlagen der Verwendung von Snowflake-Arbeitsblättern zur Ausführung verschiedener SQL-Befehle mit unterschiedlichen Datentypen und Funktionen. Neben der Verwendung von Arbeitsblättern in der Snowflake-Web-UI ist es auch möglich, einen Snowflake-eigenen Befehlszeilen-Client, SnowSQL, zu verwenden, um SQL-Befehle zu erstellen und auszuführen. Weitere Einzelheiten zu SnowSQL findest du in Kapitel 6.

Neben der Verbindung zu Snowflake über die Web-UI oder SnowSQL kannst du auch ODBC- und JDBC-Treiber verwenden, um über externe Anwendungen wie Tableau und Looker auf Snowflake-Daten zuzugreifen. Wir werden uns in Kapitel 12 mit Verbindungen zu Tableau und Looker beschäftigen. Native Konnektoren wie Python und Spark können ebenfalls verwendet werden, um Anwendungen für die Verbindung mit Snowflake zu entwickeln.

Damit du dich auf die fortgeschrittenen Themen in den folgenden Kapiteln vorbereiten kannst, sollten wir uns zunächst auf die Grundlagen der Snowflake SQL-Befehle, Datentypen und Snowflake-Funktionen konzentrieren.

Vorbereitungsarbeiten

Erstelle ein neues Arbeitsblatt mit dem Titel Chapter4 Syntax Examples, Data Types, and Functions. Siehe "Navigieren in Snowsight-Arbeitsblättern", wenn du Hilfe beim Erstellen eines neuen Arbeitsblatts brauchst. Um den Kontext des Arbeitsblatts festzulegen, stelle sicher, dass du dich im Arbeitsblatt Syntaxbeispiele befindest und die Rolle SYSADMIN sowie das virtuelle Lager COMPUTE_WH verwendest .

Arbeiten mit SQL-Befehlen in Snowflake

SQL können in fünf verschiedene Sprachbefehlstypen unterteilt werden. Um Snowflake-Objekte zu erstellen, musst du Data Definition Language (DDL)-Befehle verwenden. Um den Zugriff auf diese Objekte zu ermöglichen, brauchst du die Data Control Language (DCL). Als Nächstes verwendest du Data Manipulation Language (DML)-Befehle, um die Daten in und aus Snowflake zu manipulieren. Mit den Befehlen der Transaction Control Language (TCL) kannst du Transaktionsblöcke verwalten. Mit den Anweisungen der Data Query Language (DQL) werden dann die Daten abgefragt. Im Folgenden findest du eine Liste der gängigen SQL-Befehle, geordnet nach Typ:

DDL-Befehle:
  • CREATE
  • ALTER
  • TRUNCATE
  • RENAME
  • DROP
  • DESCRIBE
  • SHOW
  • USE
  • SET/UNSET
  • COMMENT
DCL-Befehle:
  • GRANT
  • REVOKE
DML-Befehle:
  • INSERT
  • MERGE
  • UPDATE
  • DELETE
  • COPY INTO
  • PUT
  • GET
  • LIST
  • VALIDATE
  • REMOVE
TCL-Befehle:
  • BEGIN
  • COMMIT
  • ROLLBACK
  • CREATE
DQL-Befehl:
  • SELECT

Jeder dieser fünf verschiedenen Befehlssprachentypen und die dazugehörigen Befehle werden in den folgenden Abschnitten kurz erläutert. Eine umfassende Liste aller Snowflake SQL-Befehle findest du in der Snowflake Online-Dokumentation.

DDL-Befehle

DDL Befehle sind die SQL-Befehle, die verwendet werden, um das Datenbankschema zu definieren. Mit diesen Befehlen werden Datenbankstrukturen erstellt, geändert und gelöscht. Außerdem können DDL-Befehle verwendet werden, um Sitzungsoperationen auf Kontoebene durchzuführen, z. B. das Setzen von Parametern, wie wir später in diesem Kapitel sehen werden, wenn wir die Befehle SET und UNSET besprechen. Zu den DDL-Befehlen gehören CREATE, ALTER, TRUNCATE, RENAME, DROP, DESCRIBE, SHOW, USE und COMMENT. Mit Ausnahme des Befehls COMMENT nimmt jeder DDL-Befehl einen Objekttyp und einen Bezeichner an.

Snowflake DDL-Befehle manipulieren Objekte wie Datenbanken, virtuelle Lagerhäuser, Schemata, Tabellen und Ansichten, aber keine Daten. Ausführliche Erklärungen und viele praktische Beispiele findest du in Kapitel 3, in dem die Snowflake DDL-Befehle vorgestellt werden.

DCL-Befehle

Die DCL-Befehle sind die SQL-Befehle, mit denen die Zugriffskontrolle aktiviert wird. Beispiele für DCL-Befehle sind GRANT und REVOKE. Kapitel 5 führt dich durch eine vollständige und detaillierte Reihe von Beispielen, die DCL-Befehle verwenden, um dir zu zeigen, wie du Snowflake-Objekte sichern kannst.

DML-Befehle

DML-Befehle sind die SQL-Befehle, die zur Datenmanipulation verwendet werden. Die traditionellen DML-Befehle wie INSERT, MERGE, UPDATE und DELETE sind für die allgemeine Datenmanipulation gedacht. Für das Laden und Entladen von Daten bietet Snowflake COPY INTO <table> und COPY INTO <location> Befehle. Darüber hinaus gibt es unter den DML-Befehlen von Snowflake einige Befehle, die keine eigentliche Datenmanipulation durchführen, sondern dazu dienen, die in Snowflake gespeicherten Dateien zu verwalten. Einige Beispiele sind VALIDATE, PUT, GET, LIST und REMOVE. In Kapitel 6 werden viele der DML-Befehle von Snowflake erläutert.

TCL-Befehle

Die TCL-Befehle sind die SQL-Befehle, die zur Verwaltung von Transaktionsblöcken in Snowflake verwendet werden. Befehle wie BEGIN, COMMIT und ROLLBACK können für Multistatement-Transaktionen in einer Sitzung verwendet werden. Eine Snowflake-Transaktion ist eine Reihe von lesenden und schreibenden SQL-Anweisungen, die zusammen als eine Einheit verarbeitet werden. Standardmäßig und bei erfolgreicher Abfrage wird eine separat ausgeführte DML-Anweisung einzeln übertragen oder am Ende der Anweisung zurückgesetzt, wenn die Abfrage fehlschlägt.

DQL-Befehl

Der DQL-Befehl ist der SQL-Befehl, der entweder als Anweisung oder als Klausel verwendet wird, um Daten abzurufen, die die im SELECT Befehl angegebenen Kriterien erfüllen. Beachte, dass der SELECT Befehl der einzige DQL-Befehl ist; er wird verwendet, um Daten abzurufen, indem er den Ort angibt und dann die WHERE Anweisung verwendet, um die für die Datenauswahl erforderlichen Attribute einzuschließen.

Der Snowflake-Befehl SELECT funktioniert auf externen Tabellen und kann zur Abfrage historischer Daten verwendet werden. In bestimmten Situationen ist für die Verwendung der Anweisung SELECT kein laufendes virtuelles Lager erforderlich, um Ergebnisse zurückzuliefern; das liegt am Snowflake-Caching, wie in Kapitel 2 beschrieben. Beispiele für die SELECT Anweisung, die häufigste SQL-Anweisung, finden sich in den meisten Kapiteln dieses Buches. Im folgenden Abschnitt wird beschrieben, wie du den Befehl SELECT optimal nutzen kannst.

SQL-Abfrageentwicklung, Syntax und Operatoren in Snowflake

Innerhalb von Snowflake kann die SQL-Entwicklung sowohl nativ mit Snowflake UI-Arbeitsblättern oder SnowSQL als auch mit den vielen verfügbaren SQL-Tools von Drittanbietern erfolgen.

Abfragesyntax ist die Art und Weise, wie Snowflake SQL-Abfragen strukturiert oder aufgebaut sind. Oft gibt es viele verschiedene Möglichkeiten, eine SQL-Abfrage so zu schreiben, dass sie das gewünschte Ergebnis liefert. Es ist wichtig zu überlegen, wie die Abfrage für die beste Datenbankleistung und die niedrigsten Kosten optimiert werden kann. Kapitel 9 enthält einen Abschnitt, der sich mit der Analyse der Abfrageleistung und mit Optimierungstechniken befasst.

Abfrageoperatoren umfassen Begriffe, die für die Angabe von Bedingungen in einer SQL-Abfrageanweisung reserviert sind, und werden am häufigsten in der WHERE Klausel verwendet. Sie können auch als Konjunktionen für mehrere Bedingungen in einer Anweisung verwendet werden. Wir werden uns später in diesem Abschnitt mit Abfrageoperatoren beschäftigen.

SQL-Entwicklung und -Verwaltung

Es gibt zwei native Snowflake-Optionen für die Entwicklung und Abfrage von Daten. Mit dem browserbasierten SQL-Editor Worksheets innerhalb der Snowflake-Benutzeroberfläche ist der Einstieg in die Snowflake-SQL-Entwicklung ganz einfach. Die Verwendung von Snowflake Worksheets erfordert keine Installation oder Konfiguration. Bis jetzt haben wir nur die Snowflake Worksheets zum Erstellen von Objekten und Abfragen von Daten verwendet.

Eine Alternative zu Worksheets ist SnowSQL, ein Python-basierter Client, der aus dem Snowflake-Client-Repository heruntergeladen und für Snowflake-Aufgaben wie Abfragen oder die Ausführung von DDL- und DML-Befehlen verwendet werden kann. SnowSQL wird häufig zum Laden und Entladen von Daten verwendet. In Kapitel 6 werden wir einige praktische Erfahrungen mit SnowSQL sammeln.

Snowflake bietet SnowSQL-Versionen für Linux, macOS und Microsoft Windows an. Die ausführbare Version von SnowSQL kann als interaktive Shell oder im Batch-Modus ausgeführt werden. Snowflake bietet eine vollständige Anleitung zum Herunterladen und Installieren von SnowSQL für alle unterstützten Plattformen.

Du kannst die zuletzt verwendeten Client-Versionen, einschließlich der SnowSQL-Version, in deinem Snowflake-Konto einsehen, indem du den Abfrageverlauf von Snowflake abfragst. Um diese Informationen anzuzeigen, klicke auf Aktivität → Abfrageverlauf, wenn du die neue Snowsight-Weboberfläche verwendest. Solltest du die Informationen zum Client-Treiber nicht sofort sehen, klicke auf die Schaltfläche Spalten und wähle Client-Treiber (siehe Abbildung 4-1).

Abbildung 4-1. Verfügbare Spalten zur Überprüfung der Abfragehistorie in Snowsight

Alternativ kannst du in der Web-Oberfläche der Classic Console auf die Registerkarte Verlauf klicken. Dort kannst du die Spalte "Client Info" anzeigen, indem du auf die Schaltfläche "Spalte" oben rechts klickst und "Client Driver" auswählst. Interessanterweise enthält die Spalte "Client Info" in der Weboberfläche der Classic Console ein Symbol, das anzeigt, ob die Client-Version unterstützt wird, nicht unterstützt wird oder kurz vor dem Ende der Unterstützung steht. Da wir die Snowsight-Web-Oberfläche verwenden, sehen wir, dass der Go-Client-Treiber verwendet wurde und unterstützt wird, was durch ein Häkchen angezeigt wird (siehe Abbildung 4-2).

Abbildung 4-2. Die Spalte "Client-Info" auf der Registerkarte "Verlauf" (Webschnittstelle der Classic Console)

Zusätzlich zu den nativen Snowflake-Tools gibt es eine Vielzahl von SQL-Tools von Drittanbietern für die Modellierung, Entwicklung und Bereitstellung von SQL-Code in Snowflake-Anwendungen. Einige dieser Tools von Drittanbietern, wie z. B. DataOps.live und SqlDBM, kannst du über Snowflake Partner Connect kostenlos testen. Eine ausführlichere Liste der SQL-Tools von Drittanbietern, die mit Snowflake verwendet werden können, findest du in der Snowflake Online-Dokumentation.

Hinweis

Für Treiber und Konnektoren, die das Senden einer SQL-Anweisung zur Vorbereitung vor der Ausführung unterstützen, bereitet Snowflake DML-Befehle vor und führt SELECT und SHOW <objects> SQL-Anweisungen, die von diesen Treibern und Konnektoren empfangen werden. Andere Arten von SQL-Anweisungen, die von Treibern und Konnektoren empfangen werden, werden von Snowflake ohne Vorbereitung ausgeführt.

Abfrage-Syntax

Snowflake SQL-Abfragen beginnen entweder mit der Klausel WITH oder dem Befehl SELECT. Die WITH Klausel, eine optionale Klausel, die der SELECT Anweisung vorausgeht, wird verwendet, um Common Table Expressions (CTEs) zu definieren, auf die in der FROM Klausel verwiesen wird. Die meisten Abfragen beginnen jedoch mit dem SELECT Befehl und einer anderen Syntax, die danach erscheint. Die andere Syntax, die in Tabelle 4-1 beschrieben ist, wird in der folgenden Reihenfolge ausgewertet:

  • FROM

  • WHERE

  • GROUP BY

  • HAVING

  • WINDOW

  • QUALIFY

  • ORDER BY

  • LIMIT

Tabelle 4-1. Snowflake Abfragesyntax
Abfragesyntax Abfrageklausel Kommentare
WITH Optionale Klausel, die dem Hauptteil der Anweisung SELECT vorausgeht
TOP<n> Enthält die maximale Anzahl von Zeilen, die zurückgegeben werden; empfohlen wird die Angabe ORDER BY
FROM AT | BEFORE, CHANGES, CON⁠NECT BY, JOIN, MATCH_RECOGNIZE, PIVOT oder UNPIVOT, SAMPLE oder TABLESAMPLE_VALUE Gibt die Tabellen, Views oder Tabellenfunktionen an, die in einer SELECT -Anweisung verwendet werden sollen.
WHERE Gibt eine Bedingung an, die auf eine Teilmenge von Zeilen zutrifft; kann das Ergebnis der FROM Klausel filtern; kann angeben, auf welche Zeilen in einer UPDATE, MERGE, oder DELETE
GROUP BY GROUP BY CUBE, GROUP BY GROUPING SETS, GROUP BY ROLLUP, HAVING Gruppiert Zeilen mit denselben Gruppierungsausdrücken und berechnet Aggregatfunktionen für die resultierende Gruppe; kann ein Spaltenname, eine Zahl, die auf eine Position in der Liste SELECT verweist, oder ein allgemeiner Ausdruck sein
QUALIFY Filtert die Ergebnisse von Fensterfunktionen
ORDER BY Gibt eine Reihenfolge der Zeilen der Ergebnistabelle aus einer SELECT Liste an
LIMIT/FETCH Schränkt die maximale Anzahl der zurückgegebenen Zeilen ein; empfohlen wird die Angabe ORDER BY

Beachte dass QUALIFY nach einer Fensterfunktion ausgewertet wird; QUALIFY arbeitet mit Fensterfunktionen genauso wie HAVING mit den Aggregatfunktionen und GROUP BY Klauseln. Weitere Informationen zu Fensterfunktionen findest du weiter unten in diesem Kapitel.

Unterabfragen, abgeleitete Spalten und CTEs

Eine Subquery ist eine Abfrage innerhalb einer anderen Abfrage und kann verwendet werden, um Werte zu berechnen, die in einer SELECT Liste zurückgegeben, in einer GROUP BY Klausel gruppiert oder mit anderen Ausdrücken in der WHERE oder HAVING Klausel verglichen werden.

Eine Snowflake Subquery ist eine verschachtelte SELECT Anweisung, die als Block in einer oder mehreren der folgenden Snowflake SQL-Anweisungen unterstützt wird:

  • CREATE TABLE AS

  • SELECT

  • INSERT

  • INSERT INTO

  • UPDATE

  • DELETE

Um uns auf unsere praktischen Übungen zu Unterabfragen und abgeleiteten Spalten vorzubereiten, müssen wir ein paar einfache Tabellen erstellen und einige Werte in diese Tabellen einfügen. Für dieses Kapitel werden wir eine Datenbank erstellen. Außerdem erstellen wir ein Schema und eine Tabelle für unsere Beispiele mit Unterabfragen und abgeleiteten Spalten. Navigiere zum Arbeitsblatt Chapter4 in Snowsight und führe die folgenden Anweisungen aus:

USE ROLE SYSADMIN;
USE WAREHOUSE COMPUTE_WH;
CREATE OR REPLACE DATABASE DEMO4_DB;
CREATE OR REPLACE SCHEMA SUBQUERIES;
CREATE OR REPLACE TABLE DEMO4_DB.SUBQUERIES.DERIVED
    (ID integer, AMT integer, Total integer);
INSERT INTO DERIVED (ID, AMT, Total)
VALUES (1,1000,4000),(2,2000,3500),(3,3000, 9900),(4,4000,3000),
    (5,5000,3700),(6,6000,2222);
SELECT * FROM DEMO4_DB.SUBQUERIES.DERIVED;

Deine Ergebnisse sollten mit den in Abbildung 4-3 gezeigten übereinstimmen.

Abbildung 4-3. Ergebnisse des Einfügens von Werten in eine neue Snowflake DERIVED-Tabelle

Wir brauchen eine zweite Tabelle im Schema SUBQUERIES. Nachdem wir die Tabelle hinzugefügt haben, sehen wir die in Abbildung 4-4 gezeigten Ergebnisse:

CREATE OR REPLACE TABLE DEMO4_DB.SUBQUERIES.TABLE2
    (ID integer, AMT integer, Total integer);
INSERT INTO TABLE2 (ID, AMT, Total)
VALUES (1,1000,8300),(2,1001,1900),(3,3000,4400),(4,1010,3535),
    (5,1200,3232),(6,1000,2222);
SELECT * FROM DEMO4_DB.SUBQUERIES.TABLE2;
Abbildung 4-4. Ergebnisse des Einfügens von Werten in eine neue Snowflake TABLE2-Tabelle

Nachdem wir nun beide Tabellen erstellt haben, können wir eine unkorrelierte Unterabfrage schreiben:

SELECT ID, AMT
FROM DEMO4_DB.SUBQUERIES.DERIVED
WHERE AMT = (SELECT MAX(AMT)
    FROM DEMO4_DB.SUBQUERIES.TABLE2);

Du wirst feststellen, dass eine unkorrelierte Subquery eine unabhängige Abfrage ist, bei der der zurückgegebene Wert nicht von den Spalten der äußeren Abfrage abhängt. Eine unkorrelierte Subquery liefert ein einziges Ergebnis, das von der äußeren Abfrage nur einmal verwendet wird. Eine korrelierte Subquery hingegen verweist auf eine oder mehrere externe Spalten. Eine korrelierte Subquery wird für jede Zeile der Tabelle der äußeren Abfrage ausgewertet und liefert ein Ergebnis pro ausgewerteter Zeile.

Versuchen wir nun, eine korrelierte Subquery auszuführen:

SELECT ID, AMT
FROM DEMO4_DB.SUBQUERIES.DERIVED
WHERE AMT = (SELECT AMT
    FROM DEMO4_DB.SUBQUERIES.TABLE2
    WHERE ID = ID);

Wir erhalten eine Fehlermeldung, die besagt, dass eine einzeilige Unterabfrage mehr als eine Zeile zurückgibt (wie in Abbildung 4-5 dargestellt). Das ist wahrscheinlich nicht das, was du erwartet hast.

Abbildung 4-5. Die Fehlermeldung bei der Ausführung einer korrelierten Subquery ohne Aggregat

Logischerweise wissen wir, dass es nur eine Zeile pro ID gibt; die Subquery wird also nicht mehr als eine Zeile in der Ergebnismenge zurückgeben. Der Server kann das aber nicht wissen. Wir müssen eine MIN, MAX oder AVG Funktion verwenden, damit der Server sicher sein kann, dass bei jeder Ausführung der Unterabfrage nur eine Zeile zurückgegeben wird.

Fügen wir MAX zu der Anweisung hinzu, um zu sehen, wie das funktioniert:

SELECT ID, AMT
FROM DEMO4_DB.SUBQUERIES.DERIVED
WHERE AMT = (SELECT MAX(AMT)
    FROM DEMO4_DB.SUBQUERIES.TABLE2
    WHERE ID = ID);

Erfolg! Wir erhalten eine Ergebnismenge mit einer Zeile, deren ID gleich dem Wert 3 ist. Mal sehen, was passiert, wenn wir das Gleichheitszeichen in ein Größer-als-Zeichen ändern:

SELECT ID, AMT
FROM DEMO4_DB.SUBQUERIES.DERIVED
WHERE AMT > (SELECT MAX(AMT)
    FROM DEMO4_DB.SUBQUERIES.TABLE2
    WHERE ID = ID);

Jetzt erhalten wir eine Ergebnismenge mit drei Werten (wie in Abbildung 4-6 gezeigt).

Abbildung 4-6. Eine korrelierte Abfrageergebnismenge mit drei Werten

Schauen wir mal, was passiert, wenn wir MAX in AVG ändern:

SELECT ID, AMT
FROM DEMO4_DB.SUBQUERIES.DERIVED
WHERE AMT > (SELECT AVG(AMT)
    FROM DEMO4_DB.SUBQUERIES.TABLE2
    WHERE ID = ID);

Es gibt fünf Datensätze in der Ergebnismenge. Vielleicht möchtest du verschiedene Operatoren in der WHERE Klausel und verschiedene Aggregatoren in der SELECT Klausel ausprobieren, um selbst zu sehen, wie korrelierte Unterabfragen tatsächlich funktionieren.

Korrelierte Unterabfragen werden nur selten verwendet, weil sie zu einer Abfrage pro Zeile führen, was für die meisten Anwendungsfälle wahrscheinlich nicht der beste skalierbare Ansatz ist.

Unterabfragen können für verschiedene Zwecke verwendet werden. Einer davon ist die Berechnung oder Ableitung von Werten, die dann auf unterschiedliche Weise genutzt werden. Abgeleitete Spalten können in Snowflake auch verwendet werden, um eine andere abgeleitete Spalte zu berechnen, sie können von der äußeren SELECT Abfrage konsumiert werden oder sie können als Teil der WITH Klausel verwendet werden. Diese abgeleiteten Spaltenwerte, die manchmal auch als berechnete Spaltenwerte oder virtuelle Spaltenwerte bezeichnet werden, werden nicht physisch in einer Tabelle gespeichert, sondern jedes Mal, wenn sie in einer Abfrage referenziert werden, neu errechnet.

Unser nächstes Beispiel zeigt, wie eine abgeleitete Spalte in Snowflake verwendet werden kann, um eine andere abgeleitete Spalte zu berechnen. Außerdem erfahren wir, wie wir abgeleitete Spalten in einer Abfrage, in Unterabfragen und mit CTEs verwenden können.

Wir erstellen eine abgeleitete Spalte, AMT1, aus der AMT-Spalte und verwenden dann direkt die erste abgeleitete Spalte, um die zweite abgeleitete Spalte, AMT2, zu erstellen:

SELECT ID, AMT, AMT * 10 as AMT1, AMT1 + 20 as AMT2
FROM DEMO4_DB.SUBQUERIES.DERIVED;

Die Ergebnisse der Abfrage sind in Abbildung 4-7 zu sehen.

Abbildung 4-7. Ergebnisse der Ausführung der Abfrage mit zwei abgeleiteten Spalten

Wir können die gleichen Ergebnisse erzielen, indem wir eine abgeleitete Spalte, AMT1, erstellen, die dann von einer äußeren SELECT Abfrage konsumiert werden kann. Die Unterabfrage in unserem Beispiel ist eine unkorrelierte skalare Unterabfrage von Snowflake. Zur Erinnerung: Die Subquery wird als unkorrelierte Subquery bezeichnet, weil der zurückgegebene Wert nicht von einer Spalte der äußeren Abfrage abhängt:

SELECT sub.ID, sub.AMT, sub.AMT1 + 20 as AMT2
FROM (SELECT ID, AMT, AMT * 10 as AMT1
    FROM DEMO4_DB.SUBQUERIES.DERIVED) AS sub;

Schließlich erhalten wir die gleichen Ergebnisse, wenn wir eine abgeleitete Spalte als Teil der WITH Klausel verwenden. Du wirst feststellen, dass wir eine CTE-Subquery eingefügt haben, die die Modularität erhöhen und die Wartung vereinfachen kann. Die CTE definiert einen temporären View-Namen, der in unserem Beispiel CTE1 ist. In der CTE sind die Spaltennamen und ein Abfrageausdruck enthalten, dessen Ergebnis im Grunde eine Tabelle ist:

WITH CTE1 AS (SELECT ID, AMT, AMT * 10 as AMT2
    FROM DEMO4_DB.SUBQUERIES.DERIVED)
SELECT a.ID, b.AMT, b.AMT2 + 20 as AMT2
FROM DEMO4_DB.SUBQUERIES.DERIVED a
    JOIN CTE1 b ON(a.ID = b.ID);

Ein großer Vorteil von CTEs ist, dass sie deinen Code lesbarer machen können. Mit einer CTE kannst du eine temporäre Tabelle einmal definieren und immer dann darauf verweisen, wenn du sie brauchst, anstatt jedes Mal dieselbe Subquery zu deklarieren. Auch wenn es hier nicht gezeigt wird, kann eine CTE rekursiv sein. Eine rekursive CTE kann eine Tabelle viele Male mit sich selbst verbinden, um hierarchische Daten zu verarbeiten.

Warnung

Wenn eine CTE und eine Tabelle oder Ansicht denselben Namen haben, hat die CTE Vorrang. Es wird daher empfohlen, immer einen eindeutigen Namen für deine CTEs zu wählen.

Vorsicht bei mehrreihigen Einsätzen

Jetzt ist ein guter Zeitpunkt für eine kurze Pause, um ein wenig mehr über mehrzeilige Einfügungen zu erfahren. Eine oder mehrere Datenzeilen können mit einer Select-Abfrage oder als explizit angegebene Werte in einer kommagetrennten Liste eingefügt werden. Der Einfachheit halber haben wir in diesem Kapitel Werte in kommagetrennte Listen eingefügt.

gibt es eine wichtige Sache, die du bei mehrzeiligen Einfügungen beachten musst. Wenn du mehrere Datenzeilen in einen VARCHAR Datentyp einfügst, muss jeder Datentyp, der in die VARCHAR-Spalten eingefügt wird, derselbe sein, sonst schlägt die Einfügung fehl. Ein VARCHAR Datentyp kann Datenwerte wie das Wort eins oder die Zahl 1 akzeptieren, aber niemals beide Arten von Werten in der gleichen INSERT Anweisung. Wir können das am besten an einigen Beispielen sehen.

Wir erstellen zunächst ein neues Schema und eine neue Tabelle, um einige mehrzeilige Einfügeversuche durchzuführen. Im ersten Beispiel fügen wir den Wert eins in die VARCHAR DEPT-Spalte ein:

USE ROLE SYSADMIN;
CREATE OR REPLACE SCHEMA DEMO4_DB.TEST;
CREATE OR REPLACE TABLE DEMO4_DB.TEST.TEST1 (ID integer, DEPT Varchar);
INSERT INTO TEST1 (ID, DEPT)
VALUES (1,'one');
SELECT * FROM DEMO4_DB.TEST.TEST1;

Wie erwartet, wurde der Wert erfolgreich eingegeben. Schauen wir uns an, was passiert, wenn wir stattdessen einen numerischen Wert in die VARCHAR-Spalte einfügen:

USE ROLE SYSADMIN;
CREATE OR REPLACE SCHEMA DEMO4_DB.TEST;
CREATE OR REPLACE TABLE DEMO4_DB.TEST.TEST1 (ID integer, DEPT Varchar);
INSERT INTO TEST1 (ID, DEPT)
VALUES (1,1);
SELECT * FROM DEMO4_DB.TEST.TEST1;

Auch hier wurde der Wert erfolgreich eingegeben. Versuchen wir nun, beide Typen in die Spalte innerhalb der gleichen INSERT Anweisung einzufügen:

USE ROLE SYSADMIN;
CREATE OR REPLACE SCHEMA DEMO4_DB.TEST;
CREATE OR REPLACE TABLE DEMO4_DB.TEST.TEST1 (ID integer, DEPT Varchar);
INSERT INTO TEST1 (ID, DEPT)
VALUES (1,'one'), (2,2);
SELECT * FROM DEMO4_DB.TEST.TEST1;

Wenn wir versuchen, zwei verschiedene Datentypen gleichzeitig in die VARCHAR-Spalte einzufügen, tritt ein Fehler auf, wie in Abbildung 4-8 dargestellt.

Abbildung 4-8. Die Fehlermeldung, die man erhält, wenn man versucht, zwei verschiedene Datentypen in eine VARCHAR-Spalte in einer mehrzeiligen INSERT Anweisung einzufügen

Versuchen wir es noch einmal, aber dieses Mal fügen wir zwei Werte mit demselben Datentyp ein:

USE ROLE SYSADMIN;
CREATE OR REPLACE SCHEMA DEMO4_DB.TEST;
CREATE OR REPLACE TABLE DEMO4_DB.TEST.TEST1 (ID integer, DEPT Varchar);
INSERT INTO TEST1 (ID, DEPT)
VALUES (1,'one'), (2,'two');
SELECT * FROM DEMO4_DB.TEST.TEST1;

Wir sind auch erfolgreich, wenn wir zwei numerische Werte in die VARCHAR-Spalte einfügen:

USE ROLE SYSADMIN;
CREATE OR REPLACE SCHEMA DEMO4_DB.TEST;
CREATE OR REPLACE TABLE DEMO4_DB.TEST.TEST1 (ID integer, DEPT Varchar);
INSERT INTO TEST1 (ID, DEPT)
VALUES (1,1), (2,2);
SELECT * FROM DEMO4_DB.TEST.TEST1;

Du wirst feststellen, dass wir erfolgreich zwei verschiedene Datentypen in die VARCHAR-Spalte laden können, allerdings nicht gleichzeitig. Und wenn wir zwei verschiedene Datentypen in der VARCHAR-Spalte haben, können wir immer noch zusätzliche Werte hinzufügen:

INSERT INTO TEST1 (ID, DEPT)
VALUES (5, 'five');
SELECT * FROM DEMO4_DB.TEST.TEST1;

Mehrzeilige Einfügungen sind eine Möglichkeit, Daten in Snowflake zu übertragen. Kapitel 6 ist dem Laden und Entladen von Daten gewidmet und enthält eine ausführliche Diskussion der Optionen zum Laden von Massendaten und zum kontinuierlichen Laden von Daten.

Abfrageoperatoren

Es gibt verschiedene Arten von Abfrageoperatoren, darunter arithmetische, vergleichende, logische, Unterabfrage- und Mengenoperatoren.

Arithmetische Operatoren, einschließlich +, –, *, / und %, erzeugen eine numerische Ausgabe aus einer oder mehreren Eingaben. Die Skala und Genauigkeit der Ausgabe hängt von der Skala und Genauigkeit der Eingabe(n) ab. Beachte, dass die Subtraktion die einzige arithmetische Operation ist, die für DATE Ausdrücke zulässig ist.

Vergleichsoperatoren, die typischerweise in einer WHERE Klausel vorkommen, werden verwendet, um die Gleichheit von zwei Eingaben zu testen. Zu den Vergleichsoperatoren gehören die folgenden:

  • Gleich (=)

  • Nicht gleich (!= oder <>)

  • Weniger als (<)

  • Kleiner als oder gleich (<=)

  • Größer als (>)

  • Größer als oder gleich (>=)

Tipp

Erinnere dich daran, dass die Werte von TIMESTAMP_TZ auf der Grundlage ihrer Zeit in UTC verglichen werden, also ohne Berücksichtigung der Sommerzeit. Das ist wichtig, weil zum Zeitpunkt der Erstellung TIMESTAMP_TZ den Offset einer bestimmten Zeitzone speichert, nicht die tatsächliche Zeitzone.

Logische Operatoren können nur in der WHERE Klausel verwendet werden. Die Rangfolge dieser Operatoren ist NOT dann AND dann OR. Zu den Unterabfrageoperatoren gehören [NOT] EXISTS, ANY oder ALL und [NOT] IN. Abfragen können kombiniert werden, wenn man Mengenoperatoren wie INTERSECT, MINUS oder EXCEPT, UNION und UNION ALL verwendet.

Die Standardeinstellung für die Reihenfolge der Operatoren ist INTERSECT als höchste Priorität, gefolgt von EXCEPT, MINUS und UNION, und schließlich UNION ALL als niedrigste Priorität. Natürlich kannst du die Standardeinstellung jederzeit durch Klammern außer Kraft setzen. Beachte, dass die Set-Operation UNION kostspielig ist, weil sie die Datensätze sortieren muss, um doppelte Zeilen zu entfernen.

Warnung

Wenn du Set-Operatoren verwendest, musst du sicherstellen, dass jede Abfrage die gleiche Anzahl von Spalten auswählt und der Datentyp jeder Spalte konsistent ist, obwohl ein expliziter Typ-Cast verwendet werden kann, wenn die Datentypen inkonsistent sind.

Langlaufende Abfragen sowie Abfrageleistung und -optimierung

Das Snowflake-System bricht langlaufende Abfragen ab. Die Standarddauer für lang laufende Abfragen beträgt zwei Tage, aber der STATEMENT_TIMEOUT_IN_SECONDS Dauerwert kann jederzeit auf Konto-, Sitzungs-, Objekt- oder virtueller Lagerebene festgelegt werden.

Während des Snowflake SQL-Abfrageprozesses finden die Optimierungs-Engines unter anderem den effizientesten Ausführungsplan für eine bestimmte Abfrage. In Kapitel 9 erfahren wir mehr über die Analyse der Abfrageleistung und Optimierungstechniken sowie über die Verwendung des Query Profilers von Snowflake.

Snowflake Abfrage-Grenzwerte

Für SQL -Anweisungen, die über Snowflake-Clients übermittelt werden, gilt eine Begrenzung der Abfragetextgröße auf 1 MB. Darin enthalten sind auch Literale, sowohl String- als auch Binärliterale. Die Begrenzung der Abfragetextgröße gilt für die komprimierte Größe der Abfrage. Da das Komprimierungsverhältnis für Daten jedoch sehr unterschiedlich ist, wird empfohlen, die unkomprimierte Abfragetextgröße unter 1 MB zu halten.

Außerdem begrenzt Snowflake die Anzahl der zulässigen Ausdrücke in einer Abfrage auf 16.384. Es gibt verschiedene Möglichkeiten, diese Art von Fehler zu beheben, je nachdem, was du mit deiner SQL-Abfrageanweisung bezweckst. Wenn du versuchst, Daten einzufügen, wenn du die Fehlermeldung erhältst, versuche, die Anweisung in kleinere Abfragen aufzuteilen. Noch besser wäre es wahrscheinlich, wenn du den Befehl COPY INTO anstelle des Befehls INSERT verwenden würdest.

Eine andere Art von Abfragegrenzungsfehler tritt auf, wenn eine SELECT Anweisung mit einer IN Klausel verwendet wird, die mehr als 16.384 Werte enthält. Hier ist ein Beispiel dafür, wie dieser Code aussehen könnte:

SELECT  <column_1> FROM  <table_1>
WHERE <column_2> IN  (1, 2, 3, 4, 5,...);

Eine Lösung wäre, einen JOIN oder UNION Befehl zu verwenden, nachdem du die Werte in eine zweite Tabelle eingegeben hast. Der SQL-Code könnte wie folgt aussehen::

SELECT <column_1>
FROM  <table_1> a
    JOIN  <table_2> b ON a.<column_2>  = b.<column_2>;

Einführung in die von Snowflake unterstützten Datentypen

Snowflake unterstützt die grundlegenden SQL-Datentypen, einschließlich Geodatentypen, und einen booleschen logischen Datentyp, der eine ternäre Logik ermöglicht. Der Datentyp BOOLEAN von Snowflake kann einen unbekannten Wert oder einen Wert TRUE oder FALSE haben. Wenn der Boolesche Wert in einem Ausdruck verwendet wird, z. B. in einer SELECT Anweisung, ergibt ein unbekannter Wert NULL. Wenn der Boolesche Wert als Prädikat verwendet wird, z. B. in einer WHERE Klausel, wird das unbekannte Ergebnis zu FALSE ausgewertet. Es gibt einige Datentypen, die von Snowflake nicht unterstützt werden, z. B. Large Object (LOB), einschließlich BLOB und CLOB, sowie ENUM und benutzerdefinierte Datentypen.

Snowflake bietet native Unterstützung für geospatiale Merkmale wie Punkte, Linien und Polygone auf der Erdoberfläche. Der Datentyp von Snowflake GEOGRAPHY folgt dem WGS-Standard. Punkte auf der Erde werden in Längen- und Breitengraden dargestellt. Die Höhenlage wird derzeit nicht unterstützt.

Hinweis

Wenn du über Geodaten wie Längen- und Breitengrade, WKT, WKB oder GeoJSON verfügst, empfiehlt es sich, diese Daten in GEOGRAPHY-Spalten zu konvertieren und zu speichern, anstatt sie in ihrem ursprünglichen Format in VARCHAR-, VARIANT- oder NUMBER-Spalten zu belassen. Dies kann die Leistung von Abfragen, die Geofunktionen verwenden, erheblich verbessern.

In diesem Abschnitt gehen wir näher auf verschiedene Snowflake-Datentypen ein, z. B. numerisch, string und binär, Datum und Uhrzeit, halbstrukturiert und unstrukturiert.

Numerische Datentypen

Die numerischen Datentypen von Snowflake umfassen Festkomma- und Gleitkommazahlen, wie in Tabelle 4-2 beschrieben. Die Tabelle enthält unter Informationen über die Genauigkeit und Skalierung der einzelnen numerischen Datentypen. Die Genauigkeit, d. h. die Gesamtzahl der Stellen, wirkt sich auf die Speicherung aus, während die Skalierung, d. h. die Anzahl der Stellen nach dem Komma, keine Rolle spielt. Allerdings kann die Verarbeitung numerischer Datenwerte mit einer größeren Skala zu einer langsameren Verarbeitung führen.

Tabelle 4-2. Numerische Snowflake-Datentypen
Datentypen für Festkommazahlen Präzision Kommentare
NUMBER Optional (38, 0) Zahlen mit bis zu 38 Ziffern; die maximale Skala beträgt 37
DECIMAL, NUMERIC Optional (38,0) Synonym für NUMBER
INT, INTEGER, BIGINT, SMALLINT, TINYINT, BYTEINT Kann nicht angegeben werden; immer (38,0) Mögliche Werte: -99999999999999999999999999999999999999 to +99999999999999999999999999999999999999 (inclusive)
Datentypen mit Fließkommazahlen Kommentare
FLOAT, FLOAT4, FLOAT8 Ungefähr 15 Ziffern Die Werte reichen von etwa 10-308 bis 10+308
DOUBLE, DOUBLE PRECISION, REAL Ungefähr 15 Ziffern Synonym für FLOAT
Hinweis

Es ist ein bekanntes Problem, dass DOUBLE-, DOUBLE PRECISION- und REAL-Spalten als DOUBLE gespeichert, aber als FLOAT angezeigt werden.

Festkommazahlen sind exakte numerische Werte und werden daher oft für natürliche Zahlen und exakte Dezimalwerte wie Geldbeträge verwendet. Im Gegensatz dazu werden Fließkomma-Datentypen am häufigsten in der Mathematik und den Naturwissenschaften verwendet.

Du kannst sehen, wie sich Festkommazahlen je nach Datentyp unterscheiden. Navigiere zum Arbeitsblatt in Kapitel 4 und probiere dann das folgende Beispiel aus:

USE ROLE SYSADMIN;
CREATE OR REPLACE SCHEMA DEMO4_DB.DATATYPES;
CREATE OR REPLACE TABLE NUMFIXED (
    NUM NUMBER,
    NUM12 NUMBER(12, 0),
    DECIMAL DECIMAL (10, 2),
    INT INT,
    INTEGER INTEGER
);

Um zu sehen, was erstellt wurde, kannst du die Anweisung DESC TABLE NUMFIXED ausführen, um die in Abbildung 4-9 gezeigten Ergebnisse zu erhalten.

Abbildung 4-9. Ergebnisse für den Datentyp Snowflake Festkommazahl

Anhand des nächsten Beispiels kannst du Festkommazahlen mit Fließkommazahlen vergleichen:

USE ROLE SYSADMIN; USE SCHEMA DEMO4_DB.DATATYPES;
CREATE OR REPLACE TABLE NUMFLOAT (
    FLOAT FLOAT,
    DOUBLE DOUBLE,
    DP DOUBLE PRECISION,
    REAL REAL
);

Verwende erneut den Befehl Desc, um die Ergebnisse zu sehen, wie in Abbildung 4-10 dargestellt:

DESC TABLE NUMFLOAT;
Abbildung 4-10. Ergebnisse für den Datentyp Snowflake Fließkommazahl

In der traditionellen Datenverarbeitung sind Float-Datentypen dafür bekannt, dass sie bei Berechnungen schneller sind. Aber ist diese Aussage über Float-Datentypen in modernen Datenplattformen wie Snowflake immer noch richtig? Nicht unbedingt. Es ist wichtig zu bedenken, dass Integer-Werte in Snowflake in einem komprimierten Format gespeichert werden können, Float-Datentypen dagegen nicht. Dies führt zu weniger Speicherplatz und geringeren Kosten für Ganzzahlen. Die Abfrage von Zeilen für einen Integer-Tabellentyp nimmt auch deutlich weniger Zeit in Anspruch.

Warnung

Aufgrund der Ungenauigkeit von Fließkomma-Datentypen können bei Fließkomma-Operationen kleine Rundungsfehler auftreten, die sich aufaddieren können, vor allem wenn du Aggregatfunktionen zur Verarbeitung einer großen Anzahl von Zeilen verwendest.

Die numerischen Datentypen von Snowflake werden durch numerische Konstanten unterstützt. Konstanten, die auch als Literale bezeichnet werden, stellen feste Datenwerte dar. Den numerischen Ziffern 0 bis 9 kann ein positives oder negatives Vorzeichen vorangestellt werden. Exponenten, die mit e oder E gekennzeichnet sind, werden in Snowflake auch von numerischen Konstanten unterstützt.

String und binäre Datentypen

Snowflake unterstützt sowohl Text- als auch binäre String-Datentypen, deren Details in Tabelle 4-3 zu sehen sind.

Tabelle 4-3. Snowflake Text- und binäre String-Datentypen
Text-String-Datentypen Parameter Kommentare
VARCHAR Optionaler Parameter (N), maximale Anzahl von Zeichen Enthält Unicode-Zeichen; kein Leistungsunterschied zwischen der Verwendung der vollen Länge VARCHAR (16.777.216) oder einer kleineren Länge
CHAR, CHARACTERS Synonym für VARCHAR; die Länge ist CHAR(1), wenn nicht angegeben
STRING, TEXT Synonym für VARCHAR
Binäre String-Datentypen Kommentare
BINARY Hat keine Vorstellung von Unicode-Zeichen, daher wird die Länge immer in Bytes gemessen; wenn die Länge nicht angegeben wird, ist der Standardwert 8 MB (die maximale Länge)
VARBINARY Synonym für BINARY

Du kannst sehen, wie sich die Textstring-Datentypen unterscheiden, indem du das folgende Beispiel versuchst, das die Textstring-Felder erstellt und dann die Tabelle beschreibt:

USE ROLE SYSADMIN; USE SCHEMA DEMO4_DB.DATATYPES;
CREATE OR REPLACE TABLE TEXTSTRING(
    VARCHAR VARCHAR,
    V100 VARCHAR(100),
    CHAR CHAR,
    C100 CHAR(100),
    STRING STRING,
    S100 STRING(100),
    TEXT TEXT,
    T100 TEXT(100)
);

DESC TABLE TEXTSTRING;

Wenn du dem Beispiel gefolgt bist, solltest du die in Abbildung 4-11 gezeigte Ausgabe sehen.

Abbildung 4-11. Ergebnisse der Erstellung einer TEXTSTRING Tabelle

Die String-Datentypen von Snowflake werden von String-Konstanten unterstützt, die immer von Begrenzungszeichen, entweder einfachen Anführungszeichen oder Dollarzeichen, eingeschlossen sind. Die Verwendung von Dollarzeichen als Begrenzungszeichen ist besonders nützlich, wenn der String viele Anführungszeichen enthält .

Datum und Uhrzeit Ein-/Ausgangsdatentypen

Snowflake verwendet für alle Datums- und Zeitangaben den Gregorianischen Kalender und nicht den Julianischen Kalender. Die Snowflake Datums- und Zeitdatentypen sind in Tabelle 4-4 zusammengefasst.

Tabelle 4-4. Snowflake Datum und Zeit Datentypen
Datum und Zeit Datentypen Standard-Zuordnung Kommentare
DATE Einfacher DATE Typ; die meisten gängigen Datumsformen werden akzeptiert; alle akzeptierten Zeitstempel sind gültige Eingaben, wobei TIME abgeschnitten wird; die zugehörige Zeit wird als Mitternacht angenommen
DATETIME Alias für TIMESTAMP_NTZ
TIME Einfacher TIME Typ in der Form HH:MI:SS, intern als Wanduhrzeit gespeichert; Zeitzonen werden nicht berücksichtigt
TIMESTAMP Standard ist TIMESTAMP_NTZ Benutzerdefinierter Alias für eine der drei TIMESTAMP_ Varianten
TIMESTAMP_LTZ Interne UTC-Zeit mit einer bestimmten Genauigkeit; TIMESTAMP mit lokaler Zeitzone
TIMESTAMP_NTZ Interne Wanduhrzeit; TIMESTAMP ohne Zeitzone
TIMESTAMP_TZ Interne UTC-Zeit mit einem Zeitzonen-Offset; TIMESTAMP mit Zeitzone

Die Daten- und Zeitdatentypen von Snowflake werden durch Intervallkonstanten sowie Datums- und Zeitkonstanten unterstützt. Intervallkonstanten können verwendet werden, um eine bestimmte Zeitspanne zu einem Datum, einer Uhrzeit oder einem Zeitstempel zu addieren oder davon zu subtrahieren. Das Intervall ist kein Datentyp; es kann nur in der Datums-, Zeit- oder Zeitstempelarithmetik verwendet werden und steht für Sekunden, wenn der Datums- oder Zeitteil nicht angegeben ist.

Hinweis

Die Reihenfolge der Intervallschritte ist wichtig, weil die Inkremente in der Reihenfolge addiert oder subtrahiert werden, in der sie aufgelistet sind. Das kann für Berechnungen wichtig sein, die von Schaltjahren beeinflusst werden.

Semi-Strukturierte Datenstrukturen

Strukturierte Daten, die als quantitative Daten bekannt sind, können leicht in einer Datenbanktabelle in Form von Zeilen und Spalten gespeichert werden, während halbstrukturierte Daten, wie z. B. XML-Daten, nicht schemaabhängig sind, was ihre Speicherung in einer Datenbank erschwert. In manchen Situationen können halbstrukturierte Daten jedoch in einer relationalen Datenbank gespeichert werden.

Snowflake unterstützt Datentypen für den Import und die Bearbeitung von semi-strukturierten Daten wie JSON-, Avro-, ORC-, Parquet- und XML-Daten. Snowflake ermöglicht dies über den universellen Datentyp VARIANT, einen speziellen Spaltentyp, mit dem du halbstrukturierte Daten speichern kannst. Tabelle 4-5 enthält weitere Informationen über die halbstrukturierten Datentypen in Snowflake. Beachte, dass es möglich ist, dass ein VARIANT Wert fehlt, was als etwas anderes als ein echter Nullwert angesehen wird.

Tabelle 4-5. Snowflake semi-strukturierte Datentypen
Semi-strukturierte Datenstrukturen Eigenschaften Kommentare
VARIANT Kann OBJECT speichern und ARRAY Speichert Werte jedes anderen Typs, bis zu einem Maximum von 16 MB unkomprimiert; intern in komprimierter spaltenförmiger Binärdarstellung gespeichert
OBJECT Stellt Sammlungen von Schlüssel-Wert-Paaren dar, wobei der Schlüssel ein nicht leerer String und der Wert vom Typ VARIANT ist.
ARRAY Stellt Arrays beliebiger Größe dar, deren Index eine nichtnegative ganze Zahl ist und deren Werte den Typ VARIANT haben.
Warnung

Wenn sie in eine VARIANT-Spalte geladen werden, werden nicht-native Werte wie Datums- und Zeitstempel als Strings gespeichert. Die Speicherung von Werten auf diese Weise führt wahrscheinlich dazu, dass Vorgänge langsamer sind und mehr Speicherplatz verbrauchen, als wenn Datums- und Zeitstempelwerte in einer relationalen Spalte mit dem entsprechenden Datentyp gespeichert werden.

In den praktischen Übungen für halbstrukturierte Daten werden wir die Snowflake-Beispielwetterdaten verwenden, die im nativen JSON-Format gespeichert sind. Wir werden einige Zeit damit verbringen, ein Gefühl für die vorhandenen Daten zu bekommen, und dann lernen, wie wir mit der Funktion FLATTEN eine seitliche Ansicht der halbstrukturierten Daten erstellen können.

Warnung
Zum Zeitpunkt der Erstellung dieses Artikels war der Wetterdatensatz in den kostenlosen Testkonten von Snowflake verfügbar. Es kann jedoch sein, dass Snowflake diesen Datensatz im Laufe der Zeit abschafft. Weitere Informationen findest du unter https://github.com/SnowflakeDefinitiveGuide.

Werfen wir zunächst einen kurzen Blick auf ein paar Datenzeilen:

USE ROLE SYSADMIN; 
USE SCHEMA SNOWFLAKE_SAMPLE_DATA.WEATHER;
SELECT * FROM DAILY_16_TOTAL
LIMIT 5;

Du solltest sehen, dass es zwei Spalten gibt: eine VARIANTEN-Spalte (V) und eine TIMESTAMP Spalte (T), wie in Abbildung 4-12 dargestellt.

Abbildung 4-12. Zwei Spalten in der Snowflake-Wetterbeispiel-Datentabelle

Konzentrieren wir uns auf die Daten in der Spalte VARIANT:

SELECT v:city
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL
LIMIT 10;

Sobald die Ergebnisse zurückgegeben werden, klicke auf V:CITY oben in der Spalte. Dadurch wird die Spalte hervorgehoben und du erfährst, dass es vier verschiedene Objektschlüssel in dieser Spalte gibt (siehe Abbildung 4-13). Die Objektschlüssel für V:CITY sind in dieser Reihenfolge: Koordinaten, Land, ID und Name.

Abbildung 4-13. Vier verschiedene Objektschlüssel für CITY-Daten in der VARIANT-Spalte

Lass uns nun einige der CITY-Daten manuell aufschlüsseln und sie in einer logischeren Reihenfolge auflisten (wie in Abbildung 4-14 gezeigt):

SELECT v:city:id, v:city:name, v:city:country, v:city:coord
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL
LIMIT 10;
Abbildung 4-14. Detail der CITY-Daten in der Spalte VARIANT

Die Angaben zu Längen- und Breitengraden sind in den Koordinateninformationen enthalten. Trennen wir sie und geben wir den Spalten passende Namen:

SELECT v:city:id AS ID, v:city:name AS CITY,
    v:city:country AS COUNTRY, v:city:coord:lat AS LATITUDE,
    v:city:coord:lon AS LONGITUDE
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL
LIMIT 10;

Wir können einen abweichenden Datentyp in einen anderen Datentyp umwandeln. Im nächsten Beispiel wandeln wir die Daten von Stadt und Land VARIANT in einen VARCHAR Datentyp um und weisen den Spalten sinnvolle Bezeichnungen zu:

SELECT v:city:id AS ID, v:city:name::varchar AS city,
    v:city.country::varchar AS country, v:city:coord:lon
    AS longitude, v:city:coord:lat AS latitude
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL
LIMIT 10;

Die Ergebnisse sind in Abbildung 4-15 dargestellt.

Abbildung 4-15. Umwandlung der Daten von Stadt und Land VARIANT in einen VARCHAR Datentyp

Wir können bestätigen, dass wir die beiden Spalten erfolgreich verbunden haben, indem wir Snowflake bitten, die Ergebnisse unserer letzten Abfrage zu beschreiben:

DESC RESULT LAST_QUERY_ID();

Als Nächstes sehen wir uns weitere Daten in der Spalte VARIANT an:

SELECT v:data
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL
LIMIT 10;

Sobald die Ergebnisse angezeigt werden, klicke auf V:DATA am oberen Rand der Spalte. Dadurch wird die Spalte hervorgehoben und du erhältst die Spaltendetails, die du auf der rechten Seite siehst (wie in Abbildung 4-16 dargestellt). Du wirst feststellen, dass es in dieser Spalte ein Feld gibt, das sich auf die DATA-Informationen bezieht.

Abbildung 4-16. Das Wetterdatenfeld in der Spalte VARIANT

Da die DATA-Informationen als Array gespeichert sind, können wir uns ein bestimmtes Element im Array ansehen. Achte darauf, dass du auf jede Ergebniszeile klickst, um zu sehen, dass für jede Zeile nur ein Element ausgewählt wurde:

SELECT v:data[5]
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL
LIMIT 10;

Wir können die zurückgegebenen Informationen weiter einschränken, indem wir uns den Luftfeuchtigkeitswert für einen bestimmten Tag für eine bestimmte Stadt und ein bestimmtes Land ansehen:

SELECT v:city:name AS city, v:city:country AS country,
    v:data[0]:humidity AS HUMIDITY
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL
LIMIT 10;

Lass uns einen kurzen Rückblick machen. Wenn wir uns das DATA-Array, v:data AS DATA, in der folgenden Anweisung ansehen, stellen wir fest, dass jede Zeile ein komplettes Daten-Array enthält. In jedem Datenfeld gibt es 16 Elemente für jedes einzelne Datenelement (siehe Abbildung 4-17). In unsere SQL-Abfrage werden wir die ersten beiden Datenelemente für die Luftfeuchtigkeit und die Tagestemperatur aufnehmen:

SELECT v:data[0]:dt::timestamp AS TIME,
v:data[0]:humidity AS HUMIDITY0, v:data[0]:temp:day AS DAY_TEMP0,
v:data[1]:humidity AS HUMIDITY1, v:data[1]:temp:day AS DAY_TEMP1,  
v:data AS DATA
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL
LIMIT 100;
Abbildung 4-17. Ein DATA-Array-Element (Pfad) pro Zeile

Schauen wir uns an, wie wir die Tabellenfunktion FLATTEN nutzen können. Die Funktion FLATTEN erzeugt eine Seitenansicht einer VARIANTEN-, OBJEKT- oder ARRAY-Spalte. Wir zeigen dir, wie FLATTEN mit dem DATA-Array in der Beispiel-Wetterdaten-Tabelle funktioniert:

SELECT d.value:dt::timestamp AS TIME,
    v:city:name AS CITY, v:city:country AS COUNTRY,
    d.path AS PATH, d.value:humidity AS HUMIDITY,
	d.value:temp:day AS DAY_TEMP,v:data AS DATA
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL,
LATERAL FLATTEN(input => daily_16_total.v:data) d
LIMIT 100;

Du wirst feststellen, dass für jede der 16 abgeflachten Zeilen dasselbe DATA-Array angezeigt wird, aber die HUMIDITY und DAY_TEMP, die in jeder Zeile angezeigt werden, sind mit dem spezifischen PATH des Arrays verbunden (wie in Abbildung 4-18 gezeigt).

Abbildung 4-18. Das abgeflachte DATA-Array

Die Temperaturinformationen im DATA-Array haben sechs verschachtelte Werte: day, eve, max, min, morn und night. Wir können ein verschachteltes FLATTEN verwenden, um das DATA-Array weiter zu vereinfachen. Auf diese Weise erscheint jede DATA-Zeile 96 Mal, sechs Mal für jeden der 16 PATH-Werte (wie in Abbildung 4-19 gezeigt):

SELECT d.value:dt::timestamp AS TIME,
t.key,
v:city:name AS CITY, v:city:country AS COUNTRY,
d.path AS PATH,
d.value:humidity AS HUMIDITY,
d.value:temp:day AS DAY_TEMP,
d.value:temp:night AS NIGHT_TEMP,
v:data AS data
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL,
LATERAL FLATTEN(input => daily_16_total.v:data) d,
LATERAL FLATTEN(input => d.value:temp) t
WHERE v:city:id = 1274693 
LIMIT 100;
Abbildung 4-19. Das abgeflachte, verschachtelte DATA-Array

Wie wir gerade gesehen haben, wird die Funktion Snowflake FLATTEN verwendet, um halbstrukturierte Daten in eine relationale Darstellung umzuwandeln.

Hinweis

Es möglich, eine LATERAL JOIN mit einer FLATTEN Funktion zu kombinieren, um Ereignisse in einzelne JSON-Objekte aufzuteilen, während die globalen Daten erhalten bleiben.

Unstrukturierte Datentypen

Es hat viele Vorteile, unstrukturierte Daten zu nutzen, um Erkenntnisse zu gewinnen. Unstrukturierte Daten sind oft qualitativ, aber es kann sich auch um quantitative Daten handeln, denen Zeilen, Spalten oder Begrenzungszeichen fehlen, wie z. B. bei einer PDF-Datei, die quantitative Daten enthält. Medienprotokolle, medizinische Bilder, Audiodateien von Callcenter-Aufzeichnungen, Dokumentenbilder und viele andere Arten von unstrukturierten Daten können für Analysezwecke und für die Stimmungsanalyse verwendet werden. Die Speicherung und Verwaltung unstrukturierter Daten ist nicht einfach. Unstrukturierte Daten sind nicht in einer vordefinierten Weise organisiert und eignen sich daher nicht gut für relationale Datenbanken. In der Regel werden unstrukturierte Daten in Blob-Speichern gespeichert, was mehrere Nachteile hat und die Suche nach Dateien schwierig und zeitaufwändig macht.

Um die Durchsuchbarkeit von unstrukturierten Daten zu verbessern, hat Snowflake kürzlich integrierte Verzeichnistabellen eingeführt. Die Verwendung eines tabellarischen Dateikatalogs für die Suche nach unstrukturierten Daten ist jetzt so einfach wie die Verwendung eines SELECT * Befehls für die Verzeichnistabelle. Nutzer können auch einen Tabellenstrom auf einer Verzeichnistabelle aufbauen, der es ermöglicht, Pipelines zur Verarbeitung unstrukturierter Daten zu erstellen. Darüber hinaus können Snowflake-Nutzer sichere Ansichten auf Verzeichnistabellen erstellen und diese sicheren Ansichten mit anderen teilen .

Wie Snowflake die Nutzung unstrukturierter Daten unterstützt

Unstrukturierte Daten machen heute einen immer größeren Anteil der erzeugten Daten aus. Beispiele für unstrukturierte Daten sind Video-, Audio- oder Bilddateien, Logdateien, Sensordaten und Beiträge in sozialen Medien. Unstrukturierte Daten, die von Menschen oder Maschinen erzeugt werden können, haben eine interne Struktur, die jedoch nicht in einem strukturierten Datenbankformat gespeichert werden kann.

Es gibt viele Gründe, warum du all diese unstrukturierten Daten nutzen möchtest. Solche Anwendungsfälle sind z. B. die Gewinnung von Erkenntnissen wie die Stimmungsanalyse aus Callcenter-Aufzeichnungen, die Extraktion von Text für Analysen mithilfe von optischer Zeichenerkennung auf Versicherungskarten oder verschreibungspflichtigen Medikamenten, die Anwendung von maschinellem Lernen auf medizinische DICOM-Bilder oder die Extraktion von Schlüsselwertpaaren aus gespeicherten PDF-Dokumenten.

Es ist kein Geheimnis, dass unstrukturierte Daten komplex sind und die Speicherung, Suche und Analyse dieser Daten viele Herausforderungen mit sich bringt. Herkömmliche Data Warehouses und Data Lakes sind nicht in der Lage, die Arbeitsanforderungen der heutigen Datenformate, insbesondere unstrukturierter Daten, angemessen zu erfüllen. Snowflake ist jedoch kein herkömmliches Data Warehouse oder Data Lake. Stattdessen handelt es sich um eine Datenplattform, die von Grund auf für die Cloud entwickelt wurde. Daher hat sie viele der Schwierigkeiten beseitigt, die mit der Speicherung, Suche, Analyse und Verarbeitung unstrukturierter Daten verbunden sind.

Die erste Überlegung bei der Verwendung unstrukturierter Daten ist, wie und wo die unstrukturierten Dateien gespeichert werden sollen. Bei Snowflake gibt es dafür zwei Möglichkeiten: interne Stages und externe Stages. Wir würden eine interne Stage verwenden, wenn wir Daten intern in Snowflake speichern wollen; vor allem, wenn wir nach einer einfachen, leicht zu verwaltenden Lösung suchen. Denn Snowflake verwaltet automatisch die Skalierbarkeit, Verschlüsselung, Datenkomprimierung und andere Aspekte der Speicherung. Wenn wir ältere Daten anderswo in der Cloud gespeichert haben, würden wir alternativ eine externe Stufe, die so genannte Bring your own Speicherung, verwenden, da es nicht nötig ist, alle Daten in Snowflake zu speichern.

Es ist zwar möglich, unstrukturierte Daten intern in einer Snowflake-Tabelle mit dem Spaltentyp VARIANT zu speichern, aber in der Regel wird dies nicht empfohlen, da die Speicherung von Dateien auf 16 MB begrenzt ist. Wenn wir stattdessen eine Stufe verwenden, gibt es keine anderen Größenbeschränkungen als die, die von den großen Cloud-Providern auferlegt werden, auf denen deine Snowflake-Instanz aufgebaut ist: 5 TB Daten für AWS und GCP oder 256 GB Daten für Azure.

Unabhängig davon, ob du interne oder externe Snowflake-Stages verwendest, lässt sich der Zugriff auf die Daten ganz einfach über rollenbasierte Zugriffskontrollen steuern. Mithilfe der Anweisungen GRANT und REVOKE können Berechtigungen für Snowflake-Ressourcen wie Stages erteilt werden, indem man Rollen Berechtigungen erteilt, die dann an Personen vergeben werden. Es ist leicht zu verstehen und zu lernen, wie man einen feinkörnigen Zugriff auf Daten in einer internen oder externen Stage oder auf eine Teilmenge der in Views gespeicherten Daten gewährt, die als Snowflake-Objekte auf Stages erstellt werden. Eine Auffrischung der Snowflake-Zugriffskontrollen findest du in Kapitel 5.

Mit Snowflake kann die Speicherung und der Zugriff auf unstrukturierte Daten auf drei verschiedene Arten erfolgen: Stage File URLs, Scoped URLs oder Presigned URLs.

URL-Zugang zur Stufendatei

Eine Stage Datei-URL wird verwendet, um eine permanente URL zu einer Datei auf einer Snowflake Stage zu erstellen und wird am häufigsten für benutzerdefinierte Anwendungen verwendet. Der Zugriff auf eine Datei-URL erfolgt über eine GET Anfrage an den REST-API-Endpunkt zusammen mit dem Autorisierungs-Token. Beachte, dass der Benutzer Leserechte auf der Stage haben muss. Die Datei-URLs einer Stage haben die Besonderheit, dass sie in einer Snowflake-Verzeichnistabelle aufgelistet werden können.

Die Fähigkeit von, eine Verzeichnistabelle wie einen Dateikatalog zu erstellen, die du einfach durchsuchen kannst, um Datei-URLs für den Zugriff auf die bereitgestellten Dateien sowie andere Metadaten abzurufen, ist eine einzigartige Funktion, die Snowflake für unstrukturierte Daten bietet. Snowflake-Rollen, denen Privilegien gewährt wurden, können eine Verzeichnistabelle abfragen, um URLs für den Zugriff auf bereitgestellte Dateien abzurufen.

Ob du zum Beispiel nach der Dateigröße oder nach dem letzten Änderungsdatum sortieren willst, oder nur die ersten 100 Dateien oder die größten Dateien nehmen willst, mit Snowflake-Verzeichnistabellen ist das möglich. Du kannst auch Snowflake Streams und Tasks mit Verzeichnistabellen kombinieren, um eine leistungsstarke Kombination zu erhalten. Mit Tabellen-Streams kannst du z. B. ganz einfach alle neuen Dateien finden, die kürzlich hinzugefügt wurden. Da eine Verzeichnistabelle eine Tabelle ist, kannst du feinkörnige Auswahl- und Suchvorgänge durchführen. Suchvorgänge in normalen Blob Stores sind extrem schwierig, weil sie die Kataloginformationen nicht in einem Tabellenformat haben.

Eine Snowflake-Verzeichnistabelle ist eine eingebaute schreibgeschützte Tabelle. Du kannst also keine weiteren Spalten hinzufügen oder die Spalten in einer Verzeichnistabelle ändern. Unter kannst du mit Hilfe von Snowflake Streams und Tasks Werte berechnen und sie in eine neue Tabelle mit einer Spalte einfügen, die die Ergebnisse der Berechnung enthält. Dann kannst du diese Tabelle mit der Verzeichnistabelle verknüpfen, indem du eine Ansicht erstellst. Du kannst auch Tags hinzufügen, falls gewünscht.

Skalierter URL-Zugriff

Eine URL wird häufig für benutzerdefinierte Anwendungen verwendet, insbesondere in Situationen, in denen der Zugriff auf die Daten anderen Konten über die Datenfreigabefunktion gewährt wird oder wenn intern Ad-hoc-Analysen mit Snowsight durchgeführt werden. Die sichere Freigabe unstrukturierter Daten in der Cloud ist mit Snowflake ganz einfach. Auf der Bühne werden keine Privilegien benötigt. Stattdessen erstellst du eine sichere Ansicht und gibst den Inhalt der sicheren Ansicht mithilfe der zugewiesenen URL frei. Die scoped URL ist verschlüsselt, so dass es nicht möglich ist, das Konto, die Datenbank, das Schema oder andere Details zur Speicherung aus der URL zu ermitteln.

Der Zugriff auf Dateien in einer Stufe über eine bestimmte URL kann auf zwei Arten erfolgen. Zum einen kann ein Snowflake-Benutzer in der Ergebnistabelle in Snowsight auf eine scoped URL klicken. Die andere Möglichkeit besteht darin, die zugewiesene URL in einer Anfrage zu senden, woraufhin Snowflake den Benutzer authentifiziert, überprüft, ob die zugewiesene URL noch gültig ist, und den Benutzer dann zu der bereitgestellten Datei in der Cloud Speicherung weiterleitet. Erinnere dich daran, dass der Ort der bereitgestellten Datei in der Speicherung verschlüsselt ist, so dass der Nutzer nicht in der Lage ist, den Ort zu bestimmen. Die zugewiesene URL in der Ausgabe des API-Aufrufs ist 24 Stunden lang gültig, d. h. so lange, wie der Ergebnis-Cache besteht.

Hinweis

Aus Sicherheitsgründen ist es nicht möglich, eine gesperrte URL zu teilen, die für dich freigegeben wurde. Wenn du den Link mit einer anderen Person teilen würdest, die nicht über ähnliche Zugriffsrechte verfügt, würde die Meldung Zugriff verweigert erscheinen.

Vorgegebener URL-Zugang

Eine vordefinierte URL wird meist für Business Intelligence-Anwendungen oder Reporting-Tools verwendet, die unstrukturierte Dateiinhalte für geöffnete Dateien anzeigen müssen. Da die vorzeichenbehafteten URLs bereits authentifiziert sind, kann ein Nutzer oder eine Anwendung direkt auf Dateien zugreifen oder sie herunterladen, ohne dass ein Autorisierungs-Token übergeben werden muss.

Die Funktion GET_PRESIGNED_URL generiert die vordefinierte URL zu einer Stufendatei, indem sie den Stufennamen und den relativen Dateipfad als Eingaben verwendet. Der Zugriff auf Dateien in einer Phase mit einer vordefinierten URL kann auf drei verschiedene Arten erfolgen: Verwende die vordefinierte URL in einem Webbrowser, um direkt zur Datei zu navigieren, klicke auf eine vordefinierte URL in der Ergebnistabelle in Snowsight oder sende die vordefinierte URL in einer REST-API-Anfrage.

Verarbeitung unstrukturierter Daten mit Java-Funktionen und externen Funktionen

Die Möglichkeit, Prozesse auf den unstrukturierten Daten in Dateien auszuführen, ist eine der spannendsten Funktionen von Snowflake. Derzeit gibt es zwei Möglichkeiten, unstrukturierte Daten mit Snowflake zu verarbeiten: Java-Funktionen und externe Funktionen. Für die Zukunft plant Snowflake die Möglichkeit, unstrukturierte Daten mit Python-Funktionen zu verarbeiten.

Wenn du bereits Java-Code hast, den du für unstrukturierte Daten geschrieben hast, ist es sinnvoll, eine benutzerdefinierte Java-Funktion (UDF) zu verwenden. Beachte, dass Java UDFs direkt in Snowflake ausgeführt werden und ein Snowflake Virtual Warehouse verwenden. Daher führen Java UDFs keine API-Aufrufe außerhalb von Snowflake aus. Alles ist streng gesichert und wird innerhalb der Snowflake-Umgebung verwaltet.

Wenn du externe API-Dienste wie maschinelle Lernmodelle, Geocoders oder anderen benutzerdefinierten Code nutzen möchtest, können externe Funktionen verwendet werden. Externe Funktionen ermöglichen es, bestehende maschinelle Lerndienste zu nutzen, um Text aus Bildern zu extrahieren oder PDF-Dateien zu verarbeiten, um Schlüssel-Werte-Paare zu extrahieren. In einer externen Funktion kannst du alle AWS-, Azure- oder GCP-Funktionen nutzen, einschließlich AWS Rekognition oder Azure Cognitive Services. Externe Funktionen, die auf unstrukturierten Daten ausgeführt werden, unabhängig davon, ob sie in internen oder externen Stages gespeichert sind, können verwendet werden, um den Export und Reimport von Daten zu vermeiden .

Snowflake SQL-Funktionen und Sitzungsvariablen

Snowflake bietet den Benutzern die Möglichkeit, UDFs zu erstellen und externe Funktionen zu verwenden sowie auf viele verschiedene integrierte Funktionen zuzugreifen. Auch Sitzungsvariablen erweitern die Möglichkeiten von Snowflake SQL.

Systemdefinierte (eingebaute) Funktionen verwenden

Beispiele für die in Snowflake integrierten Funktionen sind Skalar-, Aggregat-, Fenster-, Tabellen- und Systemfunktionen.

Skalare Funktionen akzeptieren eine einzelne Zeile oder einen einzelnen Wert als Eingabe und geben dann einen Wert als Ergebnis zurück, während Aggregatfunktionen ebenfalls einen einzelnen Wert zurückgeben, aber mehrere Zeilen oder Werte als Eingabe akzeptieren.

Skalare Funktionen

Einige skalare Funktionen arbeiten mit einem String oder einem binären Eingabewert. Beispiele dafür sind CONCAT, LEN, SPLIT, TRIM, UPPER und LOWER Groß-/Kleinschreibung umwandeln und REPLACE. Andere skalare Dateifunktionen, wie GET_STAGE_LOCATION, ermöglichen den Zugriff auf Dateien, die in der Snowflake Speicherung abgelegt sind.

Außerdem kannst du in Snowflake viele Dinge mit Datums- und Zeitdatentypen machen. Einige Beispiele für skalare Datums- und Zeitfunktionen und Funktionen zur Datenerzeugung sind die folgenden:

  • Konstruiere/dekonstruiere (extrahiere) mit Hilfe von Monats-, Tages- und Jahreskomponenten.

  • Trunkiere oder "runde" die Daten auf eine höhere Ebene.

  • Analysiere und formatiere Datumsangaben mit Hilfe von Strings.

  • Addiere/Subtrahiere, um Datumsunterschiede zu finden und zu verwenden.

  • Erstelle Systemdaten oder eine Tabelle mit Daten.

Aggregatfunktionen

Eine Snowflake Aggregatfunktion wird immer eine Zeile zurückgeben, auch wenn die Eingabe keine Zeilen enthält. Die Zeile, die von einer Aggregatfunktion zurückgegeben wird, wenn die Eingabe null Zeilen enthält, kann eine Null, ein leerer String oder ein anderer Wert sein. Aggregatfunktionen können allgemeiner Natur sein, wie z.B. MIN, MAX, MEDIAN, MODE und SUM. Zu den Aggregatfunktionen gehören auch lineare Regression, Statistik und Wahrscheinlichkeit, Häufigkeitsschätzungen, Perzentilschätzungen und vieles mehr.

Snowflake-Fensterfunktionen sind eine besondere Art von Aggregatfunktionen, die auf einer Teilmenge von Zeilen arbeiten können. Diese Teilmenge von zusammenhängenden Zeilen wird als Fenster bezeichnet. Im Gegensatz zu Aggregatfunktionen, die einen einzigen Wert für eine Gruppe von Zeilen zurückgeben, gibt eine Fensterfunktion für jede Eingabezeile eine Ausgabezeile zurück. Die Ausgabe hängt nicht nur von der einzelnen Zeile ab, die an die Funktion übergeben wird, sondern auch von den Werten der anderen Zeilen in dem Fenster, das an die Funktion übergeben wird.

Fensterfunktionen werden häufig verwendet, um eine prozentuale Veränderung gegenüber dem Vorjahr, einen gleitenden Durchschnitt, eine laufende oder kumulierte Summe sowie eine Rangfolge der Zeilen nach Gruppierungen oder benutzerdefinierten Kriterien zu ermitteln.

Wir wollen eine Aggregatfunktion mit einer Fensterfunktion vergleichen. In diesem ersten Beispiel erstellen wir eine Aggregatfunktion, indem wir die Vokale des Alphabets und ihre entsprechenden Stellen verwenden:

SELECT LETTER, SUM(LOCATION) as AGGREGATE
FROM (SELECT 'A' as LETTER, 1 as LOCATION
        UNION ALL (SELECT 'A' as LETTER,1 as LOCATION)
        UNION ALL (SELECT 'E' as LETTER,5 as LOCATION)
     ) as AGG_TABLE
GROUP BY LETTER;

Die Ergebnisse dieser Abfrage sind in Abbildung 4-20 dargestellt.

Abbildung 4-20. Ergebnisse einer Abfrage einer Aggregatfunktion

Als Nächstes erstellen wir eine Fensterfunktion mit der gleichen Logik:

SELECT LETTER, SUM(LOCATION) OVER (PARTITION BY LETTER) as WINDOW_FUNCTION
FROM (SELECT 'A' as LETTER, 1 as LOCATION
        UNION ALL (SELECT 'A' as LETTER, 1 as LOCATION)
        UNION ALL (SELECT 'E' as LETTER, 5 as LOCATION)
     ) as WINDOW_TABLE;

Beachte in Abbildung 4-21, wie der Buchstabe A in der Fensterfunktion denselben Summenwert hat wie in der Aggregatfunktion, sich aber in den Ergebnissen wiederholt, weil die Eingabe zwei separate A-Listen hat.

Abbildung 4-21. Ergebnisse einer Fensterfunktion

Tischfunktionen

Tabellenfunktionen, oft auch Tabellenfunktionen genannt, liefern Ergebnisse in einem Tabellenformat mit einer oder mehreren Spalten und keiner, einer oder vielen Zeilen. Die meisten Snowflake-Tabellenfunktionen sind 1-zu-N-Funktionen, bei denen jede Eingabezeile N Ausgabezeilen erzeugt, aber es gibt auch M-zu-N-Tabellenfunktionen, bei denen eine Gruppe von M Eingabezeilen eine Gruppe von N Ausgabezeilen erzeugt. Tabellenfunktionen können systemdefiniert oder benutzerdefiniert sein. Einige Beispiele für systemdefinierte Tabellenfunktionen sind VALIDATE, GENERATOR, FLATTEN, RESULT_SCAN, LOGIN_HISTORY und TASK_HISTORY.

Systemfunktionen

Eingebaute Systemfunktionen liefern Informationen auf Systemebene, fragen Informationen ab oder führen Steuerungsoperationen durch.

Eine oft genutzte Systeminformationsfunktion ist SYSTEM$CLUSTERING_INFORMATION, die Clustering-Informationen, einschließlich der durchschnittlichen Clustering-Tiefe, über eine oder mehrere Spalten in einer Tabelle liefert.

Systemsteuerungsfunktionen ermöglichen es dir, Aktionen im System auszuführen. Ein Beispiel für eine Kontrollfunktion ist SYSTEM$CANCEL_ALL_QUERIES und erfordert die Sitzungsnummer. Du erhältst die Sitzungsnummer, indem du dich als ACCOUNTADMIN anmeldest. Gehe im Hauptmenü von Snowsight zu Aktivität → Abfrageverlauf und wähle dann mit der Schaltfläche Spalte die Sitzungs-ID aus, damit sie angezeigt wird. Alternativ kannst du auch in der klassischen Konsole zu Konto → Sitzungen gehen:

SELECT SYSTEM$CANCEL_ALL_QUERIES(<session_id>);

Wenn du Abfragen für ein bestimmtes virtuelles Lager oder einen bestimmten Benutzer und nicht für die gesamte Sitzung abbrechen möchtest, solltest du den Befehl ALTER zusammen mit ABORT ALL QUERIES anstelle einer Systemsteuerungsfunktion verwenden.

SQL- und JavaScript-UDFs erstellen und Sitzungsvariablen verwenden

SQL Funktionalität kann durch SQL UDFs, Java UDFs, Python UDFs und Sitzungsvariablen erweitert werden. In Kapitel 3 haben wir uns eingehend mit SQL- und JavaScript-UDFs befasst, daher konzentrieren wir uns in diesem Abschnitt auf Sitzungsvariablen.

Snowflake unterstützt SQL-Variablen, die vom Benutzer mit dem Befehl SET deklariert werden. Diese Sitzungsvariablen existieren, solange eine Snowflake-Sitzung aktiv ist. Variablen werden in einer Snowflake SQL-Anweisung durch ein $ Präfix unterschieden und können auch Bezeichnernamen enthalten, wenn sie mit Objekten verwendet werden. Du musst eine Variable innerhalb des Bezeichners einschließen, z. B. IDENTIFIER($Variable), um eine Variable als Bezeichner zu verwenden. Alternativ kannst du die Variable im Kontext einer FROM Klausel in ein Objekt einbetten.

Um alle in der aktuellen Sitzung definierten Variablen zu sehen, verwende den Befehl SHOW VARIABLES .

Einige Beispiele für Sitzungsvariablenfunktionen sind die folgenden:

  • SYS_CONTEXT und SET_SYS_CONTEXT

  • SESSION_CONTEXT und SET_SESSION_CONTEXT

  • GETVARIABLE und SETVARIABLE

Alle Variablen, die während einer Sitzung erstellt wurden, werden gelöscht, wenn eine Snowflake-Sitzung beendet wird. Wenn du eine Variable während einer Sitzung löschen möchtest, kannst du den Befehl UNSET verwenden.

Externe Funktionen

Eine externe Funktion ist eine Art von UDF, die Code aufruft, der außerhalb von Snowflake gespeichert und ausgeführt wird. Snowflake unterstützt skalare externe Funktionen, d.h. der Remote-Dienst muss für jede empfangene Zeile genau eine Zeile zurückgeben. Innerhalb von Snowflake wird die externe Funktion als Datenbankobjekt gespeichert, das Snowflake zum Aufrufen des Remote-Dienstes verwendet.

ist es wichtig zu wissen, dass Snowflake einen Remote-Dienst nicht direkt aufruft, sondern meist einen Proxy-Dienst, der die Daten an den Remote-Dienst weiterleitet. Das Amazon API Gateway und der Microsoft Azure API Management Service sind zwei Beispiele für Proxy-Dienste, die verwendet werden können. Ein Ferndienst kann als AWS Lambda-Funktion, als Microsoft Azure-Funktion oder als HTTPS-Server (z. B. Node.js) auf einer EC2-Instanz implementiert werden.

Alle Gebühren, die von Anbietern von Remote-Diensten erhoben werden, werden separat in Rechnung gestellt. Snowflake berechnet normale Kosten für die Datenübertragung und die Nutzung des virtuellen Lagers, wenn externe Funktionen genutzt werden.

Die Verwendung externer Funktionen hat viele Vorteile. Externe Funktionen können so erstellt werden, dass sie nicht nur innerhalb von Snowflake, sondern auch von anderen Softwareprogrammen aus aufgerufen werden können. Außerdem kann der Code für die Remote Services in Sprachen wie Go oder C# geschrieben werden - Sprachen, die in anderen von Snowflake unterstützten UDFs nicht verwendet werden können. Einer der größten Vorteile ist, dass die Remote Services für die externen Funktionen von Snowflake mit kommerziell erhältlichen Bibliotheken von Drittanbietern verbunden werden können, z. B. mit Bibliotheken für maschinelles Lernen und Scoring.

Codebereinigung

Die Codebereinigung für dieses Kapitel ist einfach. Du kannst den folgenden Befehl verwenden, um die Datenbank zu löschen, die wir zuvor erstellt haben:

DROP DATABASE DEMO4_DB;

Beachte, dass wir nicht erst alle Tabellen entfernen müssen, denn wenn wir die Datenbank löschen, werden die zugehörigen Tabellen automatisch gelöscht.

Zusammenfassung

In diesem Kapitel haben wir alle unsere Snowflake-Abfragen mit der Rolle SYSADMIN erstellt und ausgeführt. Dies geschah absichtlich, damit wir uns auf die Grundlagen der Snowflake-SQL-Befehle, -Funktionen, -Anweisungen und -Datentypen konzentrieren konnten, ohne uns mit der Komplexität der Snowflake-Zugriffskontrollen auseinandersetzen zu müssen. Jetzt ist es an der Zeit, auf diesem Grundwissen aufzubauen, zusammen mit dem, was wir in Kapitel 3 über die Erstellung und Verwaltung von Architekturobjekten gelernt haben.

Im nächsten Kapitel tauchen wir tief in die Nutzung der Zugriffskontrollen von Snowflake ein. Wenn du damit rechnest, als Administrator für eine der Hauptadministrationsrollen eingesetzt zu werden, wird das nächste Kapitel wahrscheinlich eines der wichtigsten Kapitel für dich auf deiner Lernreise durch Snowflake sein. Auch wenn du keine Administratoraufgaben erwartest, musst du wissen, wie du die volle Funktionalität von Snowflake mit den dir zugewiesenen Rechten nutzen kannst. Auch wenn dir keine Snowflake-Administrator/innenrolle zugewiesen wird, ist es wahrscheinlich, dass du Zugang zu einigen Funktionen erhältst, die früher nur für Administrator/innen reserviert waren.

Snowflake hat sorgfältig darauf geachtet, Zugriffskontrollen zu entwerfen und einzurichten, die einige der Schwächen anderer Plattformen ausgleichen. Ein Beispiel dafür ist, dass Snowflake absichtlich ein Zugriffskontrollsystem entwickelt hat, das das Konzept eines Superusers ausschaltet, das bei vielen Plattformen ein großes Risiko darstellt. Dennoch ist es wichtig zu erkennen, dass du noch viel über die einzigartigen Zugriffskontrollen von Snowflake lernen kannst, auch wenn du bereits Erfahrung mit Zugriffskontrollen für andere Plattformen hast.

Wissens-Check

Die folgenden Fragen basieren auf den in diesem Kapitel enthaltenen Informationen:

  1. Womit kannst du sicherstellen, dass eine Textzeile ein Kommentar ist und nicht als Code behandelt wird?

  2. Die String-Datentypen von Snowflake werden durch String-Konstanten unterstützt. Welche Begrenzungszeichen können verwendet werden, um Strings einzuschließen?

  3. Was sind die Vorteile der Verwendung externer Funktionen?

  4. Welche Dauer verwendet Snowflake standardmäßig, um zu bestimmen, wann lange laufende Abfragen abgebrochen werden? Kannst du diese Dauer ändern, und wenn ja, wie würdest du das tun?

  5. Welche Risiken birgt die Verwendung von Datentypen mit Gleitkommazahlen?

  6. Wie unterscheidet sich eine Fensterfunktion von einer Aggregatfunktion?

  7. Unterstützt Snowflake unstrukturierte Datentypen?

  8. Welche semistrukturierten Datentypen unterstützt Snowflake?

  9. Unterstützt der Datentyp TIMESTAMP von Snowflake lokale Zeitzonen und Sommerzeit? Erkläre das.

  10. Was sind abgeleitete Spalten und wie können sie in Snowflake verwendet werden?

  11. Welche drei Möglichkeiten gibt es, um in Snowflake auf unstrukturierte Datendateien zuzugreifen ?

  12. Nenne einige Beispiele für unstrukturierte Daten.

  13. Welche Art von Tabelle ist eine Verzeichnistabelle?

Die Antworten auf diese Fragen findest du in Anhang A.

Get Schneeflocke: Der endgültige Leitfaden now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial