Funzioni aggregate definite dall'utente

Anteprima

Questo prodotto o funzionalità è soggetto ai "Termini delle Offerte pre-GA" nella sezione Termini generali del servizio dei Termini specifici del servizio. I prodotti e le funzionalità pre-GA sono disponibili "così come sono" e potrebbero avere un supporto limitato. Per saperne di più, consulta le descrizioni della fase di lancio.

Per ricevere assistenza durante l'anteprima, invia un'email all'indirizzo [e-mail].

Questo documento descrive come creare, chiamare ed eliminare funzioni aggregate definite dall'utente in BigQuery.

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

Crea una funzione SQL UDAF

In questa sezione vengono descritti i vari modi in cui puoi creare un bucket un'UDAF SQL temporanea in BigQuery.

Crea un'UDAF SQL permanente

Puoi creare una funzione SQL UDAF permanente, ovvero riutilizzabile l'UDAF su più query. Le UDAF permanenti sono sicure da chiamare quando sono condivisi tra proprietari. Le UDAF non possono modificare i dati, comunicare con sistemi esterni o inviare log a Google Cloud Observability o applicazioni simili.

Per creare una UDAF persistente, utilizza l'istruzione CREATE AGGREGATE FUNCTION senza la parola chiave TEMP o TEMPORARY. Devi includere il set di dati nel del percorso della funzione.

Ad esempio, la seguente query crea una funzione UDAF permanente chiamata ScaledAverage:

CREATE AGGREGATE FUNCTION myproject.mydataset.ScaledAverage(
  dividend FLOAT64,
  divisor FLOAT64)
RETURNS FLOAT64
AS (
  AVG(dividend / divisor)
);

Crea una funzione SQL UDAF temporanea

Puoi creare una UDAF SQL temporanea, il che significa che nell'ambito di una singola query, script, sessione o procedura.

Per creare una funzione UDAF temporanea, utilizza la classe Dichiarazione CREATE AGGREGATE FUNCTION con TEMP o TEMPORARY parola chiave.

Ad esempio, la seguente query crea un'UDAF temporanea chiamata ScaledAverage:

CREATE TEMP AGGREGATE FUNCTION ScaledAverage(
  dividend FLOAT64,
  divisor FLOAT64)
RETURNS FLOAT64
AS (
  AVG(dividend / divisor)
);

Utilizza parametri aggregati e non aggregati

Puoi creare un'UDAF SQL con parametri aggregati e non aggregati.

Le funzioni UDAF aggregano solitamente i parametri delle funzioni in tutte le righe di un gruppo. Tuttavia, puoi specificare un parametro di funzione come non aggregato con NOT AGGREGATE parola chiave.

Un parametro della funzione non aggregata è un parametro di funzione scalare con un valore costante per tutte le righe di un gruppo. Un parametro di funzione non aggregata valido deve essere un valore letterale. All'interno della definizione UDAF, i parametri delle funzioni aggregate possono essere visualizzati solo come argomenti delle funzioni per le chiamate delle funzioni aggregate. Riferimenti a i parametri delle funzioni non aggregate possono apparire ovunque nella definizione della funzione UDAF.

Ad esempio, la seguente funzione contiene un parametro aggregato chiamato dividend e un parametro non aggregato chiamato divisor:

-- Create the function.
CREATE TEMP AGGREGATE FUNCTION ScaledSum(
  dividend FLOAT64,
  divisor FLOAT64 NOT AGGREGATE)
RETURNS FLOAT64
AS (
  SUM(dividend) / divisor
);

Utilizzare il progetto predefinito nel corpo della funzione

Nel corpo di una funzione SQL UDAF, ogni 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 UDAF.

Ad esempio, considera la seguente affermazione:

CREATE AGGREGATE FUNCTION project1.dataset_a.ScaledAverage(
  dividend FLOAT64,
  divisor FLOAT64)
RETURNS FLOAT64
AS (
  ( SELECT AVG(dividend / divisor) FROM dataset_a.my_table )
);

Se esegui l'istruzione precedente nel progetto project1, l'istruzione viene eseguita correttamente perché my_table esiste in project1. Tuttavia, se esegui l'istruzione precedente da un altro progetto, l'istruzione non va a buon fine. Per correggere l'errore, includi l'ID progetto nel riferimento tabella:

CREATE AGGREGATE FUNCTION project1.dataset_a.ScaledAverage(
  dividend FLOAT64,
  divisor FLOAT64)
RETURNS FLOAT64
AS (
  ( SELECT AVG(dividend / divisor) FROM project1.dataset_a.my_table )
);

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 AGGREGATE FUNCTION project1.dataset_a.ScaledAverage(
  dividend FLOAT64,
  divisor FLOAT64)
RETURNS FLOAT64
AS (
  ( SELECT AVG(dividend / divisor) FROM project2.dataset_c.my_table )
);

Creare una funzione definita dall'utente di analisi dati avanzata in JavaScript

Questa sezione descrive i vari modi in cui puoi creare una UDAF in JavaScript in BigQuery. Ci sono alcune regole da osservare quando si crea una funzione JavaScript definita dall'utente:

• Il corpo dell'UDAF JavaScript deve essere un valore letterale stringa tra virgolette che rappresenta il codice JavaScript. Per scoprire di più sui diversi tipi di stringhe letterali tra virgolette che puoi utilizzare, consulta Formati per i letterali tra virgolette.

• Sono consentite solo alcune codifiche di tipo specifico. Per ulteriori informazioni, consulta Codifiche dei tipi SQL consentite in una UDAF JavaScript.

• Il corpo della funzione JavaScript deve includere quattro funzioni JavaScript che inizializzare, aggregare, unire e finalizzare i risultati UDAF JavaScript (initialState, aggregate, merge e finalize). Per Per ulteriori informazioni, consulta Codifica dei tipi SQL consentiti in una funzione JavaScript definita dall'utente.

• Qualsiasi valore restituito dalla funzione initialState o lasciato nell'argomento state dopo la chiamata della funzione aggregate o merge deve essere serializzabile. Se vuoi lavorare con per i dati di aggregazione non serializzabili, ad esempio funzioni o campi di simboli, deve utilizzare le funzioni serialize e deserialize incluse. Per scoprire di più, vedi Eseguire la serializzazione e la deserializzazione dei dati in una UDAF JavaScript.

Creare una UDAF JavaScript persistente

Puoi creare una funzione JavaScript definita dall'utente permanente, ovvero riutilizzabile l'UDAF su più query. È possibile chiamare UDAF permanenti quando sono condivisi tra i proprietari. Le UDAF non possono modificare i dati, comunicare con sistemi esterni o inviare log a Google Cloud Observability o applicazioni simili.

Per creare una funzione UDAF permanente, utilizza CREATE AGGREGATE FUNCTION senza il prefisso TEMP o TEMPORARY parola chiave. Devi includere il set di dati nel percorso della funzione.

La seguente query crea un'UDAF JavaScript persistente chiamata SumPositive:

CREATE OR REPLACE AGGREGATE FUNCTION my_project.my_dataset.SumPositive(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r'''

  export function initialState() {
    return {sum: 0}
  }
  export function aggregate(state, x) {
    if (x > 0) {
      state.sum += x;
    }
  }
  export function merge(state, partialState) {
    state.sum += partialState.sum;
  }
  export function finalize(state) {
    return state.sum;
  }

''';

-- Call the JavaScript UDAF.
WITH numbers AS (
  SELECT * FROM UNNEST([1.0, -1.0, 3.0, -3.0, 5.0, -5.0]) AS x)
SELECT my_project.my_dataset.SumPositive(x) AS sum FROM numbers;

/*-----*
 | sum |
 +-----+
 | 9.0 |
 *-----*/

Creare una UDAF JavaScript temporanea

Puoi creare una UDAF JavaScript temporanea, il che significa che la UDAF esiste solo nell'ambito di una singola query, script, sessione o procedura.

Per creare una UDAF temporanea, utilizza l'istruzione CREATE AGGREGATE FUNCTION con la parola chiave TEMP o TEMPORARY.

La seguente query crea un'UDAF JavaScript temporanea chiamata SumPositive:

CREATE TEMP AGGREGATE FUNCTION SumPositive(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r'''

  export function initialState() {
    return {sum: 0}
  }
  export function aggregate(state, x) {
    if (x > 0) {
      state.sum += x;
    }
  }
  export function merge(state, partialState) {
    state.sum += partialState.sum;
  }
  export function finalize(state) {
    return state.sum;
  }

''';

-- Call the JavaScript UDAF.
WITH numbers AS (
  SELECT * FROM UNNEST([1.0, -1.0, 3.0, -3.0, 5.0, -5.0]) AS x)
SELECT SumPositive(x) AS sum FROM numbers;

/*-----*
 | sum |
 +-----+
 | 9.0 |
 *-----*/

Includere parametri non aggregati in una funzione JavaScript definita dall'utente

Puoi creare una funzione JavaScript definita dall'utente che abbia sia dati aggregati che parametri non aggregati.

Le funzioni UDAF aggregano solitamente i parametri delle funzioni in tutte le righe di un gruppo. Tuttavia, puoi specificare un parametro di funzione come non aggregato con la parola chiave NOT AGGREGATE.

Un parametro della funzione non aggregata è un parametro di funzione scalare con un valore costante per tutte le righe di un gruppo. Un parametro di funzione non aggregato valido deve essere un valore letterale. All'interno della definizione UDAF, i parametri delle funzioni aggregate possono essere visualizzati solo come argomenti delle funzioni per le chiamate delle funzioni aggregate. I riferimenti ai parametri delle funzioni non aggregate possono essere visualizzati ovunque nella definizione di UDAF.

Nell'esempio seguente, la funzione JavaScript definita dall'utente contiene una funzione denominato s e un parametro non aggregato denominato delimiter:

CREATE TEMP AGGREGATE FUNCTION JsStringAgg(
  s STRING,
  delimiter STRING NOT AGGREGATE)
RETURNS STRING
LANGUAGE js
AS r'''

  export function initialState() {
    return {strings: []}
  }
  export function aggregate(state, s) {
    state.strings.push(s);
  }
  export function merge(state, partialState) {
    state.strings = state.strings.concat(partialState.strings);
  }
  export function finalize(state, delimiter) {
    return state.strings.join(delimiter);
  }

''';

-- Call the JavaScript UDAF.
WITH strings AS (
  SELECT * FROM UNNEST(["aaa", "bbb", "ccc", "ddd"]) AS values)
SELECT JsStringAgg(values, '.') AS result FROM strings;

/*-----------------*
 | result          |
 +-----------------+
 | aaa.bbb.ccc.ddd |
 *-----------------*/

Serializzare e deserializzare i dati in una funzione JavaScript definita dall'utente

BigQuery deve serializzare qualsiasi oggetto restituito dalla funzione initialState o lasciato nell'argomento state dopo l'uso della funzione aggregate o merge. BigQuery supporta serializzare un oggetto se tutti i campi sono uno dei seguenti:

• Un valore primitivo JavaScript (ad esempio: 2, "abc", null, undefined).
• Un oggetto JavaScript per il quale BigQuery supporta la serializzazione di tutti i valori di campo.
• Un array JavaScript per il quale BigQuery supporta la serializzazione di tutti elementi.

I seguenti valori restituiti sono serializzabili:

export function initialState() {
  return {a: "", b: 3, c: null, d: {x: 23} }
}

export function initialState() {
  return {value: 2.3};
}

I seguenti valori restituiti non sono serializzabili:

export function initialState() {
  return {
    value: function() {return 6;}
  }
}

export function initialState() {
  return 2.3;
}

Se vuoi lavorare con stati di aggregazione non serializzabili, la funzione JavaScript UDAF deve includere le funzioni serialize e deserialize. La funzione serialize converte lo stato di aggregazione in un oggetto serializzabile; la funzione deserialize reconverte l'oggetto serializzabile in uno stato di aggregazione.

Nell'esempio seguente, una libreria esterna calcola le somme utilizzando un'interfaccia:

export class SumAggregator {
 constructor() {
   this.sum = 0;
 }
 update(value) {
   this.sum += value;
 }
 getSum() {
   return this.sum;
 }
}

La seguente query non viene eseguita perché l'oggetto della classe SumAggregator non è serializzabile in BigQuery a causa della presenza di funzioni all'interno della classe.

CREATE TEMP AGGREGATE FUNCTION F(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r'''

  class SumAggregator {
   constructor() {
     this.sum = 0;
   }

   update(value) {
     this.sum += value;
   }

   getSum() {
     return this.sum;
   }
  }

  export function initialState() {
   return new SumAggregator();
  }

  export function aggregate(agg, value) {
   agg.update(value);
  }

  export function merge(agg1, agg2) {
   agg1.update(agg2.getSum());
  }

  export function finalize(agg) {
   return agg.getSum();
  }

''';

--Error: getSum is not a function
SELECT F(x) AS results FROM UNNEST([1,2,3,4]) AS x;

Se aggiungi le funzioni serialize e deserialize alla query precedente, la query viene eseguita perché l'oggetto di classe SumAggregator viene convertito in un oggetto serializzabile in BigQuery e poi di nuovo in un oggetto di classe SumAggregator.

CREATE TEMP AGGREGATE FUNCTION F(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r'''

  class SumAggregator {
   constructor() {
     this.sum = 0;
   }

   update(value) {
     this.sum += value;
   }

   getSum() {
     return this.sum;
   }
  }

  export function initialState() {
   return new SumAggregator();
  }

  export function aggregate(agg, value) {
   agg.update(value);
  }

  export function merge(agg1, agg2) {
   agg1.update(agg2.getSum());
  }

  export function finalize(agg) {
   return agg.getSum();
  }

  export function serialize(agg) {
   return {sum: agg.getSum()};
  }

  export function deserialize(serialized) {
   var agg = new SumAggregator();
   agg.update(serialized.sum);
   return agg;
  }

''';

SELECT F(x) AS results FROM UNNEST([1,2,3,4]) AS x;

/*-----------------*
 | results         |
 +-----------------+
 | 10.0            |
 *-----------------*/

Per scoprire di più sulle funzioni di serializzazione, vedi Funzioni di serializzazione JavaScript facoltative.

Includere variabili globali e funzioni personalizzate in una UDAF JavaScript

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

Le variabili globali vengono eseguite quando il codice JavaScript viene caricato in BigQuery e prima dell'esecuzione della funzione initialState. Le variabili globali possono essere utili se devi eseguire un'operazione di inizializzazione una tantum che non deve essere ripetuta per ogni gruppo di aggregazione, come nel caso delle funzioni initialState, aggregate, merge e finalize.

Non utilizzare variabili globali per memorizzare lo stato di aggregazione. Limita invece lo stato di aggregazione agli oggetti passati alle funzioni esportate. Solo utilizzare variabili globali per memorizzare nella cache operazioni costose che non sono specifiche una particolare operazione di aggregazione.

Nella query seguente, la funzione SumOfPrimes calcola una somma, ma nel calcolo vengono inclusi solo i numeri primi. Nel corpo della funzione JavaScript, sono presenti due variabili globali, primes e maxTested, inizializzate per prima cosa. Inoltre, è disponibile una funzione personalizzata denominata isPrime che controlla se un numero è primo.

CREATE TEMP AGGREGATE FUNCTION SumOfPrimes(x INT64)
RETURNS INT64
LANGUAGE js
AS r'''

  var primes = new Set([2]);
  var maxTested = 2;

  function isPrime(n) {
    if (primes.has(n)) {
      return true;
    }
    if (n <= maxTested) {
      return false;
    }
    for (var k = 2; k < n; ++k) {
      if (!isPrime(k)) {
        continue;
      }
      if ((n % k) == 0) {
        maxTested = n;
        return false;
      }
    }
    maxTested = n;
    primes.add(n);
    return true;
  }

  export function initialState() {
    return {sum: 0};
  }

  export function aggregate(state, x) {
    x = Number(x);
    if (isPrime(x)) {
      state.sum += x;
    }
  }

  export function merge(state, partialState) {
    state.sum += partialState.sum;
  }

  export function finalize(state) {
    return state.sum;
  }

''';

-- Call the JavaScript UDAF.
WITH numbers AS (
  SELECT * FROM UNNEST([10, 11, 13, 17, 19, 20]) AS x)
SELECT SumOfPrimes(x) AS sum FROM numbers;

/*-----*
 | sum |
 +-----+
 | 60  |
 *-----*/

Includi librerie JavaScript

Puoi estendere le tue UDAF JavaScript con l'opzione library nella Clausola OPTIONS. Questa opzione ti consente di specificare librerie di codice esterne per la UDAF JavaScript e di importarle con la dichiarazione import.

Nell'esempio seguente, il codice in bar.js è disponibile per qualsiasi codice nella corpo della funzione dell'UDAF JavaScript:

CREATE TEMP AGGREGATE FUNCTION JsAggFn(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
OPTIONS (library = ['gs://foo/bar.js'])
AS r'''

  import doInterestingStuff from 'bar.js';

  export function initialState() {
    return ...
  }
  export function aggregate(state, x) {
    var result = doInterestingStuff(x);
    ...
  }
  export function merge(state, partial_state) {
    ...
  }
  export function finalize(state) {
    return ...;
  }

''';

Struttura JavaScript obbligatoria

A differenza di una funzione JavaScript definita dall'utente, in cui il corpo della funzione è JavaScript in formato libero che viene eseguito per ogni riga, il corpo della funzione per una funzione UDAF JavaScript è un modulo JavaScript che contiene alcune funzioni esportate integrate, che vengono richiamate in varie fasi del processo di aggregazione. Alcuni di questi funzioni integrate sono obbligatorie, mentre altre sono facoltative. Puoi anche aggiungere le tue funzioni JavaScript.

Funzioni di aggregazione JavaScript obbligatorie

Puoi includere le tue funzioni JavaScript, ma il corpo della funzione JavaScript deve includere le seguenti funzioni JavaScript esportabili:

• initialState([nonAggregateParam]): restituisce un oggetto JavaScript che rappresenta uno stato di aggregazione in cui non sono ancora state aggregate righe.

• aggregate(state, aggregateParam[, ...][, nonAggregateParam]): aggrega una riga di dati, aggiornando lo stato per memorizzare il risultato dell'aggregazione. Non restituisce un valore.

• merge(state, partialState, [nonAggregateParam]): unisce lo stato di aggregazione partialState allo stato di aggregazione state. Questa funzione viene utilizzata quando il motore aggrega sezioni diverse di dati in parallelo e deve combinare i risultati. Non restituisce un valore.

• finalize(finalState, [nonAggregateParam]): restituisce il risultato finale della funzione di aggregazione, dato uno stato di aggregazione finale finalState.

Per saperne di più sulle funzioni richieste, consulta Funzioni obbligatorie in una funzione JavaScript definita dall'utente.

Funzioni di serializzazione JavaScript facoltative

Se vuoi lavorare con stati di aggregazione non serializzabili, La funzione JavaScript UDAF deve fornire le funzioni serialize e deserialize. La funzione serialize converte lo stato di aggregazione in un oggetto serializzabile BigQuery; la funzione deserialize riconverte l'oggetto serializzabile BigQuery in uno stato di aggregazione.

• serialize(state): restituisce un oggetto serializzabile che contiene la classe nello stato di aggregazione, da deserializzare attraverso Funzione deserialize.

• deserialize(serializedState): deserializza serializedState (precedentemente serializzato dalla funzione serialize) in uno stato di aggregazione che può essere trasmesso alle funzioni serialize, aggregate, merge o finalize.

Per scoprire di più sulle funzioni di serializzazione integrate in JavaScript, consulta Funzioni di serializzazione per una funzione JavaScript definita dall'utente.

Per scoprire come serializzare e deserializzare i dati con una funzione JavaScript definita dall'utente (UDAF), consulta Serializza e deserializza i dati in una funzione JavaScript definita dall'utente.

Codifiche di tipo SQL consentite in una UDAF JavaScript

Nelle UDAF JavaScript, i seguenti tipi di dati GoogleSQL supportati rappresentano i tipi di dati JavaScript come segue:

Tipo di dati GoogleSQL	Tipo di dati JavaScript	Note
ARRAY	Array	Un array di array non è supportato. Per aggirare il problema è necessario utilizzare Array<Object<Array>> (JavaScript) e ARRAY<STRUCT<ARRAY>> (GoogleSQL) tipi di dati.
BIGNUMERIC	Number o String	Uguale a NUMERIC.
BOOL	Boolean
BYTES	Uint8Array
DATE	Date
FLOAT64	Number
INT64	BigInt
JSON	Vari tipi	Il tipo di dati JSON di GoogleSQL può essere convertito in Object, Array o un altro tipo di dati JavaScript supportato da GoogleSQL.
NUMERIC	Number o String	Se un valore NUMERIC può essere rappresentato esattamente come un Virgola mobile IEEE 754 valore (intervallo [-2^53, 2^53]), e non ha una parte frazionaria, è codificato come Number di dati, altrimenti è codificato come tipo di dati String.
STRING	String
STRUCT	Object	Ogni campo STRUCT è una proprietà denominata nel tipo di dati Object. Un campo STRUCT senza nome è non supportati.
TIMESTAMP	Date	Date contiene un campo microsecondi con la frazione in microsecondi di TIMESTAMP.

Nota: le codifiche SQL per le UDAF JavaScript sono diverse da quelle per funzioni JavaScript definite dall'utente.

Chiamare una UDAF

Questa sezione descrive i vari modi in cui puoi chiamare una UDAF permanente o provvisoria dopo averla creata in BigQuery.

Nota: le chiamate UDAF JavaScript non supportano la clausola ORDER BY.

Chiamare una UDAF permanente

Puoi chiamare una UDAF permanente nello stesso modo in cui chiami un funzione aggregata integrata. Per ulteriori informazioni, vedi Chiamate di funzione aggregate. Devi includere il set di dati nel percorso della funzione.

Nell'esempio seguente, la query chiama una funzione UDAF permanente chiamato WeightedAverage:

SELECT my_project.my_dataset.WeightedAverage(item, weight, 2) AS weighted_average
FROM (
  SELECT 1 AS item, 2.45 AS weight UNION ALL
  SELECT 3 AS item, 0.11 AS weight UNION ALL
  SELECT 5 AS item, 7.02 AS weight
);

Viene generata una tabella con i seguenti risultati:

/*------------------*
 | weighted_average |
 +------------------+
 | 4.5              |
 *------------------*/

Chiamare una UDAF temporanea

Puoi chiamare una UDAF temporanea nello stesso modo in cui chiami una funzione di aggregazione integrata. Per ulteriori informazioni, consulta Chiamate di funzioni aggregate.

La funzione temporanea deve essere inclusa in un query multi-istruzione o procedura che contiene la funzione UDAF di funzione.

Nell'esempio seguente, la query chiama una UDAF temporanea chiamata WeightedAverage:

CREATE TEMP AGGREGATE FUNCTION WeightedAverage(...)

-- Temporary UDAF function call
SELECT WeightedAverage(item, weight, 2) AS weighted_average
FROM (
  SELECT 1 AS item, 2.45 AS weight UNION ALL
  SELECT 3 AS item, 0.11 AS weight UNION ALL
  SELECT 5 AS item, 7.02 AS weight
);

Viene generata una tabella con i seguenti risultati:

/*------------------*
 | weighted_average |
 +------------------+
 | 4.5              |
 *------------------*/

Ignorare o includere le righe con valori NULL

Quando viene chiamata una UDAF JavaScript con l'argomento IGNORE NULLS, BigQuery salta automaticamente le righe per le quali qualsiasi argomento aggregato ha valore NULL. Queste righe vengono escluse completamente dall'aggregazione e non vengono passate alla funzione aggregate di JavaScript. Quando È stato specificato l'argomento RESPECT NULLS, il filtro NULL è disattivato e ogni viene passata all'UDAF di JavaScript, indipendentemente dai valori NULL.

Quando non viene fornito né l'argomento IGNORE NULLS né RESPECT NULLS, l'oggetto l'argomento predefinito è IGNORE NULLS.

L'esempio seguente illustra il comportamento predefinito di NULL. il comportamento IGNORE NULLS e il comportamento RESPECT NULLS:

CREATE TEMP AGGREGATE FUNCTION SumPositive(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r'''

  export function initialState() {
    return {sum: 0}
  }
  export function aggregate(state, x) {
    if (x == null) {
      // Use 1000 instead of 0 as placeholder for null so
      // that NULL values passed are visible in the result.
      state.sum += 1000;
      return;
    }
    if (x > 0) {
      state.sum += x;
    }
  }
  export function merge(state, partialState) {
    state.sum += partialState.sum;
  }
  export function finalize(state) {
    return state.sum;
  }

''';

-- Call the JavaScript UDAF.
WITH numbers AS (
  SELECT * FROM UNNEST([1.0, 2.0, NULL]) AS x)
SELECT
  SumPositive(x) AS sum,
  SumPositive(x IGNORE NULLS) AS sum_ignore_nulls,
  SumPositive(x RESPECT NULLS) AS sum_respect_nulls
FROM numbers;

/*-----+------------------+-------------------*
 | sum | sum_ignore_nulls | sum_respect_nulls |
 +-----+------------------+-------------------+
 | 3.0 | 3.0              | 1003.0            |
 *-----+------------------+-------------------*/

Eliminare una UDAF

In questa sezione vengono descritti i vari modi in cui è possibile eliminare un oggetto una funzione UDAF temporanea dopo la creazione in BigQuery.

Eliminare una UDAF persistente

Per eliminare una funzione UDAF permanente, utilizza DROP FUNCTION. Devi includere il set di dati nel percorso della funzione.

Nell'esempio seguente, la query elimina un'espressione UDAF permanente chiamata WeightedAverage:

DROP FUNCTION IF EXISTS my_project.my_dataset.WeightedAverage;

Elimina una funzione UDAF temporanea

Per eliminare una funzione UDAF temporanea, utilizza DROP FUNCTION.

Nell'esempio seguente, la query elimina un'UDAF temporanea chiamata WeightedAverage:

DROP FUNCTION IF EXISTS WeightedAverage;

Una UDAF temporanea scade al termine della query. L'UDF non deve essere eliminata, a meno che tu non voglia rimuoverla in anticipo da una query con più istruzioni o da una procedura.

Elenca UDAF

Le UDAF sono un tipo di routine. Per elencare tutte le routine in un set di dati, consulta Elenco di routine.

Suggerimenti sulle prestazioni

Se vuoi migliorare le prestazioni delle tue query, considera quanto segue:

• Prefiltra l'input. L'elaborazione dei dati in JavaScript è più costosa in SQL, quindi è meglio filtrare il più possibile l'input in SQL.

  La seguente query è meno efficiente perché filtra l'input utilizzando x > 0 nella chiamata UDAF:

  SELECT JsFunc(x) FROM t;
  

  La seguente query è più efficiente perché prefiltra l'input utilizzando WHERE x > 0 prima della chiamata della funzione UDAF:

  SELECT JsFunc(x) FROM t WHERE x > 0;
  

• Se possibile, utilizza le funzioni aggregate integrate anziché JavaScript. La reimplementazione di una funzione di aggregazione integrata in JavaScript è più lenta rispetto alla chiamata di una funzione di aggregazione integrata che fa la stessa cosa.

  La seguente query è meno efficiente perché implementa una UDAF:

  SELECT SumSquare(x) FROM t;
  

  La seguente query è più efficiente perché implementa una query che produce gli stessi risultati della query precedente:

  SELECT SUM(x*x) FROM t;
  

• Le UDAF JavaScript sono appropriate per operazioni di aggregazione più complesse, che non possono essere espresse tramite funzioni integrate.

• Utilizza la memoria in modo efficiente. L'ambiente di elaborazione JavaScript ha una memoria limitata disponibile per ogni query. Le query UDAF JavaScript che accumulano troppo stato locale potrebbero non riuscire a causa dell'esaurimento della memoria. Presta particolare attenzione su come ridurre al minimo le dimensioni degli oggetti di stato di aggregazione ed evitare l'aggregazione stati che accumulano un gran numero di righe.

  La seguente query non è efficiente perché la funzione aggregate utilizza una quantità illimitata di memoria quando il numero di righe elaborate aumenta.

  export function initialState() {
    return {rows: []};
  }
  export function aggregate(state, x) {
    state.rows.push(x);
  }
  ...
  

• Se possibile, utilizza tabelle partizionate. Le UDAF JavaScript in genere eseguono più in modo efficiente quando si eseguono query su una tabella partizionata rispetto non partizionata, perché una tabella partizionata archivia i dati di file più piccoli rispetto a una tabella non partizionata, consentendo parallelismo.

Limitazioni

• Le funzioni definite dall'utente hanno le stesse limitazioni che si applicano alle funzioni definite dall'utente. Per maggiori dettagli, consulta Limitazioni delle funzioni definite dall'utente.

• Solo i valori letterali, i parametri di query e le variabili dello script possono essere passati come argomenti non aggregati per una UDAF.

• L'utilizzo della clausola ORDER BY in una chiamata di funzione UDAF JavaScript viene non supportato.

  SELECT MyUdaf(x ORDER BY y) FROM t; -- Error: ORDER BY is unsupported.
  

Prezzi

Le UDAF vengono fatturate utilizzando Modello di determinazione del prezzo di BigQuery.

Quote e limiti

Le funzioni UDAF hanno le stesse quote e gli stessi limiti che si applicano alle funzioni UDF. Per informazioni sulle quote delle funzioni definite dall'utente, consulta Quote e limiti.