Identifier les tables inutilisées dans Snowflake

Access History est une vue du schéma Account Usage de la base de données Snowflake. Elle est disponible sur tous les comptes Snowflake en édition Enterprise ou supérieure. Access History permet de retrouver les objets Snowflake (tables, vues et colonnes) consultés par chaque requête, directement ou indirectement.

Objets directs et objets de base consultés

Pour déterminer quelles colonnes ont été consultées par une requête, deux colonnes sont à examiner : direct_objects_accessed et base_objects_accessed. Une différence essentielle entre les deux tient à la manière dont elles traitent les vues. Prenons la définition de vue suivante :

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

La requête select * from orders_view accède directement à l'objet orders_view, et indirectement à la table de base orders. Logiquement, orders_view apparaîtra dans la colonne direct_objects_accessed de access_history, tandis que orders apparaîtra dans base_objects_accessed.

Pour déterminer si une table est inutilisée, il faut s'appuyer sur base_objects_accessed, car cette colonne tient compte des requêtes qui accèdent indirectement à une table via une vue.

Analyser base_objects_accessed

base_objects_accessed est un tableau JSON regroupant tous les objets de données de base consultés pendant l'exécution d'une requête. Voici un exemple du contenu de cette colonne, tiré de la documentation :

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]

Le tableau des objets consultés par chaque requête peut être transformé en une ligne par objet via lateral flatten, puis filtré pour ne conserver que les objets de type table, comme illustré ci-dessous :

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

Déplier le code

À partir de la version aplatie d'access_history obtenue ci-dessus, nous pouvons déterminer l'heure exacte du dernier accès à une table, ainsi que l'utilisateur ayant exécuté la requête :

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

Déplier le code

Lorsqu'on identifie des tables inutilisées à supprimer, il est utile d'en connaître les coûts de stockage associés. À partir de la vue account usage table_storage_metrics et sur la base d'un tarif de stockage de 23 $ par téraoctet et par mois, on peut calculer le coût annuel de stockage de chaque table :

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

Nous avons vu jusqu'ici comment déterminer la date du dernier accès à une table et les coûts de stockage associés à chacune. Nous pouvons combiner ces briques pour identifier toutes les tables non interrogées depuis 90 jours et estimer les économies annuelles attendues si elles étaient supprimées.

Le SQL ci-dessous s'appuie sur la vue account_usage.access_history, disponible uniquement pour les clients Snowflake en édition Enterprise ou supérieure.

Si vous utilisez dbt, jetez un œil à la version alternative de ce SQL, bien plus rapide à exécuter.

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

Déplier le code

Interroger et aplatir la vue access_history peut s'avérer très lent, étant donné le volume de données à traiter. Pour accélérer les requêtes sur l'historique d'accès aux tables, nous recommandons de matérialiser ces données de façon incrémentale via notre package dbt open-source : dbt_snowflake_monitoring. Une fois le package installé, les requêtes d'identification des tables inutilisées deviennent bien plus simples. Le code précédent peut être réécrit ainsi :

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,

Déplier le code

Avant de décider de supprimer une table, il peut être utile de savoir quand elle a été modifiée pour la dernière fois par une opération DDL ou DML. La requête ci-dessous montre comment retrouver toutes les tables modifiées au cours de la semaine écoulée à l'aide de la vue 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