Zurück zu Utils
Snowflake · View
search_optimization_cost
Täglicher Suchoptimierungs-Credit-Verbrauch pro Tabelle, mit der Rolle und dem Benutzer, der ihn aktiviert hat. enabled_by_role und enabled_by_user werden aus dem jüngsten ALTER TABLE ... ADD SEARCH OPTIMIZATION in ACCESS_HISTORY abgeleitet.
Quelle: SNOWFLAKE.ACCOUNT_USAGE.SEARCH_OPTIMIZATION_HISTORY + ACCESS_HISTORY + QUERY_HISTORY · Latenz: ~3h
| Spalte | Beschreibung |
|---|---|
| day | Datum |
| database_name | Datenbank der optimierten Tabelle |
| schema_name | Schema der optimierten Tabelle |
| table_name | Tabellenname |
| status | ACTIVE oder DISABLED |
| enabled_by_role | Rolle, die das aktivierende ALTER TABLE ausgeführt hat |
| enabled_by_user | Benutzer, der das aktivierende ALTER TABLE ausgeführt hat |
| credits_used | Credits verbraucht an diesem Tag |
| maintenance_runs | Anzahl der Wartungsjobs an diesem Tag |
Quellcode
create_admin_views.sql
create or replace view search_optimization_cost
comment = 'Daily search optimization credit consumption per table, including the role and user that enabled it (source: SNOWFLAKE.ACCOUNT_USAGE)'
as
with enabler as (
select split_part(replace(om.value:objectName::string, '"', ''), '.', 1) as database_name
, split_part(replace(om.value:objectName::string, '"', ''), '.', 2) as schema_name
, split_part(replace(om.value:objectName::string, '"', ''), '.', 3) as table_name
, qh.role_name
, ah.user_name
from snowflake.account_usage.access_history ah
join snowflake.account_usage.query_history qh
on qh.query_id = ah.query_id
, lateral flatten(ah.objects_modified) om
where om.value:objectDomain::string ilike 'Table'
and qh.query_type = 'ALTER'
and qh.query_text ilike '%search optimization%'
qualify row_number() over (partition by database_name
, schema_name
, table_name
order by qh.start_time desc) = 1)
select date_trunc('day', h.start_time)::date as day
, h.database_name
, h.schema_name
, h.table_name
, h.status
, e.role_name as enabled_by_role
, e.user_name as enabled_by_user
, sum(h.credits_used) as credits_used
, count(*) as maintenance_runs
from snowflake.account_usage.search_optimization_history h
left join enabler e
on upper(e.database_name) = upper(h.database_name)
and upper(e.schema_name) = upper(h.schema_name)
and upper(e.table_name) = upper(h.table_name)
group by 1, 2, 3, 4, 5, 6, 7;