(InvalidRequestException) when calling the GetQueryResults… Querying Athena From Lambda Python… Cannot Read Results

Question

I have been trying to query Athena from my lambda function (Python3.8) but I keep getting the same error although tried adding an if else statement to check the status of the execution and i always the same error on the aws console and the cli locally

Here is the lambda function:

import json
import boto3
import time

def function(event, context):
    client=boto3.client('athena')

    #setup and perform query
    queryStart=client.start_query_execution(
        QueryString = 'SELECT * FROM my_s3_bucket_developer limit 8;',
        QueryExecutionContext = {
            'Database':'mydb'
        },
        ResultConfiguration = {
            'OutputLocation': 's3://athena-results-queries-developer/'
        }
    ) 
    
    #get query ID
    queryId= queryStart['QueryExecutionId']

    #we gonna sleep the function now because we don't know how 
    #long it will take to execute the query
    time.sleep(25)

    results=client.get_query_results(QueryExecutionId = queryId)
    for row in results['ResultSet']['Rows']:
        print(row)

and this is the IAM Role I have attached to my lambda function:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "S3:GetBucketLocation",
                "S3:GetObject"
            ],
            "Resource": [
                "arn:aws:s3:::athena-results-queries-developer/*",
            ]
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": [
                "athena:StartQueryExecution",
                "athena:StopQueryExecution",
                "athena:GetQueryExecution",
                "athena:GetQueryResults",
                "glue:GetTable"
            ],
            "Resource": "*"
        }
    ]
}

This is the error I keep getting in the logs

An error occurred (InvalidRequestException) when calling the GetQueryResults operation: Query did not finish successfully. Final query state: FAILED

"errorType": "InvalidRequestException"

"stackTrace": [
[
"/var/task/lambda_function.py",
26,
"function",
"results=client.get_query_results(QueryExecutionId = queryId)"
], [ "/var/runtime/botocore/client.py", 316, "_api_call", "return self._make_api_call(operation_name, kwargs)" ], [ "/var/runtime/botocore/client.py", 626, "_make_api_call", "raise error_class(parsed_response, operation_name)" ] ] }

If anyone can help me would really appreciate it - I have been trying to solve this for days

Theo Theo · Accepted Answer · 2020-07-07T16:25:07

The problem is that you don't wait for the query to complete properly. You need to call get_query_execution and check that the query has succeeded before you call get_query_results.

There's a full example here that you could take inspiration from: https://www.ilkkapeltola.fi/2018/04/simple-way-to-query-amazon-athena-in.html

(InvalidRequestException) when calling the GetQueryResults… Querying Athena From Lambda Python… Cannot Read Results

1 Answers