Creación y consulta de una tabla para los registros de flujo de Amazon VPC mediante la proyección de particiones

Use la instrucción CREATE TABLE como la siguiente para crear una tabla, particionar la tabla y rellenar las particiones automáticamente mediante la proyección de particiones. Sustituya el nombre de la tabla test_table_vpclogs en el ejemplo con el nombre de su tabla. Edite la cláusula LOCATION para especificar el bucket de Amazon S3 que contiene los datos de registro de Amazon VPC.

La siguiente instrucción CREATE TABLE es para los registros de flujo de VPC entregados en formato de partición que no es de estilo Hive. El ejemplo permite agregar varias cuentas. Si va a centralizar los registros de flujo de VPC de varias cuentas en un bucket de Amazon S3, el ID de la cuenta debe introducirse en la ruta de Amazon S3.

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}"
)

Consultas de ejemplo para test_table_vpclogs

Las consultas de ejemplo a continuación consultan los test_table_vpclogs creados por la instrucción CREATE TABLE anterior. Reemplace test_table_vpclogs en las consultas por el nombre de su propia tabla. Modifique los valores de columna y otras variables según sus requisitos.

Para devolver las 100 primeras entradas de registro de acceso en orden cronológico durante un período especificado, ejecute una consulta como la siguiente.

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

Para ver qué servidor recibe el mayor número de paquetes HTTP durante un período especificado, ejecute una consulta como la siguiente. La consulta cuenta el número de paquetes recibidos en el puerto 443 HTTPS, los agrupa según la dirección IP de destino y devuelve las 10 principales entradas de la semana anterior.

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

Para devolver los registros creados durante un período especificado, ejecute una consulta como la siguiente.

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'

Para devolver los registros de acceso de una dirección IP de origen entre periodos especificados, ejecute una consulta como la siguiente.

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

Para enumerar las conexiones TCP rechazadas, ejecute una consulta como la siguiente.

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

Para devolver los registros de acceso del intervalo de direcciones IP que comienza por 10.117, ejecute una consulta como la siguiente.

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

Para devolver los registros de acceso de una dirección IP de destino entre un intervalo de tiempo determinado, ejecute una consulta como la siguiente.

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