Best Practices für Funktionen

In diesem Dokument wird beschrieben, wie Abfragen mithilfe von SQL-Funktionen optimiert werden.

Stringvergleich optimieren

Best Practice: Verwenden Sie nach Möglichkeit LIKE anstelle von REGEXP_CONTAINS.

In BigQuery können Sie die REGEXP_CONTAINS-Funktion oder den LIKE-Operator verwenden, um Strings zu vergleichen. REGEXP_CONTAINS bietet mehr Funktionen, ist aber in der Ausführung langsamer. Die Verwendung von LIKE anstelle von REGEXP_CONTAINS bietet Tempovorteile. Dies gilt besonders, wenn Sie nicht die volle Leistung regulärer Ausdrücke benötigen, die REGEXP_CONTAINS bietet, z. B. Platzhalterabgleich.

Betrachten Sie folgende Verwendung der REGEXP_CONTAINS-Funktion:

SELECT
  dim1
FROM
  `dataset.table1`
WHERE
  REGEXP_CONTAINS(dim1, '.*test.*');

Sie können diese Abfrage so optimieren:

SELECT
  dim1
FROM
  `dataset.table`
WHERE
  dim1 LIKE '%test%';

Aggregationsfunktionen optimieren

Best Practice: Nutzen Sie eine ungefähre Aggregatfunktion, wenn Ihr Anwendungsfall dies unterstützt.

Wenn es zu der von Ihnen verwendeten SQL-Aggregatfunktion eine entsprechende Näherungsfunktion gibt, erhöhen Sie damit die Abfrageleistung. Nutzen Sie beispielsweise statt COUNT(DISTINCT) APPROX_COUNT_DISTINCT. Weitere Informationen finden Sie unter ungefähre Aggregatfunktionen.

Sie können auch HyperLogLog++-Funktionen verwenden, um Näherungen (einschließlich benutzerdefinierter ungefährer Aggregationen) durchzuführen. Weitere Informationen finden Sie unter HyperLogLog++-Funktionen in der GoogleSQL-Referenz.

Betrachten Sie folgende Verwendung der COUNT-Funktion:

SELECT
  dim1,
  COUNT(DISTINCT dim2)
FROM
  `dataset.table`
GROUP BY 1;

Sie können diese Abfrage so optimieren:

SELECT
  dim1,
  APPROX_COUNT_DISTINCT(dim2)
FROM
  `dataset.table`
GROUP BY 1;

Quantilfunktionen optimieren

Best Practice: Verwenden Sie nach Möglichkeit APPROX_QUANTILE anstelle von NTILE.

Das Ausführen einer Abfrage, die die NTILE-Funktion enthält, kann mit dem Resources exceeded-Fehler fehlschlagen, wenn es zu viele Elemente in einer einzelnen Partition ORDER BY werden müssen, wodurch das Datenvolumen wächst. Das Analysefenster ist nicht partitioniert. Deshalb benötigt die NTILE-Berechnung einen globalen ORDER BY, um alle Zeilen in der Tabelle von einem einzelnen Worker/Slot zu verarbeiten.

Verwenden Sie stattdessen APPROX_QUANTILES. Mit dieser Funktion kann die Abfrage effizienter ausgeführt werden, da nicht für alle Zeilen in der Tabelle ein globales ORDER BY erforderlich ist.

Betrachten Sie folgende Verwendung der NTILE-Funktion:

SELECT
  individual_id,
  NTILE(nbuckets) OVER (ORDER BY sales desc) AS sales_third
FROM
  `dataset.table`;

Sie können diese Abfrage so optimieren:

WITH QuantInfo AS (
  SELECT
    o, qval
  FROM UNNEST((
     SELECT APPROX_QUANTILES(sales, nbuckets)
     FROM `dataset.table`
    )) AS qval
  WITH offset o
  WHERE o > 0
)
SELECT
  individual_id,
  (SELECT
     (nbuckets + 1) - MIN(o)
   FROM QuantInfo
   WHERE sales <= QuantInfo.qval
  ) AS sales_third
FROM `dataset.table`;

Die optimierte Version liefert ähnliche, aber nicht identische Ergebnisse wie die ursprüngliche Abfrage, weil APPROX_QUANTILES:

1. - eine ungefähre Zusammenfassung bereitstellt.
2. - die Restwerte (die verbleibenden Zeilen, geteilt durch Buckets) auf andere Weise platziert.

UDFs optimieren

Best Practice: Verwenden Sie SQL-UDFs für einfache Berechnungen, da das Abfrageoptimierungstool Optimierungen auf SQL-UDF-Definitionen anwenden kann. Verwenden Sie JavaScript-UDFs für komplexe Berechnungen, die von SQL-UDF nicht unterstützt werden.

Für den Aufruf einer benutzerdefinierten JavaScript-Funktion ist die Instanziierung eines Java-Unterprozesses erforderlich. Das Starten dieses Vorgangs und das Ausführen der benutzerdefinierten Funktion wirken sich auf die Abfrageleistung aus. Verwenden Sie daher, falls möglich, stattdessen eine native (SQL) UDF.

Nichtflüchtige UDFs

Es ist besser, nichtflüchtige benutzerdefinierte SQL- und JavaScript-Funktionen in einem zentralisierten BigQuery-Dataset zu erstellen, das über Abfragen und logische Ansichten hinweg aufgerufen werden kann, statt jedes Mal eine UDF im Code zu erstellen und aufzurufen. Durch das Erstellen organisationsweiter Geschäftslogik-Bibliotheken in freigegebenen Datasets können Sie die Leistung optimieren und weniger Ressourcen verbrauchen.

Das folgende Beispiel zeigt, wie eine temporäre UDF in einer Abfrage aufgerufen wird:

CREATE TEMP FUNCTION addFourAndDivide(x INT64, y INT64) AS ((x + 4) / y);

WITH numbers AS
  (SELECT 1 as val
  UNION ALL
  SELECT 3 as val
  UNION ALL
  SELECT 4 as val
  UNION ALL
  SELECT 5 as val)
SELECT val, addFourAndDivide(val, 2) AS result
FROM numbers;

Um diese Abfrage zu optimieren ersetzen Sie die temporäre UDF durch eine persistente:

WITH numbers AS
  (SELECT 1 as val
  UNION ALL
  SELECT 3 as val
  UNION ALL
  SELECT 4 as val
  UNION ALL
  SELECT 5 as val)
SELECT val, `your_project.your_dataset.addFourAndDivide`(val, 2) AS result
FROM numbers;