Funzioni definite dall'utente in SQL precedente
Questo documento descrive come utilizzare le funzioni JavaScript definite dall'utente nella sintassi delle query SQL precedente. La sintassi delle query preferita per BigQuery è GoogleSQL. Per informazioni su funzioni definite dall'utente in GoogleSQL, consulta Funzioni definite dall'utente di GoogleSQL.
Il codice SQL precedente di BigQuery supporta le funzioni definite dall'utente scritte in JavaScript. Una funzione definita dall'utente è simile a "Mappa" in un MapReduce: prende una singola riga come input e produce zero o più righe come output. Potenzialmente, l'output può avere uno schema diverso rispetto all'input.
Per informazioni sulle funzioni definite dall'utente in GoogleSQL, consulta Funzioni definite dall'utente in GoogleSQL.
Esempio di funzione definita dall'utente
// UDF definition function urlDecode(row, emit) { emit({title: decodeHelper(row.title), requests: row.num_requests}); } // Helper function with error handling function decodeHelper(s) { try { return decodeURI(s); } catch (ex) { return s; } } // UDF registration bigquery.defineFunction( 'urlDecode', // Name used to call the function from SQL ['title', 'num_requests'], // Input column names // JSON representation of the output schema [{name: 'title', type: 'string'}, {name: 'requests', type: 'integer'}], urlDecode // The function reference );
Struttura della funzione definita dall'utente
function name(row, emit) { emit(<output data>); }
Le funzioni definite dall'utente di BigQuery operano su singole righe di una tabella o selezionano sotto forma di risultati delle query. La La funzione definita dall'utente ha due parametri formali:
row
: una riga di input.emit
: un hook utilizzato da BigQuery per raccogliere i dati di output. La La funzioneemit
richiede un parametro: un oggetto JavaScript che rappresenta una singola riga dei dati di output. La funzioneemit
può essere chiamata più di una volta, ad esempio in un loop, per generare più righe di dati.
Il codice di esempio riportato di seguito mostra una funzione definita dall'utente di base.
function urlDecode(row, emit) { emit({title: decodeURI(row.title), requests: row.num_requests}); }
Registrazione della funzione definita dall'utente
Devi registrare un nome per la funzione in modo che possa essere richiamata da BigQuery per l'addestramento e sa usare il linguaggio SQL. Il nome registrato non deve necessariamente corrispondere al nome utilizzato per la funzione in JavaScript.
bigquery.defineFunction( '<UDF name>', // Name used to call the function from SQL ['<col1>', '<col2>'], // Input column names // JSON representation of the output schema [<output schema>], // UDF definition or reference <UDF definition or reference> );
Colonne di input
I nomi delle colonne di input devono corrispondere ai nomi (o alias, se applicabili) delle colonne nel una tabella di input o una sottoquery.
Per le colonne di input che sono record, devi specificare, nell'elenco delle colonne di input, la campi foglia a cui vuoi accedere dal record.
Ad esempio, se hai un record in cui sono memorizzati il nome e l'età di una persona:
person RECORD REPEATED name STRING OPTIONAL age INTEGER OPTIONAL
L'indicatore di input per il nome e l'età sarebbe:
['person.name', 'person.age']
L'utilizzo di ['person']
senza il nome o l'età può generare un errore.
L'output risultante corrisponderà allo schema. avrai un array di oggetti JavaScript, ogni oggetto ha un "nome" e "età" proprietà. Ad esempio:
[ {name: 'alice', age: 23}, {name: 'bob', age: 64}, ... ]
Schema di output
Devi fornire a BigQuery lo schema o la struttura dei record della tua funzione definita dall'utente prodotti, rappresentati come JSON. Lo schema può contenere qualsiasi tipo di dati BigQuery supportato, inclusi i record nidificati. Gli identificatori di tipo supportati sono:
- boolean
- numero in virgola mobile
- integer
- disco
- string
- timestamp
Il codice di esempio seguente mostra la sintassi per i record nello schema di output. Ogni campo di output
richiede gli attributi name
e type
. I campi nidificati devono contenere anche
Attributo fields
.
[{name: 'foo_bar', type: 'record', fields: [{name: 'a', type: 'string'}, {name: 'b', type: 'integer'}, {name: 'c', type: 'boolean'}] }]
Ogni campo può contenere un attributo mode
facoltativo, che supporta i seguenti valori:
- nullable : questa è l'impostazione predefinita e può essere omessa.
- obbligatorio : se specificato, il campo specificato deve essere impostato su un valore e non può essere indefinito.
- ripetuto : se specificato, il campo specificato deve essere una matrice.
Le righe passate alla funzione emit()
devono corrispondere ai tipi di dati dello schema di output.
I campi rappresentati nello schema di output che sono omessi nella funzione emit restituiranno come valori null.
Definizione o riferimento della funzione definita dall'utente
Se preferisci, puoi definire la funzione definita dall'utente incorporata in bigquery.defineFunction
. Ad esempio:
bigquery.defineFunction( 'urlDecode', // Name used to call the function from SQL ['title', 'num_requests'], // Input column names // JSON representation of the output schema [{name: 'title', type: 'string'}, {name: 'requests', type: 'integer'}], // The UDF function(row, emit) { emit({title: decodeURI(row.title), requests: row.num_requests}); } );
In caso contrario, puoi definire la funzione definita dall'utente separatamente e passare un riferimento alla funzione in
bigquery.defineFunction
. Ad esempio:
// The UDF function urlDecode(row, emit) { emit({title: decodeURI(row.title), requests: row.num_requests}); } // UDF registration bigquery.defineFunction( 'urlDecode', // Name used to call the function from SQL ['title', 'num_requests'], // Input column names // JSON representation of the output schema [{name: 'title', type: 'string'}, {name: 'requests', type: 'integer'}], urlDecode // The function reference );
Gestione degli errori
Se viene generato un'eccezione o un errore durante l'elaborazione di una funzione definita dall'utente, l'intera query avrà esito negativo. Puoi utilizzare un blocco di prova per gestire gli errori. Ad esempio:
// The UDF function urlDecode(row, emit) { emit({title: decodeHelper(row.title), requests: row.num_requests}); } // Helper function with error handling function decodeHelper(s) { try { return decodeURI(s); } catch (ex) { return s; } } // UDF registration bigquery.defineFunction( 'urlDecode', // Name used to call the function from SQL ['title', 'num_requests'], // Input column names // JSON representation of the output schema [{name: 'title', type: 'string'}, {name: 'requests', type: 'integer'}], urlDecode // The function reference );
Esecuzione di una query con una funzione definita dall'utente
Puoi utilizzare le funzioni definite dall'utente in SQL precedente con lo strumento a riga di comando bq o l'API BigQuery. La Google Cloud Console non e supportare le funzioni definite dall'utente in SQL precedente.
Utilizzo dello strumento a riga di comando bq
Per eseguire una query contenente una o più funzioni definite dall'utente, specifica --udf_resource
nello strumento a riga di comando bq da Google Cloud CLI. Il valore del flag può essere
a un URI Cloud Storage (gs://...
) o al percorso di
. Per specificare più file di risorse delle funzioni definite dall'utente, ripeti questo flag.
Utilizza la seguente sintassi per eseguire una query con una funzione definita dall'utente:
bq query --udf_resource=<file_path_or_URI> <sql_query>
Nell'esempio seguente viene eseguita una query che utilizza una funzione definita dall'utente archiviata in un file locale e una query SQL archiviati anche in un file locale.
Creazione della funzione definita dall'utente
Puoi archiviare la funzione definita dall'utente in Cloud Storage o come file di testo locale. Per
esempio, per archiviare la seguente funzione definita dall'utente urlDecode
, crea un file
denominato urldecode.js
e incolla il seguente codice JavaScript
il file prima di salvarlo.
// UDF definition function urlDecode(row, emit) { emit({title: decodeHelper(row.title), requests: row.num_requests}); } // Helper function with error handling function decodeHelper(s) { try { return decodeURI(s); } catch (ex) { return s; } } // UDF registration bigquery.defineFunction( 'urlDecode', // Name used to call the function from SQL ['title', 'num_requests'], // Input column names // JSON representation of the output schema [{name: 'title', type: 'string'}, {name: 'requests', type: 'integer'}], urlDecode // The function reference );
Creazione della query
Puoi anche archiviare la query in un file per mantenere la riga di comando
per evitare un livello di dettaglio eccessivo. Ad esempio, puoi creare un'istanza
file denominato query.sql
e incolla il seguente codice BigQuery
nel file.
#legacySQL SELECT requests, title FROM urlDecode( SELECT title, sum(requests) AS num_requests FROM [fh-bigquery:wikipedia.pagecounts_201504] WHERE language = 'fr' GROUP EACH BY title ) WHERE title LIKE '%ç%' ORDER BY requests DESC LIMIT 100
Dopo aver salvato il file, puoi farvi riferimento nella riga di comando.
Esecuzione della query
Dopo aver definito la funzione definita dall'utente e la query in file separati,
puoi farvi riferimento nella riga di comando.
Ad esempio, il comando seguente esegue la query
che hai salvato come file denominato query.sql
e fa riferimento alla funzione definita dall'utente che hai creato.
$ bq query --udf_resource=urldecode.js "$(cat query.sql)"
Utilizzo dell'API BigQuery
configuration.query
Le query che utilizzano le funzioni definite dall'utente devono contenere
userDefinedFunctionResources
gli elementi che forniscono il codice o le posizioni delle risorse di codice da utilizzare nella query. La
il codice fornito deve includere le chiamate di funzioni di registrazione per tutte le funzioni definite dall'utente a cui fa riferimento la query.
Risorse di codice
La configurazione della query potrebbe includere BLOB di codice JavaScript, nonché riferimenti a JavaScript archiviati in Cloud Storage.
I BLOB di codice JavaScript in linea vengono inseriti nella
inlineCode
di un elemento userDefinedFunctionResource
. Tuttavia, il codice che verrà riutilizzato
o a cui viene fatto riferimento in più query, devono essere mantenuti in Cloud Storage e indicati come
risorsa esterna.
Per fare riferimento a un file di origine JavaScript da Cloud Storage, imposta la classe
resourceURI
dell'elemento userDefinedFunctionResource
nella colonna gs://
del file
URI.
La configurazione della query può contenere più elementi userDefinedFunctionResource
.
Ogni elemento può contenere una sezione inlineCode
o resourceUri
.
Esempio
L'esempio JSON seguente illustra una richiesta di query che fa riferimento a due risorse UDF: uno
un blob di codice in linea e un file lib.js
da leggere da Cloud Storage. In questo
ad esempio myFunc
e la chiamata di registrazione per myFunc
di lib.js
.
{ "configuration": { "query": { "userDefinedFunctionResources": [ { "inlineCode": "var someCode = 'here';" }, { "resourceUri": "gs://some-bucket/js/lib.js" } ], "query": "select a from myFunc(T);" } } }
Best practice
Sviluppo della funzione definita dall'utente
Puoi usare la nostra funzione definita dall'utente di test per testare la funzione definita dall'utente ed eseguirne il debug senza dover pagare la fattura di BigQuery.
Prefiltra l'input
Se l'input può essere facilmente filtrato prima di essere passato a una funzione definita dall'utente, è probabile che la query in modo rapido ed economico.
Nell'esempio sull'esecuzione di una query, una sottoquery
viene passato come input a urlDecode
, invece che a una tabella completa. La
La tabella [fh-bigquery:wikipedia.pagecounts_201504]
ha circa 5,6 miliardi di righe,
e se eseguissimo la funzione definita dall'utente sull'intera tabella, il framework JavaScript avrebbe dovuto elaborare oltre 21
volte più righe rispetto a quanto farebbe con la sottoquery filtrata.
Evita lo stato modificabile permanente
Non archiviare o accedere allo stato modificabile nelle chiamate delle funzioni definite dall'utente. Il seguente esempio di codice descrive questo scenario:
// myCode.js var numRows = 0; function dontDoThis(r, emit) { emit({rowCount: ++numRows}); } // The query. SELECT max(rowCount) FROM dontDoThis(t);
L'esempio riportato sopra non si comporterà come previsto perché BigQuery esegue lo sharding della query
in molti nodi. Ogni nodo dispone di un ambiente di elaborazione JavaScript autonomo che si accumula
separati per numRows
.
Utilizza la memoria in modo efficiente
L'ambiente di elaborazione JavaScript ha una memoria disponibile limitata per query. delle funzioni definite dall'utente un eccesso di stato locale potrebbe non riuscire a causa dell'esaurimento della memoria.
Espandi query selezionate
Devi elencare esplicitamente le colonne selezionate da una funzione definita dall'utente.
L'opzione SELECT * FROM <UDF name>(...)
non è supportata.
Per esaminare la struttura dei dati delle righe di input, puoi utilizzare JSON.stringify()
per emettere
una colonna di output con stringa:
bigquery.defineFunction( 'examineInputFormat', ['some', 'input', 'columns'], [{name: 'input', type: 'string'}], function(r, emit) { emit({input: JSON.stringify(r)}); } );
Limiti
- La quantità di dati degli output della funzione definita dall'utente durante l'elaborazione di una singola riga dovrebbe essere approssimativamente Massimo 5 MB.
- Ogni utente può eseguire contemporaneamente circa 6 query delle funzioni definite dall'utente in un progetto specifico nel tempo. Se viene visualizzato un messaggio di errore che indica che hai superato limite di query simultanee, attendi qualche minuto e riprova.
- Una funzione definita dall'utente può scadere e impedire il completamento della query. I timeout possono essere brevi 5 minuti, ma può variare in base a diversi fattori, tra cui il tempo di utilizzo Tempo di CPU utilizzato dalla funzione e dimensioni degli input e degli output per la funzione JS.
- Un job di query può avere un massimo di 50 risorse delle funzioni definite dall'utente (BLOB di codice inline o file esterni).
- La dimensione massima di ciascun blob di codice inline è di 32 kB. Per usare una dimensione risorse di codice, archiviare il codice in Cloud Storage e farvi riferimento come risorsa esterna.
- La dimensione massima di ciascuna risorsa di codice esterna è di 1 MB.
- La dimensione cumulativa di tutte le risorse di codice esterne è limitata a un massimo di 5 MB.
Limitazioni
- Gli oggetti DOM
Window
,Document
eNode
e funzioni che le richiedono non sono supportate. - Le funzioni JavaScript che si basano su codice nativo non sono supportate.
- Le operazioni bit per bit in JavaScript gestiscono solo i 32 bit più significativi.
- A causa della loro natura non deterministica, le query che richiamano funzioni definite dall'utente non possono utilizzare i risultati memorizzati nella cache.