Historique des requêtes Snowflake

Introduction

Le suivi et l’audit de l’activité de la base de données constituent une partie essentielle de la sécurisation et de l’optimisation de tout entrepôt de données. Snowflake est une plateforme de données cloud très appréciée qui offre des outils puissants pour obtenir des informations détaillées sur les requêtes de votre compte. Dans cet article, nous explorerons en détail la vue de l’historique des requêtes Snowflake ainsi que les fonctions de table QUERY_HISTORY. Vous apprendrez comment utiliser ces fonctionnalités pour obtenir des informations précieuses sur l’activité des requêtes, la performance et les modèles d’accès.

Qu’est-ce que la vue query_history de Snowflake ?

La vue query_history dans Snowflake affiche toutes les requêtes exécutées dans votre compte. Cette vue est en lecture seule. Les informations sont disponibles pour les 7 derniers jours pour l’édition Standard. Pour l’édition Enterprise et supérieure, elles sont disponibles pour les 14 derniers jours.

Ces informations sont disponibles pour les 7 derniers jours pour l’édition Standard, et pour les 14 derniers jours pour l’édition Enterprise et supérieure. Elle fournit des informations détaillées sur chaque requête, y compris :

Le texte de la requête
Le statut d’exécution (par exemple, complétée, échouée, en cours d’exécution)
Les objets interrogés (tables, vues, etc.)
L’utilisateur ayant exécuté la requête
L’heure de début et de fin de la requête
Le nombre de lignes retournées
Les octets analysés
Et bien plus encore

Snowflake remplit automatiquement cette vue et ne nécessite aucune configuration ou mise en place de votre part. Vous pouvez interroger cette vue d’audit des accès Snowflake comme n’importe quelle autre vue Snowflake.

Exemple d’interrogation de query_history

Voici un exemple basique d’utilisation de la vue query_history. Cette vue affiche les 10 requêtes les plus récentes exécutées par l’utilisateur courant.

SELECT query_text, start_time, execution_status 
FROM query_history
WHERE user_name = current_user()
ORDER BY start_time DESC
LIMIT 10;

Cette requête sélectionne le texte SQL, l’heure de début et le statut d’exécution des requêtes depuis la vue query_history. Elle filtre pour ne récupérer que les requêtes exécutées par l’utilisateur courant, trie les résultats avec les requêtes les plus récentes en premier et limite la sortie à 10 lignes.

Les fonctions de table QUERY_HISTORY

En plus de la vue query_history, Snowflake fournit un ensemble de fonctions de table pour accéder aux données de l’historique des requêtes. Ces fonctions vous permettent de récupérer l’historique des requêtes sur une période plus longue que la période de rétention de 7 ou 14 jours de la vue.

Les fonctions QUERY_HISTORY se déclinent en trois variantes :

QUERY_HISTORY() – Retourne l’historique des requêtes sur 14 jours sous forme de micro-partitions pour des requêtes optimisées
QUERY_HISTORY_BY_SESSION() – Retourne l’historique des requêtes sur 14 jours avec des détails supplémentaires au niveau de la session
QUERY_HISTORY_BY_USER() – Retourne l’historique des requêtes sur 14 jours avec des détails supplémentaires au niveau de l’utilisateur

Exemple d’utilisation de QUERY_HISTORY

Supposons que vous souhaitiez trouver les 5 utilisateurs ayant scanné le plus de données au cours des 14 derniers jours. Vous pourriez utiliser cette fonction de table Snowflake comme suit :

SELECT user_name, sum(bytes_scanned) as total_bytes_scanned
FROM TABLE(QUERY_HISTORY())
WHERE start_time >= dateadd('day', -14, current_timestamp())  
GROUP BY user_name
ORDER BY total_bytes_scanned DESC
LIMIT 5;

Cette requête récupère l’historique des requêtes pour les 14 derniers jours en utilisant la fonction QUERY_HISTORY. Elle additionne la colonne bytes_scanned pour chaque utilisateur, trie les résultats par ordre décroissant du total d’octets scannés et sélectionne les 5 meilleurs utilisateurs.

Filtrer les résultats de l’historique des requêtes

La vue query_history et les fonctions QUERY_HISTORY supportent une large gamme de prédicats SQL pour filtrer les résultats. Quelques prédicats courants que vous pourriez utiliser incluent :

query_id – L’identifiant unique d’une requête
query_type – Le type de requête (par exemple, SELECT, INSERT, CREATE TABLE)
user_name – Le nom de l’utilisateur qui a exécuté la requête
start_time et end_time – Les timestamps indiquant le début et la fin de la requête
execution_status – Le statut de la requête (par exemple, RUNNING, COMPLETED, FAILED)
database_name, schema_name, table_name – Les noms des objets de la base de données accédés par la requête

Voici un exemple qui trouve toutes les requêtes échouées ayant accédé à une table spécifique au cours de la dernière journée :

SELECT * 
FROM query_history
WHERE execution_status = 'FAILED'
 AND table_name = 'my_table'
 AND start_time >= dateadd('day', -1, current_timestamp());

Cas d’utilisation de l’historique des requêtes

La fonctionnalité d’historique des requêtes de Snowflake présente de nombreux cas d’utilisation précieux, notamment :

Audit et sécurité

En suivant toutes les requêtes exécutées sur votre compte Snowflake, query_history vous permet de :

Surveiller les activités suspectes ou les accès non autorisés
Enquêter sur les incidents de sécurité
Garantir la conformité avec les politiques de gouvernance des données
Fournir une piste d’audit pour répondre aux exigences réglementaires

Optimisation des requêtes

Les données de l’historique des requêtes peuvent vous aider à optimiser les performances des requêtes en :

Identifiant les requêtes les plus gourmandes en ressources
Analysant les tendances des requêtes dans le temps
Détectant et résolvant les erreurs ou les délais d’attente des requêtes
Optimisant les stratégies d’indexation, de clustering et de partitionnement

Rétrofacturation et allocation des coûts

Les données sur les octets scannés et la durée d’exécution dans l’historique des requêtes vous permettent de :

Attribuer les coûts Snowflake à des utilisateurs, des équipes ou des projets spécifiques
Mettre en œuvre des modèles de rétrofacturation ou de visibilité des coûts
Encourager l’efficacité des requêtes et limiter les coûts excessifs

Accéder à l’historique des requêtes de manière sécurisée

Notez que, par défaut, la vue query_history et les fonctions QUERY_HISTORY ne sont accessibles que par le rôle ACCOUNTADMIN. Pour accorder l’accès à d’autres rôles, vous devrez utiliser la commande GRANT IMPORTED PRIVILEGES.

Faites preuve de prudence lors de l’octroi de l’accès à l’historique des requêtes, car celui-ci peut contenir des informations sensibles. Envisagez de créer un rôle séparé avec des privilèges limités spécifiquement pour l’audit et la surveillance.

Comparaison des options d’historique des requêtes dans Snowflake

La vue query_history et la fonction QUERY_HISTORY() vous donnent toutes deux un aperçu de l’activité des utilisateurs, mais elles diffèrent par leur portée, leur période de rétention et leur flexibilité. Voici une comparaison rapide :

Fonctionnalité	Vue `query_history`	Fonction `QUERY_HISTORY()`
Période de rétention	7 à 14 jours selon l’édition	Jusqu’à 1 an avec une rétention étendue
Méthode d’accès	SELECT SQL standard	Fonction de table (`TABLE(QUERY_HISTORY())`)
Flexibilité du filtrage	Limitée aux champs exposés par la vue	Filtrage riche via la clause WHERE
Impact sur les performances	Léger	Dépend de la plage de dates et des filtres
Cas d’utilisation	Recherche rapide d’utilisateurs, historique de sessions	Analyses historiques, rapports d’audit, suivi des coûts

Audit sécurisé et centralisé avec DataSunrise

Alors que Snowflake fournit d’excellentes fonctionnalités d’historique intégrées, sécuriser et analyser les requêtes au niveau du proxy offre bien plus de contrôle. Avec DataSunrise, vous pouvez :

Surveiller et enregistrer en temps réel toutes les requêtes sur Snowflake
Appliquer un masquage dynamique selon le type de requête, l’utilisateur ou l’objet
Appliquer des politiques de sécurité sans modifier les rôles Snowflake
Corréler l’activité des requêtes avec le comportement des utilisateurs et les règles d’accès

Cela est particulièrement utile dans les environnements soumis à des exigences de conformité strictes, où la visibilité de l’audit doit aller au-delà des métadonnées internes de Snowflake.

Bonne pratique : Accorder un accès sécurisé à l’historique des requêtes

Plutôt que d’accorder un accès complet au rôle ACCOUNTADMIN, créez un rôle avec des privilèges limités pour exécuter des requêtes d’audit historiques :

-- Créer un rôle d'audit en lecture seule
CREATE ROLE audit_viewer;

-- Accorder l'utilisation de la base de données SNOWFLAKE
GRANT IMPORTED PRIVILEGES ON DATABASE snowflake TO ROLE audit_viewer;

-- Facultativement, assigner ce rôle aux utilisateurs en charge de la surveillance
GRANT ROLE audit_viewer TO USER readonly_analyst;

Cette configuration limite l’accès tout en permettant une visibilité sécurisée sur votre historique des requêtes. Combinez-la avec la couche d’audit de DataSunrise pour garantir une surveillance inviolable.

Résumé et conclusion

La fonctionnalité d’historique des requêtes de Snowflake offre une visibilité essentielle sur les requêtes que les utilisateurs exécutent sur votre compte. Vous pouvez utiliser la vue query_history et les fonctions de table QUERY_HISTORY pour diverses finalités. Cela inclut l’amélioration de l’audit, l’optimisation des performances des requêtes et le suivi des coûts d’utilisation. De plus, ces outils offrent d’autres avantages supplémentaires.

Les exemples présentés dans cet article illustrent seulement quelques-unes des nombreuses façons dont vous pouvez tirer parti des données de l’historique des requêtes. En approfondissant le sujet, pensez à comment ces informations pourraient améliorer la sécurité, l’efficacité et la gouvernance au sein de votre propre environnement Snowflake.

DataSunrise propose des outils conviviaux et flexibles pour gérer la sécurité de Snowflake, les règles d’audit, le masquage dynamique des données et la conformité. Visitez notre équipe pour une démonstration en ligne afin de voir ces capacités en action !