Benutzerdefinierte Funktionen in Legacy-SQL

In diesem Dokument wird beschrieben, wie Sie benutzerdefinierte JavaScript-Funktionen in der Abfragesyntax des Legacy-SQLs verwenden. Die bevorzugte Abfragesyntax für BigQuery ist GoogleSQL. Weitere Informationen zu benutzerdefinierten Funktionen in GoogleSQL finden Sie unter Benutzerdefinierte GoogleSQL-Funktionen.

Der Legacy-SQL-Dialekt von BigQuery unterstützt benutzerdefinierte Funktionen (User-Defined Functions, UDFs), die in JavaScript geschrieben sind. UDFs ähneln der Funktion "Map" in MapReduce: Sie verwenden eine einzelne Zeile als Eingabe und erzeugen null oder mehr Zeilen als Ausgabe. Die Ausgabe kann potenziell ein anderes Schema als die Eingabe haben.

Weitere Informationen zu benutzerdefinierten Funktionen in GoogleSQL finden Sie unter Benutzerdefinierte Funktionen in GoogleSQL.

UDF-Beispiel

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

Nach oben

UDF-Struktur

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

BigQuery-UDFs verarbeiten einzelne Zeilen einer Tabelle oder Zeilen von Subselect-Abfrageergebnissen. UDFs haben zwei formale Parameter:

• row: eine Eingabezeile
• emit: Einen von BigQuery zum Sammeln von Ausgabedaten verwendeten Hook. Die Funktion emit verwendet einen Parameter: ein JavaScript-Objekt, das eine einzelne Zeile mit Ausgabedaten darstellt. Die Funktion emit kann mehrfach aufgerufen werden, beispielsweise in einer Schleife, um mehrere Datenzeilen auszugeben.

Das folgende Codebeispiel zeigt eine einfache UDF.

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

UDF registrieren

Sie müssen einen Namen für die Funktion registrieren, damit sie aus BigQuery-SQL aufgerufen werden kann. Der registrierte Name muss nicht mit dem Namen übereinstimmen, den Sie für Ihre Funktion in JavaScript verwendet haben.

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

Eingabespalten

Die Namen der Eingabespalten müssen mit den Namen (oder gegebenenfalls Aliasnamen) der Spalten in der Eingabetabelle oder Unterabfrage übereinstimmen.

Bei Eingabespalten, die Datensätze darstellen, müssen Sie – in der Eingabespaltenliste – die Blattfelder angeben, auf die Sie aus dem Datensatz zugreifen möchten.

Beispiel: Bei einem Datensatz, in dem Name und Alter einer Person gespeichert ist:

person RECORD REPEATED
  name STRING OPTIONAL
  age INTEGER OPTIONAL

wäre der Eingabebezeichner für Name und Alter:

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

Die Verwendung von ['person'] ohne "name" oder "age" würde zu einem Fehler führen.

Die Ergebnisausgabe stimmt mit dem Schema überein. Sie erhalten ein Array mit JavaScript-Objekten, in dem jedes Objekt die Attribute für Name und Alter "name" bzw. "age" hat. Beispiel:

[ {name: 'alice', age: 23}, {name: 'bob', age: 64}, ... ]

Ausgabeschema

Sie müssen BigQuery das Schema oder die Struktur der Datensätze, die Ihre UDF erzeugt, im JSON-Format bereitstellen. Das Schema kann beliebige unterstützte BigQuery-Datentypen enthalten, einschließlich verschachtelter Datensätze. Folgende Typbezeichner werden unterstützt:

• boolean
• float
• Ganzzahl
• Eintrag
• String
• timestamp

Das folgende Codebeispiel zeigt die Syntax für Datensätze im Ausgabeschema. Jedes Ausgabefeld erfordert die Attribute name und type. Verschachtelte Felder müssen außerdem das Attribut fields enthalten.

[{name: 'foo_bar', type: 'record', fields:
  [{name: 'a', type: 'string'},
   {name: 'b', type: 'integer'},
   {name: 'c', type: 'boolean'}]
}]

Jedes Feld kann das optionale Attribut mode enthalten, das die folgenden Werte unterstützt:

• Nullwerte zulässig: Dies ist der Standardwert und kann weggelassen werden.
• required (Erforderlich): Wenn dieser Wert angegeben wird, muss das angegebene Feld auf einen Wert festgelegt sein und darf nicht undefiniert sein.
• Wiederholt: Wenn dieser Wert angegeben wird, muss das angegebene Feld ein Array sein.

Zeilen, die an die Funktion emit() übergeben werden, müssen die Datentypen des Ausgabeschemas haben. Im Ausgabeschema dargestellte Felder, die in der Funktion "emit" weggelassen werden, werden als Nullwerte ausgegeben.

UDF-Definition oder Referenz

Sie können die UDF wahlweise inline in bigquery.defineFunction definieren. Beispiel:

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

Alternativ können Sie die UDF separat definieren und in bigquery.defineFunction eine Referenz zu dieser Funktion verwenden. Beispiel:

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

Fehlerbehandlung

Wenn eine Ausnahme oder ein Fehler während der Verarbeitung einer UDF ausgelöst wird, schlägt die ganze Abfrage fehl. Sie können einen Try/Catch-Block zur Fehlerbehandlung verwenden. Beispiel:

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

Abfrage mit einer UDF ausführen

Sie können UDFs in Legacy-SQL mit dem bq-Befehlszeilentool oder der BigQuery API verwenden. Die Google Cloud Console unterstützt keine UDFs in Legacy-SQL.

bq-Befehlszeilentool verwenden

Zum Ausführen einer Abfrage, die eine oder mehrere UDFs enthält, geben Sie das Flag --udf_resource im bq-Befehlszeilentool der Google Cloud CLI an. Als Flag-Wert können Sie wahlweise einen Cloud Storage-URI (gs://...) oder den Pfad zu einer lokalen Datei angeben. Wiederholen Sie dieses Flag, um mehrere UDF-Ressourcendateien anzugeben.

Verwenden Sie die folgende Syntax zum Ausführen einer Abfrage mit einer UDF:

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

Das folgende Beispiel führt eine Abfrage aus, die eine UDF, die in einer lokalen Datei gespeichert ist, und eine SQL-Abfrage verwendet, die ebenfalls in einer lokalen Datei gespeichert ist.

UDF erstellen

Sie können die UDF in Cloud Storage oder als lokale Textdatei speichern. Wenn Sie beispielsweise die folgende urlDecode-UDF speichern möchten, erstellen Sie eine Datei mit dem Namen urldecode.js und fügen Sie den folgenden JavaScript-Code in die Datei ein, bevor Sie die Datei speichern.

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

Abfrage erstellen

Sie können die Abfrage auch in einer Datei speichern, damit die Befehlszeile nicht zu detailliert wird. Erstellen Sie beispielsweise eine lokale Datei namens query.sql und fügen Sie die folgende BigQuery-Anweisung in die Datei ein.

#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

Nachdem Sie die Datei gespeichert haben, können Sie sie in der Befehlszeile referenzieren.

Abfrage ausführen

Nachdem Sie die UDF und die Abfrage in separaten Dateien definiert haben, können Sie sie in der Befehlszeile referenzieren. Mit dem folgenden Befehl werden beispielsweise die in der Datei query.sql gespeicherte Abfrage ausgeführt und die von Ihnen erstellte UDF referenziert.

$ bq query --udf_resource=urldecode.js "$(cat query.sql)"

BigQuery API verwenden

configuration.query

Abfragen, in denen UDFs verwendet werden, müssen Elemente vom Typ userDefinedFunctionResources enthalten, die den Code oder Speicherorte von Coderessourcen angeben, die in der Abfrage verwendet werden sollen. Der angegebene Code muss Funktionsaufrufe zur Registrierung aller UDFs enthalten, die von der Abfrage referenziert werden.

Coderessourcen

Die Abfragekonfiguration kann JavaScript-Code-Blobs sowie Verweise auf JavaScript-Quelldateien enthalten, die in Cloud Storage enthalten sind.

JavaScript-Inline-Code-Blobs werden im Abschnitt inlineCode des Elements userDefinedFunctionResource angegeben. Code, der wiederverwendet oder über mehrere Abfragen hinweg referenziert wird, muss jedoch in Cloud Storage beibehalten und als externe Ressource referenziert werden.

Wenn Sie eine JavaScript-Quelldatei in Cloud Storage referenzieren möchten, geben Sie im Abschnitt resourceURI des Elements userDefinedFunctionResource den gs://-URI der Datei an.

Die Abfragekonfiguration kann mehrere Elemente vom Typ userDefinedFunctionResource enthalten. Jedes Element kann den Abschnitt inlineCode oder resourceUri enthalten.

Beispiel

Das folgende JSON-Beispiel stellt eine Abfrage dar, die zwei UDF-Ressourcen referenziert: Ein Inline-Code-Blob und die Datei lib.js, die aus Cloud Storage gelesen werden muss. In diesem Beispiel werden myFunc und der Registrierungsaufruf für myFunc von lib.js bereitgestellt.

{
  "configuration": {
    "query": {
      "userDefinedFunctionResources": [
        {
          "inlineCode": "var someCode = 'here';"
        },
        {
          "resourceUri": "gs://some-bucket/js/lib.js"
        }
      ],
      "query": "select a from myFunc(T);"
    }
  }
}

Nach oben

Best Practices

UDF entwickeln

Sie können unser UDF-Testtool verwenden, um Ihre UDF zu testen und zu korrigieren, ohne dass dies Ihre BigQuery-Rechnung erhöht.

Eingabe vorfiltern

Wenn die Eingabe einfach gefiltert werden kann, bevor sie an eine UDF übergeben wird, wird die Abfrage wahrscheinlich schneller und kostengünstiger.

Im Beispiel Abfrage ausführen wird anstelle einer vollständigen Tabelle eine Unterabfrage als Eingabe für urlDecode übergeben. Die Tabelle [fh-bigquery:wikipedia.pagecounts_201504] enthält ca. 5,6 Milliarden Zeilen. Wenn die UDF für die gesamte Tabelle ausgeführt wird, muss das JavaScript Framework mehr als das 21-Fache an Zeilen verarbeiten als bei einer gefilterten Unterabfrage.

Persistenten änderbaren Status vermeiden

Speichern oder rufen Sie keinen änderbaren Status über UDF-Aufrufe hinweg auf. Im folgenden Codebeispiel wird dieses Szenario beschrieben:

// myCode.js
var numRows = 0;

function dontDoThis(r, emit) {
  emit({rowCount: ++numRows});
}

// The query.
SELECT max(rowCount) FROM dontDoThis(t);

Das obige Beispiel verhält sich nicht wie erwartet, weil BigQuery Ihre Abfrage über viele Knoten hinweg fragmentiert. Jeder Knoten hat eine eigenständige JavaScript-Verarbeitungsumgebung, die separate Werte für numRows sammelt.

Speicher effizient nutzen

Die JavaScript-Verarbeitungsumgebung verfügt nur über wenig Speicher pro Abfrage. UDF-Abfragen, die zu viel lokalen Status ansammeln, können wegen Speicherausschöpfung fehlschlagen.

Select-Abfragen aufschlüsseln

Sie müssen die Spalten, die von einer UDF ausgewählt werden, explizit auflisten. SELECT * FROM <UDF name>(...) wird nicht unterstützt

Sie können JSON.stringify() zum Ausgeben einer Stringausgabespalte verwenden, um die Struktur der Eingabezeilendaten zu prüfen:

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

Nach oben

Limits

• Die Datenmenge, die die UDF bei der Verarbeitung einer einzelnen Zeile ausgibt, muss ca. 5 MB oder weniger betragen.
• Jeder Nutzer kann ca. 6 UDF-Abfragen in einem bestimmten Projekt gleichzeitig ausführen. Wenn Sie die Fehlermeldung erhalten, dass Sie das Limit für gleichzeitige Abfragen überschritten haben, warten Sie ein paar Minuten und versuchen es dann erneut.
• Bei einer UDF kann es zu einer Zeitüberschreitung kommen, die ein Abschließen Ihrer Abfrage verhindert. Zeitüberschreitungen können bereits nach nur 5 Minuten auftreten, hängen jedoch von mehreren Faktoren ab, einschließlich davon, wie viel Nutzer-CPU-Zeit die Funktion verbraucht und wie groß die Ein- und Ausgaben an die JS-Funktion sind.
• Einem Abfragejob können maximal 50 UDF-Ressourcen (Inline-Code-Blobs oder externe Dateien) zugewiesen werden.
• Jedes Inline-Codeblob ist auf eine maximale Größe von 32 KB begrenzt. Zur Verwendung größerer Coderessourcen speichern Sie den Code in Cloud Storage und referenzieren ihn als externe Ressource.
• Jede externe Coderessource ist auf eine maximale Größe von 1 MB begrenzt.
• Die kumulative Größe aller externen Coderessourcen ist auf maximal 5 MB begrenzt.

Nach oben

Beschränkungen

• Die DOM-Objekte Window, Document und Node sowie Funktionen, die diese Objekte erfordern, werden nicht unterstützt.
• JavaScript-Funktionen, die nativen Code benötigen, werden nicht unterstützt.
• Bitweise Vorgänge in JavaScript verarbeiten nur die wichtigsten 32 Bit.
• Aufgrund ihrer nicht-deterministischen Natur können Abfragen, die benutzerdefinierte Funktionen aufrufen, keine im Cache gespeicherten Ergebnisse verwenden.

Nach oben