Identificare le tabelle inutilizzate in Snowflake

Access History è una view nello schema Account Usage dello Snowflake Database. È disponibile per tutti gli account Snowflake con Enterprise Edition o superiore. Access History consente di risalire agli oggetti Snowflake (tabelle, view e colonne) a cui ogni query ha avuto accesso, in modo diretto o indiretto.

Oggetti accessibili: direct vs. base

Per stabilire a quali colonne ha avuto accesso una query, sono due le colonne da osservare: direct_objects_accessed e base_objects_accessed. La differenza fondamentale tra le due riguarda il modo in cui gestiscono le view. Consideriamo la seguente definizione di view:

create or replace view orders_view as (
	select *
	from orders
	where
		not test
		and success
);

La query select * from orders_view accede direttamente all'oggetto orders_view e indirettamente alla tabella base orders. Di conseguenza, orders_view comparirà nella colonna direct_objects_accessed di access_history, mentre orders comparirà in base_objects_accessed.

Per stabilire se una tabella è inutilizzata, è importante affidarsi a base_objects_accessed, perché tiene conto anche delle query che accedono a una tabella indirettamente tramite una view.

Parsing di base_objects_accessed

base_objects_accessed è un array JSON di tutti gli oggetti dati di base a cui si è avuto accesso durante l'esecuzione della query. Ecco un esempio del contenuto della colonna, tratto dalla documentazione:

1[\
\
2  {\
\
3    "columns": [\
\
4      {\
\
5        "columnId": 68610,\
\
6        "columnName": "CONTENT"\
\
7      }\
\
8    ],\
\
9    "objectDomain": "Table",\
\
10    "objectId": 66564,\
\
11    "objectName": "GOVERNANCE.TABLES.T1"\
\
12  }\
\
13]

L'array degli oggetti a cui ha avuto accesso ciascuna query può essere trasformato in una riga per oggetto tramite lateral flatten e poi filtrato in modo da considerare solo gli oggetti di tipo tabella, come mostrato di seguito:

with
access_history as (
    select *
    from snowflake.account_usage.access_history
),
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_accessed.value:objectId::integer as table_id,
        objects_accessed.value:objectName::text as object_name,
        objects_accessed.value:objectDomain::text as object_domain,
        objects_accessed.value:columns as columns_array

Espandi codice

Utilizzando la versione "flattened" di access_history ottenuta dalla query precedente, possiamo determinare il momento esatto dell'ultimo accesso a una tabella e l'utente che ha eseguito la query:

with
access_history as (
    select *
    from snowflake.account_usage.access_history
),
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_accessed.value:objectId::integer as table_id,
        objects_accessed.value:objectName::text as object_name,
        objects_accessed.value:objectDomain::text as object_domain,
        objects_accessed.value:columns as columns_array

Espandi codice

Quando si individuano le tabelle inutilizzate da eliminare, è utile conoscere i relativi costi di storage. Sfruttando la view di account usage table_storage_metrics e ipotizzando una tariffa di 23 $ per terabyte al mese, è possibile calcolare il costo annuo di storage di ciascuna tabella:

select
    id as table_id,
    table_catalog || '.' ||table_schema ||'.' || table_name as fully_qualified_table_name,
    (active_bytes + time_travel_bytes + failsafe_bytes + retained_for_clone_bytes)/power(1024,4) as total_storage_tb,
    -- Assumes a storage rate of $23/TB/month
    -- Update to the appropriate value based on your Snowflake contract
    total_storage_tb*12*23 as annualized_storage_cost
from snowflake.account_usage.table_storage_metrics
where
    not deleted

Finora abbiamo visto come stabilire quando una tabella è stata interrogata per l'ultima volta e i costi di storage associati a ciascuna tabella. Possiamo combinare questi elementi per individuare tutte le tabelle non interrogate negli ultimi 90 giorni e stimare il risparmio annuo che si otterrebbe eliminandole.

Lo SQL riportato di seguito si basa sulla view account_usage.access_history, disponibile solo per i clienti Snowflake con Enterprise Edition o superiore.

Se utilizza dbt, valuti la versione alternativa di questo SQL, decisamente più rapida.

with
access_history as (
    select *
    from snowflake.account_usage.access_history
),
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_accessed.value:objectId::integer as table_id,
        objects_accessed.value:objectName::text as object_name,
        objects_accessed.value:objectDomain::text as object_domain,
        objects_accessed.value:columns as columns_array

Espandi codice

Interrogare e "appiattire" la view access_history può richiedere molto tempo, vista la mole di dati da elaborare. Per query più rapide sulla cronologia degli accessi alle tabelle, consigliamo di materializzare questi dati in modo incrementale tramite il nostro pacchetto dbt open-source: dbt_snowflake_monitoring. Una volta installato il pacchetto, le query per individuare le tabelle inutilizzate diventano molto più semplici. Il codice precedente può essere riscritto così:

with
table_access_summary as (
    select
        table_id,
        max(query_start_time) as last_accessed_at,
        max_by(user_name, query_start_time) as last_accessed_by,
        max_by(query_id, query_start_time) as last_query_id
    from query_base_table_access
    group by 1
),
table_storage_metrics as (
	select
      id as table_id,
      table_catalog || '.' ||table_schema ||'.' || table_name as fully_qualified_table_name,
      (active_bytes + time_travel_bytes + failsafe_bytes + retained_for_clone_bytes)/power(1024,4) as total_storage_tb,

Espandi codice

Nel decidere se eliminare una tabella, può essere utile sapere quando è stata modificata per l'ultima volta da un'operazione DDL o DML. La query seguente mostra come individuare tutte le tabelle aggiornate nell'ultima settimana sfruttando la view di account usage tables:

select
    table_id,
    table_catalog||'.'||table_schema||'.'||table_name as fully_qualified_table_name,
    last_altered as last_altered_at
from snowflake.account_usage.tables
where
    last_altered > current_date - 7