Petya Petrova - Fotolia
How to use Athena to troubleshoot AWS operations issues
Amazon Athena runs analysis against data stored in S3, where AWS admins can find a trove of ops data for troubleshooting and optimization. Use it on ELB, VPC and other services.
Operations is critical to running a successful application on AWS so that cloud users can prevent, or quickly detect and resolve, issues. Use these insights to avoid operations mishaps and optimize hosting for AWS systems that support applications.
CloudWatch provides visibility on AWS, with features such as Metrics, Alarms, Dashboards and Logs Insights. However, AWS users should also consider Amazon Athena, a tool that analyzes data, such as logs from operations. Analytics from Athena can help identify and troubleshoot potential issues in applications and the systems that support them, as well as point toward opportunities for improvement.
Amazon Athena is a serverless query service that provides analytics on data stored in S3, using SQL syntax. Many AWS services store log information in S3 or create log data that administrators can export to S3. The diverse examples below will give you an idea of how to use Athena to improve AWS operations. Then, follow the instructions to create a table and run your own queries.
Amazon Athena use cases in AWS operations
To dig into application troubleshooting and optimization via Athena, let's break down some specific types of log data to analyze.
Analyze web app delivery with CloudFront. CloudFront is an AWS content delivery network service. Deploying CloudFront between end users and web servers can boost web application performance, because it caches content in about 200 edge locations for proximity to end users and reduced latency.
For each request that takes place in CloudFront, logs show information such as date, time, bytes transferred, URL, client IP, response code, cache hit and miss and time taken. CloudFront optionally stores access logs in S3.
Use Athena's SQL queries to analyze CloudFront errors during a specific period or get the total bytes returned to end users. You can also analyze the source IPs for requests to the application, get the top URLs for a specific period of time and compile a summary of incoming traffic and responses.
While Athena gathers all this data for you, it's up to the AWS operations team to turn it into optimization actions or find the key to unlock a problem with the web application.
Ensure smooth ops with AWS Elastic Load Balancer. AWS users can enable logs for any type of load balancer from AWS: Application, Network or Classic. Requests to the load balancer get recorded in files stored in S3. The Elastic Load Balancer service logs fields such as timestamp, target processing time, response status, received and sent bytes and domain.
Use Athena's data analysis to identify load balancer errors in a particular time period, as well as highlight which transactions have the highest latency, to get to the bottom of AWS operations issues and prevent future ones.
Enable surveillance through CloudTrail logs. CloudTrail tracks activity in an AWS account, with a record of API calls, who made them, when and where from. Configure CloudTrail to store access logs in S3 for analysis with Athena.
Athena queries can identify who made a particular API call at a specific point in time or pull up the full list of API calls made by a particular Identity and Access Management (IAM) user. The same applies if you need to know all the AWS IAM entities that have called a specific AWS API.
With this CloudTrail data, Athena aids in security and compliance activities for AWS deployments, as well as ops ones.
Improve private cloud ops with VPC Flow Logs. Amazon Virtual Private Cloud (VPC) is an isolated network where you can launch AWS resources. VPC Flow Logs can then record all the traffic in and out of a VPC.
Analyze these logs to understand traffic patterns or troubleshoot specific situations. Try querying the VPC Flow Logs for the IP address of a server that received data from a particular EC2 instance, for example. Or, for a broader understanding of traffic patterns, use Athena to peruse incoming and outgoing bytes within a time period.
Track mobile app performance with Pinpoint Analytics. AWS Pinpoint Analytics records events in mobile applications. You can integrate Pinpoint Analytics with Kinesis Firehose and publish mobile event data to S3.
Use Athena to analyze Pinpoint Analytics data related to when a particular mobile session started and ended or to count a particular event type over time, such as clicks in a GUI element.
Use filters to pull in CloudWatch logs for server insights. If you already export server logs to CloudWatch Logs, you can also store and analyze them in S3 with Athena. Configure a CloudWatch Logs Subscription Filter that sends incoming log data to Kinesis Firehose, or use the AWS management console, API or Command Line Interface to trigger an export task to S3. This setup ensures logs from web servers like Apache and Nginx are available for data analysis to direct troubleshooting and optimization efforts.
Determine whether Athena is the best option for AWS log analytics activities on a case-by-case basis. For example, if log data is already available in CloudWatch Logs, CloudWatch Logs Insights might be a better alternative.
How to use Athena on AWS logs in S3
To get started with Athena, connect to the service through the management console, an API or a Java Database Connectivity driver. Then, define the schema, choose the relevant S3 data and execute queries.
Unlike a traditional database table, Athena does not create new data storage; it only points to a location in S3 and defines a table schema to be used when you execute queries. You can delete an Athena table without deleting any underlying data in S3.
There are three main ways to create an Athena table.
- Execute a CREATE EXTERNAL TABLE statement, where you specify all fields, their types and the location of data files in S3. This option offers the most control, with support for code versioning and automation steps.
- Select the Create table from S3 bucket option in the Athena console, where you specify the location in S3 for data files and all relevant fields and their type through a GUI. You must know the field structure, types and file location in S3. This option suits users that are not familiar with CREATE EXTERNAL TABLE statements, but it requires a number of manual steps.
- Create an Athena table with an AWS Glue crawler. A crawler is an automated process managed by Glue. It scans data stored in S3 and extracts metadata, such as field structure and file types. A crawler can access the log file data in S3 and automatically detect field structure to create an Athena table. Consider this option if you're unfamiliar with field structure in the data files and if you want to delegate Athena table creation to an external process.
How to get the best Athena results
To improve performance, use partitions in Athena tables. Use the PARTITIONED BY statement when you create a table, or create a table that points to a folder in S3 that corresponds to a subset of data, such as a specific month or day. This keeps Athena costs down, which is $5 per 1 TB of data scanned.
Run SQL statements to query the virtual tables. You can also use Athena tables as data sources for Amazon QuickSight dashboards, visualizing AWS log data for further analysis.
For best results, make sure that the log data in S3 follows a consistent format across all rows, otherwise, Athena will return errors when querying data files. This is mostly a concern when exporting custom application logs into S3 and won't be an issue for native AWS logs for services such as CloudFront, Elastic Load Balancer, CloudTrail, VPC and Pinpoint.
