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 sulle 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 alla funzione "Map" 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 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 funzioneemitutilizza un solo parametro: un oggetto JavaScript che rappresenta una singola riga di dati di output. La funzioneemitpuò 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 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 nella tabella di input o nella sottoquery.
Per le colonne di input che sono record, devi specificare, nell'elenco delle colonne di input, i 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, in cui ogni oggetto ha una proprietà "name" e una proprietà "age". Ad esempio:
[ {name: 'alice', age: 23}, {name: 'bob', age: 64}, ... ]
Schema di output
Devi fornire a BigQuery lo schema o la struttura dei record prodotti dalla funzione definita dall'utente, rappresentati come JSON. Lo schema può contenere qualsiasi tipo di dati BigQuery supportato, inclusi i record nidificati. Gli identificatori di tipo supportati sono:
- boolean
- float
- integer
- disco
- string
- timestamp
Il codice di esempio seguente mostra la sintassi per i record nello schema di output. Ogni campo di output richiede un attributo name e type. I campi nidificati devono contenere anche un
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 console Google Cloud non supporta 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 il flag --udf_resource
nello strumento a riga di comando bq di Google Cloud CLI. Il valore del flag può essere un URI Cloud Storage (gs://...) o il percorso di un file locale. 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>
L'esempio seguente esegue una query che utilizza una funzione definita dall'utente archiviata in un file locale e una query SQL archiviata 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. Ad esempio, per archiviare la seguente funzione definita dall'utente urlDecode, crea un file denominato urldecode.js e incolla il seguente codice JavaScript nel 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 evitare che la riga di comando diventi troppo dettagliata. Ad esempio, puoi creare un file locale denominato query.sql e incollare nel file la seguente istruzione BigQuery.
#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 seguente comando 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 gli elementi userDefinedFunctionResources che forniscono il codice o le posizioni alle risorse di codice da utilizzare nella query. 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 ai file sorgente JavaScript archiviati in Cloud Storage.
I BLOB di codice JavaScript in linea vengono compilati nella sezione inlineCode di un elemento userDefinedFunctionResource. Tuttavia, il codice che verrà riutilizzato o a cui viene fatto riferimento in più query deve essere mantenuto in Cloud Storage e fatto riferimento come risorsa esterna.
Per fare riferimento a un file di origine JavaScript da Cloud Storage, imposta la sezione resourceURI dell'elemento userDefinedFunctionResource sull'URI gs:// del file.
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: un blob di codice in linea e un file lib.js da leggere da Cloud Storage. In questo
esempio, myFunc e la chiamata della registrazione per myFunc sono forniti
da 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 utilizzare il nostro strumento di test delle funzioni definite dall'utente per testare ed eseguire il debug della funzione senza dover pagare la fattura di BigQuery.
Prefiltra l'input
Se l'input può essere facilmente filtrato prima di essere trasmesso a una funzione definita dall'utente, la query sarà probabilmente più veloce e più economica.
Nell'esempio di esecuzione di una query, una sottoquery viene passata come input a urlDecode, anziché una tabella completa. 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 dovrebbe 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
su molti nodi. Ogni nodo dispone di un ambiente di elaborazione JavaScript autonomo che accumula valori separati per numRows.
Utilizza la memoria in modo efficiente
L'ambiente di elaborazione JavaScript ha una memoria disponibile limitata per query. Le query UDF che accumulano troppo stato locale potrebbero 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 di tipo 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 di circa 5 MB o inferiore.
- Ogni utente può eseguire contemporaneamente circa 6 query delle funzioni definite dall'utente in un progetto specifico. Se viene visualizzato un errore che indica che superi il limite di query simultanee, attendi alcuni minuti e riprova.
- Una funzione definita dall'utente può scadere e impedire il completamento della query. I timeout possono essere anche brevi di 5 minuti, ma possono variare in base a diversi fattori, tra cui la quantità di tempo di CPU dell'utente consumata dalla funzione e le 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 utilizzare risorse di codice più grandi, archivia 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,DocumenteNodee le funzioni che li richiedono non sono supportati. - 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.