Table: aws_athena_query_execution - Query AWS Athena Query Executions using SQL

AWS Athena Query Execution is a feature of Amazon Athena that allows you to run SQL queries on data stored in Amazon S3. It executes queries using an interactive query service that leverages standard SQL. This enables you to analyze data directly in S3 without the need for complex ETL jobs.

Table Usage Guide

The aws_athena_query_execution table in Steampipe provides you with information about query executions within AWS Athena. This table allows you, as a data analyst or developer, to query execution-specific details, including execution status, result configuration, and associated metadata. You can utilize this table to track the progress of queries, analyze the performance of queries, and understand the cost of running specific queries. The schema outlines the various attributes of the Athena query execution for you, including the query execution id, query, output location, data scanned, and execution time.

Examples

List all queries in error

Explore which queries have resulted in errors to understand the issues and rectify them accordingly. This is useful in identifying and resolving potential problems within your AWS Athena query execution.

select
  id,
  query,
  error_message,
  error_type
from
  aws_athena_query_execution
where
  error_message is not null;

select
  id,
  query,
  error_message,
  error_type
from
  aws_athena_query_execution
where
  error_message is not null;

Estimate data read by each workgroup

Analyze the volume of data processed by each workgroup to understand workload distribution and optimize resources accordingly. This can be useful in identifying workgroups that are processing large amounts of data and may require additional resources or optimization.

select
  workgroup,
  sum(data_scanned_in_bytes)
from
  aws_athena_query_execution
group by
  workgroup;

select
  workgroup,
  sum(data_scanned_in_bytes)
from
  aws_athena_query_execution
group by
  workgroup;

Find queries with biggest execution time

Discover the queries that have the longest execution times to identify potential areas for performance optimization and enhance the efficiency of your AWS Athena operations.

select
  id,
  query,
  workgroup,
  engine_execution_time_in_millis
from
  aws_athena_query_execution
order by
  engine_execution_time_in_millis
limit
  5;

select
  id,
  query,
  workgroup,
  engine_execution_time_in_millis
from
  aws_athena_query_execution
order by
  engine_execution_time_in_millis
limit
  5;

Find most used databases

Discover the databases that are frequently used in your AWS Athena environment. This can help optimize resource allocation and identify potential areas for performance improvement.

select
  database,
  count(id) as nb_query
from
  aws_athena_query_execution
group by
  database
order by
  nb_query
limit
  5;

select
  database,
  count(id) as nb_query
from
  aws_athena_query_execution
group by
  database
order by
  nb_query
limit
  5;

Schema for aws_athena_query_execution

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
account_id	text	=, !=, ~~, ~~, !~~, !~~	The AWS Account ID in which the resource is located.
catalog	text		The name of the data catalog used in the query execution.
completion_date_time	timestamp with time zone		The date and time that the query completed.
data_manifest_location	text		The location and file name of a data manifest file.
data_scanned_in_bytes	bigint		The number of bytes in the data that was queried.
database	text		The name of the data database used in the query execution.
effective_engine_version	text		The engine version on which the query runs.
encryption_option	text		Indicates whether Amazon S3 server-side encryption with Amazon S3-managed keys (SSE_S3), server-side encryption with KMS-managed keys (SSE_KMS), or client-side encryption with KMS-managed keys (CSE_KMS) is used.
engine_execution_time_in_millis	bigint		The number of milliseconds that the query took to execute.
error_category	bigint		An integer value that specifies the category of a query failure error.
error_message	text		Contains a short description of the error that occurred.
error_type	bigint		An integer value that provides specific information about an Athena query error.
execution_parameters	jsonb		A list of values for the parameters in a query.
expected_bucket_owner	text		The Amazon Web Services account ID that you expect to be the owner of the Amazon S3 bucket specified by ResultConfiguration$OutputLocation.
id	text	=	The unique identifier for each query execution.
kms_key	text		For SSE_KMS and CSE_KMS, this is the KMS key ARN or ID.
output_location	text		The location in Amazon S3 where your query results are stored.
partition	text		The AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
query	text		The SQL query statements which the query execution ran.
query_planning_time_in_millis	bigint		The number of milliseconds that Athena took to plan the query processing flow.
query_queue_time_in_millis	bigint		The number of milliseconds that the query was in your query queue waiting for resources.
region	text		The AWS Region in which the resource is located.
result_reuse_by_age_enabled	boolean		True if previous query results can be reused when the query is run.
result_reuse_by_age_mag_age_in_minutes	bigint		Specifies, in minutes, the maximum age of a previous query result that Athena should consider for reuse. The default is 60.
retryable	boolean		True if the query might succeed if resubmitted.
reused_previous_result	boolean		True if a previous query result was reused; false if the result was generated.
s3_acl_option	text		The Amazon S3 canned ACL that Athena should specify when storing query results.
selected_engine_version	text		The engine version requested by the users.
service_processing_time_in_millis	bigint		The number of milliseconds that Athena took to finalize and publish the query results after the query engine finished running the query.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
state	text		The state of query execution.
state_change_reason	text		Further detail about the status of the query.
statement_type	text		The type of query statement that was run.
submission_date_time	timestamp with time zone		The date and time that the query was submitted.
substatement_type	text		The kind of query statement that was run.
total_execution_time_in_millis	bigint		The number of milliseconds that Athena took to run the query.
workgroup	text	=	The name of the workgroup in which the query ran.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- aws

You can pass the configuration to the command with the --config argument:

steampipe_export_aws --config '<your_config>' aws_athena_query_execution