Funzioni SQL definite dall’utente

BigQuery supporta le funzioni definite dall’utente (UDF). Un UDF consente di creare una funzione da un’altra espressione SQL o JavaScript. Queste funzioni accettano colonne di input ed eseguono azioni e quindi mostrano il risultato di queste azioni come valore. Per informazioni sulle funzioni definite dall’utente in SQL ereditate, consultare le funzioni definite dall’utente in SQL ereditata

UDF può essere persistente o temporaneo. È possibile riutilizzare gli UDF persistenti in diverse consultazioni e UDF temporanei in una singola query.

Sintassi dell’UDF

Per creare un UDF persistente, utilizzare la seguente sintassi:

CREATE FUNCTION dataset_name.]function_name (]) { sql_function_definition | javascript_function_definition }

Per creare un UDF temporaneo, utilizzare la seguente sintassi:

Questa sintassi è composta dai seguenti componenti:

  • Crea {funzione | O sostituire la funzione | Funzione se non esiste}. Creare una funzione o aggiornarlo. Per sostituire una funzione esistente con lo stesso nome, utilizzare la parola chiave OR REPLACE. Utilizzare Se si desidera trattare la query come completato e non si desidera che non si facciano alcuna azione nel caso in cui vi sia già una funzione con lo stesso nome.

    /li>

  • project_name è il nome del progetto in cui si crea la funzione. La configurazione predefinita è il progetto che esegue questa query DDL. Se il nome del progetto contiene caratteri speciali, come due punti, deve essere tra accenti gravi ` (esempio: ).

  • Dataset_name è il nome del set di dati in cui si crea la funzione. Il valore predefinito è defaultDataset nella richiesta.

  • named_parameter. Consiste in un paio di param_name e param_type separati da virgole. Il valore di param_type è un tipo di dati bigQuery. Per un UDF SQL, il valore di param_type potrebbe anche essere ANY TYPE.

  • Deterism_Specificatore. Si applica solo alle funzioni definite dall’utente JavaScript. Fornisce un suggerimento alla grossa grossa decidere se il risultato della query può essere memorizzato nella cache. Può essere uno dei seguenti valori:

    • DETERMINISTIC: la funzione mostra sempre lo stesso risultato quando vengono superati gli stessi argomenti. Il risultato della query può essere memorizzato nella cache. Ad esempio, se la funzione add_one(i) mostra sempre i + 1, la funzione è deterministica.

    • NOT DETERMINISTIC: La funzione non mostra sempre lo stesso risultato quando gli stessi argomenti sono passati e, pertanto, non possono essere memorizzati nella cache. Ad esempio, se add_random(i) mostra i + rand(), la funzione non è deterministica e BigQuery non utilizzerà i risultati memorizzati nella cache.

      Se tutte le funzioni invocate sono deterministiche, BigQuery tenterà di cache il risultato, a meno che i risultati non possano essere catturati da altri motivi. Per ulteriori informazioni, consultare la sezione su come utilizzare le query memorizzate in cache.

  • . Specifica il tipo di dati che mostra la funzione.

    • Se la funzione è definita in SQL, la clausola RETURNS è facoltativo. Se la clausola , la bigQuery deduce il tipo di risultato della funzione dal corpo della funzione SQL quando una query chiama la funzione.
    • Se il La funzione è definita in JavaScript, è obbligatorio. Se si desidera maggiori informazioni sui valori consentiti per , vedere Tipi di dati UDF JavaScript supportati.
  • Come (sql_expression). Specifica l’espressione SQL che definisce la funzione.

  • . Per un UDF JavaScript, specificare una disposizione delle librerie JavaScript da includere nella definizione della funzione.

  • come javascript_code. Specifica la definizione di una funzione JavaScript. è una stringa letterale.

    • Se il codice include citazioni e barre invertite, devi scappare come un stringa non trasformata. Ad esempio, il codice return "\n"; può essere preparato come una delle seguenti opzioni:
      • stringa entrequilled "return \"\\n\";". Le banche e le barre invertite devono essere escalate.
      • stringa tra commenti tripli: """return "\\n";""". Le barre invertite devono essere salvate, mentre le citazioni no.
      • Stringa raw: r"""return "\n";""". Non è necessaria alcuna fuga.

Per cancellare una funzione definita dall’utente persistente, utilizzare la seguente sintassi:

DROP FUNCTION dataset_name.]function_name

L’UDF temporaneo scade non appena la query, quindi le dichiarazioni DROP FUNCTION sono compatibili solo con UDF temporanei in sequenze di Comandi e procedure.

SQL UDF

Crea SQL UDF con la seguente sintassi:

CREATE FUNCTION dataset_name.]function_name (]) AS (sql_expression)named_parameter: param_name param_type

Parametri UDF SQL con il modello

Un parametro con il modello con ANY TYPE può corrispondere più di un tipo di argomento quando la funzione è chiamato.

  • se c’è più di un parametro con il tipo , bigQuery non impone alcuna relazione tra questi argomenti.
  • Il tipo di dati mostrato come risultato della funzione non può essere ANY TYPE. Deve essere omesso, il che significa che viene determinato automaticamente a seconda di sql_expression, oppure deve essere un tipo esplicito.
  • Se si passano i tipi di argomenti delle funzioni sono incompatibili con la definizione della funzione, si verificherà un errore durante la chiamata.

SQL UDF Esempi

Nell’esempio seguente viene visualizzato un UDF che utilizza A Funzione SQL.

CREATE TEMP FUNCTION addFourAndDivide(x INT64, y INT64) AS ((x + 4) / y);WITH numbers AS (SELECT 1 as val UNION ALL SELECT 3 as val UNION ALL SELECT 4 as val UNION ALL SELECT 5 as val)SELECT val, addFourAndDivide(val, 2) AS resultFROM numbers;+-----+--------+| val | result |+-----+--------+| 1 | 2.5 || 3 | 3.5 || 4 | 4 || 5 | 4.5 |+-----+--------+

Nell’esempio seguente, viene visualizzato un UDF SQL che utilizza un parametro con un modello con un modello. La funzione risultante accetta argomenti di vari tipi.

CREATE TEMP FUNCTION addFourAndDivideAny(x ANY TYPE, y ANY TYPE) AS ( (x + 4) / y);SELECT addFourAndDivideAny(3, 4) AS integer_output, addFourAndDivideAny(1.59, 3.14) AS floating_point_output;+----------------+-----------------------+| integer_output | floating_point_output |+----------------+-----------------------+| 1.75 | 1.7802547770700636 |+----------------+-----------------------+

Nell’esempio seguente, un UDF SQL che utilizza un parametro con il modello per mostrare l’ultimo elemento di una disposizione di qualsiasi tipo.

CREATE TEMP FUNCTION lastArrayElement(arr ANY TYPE) AS ( arr);SELECT names AS first_name, lastArrayElement(names) AS last_nameFROM ( SELECT AS names UNION ALL SELECT );+------------+-----------+| first_name | last_name |+------------+-----------+| Fred | Rogers || Marie | Curie |+------------+-----------+

Nell’esempio seguente, un UDF SQL che utilizza un subconsulto scalare per contare il numero di utenti con un’età determinata in una tabella utente .

javascript udf

Creare un UDF JavaScript dalla seguente struttura.

CREATE FUNCTION dataset_name.]function_name (]) RETURNS data_type LANGUAGE js AS javascript_code

I tipi di dati UDF JavaScript supportati

Alcuni tipi SQL corrispondono direttamente a tipi JavaScript, ma altri non lo fanno. BigQuery rappresenta i tipi come segue:

Tipo di dati di BigQuery JavaScript Tipo dati
Array Array
BOOL Boolean
bytes stringa codificata su base64
float64 numero
numerico, bignumerico (anteprima) Se un valore numerico può essere accuratamente rappresentato come un valore di IEEE Punto 754 flottante e non ha una parte frazionata , è codificato come un numero. Questi valori sono nell’intervallo. Altrimenti, è codificato come una stringa.
stringa
struct oggetto in cui ciascuno Struct Field ha un nome
Timestamp Data con un campo di microsecondo contenente la frazione di microsecond del Timestamp
data data

Poiché JavaScript non supporta un tipo intero a 64 bit, INT64 non è compatibile come tipo di ingresso per JavaScript UDF. Invece, utilizzare FLOAT64 per rappresentare valori come un numero intero o per rappresentarli come una stringa.

Supporti bigQuery INT64 come tipo di dati mostrato negli UDF JavaScript. In questo caso, il corpo della funzione JavaScript può mostrare una stringa o un numero di JavaScript. BigQuery converte quindi uno di questi tipi in INT64.

Se il valore di ritorno di JavaScript UDF è un Promise, BigQuery attende fino al Promise è stabilito. Se Promise è impostato in uno stato consegnato, BIGQuery mostra il suo risultato. Se Promise è impostato in uno stato respinto, BigQuery visualizza un errore.

Regole di citazione

È necessario racchiudere il codice JavaScript in citazioni. Per semplici frammenti di codice di una linea, è possibile utilizzare una stringa tra comillas standard:

CREATE TEMP FUNCTION plusOne(x FLOAT64)RETURNS FLOAT64LANGUAGE jsAS "return x+1;";SELECT val, plusOne(val) AS resultFROM UNNEST() AS val;+-----------+-----------+| val | result |+-----------+-----------+| 1 | 2 || 2 | 3 || 3 | 4 || 4 | 5 || 5 | 6 |+-----------+-----------+

nei casi in cui il frammento contiene quotazioni o consiste di diverse linee, utilizzare Blocchi con tripli citazioni:

CREATE TEMP FUNCTION customGreeting(a STRING)RETURNS STRINGLANGUAGE js AS r""" var d = new Date(); if (d.getHours() 

Include librerie javascript

È possibile estendere il tuo UDF JavaScript con la sezione OPTIONS. In questa sezione, è consentito specificare le librerie di codici esterni per l’UDF.

CREATE TEMP FUNCTION myFunc(a FLOAT64, b STRING) RETURNS STRING LANGUAGE js OPTIONS ( library= ) ASr""" // 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 nel dell’UDF.

Esempi di JavaScript UDF

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)RETURNS FLOAT64LANGUAGE 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 productFROM numbers;+-----+-----+--------------+| x | y | product |+-----+-----+--------------+| 1 | 5 | 5 || 2 | 10 | 20 || 3 | 15 | 45 |+-----+-----+--------------+

È possibile passare il risultato di un UDF come voce per un altro UDF. Ad esempio:

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)RETURNS FLOAT64LANGUAGE js AS r""" return x*y;""";CREATE TEMP FUNCTION divideByTwo(x FLOAT64)RETURNS FLOAT64LANGUAGE js AS r""" return x/2;""";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(divideByTwo(x), divideByTwo(y)) as half_productFROM numbers;+-----+-----+--------------+| x | y | half_product |+-----+-----+--------------+| 1 | 5 | 1.25 || 2 | 10 | 5 || 3 | 15 | 11.25 |+-----+-----+--------------+

Nell’esempio seguente, i valori di tutte le chiamate vengono aggiunti foo IN La stringa JSON data.

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 != null) { if (typeof obj == "object") { sum += SumFoo(obj); } else if (field == "foo") { sum += obj; } } } 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_sumFROM Input AS t;+---------------------------------------------------------------------+---------+| 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 |+---------------------------------------------------------------------+---------+

Se desideri ulteriori informazioni per assegnare i tipi di dati bigQuery a tipi JavaScript, consultare i tipi di dati UDF JavaScript ammessi

Raccomandazioni per JavaScript UDFS

Eseguire un filtro prima della voce

Se la voce può essere facilmente filtrata prima di passarlo a un UDF JavaScript, è probabile che sia probabile che la tua query è più veloce e più veloce.

evita il persistente stato mutabile

non magazzini o accedere a una chiamata a stato mutabile all’UDF JavaScript.

Usa la memoria Effettivamente

L’ambiente di elaborazione JavaScript ha un limite di memoria disponibile per consultazione. Le query UDF JavaScript si accumulano anche lo stato locale può non riuscire a causa dell’applemento della memoria.

Esegui una query con un UDF

utilizza la console del cloud.

È possibile utilizzare la console cloud per eseguire query con uno o più UDF.

  1. Fare clic su Draft New Query.
  2. Nel pannello Editor QueRUE, immettere la dichiarazione UDF. Ad esempio:

  3. Sotto l’istruzione UDF, immettere la query SQL. Ad esempio:

    SELECT timesTwo(numbers) AS doublesFROM UNNEST() AS numbers;
  4. Fare clic sulla query di esecuzione. I risultati della query vengono visualizzati sotto i pulsanti.

Utilizzare lo strumento BQ

riga di comando. È possibile utilizzare lo strumento Lineo di del cloud SDK per eseguire una query che contiene uno o più UDF.

Utilizzare la seguente sintassi per eseguire una query con un UDF:

bq query <statement_with_udf_and_query>

UDF autorizzato

Un UDF autorizzato è un UDF che è autorizzato ad accedere a un particolare set di dati. UDF può consultare le tabelle nel set di dati, anche se L’utente che chiama l’UDF non ha accesso a queste tabelle.

Gli UDF autorizzati consentono di condividere i risultati delle query con utenti o gruppi specifici senza fornire tali utenti o gruppi di accesso alle tabelle sottostanti. Ad esempio, un UDF autorizzato può calcolare un’aggregazione nei dati o cercare un valore di tabella e utilizzare tale valore in un calcolo.

Per autorizzare un UDF, è possibile utilizzare Google Cloud Console, Rest API il comando -Line strumento di bq:

console

  1. Vai alla pagina BigQuery in Cloud Console.

    Vai a BigQuery

  2. Nel riquadro di navigazione, nella sezione Risorse, espandere il progetto e selezionare un set di dati.

  3. Nel riquadro dei dettagli, fare clic su Autorizza le routine.

  4. Nella pagina di routine autorizzata, nella sezione Autorize Routine, selezionare l’ID del progetto, il set di dati ID e l’ID di routine che si desidera autorizzare.

  5. Fai clic su Aggiungi autorizzazione.

API

  1. Chiama il metodo datasets.get Per ripristinare il set di dati desiderato l’UDF. L’organismo di risposta contiene una rappresentazione della risorsa Dataset.

  2. Aggiungi il seguente oggetto JSON all’array access nella risorsa Dataset:

    { "routine": { "datasetId": "DATASET_NAME", "projectId": "PROJECT_ID", "routineId": "ROUTINE_NAME" }}

    qui:

    • Dataset_name è il nome del set di dati che contiene l’UDF.
    • Project_ID è l’ID del progetto che contiene l’UDF.
    • routine_name è il nome dell’UDF.
  3. Chiama il metodo dataset.update con la rappresentazione Dataset modificato.

BQ

  1. Utilizzare il comando bq show per ottenere la rappresentazione JSON del set di dati che si desidera accedere all’UDF. Il risultato del comando è una rappresentazione JSON della risorsa Dataset .Guard il risultato in un file locale.

    bq show --format=prettyjson TARGET_DATASET > dataset.json

    Sostituisci Target_Dataset con il nome del set di dati impostato su cui l’UDF avrà.

  2. Modifica il file per aggiungere il seguente oggetto JSON all’array access nella risorsa Dataset:

    { "routine": { "datasetId": "DATASET_NAME", "projectId": "PROJECT_ID", "routineId": "ROUTINE_NAME" }}

    qui:

    • Dataset_name è il nome del set di dati che contiene l’UDF.
    • progetto_id è l’ID del progetto che contiene l’UDF.
    • routine_name è il nome dell’UDF.
  3. Utilizzare il comando per aggiornare il file.

    bq update --source dataset.json TARGET_DATASET

Esempio di UDF autorizzato

Sotto, viene visualizzato un esempio di creazione e uso di un UDF autorizzato.

  1. Crea due set di dati chiamati private_dataset e public_dataset. Per ulteriori informazioni sulla creazione di un set di dati, query crea un set di dati.

  2. Esegui la seguente istruzione per creare una tabella chiamata private_table in private_dataset:

    CREATE OR REPLACE TABLE private_dataset.private_tableAS SELECT key FROM UNNEST() key;
  3. Esegue la seguente dichiarazione per creare una chiamata UDF count_key in public_dataset. L’UDF include una dichiarazione in private_table.

    CREATE OR REPLACE FUNCTION public_dataset.count_key(input_key STRING)RETURNS INT64AS((SELECT COUNT(1) FROM private_dataset.private_table t WHERE t.key = input_key));
  4. concede la funzione bigquery.dataViewer a un utente nel set di dati public_dataset. Questa funzione include l’autorizzazione bigquery.routines.get, che consente all’utente di chiamare la funzione. Se si desidera ottenere informazioni per assegnare i controlli di accesso a un set di dati, la consultazione controlla l’accesso ai set di dati.

  5. A questo punto, l’utente ha il permesso di chiamare il , ma non può accedere alla tabella in private_dataset. Se l’utente tenta di chiamare la funzione, riceverai un messaggio di errore simile al seguente:

    Access Denied: Table myproject:private_dataset.private_table: User doesnot have permission to query table myproject:private_dataset.private_table.
  6. con lo strumento Line , immettere il comando show come mostrato di seguito:

    Il risultato viene salvato in un file locale denominato dataset.json.

  7. Modifica dataset.json Per aggiungere il seguente oggetto JSON all’array access:

    { "routine": { "datasetId": "public_dataset", "projectId": "PROJECT_ID", "routineId": "count_key" }}

    Sostituisci progetto_ID con l’ID del progetto per public_dataset.

  8. con lo strumento della riga di comando di bq, immettere il come mostrato di seguito:

    bq update --source dataset.json private_dataset
  9. per verificare che l’UDF abbia accesso A private_dataset, l’utente può eseguire la seguente query:

Aggiungi descrizioni a UDF

Per aggiungere una descrizione a un UDF, attenersi alla seguente procedura:

Console

  1. Vai alla pagina BigQuery in Cloud Console.

    Vai a BigQuery

  2. In Pannello esploratore, espandere il progetto e il set di dati, quindi selezionare la funzione.

  3. Nel pannello dei dettagli, fare clic sull’icona della matita accanto alla descrizione per modificare il testo della descrizione .

  4. Nella finestra di dialogo, immettere una descrizione nella casella o modificare la descrizione esistente. Fare clic su Aggiorna per salvare il nuovo testo Descrizione.

In alternativa, è possibile utilizzare una query SQL standard per aggiornare la descrizione utilizzando del campo OPTIONS. Nella casella Editor Query, immettere la definizione della funzione, quindi aggiungere la seguente riga:

OPTIONS (description="DESCRIPTION") AS """

Sostituzione della descrizione da parte della descrizione che si desidera aggiungere.

BQ

È possibile modificare la descrizione di una funzione dalla riga di comando utilizzando la sintassi bq query dell’UDF e lo strumento Linea di BQ comandi. Specifica SQL standard con un segno --nouse_legacy_sql o --use_legacy_sql=false e quindi immettere la definizione della funzione. Per impostare il parametro nel , aggiungi la seguente riga alla tua definizione:

OPTIONS (description="DESCRIPTION") AS """

Sostituisce la descrizione della descrizione da aggiungere.

Limitazioni

Le seguenti limitazioni si applicano a funzioni temporanee e persistenti definite dal Utente:

  • oggetti DOM non sono supportati Window e Node, né le funzioni che richiedono loro.
  • Le funzioni JavaScript basate sul codice nativo non sono supportate.

  • A JavaScript UDF può scaricare il tempo di attesa e impedire che la tua query sia completato. I tempi di attesa possono essere di soli 5 minuti, ma possono variare a seconda dei diversi fattori, incluso il tempo della CPU dell’utente che consuma la funzione e la dimensione degli ingressi e delle uscite nella funzione JavaScript.
  • Operazioni a livello bit in JavaScript Maniglia solo i 32 bit più significativi
  • Gli UDF sono soggetti a determinati limiti di frequenza e limiti di quota. Per ulteriori informazioni, vedere i limiti UDF.

Le seguenti limitazioni si applicano alle funzioni persistenti definite dall’utente:

  • ciascun set di dati può contenere solo un UDF persistente con lo stesso nome. Tuttavia, è possibile creare un UDF il cui nome è uguale a quello di una tabella nello stesso set di dati.
  • Quando si fa riferimento a un UDF persistente da un altro UDF persistente o una vista logica, è necessario qualificare il nome con il set di dati. Ad esempio:
    CREATE FUNCTION mydataset.referringFunction() AS (mydataset.referencedFunction());

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

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *