USERS and QUERY_HISTORY views (ACCOUNT_USAGE) and QUERY_HISTORY function: New columns¶

When this behavior change bundle is enabled, the USERS and QUERY_HISTORY views and the output of the QUERY_HISTORY function include the following new columns.

USERS view (ACCOUNT_USAGE)¶

When the user TYPE is SNOWFLAKE_SERVICE, indicating it is a service user, the following new columns provide the service’s database and schema information. Otherwise, these columns are null.

QUERY_HISTORY view (ACCOUNT_USAGE)¶

When the QUERY_HISTORY USER_TYPE is SNOWFLAKE_SERVICE, indicating the query is executed by a Snowpark Container Services service, the other columns provide the service’s database and schema information.

QUERY_HISTORY function¶

The following new columns are added to the output (see QUERY_HISTORY function). They identify the user type (USER_TYPE). When the USER_TYPE is SNOWFLAKE_SERVICE, the other columns identify the service’s database and schema.

Examples¶

The service’s database and schema information, along with the user name (which, for the SNOWFLAKE_SERVICE user type, is also the service name) can help during debugging. For example, you can write a query to retrieve information about the service that executed the queries. The following are some example queries:

Example 1: Fetch queries run by a service.

SELECT query_history.*
FROM snowflake.account_usage.query_history
WHERE user_type = 'SNOWFLAKE_SERVICE'
AND user_name = '<service-name>'
AND user_database_name = '<service-db-name>'
AND user_schema_name = '<service-schema-name>'
order by start_time;

In the WHERE clause:

• user_name = '<service-name>' because a service executes queries as the service user, and the service user’s name is the same as the service name.
• user_type = 'SNOWFLAKE_SERVICE' and user_name = '<service-name>' retrieve only queries executed by a service.
• user_database_name and user_schema_name names, for a service user, are the service’s database and schema.

You can get the same results by querying the information_schema.query_history function.

SELECT *
FROM TABLE(<any-user-db-name>.information_schema.query_history())
WHERE user_database_name = '<service-db-name>'
AND user_schema_name = '<service-schema-name>'
AND user_type = 'SNOWFLAKE_SERVICE'
AND user_name = '<service-name>'
order by start_time;

In the WHERE clause:

• user_type = 'SNOWFLAKE_SERVICE' and user_name = '<service-name>' retrieves only queries executed by a service.
• user_database_name and user_schema_name names, for a service user, are the service’s database and schema.

Example 2: Fetch queries run by services and corresponding service information.

SELECT query_history.*, services.*
FROM snowflake.account_usage.query_history
JOIN snowflake.account_usage.services
ON query_history.user_name = services.service_name
AND query_history.user_schema_id = services.service_schema_id
AND query_history.user_type = 'SNOWFLAKE_SERVICE'

The query joins the QUERY_HISTORY and SERVICES views to retrieve information about the queries and services that executed the queries. Note the following:

• For queries run by services, the query_history.user_name is the service user’s name, which is the same as the service name.
• The query joins the views using the schema IDs (not schema name) to ensure you refer to the same schema, because if you drop and recreate a schema, the schema ID changes but the name remains the same.

You can add optional filters to the query. For example:

• Filter query_history to retrieve only services that executed specific queries.
• Filter services to retrieve only queries executed by specific services.

Example 3: For every service, fetch service user information.

SELECT services.*, users.*
FROM snowflake.account_usage.users
JOIN snowflake.account_usage.services
ON users.name = services.service_name
AND users.schema_id = services.service_schema_id
AND users.type = 'SNOWFLAKE_SERVICE'

The query join SERVICES and USERS views in the ACCOUNT_USAGE schema to retrieve services and service user information. Note the following:

• When a service runs queries, it runs the queries as service user and the service user’s name is the same as the service name. Therefore, you specify the join condition: users.name = services.service_name.
• Service names are unique only within a schema. Therefore, the query specifies the join condition (users.schema_id = services.service_schema_id) to ensure each service user is matched against the specific service they belong to (and not any other same-named service running in different schemas).

Ref: 1771