Historial de Consultas de Snowflake

Introducción

Rastrear y auditar la actividad de la base de datos es una parte crucial para asegurar y optimizar cualquier almacén de datos. Snowflake es una popular plataforma de datos en la nube que proporciona poderosas herramientas para obtener información detallada sobre las consultas en su cuenta. En este artículo, exploraremos en detalle la vista de historial de consultas de Snowflake y las funciones de tabla QUERY_HISTORY. Aprenderá cómo utilizar estas funcionalidades para obtener valiosos conocimientos sobre la actividad, el rendimiento y los patrones de acceso en las consultas.

¿Qué es la vista query_history de Snowflake?

La vista query_history en Snowflake muestra todas las consultas ejecutadas en su 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 Empresarial 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 Empresarial y superiores. Proporciona información detallada sobre cada consulta, incluyendo:

Texto de la consulta
Estado de ejecución (p. ej., 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 rellena automáticamente esta vista y no requiere ninguna configuración o ajuste por su parte. Puede consultar esta vista de auditoría de acceso de Snowflake como cualquier otra vista de Snowflake.

Ejemplo de consulta de query_history

A continuación se muestra un ejemplo básico del uso de 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 para obtener únicamente 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 tabla QUERY_HISTORY

Además de la vista query_history, Snowflake provee un conjunto de funciones de tabla para acceder a los datos del historial de consultas. Estas funciones le permiten recuperar el historial de consultas de períodos anteriores al periodo de retención de 7 o 14 días de la vista.

Las funciones QUERY_HISTORY vienen en tres variantes:

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

Ejemplo de uso de QUERY_HISTORY

Supongamos que desea encontrar a los 5 usuarios que han escaneado la mayor cantidad de datos en los últimos 14 días. Podría utilizar esta función de tabla de Snowflake de la siguiente manera:

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 utilizando la función QUERY_HISTORY. Suma la columna bytes_scanned para cada usuario, ordena los resultados de forma descendente por el total de bytes escaneados y obtiene los 5 principales usuarios.

Filtrado de resultados del historial de consultas

La vista query_history y las funciones QUERY_HISTORY admiten una amplia gama de predicados SQL para filtrar resultados. Algunos predicados comunes que puede utilizar incluyen:

query_id – El identificador único de una consulta
query_type – El tipo de consulta (p. ej., 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 (p. ej., RUNNING, COMPLETED, FAILED)
database_name, schema_name, table_name – Los nombres de los objetos de la base de datos a los que se accedió en la consulta

A continuación se muestra 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 para el historial de consultas

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

Auditoría y seguridad

Al rastrear todas las consultas ejecutadas en su cuenta de Snowflake, query_history le permite:

Monitorear actividad sospechosa o accesos no autorizados
Investigar incidentes de seguridad
Asegurar el cumplimiento de las políticas de gobernanza de datos
Proporcionar una pista de auditoría para los requisitos regulatorios

Optimización de consultas

Los datos del historial de consultas pueden ayudarle a optimizar el rendimiento de las consultas mediante:

Identificar las consultas que consumen más recursos
Analizar patrones de consultas a lo largo del tiempo
Detectar y solucionar errores o tiempos de espera en las consultas
Ajustar estrategias de indexación, agrupamiento y particionamiento

Recaudación de costos y asignación de gastos

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

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

Acceso seguro al historial de consultas

Tenga en cuenta 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, deberá usar el comando GRANT IMPORTED PRIVILEGES.

Tenga precaución al otorgar acceso al historial de consultas, ya que puede contener información sensible. Considere crear un rol separado con privilegios limitados específicamente para fines de auditoría y monitoreo.

Comparación de opciones de historial de consultas en Snowflake

Tanto query_history como QUERY_HISTORY() le brindan información sobre la actividad del usuario, pero difieren en alcance, retención y flexibilidad. Aquí hay una comparación rápida:

Característica	Vista `query_history`	Función `QUERY_HISTORY()`
Periodo de retención	7–14 días dependiendo de la edición	Hasta 1 año con retención extendida
Método de acceso	SELECT SQL estándar	Función de tabla (`TABLE(QUERY_HISTORY())`)
Flexibilidad de filtrado	Limitado a los campos expuestos de la vista	Amplio filtrado mediante cláusula WHERE
Sobrecarga de rendimiento	Ligero	Depende del rango de fechas y los filtros
Casos de uso	Búsquedas rápidas de usuarios, historial de sesiones	Análisis históricos, informes de auditoría, seguimiento de costos

Auditoría segura y centralizada con DataSunrise

Aunque Snowflake proporciona excelentes funciones integradas de historial, asegurar y analizar las consultas a nivel de proxy ofrece mucho más control. Con DataSunrise, puede:

Monitorear y registrar todas las consultas a través de Snowflake en tiempo real
Aplicar enmascaramiento dinámico basado en el tipo de consulta, usuario u objeto
Aplicar políticas de seguridad sin modificar los roles de Snowflake
Correlacionar la actividad de las consultas con el comportamiento del usuario y las reglas de acceso

Esto es especialmente útil cuando se trabaja en entornos con estrictas demandas de cumplimiento, donde la visibilidad de la auditoría debe extenderse más allá de los metadatos internos de Snowflake.

Mejor práctica: Otorgar acceso seguro al historial de consultas

En lugar de otorgar acceso total de ACCOUNTADMIN, cree un rol con privilegios limitados para ejecutar consultas de auditoría histórica:

-- Crear un rol de auditoría de solo lectura
CREATE ROLE audit_viewer;

-- Otorgar uso de la base de datos SNOWFLAKE
GRANT IMPORTED PRIVILEGES ON DATABASE snowflake TO ROLE audit_viewer;

-- Opcionalmente asignar a usuarios de monitoreo
GRANT ROLE audit_viewer TO USER readonly_analyst;

Esta configuración limita el acceso mientras permite una visibilidad segura del historial de consultas. Combínela con la capa de auditoría de DataSunrise para asegurar un monitoreo a prueba de manipulaciones.

Resumen y Conclusión

La función de historial de consultas de Snowflake proporciona una visibilidad esencial sobre las consultas que los usuarios ejecutan en su cuenta. Puede utilizar la vista query_history y las funciones de tabla QUERY_HISTORY para diversos fines. Estos incluyen mejorar la auditoría, optimizar el rendimiento de consultas y rastrear los costos de uso. Además, estas herramientas ofrecen aún más beneficios.

Los ejemplos presentados en este artículo ilustran solo algunas de las muchas maneras en que puede obtener información a partir de los datos del historial de consultas. A medida que profundice, considere cómo esta información podría mejorar la seguridad, la eficiencia y la gobernanza en su propio entorno de Snowflake.

DataSunrise ofrece herramientas fáciles de usar y flexibles para gestionar la seguridad de Snowflake, las reglas de auditoría, el enmascaramiento dinámico de datos y el cumplimiento. ¡Visite nuestro equipo para una demo en línea y vea estas capacidades en acción!