GURURAJAN's Blog

# AWS - Athena query result management FAQs

# AWS - Athena query result management FAQs

#aws_tips_and_tricks

GURURAJAN's photo
GURURAJAN
·

6 min read

How to set up the query result location?

  • The query result location that Athena uses is determined by a combination of workgroup settings and client-side settings.
  • Athena console, (Athena -> Query Editor -> Settings -> Manage Settings).
  • The workgroup settings can be specified using the console, CLI, or API.

Can we override the query result location?

  • Yes, each workgroup setting has an Override client-side settings option that can be enabled.
  • When this option is enabled, the workgroup settings take precedence over the applicable client-side setting

Will changing the result location affect the existing process?

  • Changing the query result location will not affect the existing process.
  • The result is saved in the new location.
  • Ensure that the new location is accessible by your existing processes.

Is Athena regional service or Availability Zone-based service?

  • Athena is a regional service.

What is a saved query?

  • Athena allows the user to save the query in the console, the saved query results will be stored in the specified query result location in the following format QueryName/yyyy/mm/dd/
  • Query Result Location: QueryResultsLocationInS3/[QueryName|Unsaved/yyyy/mm/dd/]

Why are the API query results stored directly?

  • Only queries run from the console whose results path has not been overridden by workgroup settings store results using this path structure: QueryName/yyyy/mm/dd/.
  • Queries that run from the AWS CLI or using the Athena API are saved directly to the QueryResultsLocationInS3

Does Athena cache query results?

  • No, Athena doesn't support query caching.

How to trigger saved queries via API / CLI?

  • Athena does not support trigger-saved queries via CLI/API directly.

Is there a Workaround to trigger the saved queries via API?

  • Use the ListNamedQueries API to grab the saved queries' IDs.
  • Using this resulting list, you can then loop through the list running GetNamedQuery on each query ID.
  • Once you have found the saved query that you are looking for, you can then run StartQueryExecution using the resulting values from the GetNamedQuery operation.

Can we store the results in a specific folder via API or CLI?

  • Yes, bypassing the OutputLocation via the API or CLI.

Can we use saved queries and store the results based on saved query name folders in s3?

  • Yes, however this only works with queries run from the console whose results path has not been overridden by workgroup settings

Can we use saved queries in Quicksight?

  • No, we can not directly use saved queries in Quicksight. You would need to copy the query and use the custom SQL option in QuickSight to create a dataset from it.

How to monitor Athena's activity?

  • We can collect the query execution details such as
    • Query string
    • Data scanned
    • Scanned time
    • Database
    • Table
    • Time etc. From Cloudtrail. Keep in mind that Cloudtrail will only keep the logs for 90 days.

How to keep the Athena query execution details for longer?

  • Periodically we can collect the Cloud Trial Athena logs and store them in S3 for future reference.

What is the maximum Query string length?

  • The fixed maximum allowed query string length is 262144 bytes.

Which encoding is used in Athena?

  • UTF-8.

Is there a cost involved for canceled queries?

  • Canceled queries are charged based on the amount of data scanned.

Is there a cost involved for failed queries?

  • There are no charges for failed queries.

What is the minimum amount of scanned data considered for billing?

  • 10MB minimum per query.

Why is the sorting in S3 not working?

  • To enable sorting in the S3, use the search to reduce the size of the list to 999 objects or fewer.

How to delete huge amounts of data, i.e: 18000 files?

  • There is no direct way from Athena's side to delete the files from the result location. Please consider using the s3 lifecycle policy to delete objects older than a specific time.

Is it safe to delete the old file (More than 45 days or 90 Days)? If yes, how do we do it?

  • You can safely delete the old files as long as you don't want to go back and review the results of that particular query at the time it was run.
  • If you delete the data and then decide you wish to know the results of that query again, you will then have to re-run the query.
  • AWS does not recommend deleting the Query metadata files as this will cause important information on the query to be lost, however, you are still welcome to do this as long as you do not require information on that query anymore.
  • You can delete the files from your S3 bucket by navigating to the S3 bucket in the S3 console, selecting the files you wish to delete, and clicking delete.
  • You can also set a lifecycle configuration on the bucket if you wish to prevent the retention of S3 objects past a certain length of time S3 Lifecycle

Will deleting the data cause any error?

  • If you select to view a query in the Athena "Recent queries" tab for which you have already deleted the data then you will receive an error message "Could not find results". You can then re-run the query to fetch new results.

Do we need to take a backup?

  • Whether you decide to delete the data or keep it for future use, backing it up or not, is up to you and your use case.

Can we use the stored query results in SQL Query?

  • You can create a table using a query result file if you want using CREATE EXTERNAL TABLE, the same way you would with any other CSV file. You can also use CREATE TABLE AS SELECT, a view, or the WITH clause if you wish to use the results of an Athena query in another query however please note these methods will not utilize the old results file but will re-run the query instead.

How to query the results file?

  • If you wish to create a table on the CSV results file, then you must move the file into a new folder that contains only the data to be scanned in the new table. If any other files are present you may encounter errors as Athena can not recognize exclusion patterns.

Reference:

aws_tips_and_tricks