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

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

Il prossimo esempio 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 ha il tipo ANY TYPE, BigQuery non applica alcun rapporto 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.
  • Se passi argomenti di funzione 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 SQL UDF può restituire il valore di sottoquery scalabile. 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 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 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 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 expressed 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 è 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 STRINGA con codifica base64
FLOAT64 NUMERO
NUMERIC, BIGNUMERIC Se un valore NUMERICO o BIGNUMERICO può essere rappresentato esattamente come un Floating-point IEEE 754 e non ha una parte frazionaria, il valore è codificato come numero. Questi valori rientrano nell'intervallo [-253, 253]. In caso contrario, è codificato come stringa.
STRING STRING
STRUCT OBJECT dove ogni campo STRUCT è un campo denominato
TIMESTAMP DATA con un campo microsecondi contenente la frazione microsecond 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. 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 numeri interi valori come numero o STRING per rappresentare un numero intero 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 diventa rifiutato, BigQuery restituisce un errore.

Regole per le offerte

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

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 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 i dati di input possono essere filtrati prima di essere passati a una UDF JavaScript, la query potrebbe essere più rapida ed economica.

Evita lo stato modificabile permanente

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 disponibile limitata per query. Le query UDF JavaScript che accumulano troppo stato locale potrebbero non riuscire a causa dell'esaurimento della memoria.

Autorizza 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 sui 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 funzione definita dall'utente, segui questi passaggi:

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 il campo o l'audiodescrizione. 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 le query, consulta 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'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.
  • 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.
  • Dopo aver creato una routine di mascheramento, non è possibile impostare una routine funzione standard. Ciò significa che se l'opzione data_governance_type è Impostato 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
  );

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, utilizza il prefisso SAFE ove possibile per evitare di esporre i dati non elaborati tramite i messaggi di errore.

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

Funzioni offerte 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 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 che si basano sul codice nativo possono non riuscire, ad esempio se fanno chiamate di sistema limitate.
  • 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 funzioni definite dall'utente sono soggette a determinati limiti di quota e di frequenza. 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 UDF 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 definite dall'utente temporanee.

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