Schema:

ACCOUNT_USAGE

AGGREGATE_QUERY_HISTORY view¶

This Account Usage view enables you to monitor and track execution of statements over time. It contains similar data to the QUERY_HISTORY view but is aggregated in one-minute intervals for repeated SQL statements. You can use this view to monitor your workload and analyze performance.

In addition to queries against hybrid tables, all queries that you execute in Snowflake are included in AGGREGATE_QUERY_HISTORY. However, AGGREGATE_QUERY_HISTORY is particularly useful for monitoring and analyzing Unistore workloads that execute a small number of distinct statements repeatedly at high throughput.

Columns¶

The OBJECT data type contains the following fields:

QUERY_PARAMETERIZED_HASH column¶

The QUERY_PARAMETERIZED_HASH column contains a hash value that is computed based on the parameterized query, which means the version of the query after parameterizing all literals.

For example, the following queries have the same QUERY_PARAMETERIZED_HASH value:

SELECT * FROM table1 WHERE table1.name = 'TIM'

SELECT * FROM table1 WHERE table1.name = 'AIHUA'

The QUERY_PARAMETERIZED_HASH value has the following restrictions:

• The constant literal must be in the following binary functions on predicates: equal, not equal, greater (or equal) than, smaller (or equal) than.
• The aliases must be the same.

As long as there are difference in the SQL text, the QUERY_HASH and QUERY_PARAMETERIZED_HASH values will be different, with the following exceptions:

• Identifier/session variable/stage name are case insensitive.
• White space differences are ignored.
• Literals satisfying the binary predicate rule mentioned above.

Usage notes¶

Latency for the view may be up to 180 minutes (3 hours).

Examples¶

You can use the AGGREGATE_QUERY_HISTORY view to monitor for potential problems with errors, queueing, lock blocking, or hybrid table throttling. You typically want these metrics to be consistently low. If you see a spike in any of these metrics, it may indicate a problem:

Copy codeExpand code block

SET (START_DATE, END_DATE) = ('2023-11-01', '2023-11-08');

WITH time_issues AS
(
    SELECT
        interval_start_time
        , SUM(transaction_blocked_time:"sum") AS transaction_blocked_time
        , SUM(queued_provisioning_time:"sum") AS queued_provisioning_time
        , SUM(queued_repair_time:"sum") AS queued_repair_time
        , SUM(queued_overload_time:"sum") AS queued_overload_time
        , SUM(hybrid_table_requests_throttled_count) AS hybrid_table_requests_throttled_count
    FROM snowflake.account_usage.aggregate_query_history
    WHERE TRUE
        AND interval_start_time > $START_DATE
        AND interval_start_time < $END_DATE
    GROUP BY ALL
),
errors AS
(
    SELECT
        interval_start_time
        , SUM(value:"count") as error_count
    FROM
    (
        SELECT
            a.interval_start_time
            , e.*
        FROM
            snowflake.account_usage.aggregate_query_history a,
            TABLE(FLATTEN(input => errors)) e
        WHERE TRUE
            AND interval_start_time > $START_DATE
            AND interval_start_time < $END_DATE
    )
    GROUP BY ALL
)
SELECT
    time_issues.interval_start_time
    , error_count
    , transaction_blocked_time
    , queued_provisioning_time
    , queued_repair_time
    , queued_overload_time
    , hybrid_table_requests_throttled_count
FROM
    time_issues FULL JOIN errors ON errors.interval_start_time = time_issues.interval_start_time
;

Show lessSee more

Scroll to top

You can query the view to monitor your overall workload throughput and concurrency. Many workloads have a regular cyclical pattern. Any unexpected spikes or drops may be worth investigating.

For example, monitor throughput and concurrency for warehouse my_warehouse in the first week of November:

SELECT
    interval_start_time
    , SUM(calls) AS execution_count
    , SUM(calls) / 60 AS queries_per_second
    , COUNT(DISTINCT session_id) AS unique_sessions
    , COUNT(user_name) AS unique_users
FROM snowflake.account_usage.aggregate_query_history
WHERE TRUE
    AND warehouse_name = 'MY_WAREHOUSE'
    AND interval_start_time > '2023-11-01'
    AND interval_start_time < '2023-11-08'
GROUP BY
    interval_start_time
;

The most common and heavily repeated queries can be a good place to focus any efforts to optimize or improve the efficiency of your workload. You can query the view to identify top queries for a workload by execution count.

For example, identify the top queries by execution count for warehouse my_warehouse:

SELECT
    query_parameterized_hash
    , ANY_VALUE(query_text)
    , SUM(calls) AS execution_count
FROM snowflake.account_usage.aggregate_query_history
WHERE TRUE
    AND warehouse_name = 'MY_WAREHOUSE'
    AND interval_start_time > '2023-11-01'
    AND interval_start_time < '2023-11-08'
GROUP BY
    query_parameterized_hash
ORDER BY execution_count DESC
;

To identify slowest queries by average total latency:

SELECT
    query_parameterized_hash
    , any_value(query_text)
    , SUM(total_elapsed_time:"sum"::NUMBER) / SUM (calls) as avg_latency
FROM snowflake.account_usage.aggregate_query_history
WHERE TRUE
    AND warehouse_name = 'MY_WAREHOUSE'
    AND interval_start_time > '2023-07-01'
    AND interval_start_time < '2023-07-08'
GROUP BY
    query_parameterized_hash
ORDER BY avg_latency DESC
;

To analyze performance over time for a specific query of interest:

SELECT
    interval_start_time
    , total_elapsed_time:"avg"::number avg_elapsed_time
    , total_elapsed_time:"min"::number min_elapsed_time
    , total_elapsed_time:"p90"::number p90_elapsed_time
    , total_elapsed_time:"p99"::number p99_elapsed_time
    , total_elapsed_time:"max"::number max_elapsed_time
FROM snowflake.account_usage.aggregate_query_history
WHERE TRUE
    AND query_parameterized_hash = '<123456>'
    AND interval_start_time > '2023-07-01'
    AND interval_start_time < '2023-07-08'
ORDER BY interval_start_time DESC
;