aankitdas/resource-optimization-ml

1import sqlite32import pandas as pd3 4print("EXPLORING RESOURCE OPTIMIZATION DATA\n")5 6# Connect to database7conn = sqlite3.connect('resource_optimization.db')8 9# ==================== QUERY 1: Service Overview ====================10print("="*100)11print("SERVICE OVERVIEW")12print("="*100)13 14query1 = """15SELECT 16    service_id,17    service_name,18    memory_mb,19    cpu_cores,20    latency_critical,21    traffic_volume_rps,22    dependencies23FROM services24ORDER BY traffic_volume_rps DESC25LIMIT 1026"""27 28df1 = pd.read_sql_query(query1, conn)29print(df1.to_string(index=False))30print()31 32# ==================== QUERY 2: Regional Latency Summary ====================33print("="*100)34print("REGIONAL LATENCY MATRIX (average ms)")35print("="*100)36 37query2 = """38SELECT 39    region1,40    region2,41    ROUND(AVG(latency_ms), 2) as avg_latency_ms,42    ROUND(MIN(latency_ms), 2) as min_latency_ms,43    ROUND(MAX(latency_ms), 2) as max_latency_ms,44    COUNT(*) as samples45FROM regional_latency46GROUP BY region1, region247ORDER BY region1, region248"""49 50df2 = pd.read_sql_query(query2, conn)51print(df2.to_string(index=False))52print()53 54# ==================== QUERY 3: Traffic by Region ====================55print("="*100)56print("TOTAL TRAFFIC BY REGION")57print("="*100)58 59query3 = """60SELECT 61    region,62    SUM(requests) as total_requests,63    ROUND(AVG(requests), 0) as avg_hourly_requests,64    COUNT(DISTINCT service_id) as num_services65FROM traffic_patterns66GROUP BY region67ORDER BY total_requests DESC68"""69 70df3 = pd.read_sql_query(query3, conn)71print(df3.to_string(index=False))72print()73 74# ==================== QUERY 4: Services by Placement Count ====================75print("="*100)76print("SERVICE PLACEMENT DISTRIBUTION")77print("="*100)78 79query4 = """80SELECT 81    s.service_id,82    s.service_name,83    COUNT(DISTINCT sp.region) as num_regions,84    ROUND(AVG(sp.avg_latency_ms), 2) as avg_latency_ms,85    ROUND(AVG(sp.error_rate), 4) as avg_error_rate86FROM services s87LEFT JOIN service_placement sp ON s.service_id = sp.service_id88GROUP BY s.service_id89ORDER BY num_regions DESC, s.service_name90"""91 92df4 = pd.read_sql_query(query4, conn)93print(df4.to_string(index=False))94print()95 96# ==================== QUERY 5: Peak Traffic Hours ====================97print("="*100)98print("PEAK TRAFFIC HOURS (all regions combined)")99print("="*100)100 101query5 = """102SELECT 103    hour,104    SUM(requests) as total_requests,105    ROUND(AVG(requests), 0) as avg_requests_per_service_region106FROM traffic_patterns107GROUP BY hour108ORDER BY total_requests DESC109LIMIT 10110"""111 112df5 = pd.read_sql_query(query5, conn)113print(df5.to_string(index=False))114print()115 116# ==================== QUERY 6: Cross-Region Traffic Analysis ====================117print("="*100)118print("HIGH LATENCY REGION PAIRS (average > 100ms)")119print("="*100)120 121query6 = """122SELECT 123    region1,124    region2,125    ROUND(AVG(latency_ms), 2) as avg_latency_ms126FROM regional_latency127GROUP BY region1, region2128HAVING AVG(latency_ms) > 100129ORDER BY avg_latency_ms DESC130"""131 132df6 = pd.read_sql_query(query6, conn)133print(df6.to_string(index=False))134print()135 136# ==================== QUERY 7: Latency Critical Services ====================137print("="*100)138print("LATENCY CRITICAL SERVICES")139print("="*100)140 141query7 = """142SELECT 143    service_id,144    service_name,145    memory_mb,146    traffic_volume_rps,147    dependencies148FROM services149WHERE latency_critical = 1150ORDER BY traffic_volume_rps DESC151"""152 153df7 = pd.read_sql_query(query7, conn)154print(df7.to_string(index=False))155print()156 157# ==================== SUMMARY STATS ====================158print("="*100)159print("SUMMARY STATISTICS")160print("="*100)161 162query_summary = "SELECT COUNT(*) as total_services FROM services"163total_services = pd.read_sql_query(query_summary, conn).iloc[0, 0]164 165query_summary = "SELECT COUNT(DISTINCT region) as num_regions FROM traffic_patterns"166num_regions = pd.read_sql_query(query_summary, conn).iloc[0, 0]167 168query_summary = "SELECT SUM(requests) as total_traffic FROM traffic_patterns"169total_traffic = pd.read_sql_query(query_summary, conn).iloc[0, 0]170 171query_summary = "SELECT ROUND(AVG(latency_ms), 2) as avg_latency FROM regional_latency"172avg_latency = pd.read_sql_query(query_summary, conn).iloc[0, 0]173 174print(f"• Total Services: {total_services}")175print(f"• Total Regions: {num_regions}")176print(f"• Total Traffic Records: {total_traffic:,}")177print(f"• Average Cross-Region Latency: {avg_latency} ms")178print()179 180conn.close()181 182print("="*100)183print("✅ DATA EXPLORATION COMPLETE!")184print("="*100)185