Criar e consultar uma tabela para logs de fluxo da Amazon VPC com o uso de projeção de partições - Amazon Athena

Criar e consultar uma tabela para logs de fluxo da Amazon VPC com o uso de projeção de partições

Use uma instrução CREATE TABLE como a que se segue para criar uma tabela, particionar a tabela e preencher as partições automaticamente usando projeção de partições. Substitua o nome da tabela test_table_vpclogs no exemplo pelo nome da tabela. Edite a cláusula LOCATION para especificar o bucket do Amazon S3 que contém os dados de log da Amazon VPC.

A instrução CREATE TABLE a seguir é para logs de fluxo da VPC fornecidos em um formato de particionamento em um estilo diferente do Hive. O exemplo permite a agregação de várias contas. Se você estiver centralizando logs de fluxo de VPC de diversas contas em um bucket do Amazon S3, o ID da conta deverá ser inserido no caminho do 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}" )

Exemplos de consulta para test_table_vpclogs

Os exemplos de consulta a seguir consultam a test_table_vpclogs criada pela instrução CREATE TABLE anterior. Substitua test_table_vpclogs nas consultas pelo nome de sua própria tabela. Modifique os valores das colunas e outras variáveis de acordo com os seus requisitos.

Para retornar as primeiras 100 entradas de log de acesso em ordem cronológica para o período de tempo especificado, execute uma consulta como a que se segue.

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

Para ver qual servidor recebe os dez principais pacotes HTTP para um período de tempo especificado, execute uma consulta como a que se segue. A consulta conta o número de pacotes recebidos na porta HTTPS 443, agrupa-os por endereço IP de destino e retorna as 10 principais entradas da 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 retornar os logs que foram criados durante um período de tempo especificado, execute uma consulta como a que se segue.

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 retornar os logs de acesso de um endereço IP de origem em um determinado intervalo de tempo, execute uma consulta como a que se segue.

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

Para listas as conexões TCP rejeitadas, execute uma consulta como a que se segue.

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 retornar os logs de acesso para o intervalo de endereços IP que começa com 10.117, execute uma consulta como a que se segue.

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

Para retornar os logs de acesso para um endereço IP de destino em um determinado intervalo de tempo, execute uma consulta como a que segue.

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