Prácticas recomendadas para las funciones

En este documento, se describe cómo optimizar las consultas que usan funciones de SQL.

Optimiza la comparación de strings

Práctica recomendada: Cuando sea posible, usa LIKE en lugar de REGEXP_CONTAINS.

En BigQuery, puedes usar la función REGEXP_CONTAINS o el operador LIKE para comparar strings. REGEXP_CONTAINS proporciona más funcionalidades, pero también tiene un tiempo de ejecución más lento. Usar LIKE en lugar de REGEXP_CONTAINS es más rápido, en especial si no necesitas toda la potencia de las expresiones regulares que proporciona REGEXP_CONTAINS, por ejemplo, la coincidencia de comodines.

Considera el siguiente uso de la función REGEXP_CONTAINS:

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

Puedes optimizar esta consulta de la siguiente manera:

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

Optimiza las funciones de agregación

Práctica recomendada: Si tu caso práctico lo permite, utiliza una función de agregación aproximada.

Si la función de agregación de SQL que estás utilizando tiene una función de aproximación equivalente, la función de aproximación producirá un rendimiento de consulta más rápido. Por ejemplo, en lugar de usar COUNT(DISTINCT), usa APPROX_COUNT_DISTINCT. Para obtener más información, consulta funciones de agregación aproximadas.

También puedes usar las funciones HyperLogLog++ para realizar aproximaciones (incluidas agregaciones aproximadas personalizadas). Para obtener más información, consulta funciones HyperLogLog++ en la referencia de GoogleSQL.

Considera el siguiente uso de la función COUNT:

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

Puedes optimizar esta consulta de la siguiente manera:

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

Optimiza las funciones cuantiles

Práctica recomendada: Cuando sea posible, usa APPROX_QUANTILE en lugar de NTILE.

Ejecutar una consulta que contenga la función NTILE puede fallar con un error Resources exceeded si hay demasiados elementos con los cuales usar ORDER BY en una sola partición, lo que hace que crezca el volumen de datos. La ventana analítica no está particionada, por lo que el cálculo de NTILE requiere un ORDER BY global para que un solo trabajador/ranura procese todas las filas de la tabla.

En su lugar, intenta usar APPROX_QUANTILES. Esta función permite que la consulta se ejecute de manera más eficiente, ya que no requiere un ORDER BY global para todas las filas de la tabla.

Considera el siguiente uso de la función NTILE:

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

Puedes optimizar esta consulta de la siguiente manera:

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 versión optimizada proporciona resultados similares, pero no idénticos, a la consulta original, debido a APPROX_QUANTILES:

1. Proporciona una agregación aproximada.
2. Coloca los valores restantes (el resto del número de filas dividido por buckets) de una manera diferente.

Optimiza las UDF

Práctica recomendada: Usa UDF de SQL para cálculos simples, ya que el optimizador de consultas puede aplicar optimizaciones a las definiciones de UDF de SQL. Usa UDF de JavaScript para cálculos complejos que no son compatibles con UDF de SQL.

Llamar a una UDF de JavaScript requiere la creación de instancias de un subproceso. La aceleración de este proceso y la ejecución del UDF afectan directamente el rendimiento de las consultas. Si es posible, usa un UDF nativo (SQL) en su lugar.

UDF persistentes

Es mejor crear funciones persistentes de SQL y JavaScript definidas por el usuario en un conjunto de datos centralizado de BigQuery que se pueda invocar en diferentes consultas y en vistas lógicas, en lugar de crear y llamar a una UDF en código cada vez. Crear bibliotecas de lógica empresarial en toda la organización dentro de los conjuntos de datos compartidos ayuda a optimizar el rendimiento y usar menos recursos.

En el siguiente ejemplo, se muestra cómo se invoca una UDF temporal en una consulta:

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;

Para optimizar esta consulta, reemplaza la UDF temporal por una 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;