QUERY_HISTORY view (Account Usage): Changes to columns and new columns¶
Attention
This behavior change is in the 2024_02 bundle.
For the current status of the bundle, refer to Bundle History.
The QUERY_HISTORY view includes the following new columns and changes to columns:
New columns in QUERY_HISTORY view¶
When this behavior change bundle is enabled, the Account Usage QUERY_HISTORY view includes the following new columns:
Column name |
Data type |
Description |
|---|---|---|
SECONDARY_ROLE_STATS |
VARCHAR |
A JSON-formatted string that contains three fields regarding secondary roles that were evaluated in the query: a list of
secondary roles or |
ROWS_WRITTEN_TO_RESULT |
NUMBER |
Number of rows written to a result object. For CREATE TABLE AS SELECT (CTAS) and all DML operations, this result is For more information, see ROWS_PRODUCED column deprecated |
ROWS_INSERTED |
NUMBER |
Number of rows inserted by the query. |
QUERY_RETRY_TIME |
NUMBER |
Total execution time (in milliseconds) for query retries caused by actionable errors. For more information, see Query retry columns. |
QUERY_RETRY_CAUSE |
VARIANT |
Array of error messages for actionable errors. The array contains one error message for each query retry. If there is no query retry, the array is empty. For more information, see Query retry columns. |
FAULT_HANDLING_TIME |
NUMBER |
Total execution time (in milliseconds) for query retries caused by errors that are not actionable. For more information, see Query retry columns. |
These columns are added as the last (right-most) columns in the view.
For more information, see also Information Schema: New columns in output for QUERY_HISTORY, QUERY_HISTORY_BY_* functions.
Query retry columns¶
A query might need to be retried one or more times in order to successfully complete. There can be multiple causes that result in a query retry. Some of these causes are actionable, that is, a user can make changes to reduce or eliminate query retries for a specific query. For example, if a query is retried due to an out of memory error, modifying warehouse settings might resolve the issue.
Some query retries are caused by a fault tolerance that is not actionable. That is, there is no change a user can make to prevent the query retry. For example, a network outage might result in a query retry. In this case, there is no change to the query or to the warehouse that executes it that can prevent the query retry.
The QUERY_RETRY_TIME, QUERY_RETRY_CAUSE, and FAULT_HANDLING_TIME columns can help you optimize queries that are retried and better understand fluctuations in query performance.
ROWS_PRODUCED column deprecated¶
The ROWS_PRODUCED column will be deprecated in a future release. The value in the ROWS_PRODUCED column doesn’t always reflect the logical number of rows affected by a query. For example, the value in the ROWS_PRODUCED column might include rows that were deleted due to rewriting of micro-partitions and could be larger than the actual number of rows affected. Snowflake recommends using the ROWS_INSERTED, ROWS_UPDATED, ROWS_WRITTEN_TO RESULTS, or ROWS_DELETED columns instead.
Changes to columns in QUERY_HISTORY view¶
The following columns are included in the Account Usage QUERY_HISTORY view:
BYTES_WRITTEN_TO_RESULT
ROWS_INSERTED
The values in the these columns for specific types of queries are as follows:
- Before the change:
BYTES_WRITTEN_TO_RESULT
0for small queries.ROWS_INSERTED:
0for CREATE TABLE AS SELECT (CTAS) queries.- After the change:
BYTES_WRITTEN_TO_RESULT
Number of bytes written to a result object for small queries.
ROWS_INSERTED:
Number of rows inserted for CREATE TABLE AS SELECT (CTAS) queries.
For more information, see also Information Schema: New columns in output for QUERY_HISTORY, QUERY_HISTORY_BY_* functions.
Ref: 1431, 1524, 1540