Terug naar Utils
Snowflake · View
search_optimization_cost
Dagelijkse kosten voor zoekoptimalisatie per tabel, inclusief de rol en gebruiker die het heeft ingeschakeld. enabled_by_role en enabled_by_user worden afgeleid uit de meest recente ALTER TABLE ... ADD SEARCH OPTIMIZATION in ACCESS_HISTORY.
Bron: SNOWFLAKE.ACCOUNT_USAGE.SEARCH_OPTIMIZATION_HISTORY + ACCESS_HISTORY + QUERY_HISTORY · Latentie: ~3h
| Kolom | Beschrijving |
|---|---|
| day | Datum |
| database_name | Database van de geoptimaliseerde tabel |
| schema_name | Schema van de geoptimaliseerde tabel |
| table_name | Tabelnaam |
| status | ACTIVE of DISABLED |
| enabled_by_role | Rol die de ALTER TABLE uitvoerde |
| enabled_by_user | Gebruiker die de ALTER TABLE uitvoerde |
| credits_used | Credits verbruikt die dag |
| maintenance_runs | Aantal onderhoudsjobs die dag |
Broncode
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;