import streamlit as st
import pandas as pd
import json
from datetime import datetime
# Configure page
st.set_page_config(
page_title="Snowflake Query Optimizer",
page_icon="❄️",
layout="wide",
initial_sidebar_state="collapsed"
)
# Enhanced CSS for modern UI with proper table styling
st.markdown("""
<style>
/* Import Google Fonts */
@import url('https://fonts.googleapis.com/css2?family=Inter:wght@300;400;500;600;700&display=swap');
/* Reset and base styles */
.main {
padding: 1rem 2rem;
font-family: 'Inter', sans-serif;
background: linear-gradient(135deg, #f5f7fa 0%, #c3cfe2 100%);
min-height: 100vh;
}
/* Header with glassmorphism effect */
.header-container {
background: rgba(255, 255, 255, 0.25);
backdrop-filter: blur(20px);
border: 1px solid rgba(255, 255, 255, 0.18);
padding: 3rem;
border-radius: 25px;
margin-bottom: 3rem;
color: #2c3e50;
text-align: center;
box-shadow: 0 20px 40px rgba(0,0,0,0.1);
position: relative;
overflow: hidden;
}
.header-container::before {
content: '';
position: absolute;
top: -50%;
left: -50%;
width: 200%;
height: 200%;
background: linear-gradient(45deg, transparent, rgba(255,255,255,0.1), transparent);
animation: shimmer 3s infinite;
}
@keyframes shimmer {
0% { transform: translateX(-100%) translateY(-100%) rotate(45deg); }
100% { transform: translateX(100%) translateY(100%) rotate(45deg); }
}
.header-title {
font-size: 3.5rem;
font-weight: 700;
margin-bottom: 1rem;
background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
-webkit-background-clip: text;
-webkit-text-fill-color: transparent;
background-clip: text;
position: relative;
z-index: 1;
}
.header-subtitle {
font-size: 1.4rem;
opacity: 0.8;
font-weight: 400;
color: #34495e;
position: relative;
z-index: 1;
}
/* Floating metrics cards with micro-interactions */
.metrics-container {
display: grid;
grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
gap: 1.5rem;
margin-bottom: 3rem;
}
.metric-card {
background: rgba(255, 255, 255, 0.9);
backdrop-filter: blur(10px);
padding: 2rem;
border-radius: 20px;
border: 1px solid rgba(255, 255, 255, 0.2);
box-shadow: 0 8px 32px rgba(0,0,0,0.1);
transition: all 0.4s cubic-bezier(0.175, 0.885, 0.32, 1.275);
position: relative;
overflow: hidden;
cursor: pointer;
}
.metric-card:hover {
transform: translateY(-10px) scale(1.02);
box-shadow: 0 20px 60px rgba(0,0,0,0.15);
background: rgba(255, 255, 255, 0.95);
}
.metric-card::before {
content: '';
position: absolute;
top: 0;
left: 0;
right: 0;
height: 4px;
background: linear-gradient(90deg, #667eea, #764ba2, #f093fb, #f5576c);
background-size: 400% 400%;
animation: gradient 3s ease infinite;
}
@keyframes gradient {
0% { background-position: 0% 50%; }
50% { background-position: 100% 50%; }
100% { background-position: 0% 50%; }
}
.metric-value {
font-size: 2.8rem;
font-weight: 700;
color: #2c3e50;
margin-bottom: 0.5rem;
line-height: 1;
}
.metric-label {
color: #7f8c8d;
font-size: 0.95rem;
text-transform: uppercase;
letter-spacing: 1.5px;
font-weight: 500;
}
/* Table container styling */
.table-container {
background: rgba(255, 255, 255, 0.95);
backdrop-filter: blur(20px);
border-radius: 25px;
overflow: hidden;
box-shadow: 0 20px 60px rgba(0,0,0,0.1);
margin: 2rem 0;
border: 1px solid rgba(255, 255, 255, 0.2);
}
.table-header {
background: linear-gradient(135deg, #2c3e50 0%, #34495e 100%);
color: white;
padding: 2rem;
font-size: 1.5rem;
font-weight: 600;
text-align: center;
position: relative;
overflow: hidden;
}
.table-header::before {
content: '';
position: absolute;
top: -2px;
left: -2px;
right: -2px;
bottom: -2px;
background: linear-gradient(45deg, #667eea, #764ba2, #f093fb, #f5576c);
z-index: -1;
animation: gradient 3s ease infinite;
}
/* Streamlit dataframe styling */
.stDataFrame {
border-radius: 0 0 25px 25px;
overflow: hidden;
}
.stDataFrame > div {
border-radius: 0 0 25px 25px;
}
/* Details page styling */
.details-container {
background: rgba(255, 255, 255, 0.95);
backdrop-filter: blur(20px);
border-radius: 25px;
padding: 3rem;
margin: 2rem 0;
box-shadow: 0 20px 60px rgba(0,0,0,0.1);
border: 1px solid rgba(255, 255, 255, 0.2);
}
.details-header {
background: linear-gradient(135deg, #2c3e50 0%, #34495e 100%);
color: white;
padding: 2rem;
border-radius: 20px;
margin-bottom: 2rem;
text-align: center;
position: relative;
overflow: hidden;
}
.details-header::before {
content: '';
position: absolute;
top: -50%;
left: -50%;
width: 200%;
height: 200%;
background: linear-gradient(45deg, transparent, rgba(255,255,255,0.1), transparent);
animation: shimmer 3s infinite;
}
.query-box {
background: linear-gradient(135deg, #f8f9fa 0%, #e9ecef 100%);
border: 2px solid #dee2e6;
border-radius: 15px;
padding: 2rem;
margin: 1.5rem 0;
font-family: 'Monaco', 'Menlo', 'Ubuntu Mono', monospace;
white-space: pre-wrap;
max-height: 400px;
overflow-y: auto;
box-shadow: inset 0 4px 12px rgba(0,0,0,0.05);
position: relative;
font-size: 0.95rem;
line-height: 1.5;
}
.query-box::before {
content: '';
position: absolute;
top: 0;
left: 0;
right: 0;
height: 4px;
background: linear-gradient(90deg, #667eea, #764ba2);
border-radius: 15px 15px 0 0;
}
/* Recommendations with enhanced styling */
.recommendation-item {
background: linear-gradient(135deg, #ffeaa7 0%, #fab1a0 100%);
color: #2d3436;
padding: 15px 25px;
margin: 12px 0;
border-radius: 25px;
font-weight: 500;
box-shadow: 0 6px 20px rgba(250, 177, 160, 0.3);
transition: all 0.3s cubic-bezier(0.175, 0.885, 0.32, 1.275);
position: relative;
overflow: hidden;
}
.recommendation-item:hover {
transform: translateX(15px) scale(1.02);
box-shadow: 0 10px 30px rgba(250, 177, 160, 0.4);
}
.recommendation-item::before {
content: '💡';
position: absolute;
left: -30px;
top: 50%;
transform: translateY(-50%);
font-size: 1.2rem;
transition: all 0.3s ease;
}
.recommendation-item:hover::before {
left: 10px;
}
/* Back button with special styling */
.stButton > button {
background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
color: white;
border: none;
border-radius: 12px;
padding: 12px 20px;
font-weight: 600;
font-size: 1rem;
transition: all 0.3s cubic-bezier(0.175, 0.885, 0.32, 1.275);
width: 100%;
margin: 4px;
box-shadow: 0 4px 15px rgba(102, 126, 234, 0.2);
position: relative;
overflow: hidden;
}
.stButton > button:hover {
transform: translateY(-3px) scale(1.05);
box-shadow: 0 10px 30px rgba(102, 126, 234, 0.4);
background: linear-gradient(135deg, #764ba2 0%, #667eea 100%);
}
/* Animation classes */
.fadeInUp {
animation: fadeInUp 0.8s ease-out;
}
.fadeInLeft {
animation: fadeInLeft 0.8s ease-out;
}
.fadeInRight {
animation: fadeInRight 0.8s ease-out;
}
@keyframes fadeInUp {
from {
opacity: 0;
transform: translateY(40px);
}
to {
opacity: 1;
transform: translateY(0);
}
}
@keyframes fadeInLeft {
from {
opacity: 0;
transform: translateX(-40px);
}
to {
opacity: 1;
transform: translateX(0);
}
}
@keyframes fadeInRight {
from {
opacity: 0;
transform: translateX(40px);
}
to {
opacity: 1;
transform: translateX(0);
}
}
/* Responsive design */
@media (max-width: 768px) {
.header-title {
font-size: 2.5rem;
}
.metric-card {
padding: 1.5rem;
}
}
/* Custom styling for clickable cells */
.clickable-info {
background: linear-gradient(135deg, #e3f2fd 0%, #bbdefb 100%);
color: #1565c0;
padding: 1rem;
border-radius: 10px;
margin: 1rem 0;
text-align: center;
font-weight: 600;
border: 2px solid #1976d2;
}
</style>
""", unsafe_allow_html=True)
# Initialize session state
if 'page' not in st.session_state:
st.session_state.page = 'overview'
if 'selected_user' not in st.session_state:
st.session_state.selected_user = None
if 'selected_metric' not in st.session_state:
st.session_state.selected_metric = None
if 'selected_count' not in st.session_state:
st.session_state.selected_count = 0
# Sample data with all columns
def create_sample_data():
"""Create comprehensive sample data matching your query structure"""
data = {
'USER_NAME': ['john_doe', 'jane_smith', 'mike_wilson', 'sarah_jones', 'alex_brown', 'emma_davis', 'ryan_miller', 'lisa_taylor'],
'TOTAL_QUERIES': [1250, 890, 2100, 567, 1800, 945, 1567, 723],
'WAREHOUSES_USED': [3, 2, 5, 2, 4, 3, 4, 2],
'DATABASES_ACCESSED': [4, 3, 6, 2, 5, 4, 5, 3],
'TOTAL_CREDITS': [125.50, 89.30, 310.75, 45.20, 198.40, 112.80, 187.90, 78.60],
'AVG_EXECUTION_TIME_MS': [2500, 1800, 4200, 1200, 3100, 2200, 2800, 1600],
'AVG_BYTES_PER_ROW': [156.7, 123.4, 289.1, 98.2, 234.5, 145.8, 201.3, 112.6],
'TOTAL_DATA_SCANNED_GB': [450.2, 320.8, 890.5, 180.3, 670.1, 380.4, 520.7, 290.1],
'FAILURE_CANCELLATION_RATE_PCT': [2.5, 1.8, 5.2, 0.9, 3.1, 2.1, 3.8, 1.4],
'SPILLED_QUERIES': [45, 23, 89, 12, 67, 34, 56, 19],
'OVER_PROVISIONED_QUERIES': [67, 34, 123, 19, 89, 45, 78, 28],
'PEAK_HOUR_LONG_RUNNING_QUERIES': [23, 15, 56, 8, 34, 18, 41, 12],
'SELECT_STAR_QUERIES': [234, 156, 445, 89, 298, 178, 312, 134],
'UNPARTITIONED_SCAN_QUERIES': [89, 45, 167, 23, 112, 67, 98, 41],
'REPEATED_QUERIES': [345, 234, 567, 123, 456, 289, 398, 201],
'COMPLEX_JOIN_QUERIES': [78, 45, 134, 23, 89, 56, 95, 38],
'ZERO_RESULT_QUERIES': [34, 21, 78, 12, 45, 28, 52, 16],
'HIGH_COMPILE_QUERIES': [56, 32, 98, 18, 67, 41, 73, 25],
'UNTAGGED_QUERIES': [123, 87, 234, 45, 167, 98, 145, 76],
'UNLIMITED_ORDER_BY_QUERIES': [23, 15, 45, 8, 32, 19, 28, 11],
'LARGE_GROUP_BY_QUERIES': [34, 21, 67, 12, 45, 28, 39, 16],
'SLOW_QUERIES': [78, 45, 134, 23, 89, 56, 95, 38],
'EXPENSIVE_DISTINCT_QUERIES': [12, 8, 23, 4, 16, 10, 18, 6],
'INEFFICIENT_LIKE_QUERIES': [45, 28, 78, 15, 56, 34, 49, 22],
'NO_RESULTS_WITH_SCAN_QUERIES': [23, 15, 45, 8, 32, 19, 28, 11],
'CARTESIAN_JOIN_QUERIES': [2, 1, 8, 0, 3, 1, 5, 0],
'HIGH_COMPILE_RATIO_QUERIES': [15, 9, 28, 5, 19, 12, 21, 7],
'WEIGHTED_SCORE': [1250.5, 890.2, 2100.8, 567.3, 1800.6, 945.7, 1567.4, 723.9],
'COST_STATUS': ['Normal', 'Normal', 'High Cost', 'Normal', 'High Cost', 'Normal', 'High Cost', 'Normal'],
'RECOMMENDATIONS': [
['Optimize memory usage or increase warehouse size', 'Specify columns instead of SELECT *'],
['Use smaller warehouses for simple queries', 'Implement query result caching'],
['Reduce data scanned with clustering', 'Schedule long queries off-peak', 'Avoid Cartesian products'],
['Apply query tags for cost attribution', 'Use materialized views for frequent queries'],
['Implement partitioning or clustering', 'Optimize slow-running queries', 'Review warehouse sizing'],
['Cache frequently accessed data', 'Use query result caching'],
['Optimize JOIN operations', 'Implement proper indexing strategies', 'Review query patterns'],
['Use column pruning techniques', 'Implement data compression']
],
'QUERY_SAMPLES': [
{
'spilled': [
{'query_id': 'Q001', 'query_text': 'SELECT cu.customer_id, cu.customer_name, o.order_date, p.product_name\nFROM customers cu\nJOIN orders o ON cu.customer_id = o.customer_id\nJOIN order_items oi ON o.order_id = oi.order_id\nJOIN products p ON oi.product_id = p.product_id\nWHERE o.order_date >= \'2024-01-01\'\nORDER BY o.order_date DESC, cu.customer_name',
'execution_time_ms': 45000, 'bytes_spilled_to_local_storage': 1000000}
],
'select_star': [
{'query_id': 'Q002', 'query_text': 'SELECT * FROM user_activity_logs\nWHERE activity_date BETWEEN \'2024-01-01\' AND \'2024-12-31\'\nLIMIT 1000',
'execution_time_ms': 12000, 'bytes_scanned': 500000000}
]
},
{
'over_provisioned': [
{'query_id': 'Q003', 'query_text': 'SELECT COUNT(*) as total_users\nFROM users\nWHERE status = \'active\'',
'execution_time_ms': 2000, 'warehouse_size': 'LARGE'}
]
},
{
'peak_hour_long_running': [
{'query_id': 'Q004', 'query_text': 'SELECT \n c.category_name,\n COUNT(p.product_id) as product_count,\n AVG(p.price) as avg_price,\n SUM(oi.quantity * p.price) as total_revenue\nFROM categories c\nJOIN products p ON c.category_id = p.category_id\nJOIN order_items oi ON p.product_id = oi.product_id\nJOIN orders o ON oi.order_id = o.order_id\nWHERE o.order_date >= \'2023-01-01\'\nGROUP BY c.category_name\nORDER BY total_revenue DESC',
'execution_time_ms': 450000, 'start_time': '2024-01-15 14:30:00'}
],
'cartesian_join': [
{'query_id': 'Q005', 'query_text': 'SELECT u.username, p.product_name\nFROM users u\nCROSS JOIN products p\nWHERE u.created_date > \'2024-01-01\'',
'execution_time_ms': 180000, 'rows_produced': 1000000}
]
},
{
'zero_result': [
{'query_id': 'Q006', 'query_text': 'SELECT name, email, phone\nFROM customers\nWHERE status = \'inactive\'\n AND last_login_date < \'2020-01-01\'\n AND account_balance > 1000',
'execution_time_ms': 5000}
]
},
{
'unpartitioned_scan': [
{'query_id': 'Q007', 'query_text': 'SELECT \n event_type,\n COUNT(*) as event_count,\n DATE_TRUNC(\'day\', event_timestamp) as event_date\nFROM system_events\nWHERE event_timestamp BETWEEN \'2024-01-01\' AND \'2024-12-31\'\n AND severity_level = \'ERROR\'\nGROUP BY event_type, DATE_TRUNC(\'day\', event_timestamp)\nORDER BY event_date DESC, event_count DESC',
'execution_time_ms': 89000, 'partitions_scanned': 365, 'partitions_total': 365}
],
'slow_query': [
{'query_id': 'Q008', 'query_text': 'WITH customer_metrics AS (\n SELECT \n customer_id,\n COUNT(order_id) as total_orders,\n SUM(order_amount) as total_spent,\n AVG(order_amount) as avg_order_value\n FROM orders\n WHERE order_date >= \'2023-01-01\'\n GROUP BY customer_id\n),\nranked_customers AS (\n SELECT *,\n ROW_NUMBER() OVER (ORDER BY total_spent DESC) as spending_rank\n FROM customer_metrics\n)\nSELECT c.customer_name, rc.*\nFROM ranked_customers rc\nJOIN customers c ON rc.customer_id = c.customer_id\nWHERE rc.total_spent > 10000\nORDER BY rc.spending_rank',
'execution_time_ms': 125000, 'bytes_scanned': 2000000000}
]
},
# Add more sample data for other users
{'over_provisioned': [{'query_id': 'Q009', 'query_text': 'SELECT status FROM orders LIMIT 10', 'execution_time_ms': 1500, 'warehouse_size': 'XLARGE'}]},
{'select_star': [{'query_id': 'Q010', 'query_text': 'SELECT * FROM inventory WHERE stock_level < 10', 'execution_time_ms': 8000, 'bytes_scanned': 300000000}]},
{'peak_hour_long_running': [{'query_id': 'Q011', 'query_text': 'SELECT region, SUM(sales) FROM sales_data GROUP BY region', 'execution_time_ms': 380000, 'start_time': '2024-02-10 15:45:00'}]}
]
}
return pd.DataFrame(data)
def render_header():
"""Render the enhanced header section"""
st.markdown("""
<div class="header-container fadeInUp">
<div class="header-title">❄️ Snowflake Query Optimizer</div>
<div class="header-subtitle">Advanced Query Performance Analysis & Optimization Dashboard</div>
</div>
""", unsafe_allow_html=True)
def render_overview_metrics(df):
"""Render overview metrics with enhanced styling"""
st.markdown('<div class="metrics-container fadeInUp">', unsafe_allow_html=True)
col1, col2, col3, col4 = st.columns(4)
metrics = [
(col1, len(df), "Total Users", "👥"),
(col2, df['TOTAL_QUERIES'].sum(), "Total Queries", "🔍"),
(col3, f"${df['TOTAL_CREDITS'].sum():.2f}", "Total Credits", "💰"),
(col4, len(df[df['COST_STATUS'] == 'High Cost']), "High Cost Users", "⚠️")
]
for col, value, label, icon in metrics:
with col:
st.markdown(f"""
<div class="metric-card">
<div class="metric-value">{icon} {value:}</div>
<div class="metric-label">{label}</div>
</div>
""", unsafe_allow_html=True)
st.markdown('</div>', unsafe_allow_html=True)
def handle_cell_click(user_name, metric_name, count):
"""Handle cell click by updating session state"""
st.session_state.page = 'details'
st.session_state.selected_user = user_name
st.session_state.selected_metric = metric_name
st.session_state.selected_count = count
def format_dataframe_for_display(df):
"""Format the dataframe for better display"""
# Get all columns except the ones we want to exclude
exclude_columns = ['RECOMMENDATIONS', 'QUERY_SAMPLES']
display_df = df[[col for col in df.columns if col not in exclude_columns]].copy()
# Format numeric columns for better display
for col in display_df.columns:
if col in ['TOTAL_CREDITS']:
display_df[col] = display_df[col].apply(lambda x: f"${x:.2f}")
elif col in ['AVG_EXECUTION_TIME_MS']:
display_df[col] = display_df[col].apply(lambda x: f"{x:,.0f}ms")
elif col in ['TOTAL_DATA_SCANNED_GB']:
display_df[col] = display_df[col].apply(lambda x: f"{x:.1f}GB")
elif col in ['FAILURE_CANCELLATION_RATE_PCT']:
display_df[col] = display_df[col].apply(lambda x: f"{x:.1f}%")
elif col in ['AVG_BYTES_PER_ROW']:
display_df[col] = display_df[col].apply(lambda x: f"{x:.1f}B")
elif col in ['WEIGHTED_SCORE']:
display_df[col] = display_df[col].apply(lambda x: f"{x:.1f}")
elif col == 'COST_STATUS':
display_df[col] = display_df[col].apply(lambda x: f"{'🔥' if x == 'High Cost' else '✅'} {x}")
elif col == 'USER_NAME':
display_df[col] = display_df[col].apply(lambda x: f"👤 {x}")
elif isinstance(display_df[col].iloc[0], (int, float)) and col not in ['TOTAL_CREDITS', 'AVG_EXECUTION_TIME_MS', 'TOTAL_DATA_SCANNED_GB', 'FAILURE_CANCELLATION_RATE_PCT', 'AVG_BYTES_PER_ROW', 'WEIGHTED_SCORE']:
display_df[col] = display_df[col].apply(lambda x: f"{x:,}")
# Rename columns for better display
column_mapping = {
'USER_NAME': '👤 User',
'TOTAL_QUERIES': 'Total Queries',
'WAREHOUSES_USED': 'Warehouses',
'DATABASES_ACCESSED': 'Databases',
'TOTAL_CREDITS': '💰 Credits',
'AVG_EXECUTION_TIME_MS': '⏱️ Avg Time',
'AVG_BYTES_PER_ROW': '📊 Bytes/Row',
'TOTAL_DATA_SCANNED_GB': '📈 Data Scanned',
'FAILURE_CANCELLATION_RATE_PCT': '❌ Failure Rate',
'SPILLED_QUERIES': '💾 Spilled',
'OVER_PROVISIONED_QUERIES': '📈 Over Prov.',
'PEAK_HOUR_LONG_RUNNING_QUERIES': '⏰ Peak Long',
'SELECT_STAR_QUERIES': '⭐ Select *',
'UNPARTITIONED_SCAN_QUERIES': '🔍 Unpart. Scan',
'REPEATED_QUERIES': '🔄 Repeated',
'COMPLEX_JOIN_QUERIES': '🔗 Complex Join',
'ZERO_RESULT_QUERIES': '🚫 Zero Results',
'HIGH_COMPILE_QUERIES': '⚙️ High Compile',
'UNTAGGED_QUERIES': '🏷️ Untagged',
'UNLIMITED_ORDER_BY_QUERIES': '📊 Unlim. Order',
'LARGE_GROUP_BY_QUERIES': '📈 Large Group',
'SLOW_QUERIES': '🐌 Slow',
'EXPENSIVE_DISTINCT_QUERIES': '💎 Exp. Distinct',
'INEFFICIENT_LIKE_QUERIES': '🔍 Ineffic. Like',
'NO_RESULTS_WITH_SCAN_QUERIES': '🚫 No Results',
'CARTESIAN_JOIN_QUERIES': '🔗 Cartesian',
'HIGH_COMPILE_RATIO_QUERIES': '⚙️ High Ratio',
'WEIGHTED_SCORE': '🎯 Score',
'COST_STATUS': '💸 Status'
}
display_df = display_df.rename(columns=column_mapping)
return display_df
def render_interactive_table(df):
"""Render an interactive table using Streamlit's native components"""
st.markdown('<div class="table-container fadeInUp">', unsafe_allow_html=True)
st.markdown('<div class="table-header">🎯 Complete Query Performance Analysis</div>', unsafe_allow_html=True)
# Format the dataframe for display
display_df = format_dataframe_for_display(df)
# Show instructions
st.markdown("""
<div class="clickable-info">
📌 <strong>How to interact:</strong> Click on any row in the table below to select a user, then use the metric buttons to view detailed analysis.
</div>
""", unsafe_allow_html=True)
# Display the dataframe with selection
selected_rows = st.dataframe(
display_df,
use_container_width=True,
hide_index=True,
on_select="rerun",
selection_mode="single-row",
height=400
)
st.markdown('</div>', unsafe_allow_html=True)
# Handle row selection
if selected_rows.selection.rows:
selected_idx = selected_rows.selection.rows[0]
selected_user = df.iloc[selected_idx]['USER_NAME']
st.markdown(f"""
<div class="clickable-info">
🎯 <strong>Selected User:</strong> {selected_user} - Click on any metric below to view detailed analysis
</div>
""", unsafe_allow_html=True)
# Define clickable metrics
clickable_metrics = [
('SPILLED_QUERIES', '💾 Spilled Queries'),
('OVER_PROVISIONED_QUERIES', '📈 Over Provisioned'),
('PEAK_HOUR_LONG_RUNNING_QUERIES', '⏰ Peak Long Running'),
('SELECT_STAR_QUERIES', '⭐ Select * Queries'),
('UNPARTITIONED_SCAN_QUERIES', '🔍 Unpartitioned Scan'),
('REPEATED_QUERIES', '🔄 Repeated Queries'),
('COMPLEX_JOIN_QUERIES', '🔗 Complex Joins'),
('ZERO_RESULT_QUERIES', '🚫 Zero Result Queries'),
('HIGH_COMPILE_QUERIES', '⚙️ High Compile Time'),
('UNTAGGED_QUERIES', '🏷️ Untagged Queries'),
('UNLIMITED_ORDER_BY_QUERIES', '📊 Unlimited Order By'),
('LARGE_GROUP_BY_QUERIES', '📈 Large Group By'),
('SLOW_QUERIES', '🐌 Slow Queries'),
('EXPENSIVE_DISTINCT_QUERIES', '💎 Expensive Distinct'),
('INEFFICIENT_LIKE_QUERIES', '🔍 Inefficient Like'),
('NO_RESULTS_WITH_SCAN_QUERIES', '🚫 No Results with Scan'),
('CARTESIAN_JOIN_QUERIES', '🔗 Cartesian Joins'),
('HIGH_COMPILE_RATIO_QUERIES', '⚙️ High Compile Ratio')
]
# Create metric buttons in a grid
cols = st.columns(6) # 6 columns for better layout
for i, (metric_col, metric_label) in enumerate(clickable_metrics):
col_idx = i % 6
value = int(df.iloc[selected_idx][metric_col])
with cols[col_idx]:
if value > 0:
if st.button(
f"{metric_label}\n{value:,}",
key=f"metric_{selected_user}_{metric_col}",
help=f"View {value} {metric_label.lower()}",
use_container_width=True,
type="primary"
):
handle_cell_click(selected_user, metric_col, value)
st.rerun()
else:
st.button(
f"{metric_label}\n{value}",
key=f"metric_zero_{selected_user}_{metric_col}",
disabled=True,
use_container_width=True,
help="No queries of this type"
)
else:
st.markdown("""
<div class="clickable-info">
👆 <strong>Select a user:</strong> Click on any row in the table above to view available metrics for that user
</div>
""", unsafe_allow_html=True)
def render_query_details(df, user_name, metric_name, count):
"""Render detailed query information with enhanced UI"""
# Enhanced back button
col1, col2, col3 = st.columns([1, 2, 1])
with col1:
if st.button("← Back to Overview", key="back_button", help="Return to main dashboard", use_container_width=True):
st.session_state.page = 'overview'
st.rerun()
# Get user data
user_data = df[df['USER_NAME'] == user_name].iloc[0]
# Enhanced header with animation
metric_display_name = metric_name.replace('_', ' ').title()
metric_icon = {
'SPILLED_QUERIES': '💾',
'OVER_PROVISIONED_QUERIES': '📈',
'PEAK_HOUR_LONG_RUNNING_QUERIES': '⏰',
'SELECT_STAR_QUERIES': '⭐',
'UNPARTITIONED_SCAN_QUERIES': '🔍',
'ZERO_RESULT_QUERIES': '🚫',
'SLOW_QUERIES': '🐌',
'CARTESIAN_JOIN_QUERIES': '🔗',
'REPEATED_QUERIES': '🔄',
'COMPLEX_JOIN_QUERIES': '🔗',
'HIGH_COMPILE_QUERIES': '⚙️',
'UNTAGGED_QUERIES': '🏷️',
'UNLIMITED_ORDER_BY_QUERIES': '📊',
'LARGE_GROUP_BY_QUERIES': '📈',
'EXPENSIVE_DISTINCT_QUERIES': '💎',
'INEFFICIENT_LIKE_QUERIES': '🔍',
'NO_RESULTS_WITH_SCAN_QUERIES': '🚫',
'HIGH_COMPILE_RATIO_QUERIES': '⚙️'
}.get(metric_name, '📊')
st.markdown(f"""
<div class="details-container fadeInUp">
<div class="details-header">
<h1>{metric_icon} {user_name}</h1>
<h2>{metric_display_name}</h2>
<p style="font-size: 1.2rem; margin-top: 1rem; opacity: 0.9;">
Found <strong>{count:,}</strong> queries of this type
</p>
</div>
</div>
""", unsafe_allow_html=True)
# Enhanced user overview metrics
st.markdown("### 📈 User Performance Overview")
col1, col2, col3, col4 = st.columns(4)
metrics_data = [
(col1, "Total Queries", f"{user_data['TOTAL_QUERIES']:,}", "🔍"),
(col2, "Total Credits", f"${user_data['TOTAL_CREDITS']:.2f}", "💰"),
(col3, "Cost Status", user_data['COST_STATUS'], "💸"),
(col4, "Current Issue", f"{count:,} {metric_display_name}", metric_icon)
]
for col, label, value, icon in metrics_data:
with col:
st.markdown(f"""
<div class="metric-card fadeInUp">
<div class="metric-value">{icon} {value}</div>
<div class="metric-label">{label}</div>
</div>
""", unsafe_allow_html=True)
# Enhanced recommendations section
if user_data['RECOMMENDATIONS']:
st.markdown("### 🎯 Optimization Recommendations")
st.markdown('<div class="fadeInLeft">', unsafe_allow_html=True)
for i, rec in enumerate(user_data['RECOMMENDATIONS']):
st.markdown(f'<div class="recommendation-item" style="animation-delay: {i*0.1}s;">{rec}</div>', unsafe_allow_html=True)
st.markdown('</div>', unsafe_allow_html=True)
# Enhanced sample queries section
st.markdown("### 📋 Sample Query Analysis")
# Get query samples for this metric
query_samples = user_data['QUERY_SAMPLES']
if isinstance(query_samples, dict):
# Map metric names to sample keys
metric_key_mapping = {
'SPILLED_QUERIES': 'spilled',
'OVER_PROVISIONED_QUERIES': 'over_provisioned',
'PEAK_HOUR_LONG_RUNNING_QUERIES': 'peak_hour_long_running',
'SELECT_STAR_QUERIES': 'select_star',
'UNPARTITIONED_SCAN_QUERIES': 'unpartitioned_scan',
'ZERO_RESULT_QUERIES': 'zero_result',
'SLOW_QUERIES': 'slow_query',
'CARTESIAN_JOIN_QUERIES': 'cartesian_join',
'REPEATED_QUERIES': 'repeated',
'COMPLEX_JOIN_QUERIES': 'complex_join',
'HIGH_COMPILE_QUERIES': 'high_compile',
'UNTAGGED_QUERIES': 'untagged',
'UNLIMITED_ORDER_BY_QUERIES': 'unlimited_order_by',
'LARGE_GROUP_BY_QUERIES': 'large_group_by',
'EXPENSIVE_DISTINCT_QUERIES': 'expensive_distinct',
'INEFFICIENT_LIKE_QUERIES': 'inefficient_like',
'NO_RESULTS_WITH_SCAN_QUERIES': 'no_results_with_scan',
'HIGH_COMPILE_RATIO_QUERIES': 'high_compile_ratio'
}
metric_key = metric_key_mapping.get(metric_name)
if metric_key and metric_key in query_samples and query_samples[metric_key]:
samples = query_samples[metric_key]
for i, query in enumerate(samples[:3]): # Show up to 3 samples
with st.expander(f"🔍 Query Sample {i+1}: {query['query_id']}", expanded=(i==0)):
col1, col2 = st.columns([2.5, 1])
with col1:
st.markdown("**📝 Query Text:**")
st.markdown(f'<div class="query-box fadeInUp">{query["query_text"]}</div>', unsafe_allow_html=True)
with col2:
st.markdown("**📊 Performance Metrics:**")
# Create metric cards for query details
if 'execution_time_ms' in query:
st.markdown(f"""
<div class="metric-card" style="margin-bottom: 1rem;">
<div class="metric-value">⏱️ {query['execution_time_ms']:,}ms</div>
<div class="metric-label">Execution Time</div>
</div>
""", unsafe_allow_html=True)
if 'bytes_scanned' in query:
bytes_gb = query['bytes_scanned'] / (1024**3)
st.markdown(f"""
<div class="metric-card" style="margin-bottom: 1rem;">
<div class="metric-value">📊 {bytes_gb:.2f}GB</div>
<div class="metric-label">Data Scanned</div>
</div>
""", unsafe_allow_html=True)
if 'warehouse_size' in query:
st.markdown(f"""
<div class="metric-card" style="margin-bottom: 1rem;">
<div class="metric-value">🏭 {query['warehouse_size']}</div>
<div class="metric-label">Warehouse Size</div>
</div>
""", unsafe_allow_html=True)
if 'rows_produced' in query:
st.markdown(f"""
<div class="metric-card" style="margin-bottom: 1rem;">
<div class="metric-value">📈 {query['rows_produced']:,}</div>
<div class="metric-label">Rows Produced</div>
</div>
""", unsafe_allow_html=True)
if 'bytes_spilled_to_local_storage' in query:
spilled_mb = query['bytes_spilled_to_local_storage'] / (1024**2)
st.markdown(f"""
<div class="metric-card" style="margin-bottom: 1rem;">
<div class="metric-value">💾 {spilled_mb:.1f}MB</div>
<div class="metric-label">Spilled to Storage</div>
</div>
""", unsafe_allow_html=True)
if 'partitions_scanned' in query and 'partitions_total' in query:
efficiency = (1 - query['partitions_scanned'] / query['partitions_total']) * 100
st.markdown(f"""
<div class="metric-card" style="margin-bottom: 1rem;">
<div class="metric-value">🎯 {efficiency:.1f}%</div>
<div class="metric-label">Partition Efficiency</div>
</div>
""", unsafe_allow_html=True)
if 'start_time' in query:
st.markdown(f"""
<div class="metric-card" style="margin-bottom: 1rem;">
<div class="metric-value">🕐 {query['start_time']}</div>
<div class="metric-label">Start Time</div>
</div>
""", unsafe_allow_html=True)
else:
st.markdown("""
<div style="text-align: center; padding: 3rem; background: rgba(255, 255, 255, 0.8); border-radius: 15px; margin: 2rem 0;">
<h3>🔍 No Sample Queries Available</h3>
<p style="color: #7f8c8d; font-size: 1.1rem;">
Sample queries for this metric are not available in the current dataset.
</p>
</div>
""", unsafe_allow_html=True)
else:
st.markdown("""
<div style="text-align: center; padding: 3rem; background: rgba(255, 255, 255, 0.8); border-radius: 15px; margin: 2rem 0;">
<h3>🔍 Query Data Unavailable</h3>
<p style="color: #7f8c8d; font-size: 1.1rem;">
Query sample data structure needs to be updated for this user.
</p>
</div>
""", unsafe_allow_html=True)
# Main application logic
def main():
# Load data
df = create_sample_data()
# Render header
render_header()
# Route to appropriate page
if st.session_state.page == 'overview':
render_overview_metrics(df)
render_interactive_table(df)
# Add footer with additional info
st.markdown("""
<div style="text-align: center; margin-top: 3rem; padding: 2rem; background: rgba(255, 255, 255, 0.8); border-radius: 15px;">
<h4 style="color: #2c3e50; margin-bottom: 1rem;">💡 How to Use This Dashboard</h4>
<p style="color: #7f8c8d; font-size: 1rem; line-height: 1.6;">
<strong>Step 1:</strong> Click on any row in the table to select a user<br>
<strong>Step 2:</strong> Click on any metric button below the table to view detailed analysis<br>
<strong>Step 3:</strong> Explore query samples and optimization recommendations
</p>
</div>
""", unsafe_allow_html=True)
elif st.session_state.page == 'details':
render_query_details(
df,
st.session_state.selected_user,
st.session_state.selected_metric,
st.session_state.selected_count
)
if __name__ == "__main__":
main()
Armaan Khan
Posted on
Dashboard 1
For further actions, you may consider blocking this person and/or reporting abuse
Top comments (0)