How do I identify Data API query errors in Amazon Redshift?

Resolution

Amazon Redshift Data API is asynchronous. You don't need to wait for completion to run long-running queries.

When a query fails, the status of the query doesn't display immediately. Use the DescribeStatement API action to determine the reasons for failure for single or multiple queries.

Single queries

Identify the statement ID

To run a single query against an Amazon Redshift cluster, first obtain the statement ID.

To get a statement ID, run the ExecuteStatement API action. The following example command authenticates through AWS Secrets Manager and executes your SQL statement against a cluster:

aws redshift-data execute-statement
    --region us-east-1
    --secret arn:aws:secretsmanager:us-east-1:123456789012:secret:myuser-secret-hKgPWn
    --cluster-identifier redshift-cluster-1
    --sql "select * from test_table;"
    --database dev

Example output:

{
    "ClusterIdentifier": "redshift-cluster-1",
    "CreatedAt": "2022-09-16T12:22:31.894000+05:30",
    "Database": "dev",
    "Id": "458c568d-717b-4f36-90bd-e642bfb06cbf",
    "SecretArn": "arn:aws:secretsmanager:us-east-1:123456789012:secret:myuser-secret-hKgPWn"
}

The Id section in the output displays the statement ID.

Check the status

To check the status of the query, use DescribeStatement and enter the statement ID.

aws redshift-data describe-statement --id 458c568d-717b-4f36-90bd-e642bfb06cbf

Example output:

{
    "ClusterIdentifier": "redshift-cluster-1",
    "CreatedAt": "2022-09-16T12:22:31.894000+05:30",
    "Duration": -1,
    "Error": "ERROR: relation "test_table" does not exist",
    "HasResultSet": false,
    "Id": "458c568d-717b-4f36-90bd-e642bfb06cbf",
    "QueryString": "select * from test_table;",
    "RedshiftPid": 1074727629,
    "RedshiftQueryId": -1,
    "ResultRows": -1,<
    "ResultSize": -1,
    "SecretArn": "arn:aws:secretsmanager:us-east-1:123456789012:secret:myuser-secret-hKgPWn",
    "Status": "FAILED",
    "UpdatedAt": "2022-09-16T12:22:32.365000+05:30"
}

The Error section in the output shows the error: "ERROR: relation "test_table" does not exist".

Multiple queries

Identify the statement ID

To run multiple queries against an Amazon Redshift cluster, first obtain the statement ID.

To get a statement ID, use the BatchExecuteStatement API action. See the following example:

aws redshift-data batch-execute-statement
    --region us-east-1
    --secret-arn arn:aws:secretsmanager:us-east-1:123456789012:secret:myuser-secret-hKgPWn     --cluster-identifier redshift-cluster-1
        --database dev
    --sqls "select * from test_table;" "select * from another_table;"

Example output:

{
    "ClusterIdentifier": "redshift-cluster-1",
    "CreatedAt": "2022-09-16T12:37:16.707000+05:30",
    "Database": "dev",
    "Id": "08b4b917-9faf-498a-964f-e82a5959d1cb",
    "SecretArn": "arn:aws:secretsmanager:us-east-1:123456789012:secret:myuser-secret-hKgPWn"
}

The Id section in the output displays the statement ID.

Check the status

To check the status of the query, use DescribeStatement and enter the statement ID:

aws redshift-data describe-statement --id 08b4b917-9faf-498a-964f-e82a5959d1cb

Example output:

{
    "ClusterIdentifier ": "redshift-cluster-1 ",
    "CreatedAt ": "2022-09-16T12:37:16.707000+05:30 ",
    "Duration ": 0,
    "Error ": "Query #1 failed with ERROR: relation \ "test_table\"
does not exist ",
    "HasResultSet ": false,
    "Id ": "08b4b917-9faf-498a-964f-e82a5959d1cb ",
    "RedshiftPid ": 1074705048,
    "RedshiftQueryId ": 0,
    "ResultRows ":-1,
    "ResultSize ": -1,
    "SecretArn ": "arn:aws:secretsmanager:us-east-1:123456789012:secret:myuser-secret-hKgPWn ",
    "Status ": "FAILED ",
    "SubStatements ": [
        {
            "CreatedAt ": "2022-09-16T12:37:16.905000+05:30 ",
            "Duration ": -1,
            "Error ": "ERROR: relation \ "test_table\" does not exist ",
            "HasResultSet ": false,
            "Id ": "08b4b917-9faf-498a-964f-e82a5959d1cb:1",
            "QueryString ": "select * from test_table; ",
            "RedshiftQueryId ": -1,
            "ResultRows ": -1,
            "ResultSize ": -1,
            "Status ": "FAILED ",
            "UpdatedAt ": "2022-09-16T12:37:17.263000+05:30 "
        },
        {
            "CreatedAt ": "2022-09-16T12:37:16.905000+05:30",
            "Duration ": -1,
            "Error ": "Connection or an prior query failed. ",
            "HasResultSet ": false,
            "Id ": "08b4b917-9faf-498a-964f-e82a5959d1cb:2 ",
            "QueryString ": "select * from another_table;",
            "RedshiftQueryId ": 0,
            "ResultRows ": -1,
            "ResultSize": -1,
            "Status ": "ABORTED ",
            "UpdatedAt ": "2022-09-16T12:37:17.263000+05:30 "
        }
    ],
    "UpdatedAt ": "2022-09-16T12:37:17.288000+05:30 "
}

The output displays the status of each individual SQL statement within your multi-statement query. The Error section in the output displays the error for each statement.

To troubleshoot issues with the Data API, see Troubleshooting issues for Amazon Redshift Data API.

Note: Users with the same AWS Identity and Access Management (IAM) role or IAM permissions as the runner of an ExecuteStatement or BatchExecuteStatement API operation can act on the same statement with DescribeStatement. If another user wants to act on the same SQL statement, then they must assume the IAM role of the user who ran the SQL statement.

For more information, see Considerations when calling the Amazon Redshift Data API.

Monitoring Data API events

Amazon EventBridge monitors Data API events. If you send this information to an AWS Lambda function, then the function connects to Amazon Simple Notification Service (Amazon SNS) to deliver notifications.

For more information, see Building an event-driven application with AWS Lambda and the Amazon Redshift Data API.