Consulta del formato de archivo de registro extendido de W3C - Amazon Athena
Creación de una tabla en Athena para los registros extendidos de W3CEjemplo de consulta Select de registro extendido de W3CCombinación de los campos de fecha y hora

Consulta del formato de archivo de registro extendido de W3C

El formato de datos de archivo de registro extendido de W3C tiene campos separados por espacios. Un administrador del servidor web, que elige los campos de registro que desea incluir, determina los campos que aparecen en los registros extendidos de W3C. En el siguiente ejemplo, los datos de registro tienen los campos date, time, c-ip, s-ip, cs-method, cs-uri-stem, sc-status, sc-bytes, cs-bytes, time-taken y 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

Creación de una tabla en Athena para los registros extendidos de W3C

Antes de poder consultar los registros extendidos de W3C, debe crear un esquema de tabla de modo que Athena pueda leer los datos de registro.

Para crear una tabla en Athena para registros extendidos de W3C

  1. Abra la consola de Athena en https://console.aws.amazon.com/athena/.

  2. Pegue una instrucción DDL como la siguiente en la consola de Athena, teniendo en cuenta los puntos a continuación:

    1. Agregue o elimine las columnas del ejemplo para que se correspondan con los campos de los registros que desea consultar.

    2. Los nombres de columna en el formato de archivo de registro extendido de W3C contienen guiones (-). Sin embargo, de acuerdo con las convenciones de nomenclatura de Athena, la instrucción CREATE TABLE de ejemplo los reemplaza con guiones bajos (_).

    3. Para especificar el delimitador de espacio, utilice FIELDS TERMINATED BY ' '.

    4. Modifique los valores de LOCATION 's3://amzn-s3-demo-bucket/w3c-log-folder/' para que apunten a los registros extendidos de W3C en 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. Ejecute la consulta en la consola de Athena para registrar la tabla iis_w3c_logs. Cuando se complete la consulta, los registros estarán listos para su consulta desde Athena.

Ejemplo de consulta Select de registro extendido de W3C

En la siguiente consulta de ejemplo se selecciona la fecha, la hora, el destino de la solicitud y el tiempo que demora la solicitud de la tabla iis_w3c_logs. La cláusula WHERE filtra los casos en los que el método HTTP es GET y el código de estado HTTP es 200 (satisfactorio).

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

En la siguiente imagen se muestran los resultados de la consulta en el Editor de consultas de Athena.

Resultados de consulta de ejemplo en Athena de archivos de registro extendidos de W3C almacenados en Amazon S3.

Combinación de los campos de fecha y hora

Los campos delimitados por espacios date y time son entradas separadas en los datos de origen del registro, pero puede combinarlas en una marca temporal, si lo desea. Utilice las funciones concat() y date_parse() en una consulta SELECT o CREATE TABLE AS SELECT para concatenar y convertir las columnas de fecha y hora en formato de marca temporal. En el siguiente ejemplo se utiliza una consulta CTAS para crear una nueva tabla con una columna 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

Una vez creada la tabla, puede consultar la nueva columna de marca temporal directamente, como en el siguiente ejemplo.

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

En la siguiente imagen, se muestran los resultados de la consulta.

La consulta del archivo de registro extendido de W3C genera una tabla con una columna de marca temporal derivada.