Funzioni definite dall'utente in SQL precedente

Questo documento descrive in dettaglio come utilizzare le funzioni definite dall'utente JavaScript nella sintassi delle query SQL precedente. La sintassi di query preferita per BigQuery è GoogleSQL. Per informazioni sulle funzioni definite dall'utente in GoogleSQL, consulta Funzioni definite dall'utente di GoogleSQL.

Il linguaggio SQL precedente di BigQuery supporta le funzioni definite dall'utente (UDF) scritte in JavaScript. Una UDF è simile alla funzione "Map" in MapReduce: accetta una singola riga come input e produce zero o più righe come output. L'output può avere uno schema potenzialmente diverso da quello dell'input.

Per informazioni sulle funzioni definite dall'utente in GoogleSQL, consulta Funzioni definite dall'utente in GoogleSQL.

Esempio di UDF

// 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
);

Torna all'inizio

Struttura della funzione definita dall'utente

function name(row, emit) {
  emit(<output data>);
}

Le UDF di BigQuery operano su singole righe di una tabella o sui risultati di query di sottoselezioni. L'udf ha due parametri formali:

• row: una riga di input.
• emit: un hook utilizzato da BigQuery per raccogliere i dati di output. La funzione emit utilizza un parametro: un oggetto JavaScript che rappresenta una singola riga di dati di output. La funzione emit può essere chiamata più volte, ad esempio in un ciclo, per produrre più righe di dati.

L'esempio di codice seguente mostra una UDF di base.

function urlDecode(row, emit) {
  emit({title: decodeURI(row.title),
        requests: row.num_requests});
}

Registrazione della FDU

Devi registrare un nome per la funzione in modo che possa essere richiamata da BigQuery SQL. Il nome registrato non deve corrispondere a quello 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 agli 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 di primo livello a cui vuoi accedere dal record.

Ad esempio, se hai un record che memorizza il nome e l'età di una persona:

person RECORD REPEATED
  name STRING OPTIONAL
  age INTEGER OPTIONAL

Il parametro di input per il nome e l'età sarà:

['person.name', 'person.age']

L'utilizzo di ['person'] senza il nome o l'età genera un errore.

L'output risultante corrisponderà allo schema; avrai un array di oggetti JavaScript, in cui ogni oggetto ha una proprietà "name" e una "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 tua UDF, rappresentata come JSON. Lo schema può contenere qualsiasi tipo di dati BigQuery supportato, inclusi i record nidificati. Gli specificatori di tipo supportati sono:

• boolean
• float
• integer
• disco
• string
• timestamp

Il seguente esempio di codice 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 : è il valore predefinito e può essere omesso.
• required : se specificato, il campo specificato deve essere impostato su un valore e non può essere non definito.
• ripetuto : se specificato, il campo specificato deve essere un array.

Le righe passate alla funzione emit() devono corrispondere ai tipi di dati dello schema di output. I campi rappresentati nello schema di output omessi nella funzione emit verranno visualizzati come null.

Definizione o riferimento della funzione definita dall'utente

Se preferisci, puoi definire la UDF in linea 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 UDF 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 generata un'eccezione o un errore durante l'elaborazione di una UDF, l'intera query non andrà a buon fine. Puoi utilizzare un blocco try-catch 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
);

Eseguire una query con una FDU

Puoi utilizzare le funzioni definite dall'utente in SQL precedente con lo strumento a riga di comando bq o con 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 UDF, ripeti questo flag.

Utilizza la seguente sintassi per eseguire una query con una UDF:

bq query --udf_resource=<file_path_or_URI> <sql_query>

L'esempio seguente esegue una query che utilizza una UDF archiviata in un file locale e una query SQL anche questa archiviata in un file locale.

Creazione della funzione definita dall'utente

Puoi archiviare la FDU 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 memorizzare la query in un file per evitare che la riga di comando sia troppo dettagliata. Ad esempio, puoi creare un file locale query.sql e incollare nel file la seguente dichiarazione 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 averlo salvato, puoi fare riferimento al file nella riga di comando.

Esecuzione della query

Dopo aver definito la FDU 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 UDF 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 elementi userDefinedFunctionResources che forniscono il codice o le posizioni delle risorse di codice da utilizzare nella query. Il codice fornito deve includere le chiamate alle funzioni di registrazione per eventuali funzioni definite dall'utente a cui fa riferimento la query.

Risorse di codice

La configurazione della query può includere blob di codice JavaScript, nonché riferimenti ai file di codice 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 verrà fatto riferimento in più query deve essere mantenuto in Cloud Storage e a cui deve essere 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

Il seguente esempio JSON illustra una richiesta di query che fa riferimento a due risorse UDF: un blob di codice inline e un file lib.js da leggere da Cloud Storage. In questo esempio, myFunc e l'invocazione di 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);"
    }
  }
}

Torna all'inizio

Best practice

Sviluppo della funzione definita dall'utente

Puoi utilizzare il nostro strumento di test delle funzioni UDF per testare e eseguire il debug delle funzioni UDF senza aumentare la fattura di BigQuery.

Filtrare in anticipo l'input

Se i dati di input possono essere facilmente filtrati prima di essere passati a una UDF, la query sarà probabilmente più rapida ed economica.

Nell'esempio di esecuzione di una query, viene passata una sottoquery come input a urlDecode anziché una tabella completa. La tabella [fh-bigquery:wikipedia.pagecounts_201504] ha circa 5,6 miliardi di righe e, se eseguiamo la UDF sull'intera tabella, il framework JavaScript deve elaborare più di 21 volte il numero di righe rispetto alla sottoquery filtrata.

Evitare stati mutabili persistenti

Non memorizzare o accedere allo stato mutabile nelle chiamate UDF. 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 suddivide la query su molti nodi. Ogni nodo ha un ambiente di elaborazione JavaScript autonomo che accumula valori distinti per numRows.

Utilizzare la memoria in modo efficiente

L'ambiente di elaborazione JavaScript ha una memoria limitata disponibile 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 UDF. SELECT * FROM <UDF name>(...) non è supportato.

Per esaminare la struttura dei dati della riga di input, puoi utilizzare JSON.stringify() per emettere una colonna di output di stringhe:

bigquery.defineFunction(
  'examineInputFormat',
  ['some', 'input', 'columns'],
  [{name: 'input', type: 'string'}],
  function(r, emit) {
    emit({input: JSON.stringify(r)});
  }
);

Torna all'inizio

Limiti

• La quantità di dati degli output delle funzioni definite dall'utente durante l'elaborazione di una singola riga deve essere di circa 5 MB o meno.
• Ogni utente può eseguire contemporaneamente circa 6 query UDF in un progetto specifico. Se ricevi un messaggio di errore che indica che hai superato il limite di query simultanee, attendi qualche minuto e riprova.
• Una FDU può scadere e impedire il completamento della query. I timeout possono essere brevi, anche di 5 minuti, ma possono variare a seconda di diversi fattori, tra cui la quantità di tempo di CPU dell'utente utilizzata dalla funzione e le dimensioni degli input e degli output della funzione JS.
• Un job di query può avere un massimo di 50 risorse UDF (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 fai riferimento a queste risorse come risorse esterne.
• La dimensione massima di ciascuna risorsa di codice esterna è di 1 MB.
• Le dimensioni cumulative di tutte le risorse di codice esterno sono limitate a un massimo di 5 MB.

Torna all'inizio

Limitazioni

• Gli oggetti DOM Window, Document e Node e 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.

Torna all'inizio