実用的なALBアクセスログ用のAthenaDDL

リクエストURIの部分をHTTPメソッド、ホスト名、パス、QueryStringに分割しました。
Athenaの?の認識が怪しかったので、\?ですむところをわざわざ[?]にしたりしています。

CREATE EXTERNAL TABLE IF NOT EXISTS table_name (
    type string,
    request_timestamp string,
    elb_name string,
    client_addr string,
    client_port int,
    target_addrport string,
    target_addr string,
    target_port int,
    request_processing_time double,
    target_processing_time double,
    response_processing_time double,
    elb_status_code int,
    target_status_code string,
    received_bytes int,
    sent_bytes int,
    request_method string,
    request_uri string,
    request_host string,
    request_path string,
    query_string string,
    request_proto string,
    user_agent string,
    ssl_cipher string,
    ssl_protocol string,
    target_group_arn string,
    trace_id string
)
PARTITIONED BY (ymd string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
    'serialization.format' = '1',
    'input.regex' = '^(https?|h2|wss?) ([^ ]+) ([^ ]+) ([^:]+):([0-9]+) (([^:]+):([0-9]+)|-) ([0-9.]+|-1) ([0-9.]+|-1) ([0-9.]+|-1) ([0-9]+) ([0-9]+|-) ([0-9]+) ([0-9]+) "([^ ]+) ([^:]+:\/\/([^:]+):[0-9]+(\/[^ ?]+)?[?]?([^ ]+)?) ([^ ]+)" "([^"]+)" ([^ ]+|-) ([^ ]+|-) ([^ ]+) (.+)$'
)
LOCATION 's3://[your-bucket]/[your-prefix]/AWSLogs/[your-account-id]/elasticloadbalancing/[your-region]/'
TBLPROPERTIES ('has_encrypted_data' = 'false')

追記

ymdというstringのパーティションを作成している。
よくyear, month, dayでパーティションしてるサンプルを見かけるけど、クエリ書くときパーティションキーは１つの方が楽だからこうしてる。
また、パーティションを自動拡張するLambda関数も書いた。
後で追記予定。