W3C 확장 로그 파일 형식 쿼리 - Amazon Athena
Athena에서 W3C 확장 로그용 테이블 생성W3C 확장 로그 셀렉트 쿼리 예제날짜 필드와 시간 필드 결합

W3C 확장 로그 파일 형식 쿼리

W3C 확장 로그 파일 데이터 형식은 공백으로 구분된 필드를 가집니다. W3C 확장 로그에 나타나는 필드는 포함할 로그 필드를 선택하는 웹 서버 관리자가 결정합니다. 다음 로그 데이터 예제에는 date, time, c-ip, s-ip, cs-method, cs-uri-stem, sc-status, sc-bytes, cs-bytes, time-taken, 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

Athena에서 W3C 확장 로그용 테이블 생성

W3C 확장 로그를 쿼리하기 전에 먼저 용 테이블 스키마를 생성해야 Athena가 로그 데이터를 읽을 수 있습니다.

Athena에서 W3C 확장 로그에 대한 테이블을 만들려면

  1. https://console.aws.amazon.com/athena/에서 Athena 콘솔을 엽니다.

  2. 다음과 같은 DDL 문을 Athena 콘솔에 붙여 넣습니다. 다음 사항에 유의하세요.

    1. 예제에서 쿼리할 로그의 필드에 해당하는 열을 추가하거나 제거합니다.

    2. W3C 확장 로그 파일 형식의 열 이름에는 하이픈(-)이 있습니다. 그러나 CREATE TABLE 문 예제는 Athena 이름 지정 규칙에 따라 이를 밑줄(_)로 바꿉니다.

    3. 공백 구분 기호를 지정하려면 FIELDS TERMINATED BY ' '를 사용합니다.

    4. Amazon S3의 W3C 확장 로그를 가리키도록 LOCATION 's3://amzn-s3-demo-bucket/w3c-log-folder/'의 값을 수정합니다.

    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. Athena 콘솔에서 iis_w3c_logs 테이블을 등록하는 쿼리를 실행합니다. 이 쿼리가 완료되면 로그를 Athena에서 쿼리할 수 있습니다.

W3C 확장 로그 셀렉트 쿼리 예제

다음 쿼리 예제는 iis_w3c_logs 테이블에서 날짜, 시간, 요청 대상, 요청에 걸린 시간을 선택합니다. WHERE 절은 HTTP 메서드가 GET이고 HTTP 상태 코드가 200(성공)인 경우를 필터링합니다.

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

다음 이미지는 Athena 쿼리 편집기의 쿼리 결과를 보여줍니다.

Amazon S3에 저장된 W3C 확장 로그 파일에 대한 Athena의 쿼리 결과 예시.

날짜 필드와 시간 필드 결합

공백으로 구분된 date 및 time 필드는 로그 소스 데이터에서 분리된 항목들이지만 원할 경우 이들을 타임스탬프로 결합할 수 있습니다. SELECT 또는 CREATE TABLE AS SELECT 쿼리에서 concat() 및 date_parse() 함수를 사용하여 날짜 및 시간 열을 타임스탬프 형식으로 연결하고 변환합니다. 다음 예제에서는 CTAS 쿼리를 사용하여 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

다음 예제와 같이 테이블을 생성하면 새 타임스탬프 열을 직접 쿼리할 수 있습니다.

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

다음 이미지는 쿼리의 결과를 보여줍니다.

파생된 타임스탬프 열을 가진 테이블에 대한 W3C 확장 로그 파일 쿼리 결과.