steampipe plugin install aws

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

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The AWS Account ID in which the resource is located.
catalogtextThe name of the data catalog used in the query execution.
completion_date_timetimestamp with time zoneThe date and time that the query completed.
data_manifest_locationtextThe location and file name of a data manifest file.
data_scanned_in_bytesbigintThe number of bytes in the data that was queried.
databasetextThe name of the data database used in the query execution.
effective_engine_versiontextThe engine version on which the query runs.
encryption_optiontextIndicates 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_millisbigintThe number of milliseconds that the query took to execute.
error_categorybigintAn integer value that specifies the category of a query failure error.
error_messagetextContains a short description of the error that occurred.
error_typebigintAn integer value that provides specific information about an Athena query error.
execution_parametersjsonbA list of values for the parameters in a query.
expected_bucket_ownertextThe Amazon Web Services account ID that you expect to be the owner of the Amazon S3 bucket specified by ResultConfiguration$OutputLocation.
idtext=The unique identifier for each query execution.
kms_keytextFor SSE_KMS and CSE_KMS, this is the KMS key ARN or ID.
output_locationtextThe location in Amazon S3 where your query results are stored.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
querytextThe SQL query statements which the query execution ran.
query_planning_time_in_millisbigintThe number of milliseconds that Athena took to plan the query processing flow.
query_queue_time_in_millisbigintThe number of milliseconds that the query was in your query queue waiting for resources.
regiontextThe AWS Region in which the resource is located.
result_reuse_by_age_enabledbooleanTrue if previous query results can be reused when the query is run.
result_reuse_by_age_mag_age_in_minutesbigintSpecifies, in minutes, the maximum age of a previous query result that Athena should consider for reuse. The default is 60.
retryablebooleanTrue if the query might succeed if resubmitted.
reused_previous_resultbooleanTrue if a previous query result was reused; false if the result was generated.
s3_acl_optiontextThe Amazon S3 canned ACL that Athena should specify when storing query results.
selected_engine_versiontextThe engine version requested by the users.
service_processing_time_in_millisbigintThe number of milliseconds that Athena took to finalize and publish the query results after the query engine finished running the query.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
statetextThe state of query execution.
state_change_reasontextFurther detail about the status of the query.
statement_typetextThe type of query statement that was run.
submission_date_timetimestamp with time zoneThe date and time that the query was submitted.
substatement_typetextThe kind of query statement that was run.
total_execution_time_in_millisbigintThe number of milliseconds that Athena took to run the query.
workgrouptext=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