使用分區投影建立和查詢 Amazon VPC 流程日誌資料表 - Amazon Athena
test_table_vpclogs 的查詢範例

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

使用分區投影建立和查詢 Amazon VPC 流程日誌資料表

使用如下 CREATE TABLE 陳述句，以建立資料表、分割資料表，並使用分割區投影自動填入分割區。將範例中的資料表名稱 test_table_vpclogs 替換為您的資料表名稱。編輯子LOCATION句以指定包含您的 Amazon VPC 日誌資料的 Amazon S3 儲存貯體。

下列CREATE TABLE陳述式適用於以非 Hive 樣式磁碟分割格式傳送的VPC流程記錄。此範例允許多帳戶彙總。如果您要將多個帳戶的VPC流程日誌集中到一個 Amazon S3 儲存貯體，則必須在 Amazon S3 路徑中輸入帳戶 ID。

CREATE EXTERNAL TABLE IF NOT EXISTS test_table_vpclogs (
  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,
  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 (accid string, region string, day string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' '
LOCATION '$LOCATION_OF_LOGS'
TBLPROPERTIES
(
"skip.header.line.count"="1",
"projection.enabled" = "true",
"projection.accid.type" = "enum",
"projection.accid.values" = "$ACCID_1,$ACCID_2",
"projection.region.type" = "enum",
"projection.region.values" = "$REGION_1,$REGION_2,$REGION_3",
"projection.day.type" = "date",
"projection.day.range" = "$START_RANGE,NOW",
"projection.day.format" = "yyyy/MM/dd",
"storage.location.template" = "s3://amzn-s3-demo-bucket/AWSLogs/${accid}/vpcflowlogs/${region}/${day}"
)

test_table_vpclogs 的查詢範例

下列查詢範例會查詢由前面 CREATE TABLE 陳述式所建立的 test_table_vpclogs。將查詢中的 test_table_vpclogs 替換為您自己的資料表名稱。根據自己的需求修改欄值和其他變數。

若要在指定時段內依時間順序傳回前 100 個存取日誌項目，請執行如下查詢。

SELECT *
FROM test_table_vpclogs
WHERE day >= '2021/02/01' AND day < '2021/02/28'
ORDER BY day ASC
LIMIT 100

若要檢視哪個伺服器在指定期間內接收前十個HTTP封包數目，請執行類似下列的查詢。查詢會計算連接HTTPS埠 443 上接收到的封包數目，依目的地 IP 位址將它們分組，並傳回前一週的前 10 個項目。

SELECT SUM(packets) AS packetcount, 
       dstaddr
FROM test_table_vpclogs
WHERE dstport = 443
  AND day >= '2021/03/01'
  AND day < '2021/03/31'
GROUP BY dstaddr
ORDER BY packetcount DESC
LIMIT 10

若要傳回在指定時段內建立的日誌，請執行如下查詢。

SELECT interface_id,
       srcaddr,
       action,
       protocol,
       to_iso8601(from_unixtime(start)) AS start_time,
       to_iso8601(from_unixtime("end")) AS end_time
FROM test_table_vpclogs
WHERE DAY >= '2021/04/01'
  AND DAY < '2021/04/30'

若要傳回指定時段之間的來源 IP 地址的存取日誌，請執行如下查詢。

SELECT *
FROM test_table_vpclogs
WHERE srcaddr = '10.117.1.22'
  AND day >= '2021/02/01'
  AND day < '2021/02/28'

若要列出拒絕的TCP連線，請執行如下所示的查詢。

SELECT day,
       interface_id,
       srcaddr,
       action,
       protocol
FROM test_table_vpclogs
WHERE action = 'REJECT' AND protocol = 6 AND day >= '2021/02/01' AND day < '2021/02/28'
LIMIT 10

若要傳回以 10.117 開頭的 IP 地址範圍的存取日誌，請執行如下查詢。

SELECT *
FROM test_table_vpclogs
WHERE split_part(srcaddr,'.', 1)='10'
  AND split_part(srcaddr,'.', 2) ='117'

若要傳回特定時間範圍之間的目的地 IP 地址的存取日誌，請執行如下查詢。

SELECT *
FROM test_table_vpclogs
WHERE dstaddr = '10.0.1.14'
  AND day >= '2021/01/01'
  AND day < '2021/01/31'