2

I wan to run my Athena query through AWS Lambda, but also change the name of my output CSV file from Query Execution ID to my-bucket/folder/my-preferred-string.csv

I tried searching for the results on web, but couldn't found the exact code for lambda function.

I am a data scientist and a beginner to AWS. This is a one time thing for me, so looking for a quick solution or a patch up.

NoobxNoob
  • 23
  • 1
  • 3

1 Answers1

2

This question is already posted here

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

# Run query
queryStart = client.start_query_execution(
    # PUT_YOUR_QUERY_HERE
    QueryString = '''
        SELECT *
        FROM "db_name"."table_name"
        WHERE value > 50
    ''',
    QueryExecutionContext = {
        # YOUR_ATHENA_DATABASE_NAME
        'Database': "covid_data"
    },
    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')
Pranav
  • 151
  • 1
  • 4
  • It works! Never knew I could run my lambda code within 5 minutes, I was stuck on this for several hours. Thanks for the quick response. Now, do I need to run my Lambda function manually each time I want the names of my query results changed? – NoobxNoob Jun 07 '20 at 02:36
  • You can do so by setting an appropriate trigger for your function. Please refer https://docs.aws.amazon.com/lambda/latest/dg/lambda-invocation.html – Pranav Jun 07 '20 at 02:45
  • Thanks, I want my lambda function to run thrice a day at a certain time. Do you have any quick-fix for that? – NoobxNoob Jun 07 '20 at 02:49
  • You can do so by cron expressions. Refer https://stackoverflow.com/a/56601973/13676851 – Pranav Jun 07 '20 at 02:51