Optimización de consultas

Utilice las técnicas de optimización de consultas que se describen en esta sección para hacer que las consultas se ejecuten más rápido o como soluciones para las consultas que superan los límites de recursos en Athena.

Optimización de combinaciones

Existen muchas estrategias diferentes para ejecutar uniones en un motor de consultas distribuido. Dos de las más comunes son las uniones hash distribuidas y las consultas con condiciones de unión complejas.

En una combinación hash distribuida, coloque las tablas grandes a la izquierda y las pequeñas a la derecha

El tipo de unión más común utiliza una comparación de igualdad como condición de unión. Athena ejecuta este tipo de unión como una unión hash distribuida.

En una unión hash distribuida, el motor crea una tabla de consulta (tabla hash) a partir de uno de los lados de la unión. Este lado se denomina lado de compilación. Los registros del lado de compilación se distribuyen entre los nodos. Cada nodo crea una tabla de búsqueda para su subconjunto. El otro lado de la unión, denominado lado de sondeo, se transmite a través de los nodos. Los registros del lado de sondeo se distribuyen entre los nodos de la misma manera que en el lado de compilación. Esto permite que cada nodo realice la unión buscando los registros coincidentes en su propia tabla de búsqueda.

Cuando las tablas de búsqueda creadas a partir del lado de compilación de la unión no caben en la memoria, las consultas pueden fallar. Aun si el tamaño total del lado de compilación es inferior a la memoria disponible, las consultas pueden fallar si la distribución de los registros presenta un sesgo significativo. En un caso extremo, todos los registros podrían tener el mismo valor para la condición de unión y tener que caber en la memoria de un único nodo. Incluso una consulta con menos asimetría puede fallar si se envía un conjunto de valores al mismo nodo y los valores suman más que la memoria disponible. Los nodos tienen la capacidad de almacenar registros en el disco, pero esto ralentiza la ejecución de la consulta y puede que no sea suficiente para evitar que la consulta falle.

Athena intenta reordenar las uniones para usar la relación más grande como el lado de sondeo y la relación más pequeña como el lado de compilación. Sin embargo, como Athena no gestiona los datos de las tablas, tiene información limitada y, a menudo, debe suponer que la primera tabla es la más grande y la segunda es la más pequeña.

Al escribir uniones con condiciones de unión basadas en la igualdad, suponga que la tabla situada a la izquierda de la palabra clave JOIN corresponde al lado de sondeo y la tabla a la derecha corresponde al lado de compilación. Asegúrese de que la tabla correcta, la del lado de compilación, sea la más pequeña de las tablas. Si no es posible hacer que el lado de compilación de la unión sea lo suficientemente pequeño como para caber en la memoria, considere la posibilidad de ejecutar varias consultas que unan subconjuntos de la tabla de compilación.

Utilice EXPLAIN para analizar consultas con combinaciones complejas

Las consultas con condiciones de unión complejas (por ejemplo, las consultas que utilizan LIKE, > u otros operadores) suelen ser exigentes desde el punto de vista computacional. En el peor de los casos, todos los registros de un lado de la unión deben compararse con todos los registros del otro lado de la unión. Como el tiempo de ejecución aumenta con el cuadrado del número de registros, estas consultas corren el riesgo de superar el tiempo máximo de ejecución.

Para saber de antemano cómo Athena ejecutará su consulta, puede usar la instrucción EXPLAIN. Para obtener más información, consulte Uso de EXPLAIN y EXPLAIN ANALYZE en Athena y Descripción de los resultados de la instrucción EXPLAIN de Athena.

Reduzca el alcance de las funciones de la ventana o quítelas

Como las funciones de ventana son operaciones que consumen muchos recursos, pueden hacer que las consultas se ejecuten con lentitud o incluso que se produzcan errores con el mensaje La consulta agotó los recursos con este factor de escala. Las funciones de ventana guardan en la memoria todos los registros en los que operan para calcular su resultado. Cuando la ventana es muy grande, la función de ventana puede quedarse sin memoria.

Para asegurarse de que las consultas se ejecuten dentro de los límites de memoria disponibles, reduzca el tamaño de las ventanas sobre las que trabajan las funciones de ventana. Para ello, puede agregar una cláusula PARTITIONED BY o reducir el alcance de las cláusulas de partición existentes.

Uso de funciones que no sean de ventana

A veces, las consultas con funciones de ventana se pueden reescribir sin funciones de ventana. Por ejemplo, en lugar de utilizar row_number para buscar los registros de N principales, puede utilizar ORDER BY y LIMIT. En lugar de usar row_number o rank para desduplicar registros, puede usar funciones de agregado como max_by, min_by y arbitrary.

Por ejemplo, supongamos que tiene un conjunto de datos con actualizaciones de un sensor. El sensor informa periódicamente el estado de la batería e incluye algunos metadatos, como la ubicación. Si desea saber el último estado de la batería de cada sensor y su ubicación, puede usar la siguiente consulta:

SELECT sensor_id,
       arbitrary(location) AS location,
       max_by(battery_status, updated_at) AS battery_status
FROM sensor_readings
GROUP BY sensor_id

Los metadatos, como la ubicación, son los mismos para todos los registros, por lo que puede utilizar la función arbitrary a fin de seleccionar cualquier valor del grupo.

Para obtener el último estado de la batería, puede usar la función max_by. La función max_by selecciona el valor de una columna del registro en el que se encontró el valor máximo de otra columna. En este caso, devuelve el estado de la batería del registro con la hora de la última actualización del grupo. Esta consulta se ejecuta más rápido y utiliza menos memoria que una consulta equivalente con una función de ventana.

Optimización de las agregaciones

Cuando Athena realiza una agregación, distribuye los registros entre los nodos de trabajo mediante las columnas de la cláusula GROUP BY. Para que la tarea de hacer coincidir los registros con los grupos sea lo más eficiente posible, los nodos intentan mantener los registros en la memoria, pero los transfieren al disco si es necesario.

También es una buena idea evitar incluir columnas redundantes en las cláusulas GROUP BY. Dado que un menor número de columnas requiere menos memoria, resulta más eficaz una consulta que describa un grupo con menos columnas. Las columnas numéricas también utilizan menos memoria que las cadenas. Por ejemplo, al agregar un conjunto de datos que tiene un ID de categoría numérico y un nombre de categoría, utilice únicamente la columna de ID de categoría en la cláusula GROUP BY.

A veces, las consultas incluyen columnas en la cláusula GROUP BY para evitar el hecho de que una columna sea parte de la cláusula GROUP BY o una expresión agregada. Si no se sigue esta regla, puede producirse un error con el siguiente mensaje:

EXPRESSION_NOT_AGGREGATE: line 1:8: 'category' must be an aggregate expression or appear in GROUP BY clause (EXPRESSION_NOT_AGGREGATE: línea 1:8: “categoría” debe ser una expresión agregada o aparecer en la cláusula GROUP BY).

Para evitar tener que agregar columnas redundantes a la cláusula GROUP BY, puede utilizar la función arbitrary, como en el siguiente ejemplo.

SELECT country_id,
       arbitrary(country_name) AS country_name,
       COUNT(*) AS city_count
FROM world_cities
GROUP BY country_id

La función ARBITRARY devuelve un valor arbitrario del grupo. La función resulta útil cuando se sabe que todos los registros del grupo tienen el mismo valor para una columna, pero el valor no identifica al grupo.

Optimización de las N consultas principales

La cláusula ORDER BY devuelve los resultados de una consulta ordenados. Athena usa la clasificación distribuida para ejecutar la operación de clasificación en paralelo en varios nodos.

Si no necesita estrictamente ordenar el resultado, evite agregar una cláusula ORDER BY. Además, evite agregar ORDER BY a las consultas internas si no son estrictamente necesarias. En muchos casos, el planificador de consultas puede eliminar la ordenación redundante, pero esto no está garantizado. Una excepción a esta regla es si una consulta interna está realizando una operación N principal, como buscar los valores N más recientes o los N más comunes.

Cuando Athena ve ORDER BY junto con LIMIT, entiende que se está ejecutando una consulta N principal y utiliza operaciones dedicadas en consecuencia.

Inclusión de las columnas necesarias únicamente

Si no necesita estrictamente una columna, no la incluya en la consulta. Cuantos menos datos tenga que procesar una consulta, más rápido se ejecutará. Esto reduce tanto la cantidad de memoria necesaria como la cantidad de datos que deben enviarse entre los nodos. Si utiliza un formato de archivo en columnas, al reducir el número de columnas también se reduce la cantidad de datos que se leen desde Amazon S3.

Athena no tiene un límite específico para el número de columnas de un resultado, pero la forma en que se ejecutan las consultas limita el tamaño combinado de las columnas posible. El tamaño combinado de las columnas incluye los nombres y tipos.

Por ejemplo, el siguiente error se debe a una relación que supera el límite de tamaño de un descriptor de relación:

GENERIC_INTERNAL_ERROR: io.airlift.bytecode.CompilationException

Para solucionar este problema, reduzca el número de columnas de la consulta o cree subconsultas y utilice una cláusula JOIN que recupere una cantidad menor de datos. Si tiene consultas que aplican SELECT * en la consulta más externa, debe cambiar * a una lista de solo las columnas que necesita.

Optimización de las consultas mediante aproximaciones

Athena admite funciones agregadas de aproximación para contar valores distintos, los valores más frecuentes, los percentiles (incluidas las medianas aproximadas) y crear histogramas. Utilice estas funciones siempre que no necesite valores exactos.

A diferencia de las operaciones COUNT(DISTINCT col), approx_distinct utiliza mucha menos memoria y se ejecuta más rápido. Del mismo modo, si se utiliza numeric_histogram en lugar de histogram, se utilizan métodos aproximados y, por lo tanto, se utiliza menos memoria.

Optimización de LIKE

Puede usar LIKE para encontrar cadenas coincidentes, pero con cadenas largas, esto requiere un uso intensivo de cómputos. La función regexp_like es, en la mayoría de los casos, una alternativa más rápida que proporciona más flexibilidad.

A menudo, puede optimizar una búsqueda anclando la subcadena que está buscando. Por ejemplo, si busca un prefijo, es mucho mejor usar “substr%” en lugar de “%substr%”. O bien, si está usando regexp_like, “^substr”.

Uso de UNION ALL en lugar de UNION

UNION ALL y UNION son dos formas de combinar los resultados de dos consultas en un solo resultado. UNION ALL concatena los registros de la primera consulta con la segunda y UNION hace lo mismo, pero también elimina los duplicados. UNION necesita procesar todos los registros y encontrar los duplicados, lo que requiere mucha memoria y procesamiento, pero UNION ALL es una operación relativamente rápida. A menos que necesite desduplicar registros, use UNION ALL para obtener el mejor rendimiento.

Uso de UNLOAD para conjuntos de resultados grandes

Si se espera que los resultados de una consulta sean grandes (por ejemplo, decenas de miles de filas o más), utilice UNLOAD para exportar los resultados. En la mayoría de los casos, esto resulta más rápido que ejecutar una consulta normal y, además, usar UNLOAD permite tener más control sobre el resultado.

Cuando termina de ejecutarse una consulta, Athena almacena el resultado como un único archivo CSV sin comprimir en Amazon S3. Esto lleva más tiempo que UNLOAD, no solo porque el resultado no está comprimido, sino también porque la operación no se puede paralelizar. Por el contrario, UNLOAD escribe los resultados directamente desde los nodos de trabajo y aprovecha al máximo el paralelismo del clúster de computación. Además, puede configurar UNLOAD para escribir los resultados en formato comprimido y en otros formatos de archivo, como JSON y Parquet.

Para obtener más información, consulte UNLOAD.

Uso de CTAS o ETL de Glue para materializar las agregaciones de uso frecuente

La “materialización” de una consulta es una forma de acelerar el rendimiento de la consulta mediante el almacenamiento de resultados de consultas complejas precalculados (por ejemplo, agregaciones y uniones) para reutilizarlos en consultas posteriores.

Si varias de las consultas incluyen las mismas uniones y agregaciones, puede materializar la subconsulta común como una tabla nueva y, a continuación, ejecutar las consultas en esa tabla. Puede crear la nueva tabla con Creación de una tabla a partir de los resultados de una consulta (CTAS) o una herramienta ETL específica, como ETL de Glue.

Por ejemplo, supongamos que tiene un panel con widgets que muestran diferentes aspectos de un conjunto de datos de pedidos. Cada widget tiene su propia consulta, pero todas las consultas comparten las mismas uniones y filtros. Una tabla de pedidos se une a una tabla de líneas de artículos y hay un filtro que muestra solo los últimos tres meses. Si se identifican las características comunes de estas consultas, se puede crear una tabla nueva que los widgets puedan usar. Esto reduce la duplicación y mejora el rendimiento. La desventaja es que se debe mantener la nueva tabla actualizada.

Reutilización de resultados de las consultas

Es habitual que la misma consulta se ejecute varias veces en poco tiempo. Por ejemplo, esto puede ocurrir cuando varias personas abren el mismo panel de datos. Al ejecutar una consulta, puede decirle a Athena que reutilice los resultados calculados con anterioridad. Usted especifica la antigüedad máxima de los resultados que se van a reutilizar. Si la misma consulta se ejecutó anteriormente dentro de ese periodo de tiempo, Athena devuelve esos resultados en lugar de volver a ejecutar la consulta. Para obtener más información, consulte Reutilización de resultados de las consultas en Athena en la Guía del usuario de Amazon Athena y Reducir los costos y mejorar el rendimiento de las consultas con la reutilización de resultados de las consultas de Amazon Athena en el Blog de macrodatos de AWS.