Partition Projection を使って AWS WAF のログを分析してみた

CREATE EXTERNAL TABLE IF NOT EXISTS waflogs_all_partitioned
(
`timestamp` bigint,
formatVersion int,
webaclId string,
terminatingRuleId string,
terminatingRuleType string,
action string,
terminatingRuleMatchDetails array < struct <
    conditionType: string,
    location: string,
    matchedData: array < string >
> >,
httpSourceName string,
httpSourceId string,
ruleGroupList array < struct <
    ruleGroupId: string,
    terminatingRule: struct < ruleId: string, action: string >,
    nonTerminatingMatchingRules: array < struct < action: string, ruleId: string > >,
    excludedRules: array < struct < exclusionType: string, ruleId: string > >
> >,
rateBasedRuleList array < struct < rateBasedRuleId: string, limitKey: string, maxRateAllowed: int > >,
nonTerminatingMatchingRules array < struct < action: string, ruleId: string > >,
httpRequest struct <
    clientIp: string,
    country: string,
    headers: array < struct < name: string, value: string > >,
    uri: string,
    args: string,
    httpVersion: string,
    httpMethod: string,
    requestId: string
>
)
PARTITIONED BY ( 
  `date` string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://aws-waf-logs-123456789012/${date}'
TBLPROPERTIES (
  'projection.enabled' = 'true',
  'projection.date.type' = 'date',
  'projection.date.range' = 'NOW-1YEARS,NOW',
  'projection.date.format' = 'yyyy/MM/dd',
  'projection.date.interval' = '1',
  'projection.date.interval.unit' = 'DAYS',
  'storage.location.template' = 's3://aws-waf-logs-123456789012/${date},
  'classification'='csv', 
  'compressionType'='gzip', 
  'delimiter'='|', 
  'typeOfData'='file');

SELECT from_unixtime(timestamp/1000, 'Asia/Tokyo') AS JST, * 
FROM waflogs, UNNEST(nonTerminatingMatchingRules) t(nonTermRule) 
WHERE nonTermRule.action = 'COUNT' AND date = '2020/09/07';