Funzioni definite dall'utente
Una funzione definita dall'utente consente di creare una funzione utilizzando un prompt SQL o dal codice JavaScript. Una funzione definita dall'utente accetta colonne di input ed esegue azioni sull'input e restituisce il risultato di queste azioni come valore.
Puoi definire le funzioni definite dall'utente come permanenti o temporanee. Puoi riutilizzare funzioni definite dall'utente permanenti in più query, mentre le funzioni definite dall'utente temporanee esistono solo nell'ambito di una singola query.
Per creare una funzione definita dall'utente, utilizza
CREATE FUNCTION
l'Informativa. Per eliminare una funzione definita dall'utente permanente, utilizza il metodo
DROP FUNCTION
l'Informativa. Le funzioni definite dall'utente temporanee scadono al termine della query. L'istruzione DROP
FUNCTION
è supportata solo per le funzioni definite dall'utente temporanee in
query multi-istruzione e
procedure.
Per informazioni sulle funzioni definite dall'utente in SQL precedente, consulta Funzioni definite dall'utente in SQL precedente.
UDF SQL
L'esempio seguente crea una funzione SQL temporanea denominata AddFourAndDivide
e
chiama la funzione definita dall'interno di un'istruzione SELECT
:
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;
Questo esempio produce il seguente output:
+-----+-----+
| val | f0_ |
+-----+-----+
| 2 | 3.0 |
| 3 | 3.5 |
| 5 | 4.5 |
| 8 | 6.0 |
+-----+-----+
L'esempio successivo crea la stessa funzione di una funzione definita dall'utente permanente:
CREATE FUNCTION mydataset.AddFourAndDivide(x INT64, y INT64)
RETURNS FLOAT64
AS (
(x + 4) / y
);
Poiché questa funzione definita dall'utente è permanente, devi specificare un set di dati per la funzione
(mydataset
in questo esempio). Dopo aver eseguito l'istruzione CREATE FUNCTION
,
puoi richiamare la funzione da una query:
SELECT val, mydataset.AddFourAndDivide(val, 2) FROM UNNEST([2,3,5,8,12]) AS val;
Parametri UDF SQL basati su modelli
Un parametro con un tipo uguale a ANY TYPE
può corrispondere a più di un argomento
quando la funzione viene chiamata.
- Se più di un parametro è di tipo
ANY TYPE
, BigQuery non applicare qualsiasi relazione di tipo tra questi argomenti. - Il tipo restituito dalla funzione non può essere
ANY TYPE
. Deve essere omesso, ovvero l'intervallo di date determinato automaticamente in base asql_expression
, o di un tipo esplicito. - Passare gli argomenti delle funzioni di tipi incompatibili con la definizione della funzione restituisce un errore al momento della chiamata.
L'esempio seguente mostra una funzione SQL che utilizza un parametro basato su modelli.
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;
Questo esempio produce il seguente output:
+----------------+-----------------------+
| integer_input | floating_point_input |
+----------------+-----------------------+
| 1.75 | 1.7802547770700636 |
+----------------+-----------------------+
Il prossimo esempio utilizza un parametro basato su modelli per restituire l'ultimo elemento di un di qualsiasi tipo:
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 );
Questo esempio produce il seguente output:
+--------------+
| last_element |
+--------------+
| 13 |
+--------------+
Sottoquery scalari
Una funzione SQL UDF può restituire il valore di sottoquery scalabile. Una sottoquery scalare deve selezionare una singola colonna.
L'esempio seguente mostra una funzione SQL che utilizza una sottoquery scalare per contare numero di utenti con una determinata età in una tabella utenti:
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;
Questo esempio produce il seguente output:
+-------------------+-------------------+-------------------+
| count_user_age_10 | count_user_age_20 | count_user_age_30 |
+-------------------+-------------------+-------------------+
| 2 | 0 | 1 |
+-------------------+-------------------+-------------------+
Progetto predefinito nelle espressioni SQL
Nel corpo di una funzione SQL, qualsiasi riferimento alle entità BigQuery come tabelle o viste, deve includere l'ID progetto, a meno che l'entità non risieda nello stesso progetto che contiene la funzione definita dall'utente.
Ad esempio, considera la seguente affermazione:
CREATE FUNCTION project1.mydataset.myfunction() AS ( (SELECT COUNT(*) FROM mydataset.mytable) );
Se esegui questa istruzione da project1
e mydataset.mytable
esiste in
project1
, l'istruzione ha esito positivo. Tuttavia, se esegui questa istruzione
da un altro progetto, l'istruzione ha esito negativo. Per correggere l'errore,
includi l'ID progetto nel riferimento tabella:
CREATE FUNCTION project1.mydataset.myfunction() AS ( (SELECT COUNT(*) FROM project1.mydataset.mytable) );
Puoi anche fare riferimento a un'entità in un progetto o set di dati diverso quella in cui crei la funzione:
CREATE FUNCTION project1.mydataset.myfunction() AS ( (SELECT COUNT(*) FROM project2.another_dataset.another_table) );
Funzioni definite dall'utente JavaScript
Una funzione JavaScript definita dall'utente consente di chiamare il codice scritto in JavaScript da una query SQL. Le funzioni JavaScript definite dall'utente in genere consumano più risorse slot rispetto a SQL standard query, diminuendo le prestazioni dei job. Se la funzione può essere espresso in SQL, spesso è più ottimale eseguire il codice come un job di query SQL.
L'esempio seguente mostra una funzione JavaScript definita dall'utente. Il codice JavaScript è riportato tra virgolette all'interno di un stringa non elaborata.
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;
Questo esempio produce il seguente output:
+-----+-----+--------------+
| x | y | product |
+-----+-----+--------------+
| 1 | 5 | 5 |
| 2 | 10 | 20 |
| 3 | 15 | 45 |
+-----+-----+--------------+
L'esempio successivo somma i valori di tutti i campi denominati foo
nella stringa JSON specificata.
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;
L'esempio produce il seguente output:
+---------------------------------------------------------------------+---------+
| 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 |
+---------------------------------------------------------------------+---------+
Tipi di dati delle funzioni JavaScript definite dall'utente supportati
Alcuni tipi SQL hanno una mappatura diretta ai tipi JavaScript, mentre altri no. BigQuery rappresenta i tipi nel seguente modo:
Tipo di dati BigQuery | Tipo di dati JavaScript |
---|---|
ARRAY | ARRAY |
BOOL | BOOLEANO |
BYTES | STRING con codifica base64 |
FLOAT64 | NUMERO |
NUMERICO, BIGNUMERICO | Se un valore NUMERICO o BIGNUMERICO può essere rappresentato esattamente come un Virgola mobile IEEE 754 e non ha una parte frazionaria, il valore è codificato come numero. Questi sono compresi nell'intervallo [-253, 253]. In caso contrario, è codificato come stringa. |
STRINGA | STRINGA |
STRUCT | OBJECT dove ogni campo STRUCT è un campo denominato |
TIMESTAMP | DATE con un campo in microsecondi contenente microsecond
frazione del timestamp |
DATA | DATA |
JSON |
Gli OBJECT, gli ARRAY e i VALORI JSON vengono convertiti in codice JavaScript equivalente OGGETTI, ARRAY e VALORI. JavaScript non supporta i valori INT64. Solo numeri JSON nell'intervallo [-253, 253] vengono convertiti esattamente. Altrimenti, il valore viene arrotondato, il che potrebbe comportare una perdita di precisione. |
Poiché JavaScript non supporta un tipo di numero intero a 64 bit,
INT64
non è supportato come tipo di input per JavaScript
funzioni definite dall'utente. Utilizza invece FLOAT64
per rappresentare i numeri interi
valori come numero o STRING
per rappresentare un numero intero
come stringa.
BigQuery supporta INT64
come tipo restituito
nelle funzioni JavaScript definite dall'utente. In questo caso, il corpo della funzione JavaScript può restituire
un numero JavaScript o una stringa. BigQuery converte quindi uno dei due
questi tipi a INT64
.
Se il valore restituito della funzione JavaScript definita dall'utente è un
Promise
, BigQuery attende Promise
fino alla liquidazione di Promise
. Se il Promise
si pone in un
se completato, BigQuery restituisce il risultato. Se
Promise
diventa rifiutato, BigQuery restituisce
un errore.
Regole per le virgolette
Devi racchiudere il codice JavaScript tra virgolette. Per semplici snippet di codice di una riga, puoi utilizzare una stringa standard tra virgolette:
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;
Questo esempio produce il seguente output:
+-----------+-----------+
| val | result |
+-----------+-----------+
| 1 | 2.0 |
| 2 | 3.0 |
| 3 | 4.0 |
| 4 | 5.0 |
| 5 | 6.0 |
+-----------+-----------+
Se lo snippet contiene virgolette o è composto da più righe, utilizza blocchi tra virgolette:
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;
Questo esempio produce il seguente output:
+-----------------------+ | everyone | +-----------------------+ | Good Morning, Hannah! | | Good Morning, Max! | | Good Morning, Jakob! | +-----------------------+
Includi librerie JavaScript
Puoi estendere le funzioni JavaScript definite dall'utente utilizzando la sezione OPTIONS
.
Questa sezione consente di specificare librerie di codice esterne per la funzione definita dall'utente.
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');
Nell'esempio precedente, il codice in lib1.js
e lib2.js
è disponibile per qualsiasi codice nella sezione [external_code]
della funzione definita dall'utente.
Best practice per le funzioni JavaScript definite dall'utente
Prefiltra l'input
Se l'input può essere filtrato prima di essere passato a un codice JavaScript funzione definita dall'utente, la query potrebbe essere più veloce e più economica.
Evita lo stato modificabile permanente
Non archiviare o accedere allo stato modificabile nelle chiamate delle funzioni JavaScript definite dall'utente. Per ad esempio, evita il seguente pattern:
-- Avoid this pattern CREATE FUNCTION temp.mutable() RETURNS INT64 LANGUAGE js AS r""" var i = 0; // Mutable state function dontDoThis() { return ++i; } return dontDoThis() """;
Utilizzare la memoria in modo efficiente
L'ambiente di elaborazione JavaScript ha una memoria disponibile limitata per query. Le query delle funzioni JavaScript definite dall'utente che accumulano troppi stati locali potrebbero non riuscire a causa esaurimento della memoria.
Autorizza routine
Puoi autorizzare le funzioni definite dall'utente come routine. Le routine autorizzate ti consentono di condividere i risultati delle query con utenti o gruppi specifici senza concedere loro l'accesso alle tabelle sottostanti che hanno generato i risultati. Ad esempio, una routine autorizzata può calcolare un'aggregazione sui dati o cercare il valore di una tabella e utilizzarlo in un calcolo. Per maggiori informazioni, vedi Routine autorizzate.
Aggiungere descrizioni alle funzioni definite dall'utente
Per aggiungere una descrizione a una funzione definita dall'utente, segui questi passaggi:
Console
Vai alla pagina BigQuery nella console Google Cloud.
Nel riquadro Explorer, espandi il progetto e il set di dati, quindi seleziona la funzione.
Nel riquadro Dettagli, fai clic su
Modifica routine Dettagli, per modificare il testo della descrizione.Nella finestra di dialogo, inserisci una descrizione nella casella o modifica il campo o l'audiodescrizione. Fai clic su Salva per salvare il nuovo testo della descrizione.
SQL
Per aggiornare la descrizione di una funzione, ricreala utilizzando
L'istruzione DDL CREATE FUNCTION
e imposta il campo description
nell'elenco OPTIONS
:
Nella console Google Cloud, vai alla pagina BigQuery.
Nell'editor query, inserisci la seguente istruzione:
CREATE OR REPLACE FUNCTION mydataset.my_function(...) AS ( ... ) OPTIONS ( description = 'DESCRIPTION' );
Fai clic su
Esegui.
Per ulteriori informazioni su come eseguire le query, vedi Eseguire una query interattiva.
Creare routine di mascheramento personalizzate
Puoi creare funzioni definite dall'utente da utilizzare con routine di mascheramento personalizzate. Le routine di mascheramento personalizzate devono soddisfare i seguenti requisiti:
- La routine di mascheramento personalizzata deve essere una funzione SQL UDF.
- Nella funzione
OPTIONS
, l'opzionedata_governance_type
deve essere impostata suDATA_MASKING
. - Le routine di mascheramento personalizzate supportano le seguenti funzioni:
REGEXP_REPLACE
funzione stringaFARM_FINGERPINT
funzione hashMD5
funzione hashSHA1
funzione hashSHA256
funzione hashSHA512
funzione hashCAST
funzione di conversioneCONCAT
funzione stringaREPLACE
funzione stringaREGEX_EXTRACT
funzione stringaSUBSTRING
funzione stringaTO_BASE32
funzione stringaTO_BASE64
funzione stringaFROM_BASE32
funzione stringaFROM_BASE64
funzione stringaTO_HEX
funzione stringaFROM_HEX
funzione stringaGENERATE_UUID
funzione di utilitàCURRENT_DATE
funzione dataCURRENT_DATETIME
funzione data/oraCURRENT_TIME
funzione temporaleCURRENT_TIMESTAMP
funzione timestamp- Funzione di conversione di
SAFE_CAST
- Funzione stringa
LENGTH
- Funzione stringa
STARTS_WITH
KEYS.KEYSET_CHAIN
Funzione di crittografia AEADAEAD.ENCRYPT
Funzione di crittografia AEAD con keyset_chain (utilizzo di chiave non elaborata non supportato)AEAD.DECRYPT_BYTES
Funzione di crittografia AEAD conKEYS.KEYSET_CHAIN
(utilizzo di chiave non elaborata non supportato)AEAD.DECRYPT_STRING,
Funzione di crittografia AEAD conKEYS.KEYSET_CHAIN
(utilizzo di chiave non elaborata non supportato)DETERMINISTIC_ENCRYPT
Funzione di crittografia AEAD conKEYS.KEYSET_CHAIN
(utilizzo di chiave non elaborata non supportato)DETERMINISTIC_DECRYPT_BYTES
Funzione di crittografia AEAD conKEYS.KEYSET_CHAIN
(utilizzo di chiave non elaborata non supportato)DETERMINISTIC_DECRYPT_STRING
Funzione di crittografia AEAD conKEYS.KEYSET_CHAIN
(utilizzo di chiave non elaborata non supportato)
- Le routine di mascheramento personalizzate possono accettare nessun input o un solo input all'interno
Tipi di dati BigQuery,
ad eccezione di
GEOGRAPHY
eSTRUCT
.GEOGRAPHY
eSTRUCT
sono non supportato per le routine di mascheramento personalizzate. - Parametri UDF SQL basati su modelli non sono supportati.
- Quando viene fornito un input, i tipi di dati di input e di output devono essere gli stessi.
- È necessario specificare un tipo di output.
- Non è possibile fare riferimento ad altre funzioni definite dall'utente, sottoquery, tabelle o viste nel del testo della definizione.
- Dopo aver creato una routine di mascheramento, non è possibile impostare una routine
funzione standard. Ciò significa che se l'opzione
data_governance_type
è Impostato suDATA_MASKING
, non puoi modificaredata_governance_type
utilizzando Istruzioni DDL o chiamate API.
Ad esempio, una routine di mascheramento che sostituisce il codice fiscale di un utente.
con XXX-XX-XXXX
potrebbe avere il seguente aspetto:
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
);
L'esempio seguente esegue l'hashing con il valore salt fornito dall'utente, utilizzando il metodo
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') );
L'esempio seguente maschera una colonna DATETIME
con un valore costante:
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) );
Come best practice, usa le
SAFE
se possibile per evitare di esporre i dati non elaborati attraverso messaggi di errore.
Dopo aver creato la routine di mascheramento personalizzata, questa è disponibile come regola di mascheramento in Crea criteri relativi ai dati.
Funzioni offerte dalla community
Le funzioni definite dall'utente fornite dalla community sono disponibili in
bigquery-public-data.persistent_udfs
set di dati pubblico e open source
bigquery-utils
Repository GitHub.
Puoi vedere tutte le
UDF della community
nella console Google Cloud aggiungendo a Speciali
il progetto bigquery-public-data
nel riquadro Explorer e quindi
espandendo il set di dati persistent_udfs
nidificato all'interno del progetto.
Se vuoi contribuire alle funzioni definite dall'utente in questo repository, consulta Funzionalità definite dall'utente che contribuiscono per istruzioni.
Limitazioni
Le seguenti limitazioni si applicano alle funzioni temporanee e permanenti definite dall'utente:
- Gli oggetti DOM
Window
,Document
eNode
e le funzioni che le richiedono non sono supportate. - Le funzioni JavaScript che si basano su codice nativo possono non riuscire, ad esempio se effettuano chiamate di sistema limitate.
- Una funzione JavaScript definita dall'utente può scadere e impedire il completamento della query. Timeout. può durare fino a 5 minuti, ma può variare in base a diversi fattori. incluse la quantità di tempo di CPU dell'utente consumata dalla funzione e le dimensioni di input e output alla funzione JavaScript.
- Le operazioni bit per bit in JavaScript gestiscono solo i 32 bit più significativi.
- Le funzioni definite dall'utente sono soggette a determinati limiti di quota e di frequenza. Per ulteriori informazioni, consulta i limiti delle funzioni definite dall'utente.
Le seguenti limitazioni si applicano alle funzioni permanenti definite dall'utente:
- Ogni set di dati può contenere solo una funzione definita dall'utente permanente con lo stesso nome. Tuttavia, puoi creare una funzione definita dall'utente il cui nome corrisponde a quello di una nello stesso set di dati.
- Quando si fa riferimento a una funzione definita dall'utente permanente da un'altra funzione definita dall'utente permanente o
logica, devi qualificare il nome con il set di dati. Ad esempio:
CREATE FUNCTION mydataset.referringFunction() AS (mydataset.referencedFunction());
Le seguenti limitazioni si applicano alle funzioni temporanee definite dall'utente.
- Quando crei una funzione definita dall'utente temporanea,
function_name
non può contenere cicli. - Le visualizzazioni e le funzioni definite dall'utente permanenti non possono fare riferimento a funzioni definite dall'utente temporanee.