Bonnes pratiques pour les fonctions

Ce document explique comment optimiser les requêtes qui utilisent des fonctions SQL.

Optimiser la comparaison de chaînes

Bonne pratique : dans la mesure du possible, utilisez LIKE au lieu de REGEXP_CONTAINS.

Dans BigQuery, vous pouvez utiliser la fonction REGEXP_CONTAINS ou l'opérateur LIKE pour comparer les chaînes. REGEXP_CONTAINS fournit plus de fonctionnalités, mais son temps d'exécution est plus lent. L'utilisation de LIKE au lieu de REGEXP_CONTAINS est plus rapide, en particulier si vous n'avez pas besoin de toute la puissance des expressions régulières fournies par REGEXP_CONTAINS, par exemple pour la mise en correspondance d'un caractère générique.

Pensez à utiliser la fonction REGEXP_CONTAINS suivante :

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

Vous pouvez optimiser cette requête comme suit :

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

Optimiser les fonctions d'agrégation

Bonne pratique : Si votre cas d'utilisation le permet, utilisez une fonction d'agrégation approximative.

Si la fonction d'agrégation SQL que vous utilisez est dotée d'une fonction d'approximation équivalente, celle-ci accélère les performances des requêtes. Par exemple, au lieu d'utiliser COUNT(DISTINCT), utilisez APPROX_COUNT_DISTINCT. Pour en savoir plus, consultez la section Fonctions d'agrégation approximative.

Vous pouvez également utiliser des fonctions HyperLogLog++ pour effectuer des approximations (y compris des agrégations approximatives personnalisées). Pour en savoir plus, consultez la page Fonctions HyperLogLog++ dans la documentation de référence sur GoogleSQL.

Envisagez d'utiliser la fonction COUNT suivante :

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

Vous pouvez optimiser cette requête comme suit :

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

Optimiser les fonctions de quantile

Bonne pratique : dans la mesure du possible, utilisez APPROX_QUANTILE au lieu de NTILE.

L'exécution d'une requête contenant la fonction NTILE peut échouer et générer une erreur Resources exceeded s'il y a de trop nombreux éléments trier avec ORDER BY dans une seule partition, ce qui entraîne une augmentation du volume de données. La fenêtre analytique n'étant pas partitionnée, le calcul NTILE nécessite une fonction globale ORDER BY pour toutes les lignes de la table à traiter par un seul nœud de calcul/emplacement.

Utilisez plutôt APPROX_QUANTILES. Cette fonction permet d'exécuter plus efficacement la requête, car elle ne nécessite pas de fonction globale ORDER BY pour toutes les lignes de la table.

Envisagez d'utiliser la fonction NTILE suivante :

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

Vous pouvez optimiser cette requête comme suit :

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

La version optimisée fournit des résultats semblables à la requête d'origine, mais pas identiques, car APPROX_QUANTILES :

1. Fournit une agrégation approximative.
2. Place les valeurs restantes (le nombre de lignes restantes divisées par les buckets) d'une manière différente.

Optimiser les fonctions définies par l'utilisateur

Bonne pratique : Utilisez des fonctions SQL définies par l'utilisateur pour effectuer des calculs simples, car l'optimiseur de requête peut appliquer des optimisations aux définitions de fonctions SQL définies par l'utilisateur. Utilisez des UDF JavaScript pour les calculs complexes qui ne sont pas compatibles avec les UDF SQL.

L'appel d'une fonction UDF JavaScript nécessite l'instanciation d'un sous-processus. L'activation de ce processus et l'exécution de la fonction UDF ont un impact direct sur les performances des requêtes. Si possible, utilisez plutôt un fichier UDF (SQL) natif.

Fonctions persistantes définies par l'utilisateur

Il est préférable de créer des fonctions SQL et JavaScript persistantes définies par l'utilisateur dans un ensemble de données BigQuery centralisé pouvant être appelées dans les requêtes et les vues logiques, plutôt que de créer et d'appeler à chaque fois une fonction définie par l'utilisateur dans le code. La création de bibliothèques de logique métier à l'échelle de l'organisation dans des ensembles de données partagés permet d'optimiser les performances et d'utiliser moins de ressources.

L'exemple suivant montre comment une fonction temporaire définie par l'utilisateur est appelée dans une requête :

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;

Vous pouvez optimiser cette requête en remplaçant la fonction temporaire définie par l'utilisateur par une fonction persistante :

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;