[Snowflake] Error: "java.sql.SQLException: Status of query associated with resultSet is FAILED_WITH_ERROR"

Difficulty Debugging Errors for Snowflake Connectors

When an Ascend component for Snowflake fails with FAILED_WITH_ERROR, it is difficult to debug. This is a known problem with the Snowflake data source for Apache Spark. The Snowflake data source for Apache Spark does not provide the detailed error message as part of the error's stack trace.

The library internally uses the Snowflake JDBC driver. The Snowflake JDBC driver can potentially provide detailed error messages. However, a similar Github issue for the Snowflake JDBC driver is currently unresolved.

The root cause error message is only visible from the Snowflake platform.

Error

An Ascend component associated with Snowflake fails with the following error (excerpt from larger stack trace):

java.sql.SQLException: Status of query associated with resultSet is FAILED_WITH_ERROR.

This error is known to occur in various Ascend components but most often occurs with the Ascend Snowflake Write Connector.

Definition

Documentation for the Snowflake JDBC driver indicates this error means the query finished unsuccessfully.

Solution

Identify the UTC timestamp when the error occurred in the Ascend platform from the partitions tab or related logs. Next, use the UTC timestamp to locate corresponding logs from the Snowflake platform to find the root cause error message.

The actual error can be found in the Snowflake Query History. For example, insert errors for table my_table_operation:

SELECT * FROM table(information_schema.query_history()) AS H WHERE 1=1 AND H.QUERY_TEXT LIKE 'insert into my_table_operation%' AND H.ERROR_MESSAGE IS NOT NULL ORDER BY H.START_TIME DESC;