Slide 27
Slide 27 text
AthenaでALBログを集計
WITH params AS (
SELECT
? param_data_point_time,
? param_http_method,
? param_path,
? param_latency_p50_threshold,
? param_latency_p95_threshold
)
SELECT
count(*),
count_if(is_bad_for_availability),
count_if(NOT is_bad_for_availability AND latency > 0),
count_if(NOT is_bad_for_availability AND latency > param_latency_p50_threshold),
count_if(NOT is_bad_for_availability AND latency > param_latency_p95_threshold)
FROM (
SELECT
(elb_status_code >= 500 OR elb_status_code = 429) is_bad_for_availability,
(request_processing_time + target_processing_time + response_processing_time) latency
FROM alb_logs_table, params
WHERE day >= ?
AND request_verb = param_http_method
AND regexp_like(url_extract_path(request_url), concat(concat('^', param_path), '$')
AND date_format(
from_unixtime(CAST(floor(to_unixtime(from_iso8601_timestamp(time))) AS int) / 300 * 300 + 300),
'%Y-%m-%d %H:%i:%s'
) = param_data_point_time
) t, params