Auf einen Blick: User Defined Functions (UDFs) in Snowflake ermöglichen es Nutzern, benutzerdefinierte SQL- und JavaScript-Funktionen für eine verbesserte Datenverarbeitung zu erstellen. Das Tutorial behandelt die Erstellung von skalaren und tabellischen Funktionen für verschiedene Anwendungen wie Wetterdatenanalyse.
- UDFs können einzelne (skalare) oder mehrere (tabellische) Werte zurückgeben.
- JavaScript wird für komplexe Berechnungen innerhalb von UDFs unterstützt.
In diesem Tutorial zeigen wir Ihnen, wie Sie benutzerdefinierte Funktionen (UDF) in Snowflake erstellen.
In Snowflake kannst du erstellen:
- Funktionen in SQL- und JavaScript-Sprachen
- Funktionen, die einen einzelnen Wert zurückgeben (skalar)
- Funktionen, die mehrere Werte zurückgeben (Tabelle)
Daten erstellen
Wenn Sie den untenstehenden Tutorials folgen möchten, nutzen Sie die Anweisungen dieses Tutorials zu statistischen Funktionen , um einige Daten in Snowflake zu laden. Die Daten sind 41 Tage stündliche Wetterdaten aus Paphos, Zypern.
Snowflake UDF SQL-Funktion
Wir beginnen mit einem SQL-Beispiel.
Der folgende Code nimmt die in der Haupttabelle beschriebenen Wetterbedingungen und wandelt diese in eine ganze Zahl um. Dies löst ein häufiges Problem beim maschinellen Lernen: die Umwandlung kategorialer Daten in eine ganze Zahl.
Beachte, dass die Funktion Parameter (dt varchar(20)) und einen Rückgabewert (int) besitzt. Der Rest ist einfach eine SQL-Select-Anweisung.
Der untenstehende Code verwendet die Aussagen iff() und regex(), um zu sehen, ob das Wort rain, cloud usw. in der Hauptspalte vorkommt. Er funktioniert, indem die Zahlen von 1 bis 9 addiert werden. Da nur eine dieser if-Aussagen wahr ist, ergibt sich die Summe zu einem der Werte 1 bis 9, was die Wetterbedingungen angibt.
create or replace function weathercategorical (dt varchar(20) )
returns int
as $$select (iff(main regexp '.*Clear.*',1,0) +
iff(main regexp '.*Clouds.*',2,0) +
iff(main regexp '.*Rain.*',3,0) +
iff(main regexp '.*Thunderstorm.*', 4,0) +
iff(main regexp '.*Mist.*', 5, 0) +
iff(main regexp '.*Fog.*', 6, 0) +
iff(main regexp '.*Squall.*',7,0) +
iff(main regexp '.*Tornado.*', 8, 0) +
iff(main regexp '.*Haze.*', 9, 0))
from weather as w where w.dt = dt$$
Datum und Uhrzeit sind im Epochenzeitformat angegeben. Die untenstehende SQL-Anweisung ruft die Funktion wetterkategorisch für das Datum 1. Januar 2000 auf und gibt den skalaren Wert 1 zurück, was klares Wetter bedeutet.
sselect weathercategorical (946684800) from weather where dt = 946684800
Schneeflocken-Tabellenfunktion
Hier zeigen wir, wie man mehr als einen Wert zurückgibt, den Snowflake als Tabelle bezeichnet.
Erstellen Sie diese zwei Tabellen:
CREATE TABLE customers
(
customernumber varchar(100) PRIMARY KEY,
customername varchar(50),
phonenumber varchar(50),
postalcode varchar(50),
locale varchar(10),
datecreated date,
email varchar(50)
);
CREATE TABLE orders
(
customernumber varchar(100) ,
ordernumber varchar(100) PRIMARY KEY,
comments varchar(200),
orderdate date,
ordertype varchar(10),
shipdate date,
discount float,
quantity int,
productnumber varchar(50)
);
Dann kopieren und fügen Sie diese Daten ein.
Skalar vs. Tabellenfunktion
Jetzt erstellen wir eine Funktion, um den Kundennamen und die E-Mail-Adresse mit einem Datensatz aus der Bestelltabelle nachzuschlagen. Bestellungen enthalten keine Kundeninformationen, also ist es wie ein Join. Aber da es eine Funktion ist, ist es viel weniger wortreich und praktischer, als jedes Mal einen Join zu erstellen, wenn man Kundeninformationen mit der Bestellung benötigt.
create or replace function getcustomer (customernumber number )
returns table (customername varchar, email varchar)
as 'select customername, email from customers
where customers.customernumber = customernumber';
Gegeben die Kundennummer aus der Bestelltabelle, erhält diese Anweisung:
- Der Name des Kunden
- Bestellnummer
select getcustomer (948 ), ordernumber from orders where customernumber = 948;
JavaScript UDFs
Du kannst JavaScript in einer benutzerdefinierten Funktion verwenden. Gib einfach die Sprache JavaScript ein.
Berechnen wir n Fakultialfaktor (n!), da Snowflake diese mathematische Funktion nicht besitzt. n Fakultät n! ist n * (n-1) * (n-2) * .. ** (n - (n + 1))). Zum Beispiel: 3!=3*2*1=6.
Beachten Sie unten, dass wir Variante als Datentyp verwenden, da JavaScript keine ganzzahligen Typen hat.
CREATE OR REPLACE FUNCTION factorial(n variant)
RETURNS variant
LANGUAGE JAVASCRIPT
AS '
var f=n;
for (i=n-1; i>0; i--) {
f=f*i
}
return f';
Führe es aus, und es berechnet den Wert 6.
select factorial(3)
Beachte, dass 33 die größte Zahl ist, die die Funktion bewältigen kann. 33! = 8683317618811886495518194401280000000
Zusätzliche Ressourcen
Für weitere Tutorials wie dieses entdecken Sie diese Ressourcen:
Schneeflocken-Leitfaden
- Snowflake 101: Einführung in die Snowflake Data Cloud
- Wie man Amazon S3-Daten in Snowflake importiert
- Wie man JSON-Daten in Snowflake abfragt
- CSV-Dateien von S3 in Snowflake laden
- Snowflake: Verwendung von Analytik und statistischen Funktionen
- Snowflake-Fensterfunktionen: Partitionieren nach und ordnen nach
- Schneeflocken-Verzögerungsfunktion und gleitende Durchschnitte
- Snowflake SQL Aggregate Functions & Table Joins
- Benutzerdefinierte Funktionen (UDFs) in Snowflake
- Schneeflockenströme erstellen und nutzen
- Verwendung von gespeicherten Verfahren in Snowflake
- Schneeflocke vs. Redshift: Was ist der Unterschied und wie wählt man?
Diese Beiträge sind meine eigenen und spiegeln nicht unbedingt die Position, Strategien oder Meinung von BMC wider.