USERS 和 QUERY_HISTORY 视图 (ACCOUNT_USAGE) 及 QUERY_HISTORY 函数：新列¶

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 视图 (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 视图 (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 函数¶

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.

示例

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:

示例 1： 提取一项服务运行的查询。

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;

在 WHERE 子句中：

• 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.

通过查询 information_schema.query_history 函数，您可以获得同样的结果。

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;

在 WHERE 子句中：

• 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.

示例 2： 获取多项服务运行的查询和相应的服务信息。

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.
• 该查询使用架构 IDs（而非架构名称）联接视图，以确保您引用相同的架构，因为如果删除并重新创建架构，架构 ID 会发生变化，但名称保持不变。

您可以向查询添加可选的筛选器。例如：

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

示例 3： 获取每项服务的服务用户信息。

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'

查询在 ACCOUNT_USAGE 架构中联接 SERVICES 和 USERS 视图，以检索服务和服务用户信息。请注意以下事项：

• 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).

参考：1771