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

Funzioni definite dall'utente SQL

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

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

  • Se più di un parametro ha il tipo ANY TYPE, BigQuery non applica alcun rapporto di tipo tra questi argomenti.
  • Il tipo di ritorno della 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.
  • Se passi alla funzione argomenti di tipi incompatibili con la definizione della funzione, viene generato 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 seguente utilizza un parametro basato su 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 sottoquery scalare. Una sottoquery scalare deve selezionare una singola colonna.

L'esempio seguente mostra una UDF SQL che utilizza una sottoquery scalare per conteggiare il numero di utenti di 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 UDF 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 UDF.

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 va a buon fine. Tuttavia, se esegui questa istruzione da un progetto diverso, l'istruzione non va a buon fine. Per correggere l'errore, includere 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 in 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. In genere, le funzioni UDF JavaScript consumano più risorse dello slot rispetto alle query SQL standard, diminuendo il rendimento del job. Se la funzione può essere expressed in SQL, spesso è più ottimale eseguire il codice come job di query SQL standard.

L'esempio seguente mostra una UDF JavaScript. Il codice JavaScript è tra virgolette 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 seguente 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 definite dall'utente JavaScript supportati

Alcuni tipi SQL hanno una mappatura diretta 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 STRINGA con codifica base64
FLOAT64 NUMERO
NUMERIC, BIGNUMERIC 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 rientrano 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 DATA con un campo microsecondi contenente la frazione microsecond 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 funzioni UDF di JavaScript. Utilizza invece FLOAT64 per rappresentare i valori interi come numero o STRING per rappresentarli come stringa.

BigQuery supporta INT64 come tipo di ritorno nelle funzioni definite dall'utente JavaScript. 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 FDU JavaScript è un Promise, BigQuery attende il valore Promise fino a quando Promise non viene risolto. Se Promise assume uno stato soddisfatto, BigQuery restituisce il relativo risultato. Se Promise assume uno stato di rifiuto, BigQuery restituisce un errore.

Regole per le offerte

Devi racchiudere il codice JavaScript tra virgolette. Per snippet di codice semplici 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       |
+-----------+-----------+

Se lo snippet contiene virgolette o è costituito da più righe, utilizza blocchi tra 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 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 definite dall'utente JavaScript

Prefiltrare l'input

Se i dati di input possono essere filtrati prima di essere passati a una UDF JavaScript, la query potrebbe essere più rapida ed economica.

Evitare stati mutabili persistenti

Non memorizzare o accedere allo stato mutabile nelle chiamate alle funzioni definite dall'utente JavaScript. 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 funzioni definite dall'utente come routine. Le routine autorizzate ti consentono di condividere i risultati di una 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 su dati o cercare un valore di tabella e utilizzarlo in un calcolo. Per ulteriori informazioni, vedi Routine autorizzate.

Aggiungere descrizioni alle funzioni definite dall'utente

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, ricrea la funzione 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 ulteriori informazioni su come eseguire query, consulta Eseguire una query interattiva.

Creare routine di mascheramento personalizzate

Puoi creare funzioni UDF 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 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 tra i 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 di output devono essere gli stessi.
  • È necessario specificare un tipo di output.
  • Nel corpo della definizione non è possibile fare riferimento ad altre funzioni definite dall'utente, sottoquery, tabelle o visualizzazioni.
  • Una volta creata, una routine di mascheramento non può essere modificata 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 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
  );

Il seguente esempio esegue l'hash con il 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)
);

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

Dopo aver creato la routine di mascheramento personalizzata, questa sarà disponibile come regola di mascheramento in Crea criteri dei dati.

Funzioni fornite dalla community

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

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

Limitazioni

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

  • Gli oggetti DOM Window, Document e Node e le funzioni che li richiedono non sono supportati.
  • Le funzioni JavaScript che si basano sul codice nativo possono non riuscire, ad esempio se fanno chiamate di sistema con limitazioni.
  • Una funzione definita dall'utente JavaScript può scadere e impedire il completamento della query. I timeout possono essere brevi, anche di soli 5 minuti, ma possono variare a seconda di diversi fattori, tra cui il tempo di CPU dell'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 quote. Per ulteriori informazioni, consulta Limiti delle funzioni definite dall'utente.

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

  • Ogni set di dati può contenere una sola funzione definita dall'utente permanente con lo stesso nome. Tuttavia, puoi creare una UDF il cui nome corrisponde 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 specificare il nome con il set di dati. Ad esempio:
    CREATE FUNCTION mydataset.referringFunction() AS (mydataset.referencedFunction());

Le seguenti limitazioni si applicano alle funzioni definite dall'utente temporanee.

  • Quando crei una funzione definita dall'utente temporanea, function_name non può contenere punti.
  • Le visualizzazioni e le funzioni definite dall'utente permanenti non possono fare riferimento alle funzioni definite dall'utente temporanee.