Consultar o formato de arquivo de log estendido do W3C - Amazon Athena
Criar uma tabela no Athena para logs estendidos do W3CExemplo de consulta select de log estendido do W3CCombinar os campos de data e hora

Consultar o formato de arquivo de log estendido do W3C

O formato de dados de arquivo de log estendido do W3C tem campos separados por espaços. Os campos que aparecem nos logs estendidos do W3C são determinados por um administrador do servidor Web que escolhe quais campos de log serão incluídos. Os dados de log de exemplo a seguir têm os campos date, time, c-ip, s-ip, cs-method, cs-uri-stem, sc-status, sc-bytes, cs-bytes, time-taken e cs-version.

2020-01-19 22:48:39 203.0.113.5 198.51.100.2 GET /default.html 200 540 524 157 HTTP/1.0
2020-01-19 22:49:40 203.0.113.10 198.51.100.12 GET /index.html 200 420 324 164 HTTP/1.0
2020-01-19 22:50:12 203.0.113.12 198.51.100.4 GET /image.gif 200 324 320 358 HTTP/1.0
2020-01-19 22:51:44 203.0.113.15 198.51.100.16 GET /faq.html 200 330 324 288 HTTP/1.0

Criar uma tabela no Athena para logs estendidos do W3C

Antes de consultar os logs estendidos do W3C, você deve criar um esquema de tabela para que o Athena possa ler os dados do log.

Para criar uma tabela no Athena para logs estendidos do W3C

  1. Abra o console do Athena em https://console.aws.amazon.com/athena/.

  2. Cole uma instrução DDL como a seguinte no console do Athena, observando estes pontos:

    1. Adicione ou remova as colunas no exemplo para corresponder aos campos nos logs que você deseja consultar.

    2. Os nomes de colunas no formato de arquivo de log estendido do W3C contêm hifens (-). No entanto, de acordo com as Convenções de nomenclatura do Athena, a instrução de exemplo CREATE TABLE os substitui por sublinhados (_).

    3. Para especificar o delimitador de espaço, use FIELDS TERMINATED BY ' '.

    4. Modifique os valores em LOCATION 's3://amzn-s3-demo-bucket/w3c-log-folder/' para apontar para seus logs estendidos do W3C no Amazon S3.

    CREATE EXTERNAL TABLE `iis_w3c_logs`( 
  date_col string, 
  time_col string, 
  c_ip string,
  s_ip string,
  cs_method string, 
  cs_uri_stem string, 
  sc_status string,
  sc_bytes string,
  cs_bytes string,
  time_taken string,
  cs_version string
  ) 
ROW FORMAT DELIMITED  
  FIELDS TERMINATED BY ' '  
STORED AS INPUTFORMAT  
  'org.apache.hadoop.mapred.TextInputFormat'  
OUTPUTFORMAT  
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
LOCATION   's3://amzn-s3-demo-bucket/w3c-log-folder/'

  3. Execute a consulta no console do Athena para registrar a tabela iis_w3c_logs. Quando a consulta for concluída, os logs estarão prontos para você consultar no Athena.

Exemplo de consulta select de log estendido do W3C

A consulta de exemplo a seguir seleciona data, hora, destino da solicitação e tempo gasto na solicitação da tabela iis_w3c_logs. A cláusula WHERE filtra os casos em que o método HTTP é GET e o código de status HTTP é 200 (com êxito).

SELECT date_col, time_col, cs_uri_stem, time_taken
FROM iis_w3c_logs
WHERE cs_method = 'GET' AND sc_status = '200'

A imagem a seguir mostra os resultados da consulta no editor de consultas do Athena.

Exemplo de resultados de consulta no Athena de arquivos de log estendidos do W3C armazenados no Amazon S3.

Combinar os campos de data e hora

Os campos date e time delimitados por espaços são entradas separadas nos dados de origem do log, mas você pode combiná-los em um carimbo de data/hora, se desejar. Use as funções concat() e date_parse() em uma consulta SELECT ou CREATE TABLE AS SELECT para concatenar e converter as colunas de data e hora no formato de carimbo de data/hora. O exemplo a seguir usa a consulta CTAS para criar uma nova tabela com uma coluna derived_timestamp.

CREATE TABLE iis_w3c_logs_w_timestamp AS
SELECT 
  date_parse(concat(date_col,' ', time_col),'%Y-%m-%d %H:%i:%s') as derived_timestamp, 
  c_ip, 
  s_ip, 
  cs_method, 
  cs_uri_stem, 
  sc_status, 
  sc_bytes, 
  cs_bytes, 
  time_taken, 
  cs_version
FROM iis_w3c_logs

Depois de criar a tabela, consulte a nova coluna de carimbo de data/hora diretamente, como no exemplo a seguir.

SELECT derived_timestamp, cs_uri_stem, time_taken
FROM iis_w3c_logs_w_timestamp
WHERE cs_method = 'GET' AND sc_status = '200'

A imagem a seguir mostra os resultados da consulta.

Resultados da consulta de arquivo de log estendido do W3C em uma tabela com uma coluna de carimbo de data/hora derivada.