Example Labels
These are example labels that make use of conditionals on columns in the enriched query history table
(REPORTING.ENRICHED_QUERY_HISTORY). This is SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY augmented with computed cost.
| Condition | Effect |
|---|---|
| BYTES_SPILLED_TO_LOCAL_STORAGE > 0 OR BYTES_SPILLED_TO_REMOTE_STORAGE > 0 | All queries that spilled to disk |
| COMPILATION_TIME > 1000 | Queries that took a long time to compile |
| QUERY_ACCELERATION_BYTES_SCANNED > 0 | Queries that were accelerated |
| COST = 0 OR COST IS NULL | Free queries |
| COST > 1 | Queries that cost more than $1 |
| QUERY_TYPE = ‘CALL’ | Stored Procedures |
| query_type in (‘CREATE_TABLE_AS_SELECT’. ‘INSERT’) | CTAS or Insert queries |
| coalesce(partitions_scanned, 0)/coalesce(partitions_total, 1) > 0.95 | Queries that read 95% of the input tables (ie full table scans) |
We have populated two columns in ENRICHED_QUERY_HISTORY with qtag information. The first is called qtag and it is
the direct output of the qtag udf. The second is called qtag_filter and this is a transformed
version of the qtag that makes it more amenable to writing conditions against. Here are some examples:
| Condition | Effect |
|---|---|
| tools.qtag(qtag_filter, ‘dbt’, ‘dbt_version’, ‘1.2’) | Exactly match a qtag source, key and value. This example will find all dbt queries run with version 1.2 |
| tools.qtag_exists(qtag_filter, ‘dbt’, ‘dbt_version’) | Find all qtag-ed queries with the given source and key, regardless of value |
| tools.qtag_matches(qtag_filter, ‘dbt’, dbt_version’, ‘1..*’) | Regex match against the value of the given qtag. This uses rlike underneath and carries all the same conditions/parameters |
You can see all the functions live in the TOOLS schema. There are lots more there, run show functions like 'QTAG%';
to see them all.
We have embedded the sqlglot library into the OpsCenter app. This means you can
use it to extract semantic information from the SQL text in ENRICHED_QUERY_HISTORY with a python UDF. Currently we
only expose one function to return a list
of fully qualified tables but it is very easy to add your own either directly or by contributing on github.
An example condition to check if LINEITEMS from TPCH is being used in a query would be:
tools.table_contains(query_text, 'SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.LINEITEMS').
If you have set up notifications you will also get access to QLIKE
functions. See this page for an intro into qlike and some examples.
To use QLIKE in label conditions call the function in TOOLS similar to sqlglot or qtag: tools.qlike(query_text, 'select ? from ?').