Funzioni definite dall'utente

Una funzione definita dall'utente dall'utente consente di creare una funzione utilizzando un'espressione SQL o un codice JavaScript. Una funzione definita dall'utente accetta colonne di input, 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 le funzioni definite dall'utente permanenti per 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 l'istruzione CREATE FUNCTION. Per eliminare una funzione definita dall'utente permanente;utente, utilizza l'istruzione DROP FUNCTION. Le funzioni definite dall'utente temporanee scadono al termine della query. L'istruzione DROP FUNCTION è supportata solo per le funzioni definite dall'utente temporanee nelle query con più istruzioni e nelle procedure.

Per informazioni sulle funzioni definite dall'utente nell'SQL precedente, consulta Funzioni definite dall'utente nell'SQL precedente.

UDF SQL

L'esempio seguente crea una funzione definita dall'utente SQL temporanea denominata AddFourAndDivide e richiama la funzione definita dall'interno da 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 chiamare la funzione da una query:

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

Parametri delle funzioni definite dall'utente SQL basate su modelli

Un parametro con tipo uguale a ANY TYPE può corrispondere a più di un tipo di argomento quando viene chiamata la funzione.

Se più parametri sono di tipo ANY TYPE, BigQuery non applica alcuna relazione di tipo tra questi argomenti.
Il tipo restituito della funzione non può essere ANY TYPE. Deve essere omesso, il che significa essere determinato automaticamente in base a sql_expression, oppure un tipo esplicito.
Se vengono passati gli argomenti della funzione di tipi incompatibili con la definizione della funzione, verrà restituito un errore al momento della chiamata.

L'esempio seguente mostra una funzione SQL UDF 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    |
+----------------+-----------------------+

L'esempio successivo utilizza un parametro basato su modelli per restituire l'ultimo elemento di un array 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 definita dall'utente SQL può restituire il valore di una sottoquery scalabile. Una sottoquery scalare deve selezionare una singola colonna.

L'esempio seguente mostra una funzione definita dall'utente SQL che utilizza una sottoquery scalare per contare il 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 definita dall'utente SQL, tutti i riferimenti a entità BigQuery, come tabelle o viste, devono includere l'ID progetto, a meno che l'entità non si trovi 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 viene eseguita correttamente. Tuttavia, se esegui questa istruzione da un altro progetto, l'istruzione non va a buon fine. Per correggere l'errore, includi l'ID progetto nel riferimento della 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 da quello in cui crei la funzione:

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

UDF JavaScript

Una funzione JavaScript definita dall'utente consente di chiamare codice scritto in JavaScript da una query SQL. Le funzioni definite dall'utente JavaScript consumano in genere più risorse slot rispetto alle query SQL standard, riducendo le prestazioni dei job. Se la funzione può essere espressa in SQL, spesso è più ottimale eseguire il codice come job di query SQL standard.

L'esempio seguente mostra una funzione JavaScript definita dall'utente. Il codice JavaScript è citato in una 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 di SQL hanno una mappatura diretta ai tipi di JavaScript, ma altri no. BigQuery rappresenta i tipi nel seguente modo:

Tipo di dati BigQuery	Tipo di dati JavaScript
ARRAY	ARRAY
BOOL	BOOLEANO
BYTES	STRING codificata in Base64
FLOAT64	NUMERO
NUMERICO, GRANDE NUMERO	Se un valore NUMERIC o BIGNUMERIC può essere rappresentato esattamente come un valore a virgola mobile IEEE 754 e non ha parte frazionaria, il valore viene codificato come numero. Questi valori sono compresi nell'intervallo [-2⁵³, 2⁵³]. In caso contrario, il valore viene codificato come stringa.
STRING	STRING
STRUCT	OBJECT dove ogni campo STRUCT è un campo denominato
TIMESTAMP	DATE con un campo in microsecondi contenente la frazione `microsecond` del timestamp
DATA	DATA
JSON	OBJECTS, ARRAY e VALORI JSON vengono convertiti in OBJECTS JavaScript, ARRAYS e VALORI equivalenti. JavaScript non supporta i valori INT64. Solo i numeri JSON nell'intervallo [-2⁵³, 2⁵³] vengono convertiti esattamente. In caso contrario, il valore numerico viene arrotondato, il che potrebbe comportare una perdita di precisione.

Poiché JavaScript non supporta un tipo intero a 64 bit, INT64 non è supportato come tipo di input per le funzioni JavaScript definite dall'utente. Utilizza invece FLOAT64 per rappresentare i valori interi come un numero oppure STRING per rappresentare i valori interi 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 di questi tipi in INT64.

Se il valore restituito della funzione JavaScript definita dall'utente è Promise, BigQuery attende l'Promise fino a quando Promise non viene saldato. Se lo stato di Promise è soddisfatto, BigQuery restituisce il risultato. Se lo stato di Promise è rifiutato, BigQuery restituisce un errore.

Regole per le quote

Il codice JavaScript deve essere racchiuso tra virgolette. Per snippet di codice semplici con una sola riga, puoi usare 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       |
+-----------+-----------+

Nei casi in cui lo snippet contiene virgolette o è costituito da più righe, utilizza blocchi con virgolette triple:

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

Prefiltrare l'input

Se l'input può essere filtrato prima di essere trasmesso a una funzione JavaScript definita dall'utente, la query potrebbe essere più veloce ed economica.

Evitare uno stato modificabile permanente

Non archiviare o accedere allo stato modificabile nelle chiamate della funzione JavaScript definita dall'utente. 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 limitata per query. Le query della funzione JavaScript definita dall'utente che accumulano troppi stati locali potrebbero non riuscire a causa dell'esaurimento della memoria.

Autorizza routine

Puoi autorizzare le funzioni definite dall'utente come routine. Le routine autorizzate 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 un valore di tabella e utilizzarlo in un calcolo. Per saperne di più, consulta Routine autorizzate.

Aggiungere descrizioni alle funzioni definite dall'utente

Per aggiungere una descrizione a una funzione definita dall'utente:

Console

Vai alla pagina BigQuery nella console Google Cloud.

Vai a BigQuery
Nel riquadro Explorer, espandi il progetto e il set di dati, quindi seleziona la funzione.
Nel riquadro Dettagli, fai clic su Modifica dettagli routine per modificare il testo della descrizione.
Nella finestra di dialogo, inserisci una descrizione o modifica la descrizione esistente. 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.

Vai a 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, consulta Eseguire una query interattiva.

Crea routine di mascheramento personalizzate

Puoi creare funzioni definite dall'utente da utilizzare con le routine di mascheramento personalizzate. Le routine di mascheramento personalizzate devono soddisfare i seguenti requisiti:

La routine di mascheramento personalizzata deve essere una funzione definita dall'utente SQL.
Nella funzione OPTIONS, l'opzione data_governance_type deve essere impostata su DATA_MASKING.
Le routine di mascheramento personalizzate supportano le seguenti funzioni:
- REGEXP_REPLACE funzione stringa
- FARM_FINGERPINT funzione hash
- MD5 funzione hash
- SHA1 funzione hash
- SHA256 funzione hash
- SHA512 funzione hash
- CAST funzione di conversione
- CONCAT funzione stringa
Le routine di mascheramento personalizzate possono accettare nessun input o un solo input nei tipi di dati di BigQuery, ad eccezione di GEOGRAPHY e STRUCT. GEOGRAPHY e STRUCT non sono supportati per le routine di mascheramento personalizzate.
I parametri delle funzioni definite dall'utente SQL basate su modelli non sono supportati.
Se viene fornito un input, i tipi di dati di input e di output devono essere uguali.
È necessario specificare un tipo di output.
Non è possibile fare riferimento ad altre funzioni definite dall'utente, sottoquery, tabelle o viste nel corpo della definizione.
Dopo aver creato una routine di mascheramento, non è possibile modificarla in una funzione standard. Ciò significa che se l'opzione data_governance_type è impostata su DATA_MASKING, non puoi modificare data_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 la funzione 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)
);

Dopo aver creato la routine di mascheramento personalizzata, è disponibile come regola di mascheramento in Crea criteri relativi ai dati.

Funzioni aggiunte dalla community

Le funzioni definite dall'utente fornite dalla community sono disponibili nel set di dati pubblico bigquery-public-data.persistent_udfs e nel repository GitHub di bigquery-utils open source. Puoi visualizzare tutte le UDF della community nella console Google Cloud aggiungendo a Speciali il progetto bigquery-public-data nel riquadro Explorer, quindi espandendo il set di dati persistent_udfs nidificato all'interno di quel progetto.

Se vuoi contribuire alle funzioni definite dall'utente in questo repository, consulta la pagina relativa al contributo delle funzioni definite dall'utente per le istruzioni.

Limitazioni

Le seguenti limitazioni si applicano alle funzioni temporanee e permanenti definite dall'utente:

Gli oggetti DOM Window, Document e Node, così come le funzioni che li richiedono, non sono supportati.
Le funzioni JavaScript che si basano sul codice nativo possono avere esito negativo, ad esempio se effettuano chiamate di sistema limitate.
Una funzione JavaScript definita dall'utente può scadere e impedire il completamento della query. I timeout possono essere anche di 5 minuti, ma possono variare in base a diversi fattori, tra cui la quantità di tempo di CPU dell'utente utilizzata dalla funzione e le dimensioni degli input e degli output per la 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 frequenza e di quota. Per ulteriori informazioni, consulta la sezione Limiti delle funzioni definite dall'utente.

Le seguenti limitazioni si applicano alle funzioni permanenti definite dall'utente:

Ogni set di dati può contenere una sola 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 tabella nello stesso set di dati.
Quando fai riferimento a una funzione definita dall'utente permanente dall'utente permanente da un'altra funzione funzione definita dall'utente permanente dall'utente o da una visualizzazione 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 punti.
Le viste e le funzioni definite dall'utente permanenti non possono fare riferimento a funzioni definite dall'utente temporanee.