Benutzerdefinierte Standard-SQL-Funktionen

Mit einer benutzerdefinierten Funktion (User-defined Function, UDF) können Sie eine Funktion mit einem SQL-Ausdruck oder JavaScript-Code erstellen. Eine UDF akzeptiert Eingabespalten, führt Aktionen für die Eingabe aus und gibt das Ergebnis dieser Aktionen als Wert zurück.

UDFs können als persistent oder temporär definiert werden. Persistente UDFs können Sie für mehrere Abfragen verwenden, temporäre UDFs jedoch nur für eine einzelne Abfrage.

Verwenden Sie zum Erstellen einer UDF die Anweisung CREATE FUNCTION. Verwenden Sie die Anweisung DROP FUNCTION, um eine persistente benutzerdefinierte Funktion zu löschen. Temporäre UDFs laufen sofort ab, wenn die Abfrage abgeschlossen ist. Die Anweisung DROP FUNCTION wird nur für temporäre UDFs in Skripts und Prozeduren unterstützt.

Informationen zu UDFs in Legacy-SQL finden Sie unter Benutzerdefinierte Funktionen in Legacy-SQL.

SQL-UDFs

Im folgenden Beispiel wird eine temporäre SQL-UDF mit dem Namen AddFourAndDivide erstellt und aus einer SELECT-Anweisung aufgerufen:

CREATE TEMP FUNCTION AddFourAndDivide(x INT64, y INT64)
  RETURNS FLOAT64
  AS ((x + 4) / y);

SELECT val, AddFourAndDivide(val, 2)
  FROM UNNEST([2,3,5,8]) AS val;

Dieses Beispiel liefert folgende Ausgabe:

+-----+-----+
| val | f0_ |
+-----+-----+
|   2 | 3.0 |
|   3 | 3.5 |
|   5 | 4.5 |
|   8 | 6.0 |
+-----+-----+

Das nächste Beispiel erstellt dieselbe Funktion wie eine persistente UDF:

CREATE FUNCTION mydataset.AddFourAndDivide(x INT64, y INT64)
  RETURNS FLOAT64
  AS ((x + 4) / y);

Da diese UDF persistent ist, müssen Sie ein Dataset für die Funktion angeben (in diesem Beispiel mydataset). Nachdem Sie die Anweisung CREATE FUNCTION ausgeführt haben, können Sie die Funktion aus einer Abfrage aufrufen:

SELECT val, mydataset.AddFourAndDivide(val, 2)
  FROM UNNEST([2,3,5,8,12]) AS val;

Vorlagen für SQL-UDF-Parameter

Ein Parameter mit dem Typ ANY TYPE kann bei einem Funktionsaufruf mit mehr als einem Argumenttyp übereinstimmen.

  • Wenn mehrere Parameter den Typ ANY TYPE haben, 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.

Das folgende Beispiel zeigt eine SQL-UDF, die einen Vorlagenparameter verwendet.

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;

Dieses Beispiel liefert folgende Ausgabe:

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

Im nächsten Beispiel wird ein 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 lastArrayElement(x) as last_element
  FROM (SELECT [2,3,5,8,13] as x)

Dieses Beispiel liefert folgende Ausgabe:

+--------------+
| last_element |
+--------------+
| 13           |
+--------------+

Skalare Teilabfragen

Eine SQL-UDF kann den Wert einer skalaren Unterabfrage zurückgeben. Eine skalare Unterabfrage muss eine einzelne Spalte auswählen.

Das folgende Beispiel zeigt eine SQL-UDF, die mithilfe einer skalaren Unterabfrage die Anzahl der Nutzer mit einem bestimmten Alter in einer Nutzertabelle erfasst.

CREATE TEMP TABLE users
AS SELECT 1 id, 10 age
UNION ALL SELECT 2, 30
UNION ALL SELECT 3, 10;

CREATE TEMP FUNCTION countUserByAge(userAge INT64)
AS ((SELECT COUNT(1) FROM users WHERE age = userAge));

SELECT countUserByAge(10) AS count_user_age_10,
       countUserByAge(20) AS count_user_age_20,
       countUserByAge(30) AS count_user_age_30;

Dieses Beispiel liefert folgende Ausgabe:

+-------------------+-------------------+-------------------+
| count_user_age_10 | count_user_age_20 | count_user_age_30 |
+-------------------+-------------------+-------------------+
|                 2 |                 0 |                 1 |
+-------------------+-------------------+-------------------+

Standardprojekt in SQL-Ausdrücken

Im Textteil einer SQL-UDF muss jeder Verweis auf BigQuery-Entitäten wie Tabellen oder Ansichten die Projekt-ID enthalten, es sei denn, die Entität befindet sich in demselben Projekt, in dem die Anweisung CREATE FUNCTION ausgeführt wird.

Betrachten Sie beispielsweise die folgende Anweisung:

CREATE FUNCTION project1.mydataset.myfunction()
  AS ((SELECT COUNT(*) FROM mydataset.mytable))

Wenn Sie diese Anweisung über project1 ausführen und mydataset.mytable in project1 vorhanden ist, wird die Anweisung erfolgreich ausgeführt. Wenn Sie diese Anweisung jedoch in einem anderen Projekt ausführen, schlägt sie fehl. Fügen Sie die Projekt-ID in die Tabellenreferenz ein, um den Fehler zu beheben:

CREATE FUNCTION project1.mydataset.myfunction()
  AS ((SELECT COUNT(*) FROM project1.mydataset.mytable))

Sie können auch auf eine Entität in einem anderen Projekt oder Dataset verweisen, in dem Sie die Funktion erstellen:

CREATE FUNCTION project1.mydataset.myfunction()
  AS ((SELECT COUNT(*) FROM project2.another_dataset.another_table))

JavaScript-UDFs

Mit einer JavaScript-UDF können Sie in JavaScript geschriebenen Code aus einer SQL-Abfrage aufrufen.

Das folgende Beispiel zeigt eine JavaScript-UDF. Der JavaScript-Code ist in einem Rohstring in Anführungszeichen gesetzt.

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS r"""
  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;

Dieses Beispiel liefert folgende Ausgabe:

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

Im nächsten 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 r"""
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;

Das Beispiel erzeugt die folgende Ausgabe:

+---------------------------------------------------------------------+---------+
| 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    |
+---------------------------------------------------------------------+---------+

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, BIGNUMERIC Wenn ein NUMERIC- oder BIGNUMERIC-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 Status befindet, gibt BigQuery das Ergebnis zurück. Wenn sich das Promise in einem abgelehnten Status befindet, gibt BigQuery einen Fehler zurück.

Quoting-Regeln

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 r"""
  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
r"""
    // 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.

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.

Autorisierte UDFs

Mit autorisierten Funktionen können Sie Abfrageergebnisse für bestimmte Nutzer oder Gruppen freigeben, ohne diesen Nutzern oder Gruppen Zugriff auf die zugrunde liegenden Tabellen zu gewähren. Beispielsweise kann eine autorisierte Funktion eine Aggregation von Daten berechnen oder einen Tabellenwert abrufen und diesen Wert in einer Berechnung verwenden.

Weitere Informationen finden Sie unter Autorisierte Funktionen erstellen.

Beschreibungen zu UDFs hinzufügen

So fügen Sie einer UDF eine Beschreibung hinzu:

Console

  1. Rufen Sie in der Cloud Console die Seite „BigQuery“ auf.

    BigQuery aufrufen

  2. Maximieren Sie im Bereich Explorer Ihr Projekt und das Dataset und wählen Sie dann die Funktion aus.

  3. Klicken Sie im Bereich Details auf das Stiftsymbol neben Beschreibung, um den Beschreibungstext zu bearbeiten.

  4. Geben Sie im Dialogfeld eine Beschreibung in das Feld ein oder bearbeiten Sie die vorhandene Beschreibung. Klicken Sie auf Aktualisieren, um den neuen Beschreibungstext zu speichern.

Alternativ können Sie die Beschreibung mithilfe einer Standard-SQL-Abfrage mit dem Parameter description des Felds OPTIONS aktualisieren. Geben Sie im Feld Abfrageeditor Ihre Funktionsdefinition ein und fügen Sie die folgende Zeile hinzu:

OPTIONS (description="DESCRIPTION") AS """

Ersetzen Sie DESCRIPTION durch die Beschreibung, die Sie hinzufügen möchten.

bq

Mithilfe der bq query-Syntax aus UDFs und dem bq-Befehlszeilentool können Sie die Beschreibung einer Funktion über die Befehlszeile bearbeiten. Geben Sie die Standard-SQL mit dem Flag --nouse_legacy_sql oder -- use_legacy_sql=false an und geben Sie dann Ihre Funktionsdefinition ein. Fügen Sie Ihrer Definition die folgende Zeile hinzu, um den Parameter description im Feld OPTIONS festzulegen:

OPTIONS (description="DESCRIPTION") AS """

Ersetzen Sie DESCRIPTION durch die Beschreibung, die Sie hinzufügen möchten.

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, können beispielsweise fehlschlagen, wenn sie eingeschränkte Systemaufrufe ausführen.
  • 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 fünf 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 JavaSript-Funktion und ihre Ausgaben sind.
  • Bitweise Vorgänge in JavaScript verarbeiten nur die wichtigsten 32 Bit
  • Für UDFs gelten bestimmte Ratenbegrenzungen und Kontingentlimits. Weitere Informationen finden Sie unter UDF-Limits.

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 oder von einer logischen Ansicht auf eine persistente UDF verweisen, müssen Sie den Namen mit dem Dataset qualifizieren. Beispiel:
    CREATE FUNCTION mydataset.referringFunction() AS (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.
  • Ansichten und persistente UDFs können nicht auf temporäre UDFs verweisen.