AWS Athena monitoring with AppDynamics

[Abhi.Bajaj]

Guide to Monitoring AWS Athena Performance with AppDynamics

Amazon Athena is a serverless, interactive analytics service that provides a simplified and flexible way to analyze petabytes of data where it lives. It is an important tool for analyzing existing data. AppDynamics is one of the best monitoring solutions that gives you insight into applications and infrastructure. Let’s dive into our setup.

Prerequisites

• Machine Agent installed on any Linux box
• The Linux box should have permission to fetch CloudWatch metrics

Setting up Amazon Athena

Set up Amazon Athena

If you all have Athena set up, then scroll down. If not, follow the below steps:

1. Create an S3 bucket where we will save Athena query results. I created one called “athena-query-result-abhi”

Setting Up Query Result Location

1. Click “Edit settings” in the Athena console
2. Specify your S3 bucket: Enter s3://athena-query-result-abhi as the query result location
3. Save the settings

Enable Amazon Athena to publish query metrics to AWS CloudWatch

1. Edit the WorkGroup your Amazon Athena is part of and select “Publish query metrics to AWS CloudWatch”

Running the Sample Queries

In the Athena console, run the following queries to create a sample database and table:

1. Create the Database:
   

   CREATE DATABASE sampledb;​

2. Create a Sample Table with some inline data:
   

   CREATE TABLE sampledb.sampletable AS
   SELECT
     'value1' AS col1,
     'value2' AS col2,
     'value3' AS col3
   UNION ALL
   SELECT
     'value4' AS col1,
     'value5' AS col2,
     'value6' AS col3;​

3. Run a Sample Query to generate activity:
   

   SELECT * FROM sampledb.sampletable LIMIT 10;​

Running Queries to Generate Metrics:

1. Execute the following queries to generate sufficient activity and metrics:
   

   SELECT * FROM sampledb.sampletable LIMIT 10;
   SELECT col1, COUNT(*) FROM sampledb.sampletable GROUP BY col1;
   SELECT COUNT(*) FROM sampledb.sampletable WHERE col2 = 'value2';
   SELECT col1, col2 FROM sampledb.sampletable WHERE col3 = 'value3';​

2. Great work, your Athena is all set up

 

Machine Agent

Now, let’s work on the machine agent side.

1. SSH inside the box where your Machine Agent is running
2. In the Machine Agent Home folder, Go to the Monitors folder and create a directory called DynamoDb. In my case I used, MA_HOME = /opt/appdynamics/ma
   

   cd /opt/appdynamics/ma/monitors
   mkdir Athena​

3. Inside the Athena folder, create a file called script.sh with the below content:
   NOTE: Please edit REGION and START_TIME, END_TIME if required
   

   #!/bin/bash
   
   # List of all metrics you want to fetch for Athena
   declare -a METRICS=("DPUAllocated" "DPUConsumed" "DPUCount" "EngineExecutionTime" "ProcessedBytes" "QueryPlanningTime" "QueryQueueTime" "ServicePreProcessingTime" "ServiceProcessingTime" "TotalExecutionTime")
   
   # Define the time period (in ISO8601 format)
   START_TIME=$(date --date='48 hours ago' --utc +%Y-%m-%dT%H:%M:%SZ)
   END_TIME=$(date --utc +%Y-%m-%dT%H:%M:%SZ)
   
   # AWS region
   REGION="us-east-1"
   
   # Fetch all workgroups
   WORKGROUPS=$(aws athena list-work-groups --region $REGION --query 'WorkGroups[*].Name' --output text)
   
   # Loop through each workgroup and fetch the metrics
   for WORKGROUP in $WORKGROUPS; do
     # Loop through each metric and fetch the data
     for METRIC_NAME in "${METRICS[@]}"; do
       # Fetch the metric data using AWS CLI
       METRIC_VALUE=$(aws cloudwatch get-metric-statistics --region $REGION --namespace AWS/Athena \
         --metric-name $METRIC_NAME \
         --dimensions Name=QueryState,Value=SUCCEEDED Name=QueryType,Value=DML Name=WorkGroup,Value=$WORKGROUP \
         --start-time $START_TIME \
         --end-time $END_TIME \
         --period 300 \
         --statistics Sum \
         --query 'Datapoints | sort_by(@, &Timestamp)[-1].Sum' \
         --output text)
   
       # If metric value is empty, set to 0, else format it as an integer
       if [ -z "$METRIC_VALUE" ]; then
         METRIC_VALUE="0"
       else
         METRIC_VALUE=$(echo $METRIC_VALUE | awk '{if($1+0==$1){print int($1)}else{print "0"}}')
       fi
   
       # Echo the metric in the specified format
       echo "name=Custom Metrics|Athena|$WORKGROUP|$METRIC_NAME,value=$METRIC_VALUE"
     done
   done

4. Create another file called monitor.xml with the below content:
   

   <monitor>
       <name>Athena monitoring</name>
       <type>managed</type>
       <description>Athena monitoring</description>
       <monitor-configuration>
       </monitor-configuration>
       <monitor-run-task>
           <execution-style>periodic</execution-style>
           <name>Run</name>
           <type>executable</type>
           <task-arguments>
           </task-arguments>
           <executable-task>
               <type>file</type>
               <file>script.sh</file>
           </executable-task>
       </monitor-run-task>
   </monitor>​

   
5. Let’s restart your Machine Agent
6. Once you are done, you will be able to see your Athena metrics in the AppDynamics Machine Agent’s metric browser, as seen below