Amazon VPC 흐름 로그용 테이블 생성 및 쿼리

다음은 VPC 흐름 로그에 대한 Amazon VPC 테이블을 생성하는 절차입니다. 사용자 지정 형식을 사용하여 흐름 로그를 생성하는 경우 흐름 로그를 생성할 때 지정한 필드와 일치하는 필드가 해당 필드를 지정할 때와 동일한 순서로 포함되는 테이블을 생성합니다.

Amazon VPC 흐름 로그에 대한 Athena 테이블 생성

1. 고려 사항 및 제한 섹션의 다음 지침에 따라 Athena 콘솔 쿼리 편집기에 다음과 같은 DDL 문을 입력합니다. 다음 문 샘플은 흐름 로그 레코드에 설명된 대로 Amazon VPC 흐름 로그 버전 2 ~ 5의 열을 갖는 테이블을 생성합니다. 다른 열 집합이나 열 순서를 사용하는 경우 그에 맞게 문을 수정합니다.

   CREATE EXTERNAL TABLE IF NOT EXISTS `vpc_flow_logs` (
     version int,
     account_id string,
     interface_id string,
     srcaddr string,
     dstaddr string,
     srcport int,
     dstport int,
     protocol bigint,
     packets bigint,
     bytes bigint,
     start bigint,
     `end` bigint,
     action string,
     log_status string,
     vpc_id string,
     subnet_id string,
     instance_id string,
     tcp_flags int,
     type string,
     pkt_srcaddr string,
     pkt_dstaddr string,
     region string,
     az_id string,
     sublocation_type string,
     sublocation_id string,
     pkt_src_aws_service string,
     pkt_dst_aws_service string,
     flow_direction string,
     traffic_path int
   )
   PARTITIONED BY (`date` date)
   ROW FORMAT DELIMITED
   FIELDS TERMINATED BY ' '
   LOCATION 's3://amzn-s3-demo-bucket/prefix/AWSLogs/{account_id}/vpcflowlogs/{region_code}/'
   TBLPROPERTIES ("skip.header.line.count"="1");
   

   다음 사항에 주의하세요.

   • 이 쿼리는 ROW FORMAT DELIMITED를 지정하며 SerDe 지정은 생략합니다. 즉, 쿼리는 CSV, TSV, 사용자 지정 구분 기호로 구분된 파일에 대한 Lazy Simple SerDe을 사용합니다. 이 쿼리에서 필드는 공백으로 끝납니다.

   • PARTITIONED BY 절은 date 유형을 사용합니다. 따라서 쿼리에서 수학 연산자를 사용하여 특정 날짜보다 오래되었거나 최신인 항목을 선택할 수 있습니다.

     참고

     date는 DDL 문에 예약된 키워드이므로 백틱 문자로 이스케이프됩니다. 자세한 내용은 쿼리에서 예약어 이스케이프 단원을 참조하십시오.

   • 다른 사용자 지정 형식을 사용하는 VPC 흐름 로그의 경우 흐름 로그를 생성할 때 지정한 필드와 일치하도록 필드를 수정합니다.

2. 로그 데이터가 포함된 Amazon S3 버킷을 가리키도록 LOCATION 's3://amzn-s3-demo-bucket/prefix/AWSLogs/{account_id}/vpcflowlogs/{region_code}/'을 수정합니다.

3. Athena 콘솔에서 쿼리를 실행합니다. 쿼리가 완료된 후 Athena는 vpc_flow_logs 테이블을 등록하여 쿼리를 실행할 수 있도록 데이터를 준비합니다.

4. 다음 샘플 쿼리와 같이 데이터를 읽을 수 있도록 파티션을 생성합니다. 이 쿼리는 지정한 날짜에 대한 단일 파티션을 생성합니다. 필요하다면 날짜와 위치의 자리 표시자를 교체하세요.

   참고

   이 쿼리는 사용자가 지정한 날짜에 대한 단일 파티션만 생성합니다. 프로세스를 자동화하려면 이 쿼리를 실행하고 year/month/day에 대해 이 방법으로 파티션을 생성하는 스크립트를 사용하거나 파티션 프로젝션을 지정하는 CREATE TABLE 문을 사용합니다.

   ALTER TABLE vpc_flow_logs
   ADD PARTITION (`date`='YYYY-MM-dd')
   LOCATION 's3://amzn-s3-demo-bucket/prefix/AWSLogs/{account_id}/vpcflowlogs/{region_code}/YYYY/MM/dd';
   

vpc_flow_logs 테이블에 대한 쿼리 예

Athena 콘솔의 쿼리 편집기를 사용하여 생성한 테이블에서 SQL 문을 실행합니다. 쿼리를 저장하거나 이전 쿼리를 보거나 쿼리 결과를 CSV 형식으로 다운로드할 수 있습니다. 다음 예에서는 vpc_flow_logs를 테이블의 이름으로 바꿉니다. 사용자의 요구 사항에 따라 열 값과 기타 변수를 수정합니다.

다음 예제 쿼리는 지정된 날짜에 대해 최대 100개의 흐름 로그를 나열합니다.

SELECT * 
FROM vpc_flow_logs 
WHERE date = DATE('2020-05-04') 
LIMIT 100;

다음 쿼리는 거절된 모든 TCP 연결을 나열하며 새로 생성한 날짜 파티션 열인 date를 이용해 이러한 이벤트가 발생한 요일을 추출합니다.

SELECT day_of_week(date) AS
  day,
  date,
  interface_id,
  srcaddr,
  action,
  protocol
FROM vpc_flow_logs
WHERE action = 'REJECT' AND protocol = 6
LIMIT 100;

어떤 서버가 가장 많은 수의 HTTPS 요청을 수신하는지 알아보려면 다음 쿼리를 사용합니다. 이 쿼리는 지난 주부터 HTTPS 포트 443에서 수신한 패킷 수를 계산하고, 대상 IP 주소별로 그룹화한 다음, 상위 10개를 반환합니다.

SELECT SUM(packets) AS
  packetcount,
  dstaddr
FROM vpc_flow_logs
WHERE dstport = 443 AND date > current_date - interval '7' day
GROUP BY dstaddr
ORDER BY packetcount DESC
LIMIT 10;