Question:
I’m currently banging my head against the wall since a few days trying to solve a strange issue happening with the recently released redshift data-api. I’m trying to execute a SQL statement on my Redshift cluster using the execute statement api and list the statement using the list statements api.
When testing locally, the API behaves well and I have no issues listing the statements. However, after deploying my code on Lambda, I’m suddenly not able to find any of my executed queries even though I’m receiving 200 responses for the execute statements requests.
Here is an extract of my CloudWatch logs showing the execute statement 200 response with the query ID:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
{ "level": "INFO", "location": "execute_asynchronous_sql_query:71", "message": { "message": "Successfully submitted sql query to redshift cluster", "query_id": "706a0ca1-084c-4733-a534-571993afd23a", "execution_statement_response": { "ClusterIdentifier": "#######", "CreatedAt": "2021-01-15 06:00:37.504000+00:00", "Database": "#######", "Id": "706a0ca1-084c-4733-a534-571993afd23a", "SecretArn": "arn:aws:secretsmanager:##############", "ResponseMetadata": { "RequestId": "#######", "HTTPStatusCode": 200, "HTTPHeaders": { "x-amzn-requestid": "#######", "content-type": "application/x-amz-json-1.1", "content-length": "237", "date": "Fri, 15 Jan 2021 06:00:38 GMT" }, "RetryAttempts": 0 }, "StatementName": "ww-redshift-asynch-2021-01-15T06:00:37.206308-summary_spots.sql" } }, "timestamp": "2021-01-15 06:00:38,008", "service": "ww_dashboard", "sampling_rate": 0, "sample_rate": 0.4, "xray_trace_id": "##############" |
}
And here is the error I get when trying to describe the SQL statement status using the AWS cli with that query ID.
|
1 2 3 4 |
aws redshift-data describe-statement --id 706a0ca1-084c-4733-a534-571993afd23a An error occurred (ResourceNotFoundException) when calling the DescribeStatement operation: Query does not exist. |
Answer:
The role/user to execute describe-statement or get-statement-result need to be same with the one you use to execute-statement.
Also, need to be same region.