Benutzerdefinierte 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 Abfragen mit mehreren Anweisungen 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 die UDF 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_input,
  addFourAndDivideAny(1.59, 3.14) AS floating_point_input;

Dieses Beispiel liefert folgende Ausgabe:

+----------------+-----------------------+
| integer_input  |  floating_point_input |
+----------------+-----------------------+
| 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 AS id, 10 AS age
  UNION ALL
  SELECT
    2 AS id, 30 AS age
  UNION ALL
  SELECT
    3 AS id, 10 AS age
);

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 UDF 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. JavaScript-UDFs nutzen normalerweise mehr Slot-Ressourcen im Vergleich zu Standard-SQL-Abfragen, wodurch die Jobleistung sich verringert. Wenn die Funktion in SQL ausgedrückt werden kann, ist es oft besser, den Code als Standard-SQL-Abfragejob auszuführen.

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 wird der Wert 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
JSON

JSON-OBJEKTE, -ARRAYS und -WERTE werden in entsprechende JavaScript-OBJEKTE, -ARRAYS und -WERTE konvertiert.

INT64-Werte werden in JavaScript nicht unterstützt. Nur JSON-Zahlen im Bereich [-253, 253] werden genau konvertiert. Andernfalls wird der numerische Wert gerundet, was zu einem Genauigkeitsverlust führen kann.

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 Promise erfüllt ist. Wenn das Promise einen erfüllten Status erreicht, gibt BigQuery das Ergebnis zurück. Wenn das Promise einen abgelehnten Status erreicht, 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;

Dieses Beispiel liefert folgende Ausgabe:

+-----------+-----------+
| val       | result    |
+-----------+-----------+
| 1         | 2.0       |
| 2         | 3.0       |
| 3         | 4.0       |
| 4         | 5.0       |
| 5         | 6.0       |
+-----------+-----------+

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;

Dieses Beispiel liefert folgende Ausgabe:

+-----------------------+
| everyone              |
+-----------------------+
| Good Morning, Hannah! |
| Good Morning, Max!    |
| Good Morning, Jakob!  |
+-----------------------+

JavaScript-Bibliotheken einschließen

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 gefiltert werden kann, bevor sie an eine JavaScript-UDF übergeben wird, kann die Abfrage schneller und günstiger ausgeführt werden.

Persistenten änderbaren Status vermeiden

Speichern oder rufen Sie keinen änderbaren Status über JavaScript-UDF-Aufrufe hinweg auf. Vermeiden Sie beispielsweise das folgende Muster:

-- Avoid this pattern
CREATE FUNCTION temp.mutable()
RETURNS INT64
LANGUAGE js
AS r"""
  var i = 0; // Mutable state
  function dontDoThis() {
    return ++i;
  }
  return dontDoThis()
""";

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.

Routinen autorisieren

Sie können UDFs als Routinen autorisieren. Mit autorisierten Routinen können Sie Abfrageergebnisse für bestimmte Nutzer oder Gruppen freigeben, ohne ihnen Zugriff auf die zugrunde liegenden Tabellen zu gewähren, mit denen die Ergebnisse generiert wurden. Beispielsweise kann eine autorisierte Routine eine Aggregation von Daten berechnen oder einen Tabellenwert abrufen und diesen Wert in einer Berechnung verwenden. Weitere Informationen finden Sie unter Autorisierte Routinen.

Beschreibungen zu UDFs hinzufügen

So fügen Sie einer UDF eine Beschreibung hinzu:

Console

  1. Rufen Sie in der Google 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 Routinedetails bearbeiten, 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 Speichern, um den neuen Beschreibungstext zu speichern.

SQL

Wenn Sie die Beschreibung einer Funktion aktualisieren möchten, erstellen Sie die Funktion mithilfe der DDL-Anweisung CREATE FUNCTION neu und legen das Feld description in der OPTIONS-Liste fest:

  1. Öffnen Sie in der Google Cloud Console die Seite BigQuery.

    BigQuery aufrufen

  2. Geben Sie im Abfrageeditor die folgende Anweisung ein:

    CREATE OR REPLACE FUNCTION mydataset.my_function(...)
AS (
  ...
) OPTIONS (
  description = 'DESCRIPTION'
);

  3. Klicken Sie auf Ausführen.

Informationen zum Ausführen von Abfragen finden Sie unter Interaktive Abfrage ausführen.

Benutzerdefinierte Maskierungsroutinen erstellen

Sie können UDFs für die Verwendung mit benutzerdefinierten Maskierungsroutinen erstellen. Benutzerdefinierte Maskierungsroutinen müssen die folgenden Anforderungen erfüllen:

  • Die benutzerdefinierte Maskierungsroutine muss eine SQL-UDF sein.
  • In der Funktion OPTIONS muss die Option data_governance_type auf DATA_MASKING festgelegt sein.
  • Benutzerdefinierte Maskierungsroutinen unterstützen die folgenden Funktionen:
  • Benutzerdefinierte Maskierungsroutinen können mit Ausnahme von GEOGRAPHY und STRUCT entweder keine Eingaben oder genau eine Eingabe innerhalb von BigQuery-Datentypen akzeptieren. GEOGRAPHY und STRUCT werden für benutzerdefinierte Maskierungsroutinen nicht unterstützt.
  • Vorlagen-SQL-UDF-Parameter werden nicht unterstützt.
  • Wenn eine Eingabe bereitgestellt wird, müssen die Eingabe- und Ausgabedatentypen identisch sein.
  • Es muss ein Ausgabetyp angegeben werden.
  • Auf keine anderen UDFs, Unterabfragen, Tabellen oder Ansichten kann im Definitionstext verwiesen werden.
  • Nachdem eine Maskierungsroutine erstellt wurde, kann sie nicht mehr in eine Standardfunktion geändert werden. Wenn also die Option data_governance_type auf DATA_MASKING gesetzt ist, können Sie data_governance_type nicht mit DDL-Anweisungen oder API-Aufrufen ändern.

Eine Maskierungsroutine, die die Sozialversicherungsnummer eines Nutzers durch XXX-XX-XXXX ersetzt, könnte beispielsweise so aussehen:

  CREATE OR REPLACE FUNCTION SSN_Mask(ssn STRING) RETURNS STRING
  OPTIONS (data_governance_type="DATA_MASKING") AS (
  SAFE.REGEXP_REPLACE(ssn, '[0-9]', 'X') # 123-45-6789 -> XXX-XX-XXXX
  );

Im folgenden Beispiel wird mit der Funktion SHA256 ein Hashwert mit einem vom Nutzer bereitgestellten Salt berechnet:

CREATE OR REPLACE FUNCTION `project.dataset.masking_routine1`(
  ssn STRING)
RETURNS STRING OPTIONS (data_governance_type = 'DATA_MASKING')
AS (
  CAST(SHA256(CONCAT(ssn, 'salt')) AS STRING format 'HEX')
);

Im folgenden Beispiel wird eine DATETIME-Spalte mit einem konstanten Wert ausgeblendet:

CREATE OR REPLACE FUNCTION `project.dataset.masking_routine2`(
  column DATETIME)
RETURNS DATETIME OPTIONS (data_governance_type = 'DATA_MASKING')
AS (
  SAFE_CAST('2023-09-07' AS DATETIME)
);

Verwenden Sie nach Möglichkeit das Präfix SAFE, um zu vermeiden, dass Rohdaten in Fehlermeldungen preisgegeben werden.

Nachdem Sie die benutzerdefinierte Maskierungsroutine erstellt haben, ist sie unter Datenrichtlinien erstellen als Maskierungsregel verfügbar.

Funktionen aus Communitybeiträgen

Von der Community bereitgestellte UDFs sind im öffentlichen Dataset bigquery-public-data.persistent_udfs und im Open Source bigquery-utils GitHub-Repository verfügbar. Sie sehen alle Community-UDFs in der Google Cloud Console, indem Sie das bigquery-public-data Projekt im Explorer-Bereich markieren und dann das verschachtelte persistent_udfs-Dataset innerhalb dieses Projekts maximieren.

Wenn Sie zu den UDFs in diesem Repository beitragen möchten, finden Sie Anleitungen unter Zu UDFs beitragen.

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.