Benutzerdefinierte Standard-SQL-Funktionen

BigQuery unterstützt benutzerdefinierte Funktionen (UDFs). Mithilfe einer UDF können Sie unter Verwendung eines anderen SQL-Ausdrucks oder mit JavaScript Funktionen erstellen. Diese Funktionen akzeptieren Spalten als Eingabe und führen Aktionen durch. Das Ergebnis dieser Aktionen wird als Wert zurückgegeben. Weitere Informationen zu benutzerdefinierten Funktionen in Legacy-SQL finden Sie unter Benutzerdefinierte Funktionen in Legacy-SQL.

UDFs können entweder persistent oder temporär sein. Sie können persistente UDFs für mehrere Abfragen verwenden, temporäre UDFs jedoch nur in einer einzigen Abfrage.

UDF-Syntax

Verwenden Sie die folgende Syntax, um eine persistente UDF zu erstellen:

CREATE [OR REPLACE] FUNCTION [IF NOT EXISTS]
    [`project_name`.]dataset_name.function_name
    ([named_parameter[, ...]])
  [RETURNS data_type]
  { sql_function_definition | javascript_function_definition }

Verwenden Sie die folgende Syntax, um eine temporäre UDF zu erstellen:

CREATE [OR REPLACE] {TEMPORARY | TEMP} FUNCTION [IF NOT EXISTS]
    function_name
    ([named_parameter[, ...]])
  [RETURNS data_type]
  { sql_function_definition | javascript_function_definition }

named_parameter:
  param_name param_type

sql_function_definition:
  AS (sql_expression)

javascript_function_definition:
  LANGUAGE js
  [OPTIONS (library = library_array)]
  AS javascript_code

Diese Syntax besteht aus den folgenden Komponenten:

  • CREATE { FUNCTION | OR REPLACE FUNCTION | FUNCTION IF NOT EXISTS }: Erstellt oder aktualisiert eine Funktion. Zum Ersetzen einer vorhandenen gleichnamigen Funktion verwenden Sie das Schlüsselwort OR REPLACE. Soll eine Abfrage als erfolgreich behandelt werden und keine Aktion auslösen, falls bereits eine gleichnamige Funktion vorhanden ist, verwenden Sie die Klausel IF NOT EXISTS.
  • named_parameter: Besteht aus durch Kommas getrennten Paaren aus param_name und param_type. Der Wert von param_type ist ein BigQuery-Datentyp. Bei einer SQL-UDF kann der Wert von param_type auch ANY TYPE sein.
  • [RETURNS data_type]: Gibt den Datentyp an, der von der Funktion zurückgegeben wird.
    • Wenn die Funktion in SQL definiert ist, ist die Klausel RETURNS optional. Wenn die Klausel RETURNS nicht angegeben wird und die Funktion von einer Abfrage aufgerufen wird, leitet BigQuery den Ergebnistyp der Funktion aus dem SQL-Funktionsrumpf ab.
    • Wenn die Funktion in JavaScript definiert ist, muss die Klausel RETURNS verwendet werden. Weitere Informationen zu zulässigen Werten für data_type finden Sie unter Von JavaScript-UDFs unterstützte Datentypen.
  • AS (sql_expression). Gibt den SQL-Ausdruck an, der die Funktion definiert.
  • [OPTIONS (library = library_array)]. Definiert für eine JavaScript-UDF ein Array von JavaScript-Bibliotheken, die in die Funktionsdefinition aufgenommen werden sollen.
  • AS javascript_code. Gibt die Definition einer JavaScript-Funktion an. javascript_code ist ein Stringliteral.

Verwenden Sie die folgende Syntax, um eine persistente benutzerdefinierte Funktion zu löschen:

DROP FUNCTION [IF EXISTS] [`project_name`.]dataset_name.function_name

Temporäre benutzerdefinierte Funktionen laufen ab, sobald die Abfrage abgeschlossen ist. Dementsprechend unterstützen sie DROP FUNCTION-Anweisungen nicht. Eine Ausnahme hiervon bilden Skripts und Prozeduren.

SQL-UDF-Struktur

Erstellen Sie SQL-UDFs mit der folgenden Syntax:

CREATE [OR REPLACE] [TEMPORARY | TEMP] FUNCTION [IF NOT EXISTS]
    [[`project_name`.]dataset_name.]function_name
    ([named_parameter[, ...]])
  [RETURNS data_type]
  AS (sql_expression)

named_parameter:
  param_name param_type

Vorlagen für SQL-UDF-Parameter

Ein Vorlagenparameter mit param_type = ANY TYPE kann bei einem Funktionsaufruf mit mehr als einem Argumenttyp übereinstimmen.

  • Wenn mehr als ein Parameter den Typ ANY TYPE hat, erzwingt BigQuery keine Typbeziehung zwischen diesen Argumenten.
  • Der Rückgabetyp der Funktion darf nicht ANY TYPE sein. Er muss entweder ausgelassen werden, d. h. automatisch anhand von sql_expression ermittelt werden, oder ein expliziter Typ sein.
  • Werden der Funktion Argumente mit Typen übergeben, die nicht mit der Funktionsdefinition kompatibel sind, wird zum Aufrufzeitpunkt ein Fehler ausgelöst.

SQL-UDF-Beispiele

Das folgende Beispiel zeigt eine UDF, die sich einer SQL-Funktion bedient.

CREATE TEMP FUNCTION addFourAndDivide(x INT64, y INT64) AS ((x + 4) / y);
WITH numbers AS
  (SELECT 1 as val
  UNION ALL
  SELECT 3 as val
  UNION ALL
  SELECT 4 as val
  UNION ALL
  SELECT 5 as val)
SELECT val, addFourAndDivide(val, 2) AS result
FROM numbers;

+-----+--------+
| val | result |
+-----+--------+
| 1   | 2.5    |
| 3   | 3.5    |
| 4   | 4      |
| 5   | 4.5    |
+-----+--------+

Das folgende Beispiel zeigt eine SQL-UDF, die einen Vorlagenparameter verwendet. Die daraus resultierende Funktion akzeptiert Argumente verschiedener Typen.

CREATE TEMP FUNCTION addFourAndDivideAny(x ANY TYPE, y ANY TYPE) AS (
  (x + 4) / y
);
SELECT addFourAndDivideAny(3, 4) AS integer_output,
       addFourAndDivideAny(1.59, 3.14) AS floating_point_output;

+----------------+-----------------------+
| integer_output | floating_point_output |
+----------------+-----------------------+
| 1.75           | 1.7802547770700636    |
+----------------+-----------------------+

Das folgende Beispiel zeigt eine SQL-UDF, die einen Vorlagenparameter verwendet, um das letzte Element eines Arrays eines beliebigen Typs zurückzugeben.

CREATE TEMP FUNCTION lastArrayElement(arr ANY TYPE) AS (
  arr[ORDINAL(ARRAY_LENGTH(arr))]
);
SELECT
  names[OFFSET(0)] AS first_name,
  lastArrayElement(names) AS last_name
FROM (
  SELECT ['Fred', 'McFeely', 'Rogers'] AS names UNION ALL
  SELECT ['Marie', 'Skłodowska', 'Curie']
);

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Fred       | Rogers    |
| Marie      | Curie     |
+------------+-----------+

Struktur einer JavaScript-UDF

JavaScript-UDFs werden mit der folgenden Struktur erstellt.

CREATE [OR REPLACE] [TEMPORARY | TEMP] FUNCTION [IF NOT EXISTS]
    [[`project_name`.]dataset_name.]function_name
    ([named_parameter[, ...]])
  RETURNS data_type
  LANGUAGE js
  [OPTIONS (library = library_array)]
  AS javascript_code

Beispiele für JavaScript-UDFs

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x*y;
""";
WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)
SELECT x, y, multiplyInputs(x, y) as product
FROM numbers;

+-----+-----+--------------+
| x   | y   | product      |
+-----+-----+--------------+
| 1   | 5   | 5            |
| 2   | 10  | 20           |
| 3   | 15  | 45           |
+-----+-----+--------------+

Sie können das Ergebnis einer UDF als Eingabe an eine andere UDF übergeben. Beispiel:

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x*y;
""";
CREATE TEMP FUNCTION divideByTwo(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x/2;
""";
WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)
SELECT x,
  y,
  multiplyInputs(divideByTwo(x), divideByTwo(y)) as half_product
FROM numbers;

+-----+-----+--------------+
| x   | y   | half_product |
+-----+-----+--------------+
| 1   | 5   | 1.25         |
| 2   | 10  | 5            |
| 3   | 15  | 11.25        |
+-----+-----+--------------+

Im folgenden Beispiel werden die Werte aller Felder namens "foo" im angegebenen JSON-String summiert.

CREATE TEMP FUNCTION SumFieldsNamedFoo(json_row STRING)
  RETURNS FLOAT64
  LANGUAGE js AS """
function SumFoo(obj) {
  var sum = 0;
  for (var field in obj) {
    if (obj.hasOwnProperty(field) && obj[field] != null) {
      if (typeof obj[field] == "object") {
        sum += SumFoo(obj[field]);
      } else if (field == "foo") {
        sum += obj[field];
      }
    }
  }
  return sum;
}
var row = JSON.parse(json_row);
return SumFoo(row);
""";

WITH Input AS (
  SELECT STRUCT(1 AS foo, 2 AS bar, STRUCT('foo' AS x, 3.14 AS foo) AS baz) AS s, 10 AS foo UNION ALL
  SELECT NULL, 4 AS foo UNION ALL
  SELECT STRUCT(NULL, 2 AS bar, STRUCT('fizz' AS x, 1.59 AS foo) AS baz) AS s, NULL AS foo
)
SELECT
  TO_JSON_STRING(t) AS json_row,
  SumFieldsNamedFoo(TO_JSON_STRING(t)) AS foo_sum
FROM Input AS t;
+---------------------------------------------------------------------+---------+
| json_row                                                            | foo_sum |
+---------------------------------------------------------------------+---------+
| {"s":{"foo":1,"bar":2,"baz":{"x":"foo","foo":3.14}},"foo":10}       | 14.14   |
| {"s":null,"foo":4}                                                  | 4       |
| {"s":{"foo":null,"bar":2,"baz":{"x":"fizz","foo":1.59}},"foo":null} | 1.59    |
+---------------------------------------------------------------------+---------+

Weitere Informationen zur Zuordnung von BigQuery-Datentypen zu JavaScript-Typen finden Sie unter Von JavaScript-UDFs unterstützte Datentypen.

Von JavaScript-UDFs unterstützte Datentypen

Einige SQL-Typen haben eine direkte Zuordnung zu JavaScript-Typen, andere dagegen nicht. BigQuery stellt Typen auf folgende Weise dar:

BigQuery-Datentyp JavaScript-Datentyp
ARRAY ARRAY
BOOL BOOLEAN
BYTES STRING mit Base64-Codierung
FLOAT64 NUMBER
NUMERIC Wenn ein NUMERIC-Wert exakt als IEEE-754-Gleitkommawert dargestellt werden kann und keinen Bruchteil hat, wird er als Zahl codiert. Diese Werte liegen im Bereich [-253, 253]. Andernfalls werden sie als String codiert.
STRING STRING
STRUCT OBJECT, wobei jedes STRUCT-Feld einen Namen hat
TIMESTAMP DATE mit einem Mikrosekundenfeld, das den microsecond-Bruchteil des Zeitstempels enthält
DATE DATE

Da JavaScript keine 64-Bit-Ganzzahlen unterstützt, wird INT64 als Eingabetyp für JavaScript-UDFs nicht unterstützt. Verwenden Sie stattdessen FLOAT64, um Ganzzahlwerte als Zahl darzustellen, oder STRING, um Ganzzahlwerte als String darzustellen.

BigQuery unterstützt INT64 als Rückgabetyp in JavaScript-UDFs. In diesem Fall gibt der JavaScript-Funktionsrumpf entweder eine JavaScript-Zahl oder einen JavaScript-String zurück. BigQuery konvertiert diese Typen in INT64.

Wenn der Rückgabewert der JavaScript-UDF ein Promise ist, wartet BigQuery auf das Promise, bis es erfüllt ist. Wenn sich das Promise in einem erfüllten Zustand befindet, gibt BigQuery das Ergebnis zurück. Wenn sich das Promise in einem abgelehnten Status befindet, gibt BigQuery einen Fehler zurück.

Regeln für Anführungszeichen

Der JavaScript-Code muss in Anführungszeichen gesetzt werden. Für einfache, einzeilige Code-Snippets können Sie einen Standardstring in Anführungszeichen verwenden:

CREATE TEMP FUNCTION plusOne(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS "return x+1;";
SELECT val, plusOne(val) AS result
FROM UNNEST([1, 2, 3, 4, 5]) AS val;

+-----------+-----------+
| val       | result    |
+-----------+-----------+
| 1         | 2         |
| 2         | 3         |
| 3         | 4         |
| 4         | 5         |
| 5         | 6         |
+-----------+-----------+

In Fällen, in denen das Snippet Anführungszeichen enthält oder aus mehreren Zeilen besteht, verwenden Sie Blocks mit dreifachen Anführungszeichen:

CREATE TEMP FUNCTION customGreeting(a STRING)
RETURNS STRING
LANGUAGE js AS """
  var d = new Date();
  if (d.getHours() < 12) {
    return 'Good Morning, ' + a + '!';
  } else {
    return 'Good Evening, ' + a + '!';
  }
  """;
SELECT customGreeting(names) as everyone
FROM UNNEST(["Hannah", "Max", "Jakob"]) AS names;
+-----------------------+
| everyone              |
+-----------------------+
| Good Morning, Hannah! |
| Good Morning, Max!    |
| Good Morning, Jakob!  |
+-----------------------+

JavaScript-Bibliotheken einbeziehen

Sie können JavaScript-UDFs über den Bereich OPTIONS erweitern. In diesem Abschnitt können Sie externe Codebibliotheken für die UDF angeben.

CREATE TEMP FUNCTION myFunc(a FLOAT64, b STRING)
  RETURNS STRING
  LANGUAGE js
  OPTIONS (
    library=["gs://my-bucket/path/to/lib1.js", "gs://my-bucket/path/to/lib2.js"]
  )
  AS
"""
    // Assumes 'doInterestingStuff' is defined in one of the library files.
    return doInterestingStuff(a, b);
""";

SELECT myFunc(3.14, 'foo');

Im vorherigen Beispiel ist der Code in lib1.js und lib2.js für jeden Code im Bereich [external_code] der UDF verfügbar.

UDFs und die Web-UI

Über die BigQuery-Web-UI können Sie Abfragen mit einer oder mehreren UDFs ausführen.

Abfrage mit einer UDF ausführen

  1. Klicken Sie auf die Schaltfläche ABFRAGE ERSTELLEN.
  2. Klicken Sie auf den Tab Abfrage-Editor.
  3. Klicken Sie auf die Schaltfläche Optionen anzeigen.
  4. Deaktivieren Sie das Kästchen Legacy-SQL verwenden.
  5. Geben Sie die UDF-Anweisung in den Textbereich im Abfrage-Editor ein. Beispiel:

    CREATE TEMPORARY FUNCTION timesTwo(x FLOAT64)
    RETURNS FLOAT64
      LANGUAGE js AS """
      return x*2;
    """;
  6. Geben Sie unterhalb der UDF-Anweisung die Abfrage ein. Beispiel:

    SELECT timesTwo(numbers) AS doubles
    FROM UNNEST([1, 2, 3, 4, 5]) AS numbers;
  7. Klicken Sie auf die Schaltfläche ABFRAGE AUSFÜHREN. Die Abfrageergebnisse werden unterhalb der Schaltflächen angezeigt.

UDFs und das bq-Befehlszeilentool

Über das bq-Befehlszeilentool im Google Cloud SDK können Sie eine Abfrage mit einer oder mehreren UDFs ausführen.

Verwenden Sie die folgende Syntax zum Ausführen einer Abfrage mit einer UDF:

bq query <statement_with_udf_and_query>

Best Practices für JavaScript-UDFs

Eingabe vorfiltern

Wenn Ihre Eingabe einfach gefiltert werden kann, bevor sie an eine JavaScript-UDF übergeben wird, kann die Abfrage wahrscheinlich schneller und günstiger ausgeführt werden.

Persistenten änderbaren Status vermeiden

Speichern oder rufen Sie keinen änderbaren Status über JavaScript-UDF-Aufrufe hinweg auf.

Speicher effizient nutzen

Die JavaScript-Verarbeitungsumgebung verfügt nur über wenig Speicher pro Abfrage. JavaScript-UDF-Abfragen, die zu viel lokalen Status ansammeln, können aufgrund von Speicherausschöpfung fehlschlagen.

Limits

  • Der Umfang an Daten, den Ihre JavaScript-UDF bei der Verarbeitung einer einzelnen Zeile ausgibt, ist auf ca. 5 MB oder weniger beschränkt.
  • Die Rate für gleichzeitige Legacy-SQL-Abfragen, die benutzerdefinierte Funktionen (UDFs) enthalten, ist auf 6 gleichzeitige Abfragen beschränkt.
  • Das Ratenlimit für gleichzeitige Legacy-SQL-Abfragen, die UDFs enthalten, umfasst sowohl interaktive als auch Batchabfragen. Interaktive Abfragen, die UDFs enthalten, werden auch auf das Limit für gleichzeitige interaktive Abfragen angerechnet. Dieses Limit gilt nicht für Standard-SQL-Abfragen.

  • Maximale Anzahl an JavaScript-UDF-Ressourcen wie Inline-Code-Blobs oder externe Dateien in einem Abfragejob: 50.
  • Maximale Größe eines Inline-Code-Blobs: 32 KB
  • Maximale Größe einer externen Coderessource: 1 MB

Für persistente benutzerdefinierte Funktionen gelten die folgenden Limits:
  • Mmaximale Länge eines Funktionsnamens: 256 Zeichen.
  • Maximale Anzahl der Argumente: 256.
  • Maximale Länge eines Argumentnamens: 128 Zeichen.
  • Maximale Tiefe der Referenzkette einer benutzerdefinierten Funktion: 16.
  • Maximale Tiefe eines Arguments oder einer Ausgabe vom Typ STRUCT: 15.
  • Maximale Anzahl der Felder in einem Argument oder einer Ausgabe vom Typ STRUCT pro UDF: 1.024.
  • Maximale Anzahl der Verweise auf einzelne UDFs und Tabellen pro Abfrage: 1.000. Nach vollständiger Erweiterung kann eine UDF auf bis zu 1.000 Kombinationen aus einzelnen Tabellen und UDFs verweisen.
  • Maximale Anzahl der JavaScript-Bibliotheken in einer CREATE-FUNCTION-Anweisung: 50.
  • Maximale Länge der enthaltenen JavaScript-Bibliothekspfade: 5.000 Zeichen.
  • Maximale Aktualisierungsrate pro UDF: 5 pro 10 Sekunden. Nach der Funktionserstellung können Sie jede Funktion bis zu fünfmal pro 10 Sekunden aktualisieren.
  • Jedes Inline-Codeblob ist auf eine maximale Größe von 32 KB begrenzt.
  • Jede JavaScript-Coderessource ist auf eine maximale Größe von 1 MB begrenzt.

Beschränkungen

Für temporäre und persistente benutzerdefinierte Funktionen gelten die folgenden Beschränkungen:

  • Die DOM-Objekte Window, Document und Node sowie die Funktionen, die diese Objekte erfordern, werden nicht unterstützt.
  • JavaScript-Funktionen, die nativen Code benötigen, werden nicht unterstützt.
  • Bei einer JavaScript-UDF kann es zu einer Zeitüberschreitung kommen, die ein Abschließen Ihrer Abfrage verhindert. Zeitüberschreitungen können bereits nach nur 5 Minuten auftreten, hängen aber von mehreren Faktoren ab, z. B. auch davon, wie viel Nutzer-CPU-Zeit die Funktion verbraucht und wie groß die Eingaben an die JS-Funktion und ihre Ausgaben sind.
  • Aufgrund ihres nicht-deterministischen Charakters können Abfragen, die JavaScript-UDFs aufrufen, keine im Cache gespeicherten Ergebnisse verwenden.
  • Bitweise Vorgänge in JavaScript verarbeiten nur die höchstwertigen 32 Bit.
  • Sie können in einer UDF auf keine Tabelle verweisen.

Für persistente benutzerdefinierte Funktionen gelten die folgenden Beschränkungen:

  • Ein Dataset kann jeweils nur eine persistente UDF mit demselben Namen enthalten. Sie können jedoch eine UDF mit dem gleichen Namen einer Tabelle in demselben Dataset erstellen.
  • Wenn Sie von einer persistenten UDF auf eine andere verweisen, müssen Sie den Namen mit dem Dataset qualifizieren. Beispiel:
    CREATE FUNCTION mydataset.referringFunction() AS (mydataset.referencedFunction());
  • Wenn Sie von einer logischen Ansicht auf eine persistente UDF verweisen, müssen Sie den Namen vollständig mit dem Projekt und dem Dataset qualifizieren. Beispiel:
    CREATE VIEW mydataset.sample_view AS SELECT `my-project`.mydataset.referencedFunction();

Für temporäre benutzerdefinierte Funktionen gelten die folgenden Beschränkungen:

  • Beim Erstellen einer temporären UDF darf function_name keine Punkte enthalten.
  • Logische Ansichten und persistente UDFs können nicht auf temporäre UDFs verweisen.