Query plan explanation: el nuevo informe de BigQuery para optimizar consultas

Como hemos comentado en ocasiones anteriores en doctormetrics, Google Bigquery nos permite realizar consultas muy rápidas sobre grandes conjuntos de datos.

No es raro el caso en el que el resultado de nuestra consulta tiene millones de filas y se han necesitado complejas operaciones intermedias para obtener cada una de sus columnas. A pesar de su potencia, en estos casos nuestras consultas suelen tardar más tiempo del que nos tiene acostumbrados BigQuery, e incluso podemos alcanzar los límites internos de la herramienta y obtener errores a la hora de ejecutar la query, especialmente si la consulta no está optimizada.

Y precisamente una cosa que echábamos en falta los que usamos Google BigQuery con frecuencia era tener la posibilidad de saber qué partes u operaciones de nuestras consultas se podían optimizar para poder sacar el máximo rendimiento de la herramienta. Pero esto ya es pasado, Google BigQuery acaba de sacar un nuevo report llamado query plan explanation. En este post explicaremos dónde podemos encontrarlo, qué información nos proporciona y cómo podemos utilizarlo para optimizar nuestras consultas.

Dicho esto, empecemos!

¿Qué es?

Es un nuevo informe que nos ofrece información detallada sobre el query plan:  los tiempos invertidos, las transformaciones/operaciones realizadas y el número de filas con las que ha trabajado BigQuery en cada una de las etapas que han compuesto el procesamiento de nuestra consulta. Tiene el aspecto siguiente:

BQexplanation4

 

¿Dónde podemos encontrarlo?

Cuando obtenemos los resultados de una consulta en la interficie web de BigQuery, actualmente nos aparece una nueva pestaña llamada Explanation con este informe (antes únicamente teníamos la opción de ver los Results). También se puede conocer el detalle del query plan directamente desde la API.

BQexplanation1

 

¿Qué información nos ofrece?

La información que nos proporciona este nuevo report se puede separar en tres partes:

 

BQexplanation5.2

  1. Stage timing

Cada query se divide en varias etapas y cada una de ellas consiste en una serie de pasos que se pueden agrupar en cuatro categorías: Wait, Read, Compute, Write. Cada barra de color nos da una idea del tiempo medio que han necesitado los subprocesos de cada paso y, además, del tiempo máximo.

BQexplanation2.2

Sin embargo, estas barras no representan el tiempo propiamente dicho, sino que representan el ratio entre el tiempo de ese paso y el tiempo máximo de todos los subprocesos. Por ejemplo, en la imagen anterior, como el subproceso que ha necesitado el tiempo más grande (barra más larga) pertenecía al paso de leer (Read) de la etapa 1 (Stage 1), todos los demás tiempos se han dividido respecto a este valor para obtener el ratio que se muestra. Por lo tanto, cualquier otra barra representa qué proporción de ese tiempo máximo ha necesitado el paso concreto. Así pues, una barra que llena la mitad de la barra significa que ha necesitado la mitad del tiempo que se ha necesitado para leer en la etapa 1.

Aun así, la interpretación del gráfico no cambia: una barra más larga significa que se ha invertido más tiempo en ese paso mientras que una barra más corta significa que el proceso ha sido más rápido.

  1. Rows

Al hacer una query, consultamos los datos de una tabla inicial (input) y acabamos obteniendo una tabla con resultados (output). Sin embargo, en el proceso de pasar de input a ouput, es probable que BigQuery haya tenido que crear tablas intermedias para ir haciendo los distintos cálculos que le hemos pedido. Por este motivo, la consulta se divide en varias etapas (stages). Con la query plan explanation, además de los tiempos relativos que hemos visto en el punto anterior, tenemos el número de filas de la tabla input y de la tabla output correspondiente a cada etapa.

  1. Steps metadata

Por si fuera poco todo lo que hemos comentado hasta ahora, podemos desplegar cada etapa para obtener información completa sobre los pasos que se han hecho en ella: las columnas que se han leído y su correspondiente tabla de origen, las funciones agregadas y condiciones aplicadas, la tabla donde se han almacenado los resultados de ese paso intermedio, etc.

Los posibles pasos son: READ, WRITE, COMPUTE, FILTER, SORT, AGGREGATE, LIMIT, JOIN, ANALYTIC_FUNCTION, USER_DEFINED_FUNCTION.

¿Por qué es útil?

Este informe nos puede servir para optimizar nuestras consultas si combinamos la información que nos proporciona el gráfico de tiempos relativos (stage timing) con la de steps metadata. Con el primero, podemos detectar las partes críticas de nuestra consulta (las que tengan la barra más larga) que podrían estar ralentizando su ejecución, mientras que con el segundo podemos conocer los pasos concretos que se realizan en esa parte y así saber para qué operaciones de la query necesitamos pensar alternativas más eficientes que nos permitan obtener la misma tabla de resultados final.

Únicamente hay que tener en cuenta un pequeño pero importante detalle: siempre habrá un tiempo máximo de todos los subprocesos, que es el que se utilizará para calcular los demás ratios. Por lo tanto, siempre habrá algún paso que llenará toda la barra. Sin embargo, eso no significa que ese paso no sea eficiente! Incluso en las consultas perfectamente optimizadas veremos barras más largas que otras… Es importante tenerlo en cuenta para no sacar conclusiones erróneas.

 

Esto es todo por hoy! Espero que este post os haya sido útil para aprender a interpretar este nuevo informe y también ver la importancia que puede llegar a tener a la hora de optimizar consultas, algo imprescindible y que hasta ahora no era posible en la interficie de BQ. Como siempre, si tenéis alguna duda relacionada con el post o no encontráis la forma de optimizar una consulta, podéis dejar vuestro comentario y os intentaré ayudar en la medida de lo posible. Por último, si os interesa saber más sobre cómo hacer consultas en BigQuery, os invito a que leáis este post que escribí hace un tiempo sobre el tema, explicando su estructura básica y las funciones más importantes que se suelen utilizar.

Hasta la próxima! 🙂

 

 

Autor:

Digital Analytics Consultant en Metriplica. Graduado en Matemáticas y Física, y con un máster en Estadística e Investigación Operativa.

Leave Comment

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.