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 vonsql_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. JavaScript unterstützt keine INT64-Werte. 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
Rufen Sie in der Google Cloud Console die Seite "BigQuery" auf.
Maximieren Sie im Bereich Explorer Ihr Projekt und das Dataset und wählen Sie dann die Funktion aus.
Klicken Sie im Bereich Details auf
Routinedetails bearbeiten, um den Beschreibungstext zu bearbeiten.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:
Öffnen Sie in der Google Cloud Console die Seite BigQuery.
Geben Sie im Abfrageeditor die folgende Anweisung ein:
CREATE OR REPLACE FUNCTION mydataset.my_function(...) AS ( ... ) OPTIONS ( description = 'DESCRIPTION' );
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 Optiondata_governance_type
aufDATA_MASKING
festgelegt sein. - Benutzerdefinierte Maskierungsroutinen unterstützen die folgenden Funktionen:
REGEXP_REPLACE
StringfunktionFARM_FINGERPINT
-Hash-FunktionMD5
-Hash-FunktionSHA1
-Hash-FunktionSHA256
-Hash-FunktionSHA512
-Hash-FunktionCAST
-KonversionsfunktionCONCAT
StringfunktionREPLACE
StringfunktionREGEX_EXTRACT
StringfunktionSUBSTRING
StringfunktionTO_BASE32
StringfunktionTO_BASE64
StringfunktionFROM_BASE32
StringfunktionFROM_BASE64
StringfunktionTO_HEX
StringfunktionFROM_HEX
StringfunktionGENERATE_UUID
DienstfunktionCURRENT_DATE
DATE-FunktionCURRENT_DATETIME
DATETIME-FunktionCURRENT_TIME
TIME-FunktionCURRENT_TIMESTAMP
-Zeitstempelfunktion
- Benutzerdefinierte Maskierungsroutinen können mit Ausnahme von
GEOGRAPHY
undSTRUCT
entweder keine Eingaben oder genau eine Eingabe innerhalb von BigQuery-Datentypen akzeptieren.GEOGRAPHY
undSTRUCT
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
aufDATA_MASKING
gesetzt ist, können Siedata_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
);
Die folgenden Beispiel-Hashes mit vom Nutzer bereitgestelltem salt mithilfe der Funktion SHA256
:
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 maskiert:
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 die Offenlegung von Rohdaten durch Fehlermeldungen zu vermeiden.
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
undNode
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.