Spaces:
Sleeping
Sleeping
File size: 4,472 Bytes
035d781 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 |
import sqlite3
import pandas as pd
print("EXPLORING RESOURCE OPTIMIZATION DATA\n")
# Connect to database
conn = sqlite3.connect('resource_optimization.db')
# ==================== QUERY 1: Service Overview ====================
print("="*100)
print("SERVICE OVERVIEW")
print("="*100)
query1 = """
SELECT
service_id,
service_name,
memory_mb,
cpu_cores,
latency_critical,
traffic_volume_rps,
dependencies
FROM services
ORDER BY traffic_volume_rps DESC
LIMIT 10
"""
df1 = pd.read_sql_query(query1, conn)
print(df1.to_string(index=False))
print()
# ==================== QUERY 2: Regional Latency Summary ====================
print("="*100)
print("REGIONAL LATENCY MATRIX (average ms)")
print("="*100)
query2 = """
SELECT
region1,
region2,
ROUND(AVG(latency_ms), 2) as avg_latency_ms,
ROUND(MIN(latency_ms), 2) as min_latency_ms,
ROUND(MAX(latency_ms), 2) as max_latency_ms,
COUNT(*) as samples
FROM regional_latency
GROUP BY region1, region2
ORDER BY region1, region2
"""
df2 = pd.read_sql_query(query2, conn)
print(df2.to_string(index=False))
print()
# ==================== QUERY 3: Traffic by Region ====================
print("="*100)
print("TOTAL TRAFFIC BY REGION")
print("="*100)
query3 = """
SELECT
region,
SUM(requests) as total_requests,
ROUND(AVG(requests), 0) as avg_hourly_requests,
COUNT(DISTINCT service_id) as num_services
FROM traffic_patterns
GROUP BY region
ORDER BY total_requests DESC
"""
df3 = pd.read_sql_query(query3, conn)
print(df3.to_string(index=False))
print()
# ==================== QUERY 4: Services by Placement Count ====================
print("="*100)
print("SERVICE PLACEMENT DISTRIBUTION")
print("="*100)
query4 = """
SELECT
s.service_id,
s.service_name,
COUNT(DISTINCT sp.region) as num_regions,
ROUND(AVG(sp.avg_latency_ms), 2) as avg_latency_ms,
ROUND(AVG(sp.error_rate), 4) as avg_error_rate
FROM services s
LEFT JOIN service_placement sp ON s.service_id = sp.service_id
GROUP BY s.service_id
ORDER BY num_regions DESC, s.service_name
"""
df4 = pd.read_sql_query(query4, conn)
print(df4.to_string(index=False))
print()
# ==================== QUERY 5: Peak Traffic Hours ====================
print("="*100)
print("PEAK TRAFFIC HOURS (all regions combined)")
print("="*100)
query5 = """
SELECT
hour,
SUM(requests) as total_requests,
ROUND(AVG(requests), 0) as avg_requests_per_service_region
FROM traffic_patterns
GROUP BY hour
ORDER BY total_requests DESC
LIMIT 10
"""
df5 = pd.read_sql_query(query5, conn)
print(df5.to_string(index=False))
print()
# ==================== QUERY 6: Cross-Region Traffic Analysis ====================
print("="*100)
print("HIGH LATENCY REGION PAIRS (average > 100ms)")
print("="*100)
query6 = """
SELECT
region1,
region2,
ROUND(AVG(latency_ms), 2) as avg_latency_ms
FROM regional_latency
GROUP BY region1, region2
HAVING AVG(latency_ms) > 100
ORDER BY avg_latency_ms DESC
"""
df6 = pd.read_sql_query(query6, conn)
print(df6.to_string(index=False))
print()
# ==================== QUERY 7: Latency Critical Services ====================
print("="*100)
print("LATENCY CRITICAL SERVICES")
print("="*100)
query7 = """
SELECT
service_id,
service_name,
memory_mb,
traffic_volume_rps,
dependencies
FROM services
WHERE latency_critical = 1
ORDER BY traffic_volume_rps DESC
"""
df7 = pd.read_sql_query(query7, conn)
print(df7.to_string(index=False))
print()
# ==================== SUMMARY STATS ====================
print("="*100)
print("SUMMARY STATISTICS")
print("="*100)
query_summary = "SELECT COUNT(*) as total_services FROM services"
total_services = pd.read_sql_query(query_summary, conn).iloc[0, 0]
query_summary = "SELECT COUNT(DISTINCT region) as num_regions FROM traffic_patterns"
num_regions = pd.read_sql_query(query_summary, conn).iloc[0, 0]
query_summary = "SELECT SUM(requests) as total_traffic FROM traffic_patterns"
total_traffic = pd.read_sql_query(query_summary, conn).iloc[0, 0]
query_summary = "SELECT ROUND(AVG(latency_ms), 2) as avg_latency FROM regional_latency"
avg_latency = pd.read_sql_query(query_summary, conn).iloc[0, 0]
print(f"• Total Services: {total_services}")
print(f"• Total Regions: {num_regions}")
print(f"• Total Traffic Records: {total_traffic:,}")
print(f"• Average Cross-Region Latency: {avg_latency} ms")
print()
conn.close()
print("="*100)
print("✅ DATA EXPLORATION COMPLETE!")
print("="*100)
|