Analisando dados no S3 usando Amazon Athena

Por Neil Mukerje is a Solution Architect for Amazon Web Services
Abhishek Sinha is a Senior Product Manager on Amazon Athena

Amazon Athena é um serviço de consulta interativo que facilita a análise de dados diretamente no Amazon S3 utilizando o SQL padrão. O Athena é serverless, então não há infraestrutura para configurar ou gerenciar e você pode começar a analisar seus dados imediatamente. Você nem precisa carregar seus dados no Amazon Athena ou ter processos de ETL complexos. O Athena trabalha diretamente com dados armazenados no S3.

Athena usa Presto, uma engine SQL distribuída para executar consultas. Ele também usa Apache Hive para criar, derrubar(drop) e alterar tabelas e partições. Você pode escrever instruções DDL compatíveis com Hive e instruções SQL ANSI no editor de queries dentro do Athena. Você também pode usar joins complexos, funções de janela e tipos de dados complexos no Athena. O Amazon Athena usa uma abordagem conhecida como esquema na leitura(schema-on-read), que permite projetar seu esquema em seus dados no momento em que você executar uma consulta. Isso elimina a necessidade de qualquer carregamento de dados ou ETL.

Athena cobra pela quantidade de dados verificados por consulta. Você pode economizar em custos e obter melhor desempenho se você dividir os dados, comprimi-los ou convertê-los em formatos colunares, como o Apache Parquet. Para obter mais informações, consulte os .

Neste post, demonstramos como usar o Athena em logs de Elastic Load Balancers, gerados como arquivos de texto em um formato pré-definido. Mostramos como criar uma tabela, dividir os dados em um formato usado pelo Athena, convertê-los para Parquet e comparar o desempenho da consulta.

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

Para este exemplo, os logs brutos são armazenados no Amazon S3 no seguinte formato. Há um prefixo separado para ano, mês e data, com 2570 objetos e 1 TB de dados.

Criando tabelas

Se está familiarizado com Apache Hive, você pode se . Pode-se criar tabelas escrevendo a instrução DDL no editor de consulta, ou usando o wizard ou o driver JDBC. Copie e cole a seguinte instrução DDL no editor de consulta do Athena para criar uma tabela.

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 a expressão regular especificada na instrução CREATE TABLE. Você pode especificar qualquer expressão regular, que diz ao Athena como interpretar cada linha do texto. Você também pode usar o Athena para consultar outros formatos de dados, como o JSON. Uma expressão regular não é necessária se você estiver processando formatos CSV, TSV ou JSON. Após a instrução de acesso, a tabela e o esquema aparecem no catálogo de dados (painel esquerdo). O Athena tem um catálogo de dados interno() usado para armazenar informações sobre tabelas, bancos de dados e partições. É altamente durável e não requer gestão. Você pode interagir com o catálogo usando consultas DDL ou através do console.

Você criou uma tabela sobre os dados armazenados no Amazon S3 e agora está pronto para consultar os dados. Observe que a tabela elb_logs_raw_native aponta para o prefixo s3://athena-examples/elb/raw/. Portanto, quando você adiciona mais dados nesse prefixo, os dados de um novo mês por exemplo, a tabela cresce automaticamente. Execute uma consulta simples:

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

Agora você tem a capacidade de consultar todos os logs, sem a necessidade de configurar qualquer infraestrutura ou ETL.

Partição de dados

Os clientes geralmente armazenam seus dados em formatos ordenados por tempo e precisam consultar itens específicos dentro de um dia, mês ou ano. Sem uma partição, Athena verifica toda a tabela enquanto executa consultas. Com o particionamento, você pode restringir o Athena a partições específicas, reduzindo assim a quantidade de dados verificados, reduzindo custos e melhorando o desempenho.

Athena usa particionamento de dados da mesma forma que o Apache Hive. Você pode dividir seus dados em várias dimensões— por exemplo, mês, semana, dia, hora ou ID do cliente — ou todos eles juntos.

Para usar partições, primeiro você precisa alterar sua definição de esquema para incluir partições e, em seguida, carregar os metadados de partição no Athena. Use a mesma instrução CREATE TABLE, mas com partição 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 a cláusula PARTITIONED BY na instrução CREATE TABLE. Os dados são particionados por ano, mês e dia. Na seção Resultados, o Athena lembra você de carregar partições para uma tabela particionada.

A instrução ALTER TABLE ADD PARTITION permite carregar os metadados relacionados a uma partição. Por exemplo, para carregar os dados do bucket s3://athena-examples/elb/raw/2015/01/01/, você pode executar o seguinte:

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

Agora você pode restringir cada consulta especificando as partições na cláusula WHERE. Neste caso, Athena verifica menos dados e termina mais rápido. Aqui está um exemplo:

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

Se você tem um grande número de partições, especificá-las manualmente pode ser complicado. Você pode automatizar esse processo usando um driver JDBC. Você não precisa fazer isso se seus dados já estão no mesmo formato de partição do Hive.

Conversão de dados em formatos colunares

Athena permite que você use formatos colunares de código aberto, como  Apache Parquet  e  Apache ORC. Converter seus dados em formatos colunares não só ajuda você a melhorar o desempenho da consulta, mas também economiza custos.

Existem várias maneiras de converter dados em formato colunar. Neste post, você pode usufruir de um script PySpark, com cerca de 20 linhas de duração, rodando no AMAZON EMR para converter dados em Apache Parquet. O script também divide dados por ano, mês e dia. No momento da publicação, um cluster de 2 nós r3.x8large em US-East foi capaz de converter 1 TB de arquivos de log em 130 GB de arquivos Apache Parquet comprimidos (87% de compressão) com um custo total de US $ 5.

Aqui está o layout dos arquivos no Amazon S3 agora:

Observe o layout dos arquivos. Este formato de partição, especificado no formato key = value (chave = valor), é automaticamente reconhecido pelo Athena como uma partição. Ele permite carregar todas as partições automaticamente usando o comando msck repair table <nome_da_tabela>. Isso é semelhante à forma como o Hive também entende dados particionados. Se os dados não forem no formato de key-value especificado acima, carregue as partições manualmente como discutido anteriormente.

Crie uma tabela no conjunto de dados do Parquet. Observe que seu esquema permanece o mesmo e você está comprimindo arquivos usando 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 o catálogo reconheça todas as partições, execute msck repair table elb_logs_pq. Depois que a consulta estiver concluída, você pode listar todas as suas partições.

msck repair table elb_logs_pq
show partitions elb_logs_pq

Comparando o desempenho

Você pode comparar o desempenho da mesma consulta entre arquivos de texto e arquivos 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 aos dados compactados, particionados e colunares

Consulta em arquivos de texto bruto

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 pela quantidade de dados digitalizados por consulta. Ao converter seus dados em formato colunar, comprimindo e os particionando, você não só economiza custos como também obter melhor desempenho. A tabela a seguir compara as economias criadas pela conversão de dados em formato colunar.

(*comprimido utilizando compressão Snappy)

Resumo

O Amazon Athena permite analisar dados no S3 usando SQL padrão, sem a necessidade de gerenciar qualquer infraestrutura. Você também pode acessar o Athena através de uma ferramenta de business intelligence, usando o driver JDBC. Athena cobra pela quantidade de dados verificados por consulta. Como ficou evidente neste post, converter seus dados em formatos de código aberto não só permite economizar custos, mas também melhora o desempenho.

Você pode experimentar o Amazon Athena nas regiões EUA-Leste (N. Virginia) e EUA-Oeste 2 (Oregon). Para saber mais, consulte a página de produto do Amazon Athena  ou o Guia do Usuário do Amazon Athena

Sobre os autores

Neil Mukerje é Arquiteto de Soluções na Amazon Web Services