Ungenutzte Tabellen in Snowflake identifizieren

Access History ist eine View im Schema Account Usage der Snowflake-Datenbank. Sie steht allen Snowflake-Accounts ab der Enterprise Edition zur Verfügung. Über Access History lässt sich nachvollziehen, auf welche Snowflake-Objekte (also Tabellen, Views und Spalten) eine Query direkt oder indirekt zugegriffen hat.

Direct- versus Base-Objects-Accessed

Um zu ermitteln, auf welche Spalten eine Query zugegriffen hat, sind zwei Spalten interessant: direct_objects_accessed und base_objects_accessed. Der entscheidende Unterschied liegt im Umgang mit Views. Sehen Sie sich folgende View-Definition an:

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

Die Query select * from orders_view greift direkt auf das Objekt orders_view zu und indirekt auf die zugrunde liegende Base-Tabelle orders. Entsprechend taucht orders_view in der Spalte direct_objects_accessed von access_history auf, während orders in base_objects_accessed erscheint.

Wenn es darum geht, ob eine Tabelle ungenutzt ist, sollten Sie base_objects_accessed heranziehen – denn nur so werden auch Queries erfasst, die indirekt über eine View auf die Tabelle zugreifen.

base_objects_accessed parsen

base_objects_accessed ist ein JSON-Array aller Basisdatenobjekte, auf die während der Query-Ausführung zugegriffen wurde. Hier ein Beispiel für den Spalteninhalt aus der Dokumentation:

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]

Das Array der pro Query angesprochenen Objekte lässt sich per lateral flatten in eine Zeile pro Objekt überführen und anschließend so filtern, dass nur Tabellenobjekte übrig bleiben – wie hier gezeigt:

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

Code anzeigen

Mit der "geflatteten" access_history aus der obigen Query lassen sich der genaue Zeitpunkt des letzten Zugriffs auf eine Tabelle und der ausführende Benutzer ermitteln:

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

Code anzeigen

Wenn Sie ungenutzte Tabellen zum Löschen identifizieren, lohnt sich ein Blick auf die zugehörigen Speicherkosten. Mit der Account-Usage-View table_storage_metrics und einem angenommenen Speicherpreis von 23 $ pro Terabyte und Monat lassen sich die jährlichen Speicherkosten je Tabelle berechnen:

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

Bisher haben wir gezeigt, wie sich der letzte Zugriff auf eine Tabelle ermitteln lässt und welche Speicherkosten jeder Tabelle zuzuordnen sind. Diese Bausteine fügen wir nun zusammen, um alle Tabellen aufzuspüren, die in den letzten 90 Tagen nicht abgefragt wurden – samt der jährlichen Einsparung, die bei einer Löschung zu erwarten wäre.

Das folgende SQL nutzt die View account_usage.access_history, die nur Snowflake-Kunden ab der Enterprise Edition zur Verfügung steht.

Wenn Sie dbt einsetzen, werfen Sie einen Blick auf die alternative Variante dieses SQL – sie läuft deutlich schneller.

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

Code anzeigen

Die View access_history abzufragen und zu flatten kann aufgrund der Datenmengen sehr langsam werden. Für schnellere Abfragen zum Tabellenzugriffsverlauf empfehlen wir, diese Daten inkrementell mit unserem Open-Source-dbt-Package zu materialisieren: dbt_snowflake_monitoring. Nach der Installation des Packages werden Abfragen zur Identifikation ungenutzter Tabellen deutlich einfacher. Der Code von oben lässt sich dann so umschreiben:

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,

Code anzeigen

Bei der Entscheidung, ob eine Tabelle gelöscht werden kann, hilft es zu wissen, wann sie zuletzt durch eine DDL- oder DML-Operation verändert wurde. Die folgende Query zeigt anhand der Account-Usage-View tables, wie sich alle Tabellen finden lassen, die in der vergangenen Woche aktualisiert wurden:

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