from flask import Flask, jsonify, request
from flask_cors import CORS
import json
import os
from datetime import datetime, timedelta
import logging
from typing import Dict, List, Any, Optional
import pickle
from functools import wraps
import hashlib
# Set up logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
app = Flask(__name__)
CORS(app)
# Configuration
app.config['SECRET_KEY'] = 'your-secret-key-here'
CACHE_DIR = 'cache'
CACHE_EXPIRY_HOURS = 6
# Ensure cache directory exists
os.makedirs(CACHE_DIR, exist_ok=True)
class SnowflakeAnalytics:
def __init__(self, cursor):
self.cursor = cursor
def _get_cache_key(self, query: str, params: Dict = None) -> str:
"""Generate cache key from query and parameters"""
cache_data = f"{query}_{params if params else ''}"
return hashlib.md5(cache_data.encode()).hexdigest()
def _get_cached_data(self, cache_key: str) -> Optional[Any]:
"""Get cached data if it exists and hasn't expired"""
cache_file = os.path.join(CACHE_DIR, f"{cache_key}.pkl")
if not os.path.exists(cache_file):
return None
try:
with open(cache_file, 'rb') as f:
cached_data = pickle.load(f)
# Check if cache has expired
if datetime.now() - cached_data['timestamp'] > timedelta(hours=CACHE_EXPIRY_HOURS):
os.remove(cache_file)
return None
return cached_data['data']
except Exception as e:
logger.error(f"Error reading cache: {e}")
return None
def _save_to_cache(self, cache_key: str, data: Any):
"""Save data to cache"""
cache_file = os.path.join(CACHE_DIR, f"{cache_key}.pkl")
try:
with open(cache_file, 'wb') as f:
pickle.dump({
'data': data,
'timestamp': datetime.now()
}, f)
except Exception as e:
logger.error(f"Error saving to cache: {e}")
def _execute_query_with_cache(self, query: str, params: Dict = None) -> List[Dict]:
"""Execute query with caching"""
cache_key = self._get_cache_key(query, params)
# Try to get from cache first
cached_data = self._get_cached_data(cache_key)
if cached_data:
logger.info(f"Using cached data for query hash: {cache_key[:8]}")
return cached_data
# Execute query
logger.info(f"Executing query: {query[:100]}...")
self.cursor.execute(query)
columns = [desc[0] for desc in self.cursor.description]
rows = self.cursor.fetchall()
# Convert to list of dictionaries
data = [dict(zip(columns, row)) for row in rows]
# Cache the results
self._save_to_cache(cache_key, data)
return data
def get_warehouse_summary(self) -> List[Dict]:
"""Get warehouse analytics dashboard data"""
query = "SELECT * FROM WAREHOUSE_ANALYTICS_DASHBOARD_with_queries ORDER BY TOTAL_QUERIES DESC"
return self._execute_query_with_cache(query)
def get_query_summary(self, limit: int = 1000) -> List[Dict]:
"""Get query history summary"""
query = f"SELECT * FROM QUERY_HISTORY_SUMMARY ORDER BY START_TIME DESC LIMIT {limit}"
return self._execute_query_with_cache(query)
def get_query_details(self, query_id: str) -> Dict:
"""Get detailed information about a specific query"""
query = f"SELECT * FROM QUERY_DETAILS_COMPLETE WHERE QUERY_ID = '{query_id}'"
result = self._execute_query_with_cache(query)
return result[0] if result else {}
def get_user_performance_report(self) -> List[Dict]:
"""Get user query performance report"""
query = "SELECT * FROM user_query_performance_report ORDER BY weighted_score DESC"
return self._execute_query_with_cache(query)
def get_warehouse_queries_by_user(self, warehouse_id: str, query_type: str) -> List[Dict]:
"""Get queries for a specific warehouse grouped by user"""
# Extract query IDs from the warehouse data first
warehouse_data = self.get_warehouse_summary()
target_warehouse = next((w for w in warehouse_data if str(w['WAREHOUSE_ID']) == warehouse_id), None)
if not target_warehouse or 'QUERY_IDS' not in target_warehouse:
return []
query_ids = target_warehouse['QUERY_IDS'].get(query_type, [])
if not query_ids:
return []
# Clean up query IDs (remove None values)
clean_query_ids = [qid for qid in query_ids if qid is not None]
if not clean_query_ids:
return []
# Get query details for these IDs
ids_str = "','".join(clean_query_ids)
query = f"""
SELECT
USER_NAME,
COUNT(*) as query_count,
ARRAY_AGG(QUERY_ID) as query_ids,
AVG(TOTAL_ELAPSED_TIME) as avg_execution_time,
SUM(BYTES_SCANNED) as total_bytes_scanned
FROM QUERY_HISTORY_SUMMARY
WHERE QUERY_ID IN ('{ids_str}')
GROUP BY USER_NAME
ORDER BY query_count DESC
"""
return self._execute_query_with_cache(query, {'warehouse_id': warehouse_id, 'query_type': query_type})
def get_user_queries(self, user_name: str, query_ids: List[str] = None) -> List[Dict]:
"""Get all queries for a specific user"""
if query_ids:
ids_str = "','".join(query_ids)
query = f"""
SELECT * FROM QUERY_HISTORY_SUMMARY
WHERE USER_NAME = '{user_name}' AND QUERY_ID IN ('{ids_str}')
ORDER BY START_TIME DESC
"""
else:
query = f"""
SELECT * FROM QUERY_HISTORY_SUMMARY
WHERE USER_NAME = '{user_name}'
ORDER BY START_TIME DESC
LIMIT 1000
"""
return self._execute_query_with_cache(query, {'user_name': user_name})
def get_user_sample_queries(self, user_name: str, query_type: str) -> List[Dict]:
"""Get sample queries for a specific user and query type"""
user_report = self.get_user_performance_report()
target_user = next((u for u in user_report if u['USER_NAME'] == user_name), None)
if not target_user or 'QUERY_SAMPLES' not in target_user:
return []
sample_queries = target_user['QUERY_SAMPLES'].get(query_type, [])
return sample_queries if sample_queries else []
# Global analytics instance (will be initialized with cursor)
analytics = None
def init_analytics(cursor):
"""Initialize the analytics instance with Snowflake cursor"""
global analytics
analytics = SnowflakeAnalytics(cursor)
def require_analytics(f):
"""Decorator to ensure analytics is initialized"""
@wraps(f)
def decorated_function(*args, **kwargs):
if analytics is None:
return jsonify({'error': 'Analytics not initialized'}), 500
return f(*args, **kwargs)
return decorated_function
# API Routes
@app.route('/api/warehouse-summary')
@require_analytics
def get_warehouse_summary():
"""Get warehouse analytics summary"""
try:
data = analytics.get_warehouse_summary()
return jsonify({'data': data, 'count': len(data)})
except Exception as e:
logger.error(f"Error fetching warehouse summary: {e}")
return jsonify({'error': str(e)}), 500
@app.route('/api/query-summary')
@require_analytics
def get_query_summary():
"""Get query history summary"""
try:
limit = request.args.get('limit', 1000, type=int)
data = analytics.get_query_summary(limit)
return jsonify({'data': data, 'count': len(data)})
except Exception as e:
logger.error(f"Error fetching query summary: {e}")
return jsonify({'error': str(e)}), 500
@app.route('/api/query-details/<query_id>')
@require_analytics
def get_query_details(query_id):
"""Get detailed information about a specific query"""
try:
data = analytics.get_query_details(query_id)
return jsonify({'data': data})
except Exception as e:
logger.error(f"Error fetching query details: {e}")
return jsonify({'error': str(e)}), 500
@app.route('/api/user-performance')
@require_analytics
def get_user_performance():
"""Get user performance report"""
try:
data = analytics.get_user_performance_report()
return jsonify({'data': data, 'count': len(data)})
except Exception as e:
logger.error(f"Error fetching user performance: {e}")
return jsonify({'error': str(e)}), 500
@app.route('/api/warehouse/<warehouse_id>/queries/<query_type>/users')
@require_analytics
def get_warehouse_queries_by_user(warehouse_id, query_type):
"""Get queries for a warehouse grouped by user"""
try:
data = analytics.get_warehouse_queries_by_user(warehouse_id, query_type)
return jsonify({'data': data, 'count': len(data)})
except Exception as e:
logger.error(f"Error fetching warehouse queries by user: {e}")
return jsonify({'error': str(e)}), 500
@app.route('/api/user/<user_name>/queries')
@require_analytics
def get_user_queries(user_name):
"""Get all queries for a specific user"""
try:
query_ids = request.args.getlist('query_ids')
data = analytics.get_user_queries(user_name, query_ids if query_ids else None)
return jsonify({'data': data, 'count': len(data)})
except Exception as e:
logger.error(f"Error fetching user queries: {e}")
return jsonify({'error': str(e)}), 500
@app.route('/api/user/<user_name>/sample-queries/<query_type>')
@require_analytics
def get_user_sample_queries(user_name, query_type):
"""Get sample queries for a specific user and query type"""
try:
data = analytics.get_user_sample_queries(user_name, query_type)
return jsonify({'data': data, 'count': len(data)})
except Exception as e:
logger.error(f"Error fetching user sample queries: {e}")
return jsonify({'error': str(e)}), 500
@app.route('/api/health')
def health_check():
"""Health check endpoint"""
return jsonify({'status': 'healthy', 'timestamp': datetime.now().isoformat()})
@app.errorhandler(404)
def not_found(error):
return jsonify({'error': 'Endpoint not found'}), 404
@app.errorhandler(500)
def internal_error(error):
return jsonify({'error': 'Internal server error'}), 500
if __name__ == '__main__':
# For development - in production, you'll initialize with actual cursor
print("Flask app ready. Initialize with cursor using init_analytics(cursor)")
app.run(debug=True, host='0.0.0.0', port=5000)
Enter fullscreen mode
Exit fullscreen mode
import React, { useState, useEffect, useCallback, useMemo } from 'react';
import {
ChevronDownIcon,
ChevronRightIcon,
EyeIcon,
UserIcon,
ServerIcon,
ClockIcon,
CpuChipIcon,
ExclamationTriangleIcon,
DocumentMagnifyingGlassIcon
} from '@heroicons/react/24/outline';
// API Configuration
const API_BASE_URL = process.env.REACT_APP_API_URL || 'http://localhost:5000';
// API Service
class ApiService {
static async get(endpoint) {
try {
const response = await fetch(`${API_BASE_URL}/api${endpoint}`);
if (!response.ok) {
throw new Error(`HTTP error! status: ${response.status}`);
}
return await response.json();
} catch (error) {
console.error('API Error:', error);
throw error;
}
}
}
// Utility Functions
const formatBytes = (bytes) => {
if (!bytes) return '0 B';
const k = 1024;
const sizes = ['B', 'KB', 'MB', 'GB', 'TB'];
const i = Math.floor(Math.log(bytes) / Math.log(k));
return parseFloat((bytes / Math.pow(k, i)).toFixed(2)) + ' ' + sizes[i];
};
const formatDuration = (ms) => {
if (!ms) return '0ms';
if (ms < 1000) return `${ms}ms`;
if (ms < 60000) return `${(ms / 1000).toFixed(1)}s`;
return `${(ms / 60000).toFixed(1)}m`;
};
const formatNumber = (num) => {
if (!num) return '0';
return num.toLocaleString();
};
// Loading Spinner Component
const LoadingSpinner = () => (
<div className="flex justify-center items-center p-8">
<div className="animate-spin rounded-full h-8 w-8 border-b-2 border-blue-600"></div>
</div>
);
// Table Components
const TableHeader = ({ children, sortable = false, onSort, sortDirection }) => (
<th
className={`px-4 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider ${
sortable ? 'cursor-pointer hover:bg-gray-100' : ''
}`}
onClick={sortable ? onSort : undefined}
>
<div className="flex items-center space-x-1">
<span>{children}</span>
{sortable && (
<ChevronDownIcon
className={`w-4 h-4 transform transition-transform ${
sortDirection === 'desc' ? 'rotate-180' : ''
}`}
/>
)}
</div>
</th>
);
const TableCell = ({ children, className = '' }) => (
<td className={`px-4 py-3 text-sm text-gray-900 ${className}`}>
{children}
</td>
);
// Badge Component
const Badge = ({ variant, children, className = '' }) => {
const variants = {
success: 'bg-green-100 text-green-800',
warning: 'bg-yellow-100 text-yellow-800',
error: 'bg-red-100 text-red-800',
info: 'bg-blue-100 text-blue-800',
default: 'bg-gray-100 text-gray-800'
};
return (
<span className={`inline-flex px-2 py-1 text-xs font-medium rounded-full ${variants[variant] || variants.default} ${className}`}>
{children}
</span>
);
};
// Button Component
const Button = ({
children,
variant = 'primary',
size = 'md',
onClick,
disabled = false,
icon: Icon,
className = ''
}) => {
const variants = {
primary: 'bg-blue-600 hover:bg-blue-700 text-white',
secondary: 'bg-gray-600 hover:bg-gray-700 text-white',
outline: 'border border-gray-300 hover:bg-gray-50 text-gray-700'
};
const sizes = {
sm: 'px-2 py-1 text-xs',
md: 'px-3 py-2 text-sm',
lg: 'px-4 py-2 text-base'
};
return (
<button
onClick={onClick}
disabled={disabled}
className={`
inline-flex items-center space-x-2 font-medium rounded-md
${variants[variant]} ${sizes[size]}
${disabled ? 'opacity-50 cursor-not-allowed' : 'transition-colors'}
${className}
`}
>
{Icon && <Icon className="w-4 h-4" />}
<span>{children}</span>
</button>
);
};
// Warehouse Analytics Table Component
const WarehouseTable = ({ onDrillDown }) => {
const [data, setData] = useState([]);
const [loading, setLoading] = useState(true);
const [sortConfig, setSortConfig] = useState({ key: 'TOTAL_QUERIES', direction: 'desc' });
const [expandedRows, setExpandedRows] = useState(new Set());
useEffect(() => {
const fetchData = async () => {
try {
const response = await ApiService.get('/warehouse-summary');
setData(response.data);
} catch (error) {
console.error('Failed to fetch warehouse data:', error);
} finally {
setLoading(false);
}
};
fetchData();
}, []);
const handleSort = useCallback((key) => {
setSortConfig(prev => ({
key,
direction: prev.key === key && prev.direction === 'desc' ? 'asc' : 'desc'
}));
}, []);
const sortedData = useMemo(() => {
return [...data].sort((a, b) => {
const aVal = a[sortConfig.key] || 0;
const bVal = b[sortConfig.key] || 0;
return sortConfig.direction === 'desc' ? bVal - aVal : aVal - bVal;
});
}, [data, sortConfig]);
const toggleRowExpansion = (warehouseId) => {
setExpandedRows(prev => {
const newSet = new Set(prev);
if (newSet.has(warehouseId)) {
newSet.delete(warehouseId);
} else {
newSet.add(warehouseId);
}
return newSet;
});
};
const getQueryTypeButtons = (warehouse) => {
const queryTypes = [
{ key: '1-10_sec_ids', label: '1-10s', value: warehouse.QUERIES_1_10_SEC },
{ key: '10-20_sec_ids', label: '10-20s', value: warehouse.QUERIES_10_20_SEC },
{ key: '20-60_sec_ids', label: '20-60s', value: warehouse.QUERIES_20_60_SEC },
{ key: '1-3_min_ids', label: '1-3m', value: warehouse.QUERIES_1_3_MIN },
{ key: '3-5_min_ids', label: '3-5m', value: warehouse.QUERIES_3_5_MIN },
{ key: '5_plus_min_ids', label: '5m+', value: warehouse.QUERIES_5_PLUS_MIN },
{ key: 'spilled_local_ids', label: 'Spilled Local', value: warehouse.QUERIES_SPILLED_LOCAL },
{ key: 'spilled_remote_ids', label: 'Spilled Remote', value: warehouse.QUERIES_SPILLED_REMOTE },
{ key: 'failed_queries_ids', label: 'Failed', value: warehouse.FAILED_QUERIES },
];
return queryTypes.filter(type => type.value > 0);
};
if (loading) return <LoadingSpinner />;
return (
<div className="bg-white shadow-lg rounded-lg overflow-hidden">
<div className="px-6 py-4 border-b border-gray-200">
<h2 className="text-xl font-semibold text-gray-800 flex items-center space-x-2">
<ServerIcon className="w-6 h-6" />
<span>Warehouse Analytics</span>
</h2>
</div>
<div className="overflow-x-auto">
<table className="min-w-full divide-y divide-gray-200">
<thead className="bg-gray-50">
<tr>
<th className="px-4 py-3 text-left"></th>
<TableHeader
sortable
onSort={() => handleSort('WAREHOUSE_NAME')}
sortDirection={sortConfig.key === 'WAREHOUSE_NAME' ? sortConfig.direction : null}
>
Warehouse Name
</TableHeader>
<TableHeader>Size</TableHeader>
<TableHeader
sortable
onSort={() => handleSort('TOTAL_QUERIES')}
sortDirection={sortConfig.key === 'TOTAL_QUERIES' ? sortConfig.direction : null}
>
Total Queries
</TableHeader>
<TableHeader
sortable
onSort={() => handleSort('TOTAL_CREDITS_USED')}
sortDirection={sortConfig.key === 'TOTAL_CREDITS_USED' ? sortConfig.direction : null}
>
Credits Used
</TableHeader>
<TableHeader>Failed</TableHeader>
<TableHeader>Spilled</TableHeader>
<TableHeader>Actions</TableHeader>
</tr>
</thead>
<tbody className="bg-white divide-y divide-gray-200">
{sortedData.map((warehouse) => (
<React.Fragment key={warehouse.WAREHOUSE_ID}>
<tr className="hover:bg-gray-50">
<TableCell>
<button
onClick={() => toggleRowExpansion(warehouse.WAREHOUSE_ID)}
className="p-1 hover:bg-gray-200 rounded"
>
{expandedRows.has(warehouse.WAREHOUSE_ID) ? (
<ChevronDownIcon className="w-4 h-4" />
) : (
<ChevronRightIcon className="w-4 h-4" />
)}
</button>
</TableCell>
<TableCell className="font-medium">
{warehouse.WAREHOUSE_NAME}
</TableCell>
<TableCell>
<Badge variant="info">{warehouse.WAREHOUSE_SIZE}</Badge>
</TableCell>
<TableCell className="font-semibold text-blue-600">
{formatNumber(warehouse.TOTAL_QUERIES)}
</TableCell>
<TableCell>
{warehouse.TOTAL_CREDITS_USED ? warehouse.TOTAL_CREDITS_USED.toFixed(2) : '0.00'}
</TableCell>
<TableCell>
{warehouse.FAILED_QUERIES > 0 ? (
<Badge variant="error">{formatNumber(warehouse.FAILED_QUERIES)}</Badge>
) : (
<Badge variant="success">0</Badge>
)}
</TableCell>
<TableCell>
{(warehouse.QUERIES_SPILLED_LOCAL + warehouse.QUERIES_SPILLED_REMOTE) > 0 ? (
<Badge variant="warning">
{formatNumber(warehouse.QUERIES_SPILLED_LOCAL + warehouse.QUERIES_SPILLED_REMOTE)}
</Badge>
) : (
<Badge variant="success">0</Badge>
)}
</TableCell>
<TableCell>
<Button
size="sm"
variant="outline"
icon={EyeIcon}
onClick={() => onDrillDown('warehouse-details', { warehouse })}
>
View Details
</Button>
</TableCell>
</tr>
{expandedRows.has(warehouse.WAREHOUSE_ID) && (
<tr>
<td colSpan="8" className="px-4 py-3 bg-gray-50">
<div className="grid grid-cols-2 md:grid-cols-4 lg:grid-cols-6 gap-2">
{getQueryTypeButtons(warehouse).map((type) => (
<Button
key={type.key}
size="sm"
variant="outline"
onClick={() => onDrillDown('warehouse-queries', {
warehouseId: warehouse.WAREHOUSE_ID,
warehouseName: warehouse.WAREHOUSE_NAME,
queryType: type.key,
queryLabel: type.label
})}
className="justify-center"
>
{type.label}: {formatNumber(type.value)}
</Button>
))}
</div>
</td>
</tr>
)}
</React.Fragment>
))}
</tbody>
</table>
</div>
</div>
);
};
// User Performance Table Component
const UserPerformanceTable = ({ onDrillDown }) => {
const [data, setData] = useState([]);
const [loading, setLoading] = useState(true);
const [sortConfig, setSortConfig] = useState({ key: 'weighted_score', direction: 'desc' });
useEffect(() => {
const fetchData = async () => {
try {
const response = await ApiService.get('/user-performance');
setData(response.data);
} catch (error) {
console.error('Failed to fetch user performance data:', error);
} finally {
setLoading(false);
}
};
fetchData();
}, []);
const handleSort = useCallback((key) => {
setSortConfig(prev => ({
key,
direction: prev.key === key && prev.direction === 'desc' ? 'asc' : 'desc'
}));
}, []);
const sortedData = useMemo(() => {
return [...data].sort((a, b) => {
const aVal = a[sortConfig.key] || 0;
const bVal = b[sortConfig.key] || 0;
return sortConfig.direction === 'desc' ? bVal - aVal : aVal - bVal;
});
}, [data, sortConfig]);
const getScoreVariant = (score) => {
if (score >= 100) return 'error';
if (score >= 50) return 'warning';
return 'success';
};
if (loading) return <LoadingSpinner />;
return (
<div className="bg-white shadow-lg rounded-lg overflow-hidden">
<div className="px-6 py-4 border-b border-gray-200">
<h2 className="text-xl font-semibold text-gray-800 flex items-center space-x-2">
<UserIcon className="w-6 h-6" />
<span>User Performance Report</span>
</h2>
</div>
<div className="overflow-x-auto">
<table className="min-w-full divide-y divide-gray-200">
<thead className="bg-gray-50">
<tr>
<TableHeader
sortable
onSort={() => handleSort('user_name')}
sortDirection={sortConfig.key === 'user_name' ? sortConfig.direction : null}
>
User Name
</TableHeader>
<TableHeader
sortable
onSort={() => handleSort('total_queries')}
sortDirection={sortConfig.key === 'total_queries' ? sortConfig.direction : null}
>
Total Queries
</TableHeader>
<TableHeader
sortable
onSort={() => handleSort('total_credits')}
sortDirection={sortConfig.key === 'total_credits' ? sortConfig.direction : null}
>
Credits Used
</TableHeader>
<TableHeader>Spilled Queries</TableHeader>
<TableHeader>Failed Queries</TableHeader>
<TableHeader>Performance Issues</TableHeader>
<TableHeader
sortable
onSort={() => handleSort('weighted_score')}
sortDirection={sortConfig.key === 'weighted_score' ? sortConfig.direction : null}
>
Score
</TableHeader>
<TableHeader>Actions</TableHeader>
</tr>
</thead>
<tbody className="bg-white divide-y divide-gray-200">
{sortedData.map((user) => (
<tr key={user.user_name} className="hover:bg-gray-50">
<TableCell className="font-medium">
{user.user_name}
</TableCell>
<TableCell className="font-semibold text-blue-600">
{formatNumber(user.total_queries)}
</TableCell>
<TableCell>
{user.total_credits ? user.total_credits.toFixed(2) : '0.00'}
</TableCell>
<TableCell>
<button
onClick={() => onDrillDown('user-sample-queries', {
userName: user.user_name,
queryType: 'spilled',
queryLabel: 'Spilled Queries'
})}
className="text-blue-600 hover:text-blue-800 underline"
>
{formatNumber(user.spilled_queries)}
</button>
</TableCell>
<TableCell>
<span className={`font-medium ${user.failure_cancellation_rate_pct > 10 ? 'text-red-600' : 'text-green-600'}`}>
{user.failure_cancellation_rate_pct ? user.failure_cancellation_rate_pct.toFixed(1) : '0.0'}%
</span>
</TableCell>
<TableCell>
<div className="flex flex-wrap gap-1">
{user.over_provisioned_queries > 0 && (
<button
onClick={() => onDrillDown('user-sample-queries', {
userName: user.user_name,
queryType: 'over_provisioned',
queryLabel: 'Over Provisioned Queries'
})}
className="text-xs bg-yellow-100 text-yellow-800 px-2 py-1 rounded hover:bg-yellow-200"
>
Over Provisioned: {user.over_provisioned_queries}
</button>
)}
{user.select_star_queries > 0 && (
<button
onClick={() => onDrillDown('user-sample-queries', {
userName: user.user_name,
queryType: 'select_star',
queryLabel: 'SELECT * Queries'
})}
className="text-xs bg-orange-100 text-orange-800 px-2 py-1 rounded hover:bg-orange-200"
>
SELECT *: {user.select_star_queries}
</button>
)}
{user.slow_queries > 0 && (
<button
onClick={() => onDrillDown('user-sample-queries', {
userName: user.user_name,
queryType: 'slow_query',
queryLabel: 'Slow Queries'
})}
className="text-xs bg-red-100 text-red-800 px-2 py-1 rounded hover:bg-red-200"
>
Slow: {user.slow_queries}
</button>
)}
</div>
</TableCell>
<TableCell>
<Badge variant={getScoreVariant(user.weighted_score)}>
{user.weighted_score ? user.weighted_score.toFixed(1) : '0.0'}
</Badge>
</TableCell>
<TableCell>
<Button
size="sm"
variant="outline"
icon={EyeIcon}
onClick={() => onDrillDown('user-details', { user })}
>
View Details
</Button>
</TableCell>
</tr>
))}
</tbody>
</table>
</div>
</div>
);
};
// Query Details Modal Component
const QueryDetailsModal = ({ queryId, isOpen, onClose }) => {
const [data, setData] = useState(null);
const [loading, setLoading] = useState(false);
useEffect(() => {
if (isOpen && queryId) {
setLoading(true);
ApiService.get(`/query-details/${queryId}`)
.then(response => setData(response.data))
.catch(error => console.error('Failed to fetch query details:', error))
.finally(() => setLoading(false));
}
}, [isOpen, queryId]);
if (!isOpen) return null;
return (
<div className="fixed inset-0 bg-gray-600 bg-opacity-50 overflow-y-auto h-full w-full z-50">
<div className="relative top-20 mx-auto p-5 border w-11/12 max-w-4xl shadow-lg rounded-md bg-white">
<div className="mt-3">
<div className="flex items-center justify-between mb-4">
<h3 className="text-lg font-medium text-gray-900">Query Details</h3>
<button
onClick={onClose}
className="text-gray-400 hover:text-gray-600"
>
<svg className="w-6 h-6" fill="none" stroke="currentColor" viewBox="0 0 24 24">
<path strokeLinecap="round" strokeLinejoin="round" strokeWidth="2" d="M6 18L18 6M6 6l12 12" />
</svg>
</button>
</div>
{loading ? (
<LoadingSpinner />
) : data ? (
<div className="space-y-6">
{/* Query Information */}
<div className="bg-gray-50 p-4 rounded-lg">
<h4 className="font-medium text-gray-900 mb-3">Query Information</h4>
<div className="grid grid-cols-1 md:grid-cols-2 gap-4">
<div>
<span className="text-sm font-medium text-gray-500">Query ID:</span>
<p className="text-sm text-gray-900 font-mono">{data.QUERY_ID}</p>
</div>
<div>
<span className="text-sm font-medium text-gray-500">User:</span>
<p className="text-sm text-gray-900">{data.USER_NAME}</p>
</div>
<div>
<span className="text-sm font-medium text-gray-500">Warehouse:</span>
<p className="text-sm text-gray-900">{data.WAREHOUSE_NAME} ({data.WAREHOUSE_SIZE})</p>
</div>
<div>
<span className="text-sm font-medium text-gray-500">Database:</span>
<p className="text-sm text-gray-900">{data.DATABASE_NAME}.{data.SCHEMA_NAME}</p>
</div>
</div>
</div>
{/* Performance Metrics */}
<div className="bg-gray-50 p-4 rounded-lg">
<h4 className="font-medium text-gray-900 mb-3">Performance Metrics</h4>
<div className="grid grid-cols-1 md:grid-cols-3 gap-4">
<div>
<span className="text-sm font-medium text-gray-500">Total Duration:</span>
<p className="text-sm text-gray-900">{formatDuration(data.TOTAL_ELAPSED_TIME)}</p>
</div>
<div>
<span className="text-sm font-medium text-gray-500">Compilation Time:</span>
<p className="text-sm text-gray-900">{formatDuration(data.COMPILATION_TIME)}</p>
</div>
<div>
<span className="text-sm font-medium text-gray-500">Execution Time:</span>
<p className="text-sm text-gray-900">{formatDuration(data.EXECUTION_TIME)}</p>
</div>
<div>
<span className="text-sm font-medium text-gray-500">Bytes Scanned:</span>
<p className="text-sm text-gray-900">{formatBytes(data.BYTES_SCANNED)}</p>
</div>
<div>
<span className="text-sm font-medium text-gray-500">Rows Produced:</span>
<p className="text-sm text-gray-900">{formatNumber(data.ROWS_PRODUCED)}</p>
</div>
<div>
<span className="text-sm font-medium text-gray-500">Credits Used:</span>
<p className="text-sm text-gray-900">{data.CREDITS_USED_CLOUD_SERVICES?.toFixed(4) || '0.0000'}</p>
</div>
</div>
</div>
{/* Query Text */}
<div className="bg-gray-50 p-4 rounded-lg">
<h4 className="font-medium text-gray-900 mb-3">Query Text</h4>
<pre className="text-sm text-gray-900 bg-white p-3 rounded border overflow-auto max-h-64 font-mono">
{data.QUERY_TEXT}
</pre>
</div>
{/* Spilling Information */}
{(data.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 || data.BYTES_SPILLED_TO_REMOTE_STORAGE > 0) && (
<div className="bg-yellow-50 p-4 rounded-lg border border-yellow-200">
<h4 className="font-medium text-yellow-900 mb-3 flex items-center">
<ExclamationTriangleIcon className="w-5 h-5 mr-2" />
Memory Spilling Detected
</h4>
<div className="grid grid-cols-1 md:grid-cols-2 gap-4">
<div>
<span className="text-sm font-medium text-yellow-800">Local Storage:</span>
<p className="text-sm text-yellow-900">{formatBytes(data.BYTES_SPILLED_TO_LOCAL_STORAGE)}</p>
</div>
<div>
<span className="text-sm font-medium text-yellow-800">Remote Storage:</span>
<p className="text-sm text-yellow-900">{formatBytes(data.BYTES_SPILLED_TO_REMOTE_STORAGE)}</p>
</div>
</div>
</div>
)}
{/* Error Information */}
{data.ERROR_CODE && (
<div className="bg-red-50 p-4 rounded-lg border border-red-200">
<h4 className="font-medium text-red-900 mb-3">Error Information</h4>
<div>
<span className="text-sm font-medium text-red-800">Error Code:</span>
<p className="text-sm text-red-900">{data.ERROR_CODE}</p>
</div>
{data.ERROR_MESSAGE && (
<div className="mt-2">
<span className="text-sm font-medium text-red-800">Error Message:</span>
<p className="text-sm text-red-900">{data.ERROR_MESSAGE}</p>
</div>
)}
</div>
)}
</div>
) : (
<div className="text-center py-8">
<p className="text-gray-500">No query details found</p>
</div>
)}
<div className="flex justify-end mt-6">
<Button onClick={onClose} variant="secondary">
Close
</Button>
</div>
</div>
</div>
</div>
);
};
// Warehouse Query Users Component
const WarehouseQueryUsers = ({ warehouseId, warehouseName, queryType, queryLabel, onDrillDown, onBack }) => {
const [data, setData] = useState([]);
const [loading, setLoading] = useState(true);
useEffect(() => {
const fetchData = async () => {
try {
const response = await ApiService.get(`/warehouse/${warehouseId}/queries/${queryType}/users`);
setData(response.data);
} catch (error) {
console.error('Failed to fetch warehouse query users:', error);
} finally {
setLoading(false);
}
};
fetchData();
}, [warehouseId, queryType]);
if (loading) return <LoadingSpinner />;
return (
<div className="bg-white shadow-lg rounded-lg overflow-hidden">
<div className="px-6 py-4 border-b border-gray-200">
<div className="flex items-center justify-between">
<h2 className="text-xl font-semibold text-gray-800 flex items-center space-x-2">
<ServerIcon className="w-6 h-6" />
<span>{warehouseName} - {queryLabel} by User</span>
</h2>
<Button onClick={onBack} variant="outline">
Back to Warehouses
</Button>
</div>
</div>
<div className="overflow-x-auto">
<table className="min-w-full divide-y divide-gray-200">
<thead className="bg-gray-50">
<tr>
<TableHeader>User Name</TableHeader>
<TableHeader>Query Count</TableHeader>
<TableHeader>Avg Execution Time</TableHeader>
<TableHeader>Total Bytes Scanned</TableHeader>
<TableHeader>Actions</TableHeader>
</tr>
</thead>
<tbody className="bg-white divide-y divide-gray-200">
{data.map((user) => (
<tr key={user.USER_NAME} className="hover:bg-gray-50">
<TableCell className="font-medium">
{user.USER_NAME}
</TableCell>
<TableCell className="font-semibold text-blue-600">
{formatNumber(user.QUERY_COUNT)}
</TableCell>
<TableCell>
{formatDuration(user.AVG_EXECUTION_TIME)}
</TableCell>
<TableCell>
{formatBytes(user.TOTAL_BYTES_SCANNED)}
</TableCell>
<TableCell>
<Button
size="sm"
variant="outline"
icon={UserIcon}
onClick={() => onDrillDown('user-queries', {
userName: user.USER_NAME,
queryIds: user.QUERY_IDS
})}
>
View Queries
</Button>
</TableCell>
</tr>
))}
</tbody>
</table>
</div>
</div>
);
};
// User Queries Component
const UserQueriesTable = ({ userName, queryIds, onDrillDown, onBack }) => {
const [data, setData] = useState([]);
const [loading, setLoading] = useState(true);
const [selectedQuery, setSelectedQuery] = useState(null);
useEffect(() => {
const fetchData = async () => {
try {
const params = queryIds ? `?${queryIds.map(id => `query_ids=${id}`).join('&')}` : '';
const response = await ApiService.get(`/user/${encodeURIComponent(userName)}/queries${params}`);
setData(response.data);
} catch (error) {
console.error('Failed to fetch user queries:', error);
} finally {
setLoading(false);
}
};
fetchData();
}, [userName, queryIds]);
if (loading) return <LoadingSpinner />;
return (
<div className="bg-white shadow-lg rounded-lg overflow-hidden">
<div className="px-6 py-4 border-b border-gray-200">
<div className="flex items-center justify-between">
<h2 className="text-xl font-semibold text-gray-800 flex items-center space-x-2">
<UserIcon className="w-6 h-6" />
<span>Queries by {userName}</span>
</h2>
<Button onClick={onBack} variant="outline">
Back
</Button>
</div>
</div>
<div className="overflow-x-auto">
<table className="min-w-full divide-y divide-gray-200">
<thead className="bg-gray-50">
<tr>
<TableHeader>Query ID</TableHeader>
<TableHeader>Start Time</TableHeader>
<TableHeader>Duration</TableHeader>
<TableHeader>Status</TableHeader>
<TableHeader>Warehouse</TableHeader>
<TableHeader>Bytes Scanned</TableHeader>
<TableHeader>Actions</TableHeader>
</tr>
</thead>
<tbody className="bg-white divide-y divide-gray-200">
{data.map((query) => (
<tr key={query.QUERY_ID} className="hover:bg-gray-50">
<TableCell className="font-mono text-xs">
{query.QUERY_ID.substring(0, 16)}...
</TableCell>
<TableCell>
{new Date(query.START_TIME).toLocaleString()}
</TableCell>
<TableCell>
<Badge variant={query.DURATION_BUCKET === '5+ minutes' ? 'warning' : 'default'}>
{query.DURATION_BUCKET}
</Badge>
</TableCell>
<TableCell>
<Badge variant={query.EXECUTION_STATUS === 'SUCCESS' ? 'success' : 'error'}>
{query.EXECUTION_STATUS}
</Badge>
</TableCell>
<TableCell>
{query.WAREHOUSE_NAME}
</TableCell>
<TableCell>
{formatBytes(query.BYTES_SCANNED)}
</TableCell>
<TableCell>
<Button
size="sm"
variant="outline"
icon={DocumentMagnifyingGlassIcon}
onClick={() => setSelectedQuery(query.QUERY_ID)}
>
View Details
</Button>
</TableCell>
</tr>
))}
</tbody>
</table>
</div>
<QueryDetailsModal
queryId={selectedQuery}
isOpen={!!selectedQuery}
onClose={() => setSelectedQuery(null)}
/>
</div>
);
};
// User Sample Queries Component
const UserSampleQueriesTable = ({ userName, queryType, queryLabel, onBack }) => {
const [data, setData] = useState([]);
const [loading, setLoading] = useState(true);
const [selectedQuery, setSelectedQuery] = useState(null);
useEffect(() => {
const fetchData = async () => {
try {
const response = await ApiService.get(`/user/${encodeURIComponent(userName)}/sample-queries/${queryType}`);
setData(response.data);
} catch (error) {
console.error('Failed to fetch user sample queries:', error);
} finally {
setLoading(false);
}
};
fetchData();
}, [userName, queryType]);
if (loading) return <LoadingSpinner />;
return (
<div className="bg-white shadow-lg rounded-lg overflow-hidden">
<div className="px-6 py-4 border-b border-gray-200">
<div className="flex items-center justify-between">
<h2 className="text-xl font-semibold text-gray-800 flex items-center space-x-2">
<UserIcon className="w-6 h-6" />
<span>{userName} - {queryLabel}</span>
</h2>
<Button onClick={onBack} variant="outline">
Back
</Button>
</div>
</div>
<div className="overflow-x-auto">
<table className="min-w-full divide-y divide-gray-200">
<thead className="bg-gray-50">
<tr>
<TableHeader>Query ID</TableHeader>
<TableHeader>Start Time</TableHeader>
<TableHeader>Duration</TableHeader>
<TableHeader>Bytes Scanned</TableHeader>
<TableHeader>Query Preview</TableHeader>
<TableHeader>Actions</TableHeader>
</tr>
</thead>
<tbody className="bg-white divide-y divide-gray-200">
{data.map((query, index) => (
<tr key={query.query_id || index} className="hover:bg-gray-50">
<TableCell className="font-mono text-xs">
{query.query_id ? `${query.query_id.substring(0, 16)}...` : 'N/A'}
</TableCell>
<TableCell>
{query.start_time ? new Date(query.start_time).toLocaleString() : 'N/A'}
</TableCell>
<TableCell>
{formatDuration(query.execution_time_ms)}
</TableCell>
<TableCell>
{formatBytes(query.bytes_scanned)}
</TableCell>
<TableCell className="max-w-xs truncate">
{query.query_text ? query.query_text.substring(0, 50) + '...' : 'N/A'}
</TableCell>
<TableCell>
{query.query_id && (
<Button
size="sm"
variant="outline"
icon={DocumentMagnifyingGlassIcon}
onClick={() => setSelectedQuery(query.query_id)}
>
View Details
</Button>
)}
</TableCell>
</tr>
))}
</tbody>
</table>
</div>
<QueryDetailsModal
queryId={selectedQuery}
isOpen={!!selectedQuery}
onClose={() => setSelectedQuery(null)}
/>
</div>
);
};
// Main Application Component
const App = () => {
const [currentView, setCurrentView] = useState('dashboard');
const [viewData, setViewData] = useState({});
const [navigationHistory, setNavigationHistory] = useState([]);
const handleDrillDown = (view, data) => {
setNavigationHistory(prev => [...prev, { view: currentView, data: viewData }]);
setCurrentView(view);
setViewData(data);
};
const handleBack = () => {
const lastView = navigationHistory[navigationHistory.length - 1];
if (lastView) {
setCurrentView(lastView.view);
setViewData(lastView.data);
setNavigationHistory(prev => prev.slice(0, -1));
}
};
const renderCurrentView = () => {
switch (currentView) {
case 'dashboard':
return (
<div className="space-y-8">
<WarehouseTable onDrillDown={handleDrillDown} />
<UserPerformanceTable onDrillDown={handleDrillDown} />
</div>
);
case 'warehouse-queries':
return (
<WarehouseQueryUsers
warehouseId={viewData.warehouseId}
warehouseName={viewData.warehouseName}
queryType={viewData.queryType}
queryLabel={viewData.queryLabel}
onDrillDown={handleDrillDown}
onBack={handleBack}
/>
);
case 'user-queries':
return (
<UserQueriesTable
userName={viewData.userName}
queryIds={viewData.queryIds}
onDrillDown={handleDrillDown}
onBack={handleBack}
/>
);
case 'user-sample-queries':
return (
<UserSampleQueriesTable
userName={viewData.userName}
queryType={viewData.queryType}
queryLabel={viewData.queryLabel}
onBack={handleBack}
/>
);
default:
return (
<div className="text-center py-8">
<p className="text-gray-500">View not found</p>
<Button onClick={() => setCurrentView('dashboard')} className="mt-4">
Return to Dashboard
</Button>
</div>
);
}
};
return (
<div className="min-h-screen bg-gray-100">
<div className="bg-white shadow">
<div className="max-w-7xl mx-auto px-4 sm:px-6 lg:px-8">
<div className="flex justify-between items-center py-6">
<div className="flex items-center space-x-4">
<h1 className="text-3xl font-bold text-gray-900">
Snowflake Analytics Dashboard
</h1>
{currentView !== 'dashboard' && (
<Button onClick={() => setCurrentView('dashboard')} variant="outline">
Dashboard
</Button>
)}
</div>
<div className="flex items-center space-x-2 text-sm text-gray-500">
<ClockIcon className="w-4 h-4" />
<span>Last updated: {new Date().toLocaleString()}</span>
</div>
</div>
</div>
</div>
<main className="max-w-7xl mx-auto py-6 sm:px-6 lg:px-8">
<div className="px-4 py-6 sm:px-0">
{renderCurrentView()}
</div>
</main>
</div>
);
};
export default App;
Enter fullscreen mode
Exit fullscreen mode
Top comments (0)