JavaScript UDFs

Unter diesem Thema werden allgemeine JavaScript UDF-Anforderungen (für benutzerdefinierte Funktionen) und Nutzungsdetails sowie spezifische Einschränkungen für UDFs behandelt.

Unter diesem Thema:

Einführungsbeispiel

Das folgende Beispiel zeigt, wie ein Array mit einer JavaScript-UDF sortiert wird. In diesem Beispiel wird die Tatsache genutzt, dass JavaScript eine integrierte sort()-Methode für Arrays enthält.

Beachten Sie, dass der JavaScript-Code die Namen der Eingabeparameter komplett als Großbuchstaben angeben muss, auch wenn die Namen im SQL-Code keine Großbuchstaben sind.

-- Create the UDF.
CREATE OR REPLACE FUNCTION array_sort(a array)
  RETURNS array
  LANGUAGE JAVASCRIPT
AS
$$
  return A.sort();
$$
;

-- Call the UDF with a small array.
SELECT ARRAY_SORT(PARSE_JSON('[2,4,5,3,1]'));

Die Ausgabe würde ungefähr so aussehen:

[1, 2, 3, 4, 5]

JavaScript-Datentypen

SQL und JavaScript-UDFs stellen ähnliche, jedoch unterschiedliche Datentypen bereit, basierend auf der Unterstützung ihres nativen Datentyps. Objekte innerhalb von Snowflake und JavaScript werden mithilfe der folgenden Zuordnungen übertragen.

Ganze Zahlen und Duplikate

JavaScript hat keinen Ganzzahltyp; alle Zahlen werden als Duplikate dargestellt. JavaScript-UDFs akzeptieren oder geben keine Ganzzahlwerte zurück, außer durch Typkonvertierung (d. h. Sie können eine Ganzzahl an eine JavaScript-UDF übergeben, die ein Duplikat akzeptiert).

Sowohl Snowflake-SQL als auch JavaScript unterstützen doppelte Werte. Diese Werte werden unverändert übernommen.

Zeichenfolgen

Sowohl Snowflake-SQL als auch JavaScript unterstützen Zeichenfolgenwerte. Diese Werte werden unverändert übernommen.

Binärwerte

Alle Binärwerte werden in JavaScript Uint8Array-Objekte konvertiert. Auf diese typisierten Arrays kann auf die gleiche Weise wie auf reguläre JavaScript-Arrays zugegriffen werden, sie sind jedoch effizienter und unterstützen zusätzliche Methoden.

Wenn ein JavaScript-UDF ein Uint8Array-Objekt zurückgibt, wird es in einen Snowflake SQL-Binärwert konvertiert.

Datumsangaben:

Alle Zeitstempel- und Datumstypen werden in JavaScript Date()-Objekte konvertiert. Der JavaScript-Datumstyp entspricht TIMESTAMP_LTZ(3) in Snowflake-SQL.

Beachten Sie die folgenden Hinweise für JavaScript-UDFs, die ein Datum oder eine Uhrzeit akzeptieren:

  • Alle Detailangaben über Millisekunden hinaus gehen verloren.

  • Ein aus SQL TIMESTAMP_NTZ generiertes JavaScript Date fungiert nicht mehr als „Wanduhr“-Zeit; es wird von der Sommerzeit beeinflusst. Dies entspricht dem Verhalten beim Konvertieren von TIMESTAMP_NTZ in TIMESTAMP_LTZ.

  • Ein aus SQL TIMESTAMP_TZ generiertes JavaScript Date verliert Zeitzoneninformationen, stellt jedoch den gleichen Zeitpunkt wie die Eingabe dar (ähnlich wie beim Konvertieren von TIMESTAMP_TZ in TIMESTAMP_LTZ).

  • SQL DATE wird in JavaScript Date umgewandelt und stellt Mitternacht des aktuellen Tages in der lokalen Zeitzone dar.

Beachten Sie außerdem die folgenden Hinweise für JavaScript-UDFs, die die Typen DATE und TIMESTAMP zurückgeben:

  • JavaScript Date-Objekte werden in den Ergebnisdatentyp von UDF konvertiert, wobei sie die gleiche Konvertierungssemantik wie Konvertierungen von TIMESTAMP_LTZ(3) in den Rückgabedatentyp einhalten.

  • JavaScript Date-Objekte, die in VARIANT-Objekten verschachtelt sind, haben immer den Typ TIMESTAMP_LTZ(3).

Variant, Objekte und Arrays

JavaScript-UDFs ermöglichen eine einfache und intuitive Bearbeitung von Variant- und JSON-Daten. An eine UDF übergebene Variant-Objekte werden in native JavaScript-Typen und -Werte umgewandelt. Alle zuvor aufgelisteten Werte werden in die entsprechenden JavaScript-Typen übersetzt. Variant-Objekt und -Arrays werden in JavaScript-Objekte und -Arrays konvertiert. In ähnlicher Weise werden alle von der UDF zurückgegebenen Werte in die entsprechenden Variant-Werte umgewandelt. Beachten Sie, dass von der UDF zurückgegebene Objekte und Arrays Größen- und Tiefenbeschränkungen unterliegen.

-- flatten all arrays and values of objects into a single array
-- order of objects may be lost
CREATE OR REPLACE FUNCTION flatten_complete(v variant)
  RETURNS variant
  LANGUAGE JAVASCRIPT
  AS '
  // Define a function flatten(), which always returns an array.
  function flatten(input) {
    var returnArray = [];
    if (Array.isArray(input)) {
      var arrayLength = input.length;
      for (var i = 0; i < arrayLength; i++) {
        returnArray.push.apply(returnArray, flatten(input[i]));
      }
    } else if (typeof input === "object") {
      for (var key in input) {
        if (input.hasOwnProperty(key)) {
          returnArray.push.apply(returnArray, flatten(input[key]));
        }
      }
    } else {
      returnArray.push(input);
    }
    return returnArray;
  }

  // Now call the function flatten() that we defined earlier.
  return flatten(V);
  ';

select value from table(flatten(flatten_complete(parse_json(
'[
  {"key1" : [1, 2], "key2" : ["string1", "string2"]},
  {"key3" : [{"inner key 1" : 10, "inner key 2" : 11}, 12]}
  ]'))));

-----------+
   VALUE   |
-----------+
 1         |
 2         |
 "string1" |
 "string2" |
 10        |
 11        |
 12        |
-----------+

JavaScript-Argumente und zurückgegebene Werte

Argumente können innerhalb von JavaScript direkt über den Namen referenziert werden. Beachten Sie, dass ein Bezeichner ohne Anführungszeichen mit dem Variablennamen in Großbuchstaben referenziert werden muss. Da Argumente und die UDF von JavaScript aus referenziert werden, müssen sie gültige JavaScript-Bezeichner sein. UDF- und Argumentnamen müssen insbesondere mit einem Buchstaben oder $ beginnen, während nachfolgende Zeichen alphanumerisch, $ oder _ sein können. Darüber hinaus können Namen keine JavaScript-reservierten Wörter sein.

Die folgenden drei Beispiele veranschaulichen UDFs, die Argumente verwenden, die über den Namen referenziert werden:

-- Valid UDF.  'N' must be capitalized.
CREATE OR REPLACE FUNCTION add5(n double)
  RETURNS double
  LANGUAGE JAVASCRIPT
  AS 'return N + 5;';

select add5(0.0);

-- Valid UDF. Lowercase argument is double-quoted.
CREATE OR REPLACE FUNCTION add5_quoted("n" double)
  RETURNS double
  LANGUAGE JAVASCRIPT
  AS 'return n + 5;';

select add5_quoted(0.0);

-- Invalid UDF. Error returned at runtime because JavaScript identifier 'n' cannot be resolved.
CREATE OR REPLACE FUNCTION add5_lowercase(n double)
  RETURNS double
  LANGUAGE JAVASCRIPT
  AS 'return n + 5;';

select add5_lowercase(0.0);

NULL- und undefinierte Werte

Achten Sie bei der Verwendung von JavaScript-UDFs besonders auf Zeilen und Variablen, die möglicherweise NULL-Werte enthalten: Snowflake enthält zwei unterschiedliche NULL-Werte (SQL-NULL und Variant-JSON-null), während JavaScript zusätzlich zu null den Wert undefined enthält.

SQL-NULL-Argumente für eine JavaScript-UDF werden in den JavaScript-undefined-Wert konvertiert. Ebenso werden zurückgegebene JavaScript-undefined-Werte zurück in SQL-NULL konvertiert. Dies gilt für alle Datentypen einschließlich Variant. Bei Nicht-Variant-Typen führt ein zurückgegebener JavaScript-null-Wert ebenfalls zu einem SQL-NULL-Wert.

Argumente und zurückgegebene Werte des Variant-Typs unterscheiden in JavaScript zwischen undefined- und null-Werten. SQL-NULL wird weiterhin in JavaScript-undefined (und JavaScript-undefined zurück in SQL-NULL) konvertiert. JSON-Variant-null wird in JavaScript-null (und JavaScript-null zurück in JSON-Variant-null) konvertiert. Ein undefined-Wert, der in einem JavaScript-Objekt (als Wert) oder Array eingebettet ist, führt dazu, dass das Element weggelassen wird.

Erstellen Sie eine Tabelle mit einer Zeichenfolge und einem NULL-Wert:

create or replace table strings (s string);
insert into strings values (null), ('non-null string');

Erstellen Sie eine Funktion, die eine Zeichenfolge in einen NULL-Wert und eine NULL-Wert in eine Zeichenfolge konvertiert:

CREATE OR REPLACE FUNCTION string_reverse_nulls(s string)
    RETURNS string
    LANGUAGE JAVASCRIPT
    AS '
    if (S === undefined) {
        return "string was null";
    } else
    {
        return undefined;
    }
    ';

Rufen Sie die Funktion auf:

select string_reverse_nulls(s) 
    from strings
    order by 1;
+-------------------------+
| STRING_REVERSE_NULLS(S) |
|-------------------------|
| string was null         |
| NULL                    |
+-------------------------+

Erstellen Sie eine Funktion, die den Unterschied zwischen der Übergabe eines SQL-NULL-Wertes und eines JSON-Variant-null-Wertes zeigt:

CREATE OR REPLACE FUNCTION variant_nulls(V VARIANT)
      RETURNS VARCHAR
      LANGUAGE JAVASCRIPT
      AS '
      if (V === undefined) {
        return "input was SQL null";
      } else if (V === null) {
        return "input was variant null";
      } else {
        return V;
      }
      ';
select null, 
       variant_nulls(cast(null as variant)),
       variant_nulls(PARSE_JSON('null'))
       ;
+------+--------------------------------------+-----------------------------------+
| NULL | VARIANT_NULLS(CAST(NULL AS VARIANT)) | VARIANT_NULLS(PARSE_JSON('NULL')) |
|------+--------------------------------------+-----------------------------------|
| NULL | input was SQL null                   | input was variant null            |
+------+--------------------------------------+-----------------------------------+

Erstellen Sie eine Funktion, die den Unterschied zwischen der Rückgabe eines undefined-Werts, eines null-Werts und eines Variant-Werts, der einen undefined-Wert und einen null-Wert enthält, anzeigt (beachten Sie, dass der undefined-Wert aus dem zurückgegebenen Variant-Wert entfernt wird):

CREATE OR REPLACE FUNCTION variant_nulls(V VARIANT)
      RETURNS variant
      LANGUAGE JAVASCRIPT
      AS $$
      if (V == 'return undefined') {
        return undefined;
      } else if (V == 'return null') {
        return null;
      } else if (V == 3) {
        return {
            key1 : undefined,
            key2 : null
            };
      } else {
        return V;
      }
      $$;
select variant_nulls('return undefined'::VARIANT) AS "RETURNED UNDEFINED",
       variant_nulls('return null'::VARIANT) AS "RETURNED NULL",
       variant_nulls(3) AS "RETURNED VARIANT WITH UNDEFINED AND NULL; NOTE THAT UNDEFINED WAS REMOVED";
+--------------------+---------------+---------------------------------------------------------------------------+
| RETURNED UNDEFINED | RETURNED NULL | RETURNED VARIANT WITH UNDEFINED AND NULL; NOTE THAT UNDEFINED WAS REMOVED |
|--------------------+---------------+---------------------------------------------------------------------------|
| NULL               | null          | {                                                                         |
|                    |               |   "key2": null                                                            |
|                    |               | }                                                                         |
+--------------------+---------------+---------------------------------------------------------------------------+

Typkonvertierung in JavaScript

JavaScript konvertiert Werte implizit zwischen vielen verschiedenen Typen. Wenn ein Wert zurückgegeben wird, wird der Wert zuerst in den angeforderten Rückgabetyp konvertiert, bevor er in einen SQL-Wert übersetzt wird. Wenn beispielsweise eine Zahl zurückgegeben wird, die UDF jedoch als zurückgegebene Zeichenfolge deklariert wird, wird diese Zahl in eine Zeichenfolge innerhalb von JavaScript umgewandelt. Beachten Sie, dass JavaScript-Programmierfehler, z. B. das Zurückgeben des falschen Typs, durch dieses Verhalten verborgen werden können. Wenn beim Konvertieren des Werttyps ein Fehler ausgelöst wird, wird ein Fehler zurückgegeben.

JavaScript-Nummernbereich

Der Bereich für Zahlen mit intakter Genauigkeit reicht von

-(2^53 -1)

bis

(2^53 -1)

Der Bereich der gültigen Werte in den Snowflake-Datentypen NUMBER(p, s) und DOUBLE ist größer. Wenn Sie einen Wert aus Snowflake abrufen und in einer numerischen JavaScript-Variablen speichern, kann dies zu Genauigkeitsverlusten führen. Beispiel:

CREATE OR REPLACE FUNCTION num_test(a double)
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
  return A;
$$
;
select hash(1) AS a, 
       num_test(hash(1)) AS b, 
       a - b;
+----------------------+----------------------+------------+
|                    A | B                    |      A - B |
|----------------------+----------------------+------------|
| -4730168494964875235 | -4730168494964875000 | -235.00000 |
+----------------------+----------------------+------------+

Die ersten beiden Spalten sollten übereinstimmen und die dritte sollte 0,0 enthalten.

Das Problem betrifft benutzerdefinierte JavaScript-Funktionen (UDFs) und gespeicherte Prozeduren.

Wenn das Problem in gespeicherten Prozeduren bei Verwendung von getColumnValue() auftritt, können Sie das Problem möglicherweise vermeiden, indem Sie einen Wert als Zeichenfolge abrufen, z. B. mit:

getColumnValueAsString()

Sie können dann die Zeichenfolge aus der gespeicherten Prozedur zurückgeben und die Zeichenfolge in einen numerischen Datentyp in SQL umwandeln.

JavaScript-Fehler

Alle Fehler, die während der Ausführung von JavaScript aufgetreten sind, werden dem Benutzer als SQL-Fehler angezeigt. Dazu gehören Fehler beim Parsen, Laufzeitfehler und nicht erfasste Fehler, die innerhalb der UDF ausgegeben werden. Wenn der Fehler ein Stacktrace enthält, wird es zusammen mit der Fehlermeldung gedruckt. Es ist zulässig, einen Fehler auszulösen, ohne ihn abzufangen, um die Abfrage zu beenden und einen SQL-Fehler zu erzeugen.

Beim Debuggen kann es hilfreich sein, Argumentwerte zusammen mit der Fehlermeldung zu drucken, sodass sie im Text der SQL-Fehlermeldung angezeigt werden. Bei deterministischen UDFs liefert dies die erforderlichen Daten, um Fehler in einem lokalen JavaScript-Modul zu reproduzieren. Ein allgemeines Muster besteht darin, einen gesamten JavaScript-UDF-Textkörper in einen „try-catch“-Block zu setzen, der angefangenen Fehlermeldung Argumentwerte anzufügen und einen Fehler mit der erweiterten Meldung auszulösen. Sie sollten in Betracht ziehen, solche Mechanismen zu entfernen, bevor Sie UDFs in einer Produktionsumgebung bereitstellen, denn bei Aufzeichnung von Werten in Fehlermeldungen kann es zur unbeabsichtigten Offenlegung sensibler Daten kommen.

Die Funktion kann vordefinierte Ausnahmen oder benutzerdefinierte Ausnahmen auslösen und abfangen. Ein einfaches Beispiel für das Auslösen einer benutzerdefinierten Ausnahme finden Sie hier.

JavaScript-UDF-Einschränkungen

Um die Stabilität in der Snowflake-Umgebung sicherzustellen, sind folgende Einschränkungen auf JavaScript-UDFs definiert. Diese Einschränkungen werden nicht zum Zeitpunkt der UDF-Erstellung aufgerufen, sondern zur Laufzeit beim Aufruf der UDF.

Speicher

JavaScript-UDFs schlagen fehl, wenn sie zu viel Speicher verbrauchen. Das jeweilige Limit kann sich ändern. Wenn zu viel Speicher verwendet wird, wird ein Fehler zurückgegeben.

Dauer

JavaScript-UDFs, die zu lange dauern, werden beendet und ein Fehler wird an den Benutzer zurückgegeben. Darüber hinaus führen JavaScript-UDFs, die in Endlosschleifen eintreten, zu Fehlern.

Stapeltiefe

Übermäßige Stapeltiefe aufgrund von Rekursion führt zu einem Fehler.

Globaler Status

Snowflake erhält normalerweise den globalen JavaScript-Status zwischen Iterationen einer UDF. Sie sollten sich jedoch nicht darauf verlassen, dass vorherige Änderungen des globalen Status zwischen Funktionsaufrufen verfügbar sind. Außerdem sollten Sie nicht davon ausgehen, dass alle Zeilen in derselben JavaScript-Umgebung ausgeführt werden.

In der Praxis ist der globale Status relevant bei:

  • Komplexer/aufwendiger Initialisierungslogik. Standardmäßig wird der bereitgestellte UDF-Code für jede verarbeitete Zeile ausgewertet. Wenn dieser Code eine komplexe Logik enthält, kann dies ineffizient sein.

  • Funktionen, die Code enthalten, der nicht idempotent ist. Ein typisches Muster wäre:

    Date.prototype._originalToString = Date.prototype.toString;
    Date.prototype.toString = function() {
      /* ... SOME CUSTOM CODE ... */
      this._originalToString()
      }
    

    Bei der ersten Ausführung dieses Codes ändert sich der Status von toString und _originalToString. Diese Änderungen werden im globalen Status beibehalten. Wenn der Code zum zweiten Mal ausgeführt wird, werden die Werte erneut so geändert, dass eine Rekursion entsteht. Beim zweiten Aufruf von toString wird der Code unendlich oft rekursiert (bis der Stapelspeicherplatz ausgeht).

In diesen Situationen wird empfohlen, zu gewährleisten, dass der relevante Code nur einmal ausgewertet wird, wobei die Semantik der globalen Variablen von JavaScript verwendet wird. Beispiel:

var setup = function() {
/* SETUP LOGIC */
};

if (typeof(setup_done) === "undefined") {
  setup();
  setup_done = true;  // setting global variable to true
}

Beachten Sie, dass dieser Mechanismus nur für die Zwischenspeicherung der Auswirkungen der Codeauswertung geeignet ist. Es kann nicht garantiert werden, dass der globale Kontext nach einer Initialisierung für alle Zeilen erhalten bleibt; daher sollte keine Geschäftslogik davon abhängen.

Bibliotheken

JavaScript-UDFs unterstützen den Zugriff auf die JavaScript-Standardbibliothek. Beachten Sie, dass dadurch viele Objekte und Methoden ausgeschlossen werden, die normalerweise von Browsern bereitgestellt werden. Es gibt keinen Mechanismus zum Importieren, Einschließen oder Aufrufen zusätzlicher Bibliotheken. Der gesamte erforderliche Code sollte in der UDF eingebettet sein.

Darüber hinaus ist die integrierte JavaScript-eval()-Funktion deaktiviert.

Zurückgegebene Variant-Größe und -Tiefe

Zurückgegebene Variant-Objekte unterliegen Größen- und Verschachtelungstiefenbeschränkungen:

Größe

Derzeit auf mehrere Megabyte beschränkt, Änderungen vorbehalten.

Tiefe

Derzeit auf eine Schachtelungstiefe von 1.000 begrenzt, Änderungen vorbehalten.

Wenn ein Objekt zu groß oder zu tief ist, wird beim Aufruf der UDF ein Fehler zurückgegeben.

JavaScript-UDF-Sicherheit

JavaScript-UDFs sind so konzipiert, dass sie mehrere Abfrage- und Datenisolationsebenen bieten und dadurch sicher und geschützt sind:

  • Server innerhalb des virtuellen Warehouse, auf denen eine JavaScript-UDF ausgeführt wird, können nur von Ihrem Konto aus aufgerufen werden (d. h. Warehouses teilen sich keine Ressourcen mit anderen Snowflake-Konten).

  • Tabellendaten werden innerhalb des virtuellen Warehouse verschlüsselt, um den Zugriff durch Unbefugte zu verhindern.

  • Snowflake-JavaScript-UDFs werden in einem eingeschränkten Modul ausgeführt, wodurch Systemaufrufe aus dem JavaScript-Kontext (z. B. kein Netzwerk- und Festplattenzugriff) verhindert werden und die für das Modul verfügbare Systemressourcen, insbesondere der Arbeitsspeicher, eingeschränkt werden.

Aus diesem Grund können JavaScript-UDFs nur auf die Daten zugreifen, die zur Ausführung der definierten Funktion erforderlich sind. Sie können den Status des zugrunde liegenden Systems nur beeinflussen, wenn sie eine angemessene Menge an Speicher und Prozessorzeit verbrauchen.

Beispiele

Dieser Abschnitt enthält zusätzliche Beispiele.

Dieses Beispiel zeigt, dass eine JavaScript UDF sich selbst aufrufen kann (d. h. sie kann eine Rekursion verwenden):

Erstellen Sie eine rekursive UDF:

CREATE OR REPLACE FUNCTION RECURSION_TEST (STR VARCHAR)
  RETURNS VARCHAR
  LANGUAGE JAVASCRIPT
  AS $$
  return (STR.length <= 1 ? STR : STR.substring(0,1) + '_' + RECURSION_TEST(STR.substring(1)));
  $$
  ;

Rufen Sie die rekursive UDF auf:

SELECT RECURSION_TEST('ABC');
+-----------------------+
| RECURSION_TEST('ABC') |
|-----------------------|
| A_B_C                 |
+-----------------------+

Dieses Beispiel zeigt eine JavaScript-UDF, die eine benutzerdefinierte Ausnahme auslöst:

Erstellen Sie die Funktion:

CREATE FUNCTION validate_ID(ID FLOAT)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS $$
    try {
        if (ID < 0) {
            throw "ID cannot be negative!";
        } else {
            return "ID validated.";
        }
    } catch (err) {
        return "Error: " + err;
    }
$$;

Erstellen Sie eine Tabelle mit gültigen und ungültigen Werten:

CREATE TABLE employees (ID INTEGER);
INSERT INTO employees (ID) VALUES 
    (1),
    (-1);

Rufen Sie die Funktion auf:

SELECT ID, validate_ID(ID) FROM employees ORDER BY ID;
+----+-------------------------------+
| ID | VALIDATE_ID(ID)               |
|----+-------------------------------|
| -1 | Error: ID cannot be negative! |
|  1 | ID validated.                 |
+----+-------------------------------+

Problembehandlung

Fehlermeldung: Variable is not defined

Ursache

In einigen Fällen kann diese Fehlermeldung durch ein kaufmännisches Und-Zeichen (&) innerhalb eines CREATE FUNCTION-Befehls verursacht werden, da das kaufmännische Und-Zeichen das Variablensubstitutionszeichen ist. Beispiel:

create function mask_bits(...)
    ...
    as
    $$
    var masked = (x & y);
    ...
    $$;

Der Fehler tritt zu dem Zeitpunkt auf, zu dem die Funktion erstellt wird, nicht zu dem Zeitpunkt, zu dem die Funktion aufgerufen wird.

Lösung

Wenn Sie die Variablensubstitution nicht verwenden möchten, können Sie sie explizit deaktivieren. In SnowSQL können Sie beispielsweise die Variablensubstitution deaktivieren, indem Sie den folgenden Befehl ausführen:

!set variable_substitution=false;

Weitere Informationen zur Variablenersetzung finden Sie unter Verwenden von Variablen.