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 su 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. 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
);

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. 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 funzione emit utilizza un parametro: un oggetto JavaScript che rappresenta una singola riga di dati di output. La funzione emit 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 FDU

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 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 che memorizza il nome e l'età di una persona:

person RECORD REPEATED
  name STRING OPTIONAL
  age INTEGER OPTIONAL

Lo specificatore di input per il nome e l'età è:

['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 indicatori 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 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 della CLI Google Cloud. 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 UDF 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 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 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 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 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);"
    }
  }
}

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 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 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 esegue lo sharding della query in molti nodi. Ogni nodo dispone di un ambiente di elaborazione JavaScript autonomo che si accumula separati per numRows.

Utilizzare 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 UDF. L'opzione SELECT * FROM <UDF name>(...) non è supportata.

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 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, 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 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.
• 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