Problem

When you submit an SQL query to your SQL warehouse with the SQL execution API, it intermittently fails with an error message, timeout due to inactivity even though the query previously completed successfully and the API response was successful as well.

Cause

When a query is submitted using the SQL execution API, it can go to a pending state if there are more results or if it takes more time to complete. If you close the POST API call’s connection without obtaining a status (succeeded or failed), the query remains active and is in a waiting state. If no further calls are made against the query, the query fails with the timeout error.

Solution

Make the POST API call to '/api/2.0/sql/statements' to execute the query. If the POST call returns a query status (succeeded or failed), the query has been executed.

If the POST call returns a pending state, take the statement_id from the JSON response, and use this ID to make a GET API call to  '/api/2.0/sql/statements/{<statement-id>}’ instead. 

Make the GET call in a loop, waiting between each call, until the status returns as succeeded or failed. The successful return includes results, and the query is marked as closed.

For more information, refer to the Execute a SQL statement and Get status, manifest, and result first chunk documentation.