Ahorra dinero y tiempo en tus consultas con Amazon Athena

Por Javier Ramírez, Senior Developer Advocate, Developer Relations

Amazon Athena permite consultar datos en Amazon S3 mediante SQL, sin tener que gestionar servidores y pagando en función de cuántos datos leemos en cada consulta. Utilizando técnicas como la compresión de ficheros, el particionado de los datos, o los formatos de fichero especialmente diseñados para analítica de datos, reduciremos significativamente la cantidad de datos que escaneamos, ahorrando así tanto en nuestra factura como en el tiempo de respuesta.

En este artículo demostraremos cómo diferentes optimizaciones en nuestros ficheros en S3 afectan al rendimiento y al coste de nuestras consultas en Athena. Estas técnicas también nos ayudarían si estamos analizando datos con Amazon Redshift Spectrum o con Amazon EMR.

Atención: si ejecutas el código que se muestra a lo largo de este artículo, incurrirás en costes de almacenamiento en S3 —aproximadamente $0.02 por cada mes que mantengas los juegos de datos generados en S3— y de consultas en Athena —aproximadamente $0.02 por ejecutar todas las consultas. Como guía, si se ejecutan todos los ejemplos de este artículo en la región us-west-2 (donde reside el juego de datos que uso en el ejemplo) y se borran los datos pasadas 24 horas, el coste total aproximado sería $0.025. Si prefieres ejecutar en cualquier otra región, hay un coste adicional aproximado de $0.012 por la transferencia de datos inicial entre regiones.

Empezando nuestro experimento

Para este ejemplo voy a utilizar el juego de datos público “Multilingual Amazon Reviews Corpus”, que contiene 1.2 millones de reseñas de usuarios en seis idiomas (200.000 por idioma) en formato JSON ocupando un total de 581.69 MB (609945741 bytes).

Los datos están disponibles en s3://amazon-reviews-ml/json/train/, así que puedo registrarlo como tabla directamente desde la consola de Athena mediante el siguiente SQL

CREATE EXTERNAL TABLE amazon_multilingual_reviews (
  review_id string,  product_id string,  reviewer_id string,
  stars integer,  review_body string,  review_title string,
  language string,  product_category string
 )
 ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
 WITH SERDEPROPERTIES ('ignore.malformed.json' = 'true')
 LOCATION 's3://amazon-reviews-ml/json/train/'

Para ver el resultado de las mejoras que introduciremos, primero ejecutaremos unas consultas contra este juego de datos para tener una referencia de los bytes escaneados por Athena en datos sin optimizar, y las iremos ejecutando de nuevo con cada cambio que hagamos para comparar resultados.

SELECT COUNT(*) FROM amazon_multilingual_reviews
SELECT AVG(stars) AS avg_stars FROM amazon_multilingual_reviews WHERE language='es'
SELECT product_category, avg(stars) AS avg_stars FROM amazon_multilingual_reviews WHERE language='es' GROUP BY product_category

Vemos que los datos escaneados son siempre 581.69 MB, es decir, el juego de datos completo (lo que se conoce como un Full Table Scan en bases de datos). Incluso si nuestras consultas tienen condiciones o aunque solo sea un simple SELECT COUNT, Athena necesita leer todo el contenido de nuestros ficheros JSON para poder darnos un resultado —excepto si consultamos filas sin valores agregados y añadiendo LIMIT.

Con un juego de datos pequeño y con consultas ocasionales, este comportamiento nos puede parecer totalmente asumible. Pero si tenemos juegos de datos muy grandes, o si lanzamos consultas de manera frecuente, tenemos varias formas de mejorar estos números. Y todas ellas se basan en el mismo principio: escanear menos datos.

Primer refinamiento. Comprime tus ficheros

La primera forma de escanear menos datos es muy obvia, pero muy eficaz. Como Athena es capaz de hacer consultas sobre ficheros comprimidos, solo necesitamos comprimir en formato gzip nuestros ficheros JSON (también funcionaría si usamos ficheros CSV o TSV). Esta compresión la podemos hacer antes de ingerir nuestros ficheros en S3, pero si ya tienes los ficheros en S3, como es nuestro caso, podemos usar Athena directamente para crear una tabla con los datos comprimidos, con la técnica denominada CREATE TABLE AS SELECT (CTAS).

CREATE TABLE amazon_ml_reviews_json_gz
WITH (format='JSON') AS
SELECT review_id, product_id, reviewer_id, stars, review_body,
         review_title, product_category, language
FROM amazon_multilingual_reviews

Cuando se crea una tabla usando CTAS, el resultado se comprime automáticamente, por lo que esta consulta, creará en S3 varios ficheros con extensión .gz en una subcarpeta del bucket que tengamos configurado para la salida de Athena. Podemos, si queremos, pasar como parte de las opciones la URI de S3 donde queremos almacenar los ficheros. Si consultamos la nueva tabla, veremos que ahora estamos escaneando muchos menos datos.

SELECT COUNT(*) FROM amazon_ml_reviews_json_gz
SELECT AVG(stars) AS avg_stars FROM amazon_ml_reviews_json_gz WHERE language='es'
SELECT product_category, avg(stars) AS avg_stars FROM amazon_ml_reviews_json_gz WHERE language='es' GROUP BY product_category

Las tres consultas dan como resultado 128.72 MB escaneados, o lo que es lo mismo 4.5 veces menos que antes.

No solo eso, sino que además las tres consultas necesitan algunos segundos menos para ejecutarse que sobre la tabla original. Llegado este punto, podríamos borrar la tabla original y quedarnos solamente con esta, ahorrando no solamente tiempo y dinero en nuestras consultas en Athena, sino pagando también 4.5 veces menos por almacenar nuestros datos en S3. Esta ratio de compresión depende de cada caso, pero sea como sea siempre va a ser un ahorro considerable con respecto a los datos originales sin comprimir.

Vamos a ver si somos capaces de escanear todavía menos datos.

Segundo refinamiento. Particiona tus datos

Nuestro juego de datos contiene reseñas en 6 idiomas, con cada idioma teniendo exactamente 200.000 filas. Podemos asumir que la mayoría de nuestras consultas irán filtradas por idioma. Sin embargo, como hemos visto anteriormente, aunque filtremos por un campo estamos siempre escaneando todos los ficheros.

Si tu juego de datos tiene columnas que normalmente se incluyen en una mayoría de consultas —algunos ejemplos típicos son año, mes, fecha, país, ciudad, departamento, idioma, ID de dispositivo, tipo…— podemos aplicar la técnica del particionado. Cuando particionamos un juego de datos, lo que hacemos es crear subcarpetas para cada uno de los valores de las particiones en el bucket donde se guardan los datos. En nuestro ejemplo, los valores posibles para los idiomas son: ‘de’, ‘es’, ‘zh’, ‘fr’, ‘ja’ y ‘en’, así que si particionamos por este campo, tendríamos seis subcarpetas, una por cada valor diferente.

De nuevo, podemos usar la funcionalidad CTAS de Athena para particionar datos que ya tengamos en S3

CREATE TABLE amazon_ml_reviews_json_gz_part
WITH (format='JSON', partitioned_by = ARRAY['language']) AS
SELECT review_id, product_id, reviewer_id, stars, review_body,
         review_title, product_category, language
FROM amazon_ml_reviews_json_gz

En la consulta vemos que estoy especificando que mis datos los quiero particionados por el campo ‘language’. La sintaxis va en formato de ARRAY porque podemos especificar varios campos de particionado en orden jerárquico. En nuestro caso podríamos haber elegido particionar primero por el campo ‘language’ y después por ‘product_category’ si un número significativo de consultas suele filtrar por esos dos campos. Podemos comprobar mirando nuestros datos en S3 que se han creado las particiones que hemos pedido, tal y como vemos en la siguiente imagen.

Ejecutemos las consultas anteriores sobre la nueva tabla

SELECT COUNT(*) FROM amazon_ml_reviews_json_gz_part
SELECT AVG(stars) AS avg_stars FROM amazon_ml_reviews_json_gz_part WHERE language='es'
SELECT product_category, avg(stars) AS avg_stars FROM amazon_ml_reviews_json_gz_part WHERE language='es' GROUP BY product_category

La primera consulta ha escaneado de nuevo todo el conjunto de datos (~128MB), que tiene sentido porque no estamos filtrando por la columna particionada. Sin embargo, las otras dos consultas han escaneado solamente 17.86MB, porque Athena ha descartado automáticamente todo lo que no estuviera en la subcarpeta language=es, ahorrando así en coste siete veces sobre la tabla comprimida y 32 veces sobre nuestro juego de datos original sin comprimir.

Quizás viendo el ahorro conseguido estemos tentados de ir a nuestra jefa para pedirle que nos suba el sueldo. No nos precipitemos. ¡Podemos escanear todavía menos datos y ahorrar todavía más!

Tercer refinamiento. Usa formatos de fichero columnares (Parquet y ORC)

A pesar de la considerable mejora que hemos visto, cuando lanzamos una consulta a una partición estamos escaneando todos los datos de esa partición, cuando la mayor parte de las veces solo estamos consultando unas pocas columnas. Los formatos de fichero como CSV o JSON son fantásticos para muchos casos de uso, pero no están diseñados para el mundo de la analítica de datos y no permiten leer columnas de manera individual. Esto hace que tengamos que escanear un registro completo, aunque solo queramos utilizar una o dos columnas.

Afortunadamente, desde hace varios años hay formatos de fichero específicos para analítica de datos, diseñados para este caso de uso. Athena soporta dos de esos formatos: Parquet y ORC. Tanto Parquet como ORC almacenan datos en formato binario, soportan compresión, y además guardan la información internamente de forma que se puede acceder a las columnas de manera individual. Los dos formatos almacenan también metadatos e índices dentro de los propios ficheros para hacer más eficiente el escaneado. Por ejemplo, los metadatos permiten omitir escanear un fichero si estamos filtrando por una columna con un valor en concreto y no hay valores con ese rango en ese fichero.

Vamos a crearnos un juego de datos particionado y en formato Parquet y a ejecutar las consultas anteriores para poder comparar los resultados:

CREATE TABLE amazon_ml_reviews_parquet_part
WITH (format='parquet', partitioned_by = ARRAY['language']) AS
SELECT review_id, product_id, reviewer_id, stars,  review_body,
review_title, product_category, language
FROM amazon_multilingual_reviews

SELECT COUNT(*) FROM amazon_ml_reviews_parquet_part
SELECT AVG(stars) AS avg_stars FROM amazon_ml_reviews_parquet_part WHERE language='es'
SELECT product_category, avg(stars) AS avg_stars FROM amazon_ml_reviews_parquet_part WHERE language='es' GROUP BY product_category

La primera consulta con el COUNT(*) ahora escanea CERO KB. Esto es así porque Parquet permite recuperar información estadística, como el número de registros, directamente de los metadatos, sin necesidad de escanear el contenido. Para este caso tenemos un ahorro del 100% sobre cualquiera de los formatos anteriores.

La segunda consulta solamente utiliza dos columnas. Una de ellas es la del particionado, por lo que tiene coste cero acceder a ella, y la segunda columna es el campo ‘star’, que es un entero. De nuevo el formato Parquet brilla en este caso, escaneando solamente 2.48KB. Comparado con los 17MB de la consulta sobre JSON comprimido y particionado, o sobre los 581MB de nuestra consulta inicial, tenemos un ahorro cercano al 100%.

La tercera consulta está accediendo además al campo ‘product_category’ para hacer un agregado, con lo que necesitamos escanear toda esa columna para la partición ‘es’. En este caso, la consulta escanea 129.73KB, que supone 150 veces menos que la opción anterior y 4586 veces menos que la consulta sobre el juego de datos original.

En cuanto al coste del almacenamiento, nuestros objetos en S3 ocupan ahora 114.2 MB, que frente a los 128 MB del JSON comprimido no parece tan significativo, pero sigue siendo un 10% de ahorro con respecto al JSON, y un 80% de ahorro en coste de almacenamiento con respecto al original.

Ahora ya podemos ir a nuestra jefa y explicarle, con una sonrisa de oreja a oreja, el ahorro que hemos conseguido.

Nota: He comentado que hay un formato llamado ORC, cuyo funcionamiento es similar a Parquet. Por lo general, ORC suele ocupar algo menos de espacio que Parquet, pero a cambio Parquet es muy eficiente a la hora de escanear campos agregados, como hemos visto en el ejemplo de COUNT y AVG. En cualquier caso, tanto Parquet como ORC son más eficientes para analítica que formatos como JSON o CSV. Mi recomendación es probar los dos formatos sobre tu juego de datos y tus consultas reales para ver cuál te da mejores resultados.

Esto me está gustando. Quiero saber más

En este artículo hemos visto técnicas que se pueden aplicar a prácticamente cualquier juego de datos. Si quieres optimizar todavía más tus consultas con Athena, hay más cosas que se pueden hacer, pero requieren más experimentación para evaluar los resultados. Por ejemplo, una técnica complementaria al particionado es la asignación de buckets, que puede ayudar tanto en coste como en rendimiento para algunos juegos de datos, dependiendo de tus patrones de consulta. Si quieres profundizar más en técnicas para optimizar tus datos y tus consultas, este artículo en el blog de AWS te da muchos consejos interesantes.

Limpiando todas las tablas que hemos creado

Si has ido siguiendo los ejemplos en tu cuenta, no te olvides de borrar todos los recursos generados. Para ello tienes que eliminar tanto los ficheros en S3 —puedes ver el bucket y carpeta para cada tabla desde la consola de Athena seleccionando la opción propiedades de tabla—, como las tablas en Athena.

Conclusión

Amazon Athena es una opción excelente para analizar datos almacenados en S3. Como en S3 se paga por cada byte almacenado, y en Athena por cada byte escaneado, si conseguimos reducir el tamaño de los ficheros y la cantidad de datos escaneados por las consultas, estaremos ahorrando dinero y, por lo general, tiempo en las consultas. En este artículo hemos visto como comprimir ficheros, particionar nuestros datos, o utilizar formatos específicos para analítica pueden tener un impacto grande en el coste de operación de nuestro sistema.