AWS Athena Metrics Query Guide

Overview

AWS Athena enables SQL queries against metrics data stored in S3. This guide provides sample queries for common metric analysis patterns across application telemetry.

Getting Started

1. Login to AWS Console
2. Select appropriate role (e.g., DEV EC2 Admin)
3. Navigate to Athena service
4. Select the metrics database
5. Run queries in the query editor

Query Structure

Metrics are typically partitioned by:

• partition_0: Metric category (e.g., system, realtime)
• partition_1: Year (e.g., 2022)
• partition_2: Month (e.g., 04)
• partition_3: Day (e.g., 06)

System Metrics Queries

Device Battery Metrics

SELECT * 
FROM "app_metrics"."dev_v2" 
WHERE partition_0 = 'system' 
  AND partition_1 = '2022' 
  AND partition_2 = '04' 
  AND partition_3 = '06'
  AND metricname = 'lastPlugBatteryCharge' 
ORDER BY timestamp DESC

Memory Metrics

-- Available RAM
SELECT * 
FROM "app_metrics"."dev_v2" 
WHERE partition_0 = 'system' 
  AND partition_1 = '2022' 
  AND partition_2 = '04' 
  AND partition_3 = '06'
  AND metricname = 'ramAvailable' 
ORDER BY timestamp DESC

-- Total RAM
SELECT * 
FROM "app_metrics"."dev_v2" 
WHERE partition_0 = 'system' 
  AND partition_1 = '2022' 
  AND partition_2 = '04' 
  AND partition_3 = '06'
  AND metricname = 'ramTotal' 
ORDER BY timestamp DESC

Storage Metrics

-- Internal storage total
SELECT * 
FROM "app_metrics"."dev_v2" 
WHERE partition_0 = 'system' 
  AND partition_1 = '2022' 
  AND partition_2 = '04' 
  AND partition_3 = '06'
  AND metricname = 'hddInternalTotal' 
ORDER BY timestamp DESC

-- Internal storage available
SELECT * 
FROM "app_metrics"."dev_v2" 
WHERE partition_0 = 'system' 
  AND partition_1 = '2022' 
  AND partition_2 = '04' 
  AND partition_3 = '06'
  AND metricname = 'hddInternalAvailable' 
ORDER BY timestamp DESC

Network Metrics

-- Network status
SELECT * 
FROM "app_metrics"."dev_v2" 
WHERE partition_0 = 'system' 
  AND partition_1 = '2022' 
  AND partition_2 = '04' 
  AND partition_3 = '06'
  AND metricname = 'networkStatus' 
ORDER BY timestamp DESC

-- Network type (WiFi, LTE, etc.)
SELECT * 
FROM "app_metrics"."dev_v2" 
WHERE partition_0 = 'system' 
  AND partition_1 = '2022' 
  AND partition_2 = '04' 
  AND partition_3 = '06'
  AND metricname = 'networkType' 
ORDER BY timestamp DESC

-- Signal strength (RSRP)
SELECT * 
FROM "app_metrics"."dev_v2" 
WHERE partition_0 = 'system' 
  AND partition_1 = '2022' 
  AND partition_2 = '04' 
  AND partition_3 = '06'
  AND metricname = 'networkRsrp' 
ORDER BY timestamp DESC

Performance Optimization Tips

1. Always use partition filters: Queries without partition predicates scan entire datasets
2. Limit result sets: Use LIMIT for exploratory queries
3. Select specific columns: Avoid SELECT * in production queries
4. Use appropriate data types: Cast columns when needed for aggregations

Sample Aggregation Query

-- Daily metric summary
SELECT 
  DATE(from_unixtime(timestamp/1000)) as metric_date,
  metricname,
  COUNT(*) as event_count,
  AVG(CAST(value AS DOUBLE)) as avg_value
FROM "app_metrics"."dev_v2" 
WHERE partition_0 = 'system' 
  AND partition_1 = '2022' 
  AND partition_2 = '04'
GROUP BY DATE(from_unixtime(timestamp/1000)), metricname
ORDER BY metric_date DESC, event_count DESC