Amazon Athena RegexSerDe を利用して ALB ログを探索する(2018年8月版)

これは記事の転載です

• 転載の理由は私がこの問題でハマりググって３時間くらい時間がかかったので、出来るだけ他の人に時間をかけて欲しくないためです。
• この記事のおかげで助かりました。感謝

転載元記事

• Amazon Athena RegexSerDe を利用して ALB ログを探索する(2018年8月版)

ハマったこと

• ALBのアクセスログを出力をしてAthenaで解析しようとしたら、インポートがうまくいかない

AWSのドキュメントにもあるダメなインポートクエリ

• athena ドキュ

CREATE EXTERNAL TABLE IF NOT EXISTS alb_logs (  
type string,  
time string,  
elb string,  
client_ip string,  
client_port int,  
target_ip string,  
target_port int,  
request_processing_time double,  
target_processing_time double,  
response_processing_time double,  
elb_status_code string,  
target_status_code string,  
received_bytes bigint,  
sent_bytes bigint,  
request_verb string,  
request_url string,  
request_proto string, 
user_agent string,  
ssl_cipher string,  
ssl_protocol string,  
target_group_arn string,  
trace_id string,  
domain_name string,  
chosen_cert_arn string, 
matched_rule_priority string,  
request_creation_time string, 
actions_executed string
 )
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = '1',
'input.regex' =
'([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*)[:-]([0-9]*) ([-.0-9]*) ([-.0-9]*) ([-.0-9]*) (|[-0-9]*) (-|[-0-9]*) ([-0-9]*) ([-0-9]*) \"([^ ]*) ([^ ]*) (- |[^ ]*)\" \"([^\"]*)\" ([A-Z0-9-]+) ([A-Za-z0-9.-]*) ([^ ]*) \"([^\"]*)\" \"([^\"]*)\" \"([^\"]*)\" ([-.0-9]*) ([^ ]*) \"([^\"]*)\"' ) 
LOCATION 's3://your-alb-logs-directory/AWSLogs/elasticloadbalancing/region';

インポート結果

うまくいった転載元記事にあるイケてるインポートクエリ

CREATE EXTERNAL TABLE IF NOT EXISTS alb_logs (
type string,
request_timestamp string,
elb_name string,
client_addrport string,
client_ip string,
client_port int,
target_addrport string,
target_ip string,
target_port int,
request_processing_time decimal(8,6),
target_processing_time decimal(8,6),
response_processing_time decimal(8,6),
elb_status_code string,
target_status_code string,
received_bytes int,
sent_bytes int,
request string,
user_agent string,
ssl_cipher string,
ssl_protocol string,
target_group_arn string,
trace_id string,
domain_name string, 
chosen_cert_arn string, 
matched_rule_priority string, 
request_creation_time string, 
actions_executed string, 
redirect_url string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1',
  'input.regex'='([^ ]*) ([^ ]*) ([^ ]*) (([^ ]*):([^ ]*)|-) (([^ ]*):([^ ]*)|-) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) \"([^\\\"]*)\" \"([^\\\"]*)\" ([^ ]*) ([^ ]*) ([^ ]*) \"([^\\\"]*)[ ]*\" \"([^ ]*)\" \"([^ ]*)\" ([^ ]*) ([^ ]*) \"([^ ]*)\" \"([^\\\"]*)\".*$'
) 
LOCATION 's3://devio2018-handson/07-alblogs/2018/08/13/'
TBLPROPERTIES ('has_encrypted_data'='false');

インポート結果

-　スクショ貼れませんが、うまくいきました