Athena Snippets for Amazon VPC.md

More snippets

Flow Logs

tktk

flow_logs.sql

-- See: https://gist.github.com/farski/a5567a01d1e679ae8e59bef280678b42
CREATE EXTERNAL TABLE IF NOT EXISTS __DATABASE_NAME__.__TABLE_NAME__ (
	version int,
	account string,
	interfaceid string,
	sourceaddress string,
	destinationaddress string,
	sourceport int,
	destinationport int,
	protocol int,
	numpackets int,
	numbytes bigint,
	starttime int,
	endtime int,
	action string,
	logstatus string,
	vpcid string,
	subnetid string,
	instanceid string,
	tcpflags int,
	type string,
	pktsrcaddr string,
	pktdstaddr string,
	region string,
	azid string,
	sublocationtype string,
	sublocationid string,
	pktsrcawsservice string,
	pktdstawsservice string,
	flowdirection string,
	trafficpath string
)

COMMENT "TKTKTK A description of the table"

PARTITIONED BY (
	`date` string
)

ROW FORMAT SERDE "org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe" 

WITH SERDEPROPERTIES (
	"field.delim" = " ",
	"serialization.format" = " "
)

STORED AS 
	INPUTFORMAT "org.apache.hadoop.mapred.TextInputFormat"
	OUTPUTFORMAT "org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat"

LOCATION "s3://__BUCKET_AND_PREFIX__/AWSLogs/__ACCOUNT_ID__/vpcflowlogs/__REGION__/"

BLPROPERTIES (
	"skip.header.line.count" = "1"
	"projection.enabled" = "true",
	"projection.date.type" = "date",
	"projection.date.range" = "2021/01/01,NOW",
	"projection.date.format" = "yyyy/MM/dd",
	"projection.date.interval" = "1",
	"projection.date.interval.unit" = "DAYS",
	"storage.location.template" = "s3://__BUCKET_AND_PREFIX__/AWSLogs/__ACCOUNT_ID__/vpcflowlogs/__REGION__/${date}"
);