Tabellarische JavaScript-UDFs (UDTFs)¶

processRow()¶

Diese Callback-Funktion wird in der Eingabebeziehung einmal für jede Zeile aufgerufen. Die Argumente für processRow() werden im row-Objekt übergeben. Bei allen in der CREATE FUNCTION-Anweisung definierten Argumenten, die zum Erstellen der UDTF verwendet wurden, gibt es ein row-Objekt mit einer Eigenschaft, die denselben Namen in Großbuchstaben hat. Der Wert dieser Eigenschaft ist der Wert des Arguments für die aktuelle Zeile. (Der Wert wird in einen JavaScript-Wert umgewandelt.)

Das Argument rowWriter wird vom benutzerdefinierten Code zum Erstellen von Ausgabezeilen verwendet. Das Objekt rowWriter definiert eine einzelne Funktion: writeRow(). Der writeRow()-Funktion wird ein Zeilenobjekt als Argument übergeben. Hierbei handelt es sich um eine einzelne Zeile in der Ausgabetabelle, die durch ein JavaScript-Objekt repräsentiert wird. Für jede Spalte, die in der RETURNS-Klausel des CREATE FUNCTION-Befehls definiert ist, kann eine entsprechende Eigenschaft für das Zeilenobjekt definiert werden. Der Wert dieser Eigenschaft für das Zeilenobjekt wird zum Wert für die entsprechende Spalte in der Ausgabebeziehung. Ausgabespalten ohne entsprechende Eigenschaft im Zeilenobjekt haben in der Ergebnistabelle den Wert NULL.

finalize()¶

Diese Callback-Funktion finalize() wird einmal aufgerufen, nachdem alle Zeilen an processRow() übergeben wurden. (Wenn die Daten in Partitionen gruppiert sind, dann wird finalize() einmal für jede Partition aufgerufen, nachdem alle Zeilen in dieser Partition an processRow() übergeben wurden.)

Diese Callback-Funktion kann verwendet werden, um einen beliebigen Status auszugeben, der möglicherweise in processRow() mit demselben rowWriter-Objekt aggregiert wurde, das an processRow() übergeben wird.

initialize()¶

Diese Callback-Funktion wird einmal für jede Partition aufgerufen, bevor processRow() aufgerufen wird.

Verwenden Sie initialize(), um während der Ergebnisberechnung einen beliebigen Status einzustellen.

Der Parameter argumentInfo der initialize()-Funktion enthält Metadaten zu den Argumenten der benutzerdefinierten Funktion. Wenn zum Beispiel die UDF wie folgt definiert ist:

CREATE FUNCTION f(argument_1 INTEGER, argument_2 VARCHAR) ...

Dann enthält argumentInfo Informationen zu argument_1 und argument_2.

argumentInfo hat eine Eigenschaft für jedes dieser Argumente. Jede Eigenschaft ist ein Objekt mit den folgenden Werten:

• type: String. Der Typ dieses Arguments.

• isConst: Boolean. Bei „true“ ist der Wert dieses Arguments konstant (d. h. für jede Zeile der gleiche).

• constValue: Wenn isConst (wie oben definiert) „true“ ist, enthält dieser Eintrag den konstanten Wert des Arguments. Andernfalls lautet dieses Feld undefined.

Die initialize()-Funktion kann keine Ausgabezeilen erzeugen.

Allgemeine Nutzungshinweise für Callback-Funktionen¶

• Alle drei Callback-Funktionen benötigen ein context-Objekt. Dies ist für die zukünftige Verwendung reserviert und derzeit leer.

  Vorsicht

  Das Ändern des context-Objekts kann zu undefiniertem Verhalten führen.

• Zusätzliche Funktionen und Eigenschaften können bei Bedarf für das Objekt definiert werden, das in der UDTF verwendet werden soll.

• Die Argumente für die Callback-Funktionen sind positionell und können beliebig benannt werden. Für die Zwecke dieses Themas werden die obigen Namen jedoch für die verbleibende Diskussion und die Beispiele verwendet.

Ergebnisspalten¶

Wenn eine Tabelle mit einer Tabellenfunktion verbunden wird, wie in den obigen Partitionierungsbeispielen, kann das Resultset je nach Auswahl Folgendes enthalten:

• Die Spalten, die in der RETURNS-Klausel des CREATE FUNCTION-Befehls definiert sind.

• Die Spalten der Tabelle, einschließlich der Spalten, die zur Partitionierung der Daten verwendet werden, und anderer Spalten, unabhängig davon, ob sie als Eingabeparameter für UDTF verwendet werden oder nicht.

Beachten Sie, dass sich die Zeilen, die im processRow-Callback erzeugt werden, und die Zeilen, die von finalize erzeugt werden, in den folgenden Punkten unterscheiden:

• Wenn eine Zeile in processRow erzeugt wird, kann Snowflake sie einer Eingabezeile zuordnen, nämlich derjenigen, die der Funktion als row-Argument übergeben wurde. Beachten Sie, dass die Eingabeattribute mit jeder Zeile der Ausgabe korreliert sind, wenn ein bestimmter processRow-Aufruf mehr als eine Zeile erzeugt.

  Bei Zeilen, die in processRow erstellt werden, können alle Eingabespalten mit der Ausgabebeziehung verbunden werden.

Im finalize-Callback kann Snowflake keine Korrelation zu einer einzelnen Zeile herstellen, da es keine aktuelle Zeile gibt, mit der korreliert werden kann.

• Bei Zeilen, die mit dem finalize-Callback erzeugt werden, sind nur die in der PARTITION BY-Klausel verwendeten Spalten verfügbar (da diese für alle Zeilen in der aktuellen Partition gleich sind). Alle anderen Attribute sind NULL. Wenn keine PARTITION BY-Klausel angegeben ist, sind dieser Attribute NULL.

Keine Partitionierung¶

Dieses einfache Beispiel zeigt, wie eine UDTF aufgerufen wird. In diesem Beispiel werden Literalwerte übergeben. Die UDTF gibt lediglich die Parameter in umgekehrter Reihenfolge zurück, in der sie übergeben wurden. In diesem Beispiel wird keine Partitionierung verwendet.

SELECT * FROM TABLE(js_udtf(10.0::FLOAT, 20.0::FLOAT));
+----+----+
|  Y |  X |
|----+----|
| 20 | 10 |
+----+----+

In diesem Beispiel wird eine UDTF aufgerufen, und es werden Werte aus einer anderen Tabelle übergeben. In diesem Beispiel wird die UDTF namens js_udtf für jede Zeile in der Tabelle namens tab1 einmal aufgerufen. Bei jedem Aufruf der Funktion werden Werte aus den Spalten c1 und c2 der aktuellen Zeile übergeben. Wie oben wird die UDTF ohne eine PARTITION BY-Klausel aufgerufen.

SELECT * FROM tab1, TABLE(js_udtf(tab1.c1, tab1.c2)) ;

Wenn keine Partitionierung verwendet wird, partitioniert das Snowflake-Ausführungsmodul die Eingabe selbst anhand mehrerer Faktoren, z. B. der Größe des Warehouse, das die Funktion verarbeitet, und der Kardinalität der Eingabebeziehung. In diesem Modus kann der Benutzercode keine Annahmen über Partitionen treffen. Dies ist am nützlichsten, wenn die Funktion nur Zeilen isoliert betrachten muss, um ihre Ausgabe zu erzeugen, und kein Zustand über Zeilen hinweg aggregiert wird.

Explizite Partitionierung¶

JavaScript-UDTFs können auch über eine Partition aufgerufen werden. Beispiel:

SELECT * FROM tab1, TABLE(js_udtf(tab1.c1, tab1.c2) OVER (PARTITION BY tab1.c3 ORDER BY tab1.c1));

Explizite Partitionierung mit einer leeren OVER-Klausel¶

SELECT * FROM tab1, TABLE(js_udtf(tab1.c1, tab1.c2) OVER ());

Eine leere OVER-Klausel bedeutet, dass jede Zeile zu derselben Partition gehört (d. h. die gesamte Eingabebeziehung ist eine Partition).

Grundlegende Hello World-Beispiele¶

Die folgende JavaScript-UDTF benötigt keine Parameter und gibt immer die gleichen Werte zurück. Sie wird hauptsächlich zu Illustrationszwecken bereitgestellt:

CREATE OR REPLACE FUNCTION HelloWorld0()
    RETURNS TABLE (OUTPUT_COL VARCHAR)
    LANGUAGE JAVASCRIPT
    AS '{
        processRow: function f(row, rowWriter, context){
           rowWriter.writeRow({OUTPUT_COL: "Hello"});
           rowWriter.writeRow({OUTPUT_COL: "World"});
           }
        }';

SELECT output_col FROM TABLE(HelloWorld0());

Ausgabe:

+------------+
| OUTPUT_COL |
+============+
| Hello      |
+------------+
| World      |
+------------+

Die folgende JavaScript-UDTF dient ebenfalls zur Veranschaulichung, verwendet jedoch einen Eingabeparameter. Beachten Sie, dass JavaScript die Groß-/Kleinschreibung berücksichtigt, aber SQL erzwingt bei Bezeichnern Großbuchstaben. Wenn der JavaScript-Code auf einen SQL-Parameternamen verweist, muss der JavaScript-Code Großbuchstaben verwenden.

Beachten Sie auch, dass auf Funktionsparameter über den Parameter row in der Funktion get_params() zugegriffen wird:

CREATE OR REPLACE FUNCTION HelloHuman(First_Name VARCHAR, Last_Name VARCHAR)
    RETURNS TABLE (V VARCHAR)
    LANGUAGE JAVASCRIPT
    AS '{
        processRow: function get_params(row, rowWriter, context){
           rowWriter.writeRow({V: "Hello"});
           rowWriter.writeRow({V: row.FIRST_NAME});  // Note the capitalization and the use of "row."!
           rowWriter.writeRow({V: row.LAST_NAME});   // Note the capitalization and the use of "row."!
           }
        }';

SELECT V AS Greeting FROM TABLE(HelloHuman('James', 'Kirk'));

Ausgabe:

+------------+
|  GREETING  |
+============+
| Hello      |
+------------+
| James      |
+------------+
| Kirk       |
+------------+

Grundlegende Beispiele zur Veranschaulichung der Callback-Funktionen¶

Die folgende JavaScript-UDTF zeigt alle API-Callback-Funktionen und verschiedene Ausgabespalten. Es werden einfach alle Zeilen in der vorliegenden Form zurückgegeben und die Anzahl der Zeichen angegeben, die in jeder Partition angezeigt werden. Außerdem wird veranschaulicht, wie der Status über eine Partition mit der Referenz THIS gemeinsam genutzt wird. Beachten Sie, dass das Beispiel einen initialize()-Callback verwendet, um den Zähler mit null zu initialisieren. Dies ist erforderlich, da eine bestimmte Funktionsinstanz zur Verarbeitung mehrerer Partitionen verwendet werden kann:

-- set up for the sample
CREATE TABLE parts (p FLOAT, s STRING);

INSERT INTO parts VALUES (1, 'michael'), (1, 'kelly'), (1, 'brian');
INSERT INTO parts VALUES (2, 'clara'), (2, 'maggie'), (2, 'reagan');

-- creation of the UDTF
CREATE OR REPLACE FUNCTION "CHAR_SUM"(INS STRING)
    RETURNS TABLE (NUM FLOAT)
    LANGUAGE JAVASCRIPT
    AS '{
    processRow: function (row, rowWriter, context) {
      this.ccount = this.ccount + 1;
      this.csum = this.csum + row.INS.length;
      rowWriter.writeRow({NUM: row.INS.length});
    },
    finalize: function (rowWriter, context) {
     rowWriter.writeRow({NUM: this.csum});
    },
    initialize: function(argumentInfo, context) {
     this.ccount = 0;
     this.csum = 0;
    }}';

Die folgende Abfrage veranschaulicht das Aufrufen der CHAR_SUM-UDTF für die Tabelle parts ohne Partitionierung:

SELECT * FROM parts, TABLE(char_sum(s));

Ausgabe:

+--------+---------+-----+
| P      | S       | NUM |
+--------+---------+-----+
| 1      | michael | 7   |
| 1      | kelly   | 5   |
| 1      | brian   | 5   |
| 2      | clara   | 5   |
| 2      | maggie  | 6   |
| 2      | reagan  | 6   |
| [NULL] | [NULL]  | 34  |
+--------+---------+-----+

Wenn keine Partitionierung angegeben ist, definiert Snowflake automatisch Partitionen. In diesem Beispiel wird aufgrund der geringen Anzahl von Zeilen nur eine Partition erstellt (d. h. es wird nur ein Aufruf von finalize() ausgeführt). Beachten Sie, dass in der letzten Zeile für die Eingabespalten NULL-Werte verwendet werden.

Gleiche Abfrage, jedoch mit expliziter Partitionierung:

SELECT * FROM parts, TABLE(char_sum(s) OVER (PARTITION BY p));

Ausgabe:

+--------+---------+-----+
| P      | S       | NUM |
+--------+---------+-----+
| 1      | michael | 7   |
| 1      | kelly   | 5   |
| 1      | brian   | 5   |
| 1      | [NULL]  | 17  |
| 2      | clara   | 5   |
| 2      | maggie  | 6   |
| 2      | reagan  | 6   |
| 2      | [NULL]  | 17  |
+--------+---------+-----+

Dieses Beispiel wird über die Spalte p partitioniert, wodurch zwei Partitionen entstehen. Für jede Partition wird im finalize()-Callback eine einzelne Zeile zurückgegeben. Dies ergibt insgesamt zwei Zeilen, die durch den NULL-Wert in der Spalte s unterschieden werden. Da p die PARTITION BY-Spalte ist, haben die in finalize() erstellten Zeilen den Wert p, der die aktuelle Partition definiert.

Erweiterte Beispiele mit Tabellenwerten und anderen UDTFs als Eingabe¶

Diese einfache UDTF konvertiert einen „Bereich“ von IP-Adressen in eine vollständige Liste von IP-Adressen. Die Eingabe besteht aus den ersten 3 Segmenten der IP-Adresse (z. B. '192.168.1') und dann aus Anfang und Ende des Bereichs, der zum Generieren des letzten Segments (z. B. 42 und 45) verwendet wird:

CREATE OR REPLACE FUNCTION range_to_values(PREFIX VARCHAR, RANGE_START FLOAT, RANGE_END FLOAT)
    RETURNS TABLE (IP_ADDRESS VARCHAR)
    LANGUAGE JAVASCRIPT
    AS $$
      {
        processRow: function f(row, rowWriter, context)  {
          var suffix = row.RANGE_START;
          while (suffix <= row.RANGE_END)  {
            rowWriter.writeRow( {IP_ADDRESS: row.PREFIX + "." + suffix} );
            suffix = suffix + 1;
            }
          }
      }
      $$;

SELECT * FROM TABLE(range_to_values('192.168.1', 42::FLOAT, 45::FLOAT));

Ausgabe:

+--------------+
| IP_ADDRESS   |
+==============+
| 192.168.1.42 |
+--------------+
| 192.168.1.43 |
+--------------+
| 192.168.1.44 |
+--------------+
| 192.168.1.45 |
+--------------+

Aufbauend auf dem vorherigen Beispiel können Sie einzelne IP-Adressen für mehr als einen Bereich berechnen. Diese nächste Anweisung erstellt eine Tabelle mit Bereichen, die zum Erweitern auf einzelne IP-Adressen verwendet werden können. Die Abfrage gibt dann die Zeilen aus der Tabelle in die range_to_values()-UDTF ein, um die einzelnen IP-Adressen zurückzugeben:

CREATE TABLE ip_address_ranges(prefix VARCHAR, range_start INTEGER, range_end INTEGER);
INSERT INTO ip_address_ranges (prefix, range_start, range_end) VALUES
    ('192.168.1', 42, 44),
    ('192.168.2', 10, 12),
    ('192.168.2', 40, 40)
    ;

SELECT rtv.ip_address
  FROM ip_address_ranges AS r, TABLE(range_to_values(r.prefix, r.range_start::FLOAT, r.range_end::FLOAT)) AS rtv;

Ausgabe:

+--------------+
| IP_ADDRESS   |
+==============+
| 192.168.1.42 |
+--------------+
| 192.168.1.43 |
+--------------+
| 192.168.1.44 |
+--------------+
| 192.168.2.10 |
+--------------+
| 192.168.2.11 |
+--------------+
| 192.168.2.12 |
+--------------+
| 192.168.2.40 |
+--------------+

Das Konzept der Übergabe von Werten an eine UDTF kann auf mehrere UDTFs erweitert werden. Im nächsten Beispiel wird eine UDTF mit dem Namen fake_ipv4_to_ipv6() erstellt, die die IPV4-Adresse in IPV6-Adressen „konvertiert“. Die Abfrage ruft die Funktion dann als Teil einer komplexeren Anweisung auf, die eine andere UDTFbeinhaltet:

-- Example UDTF that "converts" an IPV4 address to a range of IPV6 addresses.
-- (for illustration purposes only and is not intended for actual use)
CREATE OR REPLACE FUNCTION fake_ipv4_to_ipv6(ipv4 VARCHAR)
    RETURNS TABLE (IPV6 VARCHAR)
    LANGUAGE JAVASCRIPT
    AS $$
      {
        processRow: function f(row, rowWriter, context)  {
          rowWriter.writeRow( {IPV6: row.IPV4 + "." + "000.000.000.000"} );
          rowWriter.writeRow( {IPV6: row.IPV4 + "." + "..."} );
          rowWriter.writeRow( {IPV6: row.IPV4 + "." + "FFF.FFF.FFF.FFF"} );
          }
      }
      $$;

SELECT ipv6 FROM TABLE(fake_ipv4_to_ipv6('192.168.3.100'));

Ausgabe:

+-------------------------------+
| IPV6                          |
+===============================+
| 192.168.3.100.000.000.000.000 |
+-------------------------------+
| 192.168.3.100....             |
+-------------------------------+
| 192.168.3.100.FFF.FFF.FFF.FFF |
+-------------------------------+

Die folgende Abfrage verwendet die zuvor erstellten fake_ipv4_to_ipv6- und range_to_values()-UDTFs mit Eingabe aus der Tabelle ip_address changes. Mit anderen Worten, sie beginnt mit einem Set von IP-Adressbereichen, konvertiert diese in einzelne IPV4-Adressen und nimmt dann jede IPV4-Adresse und „konvertiert“ diese wiederum in einen Bereich von IPV6-Adressen:

SELECT rtv6.ipv6
  FROM ip_address_ranges AS r,
       TABLE(range_to_values(r.prefix, r.range_start::FLOAT, r.range_end::FLOAT)) AS rtv,
       TABLE(fake_ipv4_to_ipv6(rtv.ip_address)) AS rtv6
  WHERE r.prefix = '192.168.2'  -- limits the output for this example
  ;

Ausgabe:

+------------------------------+
| IPV6                         |
+==============================+
| 192.168.2.10.000.000.000.000 |
+------------------------------+
| 192.168.2.10....             |
+------------------------------+
| 192.168.2.10.FFF.FFF.FFF.FFF |
+------------------------------+
| 192.168.2.11.000.000.000.000 |
+------------------------------+
| 192.168.2.11....             |
+------------------------------+
| 192.168.2.11.FFF.FFF.FFF.FFF |
+------------------------------+
| 192.168.2.12.000.000.000.000 |
+------------------------------+
| 192.168.2.12....             |
+------------------------------+
| 192.168.2.12.FFF.FFF.FFF.FFF |
+------------------------------+
| 192.168.2.40.000.000.000.000 |
+------------------------------+
| 192.168.2.40....             |
+------------------------------+
| 192.168.2.40.FFF.FFF.FFF.FFF |
+------------------------------+

Beachten Sie, dass in diesem Beispiel zweimal die Join-Syntax verwendet wurde, aber keine der Operationen eine echte Verknüpfung war: Beide waren Aufrufe einer UDTF, wobei die Ausgabe einer Tabelle oder eine andere UDTF als Eingabe verwendet wurde.

Bei einem echten INNER JOIN muss die Reihenfolge nicht beachtet werden. Beispielsweise sind die folgenden Anweisungen identisch:

Die Übergabe von Werten an eine UDTF ist keine echte Verknüpfung, und bei den Operationen wird die Reihenfolge nicht beachtet. Die folgende Abfrage ist beispielsweise identisch mit dem vorherigen Beispiel, außer dass die Reihenfolge der UDTFs in der FROM-Klausel umgekehrt wird:

SELECT rtv6.ipv6
  FROM ip_address_ranges AS r,
       TABLE(fake_ipv4_to_ipv6(rtv.ip_address)) AS rtv6,
       TABLE(range_to_values(r.prefix, r.range_start::FLOAT, r.range_end::FLOAT)) AS rtv
 WHERE r.prefix = '192.168.2'  -- limits the output for this example
  ;

Die Abfrage schlägt mit der folgenden Fehlermeldung fehl:

SQL compilation error: error line 3 at position 35 invalid identifier 'RTV.IP_ADDRESS'

Der Bezeichner rtv.ip_address ist ungültig, da er vor seiner Verwendung nicht definiert wurde. Bei einer echten Verknüpfung würde dies nicht passieren, aber bei der Verarbeitung von UDTFs mithilfe der Join-Syntax kann dieser Fehler möglicherweise auftreten.

Testen Sie als Nächstes eine Anweisung, die die Übergabe von Eingabewerten an eine UDTF mit einer echten Verknüpfung kombiniert. Denken Sie jedoch daran, dass die Eingabe in eine UDTF und INNER JOIN die gleiche Syntax verwenden, was verwirrend sein kann:

-- First, create a small table of IP address owners.
-- This table uses only IPv4 addresses for simplicity.
DROP TABLE ip_address_owners;
CREATE TABLE ip_address_owners (ip_address VARCHAR, owner_name VARCHAR);
INSERT INTO ip_address_owners (ip_address, owner_name) VALUES
  ('192.168.2.10', 'Barbara Hart'),
  ('192.168.2.11', 'David Saugus'),
  ('192.168.2.12', 'Diego King'),
  ('192.168.2.40', 'Victoria Valencia')
  ;

-- Now join the IP address owner table to the IPv4 addresses.
SELECT rtv.ip_address, ipo.owner_name
  FROM ip_address_ranges AS r,
       TABLE(range_to_values(r.prefix, r.range_start::FLOAT, r.range_end::FLOAT)) AS rtv,
       ip_address_owners AS ipo
 WHERE ipo.ip_address = rtv.ip_address AND
      r.prefix = '192.168.2'   -- limits the output for this example
  ;

Ausgabe:

+--------------+-------------------+
| IP_ADDRESS   | OWNER_NAME        |
+==============+===================+
| 192.168.2.10 | Barbara Hart      |
+--------------+-------------------+
| 192.168.2.11 | David Saugus      |
+--------------+-------------------+
| 192.168.2.12 | Diego King        |
+--------------+-------------------+
| 192.168.2.40 | Victoria Valencia |
+--------------+-------------------+