Cronologia Query Snowflake

Introduzione

Monitorare e auditare l’attività del database è una parte cruciale per garantire la sicurezza e l’ottimizzazione di qualsiasi data warehouse. Snowflake è una piattaforma dati cloud molto apprezzata che mette a disposizione strumenti avanzati per ottenere informazioni dettagliate sulle query eseguite nel Suo account. In questo articolo, esploreremo in dettaglio la visualizzazione della cronologia query di Snowflake e le funzioni tabellari QUERY_HISTORY. Scoprirà come utilizzare queste funzionalità per trarre preziose informazioni sull’attività delle query, sulle prestazioni e sui modelli di accesso.

Che Cos’è la View query_history di Snowflake?

La view query_history in Snowflake mostra tutte le query eseguite nel Suo account. Essa è di sola lettura. Le informazioni sono disponibili per i 7 giorni precedenti per la Standard Edition. Per Enterprise Edition e versioni successive, i dati sono disponibili per i 14 giorni precedenti.

Queste informazioni forniscono una descrizione dettagliata di ogni query, inclusi:

Testo della query
Stato di esecuzione (es. completata, fallita, in esecuzione)
Oggetti interrogati (tabelle, viste, ecc.)
Utente che ha eseguito la query
Orario di inizio e di fine della query
Numero di righe restituite
Byte analizzati
E altro ancora

Snowflake popola automaticamente questa view e non richiede alcuna configurazione da parte Sua. Potrà interrogare questa view di audit di accesso a Snowflake proprio come qualsiasi altra view di Snowflake.

Esempio di Interrogazione della query_history

Di seguito è riportato un esempio basilare dell’utilizzo della view query_history. Questa view visualizza le 10 query più recenti eseguite dall’utente corrente.

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

Questa query seleziona il testo SQL, l’orario di inizio e lo stato di esecuzione delle query dalla view query_history. Essa filtra le query eseguite esclusivamente dall’utente corrente, ordina i risultati con le query più recenti in cima e limita l’output a 10 righe.

Le Funzioni Tabellari QUERY_HISTORY

In aggiunta alla view query_history, Snowflake offre un insieme di funzioni tabellari QUERY_HISTORY per accedere ai dati della cronologia delle query. Queste funzioni consentono di recuperare la cronologia delle query anche per un periodo antecedente rispetto ai 7 o 14 giorni di conservazione della view.

Le funzioni QUERY_HISTORY si presentano in tre varianti:

QUERY_HISTORY() – Restituisce la cronologia delle query degli ultimi 14 giorni in una struttura a micro-partizioni per query ottimizzate
QUERY_HISTORY_BY_SESSION() – Restituisce la cronologia delle query degli ultimi 14 giorni con dettagli aggiuntivi a livello di sessione
QUERY_HISTORY_BY_USER() – Restituisce la cronologia delle query degli ultimi 14 giorni con dettagli aggiuntivi a livello utente

Esempio di Utilizzo di QUERY_HISTORY

Supponiamo che desideri individuare i 5 utenti che hanno analizzato il maggior quantitativo di dati negli ultimi 14 giorni. Potrà utilizzare questa funzione tabellare di Snowflake nel seguente modo:

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;

Questa query recupera la cronologia delle query degli ultimi 14 giorni utilizzando la funzione QUERY_HISTORY. Essa somma la colonna bytes_scanned per ciascun utente, ordina i risultati in ordine decrescente in base al totale dei byte analizzati e ne seleziona i 5 principali.

Filtraggio dei Risultati della Cronologia delle Query

La view query_history e le funzioni QUERY_HISTORY supportano una vasta gamma di predicati SQL per filtrare i risultati. Alcuni predicati comuni che potrà utilizzare includono:

query_id – L’identificativo univoco della query
query_type – Il tipo di query (es. SELECT, INSERT, CREATE TABLE)
user_name – Il nome dell’utente che ha eseguito la query
start_time ed end_time – I timestamp in cui la query è iniziata e terminata
execution_status – Lo stato di esecuzione della query (es. RUNNING, COMPLETED, FAILED)
database_name, schema_name, table_name – I nomi degli oggetti del database interrogati dalla query

Ecco un esempio che individua tutte le query fallite che hanno accesso a una specifica tabella nelle ultime 24 ore:

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

Casi d’Uso per la Cronologia delle Query

La funzionalità di cronologia delle query di Snowflake presenta numerosi casi d’uso preziosi, tra cui:

Audit e Sicurezza

Monitorando tutte le query eseguite sul Suo account Snowflake, la view query_history consente di:

Monitorare eventuali attività sospette o accessi non autorizzati
Indagare su eventuali incidenti di sicurezza
Garantire la conformità alle politiche di governance dei dati
Fornire una traccia di audit per i requisiti regolatori

Ottimizzazione delle Query

I dati della cronologia delle query possono aiutarLa a ottimizzare le prestazioni delle query:

Individuando le query che richiedono maggiore impiego di risorse
Analizzando i modelli di esecuzione delle query nel tempo
Rilevando e risolvendo errori o timeout nelle query
Ottimizzando le strategie di indicizzazione, clustering e partizionamento

Accredito e Allocazione dei Costi

I dati relativi ai byte analizzati e al tempo di esecuzione nelle cronologie permettono di:

Attribuire i costi di Snowflake a specifici utenti, team o progetti
Implementare modelli di chargeback o showback
Incentivare l’efficienza delle query e contenere costi eccessivi

Accesso Sicuro alla Cronologia delle Query

Si noti che, per impostazione predefinita, la view query_history e le funzioni QUERY_HISTORY sono accessibili esclusivamente dal ruolo ACCOUNTADMIN. Per concedere l’accesso ad altri ruoli, dovrà utilizzare il comando GRANT IMPORTED PRIVILEGES.

Prestare attenzione nel concedere l’accesso alla cronologia delle query, in quanto essa potrebbe contenere informazioni sensibili. Si raccomanda di creare un ruolo separato con privilegi limitati, appositamente destinato alle funzioni di audit e monitoraggio.

Confronto tra Opzioni di Cronologia delle Query in Snowflake

Sia la view query_history che la funzione QUERY_HISTORY() offrono visibilità sull’attività degli utenti, pur differenziandosi per ambito, periodo di conservazione e flessibilità. Ecco un rapido confronto:

Caratteristica	View `query_history`	Funzione `QUERY_HISTORY()`
Periodo di conservazione	7–14 giorni a seconda dell’edizione	Fino a 1 anno con conservazione estesa
Metodo di accesso	SELECT SQL standard	Funzione tabellare (`TABLE(QUERY_HISTORY())`)
Flessibilità del filtraggio	Limitato ai campi esposti dalla view	Filtraggio avanzato tramite clausola WHERE
Impatto sulle prestazioni	Leggero	Dipende dall’intervallo di date e dai filtri
Casi d’uso	Ricerche rapide degli utenti, cronologia delle sessioni	Analisi storiche, report di audit, monitoraggio dei costi

Audit Sicuro e Centralizzato con DataSunrise

Pur offrendo eccellenti funzionalità di cronologia integrate, Snowflake permette di garantire una maggiore sicurezza e controllo analizzando le query a livello di proxy. Con DataSunrise, Lei può:

Monitorare e loggare in tempo reale tutte le query eseguite in Snowflake
Applicare il Mascheramento Dinamico dei Dati in base al tipo di query, all’utente o all’oggetto
Applicare politiche di sicurezza senza modificare i ruoli di Snowflake
Correlare l’attività delle query con il comportamento degli utenti e le regole di accesso

Questo approccio risulta particolarmente utile in ambienti in cui sono richiesti standard di conformità stringenti, dove la visibilità dell’audit deve estendersi oltre i metadati interni di Snowflake.

Migliore Pratica: Concedere un Accesso Sicuro alla Cronologia delle Query

Invece di concedere l’accesso completo al ruolo ACCOUNTADMIN, crei un ruolo con privilegi limitati per eseguire query di audit storiche:

-- Creare un ruolo di audit in sola lettura
CREATE ROLE audit_viewer;

-- Concedere l'utilizzo sul database SNOWFLAKE
GRANT IMPORTED PRIVILEGES ON DATABASE snowflake TO ROLE audit_viewer;

-- Facoltativamente, assegnare il ruolo agli utenti di monitoraggio
GRANT ROLE audit_viewer TO USER readonly_analyst;

Questa configurazione limita l’accesso pur consentendo una visibilità sicura sulla cronologia delle query. È possibile combinare questa soluzione con il livello di audit di DataSunrise per garantire un monitoraggio a prova di manomissioni.

Riepilogo e Conclusione

La funzionalità di cronologia delle query di Snowflake fornisce una visibilità essenziale sulle query eseguite dagli utenti sul Suo account. Potrà utilizzare la view query_history e le funzioni tabellari QUERY_HISTORY per svariate finalità, tra cui il miglioramento dell’audit, l’ottimizzazione delle prestazioni delle query e il monitoraggio dei costi di utilizzo. Inoltre, questi strumenti offrono ulteriori benefici.

Gli esempi trattati in questo articolo illustrano solo alcune delle molteplici modalità con cui è possibile ottenere preziose informazioni dai dati della cronologia delle query. Con ulteriori approfondimenti, valuti come tali informazioni possano migliorare la sicurezza, l’efficienza e la governance nel Suo ambiente Snowflake.

DataSunrise offre strumenti user-friendly e flessibili per gestire la sicurezza in Snowflake, le regole di audit, il mascheramento dinamico dei dati e la conformità. Visiti il nostro team per una demo online e osservi queste funzionalità in azione!