Funzioni definite dall'utente

Una funzione definita dall'utente dall'utente consente di creare una funzione utilizzando un'espressione SQL o il codice JavaScript. Una funzione definita dall'utente accetta colonne di input, esegue azioni sull'input e restituisce il risultato di queste azioni sotto forma di un valore.

Puoi definire le funzioni definite dall'utente come permanenti o temporanee. Puoi riutilizzare le 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 UDF, utilizza l'istruzione CREATE FUNCTION. Per eliminare una funzione definita dall'utente permanente, 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 in SQL precedente, consulta Funzioni definite dall'utente in SQL precedente.

UDF SQL

L'esempio seguente crea una funzione definita dall'utente SQL temporanea denominata AddFourAndDivide e chiama la funzione definita dall'utente 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 come funzione 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 è persistente, 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 UDF SQL basati su modelli

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

  • Se più di un parametro ha il tipo ANY TYPE, BigQuery non impone alcuna relazione di tipo tra questi argomenti.
  • Il tipo restituito dalla funzione non può essere ANY TYPE. Deve essere omesso, il che significa che deve essere determinato automaticamente in base a sql_expression, o un tipo esplicito.
  • Il passaggio degli argomenti della funzione di tipi incompatibili con la definizione della funzione genera un errore al momento della chiamata.

L'esempio seguente mostra una UDF SQL che utilizza un parametro basato su modello.

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 un modello 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 subquery scalare. Una sottoquery scalare deve selezionare una singola colonna.

Il seguente esempio mostra una UDF SQL che utilizza una sottoquery scalare per conteggiare il numero di utenti con una determinata età in una tabella utente:

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 alle 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 ha esito positivo. Tuttavia, se esegui questa istruzione da un progetto diverso, l'istruzione non va a buon fine. Per correggere l'errore, includi l'ID progetto nel riferimento alla tabella:

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

Puoi anche fare riferimento a un'entità in un progetto o un 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)
);

Funzioni definite dall'utente JavaScript

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

Il seguente esempio mostra una UDF JavaScript. Il codice JavaScript è racchiuso all'interno di 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 supportati per le funzioni JavaScript definite dall'utente

Alcuni tipi SQL hanno un mapping diretto ai tipi 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 con codifica Base64
FLOAT64 NUMERO
NUMERIC, BIGNUMERIC Se un valore NUMERIC o BIGNUMERIC può essere rappresentato esattamente come un valore IEEE 754 in virgola mobile e non ha una parte frazionaria, il valore viene codificato come numero. Questi valori sono compresi nell'intervallo [-253, 253]. In caso contrario, il valore viene codificato come stringa.
STRING STRING
STRUCT OBJECT in cui ogni campo STRUCT è un campo denominato
TIMESTAMP DATE con un campo di microsecondi contenente la microsecond frazione del timestamp
DATA DATA
JSON

Gli oggetti, gli array e i valori JSON vengono convertiti in oggetti, array e valori JavaScript equivalenti.

JavaScript non supporta i valori INT64. Solo i numeri JSON nell'intervallo [-253, 253] 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 di numero intero a 64 bit, INT64 non è supportato come tipo di input per le UDF JavaScript. Utilizza invece FLOAT64 per rappresentare i valori interi come numero o STRING per rappresentare i valori interi come stringa.

BigQuery supporta INT64 come tipo restituito nelle UDF JavaScript. In questo caso, il corpo della funzione JavaScript può restituire un numero o una stringa JavaScript. BigQuery converte quindi uno di questi tipi in INT64.

Se il valore restituito della UDF JavaScript è un Promise, BigQuery attende che Promise venga risolto.Promise Se Promise passa allo stato completato, BigQuery restituisce il risultato. Se la Promise passa allo stato rifiutato, BigQuery restituisce un errore.

Regole per i preventivi

Devi racchiudere il codice JavaScript tra virgolette. Per gli snippet di codice di una riga, puoi utilizzare una stringa tra virgolette standard:

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 tra triple 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 tue UDF JavaScript utilizzando la sezione OPTIONS. Questa sezione consente di specificare librerie di codice esterne per la UDF.

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 UDF.

Best practice per le funzioni JavaScript definite dall'utente

Prefiltrare l'input

Se l'input può essere filtrato prima di essere passato a una UDF JavaScript, la query potrebbe essere più veloce ed economica.

Evitare lo stato mutabile persistente

Non memorizzare o accedere allo stato modificabile tra le chiamate alla 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 disponibile per query. Le query UDF JavaScript che accumulano troppo stato locale potrebbero non riuscire a causa dell'esaurimento della memoria.

Autorizzare le routine

Puoi autorizzare le UDF 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 un valore di tabella e utilizzarlo in un calcolo. Per ulteriori informazioni, vedi Routine autorizzate.

Aggiungere descrizioni alle UDF

Per aggiungere una descrizione a una UDF:

Console

  1. Vai alla pagina BigQuery nella console Google Cloud .

    Vai a BigQuery

  2. Nel riquadro Explorer, espandi il progetto e il set di dati, quindi seleziona la funzione.

  3. Nel riquadro Dettagli, fai clic su Modifica dettagli routine per modificare il testo della descrizione.

  4. Nella finestra di dialogo, inserisci una descrizione nella casella o modifica quella 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:

  1. Nella console Google Cloud , vai alla pagina BigQuery.

    Vai a BigQuery

  2. Nell'editor di query, inserisci la seguente istruzione:

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

  3. Fai clic su Esegui.

Per maggiori informazioni su come eseguire le query, consulta Eseguire una query interattiva.

Creare routine di mascheramento personalizzate

Puoi creare UDF da utilizzare con routine di mascheramento personalizzate. Devi creare set di dati dedicati e configurare le autorizzazioni IAM appropriate per gestire le UDF di mascheramento. Le routine di mascheramento personalizzate devono soddisfare i seguenti requisiti:

  • La routine di mascheramento personalizzata deve essere una UDF SQL.
  • Nella funzione OPTIONS, l'opzione data_governance_type deve essere impostata su DATA_MASKING.
  • Le routine di mascheramento personalizzate supportano le seguenti funzioni:
  • Le routine di mascheramento personalizzate possono accettare nessun input o un input all'interno dei tipi di dati BigQuery, ad eccezione di GEOGRAPHY e STRUCT. GEOGRAPHY e STRUCT non sono supportati per le routine di mascheramento personalizzate.
  • I parametri UDF SQL basati su modelli non sono supportati.
  • Quando viene fornito un input, i tipi di dati di input e output devono essere uguali.
  • È necessario fornire un tipo di output.
  • Non è possibile fare riferimento ad altre UDF, 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.
  • Le routine di mascheramento personalizzate supportano le istruzioni CASE e CASE expr. I seguenti operatori possono essere utilizzati con le istruzioni CASE e CASE expr:

Ad esempio, una routine di mascheramento che sostituisce il numero di previdenza sociale 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
  );

I seguenti hash di esempio con sale 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)
);

Come best practice, utilizza il prefisso SAFE ove possibile per evitare di esporre dati non elaborati tramite messaggi di errore.

Dopo aver creato la routine di mascheramento personalizzata, questa è disponibile come regola di mascheramento in Crea criteri per i dati.

Funzioni create dalla community

Le UDF con il contributo della community sono disponibili nel dataset pubblico bigquery-public-data.persistent_udfs e nel repository GitHub 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 questo progetto.

Consenti l'accesso alle funzioni con il contributo della community all'interno di un perimetro di Controlli di servizio VPC

Per i progetti in cui sono attivi i controlli di servizio VPC e BigQuery è un servizio protetto, devi definire una regola di uscita per il progetto bigquery-public-data (ID progetto: 1057666841514).

Questa regola deve abilitare le seguenti operazioni:

  • bigquery.routines.get (per l'utilizzo delle routine)
  • bigquery.tables.getData (per l'esecuzione di query sulle tabelle BigQuery)

Il seguente codice mostra un esempio di configurazione YAML:

  - egressFrom:
      identityType: ANY_IDENTITY
    egressTo:
      operations:
      - serviceName: 'bigquery.googleapis.com'
        methodSelectors:
        - permission: 'bigquery.routines.get'
        - permission: 'bigquery.tables.getData'
      resources:
      - projects/1057666841514 # bigquery-public-data

Se vuoi contribuire alle UDF in questo repository, consulta la sezione Contribuire alle UDF per istruzioni.

Accesso unificato alle routine in più regioni

Per utilizzare le funzioni definite dall'utente nelle query in più regioni, la funzione definita dall'utente deve essere disponibile in ogni regione in cui viene eseguita una query che la contiene. Pertanto, devi creare e gestire le funzioni definite dall'utente in qualsiasi regione in cui potresti utilizzarle in una query. Anche se le tabelle sono identiche, devi mantenere due versioni della funzione. Ad esempio, se memorizzi i dati di vendita nelle multiregioni EU e US, devi mantenere una versione della funzione in ogni regione. Ad esempio:

Una query nella multiregione EU: 

  SELECT 
    id,
    europe_dataset.my_function(value)
  FROM
    sales;

Una query nella multiregione US: 

  SELECT 
    id,
    us_dataset.my_function(value)
  FROM
    sales;

Inoltre, quando la definizione della funzione cambia, devi aggiornarla in tutte le regioni.

Per rendere le tue funzioni definite dall'utente indipendenti dalla regione, puoi utilizzare la replica dei set di dati tra regioni:

  1. Crea un nuovo set di dati dedicato, ad esempio my_utils, per archiviare tutte le UDF necessarie. Ricorda che tutte le tabelle aggiunte a questo set di dati verranno replicate, il che aumenterà i costi. Tuttavia, la replica di UDF e procedure non comporta costi aggiuntivi.
  2. Aggiungi tutte le tue UDF al nuovo set di dati. Possono essere incluse anche UDF della community, ad esempio bqutil copiate da GitHub.
  3. Abilita la replica del set di dati. Configura questo set di dati in modo che venga replicato in tutte le regioni in cui devi eseguire query che chiamano queste UDF. In questo modo, le funzioni verranno copiate in queste regioni e rimarranno sincronizzate.

Quando esegui una query, BigQuery utilizza automaticamente la versione locale della UDF dalla replica locale del set di dati senza che tu debba specificare la regione in cui è definita la funzione, rendendo le query portabili in diverse località. Ad esempio:

  SELECT 
    id,
    my_utils.my_function(value)
  FROM
    sales;

Limitazioni

Si applicano le seguenti limitazioni alle funzioni temporanee e permanenti definite dall'utente:

  • Gli oggetti DOM Window, Document e Node e le funzioni che li richiedono non sono supportati.
  • Le funzioni JavaScript operano in un ambiente limitato tramite sandbox e quelle che si basano sul codice di sistema sottostante potrebbero non riuscire a causa di chiamate di sistema limitate.
  • Una funzione definita dall'utente JavaScript può scadere e impedire il completamento della query. I timeout possono durare anche solo 5 minuti, ma possono variare a seconda di diversi fattori, tra cui il tempo di CPU utente consumato dalla funzione e le dimensioni degli input e degli output della funzione JavaScript.
  • Le operazioni bit per bit in JavaScript gestiscono solo i 32 bit più significativi.
  • Le UDF sono soggette a determinati limiti di frequenza e di quota. Per ulteriori informazioni, consulta Limiti delle UDF.

Alle funzioni permanenti definite dall'utente si applicano le seguenti limitazioni:

  • 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 è uguale a quello di una tabella nello stesso set di dati.
  • Quando fai riferimento a una funzione definita dall'utente permanente da un'altra funzione definita dall'utente permanente o da una vista 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.