Prácticas recomendadas para las funciones

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

Optimizar la comparación de cadenas

Práctica recomendada: Si es posible, usa LIKE en lugar de REGEXP_CONTAINS.

En BigQuery, puedes usar la función REGEXP_CONTAINS o el operador LIKE para comparar cadenas. REGEXP_CONTAINS ofrece más funciones, pero también tiene un tiempo de ejecución más lento. Usar LIKE en lugar de REGEXP_CONTAINS es más rápido, sobre todo si no necesitas toda la potencia de las expresiones regulares que ofrece REGEXP_CONTAINS, como la concordancia con comodines.

Veamos 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%';

Optimizar las funciones de agregación

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

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

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

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

Optimizar funciones de cuantiles

Práctica recomendada: Si es posible, usa APPROX_QUANTILE en lugar de NTILE.

Si ejecutas una consulta que contiene la función NTILE, puede producirse un error Resources exceeded si hay demasiados elementos que ORDER BY en una sola partición, lo que provoca que aumente el volumen de datos. La ventana analítica no está particionada, por lo que el NTILE requiere que se procese un ORDER BY global para todas las filas de la tabla en un único trabajador o ranura.

Prueba a usar APPROX_QUANTILES en su lugar. Esta función permite que la consulta se ejecute de forma más eficiente porque no requiere un ORDER BY global para todas las filas de la tabla.

Veamos 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 ofrece resultados similares, pero no idénticos a la consulta original, porque APPROX_QUANTILES:

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

Optimizar las funciones definidas por el usuario

Práctica recomendada: Usa funciones definidas por el usuario de SQL para cálculos sencillos, ya que el optimizador de consultas puede aplicar optimizaciones a las definiciones de funciones definidas por el usuario de SQL. Usa funciones definidas por el usuario de JavaScript para cálculos complejos que no sean compatibles con las funciones definidas por el usuario de SQL.

Para llamar a una función definida por el usuario de JavaScript, es necesario crear una instancia de un subproceso. Poner en marcha este proceso y ejecutar la FDU afecta directamente al rendimiento de las consultas. Si es posible, usa una UDF nativa (SQL) en su lugar.

Funciones definidas por el usuario persistentes

Es mejor crear funciones SQL y JavaScript definidas por el usuario persistentes en un conjunto de datos de BigQuery centralizado que se pueda invocar en consultas y vistas lógicas, en lugar de crear y llamar a una UDF en el código cada vez. Crear bibliotecas de lógica empresarial en toda la organización dentro de conjuntos de datos compartidos ayuda a optimizar el rendimiento y a 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;

Puedes optimizar esta consulta sustituyendo la función definida por el usuario 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;