CODEWITHSUNDEEP
amazon-redshift
Jason
Jason

Reputation: 1784

See why an Amazon Redshift scheduled query fails

I have a scheduled query in Redshift that simply will not run. It works when I execute the query manually in the query editor, just not when scheduled. I can't seem to find any feedback about why it's failing, though. I can see the schedule history, a unique Id, and the status of "Failed", but no more info.

Is there a system table/view that I can dig into and find out what's going on with my query? The SQL consists of basic select, update, insert style code - nothing fancy, just long.

Upvotes: 6

Views: 3240

Answers (4)

noob_user
noob_user

Reputation: 1

I encountered a situation where my IAM user wasn’t permitted to assume the role (via AWS CLI or CloudShell) used by the scheduled query—even attaching an inline policy wasn’t allowed.

So, I did a workaround to at least obtain the error message of the failed query.

select * from pg_user;

In the result set, look for the record corresponding to your IAM role (this is the role used by the scheduled query). Note its usersysid.

Then use this usersysid as user_id to get the error msg. (for example, suppose it is 1), run this query:

SELECT user_id, query_id, transaction_id, session_id, database_name, query_type, status, error_message, query_text 
FROM sys_query_history
WHERE status = 'failed' and user_id=1;

Upvotes: 0

Rizxcviii
Rizxcviii

Reputation: 138

For anyone else wondering, I was able to follow this tutorial and able to check the reason why my scheduled query was also failing. I also use Redshift serverless and this solution works for that service also: https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor-v2-schedule-query-troubleshooting-cloudshell.html

Upvotes: 0

gato negro
gato negro

Reputation: 123

It says in the description that it doesn't record SQL errors which would likely be the problem of the failed query.

I'm trying to sort this one as well and it seems it's limited as I only see the

Error

Any idea how to get the associated query ID, PID, or EID so I can check maybe against the query history?

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300728

You can look for any errors using:

select 
    process, 
    errcode, 
    linenum as line,
    trim(error) as err
from 
    stl_error;

Also: Retrieve Redshift error messages

Ref.: STL_ERROR

Upvotes: 0

Related Questions