Terug naar Utils
Snowflake · View
clustering_cost
Dagelijkse automatische clusteringkosten per tabel, inclusief de rol en gebruiker die clustering heeft ingeschakeld. enabled_by_role en enabled_by_user worden afgeleid uit de meest recente ALTER TABLE ... CLUSTER BY in ACCESS_HISTORY.
Bron: SNOWFLAKE.ACCOUNT_USAGE.AUTOMATIC_CLUSTERING_HISTORY + ACCESS_HISTORY + QUERY_HISTORY · Latentie: ~3h
| Kolom | Beschrijving |
|---|---|
| day | Datum |
| database_name | Database van de geclusterde tabel |
| schema_name | Schema van de geclusterde tabel |
| table_name | Tabelnaam |
| enabled_by_role | Rol die CLUSTER BY heeft uitgevoerd |
| enabled_by_user | Gebruiker die CLUSTER BY heeft uitgevoerd |
| credits_used | Credits verbruikt die dag |
| bytes_reclustered | Bytes herschreven tijdens clustering |
| rows_reclustered | Rijen herschreven tijdens clustering |
| clustering_runs | Aantal clusteringjobs die dag |
Broncode
create_admin_views.sql
create or replace view clustering_cost
comment = 'Daily automatic clustering 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 '%cluster by%'
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
, e.role_name as enabled_by_role
, e.user_name as enabled_by_user
, sum(h.credits_used) as credits_used
, sum(h.num_bytes_reclustered) as bytes_reclustered
, sum(h.num_rows_reclustered) as rows_reclustered
, count(*) as clustering_runs
from snowflake.account_usage.automatic_clustering_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;