Historial de Consultas de Snowflake

Introducción

El seguimiento y auditoría de la actividad de la base de datos es una parte crucial para asegurar y optimizar cualquier almacén de datos. Snowflake es una plataforma de datos en la nube muy popular que proporciona herramientas poderosas para obtener información detallada sobre las consultas en tu cuenta. En este artículo, exploraremos la vista del historial de consultas de Snowflake y las funciones de la tabla QUERY_HISTORY en detalle. Aprenderás cómo usar estas funciones para obtener valiosos insights sobre la actividad de consultas, el rendimiento y los patrones de acceso.

¿Qué es la vista query_history de Snowflake?

La vista query_history en Snowflake muestra todas las consultas ejecutadas en tu cuenta. Es de solo lectura. La información está disponible durante los últimos 7 días para la Edición Estándar. Para la Edición Enterprise y superiores, está disponible durante los últimos 14 días.

Esta información está disponible durante los últimos 7 días para la Edición Estándar, y 14 días para la Edición Enterprise y superiores. Proporciona información detallada sobre cada consulta, incluyendo:

Texto de la consulta
Estado de ejecución (por ejemplo, completada, fallida, en ejecución)
Objetos consultados (tablas, vistas, etc.)
Usuario que ejecutó la consulta
Hora de inicio y fin de la consulta
Número de filas devueltas
Bytes escaneados
Y más

Snowflake automáticamente rellena esta vista y no requiere ninguna configuración o preparación de tu parte. Puedes consultar esta vista de auditoría de acceso de Snowflake como cualquier otra vista de Snowflake.

Ejemplo de consulta en query_history

Aquí hay un ejemplo básico de cómo usar la vista query_history. Esta vista muestra las 10 consultas más recientes ejecutadas por el usuario actual.

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

Esta consulta selecciona el texto SQL, la hora de inicio y el estado de ejecución de las consultas de la vista query_history. Filtra solo aquellas consultas ejecutadas por el usuario actual, ordena los resultados con las consultas más recientes primero y limita la salida a 10 filas.

Las funciones de la tabla QUERY_HISTORY

Además de la vista query_history, Snowflake proporciona un conjunto de funciones de tabla para acceder a los datos del historial de consultas. Estas funciones te permiten recuperar el historial de consultas desde más atrás en el tiempo que el período de retención de 7 o 14 días de la vista.

Las funciones QUERY_HISTORY vienen en tres variantes:

QUERY_HISTORY() – Retorna el historial de consultas de 14 días en una estructura de micro-partición para consultas optimizadas
QUERY_HISTORY_BY_SESSION() – Retorna el historial de consultas de 14 días con detalles adicionales a nivel de sesión
QUERY_HISTORY_BY_USER() – Retorna el historial de consultas de 14 días con detalles adicionales a nivel de usuario

Ejemplo de uso de QUERY_HISTORY

Supongamos que quieres encontrar los 5 usuarios que más datos han escaneado en los últimos 14 días. Podrías usar esta función de tabla de Snowflake como sigue:

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;

Esta consulta recupera el historial de consultas de los últimos 14 días usando la función QUERY_HISTORY. Suma la columna bytes_scanned para cada usuario, ordena los resultados en orden descendente por total de bytes escaneados y toma los 5 primeros usuarios.

Filtrando los resultados del historial de consultas

La vista query_history y las funciones QUERY_HISTORY soportan una amplia gama de predicados SQL para filtrar los resultados. Algunos predicados comunes que podrías usar incluyen:

query_id – El identificador único de una consulta
query_type – El tipo de consulta (por ejemplo, SELECT, INSERT, CREATE TABLE)
user_name – El nombre del usuario que ejecutó la consulta
start_time y end_time – Las marcas de tiempo cuando la consulta comenzó y terminó
execution_status – El estado de la consulta (por ejemplo, RUNNING, COMPLETED, FAILED)
database_name, schema_name, table_name – Los nombres de los objetos de la base de datos accesados por la consulta

A continuación, un ejemplo que encuentra todas las consultas fallidas que accedieron a una tabla específica en el último día:

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

Casos de uso del historial de consultas

La función de historial de consultas de Snowflake tiene muchos casos de uso valiosos, incluyendo:

Auditoría y Seguridad

Al rastrear todas las consultas ejecutadas contra tu cuenta de Snowflake, query_history te permite:

Monitorizar actividades sospechosas o accesos no autorizados
Investigar incidentes de seguridad
Asegurar el cumplimiento con las políticas de gobernanza de datos
Proporcionar un rastro de auditoría para requisitos regulatorios

Optimización de Consultas

Los datos del historial de consultas pueden ayudarte a optimizar el rendimiento de las consultas al:

Identificar las consultas más intensivas en recursos
Analizar los patrones de consultas a lo largo del tiempo
Detectar y solucionar errores de consultas o tiempos de espera
Ajustar estrategias de indexación, agrupación y partición

Asignación de Costos y Distribución de Cargos

Los datos de bytes escaneados y tiempo de ejecución en el historial de consultas te permiten:

Atribuir costos de Snowflake a usuarios específicos, equipos o proyectos
Implementar modelos de asignación o distribución de cargos
Fomentar la eficiencia de las consultas y limitar los costos descontrolados

Acceso Seguro al Historial de Consultas

Nota que, por defecto, la vista query_history y las funciones QUERY_HISTORY solo son accesibles por el rol ACCOUNTADMIN. Para otorgar acceso a otros roles, necesitarás usar el comando GRANT IMPORTED PRIVILEGES.

Ten cuidado al otorgar acceso al historial de consultas, ya que puede contener información sensible. Considera crear un rol separado con privilegios limitados específicamente para propósitos de auditoría y monitoreo.

Resumen y Conclusión

La función de historial de consultas de Snowflake proporciona visibilidad esencial sobre las consultas que los usuarios ejecutan contra tu cuenta. Puedes usar la vista query_history y las funciones de la tabla QUERY_HISTORY para varios propósitos. Estos incluyen mejorar la auditoría, optimizar el rendimiento de las consultas y rastrear los costos de uso. Además, estas herramientas ofrecen más beneficios también.

Los ejemplos cubiertos en este artículo ilustran solo algunas de las muchas maneras en las que puedes obtener insights de los datos del historial de consultas. A medida que explores más, considera cómo esta información podría mejorar la seguridad, la eficiencia y la gobernanza dentro de tu propio entorno de Snowflake.

DataSunrise proporciona herramientas fáciles de usar y flexibles para gestionar la seguridad de Snowflake, reglas de auditoría, enmascaramiento dinámico de datos y cumplimiento. ¡Visita a nuestro equipo para una demostración en línea y ve estas capacidades en acción!