Athena Snippets for Amazon S3.md

tktk

s3_access_logs.sql

CREATE EXTERNAL TABLE __DATABASE_NAME__.__TABLE_NAME__ (
	bucketowner string,
	bucket_name string,
	requestdatetime string,
	remoteip string,
	requester string,
	requestid string,
	operation string,
	key string,
	request_uri string,
	httpstatus string,
	errorcode string,
	bytessent bigint,
	objectsize bigint,
	totaltime string,
	turnaroundtime string,
	referrer string,
	useragent string,
	versionid string,
	hostid string,
	sigv string,
	ciphersuite string,
	authtype string,
	endpoint string,
	tlsversion string
)

COMMENT "TKTKTK A description of the table"

ROW FORMAT SERDE "org.apache.hadoop.hive.serde2.RegexSerDe"

WITH SERDEPROPERTIES (
	"input.regex" = '([^ ]*) ([^ ]*) \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\"|-) (-|[0-9]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\"|-) ([^ ]*)(?: ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*))?.*$'
)

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

LOCATION 's3://__BUCKET_AND_PREFIX__/'