CODEWITHSUNDEEP
amazon-web-servicesaws-lambdaamazon-athena
Amir
Amir

Reputation: 1087

Using AWS lambda to run query on Athena

I have a very simple table on AWSAthena with three column: name, city and price. I can run the following simple query:

select * from mytestdb.test where city='austin'

and the running time is less than 1 second. I create a lambda function using boto3 to run the same query however the query can not be finished after 3 mins

import time
import boto3

# athena constant
DATABASE = 'mytestdb'
TABLE = 'test'
# S3 constant
output='s3://mybucket'


COLUMN = 'city'

def lambda_handler(event, context):
    # get keyword
    keyword = 'Austin'
    # created query
    query = "SELECT * FROM %s.%s where %s = '%s';" % (DATABASE, TABLE, COLUMN, keyword)
    # athena client
    client = boto3.client('athena')


    # Execution
    response = client.start_query_execution(
        QueryString=query,
        QueryExecutionContext={
            'Database': DATABASE
        },
        ResultConfiguration={
            'OutputLocation': output,
        }
    )

    # get query execution id
    query_execution_id = response['QueryExecutionId']
    print(query_execution_id)

    # get execution status


    # get query execution

    query_status = client.get_query_execution(QueryExecutionId=query_execution_id)
    print('Amir2')
    query_execution_status = query_status['QueryExecution']['Status']['State']


    time.sleep(200)        

    if query_execution_status == 'SUCCEEDED':
        result = client.get_query_results(QueryExecutionId=query_execution_id)
    else:
        print('killed')
        client.stop_query_execution(QueryExecutionId=query_execution_id)

    return

the table has total 10 rows so it is not large at all. Is the right way to lookup for the desirable value based on city or there is better way to do that?

UPDATE: here is my lambda permission (It has full access to athena and s3:

{   "Version": "2012-10-17",   "Statement": [
    {
      "Sid": "Stmt1547414166585",
      "Action": [
        "athena:*"
      ],
      "Effect": "Allow",
      "Resource": "*"
    },
    {
      "Sid": "Stmt1547414166586",
      "Action": [
        "s3:*"
      ],
      "Effect": "Allow",
      "Resource": "arn:aws:s3:::your-bucket-name/*"
    }    ] }

Upvotes: 2

Views: 3996

Answers (1)

chris
chris

Reputation: 2810

A little late here but make sure the Role for your lambda has the correct policies.

I solved it by giving full access to not only Athena but also Glue which you may be missing. Add those policies to your lambda role.

Upvotes: 1

Related Questions