Athena query results at specific path on S3

Question

I am aware that running a saved Athena query stores results in an Amazon S3 location based on the name of the query and the date the query ran, as follows:

QueryLocation}/{QueryName|Saved}/{yyyy}/{mm}/{dd}/{QueryID}/

Is it possible to override this and store it on a path similar to

QueryLocation}/QueryName

overwriting the results file whenever the query is run?

Surely it can be done by renaming (moving + deleting) file on S3 but wonder if there is a straighter way.

user_419863 · Answer

Create Table as Select

Another way of storing Athena query results at a specific location in S3 is to use a CTAS-Query (CREATE TABLE AS SELECT).

Using this has tons of advantages, because you can even specify the result format. Gzipped JSON, Parquet etc...

CREATE TABLE default.my_result_table
WITH 
(
  format='JSON',
  external_location='s3://MY_BUCKET/MY_KEY/...'
) AS

SELECT * FROM MY_SOURCE_TABLE WHERE ....

You can even specify directly the partitions you want to use (for example

CREATE TABLE default.my_result_table
WITH 
(
  format='Parquet',
  external_location='s3://MY_BUCKET/MY_KEY/...',
  partitioned_by = ARRAY['parition_key_1', ...]
) AS

SELECT * FROM MY_SOURCE_TABLE WHERE ....

user_1109 · Answer

It is not possible to set the output location, you can only set the prefix. Athena will always use the query execution ID as the last part of the S3 key, i.e. what can be thought of as the filename. One reason for this is that the GetQueryResults API call reads the data off of S3, and if queries could overwrite each other's output you would end up with inconsistent states.

user_2441632 · Answer

Good question,

If you are running your query from the web console, you can set the QueryLocation

However, all that will do is create a series of tags formatted as such {QueryName}/{yyyy}/{mm}/{dd}/{QueryID}/ in the "folder" you specify.

Athena query results at specific path on S3

3 Answers

Create Table as Select