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
:
- Proporciona una agregación aproximada.
- 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;