Query and Task History views and functions: New columns¶

Attention

This behavior change is in the 2023_06 bundle.

For the current status of the bundle, refer to Bundle History.

In the current Snowflake release, the output of query and task history views and functions include new columns. The views and functions that are affected include:

The following Account Usage views:
The following Information Schema table functions:
- QUERY_HISTORY
- TASK_HISTORY

The output of these views and functions includes the following new columns:

Column Name	Data Type	Description
`query_hash`	TEXT	Hash value that is computed based on the canonicalized text of the SQL statement.
`query_hash_version`	NUMBER	The version of the hash in the `query_hash` column.
`query_parameterized_hash`	TEXT	Hash value of the query text after literals are parameterized
`query_parameterized_hash_version`	NUMBER	The version of the hash in the `query_parameterized_hash` column.

If you previously defined a view that selects all columns (SELECT *) from any of these views, querying the view returns an error. You must recreate your view by using the CREATE OR REPLACE VIEW command.

For example, suppose that you defined a view that selected all columns from the TASK_HISTORY view:

CREATE OR REPLACE VIEW my_task_history
  AS SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.TASK_HISTORY;

Copy

Previously:

Querying your view (my_task_history in this example) returns the results from the view.

Currently:

Querying your view (my_task_history in this example) returns an error about the number of columns in the view:

View definition for MY_DB.MY_SCHEMA.MY_TASK_HISTORY' declared 22 column(s),
but view query produces 27 column(s).

As noted in the Usage Notes for CREATE VIEW, if a view selects all columns from an underlying table or view, the view is not updated automatically when a new column is added to the underlying table or view. Querying the view returns a column-related error.

To prevent this error, you must recreate the view. For example, to recreate the view in the example above with specific columns selected (to avoid problems in the future due to columns being added):

CREATE OR REPLACE VIEW my_task_history
  AS SELECT query_text, completed_time FROM SNOWFLAKE.ACCOUNT_USAGE.TASK_HISTORY;

Copy

In addition, if you created a table that has the same columns as one of these views (for example, by using CREATE TABLE … LIKE SNOWFLAKE.ACCOUNT_USAGE.TASK_HISTORY), and you are copying rows from that view to the table, you must add the new columns to your table. Use the ALTER TABLE … ADD COLUMN command to add the same columns to your table that were added to the view.

Ref: 1147