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 le funzioni definite dall'utente in BigQuery è la sintassi GoogleSQL. Per maggiori informazioni, consulta Disponibilità delle funzionalità SQL precedenti.

BigQuery SQL precedente 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ò potenzialmente avere uno schema diverso rispetto all'input.

Per informazioni sulle funzioni definite dall'utente in GoogleSQL, vedi 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 delle funzioni definite dall'utente

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

Le UDF BigQuery operano su singole righe di una tabella o sui risultati di una query di selezione secondaria. La 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 accetta 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 generare più righe di dati.

Il seguente esempio di codice mostra una UDF 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 corrispondere al nome che hai 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 applicabile) 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 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à sarebbe:

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

L'utilizzo di ['person'] senza il nome o l'età genererebbe 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 tua funzione definita dall'utente, rappresentati 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 : questa è l'impostazione predefinita e può essere omessa.
  • required : se specificato, il campo indicato deve essere impostato su un valore e non può essere indefinito.
  • repeated : se specificato, il campo indicato 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 che vengono omessi nella funzione emit verranno restituiti come valori nulli.

Definizione o riferimento della funzione definita dall'utente

Se preferisci, puoi definire la UDF inline 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 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
);

Esecuzione di una query con una funzione definita dall'utente

Puoi utilizzare le UDF in SQL precedente con lo strumento a riga di comando bq o l'API BigQuery. La Google Cloud console non supporta le UDF in SQL precedente.

Utilizzo dello strumento a riga di comando bq

Per eseguire una query contenente una o più UDF, 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 funzione definita dall'utente archiviata in un file locale e una query SQL anch'essa 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 la seguente istruzione BigQuery nel file.

#legacySQL
SELECT requests, title
FROM
  urlDecode(
    SELECT
      title, sum(requests) AS num_requests
    FROM
      [my-project: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 in corso…

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 UDF 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 alla funzione di registrazione per qualsiasi UDF a cui fa riferimento la query.

Risorse di codice

La configurazione della query può includere blob di codice JavaScript, nonché riferimenti a file sorgente JavaScript archiviati in Cloud Storage.

I blob di codice JavaScript inline vengono inseriti nella sezione inlineCode di un elemento userDefinedFunctionResource. Tuttavia, il codice che verrà riutilizzato o a cui verrà fatto riferimento in più query deve essere reso persistente 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 la chiamata 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 UDF per testare ed eseguire il debug delle UDF senza aumentare la fattura BigQuery.

Prefiltrare l'input

Se l'input può essere facilmente filtrato prima di essere passato a una UDF, è probabile che la query sia più veloce ed economica.

Nell'esempio esecuzione di una query, una sottoquery viene passata come input a urlDecode, anziché una tabella completa. Una tabella potrebbe avere miliardi di righe e, se eseguissimo la UDF sull'intera tabella, il framework JavaScript dovrebbe elaborare molte più righe rispetto a quelle che elaborerebbe con la sottoquery filtrata.

Evitare lo stato mutabile persistente

Non archiviare o accedere a uno stato modificabile tra le 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 precedente non si comporterà come previsto, perché BigQuery suddivide la query in molti nodi. Ogni nodo ha un ambiente di elaborazione JavaScript autonomo che accumula valori separati 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 le 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 stringa:

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 deve essere circa 5 MB o meno.
  • Ogni utente può eseguire circa 6 query UDF in un progetto specifico contemporaneamente. Se ricevi un errore che indica che hai superato il 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 durare anche solo 5 minuti, ma possono variare a seconda di diversi fattori, tra cui la quantità di tempo di CPU utilizzato 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 esso come risorsa esterna.
  • La dimensione massima di ciascuna risorsa di codice esterna è di 1 MB.
  • La dimensione cumulativa di tutte le risorse di codice esterno è limitata 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 risultati memorizzati nella cache.

Torna all'inizio