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'