sql - How to change the name of the Athena results stored in S3?

Question

The results of Athena query is saved by the query id (a long string) in S3. I was wondering if there's a way to save the results of the query with a pre-specified name? (that can later be easily looked up)

Answer 1

You can do so by a simple AWS Lambda function.

Change names of AWS Athena results stored in S3 bucket

client = boto3.client('athena')
s3 = boto3.resource("s3")

#run query 
queryStart = client.start_query_execution(
    QueryString = '
      #PUT_YOUR_QUERY_HERE
      SELECT *
      FROM "db_name"."table_name"
      WHERE value > 50
    ',
    QueryExecutionContext = {
      'Database': "covid_data" //YOUR_ATHENA_DATABASE_NAME
    },
    ResultConfiguration = {
      #query result output location you mentioned in AWS Athena
      "OutputLocation": "s3://bucket-name-X/folder-Y/"
    }
  )

#executes query and waits 3 seconds
queryId = queryStart['QueryExecutionId']
time.sleep(3)

#copies newly generated csv file with appropriate name
#query result output location you mentioned in AWS Athena
queryLoc = "bucket-name-X/folder-Y/" + queryId + ".csv"

#destination location and file name
s3.Object("bucket-name-A", "report-2018.csv").copy_from(CopySource = queryLoc)

#deletes Athena generated csv and it's metadata file
response = s3.delete_object(
    Bucket='bucket-name-A',
    Key=queryId+".csv"
)
response = s3.delete_object(
    Bucket='bucket-name-A',
    Key=queryId+".csv.metadata"
)
print('{file-name} csv generated')