Optimiza el procesamiento de las consultas

En este documento, se proporcionan las prácticas recomendadas para optimizar el rendimiento de tus consultas.

Una vez que se completó la consulta, puedes ver el plan de consulta en la consola de Google Cloud. También puedes solicitar detalles de ejecución con las vistas INFORMATION_SCHEMA.JOBS* o el método de la API de RESTjobs.get.

El plan de consulta incluye detalles sobre las etapas y los pasos de una consulta. Estos detalles pueden ayudarte a identificar formas de mejorar el rendimiento de las consultas. Por ejemplo, si notas una etapa que escribe mucho más resultados que otras etapas, es posible que debas filtrar con anterioridad en la consulta.

Para obtener más información sobre el plan de consulta y ver ejemplos de cómo la información del plan de consulta puede ayudarte a mejorar el rendimiento de las consultas, visita Obtén estadísticas de rendimiento de las consultas. Después de abordar las estadísticas de rendimiento de las consultas, puedes optimizar aún más tu consulta mediante las siguientes tareas:

Reduce los datos procesados

Puedes reducir los datos que se deben procesar con las opciones que se describen en las siguientes secciones.

Evita SELECT *

Práctica recomendada: Controla la proyección consultando solo las columnas que necesitas.

La proyección se refiere al número de columnas que lee tu consulta. Proyectar columnas en exceso incurre en materialización (resultados de escritura) y E/S adicionales (que se desperdician).

  • Usa las opciones de vista previa de datos. Si realizas experimentos o exploración en los datos, usa una de las opciones de vista previa de datos en lugar de SELECT *.
  • Consulta columnas específicas. La aplicación de una cláusula LIMIT a una consulta SELECT * no afecta la cantidad de datos leídos. Se te cobra por leer todos los bytes en la tabla completa y la consulta cuenta para tu cuota de nivel gratuito. En su lugar, consulta solo las columnas que necesitas. Por ejemplo, usa SELECT * EXCEPT para excluir una o más columnas de los resultados.
  • Usar tablas particionadas. Si realmente necesitas consultar todas las columnas de una tabla, pero solo en un subconjunto de datos, considera estas opciones:

    • Materializar los resultados en una tabla de destino y ejecutar la consulta sobre esa tabla en lugar de la original.
    • Particionar tus tablas y consultar la partición relevante. Por ejemplo, usa WHERE _PARTITIONDATE="2017-01-01" para consultar solo la partición del 1 de enero de 2017.

  • Usa SELECT * EXCEPT. Consultar un subconjunto de datos o usar SELECT * EXCEPT puede reducir mucho la cantidad de datos que lee una consulta. Además de ahorrar costos, se mejora el rendimiento porque se reduce la cantidad de E/S de datos y la cantidad de materialización necesaria para los resultados de la consulta.

    SELECT * EXCEPT (col1, col2, col5)
FROM mydataset.newtable

Evita el exceso de tablas comodín

Práctica recomendada: Cuando consultes tablas comodín, debes usar el prefijo más detallado.

Usa comodines para consultar tablas múltiples mediante instrucciones de SQL concisas. Las tablas comodín son una unión de tablas que coinciden con la expresión de comodín. Las tablas comodín son útiles si tu conjunto de datos contiene los siguientes recursos:

  • Múltiples tablas con nombres similares y esquemas compatibles
  • Tablas fragmentadas

Cuando consultes una tabla comodín, especifica un comodín (*) después del prefijo de tabla común. Por ejemplo, FROM bigquery-public-data.noaa_gsod.gsod194* consulta todas las tablas de la década de 1940.

Los prefijos más detallados funcionan mejor que los más cortos. Por ejemplo, FROM bigquery-public-data.noaa_gsod.gsod194* funciona mejor que FROM bigquery-public-data.noaa_gsod.* porque hay menos tablas que coinciden con el comodín.

Evita las tablas fragmentadas por fecha

Práctica recomendada: No uses tablas fragmentadas por fecha (también llamadas tablas denominadas por fecha) en lugar de tablas particionadas por tiempo.

Las tablas particionadas tienen un mejor rendimiento que las tablas llamadas por fecha. Cuando creas tablas fragmentadas por fecha, BigQuery debe mantener una copia del esquema y los metadatos para cada tabla llamada por fecha. Además, cuando las tablas llamadas por fecha no se usan, BigQuery puede necesitar verificar los permisos para cada tabla consultada. Esta práctica también aumenta la sobrecarga de la consulta y tiene un impacto en su rendimiento.

Evita fragmentar demasiado las tablas

Recomendación: Evita crear demasiados fragmentos de tabla. Si fragmentas tablas por fecha, usa en su lugar tablas de partición por tiempo.

La fragmentación de tablas hace referencia a la división de grandes conjuntos de datos en tablas diferentes y al agregado de un sufijo en el nombre de cada una. Si fragmentas tablas por fecha, usa en su lugar tablas de partición por tiempo.

Ya que el almacenamiento de BigQuery tiene un costo bajo, no necesitas optimizar tus tablas por costo como lo harías en un sistema de base de datos relacional. La creación de una gran cantidad de fragmentos de tabla tiene tal impacto en el rendimiento que supera a los beneficios de costo.

Las tablas fragmentadas requieren que BigQuery mantenga el esquema, los metadatos y los permisos para cada fragmento. Debido a la sobrecarga adicional necesaria para mantener la información en cada fragmento, fragmentar por demás las tablas puede tener un impacto en el rendimiento de las consultas.

La cantidad y la fuente de datos que lee una consulta pueden afectar el rendimiento y el costo de la consulta.

Reduce las consultas de partición

Práctica recomendada: Cuando consultas una tabla particionada, debes usar las siguientes columnas para filtrar con particiones en las tablas particionadas:

  • Para las tablas particionadas por tiempo de transferencia, usa la seudocolumna _PARTITIONTIME.
  • Para las tablas particionadas, como el rango de número entero y basado en la columna de unidad de tiempo, usa la columna de partición.

Para las tablas particionadas por unidad de tiempo, filtrar los datos con _PARTITIONTIME o la columna de partición te permite especificar una fecha o un rango de fechas. Por ejemplo, con la siguiente cláusula WHERE se usa la seudocolumna _PARTITIONTIME para especificar particiones entre el 1 de enero de 2016 y el 31 de enero de 2016:

WHERE _PARTITIONTIME
BETWEEN TIMESTAMP("20160101")
AND TIMESTAMP("20160131")

La consulta procesa datos solo en las particiones indicadas en el período. Filtrar tus particiones mejora el rendimiento de las consultas y reduce los costos.

Reduce los datos antes de usar una JOIN

Práctica recomendada: Reduce las cantidades de datos que se procesan antes de una cláusula JOIN realizando agregaciones.

El uso de una cláusula GROUP BY con funciones agregadas es intensivo en términos de procesamiento, ya que estos tipos de consultas usan Shuffle. Como estas consultas son intensivas en términos de procesamiento, debes usar una cláusula GROUP BY solo cuando sea necesario.

Para las consultas con GROUP BY y JOIN, realiza la agregación antes en la consulta para reducir la cantidad de datos procesados. Por ejemplo, con la siguiente consulta, se realiza un JOIN en dos tablas grandes sin ningún filtro con anticipación:

WITH
  users_posts AS (
  SELECT *
  FROM
    `bigquery-public-data`.stackoverflow.comments AS c
  JOIN
    `bigquery-public-data`.stackoverflow.users AS u
  ON
    c.user_id = u.id
  )
SELECT
  user_id,
  ANY_VALUE(display_name) AS display_name,
  ANY_VALUE(reputation) AS reputation,
  COUNT(text) AS comments_count
FROM users_posts
GROUP BY user_id
ORDER BY comments_count DESC
LIMIT 20;

Esta consulta agrega previamente las cantidades de comentarios, lo que reduce la cantidad de datos leídos para JOIN:

WITH
  comments AS (
  SELECT
    user_id,
    COUNT(text) AS comments_count
  FROM
    `bigquery-public-data`.stackoverflow.comments
  WHERE
    user_id IS NOT NULL
  GROUP BY user_id
  ORDER BY comments_count DESC
  LIMIT 20
  )
SELECT
  user_id,
  display_name,
  reputation,
  comments_count
FROM comments
JOIN
  `bigquery-public-data`.stackoverflow.users AS u
ON
  user_id = u.id
ORDER BY comments_count DESC;

Usa la cláusula WHERE

Práctica recomendada: Usa una cláusula WHERE para limitar la cantidad de datos que muestra una consulta. Cuando sea posible, usa las columnas BOOL, INT, FLOAT o DATE en la cláusula WHERE.

Las operaciones en las columnas BOOL, INT, FLOAT y DATE suelen ser más rápidas que las operaciones en las columnas STRING o BYTE. Cuando sea posible, usa una columna que use uno de estos tipos de datos en la cláusula WHERE para reducir la cantidad de datos que muestra la consulta.

Optimiza las operaciones de consulta

Puedes optimizar tus operaciones de consulta con las opciones que se describen en las siguientes secciones.

Evita transformar repetidamente los datos

Práctica recomendada: Si estás utilizando SQL para realizar operaciones de ETL, evita situaciones en las que transformes repetidamente los mismos datos.

Por ejemplo, si estás utilizando SQL para acortar strings o extraer datos mediante expresiones regulares, es más eficaz materializar los resultados transformados en una tabla de destino. Las funciones como expresiones regulares requieren un cómputo adicional. Es mucho más eficaz consultar la tabla de destino sin la sobrecarga de transformación agregada.

Evita varias evaluaciones de las mismas CTEs

Práctica recomendada: Usa lenguaje de procedimiento, variables, tablas temporales y tablas que vencen de forma automática para conservar los cálculos y usarlas más adelante en la consulta.

Cuando tu consulta contiene expresiones de tabla comunes (CTEs) que se usan en varios lugares de la consulta, es posible que se evalúen cada vez que se hace referencia a ellas. El optimizador de consultas intenta detectar partes de la consulta que podrían ejecutarse solo una vez, pero esto no siempre es posible. Como resultado, usar una CTE puede no ayudar a reducir la complejidad de las consultas internas y el consumo de recursos.

Puedes almacenar el resultado de una CTE en una variable escalar o en una tabla temporal según los datos que muestre la CTE.

Evita las uniones y subconsultas repetidas

Recomendación: Evita unir repetidamente las mismas tablas y usar las mismas subconsultas.

En lugar de unir repetidamente los datos, podría ser más eficaz usar los datos repetidos anidados para representar las relaciones. Los datos repetidos anidados te ahorran el impacto en el rendimiento del ancho de banda de comunicación que requiere una unión. También te ahorra los costos de E/S en los que incurres si lees y escribes repetidamente los mismos datos. Para obtener más información, consulta Usa campos anidados y repetidos.

De manera similar, la repetición de las mismas subconsultas afecta el rendimiento a través del procesamiento repetitivo de consultas. Si usas las mismas subconsultas en varias consultas, considera materializar los resultados de la subconsulta en una tabla. Luego consume los datos materializados en tus consultas.

La materialización de los resultados de tu subconsulta mejora el rendimiento y reduce la cantidad total de datos que BigQuery lee y escribe. El pequeño costo de almacenar los datos materializados supera el impacto en el rendimiento del proceso repetido de E/S y de consultas.

Optimiza tus patrones de unión

Práctica recomendada: Para consultas que unen datos de varias tablas, optimiza tus patrones de unión comenzando con la tabla más grande.

Cuando creas una consulta con una cláusula JOIN, ten en cuenta el orden en el cual estás fusionando los datos. El optimizador de consultas Google SQL determina qué tabla debe estar en qué lado de la unión. La práctica recomendada es colocar primero la tabla con la mayor cantidad de filas, seguida de la que tiene menos filas y, luego, en tamaño decreciente.

Cuando tienes una tabla grande como lado izquierdo de JOIN y una pequeña al lado derecho de JOIN, se crea una unión de transmisión. Una unión de transmisión envía todos los datos de la tabla más pequeña a cada ranura que procesa la tabla más grande. Se aconseja primero realizar la transmisión.

Para ver el tamaño de las tablas en JOIN, consulta Obtén información sobre las tablas.

Optimiza la cláusula ORDER BY

Práctica recomendada: Cuando uses la cláusula ORDER BY, asegúrate de seguir las prácticas recomendadas:

  • Usa ORDER BY en la consulta más externa o dentro de las cláusulas window. Envía las operaciones complejas al final de la consulta. Si colocas una cláusula ORDER BY en medio de una consulta, tendrá un gran impacto en el rendimiento, a menos que se use en una función analítica.

    Otra técnica para ordenar tu consulta es enviar las operaciones complejas, como expresiones regulares y funciones matemáticas, al final de la consulta. Esta técnica reduce los datos que se procesarán antes de que se realicen las operaciones complejas.

  • Usa una cláusula LIMIT. Si ordenas una gran cantidad de valores, pero no necesitas que se muestren todos, usa una cláusula LIMIT. Por ejemplo, con la siguiente consulta, se ordena un conjunto de resultados muy grande y se genera un error Resources exceeded. Con la consulta, se realiza el ordenamiento según la columna title en mytable. La columna title contiene millones de valores.

    SELECT
title
FROM
`my-project.mydataset.mytable`
ORDER BY
title;

    Para quitar el error, usa una consulta como la siguiente:

    SELECT
title
FROM
`my-project.mydataset.mytable`
ORDER BY
title DESC
LIMIT
1000;

  • Usa una función analítica. Si ordenas una gran cantidad de valores, usa una función analítica y limita los datos antes de llamar a la función analítica. Por ejemplo, en la siguiente consulta, se enumeran los diez usuarios de Stack Overflow más antiguos y su clasificación, y la cuenta más antigua que se posiciona en el último lugar:

    SELECT
id,
reputation,
creation_date,
DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
FROM bigquery-public-data.stackoverflow.users
ORDER BY user_rank ASC
LIMIT 10;

    Esta consulta tarda unos 15 segundos en ejecutarse. Utiliza LIMIT al final de la consulta, pero no en la función analítica DENSE_RANK() OVER. Debido a esto, la consulta requiere que todos los datos se ordenen en un solo nodo trabajador.

    Debes limitar el conjunto de datos antes de calcular la función analítica para mejorar el rendimiento:

    WITH users AS (
SELECT
id,
reputation,
creation_date,
FROM bigquery-public-data.stackoverflow.users
ORDER BY creation_date ASC
LIMIT 10)
SELECT
id,
reputation,
creation_date,
DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
FROM users
ORDER BY user_rank;

    Esta consulta tarda unos 2 segundos en ejecutarse, y muestra los mismos resultados que la consulta anterior.

    Una advertencia es que la función DENSE_RANK() clasifica los datos en años, por lo que para los datos de clasificación que abarcan varios años, estas consultas no brindan resultados idénticos.

Divide las consultas complejas en más pequeñas

Práctica recomendada: Usa las funcionalidades de consultas de varias declaraciones y los procedimientos almacenados para realizar los cálculos que se diseñaron como una consulta compleja en varias consultas más pequeñas y simples.

La ejecución de las consultas complejas, las funciones REGEX y las subconsultas o uniones en capas puede ser lenta y requerir requiere muchos recursos. Intentar adaptar todos los procesamientos en una declaración SELECT enorme, por ejemplo, para convertirla en una vista, a veces es un antipatrón, y puede dar como resultado una consulta lenta y de uso intensivo de recursos. En casos extremos, el plan de consultas interno se vuelve tan complejo que BigQuery no puede ejecutarlo.

La división de una consulta compleja permite la materialización de resultados intermedios en variables o tablas temporales. Puedes usar estos resultados intermedios en otras partes de la consulta. Es cada vez más útil cuando se necesitan estos resultados en más de un lugar de la consulta.

A menudo, te permite expresar mejor el intent verdadero de las partes de la consulta con tablas temporales que son los puntos de materialización de datos.

Usa campos anidados y repetidos

Para obtener información sobre cómo desnormalizar el almacenamiento de datos con campos anidados y repetidos, consulta Usa campos anidados y repetidos.

Usa tipos de datos INT64 en uniones

Práctica recomendada: Usa tipos de datos INT64 en uniones, en lugar de tipos de datos STRING, para reducir los costos y mejorar el rendimiento de la comparación.

BigQuery no indexa claves primarias como las bases de datos tradicionales, por lo que más amplia será la columna de unión, más larga será la comparación. Por lo tanto, usar tipos de datos INT64 en uniones es más económico y eficiente que usar tipos de datos STRING.

Reduce los resultados de las consultas

Puedes reducir los resultados de las consultas con las opciones que se describen en las siguientes secciones.

Materializa conjuntos de resultados grandes

Práctica recomendada: Considera la materialización de grandes conjuntos de resultados en una tabla de destino. Escribir grandes conjuntos de resultados tiene un impacto en el rendimiento y en los costos.

BigQuery limita los resultados almacenados en caché a aproximadamente 10 GB comprimidos. Las consultas que muestran resultados más grandes superan este límite y suelen generar el siguiente error: Response too large.

Este error ocurre a menudo cuando seleccionas una gran cantidad de campos de una tabla con una cantidad considerable de datos. Los problemas cuando se escriben resultados en caché también pueden ocurrir en consultas de estilo ETL que normalizan los datos sin reducción ni agregación.

Puedes superar la limitación en el tamaño del resultado almacenado en caché con las siguientes opciones:

  • Usar filtros para limitar el conjunto de resultados.
  • Usar una cláusula LIMIT para reducir el conjunto de resultados, en especial si usas una cláusula ORDER BY.
  • Escribir los datos de salida en una tabla de destino.

Puedes desplazarte por los resultados mediante la API de REST de BigQuery. Para obtener más información, consulta Desplázate por los datos de tablas.

Usa BI Engine

Para acelerar aún más tus consultas en SQL mediante el almacenamiento en caché de los datos que usas con mayor frecuencia, considera agregar una reserva de BI Engine al proyecto en el que se calculan las consultas. BigQuery BI Engine usa un motor de consulta vectorizado para acelerar el rendimiento de las consultas SELECT.

Evita patrones antiSQL

Las siguientes recomendaciones proporcionan una guía para evitar los antipatrones de consulta que afectan el rendimiento en BigQuery.

Evita las uniones de tabla con sí misma

Práctica recomendada: En lugar de usar uniones de tabla con sí misma, usa una función (analítica) de ventana o el operador PIVOT

Por lo general, las uniones de tablas con sí mismas se usan para procesar relaciones que dependen de las filas. El uso de una unión de tabla con sí misma podría elevar al cuadrado la cantidad de filas de salida. Este aumento en los datos de salida puede provocar un rendimiento bajo.

Evita las uniones cruzadas

Práctica recomendada: evita las uniones que generan más salidas que entradas. Cuando se requiere una CROSS JOIN, agrega previamente tus datos.

Las uniones cruzadas son consultas en las que cada fila de la primera tabla se une a cada fila de la segunda tabla, con claves que no son únicas en ambos lados. En el peor caso, el resultado es el número de filas en la tabla de la izquierda multiplicado por el número de filas en la tabla de la derecha. En casos extremos, la consulta podría no finalizarse.

Si el trabajo de consulta se completa, la explicación del plan de consulta muestra las filas de salida frente a las filas de entrada. Puedes confirmar un producto cartesiano si modificas la consulta para que copie la cantidad de filas a cada lado de la cláusula JOIN y las agrupe por la clave de unión.

Para evitar problemas de rendimiento asociados con uniones que generan más salidas que entradas:

  • Usa una cláusula GROUP BY para agregar previamente los datos.
  • Usa una función analítica. A menudo, las funciones analíticas son más eficientes que las uniones cruzadas. Para obtener más información, consulta las funciones analíticas.

Evita declaraciones DML que actualizan o insertan filas individuales

Práctica recomendada: Evita las declaraciones DML que actualizan o insertan filas individuales. Carga por lotes tus inserciones y actualizaciones.

El uso de declaraciones DML para un punto específico es un intento por tratar a BigQuery como un sistema de procesamiento de transacciones en línea (OLTP). BigQuery se centra en el procesamiento analítico en línea (OLAP) mediante el uso de análisis de tablas y no de búsquedas de puntos. Si necesitas obtener un comportamiento similar al del OLTP (actualizaciones o inserciones de filas individuales), considera contar con una base de datos que esté diseñada para admitir casos prácticos de OLTP como Cloud SQL.

Las declaraciones DML de BigQuery están diseñadas para realizar actualizaciones en forma masiva. Las declaraciones DML UPDATE y DELETE en BigQuery están destinadas a la ejecución de reemplazos periódicos de tus datos, no a las mutaciones de filas individuales. La declaración DML INSERT se diseñó para usarse con moderación. Las inserciones consumen las mismas cuotas de modificación que los trabajos de carga. Si tu caso práctico implica la ejecución de inserciones frecuentes en filas individuales, considera transmitir tus datos en su lugar.

Si el procesamiento por lotes de tus declaraciones UPDATE produce muchas tuplas en las consultas que son muy largas, podrías acercarte al límite de longitud de 256 KB de la consulta. Para evitar exceder el límite de longitud de la consulta, considera si tus actualizaciones se pueden controlar en función de criterios lógicos en lugar de una serie de reemplazos directos de tuplas.

Por ejemplo, podrías cargar tu conjunto de registros de reemplazo en otra tabla y, luego, escribir la declaración DML para actualizar todos los valores de la tabla original si las columnas no actualizadas coinciden. Por ejemplo, si los datos originales se encuentran en la tabla t y las actualizaciones están habilitadas por etapas en la tabla u, la consulta se verá de la siguiente manera:

UPDATE
  dataset.t t
SET
  my_column = u.my_column
FROM
  dataset.u u
WHERE
  t.my_key = u.my_key

Usa nombres de alias para columnas con nombres similares

Práctica recomendada: Usa alias de tabla y columna cuando trabajes con columnas con nombres similares en todas las consultas, incluidas las subconsultas.

Los alias ayudan a identificar a qué columnas y tablas se hace referencia además de tu referencia inicial de la columna. El uso de alias puede ayudarte a comprender y abordar los problemas de tu consulta en SQL, incluida la búsqueda de las columnas que se usan en las subconsultas.

Especifica restricciones en el esquema de tabla

Cuando los datos de la tabla contienen restricciones, especifica las restricciones en el esquema de la tabla. El motor de consultas puede optimizar los planes de consultas con restricciones de tablas.

Especifica restricciones de clave primaria y externa

Debes especificar restricciones de clave en el esquema de tabla cuando los datos de tabla cumplen con los requisitos de integridad de los datos de restricciones de clave primaria o clave externa. El motor de consultas puede usar las restricciones clave para optimizar los planes de consulta. Puedes encontrar información detallada en la entrada de blog Une optimizaciones con claves primarias y claves externas de BigQuery.

BigQuery no verifica de forma automática la integridad de los datos, por lo que debes asegurarte de que tus datos cumplan con las restricciones especificadas en el esquema de tabla. Si no mantienes la integridad de los datos en las tablas que tienen restricciones especificadas, es posible que los resultados de la consulta no sean precisos.

¿Qué sigue?