Amazon Athena がクエリ実行のオブザーバビリティを高めるシステムテーブルを導入

SELECT
    DATE_TRUNC('day', submission_date_time) AS day,
    COUNT(*) AS query_count
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_DATE - INTERVAL '30' DAY
GROUP BY 1
ORDER BY 1

SELECT
    DATE_TRUNC('week', submission_date_time) AS week,
    work_group_name,
    COUNT(*) AS query_count,
    COUNT(DISTINCT principal_arn) AS distinct_users
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_DATE - INTERVAL '90' DAY
GROUP BY 1, 2
ORDER BY 1, 2

SELECT
    CASE
        WHEN data_scanned_size = 0 THEN '0 bytes'
        WHEN data_scanned_size < 1048576 THEN '<1 MB'
        WHEN data_scanned_size < 10485760 THEN '1-10 MB'
        WHEN data_scanned_size < 104857600 THEN '10-100 MB'
        WHEN data_scanned_size < 1073741824 THEN '100 MB-1 GB'
        WHEN data_scanned_size < 10737418240 THEN '1-10 GB'
        WHEN data_scanned_size < 107374182400 THEN '10-100 GB'
        ELSE '>100 GB'
    END AS scan_bucket,
    COUNT(*) AS query_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS percent_of_query_count
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_DATE - INTERVAL '30' DAY
AND state_enum = 'SUCCEEDED'
GROUP BY 1
ORDER BY MIN(data_scanned_size)

SELECT
    work_group_name,
    region_name,
    ROUND(SUM(data_scanned_size) / POWER(1024.0, 4), 3) AS total_tb_scanned,
    COUNT(*) AS query_count,
    ROUND(SUM(data_scanned_size) * 100.0 / SUM(SUM(data_scanned_size)) OVER (), 3) AS percent_of_total_tb_scanned
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_DATE - INTERVAL '30' DAY
AND state_enum = 'SUCCEEDED'
GROUP BY 1, 2
ORDER BY 1, 2

SELECT
    query_execution_id,
    work_group_name,
    principal_arn,
    submission_date_time,
    total_execution_duration / 1000.0 AS execution_seconds,
    data_scanned_size / POWER(1024, 3) AS data_scanned_gb,
    SUBSTR(query_string, 1, 200) AS query_preview
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_DATE - INTERVAL '7' DAY
AND state_enum = 'SUCCEEDED'
ORDER BY total_execution_duration DESC
LIMIT 50

WITH weekly_stats AS (
    SELECT
        work_group_name,
        IF(submission_date_time >= DATE_TRUNC('week', CURRENT_DATE), 'this_week', 'last_week') AS period,
        COUNT(*) AS query_count,
        AVG(total_execution_duration) AS avg_exec_ms,
        APPROX_PERCENTILE(total_execution_duration, 0.95) AS p95_exec_ms,
        SUM(data_scanned_size) AS total_bytes_scanned
    FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
    WHERE submission_date_time >= DATE_TRUNC('week', CURRENT_DATE) - INTERVAL '7' DAY
    AND state_enum = 'SUCCEEDED'
    GROUP BY 1, 2
)
SELECT
    tw.work_group_name,
    lw.query_count AS last_week_queries,
    tw.query_count AS this_week_queries,
    lw.avg_exec_ms / 1000.0 AS last_week_avg_sec,
    tw.avg_exec_ms / 1000.0 AS this_week_avg_sec,
    ROUND((tw.avg_exec_ms - lw.avg_exec_ms) * 100.0 / lw.avg_exec_ms, 1) AS avg_pct_change,
    lw.p95_exec_ms / 1000.0 AS last_week_p95_sec,
    tw.p95_exec_ms / 1000.0 AS this_week_p95_sec
FROM weekly_stats tw
JOIN weekly_stats lw ON tw.work_group_name = lw.work_group_name
WHERE tw.period = 'this_week'
AND lw.period = 'last_week'
ORDER BY avg_pct_change DESC

SELECT
    work_group_name,
    DATE(submission_date_time) AS query_date,
    COUNT(*) AS query_count,
    AVG(query_queue_time_duration) / 1000.0 AS avg_queue_sec,
    APPROX_PERCENTILE(query_queue_time_duration, 0.50) / 1000.0 AS median_queue_sec,
    APPROX_PERCENTILE(query_queue_time_duration, 0.95) / 1000.0 AS p95_queue_sec,
    MAX(query_queue_time_duration) / 1000.0 AS max_queue_sec
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_DATE - INTERVAL '14' DAY
GROUP BY work_group_name, DATE(submission_date_time)
HAVING AVG(query_queue_time_duration) > 1000
ORDER BY avg_queue_sec DESC

SELECT
    query_execution_id,
    work_group_name,
    principal_arn,
    submission_date_time,
    data_scanned_size / POWER(1024, 4) AS data_scanned_tb,
    total_execution_duration / 1000.0 AS execution_seconds,
    SUBSTR(query_string, 1, 300) AS query_preview
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_DATE - INTERVAL '7' DAY
AND state_enum = 'SUCCEEDED'
ORDER BY data_scanned_size DESC
LIMIT 50

SELECT
    query_execution_id,
    work_group_name,
    submission_date_time,
    state_enum,
    total_execution_duration / 1000.0 AS execution_seconds,
    data_scanned_size / POWER(1024, 2) AS data_scanned_mb,
    SUBSTR(query_string, 1, 200) AS query_preview
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE principal_arn = 'arn:aws:iam::111122223333:user/example-user' -- Replace with target user
AND submission_date_time >= CURRENT_DATE - INTERVAL '7' DAY
ORDER BY submission_date_time DESC

SELECT
    query_execution_id,
    work_group_name,
    principal_arn,
    submission_date_time,
    state_enum,
    ROUND(data_scanned_size / POWER(1024.0, 3), 3) AS data_scanned_gb,
    SUBSTR(query_string, 1, 200) AS query_preview
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
CROSS JOIN UNNEST(tables_accessed) AS t(tbl)
WHERE tbl.database_name = 'my_database' -- Replace with target database
AND tbl.table_name = 'my_table' -- Replace with target table
AND submission_date_time >= CURRENT_DATE - INTERVAL '30' DAY
ORDER BY submission_date_time DESC

WITH error_stats AS (
    SELECT
        work_group_name,
        COUNT(*) AS total_queries,
        COUNT(*) FILTER (WHERE state_enum = 'FAILED' AND error_category_enum = 1) AS system_errors,
        COUNT(*) FILTER (WHERE state_enum = 'FAILED' AND error_category_enum = 2) AS user_errors
    FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
    WHERE submission_date_time >= CURRENT_DATE - INTERVAL '7' DAY
    GROUP BY work_group_name
)
SELECT
    work_group_name,
    total_queries,
    system_errors,
    ROUND(system_errors * 100.0 / SUM(system_errors) OVER (), 1) AS percent_of_all_system_errors,
    user_errors,
    ROUND(user_errors * 100.0 / SUM(user_errors) OVER (), 1) AS percent_of_all_user_errors
FROM error_stats

SELECT
    query_execution_id,
    work_group_name,
    principal_arn,
    submission_date_time,
    error_message,
    ROUND(data_scanned_size / POWER(1024.0, 3)) AS data_scanned_gb,
    ROUND(total_execution_duration / 1000.0, 1) AS execution_seconds,
    SUBSTR(query_string, 1, 200) AS query_preview
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_DATE - INTERVAL '7' DAY
AND state_enum = 'FAILED'
AND error_message LIKE 'Query exhausted resources%'
ORDER BY data_scanned_size DESC

SELECT
    DATE_TRUNC('week', submission_date_time) AS week,
    COUNT(*) AS reservation_queries,
    ROUND(SUM(dpu_count * total_execution_duration / 3600000.0), 1) AS dpu_hours
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_DATE - INTERVAL '90' DAY
AND state_enum = 'SUCCEEDED'
AND dpu_count > 0 -- populated only for capacity-reservation queries
GROUP BY 1
ORDER BY 1