Análisis de datos en S3 con Amazon Athena

Por Neil Mukerje, Arquitecto de Soluciones en Amazon Web Services
Abhishek Sinha, Senior Product Manager en Amazon Athena

Amazon Athena es un servicio de consultas interactivas que facilita el análisis de datos directamente en Amazon S3 mediante el estándar SQL. Athena no tiene servidor, por lo que no hay que configurar ni administrar ninguna infraestructura, y puede empezar a analizar sus datos de inmediato. Ni siquiera tiene que cargar sus datos en Amazon Athena ni tener procesos de ETL complejos. Athena trabaja directamente con los datos almacenados en S3.

Athena usa Presto, un motor SQL distribuido para ejecutar consultas. También usa Apache Hive para crear, eliminar, modificar tablas y particiones. Puede escribir instrucciones DDL compatibles con Hive y sentencias ANSI SQL en el editor de consultas de Athena. También puede usar uniones complejas, funciones de ventana y tipos de datos complejos en Athena. Amazon Athena utiliza un enfoque conocido como esquema en lectura, que le permite diseñar el esquema en los datos en el momento en que ejecuta una consulta. Esto elimina la necesidad de cargar datos o ETL.

Athena cobra por la cantidad de datos verificados por consulta. Puede ahorrar costos y obtener un mejor rendimiento si divide los datos, los comprime o los convierte a formatos de columnas, como Apache Parquet. Para obtener más información, consulte los precios de Amazon Athena.

En esta publicación, demostramos cómo usar Athena en los registros de Elastic Load Balancers, generados como archivos de texto en un formato predeterminado. Le mostramos cómo crear una tabla, dividir los datos en un formato utilizado por Athena, convertirlos a Parquet y comparar el rendimiento de las consultas.

—————————————————-

Para este ejemplo, los registros sin procesar se almacenan en Amazon S3 en el siguiente formato. Hay un prefijo diferente para el año, el mes y la fecha, con 2570 objetos y 1 TB de datos.

Creación de tablas

Si está familiarizado con Apache Hive, puede familiarizarse con la creación de tablas en Athena. Puede crear tablas escribiendo la sentencia DDL en el editor de consultas o mediante el asistente o el controlador JDBC. Copie y pegue la siguiente declaración DDL en el editor de consultas Athena para crear una tabla.

CREATE EXTERNAL TABLE IF NOT EXISTS elb_logs_raw_native (
  request_timestamp string, 
  elb_name string, 
  request_ip string, 
  request_port int, 
  backend_ip string, 
  backend_port int, 
  request_processing_time double, 
  backend_processing_time double, 
  client_response_time double, 
  elb_response_code string, 
  backend_response_code string, 
  received_bytes bigint, 
  sent_bytes bigint, 
  request_verb string, 
  url string, 
  protocol string, 
  user_agent string, 
  ssl_cipher string, 
  ssl_protocol string ) 
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
         'serialization.format' = '1','input.regex' = '([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*)[:\-]([0-9]*) ([-.0-9]*) ([-.0-9]*) ([-.0-9]*) (|[-0-9]*) (-|[-0-9]*) ([-0-9]*) ([-0-9]*) \\\"([^ ]*) ([^ ]*) (- |[^ ]*)\\\" (\"[^\"]*\") ([A-Z0-9-]+) ([A-Za-z0-9.-]*)$' ) 
LOCATION 's3://athena-examples/elb/raw/';

Observe la expresión regular especificada en la instrucción CREATE TABLE. Puede especificar cualquier expresión regular, que indique a Athena cómo interpretar cada línea de texto. También puedes usar Athena para consultar otros formatos de datos como JSON. No es necesaria una expresión regular si se procesan formatos CSV, TSV o JSON. Después de la declaración de acceso, la tabla y el esquema aparecen en el catálogo de datos (panel izquierdo). Athena tiene un catálogo de datos interno (catálogo de datos) que se utiliza para almacenar información sobre tablas, bases de datos, datos y particiones. Es muy duradero y no requiere gestión. Puede interactuar con el catálogo mediante consultas DDL o mediante la consola.

Ha creado una tabla sobre los datos almacenados en Amazon S3 y ahora está listo para consultarlos. Observe que la tabla elb_logs_raw_native apunta al prefijo s3://athena-examples/elb/raw/. Por lo tanto, cuando agrega más datos a este prefijo, por ejemplo, los datos de un mes nuevo, la tabla crece automáticamente. Ejecute una consulta sencilla:

SELECT * FROM elb_logs_raw_native WHERE elb_response_code = '200' LIMIT 100;

Ahora tiene la capacidad de consultar todos los registros, sin necesidad de configurar ninguna infraestructura o ETL.

Partición de datos

Los clientes suelen almacenar sus datos en formatos ordenados por tiempo y necesitan consultar artículos específicos dentro de un día, mes o año. Sin una partición, Athena analiza toda la tabla mientras ejecuta consultas. Con particiones, puede restringir Athena a particiones específicas, lo que reduce la cantidad de datos escaneados, reduce los costos y mejora el rendimiento.

Athena usa el estilo de partición de Apache Hive. Puede dividir los datos en varias dimensiones (por ejemplo, mes, semana, día, hora o ID de cliente) o todas juntas.

Para usar particiones, primero debe cambiar la definición del esquema para incluir particiones y, a continuación, cargar los metadatos de la partición en Athena. Utilice la misma instrucción CREATE TABLE, pero con la partición habilitada.

CREATE EXTERNAL TABLE IF NOT EXISTS elb_logs_raw_native_part (
  request_timestamp string, 
  elb_name string, 
  request_ip string, 
  request_port int, 
  backend_ip string, 
  backend_port int, 
  request_processing_time double, 
  backend_processing_time double, 
  client_response_time double, 
  elb_response_code string, 
  backend_response_code string, 
  received_bytes bigint, 
  sent_bytes bigint, 
  request_verb string, 
  url string, 
  protocol string, 
  user_agent string, 
  ssl_cipher string, 
  ssl_protocol string ) 
PARTITIONED BY(year string, month string, day string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
         'serialization.format' = '1','input.regex' = '([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*)[:\-]([0-9]*) ([-.0-9]*) ([-.0-9]*) ([-.0-9]*) (|[-0-9]*) (-|[-0-9]*) ([-0-9]*) ([-0-9]*) \\\"([^ ]*) ([^ ]*) (- |[^ ]*)\\\" (\"[^\"]*\") ([A-Z0-9-]+) ([A-Za-z0-9.-]*)$' )
LOCATION 's3://athena-examples/elb/raw/';

Observe la cláusula PARTITIONED BY en la instrucción CREATE TABLE. Los datos se dividen por año, mes y día. En la sección Resultados, Athena te recuerda que debes cargar particiones en una tabla particionada.

La instrucción ALTER TABLE ADD PARTITION le permite cargar los metadatos relacionados con una partición. Por ejemplo, para cargar los datos del bucket s3://athena-examples/elb/raw/2015/01/01/, puedes ejecutar lo siguiente:

ALTER TABLE elb_logs_raw_native_part ADD PARTITION (year='2015',month='01',day='01') location 's3://athena-examples/elb/raw/2015/01/01/'
show partitions elb_logs_raw_native_part

Ahora puede restringir cada consulta especificando las particiones en la cláusula WHERE. En este caso, Athena comprueba menos datos y termina más rápido. He aquí un ejemplo:

SELECT distinct(elb_response_code),
         count(url)
FROM elb_logs_raw_native_part
WHERE year='2015'
        AND month= '01'
        AND day='01'
GROUP BY  elb_response_code

Si tiene un gran número de particiones, especificarlas manualmente puede resultar complicado. Puede automatizar este proceso mediante un controlador JDBC. No tiene que hacerlo si sus datos ya están en el mismo formato de partición que Hive.

Conversión de datos a formatos columnares

Athena permite utilizar formatos columnares de código abierto, como Apache Parquet y Apache ORC. La conversión de los datos a formatos columnares no solo ayuda a mejorar el rendimiento de las consultas, sino que también ahorra costos.

Hay varias formas de convertir datos a formato columnar. En esta publicación, puede aprovechar un script de PySpark, con aproximadamente 20 líneas de duración, que se ejecuta en AMAZON EMR para convertir datos a Apache Parquet. El script también divide los datos por año, mes y día. En el momento de la publicación, un clúster r3.x8large de 2 nodos en US-east podía convertir 1 TB de archivos de registro en 130 GB de archivos Apache Parquet comprimidos (compresión del 87%) con un costo total de 5 dólares.

Este es el diseño de los archivos en Amazon S3 ahora:

Observe el diseño de los archivos. Athena reconoce automáticamente este formato de partición, especificado en el formato clave = valor como una partición. Le permite cargar todas las particiones automáticamente mediante el comando msck repair table <table_name>. Esto es similar a cómo Hive también entiende los datos particionados. Si los datos no están en el formato clave-valor especificado anteriormente, cargue las particiones manualmente como se ha explicado anteriormente.

Cree una tabla en el conjunto de datos Parquet. Tenga en cuenta que su esquema sigue siendo el mismo y que está comprimiendo archivos con Snappy.

CREATE EXTERNAL TABLE IF NOT EXISTS elb_logs_pq (
  request_timestamp string,
  elb_name string,
  request_ip string,
  request_port int,
  backend_ip string,
  backend_port int,
  request_processing_time double,
  backend_processing_time double,
  client_response_time double,
  elb_response_code string,
  backend_response_code string,
  received_bytes bigint,
  sent_bytes bigint,
  request_verb string,
  url string,
  protocol string,
  user_agent string,
  ssl_cipher string,
  ssl_protocol string )
PARTITIONED BY(year int, month int, day int) 
STORED AS PARQUET
LOCATION 's3://athena-examples/elb/parquet/'
tblproperties ("parquet.compress"="SNAPPY");

Para permitir que el catálogo reconozca todas las particiones, ejecute msck repair table elb_logs_pq. Una vez finalizada la consulta, puede enumerar todas las particiones.

msck repair table elb_logs_pq
show partitions elb_logs_pq

Comparar el rendimiento

Puede comparar el rendimiento de la misma consulta entre archivos de texto y archivos de Parquet.

SELECT elb_name,
       uptime,
       downtime,
       cast(downtime as DOUBLE)/cast(uptime as DOUBLE) uptime_downtime_ratio
FROM 
    (SELECT elb_name,
        sum(case elb_response_code
        WHEN '200' THEN
        1
        ELSE 0 end) AS uptime, sum(case elb_response_code
        WHEN '404' THEN
        1
        ELSE 0 end) AS downtime
    FROM elb_logs_pq
    GROUP BY  elb_name)

Consulta datos comprimidos, particionados y en columnas

Consultar archivos de texto sin formato

SELECT elb_name,
       uptime,
       downtime,
       cast(downtime as DOUBLE)/cast(uptime as DOUBLE) uptime_downtime_ratio
FROM 
    (SELECT elb_name,
        sum(case elb_response_code
        WHEN '200' THEN
        1
        ELSE 0 end) AS uptime, sum(case elb_response_code
        WHEN '404' THEN
        1
        ELSE 0 end) AS downtime
    FROM elb_logs_raw_native
    GROUP BY  elb_name)

Athena cobra por la cantidad de datos escaneados por consulta. Al convertir sus datos a formato columnar, comprimirlos y particionarlos, no solo ahorrará costos sino que también obtendrá un mejor rendimiento. En la siguiente tabla se comparan los ahorros generados al convertir los datos al formato de columnas.

(*comprimido usando Snappy)

Resumen

Amazon Athena le permite analizar datos en S3 mediante el estándar SQL, sin necesidad de administrar ninguna infraestructura. También puede acceder a Athena a través de una herramienta de inteligencia de negocios, utilizando el controlador JDBC. Athena cobra por la cantidad de datos escaneados por consulta. Como fue evidente en esta publicación, convertir sus datos a formatos de código abierto no solo le permite ahorrar costos, sino que también mejora el rendimiento.

Puede probar Amazon Athena en las regiones US-East (Virginia del Norte) y US-West 2 (Oregón). Para obtener más información, consulte la página de productos de Amazon Athena o la Guía del usuario de Amazon Athena

Este articulo fue traducido de Blog da AWS em Inglés.

Sobre los autores

Neil Mukerje es Arquitecto de Soluciones en Amazon Web Services