Introduzione alle routine

Questo documento descrive come scegliere una routine, ovvero 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 si sceglie una routine e confronta le routine in base all'attività.

Fattori da considerare

Per scegliere una routine, prendi in considerazione i seguenti fattori, descritti nelle sezioni relative a ciascun 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

Creare funzioni che eseguono attività di uso generale in BigQuery.

Funzione UDF SQL o JavaScript

UDAF SQL o JavaScript

Crea funzioni che eseguono attività di uso generale in BigQuery e che comunicano con sistemi Trusted Cloud by S3NS esterni utilizzando una connessione a una risorsa cloud.

Funzione definita dall'utente Python

Creare funzioni che aggregano i dati.

UDAF

Crea una tabella utilizzando i parametri.

Funzioni tabella

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

Funzioni remote

Esegui più istruzioni in una query come query con più istruzioni utilizzando un linguaggio procedurale. Puoi utilizzare una query con più istruzioni per:

  • Esegui più istruzioni in sequenza, con stato condiviso.
  • Automatizza le attività di gestione come 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 UDF 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 resituiscono 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 funzioni personalizzate da utilizzare con le routine di mascheramento personalizzate, che restituiscono il valore di una colonna dopo aver applicato una funzione personalizzata alla colonna. Dopo aver creato la routine di mascheramento personalizzata, questa sarà disponibile come regola di mascheramento in Crea criteri relativi ai dati.

Per ulteriori informazioni sulle funzioni definite dall'utente, consulta le seguenti risorse:

Funzioni definite dall'utente basate sul linguaggio

  • Le UDF basate su SQL supportano i parametri delle UDF basate su modelli, che possono corrispondere a più tipi di argomenti quando viene chiamata la UDF. Le funzioni UDF SQL possono anche restituire il valore di una sottoquery scalare.
  • Le funzioni definite dall'utente basate su JavaScript ti consentono di chiamare il codice scritto in JavaScript da una query SQL.
    • In genere, le funzioni definite dall'utente JavaScript consumano più risorse dello slot rispetto alle query SQL standard, diminuendo il rendimento 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 sulle risorse gestite di BigQuery. Queste UDF ti consentono di implementare una funzione in Python e utilizzarla in una query SQL.

UDF forniti dalla community

Oltre alle funzioni UDF create da te, sono disponibili quelle fornite dalla community nel bigquery-public-data.persistent_udfs set di dati pubblico e nel repository GitHub open source bigquery-utils.

Funzioni aggregate definite dall'utente (UDAF)

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

Le UDAF 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 di SQL

In genere, le UDAF SQL aggregano i parametri di 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 funzioni UDAF SQL possono contenere parametri sia aggregati che non aggregati.

Funzioni UDAF JavaScript

Le funzioni definite dall'utente in JavaScript possono includere librerie JavaScript. Il corpo della funzione JavaScript può includere codice JavaScript personalizzato, ad esempio variabili globali e funzioni personalizzate JavaScript.

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

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

Confrontare le funzioni definite dall'utente e le funzioni definite dall'utente aggregate

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

  • Per eseguire un calcolo o una trasformazione sui singoli valori dei dati, utilizza una funzione 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 funzione dinamica di riga.

Le funzioni UDF e UDAF presentano le seguenti somiglianze:

  • Le funzioni UDF e UDAF non possono modificare i dati, comunicare con sistemi esterni o inviare log a Google Cloud Observability o applicazioni simili. L'eccezione sono le funzioni UDF di Python, che possono accedere a servizi esterni utilizzando una connessione alla risorsa Cloud. Tuttavia, le funzioni UDF di Python non supportano i Controlli di servizio VPC o le chiavi di crittografia gestite dal cliente (CMEK).
  • Le UDAF presentano le stesse limitazioni delle UDF, oltre ad alcune altre.
  • Le funzioni definite dall'utente e le funzioni definite dall'utente aggregate hanno le stesse quote e gli stessi limiti.

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

Attributo

UDF

Funzioni UDAF

Definizione

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

Le funzioni aggregate definite dall'utente (UDAF) accettano colonne di input, eseguono un calcolo su un gruppo di righe alla volta e poi restituiscono il risultato del 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 in Python possono essere solo permanenti, non temporanee.
  • Può essere temporaneo o persistente.
  • Puoi utilizzare le funzioni definite dall'utente aggregate permanenti in più query.
  • Puoi utilizzare le funzioni definite dall'utente aggregate temporanee solo per una singola query, script, sessione o procedura.
  • È possibile chiamare in sicurezza gli UDAF permanenti quando sono condivisi tra i proprietari.

Argomenti e tipi di dati

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

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

Solo le funzioni UDF JavaScript hanno uno specificatore di determinismo che fornisce a BigQuery un suggerimento su se il risultato della query puoi essere memorizzato nella cache.

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

I parametri di funzione possono essere aggregati o non aggregati.

Utilizzo

Le funzioni definite dall'utente vengono comunemente utilizzate per la pulizia, la trasformazione e la convalida dei dati.

Le funzioni UDAF vengono comunemente utilizzate per calcolare statistiche di riepilogo, come medie, somme e conteggi.

Funzioni tabella

Una funzione tabella, chiamata anche funzione con valore di tabella (TVF), è una UDF che restituisce una tabella. Puoi utilizzare una funzione tabella ovunque puoi utilizzare una tabella. Le funzioni con valori di tabella si comportano in modo simile alle viste, ma possono accettare parametri.

Con le funzioni di tabella puoi:

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

Per ulteriori informazioni sulle funzioni tabella, consulta Funzioni tabella, Limiti e Quote e limiti.

Funzioni remote

Le funzioni remote ti 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 funzione Google SQL con le funzioni Cloud Run e Cloud Run utilizzando qualsiasi linguaggio supportato, quindi richiama queste funzioni dalle query Google SQL.

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 nelle funzioni Cloud Run o in Cloud Run.
  2. Crea una funzione remota in BigQuery utilizzando il tipo di connessioneCLOUD_RESOURCE.
  3. Utilizza la funzione remota in una query come qualsiasi altra UDF per BigQuery.

Per ulteriori informazioni 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 procedure archiviate supportano le istruzioni del linguaggio procedurale, che ti consentono di eseguire operazioni come definire variabili e implementare il flusso di controllo. Puoi scoprire di più sulle dichiarazioni del linguaggio procedurale nel documento di riferimento sul linguaggio procedurale.

Una stored procedure può:

  • Accetta gli argomenti di input e restituisce i valori come output.
  • Accedere o modificare i dati di più set di dati da parte di più utenti.
  • Contiene una query con più istruzioni.

Alcune stored procedure sono integrate in BigQuery e non devono essere create. Si tratta delle procedure di sistema e puoi scoprire di più al riguardo nel riferimento alle procedure di sistema.

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

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