Introduzione alle routine

Questo documento descrive come scegliere una routine, un tipo di risorsa che utilizzi per creare funzioni o stored procedure in BigQuery.

Routine supportate

BigQuery supporta le seguenti routine:

Come scegliere una routine

Questa sezione descrive i fattori da considerare quando scegli una routine e confronta le routine in base all'attività.

Fattori da considerare

Per scegliere una routine, considera i seguenti fattori, descritti nelle sezioni per ogni tipo di routine:

  • Il tipo di attività da implementare.
  • Il linguaggio di programmazione da utilizzare.
  • Il tipo di persistenza da implementare per la routine: temporanea o permanente.
  • Il tipo di riutilizzo richiesto per la routine: in una o più query.
  • Considerazioni sulle prestazioni.
  • Accesso a servizi esterni.
  • Condivisione della routine con gli utenti.

Confrontare le routine per attività

La tabella seguente mostra il tipo di attività che puoi eseguire per ogni tipo di routine:

Attività

Tipo di risorsa della routine

Crea funzioni che eseguono attività per uso generico in BigQuery.

Funzione definita dall'utente SQL o JavaScript

UDAF SQL o JavaScript

Crea funzioni che eseguono attività per uso generico in BigQuery e che comunicano con sistemi esterni Google Cloud utilizzando una connessione alle risorse Cloud.

Funzione definita dall'utente Python

Crea funzioni che aggregano i dati.

UDAF

Crea una tabella utilizzando i parametri.

Funzioni tabella

Crea funzioni che utilizzano linguaggi, librerie o servizi non supportati in BigQuery. Queste funzioni si integrano direttamente con Cloud Run Functions e Cloud Run.

Funzioni remote

Esegui più istruzioni in una query come query con più istruzioni utilizzando il linguaggio procedurale. Puoi utilizzare una query multi-istruzione per:

  • Esegui più istruzioni in sequenza, con stato condiviso.
  • Automatizza le attività di gestione, ad esempio la creazione o l'eliminazione di tabelle.
  • Implementa una logica complessa utilizzando costrutti di programmazione come IF e WHILE.

Crea e chiama stored procedure per Apache Spark in BigQuery.

Stored procedure

Funzioni definite dall'utente

Una funzione definita dall'utente consente di creare una funzione utilizzando un'espressione SQL, codice JavaScript o codice Python. Le funzioni definite dall'utente accettano colonne di input, eseguono azioni sull'input e restituiscono 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.

Puoi creare UDF da utilizzare con routine di mascheramento personalizzate, che restituiscono il valore di una colonna dopo l'applicazione di una UDF alla colonna. Dopo aver creato la routine di mascheramento personalizzata, questa è disponibile come regola di mascheramento in Crea criteri dei dati.

Per saperne di più sulle UDF, consulta le seguenti risorse:

Funzioni definite dall'utente basate sulla lingua

  • Le UDF basate su SQL supportano i parametri UDF basati su modelli, che possono corrispondere a più di un tipo di argomento quando viene chiamata la UDF. Le UDF SQL possono anche restituire il valore di una subquery scalare.
  • Le UDF basate su JavaScript consentono di chiamare il codice scritto in JavaScript da una query SQL.
    • Le funzioni definite dall'utente JavaScript in genere consumano più risorse 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.
  • Le UDF basate su Python vengono create ed eseguite su risorse gestite da BigQuery. Queste UDF ti consentono di implementare una funzione in Python e utilizzarla in una query SQL.

Funzioni definite dall'utente con contributi della community

Oltre alle UDF che crei, le UDF con il contributo della community sono disponibili nel set di dati pubblici bigquery-public-data.persistent_udfs e nel repository GitHub open source bigquery-utils.

Funzioni aggregate definite dall'utente

Una UDAF consente di creare una funzione di aggregazione utilizzando un'espressione che contiene codice SQL o JavaScript. Una funzione di aggregazione definita dall'utente accetta colonne di input, esegue un calcolo su un gruppo di righe alla volta e poi restituisce il risultato di questo calcolo come un singolo valore.

Le UDF non possono modificare i dati, comunicare con sistemi esterni o inviare log a Google Cloud Observability o applicazioni simili.

Per maggiori informazioni, consulta le seguenti risorse:

UDAF SQL

Le UDAF SQL in genere aggregano i parametri della funzione in tutte le righe di un gruppo. Tuttavia, puoi specificare un parametro di funzione come non aggregato utilizzando la parola chiave NOT AGGREGATE. Un parametro di funzione non aggregata è un parametro di funzione scalare con un valore costante per tutte le righe di un gruppo. Le UDAF SQL possono contenere parametri aggregati e non aggregati.

Funzioni definite dall'utente JavaScript

Le UDAF JavaScript possono includere librerie JavaScript. Il corpo della funzione JavaScript può includere codice JavaScript personalizzato, ad esempio variabili globali JavaScript e funzioni personalizzate.

Poiché le funzioni basate su JavaScript in genere utilizzano più risorse, può essere utile consultare questi suggerimenti per il rendimento.

Le UDF JavaScript presentano alcuni vincoli. Sono consentite solo codifiche di tipo specifico e sono previsti requisiti per la serializzazione e la deserializzazione.

Confrontare le funzioni definite dall'utente e le funzioni di aggregazione definite dall'utente

La scelta di una UDF anziché di una UDAF dipende dall'attività specifica che stai cercando di eseguire.

  • Per eseguire un calcolo o una trasformazione su singoli valori di dati, utilizza una UDF.
  • Per eseguire la stessa operazione su gruppi di valori di dati, utilizza una UDAF.

Ad esempio, se vuoi calcolare la media di una colonna di numeri, utilizza una UDAF. Se vuoi convertire una colonna di stringhe in maiuscolo, utilizza una UDF.

Le UDF e le UDAF hanno le seguenti somiglianze:

  • Le UDF e le UDAF non possono modificare i dati, comunicare con sistemi esterni o inviare log a Google Cloud Observability o applicazioni simili. L'eccezione è rappresentata dalle UDF Python, che possono accedere a servizi esterni utilizzando una connessione alle risorse Cloud. Tuttavia, le UDF Python non supportano i controlli di servizio VPC o le chiavi di crittografia gestite dal cliente (CMEK).
  • Le UDAF hanno le stesse limitazioni delle UDF, più qualche altra.
  • Le funzioni definite dall'utente e le funzioni di aggregazione definite dall'utente hanno le stesse quote e gli stessi limiti.

Le funzioni definite dall'utente e le funzioni di aggregazione definite dall'utente presentano le seguenti differenze:

Attributo

UDF

UDAF

Definizione

Le funzioni definite dall'utente accettano colonne di input, eseguono azioni sull'input e restituiscono il risultato di queste azioni sotto forma di un valore.

Le funzioni di aggregazione definite dall'utente (UDAF) accettano colonne di input, eseguono un calcolo su un gruppo di righe alla volta e restituiscono il risultato di questo calcolo come singolo valore.

Lingue supportate

SQL, JavaScript e Python

SQL e JavaScript

Persistenza
  • Può essere temporaneo o persistente.
  • Puoi utilizzare le funzioni definite dall'utente permanenti in più query.
  • Puoi utilizzare le funzioni definite dall'utente temporanee solo per una singola query.
  • Le funzioni definite dall'utente Python possono essere solo permanenti, non temporanee.
  • Può essere temporaneo o persistente.
  • Puoi utilizzare le UDAF permanenti in più query.
  • Puoi utilizzare le UDAF temporanee solo per una singola query, script, sessione o procedura.
  • Le UDF persistenti possono essere chiamate in sicurezza quando vengono condivise tra proprietari.

Argomenti e tipi di dati

Le UDF accettano valori di parametri conformi a GoogleSQL per i tipi di dati di BigQuery. Alcuni tipi di SQL hanno una mappatura diretta ai tipi JavaScript, altri no. Consulta i tipi supportati per JavaScript.

Per una UDF SQL, i valori dei parametri possono essere ANY TYPE, che può corrispondere a più di un tipo di argomento quando viene chiamata la funzione.

Solo le funzioni definite dall'utente JavaScript hanno uno specificatore di determinismo che fornisce un suggerimento a BigQuery per indicare se il risultato della query può essere memorizzato nella cache.

Le UDF SQL e JavaScript accettano valori di parametri conformi ai tipi di dati GoogleSQL per BigQuery.

I parametri della funzione possono essere aggregati o non aggregati.

Utilizzo

Le funzioni definite dall'utente sono di uso comune per la pulizia, la trasformazione e la convalida dei dati.

Le UDAF sono di uso comune per calcolare statistiche riepilogative, come medie, somme e conteggi.

Funzioni tabella

Una funzione tabella, chiamata anche funzione con valori di tabella (TVF), è una funzione definita dall'utente che restituisce una tabella. Puoi utilizzare una funzione di tabella ovunque tu possa utilizzare una tabella. Le funzioni di tabella si comportano in modo simile alle viste, ma una funzione di tabella può accettare parametri.

Con le funzioni di tabella puoi:

  • Passa più parametri.
  • Chiama una funzione di tabella in qualsiasi contesto in cui una tabella è valida.
  • Unisci l'output di una funzione di tabella a un'altra tabella.
  • Utilizza una funzione di tabella in una subquery.

Per saperne di più sulle funzioni di tabella, consulta Funzioni di tabella, Limitazioni e Quote e limiti.

Funzioni remote

Le funzioni remote consentono di implementare la funzione in linguaggi diversi da SQL e JavaScript oppure di utilizzare librerie o servizi non supportati nelle funzioni definite dall'utente di BigQuery.

Una funzione remota BigQuery integra la tua funzione GoogleSQL con le funzioni Cloud Run e Cloud Run utilizzando qualsiasi linguaggio supportato, quindi richiama queste funzioni dalle query GoogleSQL.

Le seguenti attività sono esempi di ciò che puoi fare con le funzioni remote:

La creazione di una funzione remota richiede i seguenti passaggi:

  1. Crea l'endpoint HTTP in Cloud Run Functions o Cloud Run.
  2. Crea una funzione remota in BigQuery utilizzando il tipo di connessione CLOUD_RESOURCE.
  3. Utilizza la funzione remota in una query come qualsiasi altra UDF per BigQuery.

Per saperne di più sulle funzioni remote, consulta Funzioni remote, Limitazioni e Quote e limiti.

Stored procedure

Una stored procedure SQL è una raccolta di istruzioni che possono essere chiamate da altre query o altre stored procedure. Assegni un nome e memorizzi una procedura in un set di dati BigQuery.

Le stored procedure supportano le istruzioni del linguaggio procedurale, che ti consentono di definire variabili e implementare il flusso di controllo. Puoi scoprire di più sulle istruzioni del linguaggio procedurale nel riferimento al linguaggio procedurale.

Una stored procedure può:

  • Accettano argomenti di input e restituiscono valori come output.
  • Accedere ai dati o modificarli in più set di dati da parte di più utenti.
  • Contenere una query con più istruzioni.

Alcune stored procedure sono integrate in BigQuery e non devono essere create. Queste vengono chiamate procedure di sistema e puoi scoprire di più su di loro nel riferimento per le procedure di sistema.

Sono supportate anche le stored procedure per Spark in BigQuery. Queste procedure hanno quote e limiti.

Per saperne di più sulle stored procedure, consulta Stored procedure SQL.