26Y01d6
"""
step6_excel_builder.py — 고도화 11대 메트릭 통합형 리포트 빌더 및 배포 자동화 스크립트 (실시간 로그 강화판)
"""
import os
import sys
import boto3
import pandas as pd
import numpy as np
from pathlib import Path
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.drawing.image import Image as XLImage
from openpyxl.formatting.rule import ColorScaleRule, DataBarRule
# config 로드하여 강제 변수 매싱 바인딩
import config
from config import MERGED_DIR, PLOT_DIR, OUT_DIR
C = dict(
hdr_dark="1F4E79", hdr_mid="2E75B6", hdr_light="BDD7EE",
accent="ED7D31", red="C00000", green="70AD47",
yellow="FFC000", gray_row="F2F2F2", white="FFFFFF",
border="9DC3E6", summary_bg="EBF3FB", purple="7030A0",
teal="008080",
)
def ft(bold=False, size=10, color="000000", name="Arial"):
return Font(name=name, bold=bold, size=size, color=color)
def fill(hex_color):
return PatternFill("solid", fgColor=hex_color)
def thin_border():
t = Side(style="thin", color=C["border"])
return Border(left=t, right=t, top=t, bottom=t)
def center(wrap=False):
return Alignment(horizontal="center", vertical="center", wrap_text=wrap)
def left(wrap=False):
return Alignment(horizontal="left", vertical="center", wrap_text=wrap)
def set_col_widths(ws, widths):
for col, w in widths.items():
ws.column_dimensions[col].width = w
def apply_header_row(ws, row_idx, headers, bg=None, fg=C["white"], size=10):
bg = bg or C["hdr_dark"]
for c, h in enumerate(headers, 1):
cell = ws.cell(row=row_idx, column=c, value=h)
cell.font = ft(bold=True, size=size, color=fg)
cell.fill = fill(bg)
cell.alignment = center(wrap=True)
cell.border = thin_border()
def apply_data_rows(ws, df, start_row, num_formats=None, status_col_idx=None, zebra=True):
nf = num_formats or {}
for r_offset, row in enumerate(df.itertuples(index=False)):
row_num = start_row + r_offset
bg_hex = C["gray_row"] if (r_offset % 2 == 1 and zebra) else C["white"]
for c_idx, val in enumerate(row, 1):
cell = ws.cell(row=row_num, column=c_idx, value=val)
cell.font = ft(size=9)
cell.fill = fill(bg_hex)
cell.alignment = left()
cell.border = thin_border()
if c_idx in nf:
cell.number_format = nf[c_idx]
if status_col_idx and c_idx == status_col_idx:
v = str(val)
if "OOM" in v or "Killed" in v:
cell.fill = fill("FFCCCC"); cell.font = ft(bold=True, size=9, color=C["red"])
elif "Shortage" in v or "부족" in v:
cell.fill = fill("FFF2CC"); cell.font = ft(bold=True, size=9, color="7F6000")
elif "Over" in v or "과다" in v:
cell.fill = fill("DDEEFF"); cell.font = ft(bold=True, size=9, color=C["hdr_dark"])
elif "Optim" in v or "최적" in v:
cell.fill = fill("E2EFDA"); cell.font = ft(bold=True, size=9, color="375623")
return start_row + len(df)
def freeze_and_filter(ws, row=2):
ws.freeze_panes = ws.cell(row=row+1, column=1)
ws.auto_filter.ref = ws.dimensions
def add_chart_image(ws, path_key, anchor_cell, w=860, h=400, label=None):
path = PLOT_DIR / path_key
if not path.exists():
print(f" ⚠️ [주의] 임베딩용 차트 파일 유실됨: {path_key}")
return
row_num = int(''.join(filter(str.isdigit, anchor_cell)))
if label:
ws.cell(row=row_num-1, column=1, value=label).font = ft(bold=True, size=11, color=C["hdr_dark"])
img = XLImage(str(path))
img.width = w; img.height = h
ws.add_image(img, anchor_cell)
# ─── Sheet 0: 전사 종합 요약 ───────────────────────────────
def build_sheet_summary(wb, df_pod, df_ns, infra_tag):
print(" [0/8] '0. 전사종합요약' 시트 렌더링 시작...")
ws = wb.active
ws.title = "0. 전사종합요약"
ws.sheet_view.showGridLines = False
ws.row_dimensions[1].height = 42
ws.merge_cells("A1:H1")
t = ws["A1"]
t.value = f"Resource Governance Master Report [{infra_tag}] (KST 기준)"
t.font = ft(bold=True, size=15, color=C["white"])
t.fill = fill(C["hdr_dark"]); t.alignment = center()
print(" -> 주요 결산 핵심 메트릭(KPI) 집계 계산 중...")
oom_cnt = int(df_pod["is_oom_killed"].sum())
no_req_cnt = int((df_pod["has_no_request"] | df_pod["has_no_limit"]).sum())
alloc_ch = df_pod["cpu_allocated_core_hours"].sum()
usage_ch = df_pod["cpu_usage_core_hours"].sum()
waste_ch = df_pod["cpu_waste_core_hours"].sum()
eff_pct = usage_ch / max(alloc_ch, 0.001) * 100
mem_waste = df_pod["mem_waste_gb_hours"].sum()
pv_waste = df_pod["pv_waste_gb_hours"].sum()
throttle_risks = int((df_pod["cpu_throttled_max"] > 0.2).sum())
kst_dates = sorted(df_pod["date"].unique())
date_range = f"{kst_dates[0]} ~ {kst_dates[-1]} (KST)"
kpis = [
("정산 대상 인프라 도메인", infra_tag, "002060", C["white"]),
("분석 기간 (KST)", date_range, C["hdr_dark"], C["white"]),
("총 관측 컨테이너 볼륨", f"{len(df_pod):,} 개", C["hdr_dark"], C["white"]),
("OOMKilled 장애 컨테이너", f"{oom_cnt:,} 개", C["red"], C["white"]),
("연산 스로틀링 위험군 컨테이너", f"{throttle_risks:,} 개", "C00000", C["white"]),
("리소스 설정 규격 위반군", f"{no_req_cnt:,} 개", "E26B0A", C["white"]),
("전사 CPU 무효 선점 낭비량", f"{waste_ch:,.1f} Core-H", C["hdr_mid"], C["white"]),
("전사 Memory 무효 선점 낭비량", f"{mem_waste:,.1f} GB-H", "6B4F9B", C["white"]),
("전사 PV 스토리지 알박기 낭비량", f"{pv_waste:,.1f} GB-H", "008080", C["white"]),
("전사 CPU 평균 실효 활용률", f"{eff_pct:.1f} %", "375623", C["white"]),
]
print(" -> 엑셀 격자 스코어보드 프레임 주입 중...")
for i, (label, value, bg, fg) in enumerate(kpis):
row = 3 + i
ws.row_dimensions[row].height = 24
lc = ws.cell(row=row, column=1, value=label)
lc.font = ft(bold=True, size=10, color=C["hdr_dark"])
lc.fill = fill(C["summary_bg"]); lc.alignment = left(); lc.border = thin_border()
ws.merge_cells(f"A{row}:C{row}")
vc = ws.cell(row=row, column=4, value=value)
vc.font = ft(bold=True, size=11, color=fg)
vc.fill = fill(bg); vc.alignment = center(); vc.border = thin_border()
ws.merge_cells(f"D{row}:F{row}")
set_col_widths(ws, {"A":28,"B":14,"C":14,"D":22,"E":14,"F":14,"G":20,"H":20})
print(" -> 🎨 요약 배포 차트 앵커링 컴파일 중...")
add_chart_image(ws, "chart6_status_donut.png", "A16", w=420, h=300)
add_chart_image(ws, "chart5_pareto_ns_waste.png", "E16", w=560, h=300)
print(" ✅ '0. 전사종합요약' 시트 구성 완수.")
# 1번 시트
def build_sheet_pareto(wb, df_ns, infra_tag):
print(" [1/8] '1. 파레토분석_NS' 시트 데이터 매핑 개시...")
ws = wb.create_sheet("1. 파레토분석_NS")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:I1")
ws["A1"] = f"Namespace별 CPU Waste 파레토 분석 [{infra_tag}]"
ws["A1"].font = ft(bold=True, size=13, color=C["white"])
ws["A1"].fill = fill(C["hdr_dark"]); ws["A1"].alignment = center()
ws.row_dimensions[1].height = 32
headers = ["Namespace","실행시간 합계(분)","컨테이너 수","할당 Core-H","낭비 Core-H","낭비 비중(%)","누적 비중(%)","등급"]
apply_header_row(ws, 2, headers, bg=C["hdr_mid"])
print(" -> 낭비 총량 기준 파레토 정렬 및 행 기재 처리...")
df_disp = df_ns.copy()
df_disp["등급"] = df_disp["waste_cumsum_pct"].apply(lambda x: "Critical (Top 20%)" if x<=20 else ("High (Top 50%)" if x<=50 else ("Medium" if x<=80 else "Low")))
col_order = ["namespace","minutes_running_sum","container_cnt","total_allocated_core_hours","total_waste_core_hours","waste_share_pct","waste_cumsum_pct","등급"]
end_row = apply_data_rows(ws, df_disp[col_order], start_row=3, num_formats={4:"#,##0.0", 5:"#,##0.0", 6:"0.00", 7:"0.00"})
print(" -> 낭비 지분율 가독성 향상용 데이터바 인젝션 중...")
ws.conditional_formatting.add(f"E3:E{end_row}", DataBarRule(start_type="min", end_type="max", color="2E75B6", showValue=True))
set_col_widths(ws, {"A":22,"B":18,"C":14,"D":16,"E":16,"F":12,"G":12,"H":22})
freeze_and_filter(ws)
print(" ✅ '1. 파레토분석_NS' 시트 구성 완수.")
# 1-2번 시트 (NS 일별 청산)
def build_sheet_ns_daily(wb, df_daily_ns, infra_tag):
print(" [2/8] '1-2. 일단위_NS별_사용량' 가중합 청산 장표 마킹 시작...")
ws = wb.create_sheet("1-2. 일단위_NS별_사용량")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:J1")
ws["A1"] = f"Namespace 일일 자원 소모량 및 통합 사용량 점수 지표 [{infra_tag}]"
ws["A1"].font = ft(bold=True, size=12, color=C["white"])
ws["A1"].fill = fill("1F4E79"); ws["A1"].alignment = center()
ws.row_dimensions[1].height = 35
headers = ["관측 일자(KST)", "네임스페이스(Namespace)", "CPU 사용(Core-H)", "CPU 할당(Core-H)", "CPU 낭비(Core-H)", "Mem 사용(GB-H)", "Mem 할당(GB-H)", "PV 사용(GB-H)", "PV 할당(GB-H)", "통합 사용량 점수 (Score)"]
apply_header_row(ws, 2, headers, bg=C["hdr_mid"])
print(" -> 일자별 테넌트 점수 행 배치 스트림 전송 중...")
col_order = ["date", "namespace", "cpu_used_ch", "cpu_alloc_ch", "cpu_waste_ch", "mem_used_gh", "mem_alloc_gh", "pv_used_gh", "pv_alloc_gh", "final_usage_score"]
df_out = df_daily_ns[col_order].sort_values(by=["date", "final_usage_score"], ascending=[True, False])
nf = {3:"#,##0.0", 4:"#,##0.0", 5:"#,##0.0", 6:"#,##0.0", 7:"#,##0.0", 8:"#,##0.0", 9:"#,##0.0", 10:"#,##0.0"}
end_row = apply_data_rows(ws, df_out, start_row=3, num_formats=nf)
print(" -> 통합 소모 지분율 인프라 스코어 데이터바 그라데이션 주입 중...")
ws.conditional_formatting.add(f"J3:J{end_row}", DataBarRule(start_type="min", end_type="max", color="375623", showValue=True))
set_col_widths(ws, {"A":14, "B":22, "C":18, "D":18, "E":18, "F":18, "G":18, "H":18, "I":18, "J":24})
freeze_and_filter(ws, row=2)
print(" ✅ '1-2. 일단위_NS별_사용량' 시트 구성 완수.")
# 2번 시트
def build_sheet_cpu(wb, df_pod, infra_tag):
top30 = max(1, int(len(df_pod)*0.30))
print(f" [3/8] '2. CPU Request_Usage 분석' 상위 위반 스크리닝 진입 (대상 타겟: {top30}행)...")
ws = wb.create_sheet("2. CPU Request_Usage 분석")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:N1")
ws["A1"] = f"CPU Resource Efficiency Analysis [{infra_tag}]"
ws["A1"].font = ft(bold=True, size=13, color=C["white"])
ws["A1"].fill = fill(C["hdr_dark"]); ws["A1"].alignment = center()
headers = ["날짜(KST)","클러스터","인프라도메인","네임스페이스","워크로드","Pod","컨테이너","CPU Request","CPU Limit","CPU P95","Throttle Peak","활용률(%)","낭비 Core-H","상태"]
apply_header_row(ws, 2, headers, bg=C["hdr_mid"])
df_out = df_pod.sort_values("cpu_waste_core_hours", ascending=False).head(top30).copy()
df_out["util"] = np.where(df_out["cpu_request_max"]>0, (df_out["cpu_usage_p95"]/df_out["cpu_request_max"]*100).round(1), 0)
df_out["status_en"] = df_out["status"].map({"💥 OOM장애발생":"OOM Killed","⚠️ Request부족":"Request Shortage","📉 과다할당":"Over-allocated","✅ 최적화완료":"Optimized"}).fillna("Unknown")
cols = ["date","cluster","workload_domain","namespace","workload_type","pod","container","cpu_request_max","cpu_limit_max","cpu_usage_p95","cpu_throttled_max","util","cpu_waste_core_hours","status_en"]
end_row = apply_data_rows(ws, df_out[cols], start_row=3, num_formats={8:"0.00",9:"0.00",10:"0.00",11:"0.00",12:"0.0",13:"#,##0.0"}, status_col_idx=14)
set_col_widths(ws, {"A":12,"B":14,"C":16,"D":18,"E":16,"F":26,"G":14,"H":12,"I":12,"J":12,"K":14,"L":12,"M":14,"N":16})
freeze_and_filter(ws)
print(" -> 🎨 평균 대 실적 차트 및 Outlier 탐지용 Boxplot 2중 임베딩 중...")
add_chart_image(ws, "chart1_cpu_req_vs_usage_by_workload.png", f"A{end_row+3}", w=800, h=380, label="[ Average CPU Request vs Peak Usage ]")
add_chart_image(ws, "chart9_boxplot_cpu_util_by_workload.png", f"A{end_row+24}", w=800, h=380, label="[ CPU Outliers & Distribution Boxplot ]")
print(" ✅ '2. CPU Request_Usage 분석' 시트 구성 완수.")
# 3번 시트
def build_sheet_memory(wb, df_pod, infra_tag):
top30 = max(1, int(len(df_pod)*0.30))
print(f" [4/8] '3. Memory_PV 입체분석' 다차원 알박기 정산 필터 결합 중...")
ws = wb.create_sheet("3. Memory_PV 입체분석")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:P1")
ws["A1"] = f"Memory RSS & Persistent Volume Storage Cross Analysis [{infra_tag}]"
ws["A1"].font = ft(bold=True, size=13, color=C["white"])
ws["A1"].fill = fill(C["hdr_dark"]); ws["A1"].alignment = center()
headers = ["날짜(KST)","클러스터","인프라도메인","네임스페이스","워크로드","Pod","컨테이너","Mem Request","Mem P95","Mem RSS","PV Cap(GB)","PV Used(GB)","PV Waste(GB-H)","활용률(%)","낭비 GB-H","상태"]
apply_header_row(ws, 2, headers, bg=C["hdr_mid"])
df_out = df_pod.sort_values(by=["mem_waste_gb_hours", "pv_waste_gb_hours"], ascending=[False, False]).head(top30).copy()
df_out["util"] = np.where(df_out["mem_request_max"]>0, (df_out["mem_usage_p95"]/df_out["mem_request_max"]*100).round(1), 0)
df_out["status_en"] = df_out["status"].map({"💥 OOM장애발생":"OOM Killed","⚠️ Request부족":"Request Shortage","📉 과다할당":"Over-allocated","✅ 최적화완료":"Optimized"}).fillna("Unknown")
cols = ["date","cluster","workload_domain","namespace","workload_type","pod","container","mem_request_max","mem_usage_p95","mem_rss_p95","pv_capacity_max","pv_used_p95","pv_waste_gb_hours","util","mem_waste_gb_hours","status_en"]
end_row = apply_data_rows(ws, df_out[cols], start_row=3, num_formats={8:"0.0",9:"0.0",10:"0.0",11:"0.0",12:"0.0",13:"#,##0",14:"0.0",15:"#,##0"}, status_col_idx=16)
set_col_widths(ws, {"A":12,"B":14,"C":16,"D":18,"E":16,"F":26,"G":14,"H":12,"I":12,"J":12,"K":12,"L":12,"M":14,"N":12,"O":12,"P":16})
freeze_and_filter(ws)
print(" -> 🎨 물리 RSS 한계선 분석 및 스토리지 선점 추이 Boxplot 2중 임베딩 중...")
add_chart_image(ws, "chart2_mem_req_vs_usage_by_workload.png", f"A{end_row+3}", w=800, h=380, label="[ Average Memory Specs vs RSS Peak ]")
add_chart_image(ws, "chart10_boxplot_mem_util_by_workload.png", f"A{end_row+24}", w=800, h=380, label="[ Memory Outliers & Distribution Boxplot ]")
print(" ✅ '3. Memory_PV 입체분석' 시트 구성 완수.")
# 4번 시트
def build_sheet_oom(wb, df_pod, infra_tag):
print(" [5/8] '4. 자원부족및OOM장애군' 커널 데드락 용량 부족 필터 격리 중...")
ws = wb.create_sheet("4. 자원부족및OOM장애군")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:M1")
ws["A1"] = f"OOMKilled / CPU CFS Throttled 위험군 [{infra_tag}]"
ws["A1"].font = ft(bold=True, size=13, color=C["white"])
ws["A1"].fill = fill(C["red"]); ws["A1"].alignment = center()
headers = ["날짜(KST)","클러스터","인프라도메인","네임스페이스","워크로드","Pod","컨테이너","상태","CPU Request","CPU P95","Throttle Peak","Mem Limit(GB)","Mem P95(GB)"]
apply_header_row(ws, 2, headers, bg=C["red"])
df_out = df_pod[(df_pod["cpu_shortage_cores"]>0) | (df_pod["is_oom_killed"]) | (df_pod["cpu_throttled_max"] > 0.2)].sort_values(["is_oom_killed","cpu_throttled_max"], ascending=[False,False]).copy()
df_out["status_en"] = df_out["status"].map({"💥 OOM장애발생":"OOM Killed","⚠️ Request부족":"Request Shortage","📉 과다할당":"Over-allocated","✅ 최적화완료":"Optimized"}).fillna("Unknown")
cols = ["date","cluster","workload_domain","namespace","workload_type","pod","container","status_en","cpu_request_max","cpu_usage_p95","cpu_throttled_max","mem_limit_max","mem_usage_p95"]
apply_data_rows(ws, df_out[cols], start_row=3, num_formats={9:"0.00",10:"0.00",11:"0.00",12:"0.0",13:"0.0"}, status_col_idx=8)
set_col_widths(ws, {"A":12,"B":14,"C":16,"D":18,"E":16,"F":28,"G":16,"H":16,"I":12,"J":12,"K":14,"L":14,"M":14})
freeze_and_filter(ws)
print(" ✅ '4. 자원부족및OOM장애군' 시트 구성 완수.")
# 5번 시트
def build_sheet_violations(wb, df_pod, infra_tag):
print(" [6/8] '5. 리소스미설정위반군' 스케줄러 노이즈 유발 블랙리스트 전수 조사 중...")
ws = wb.create_sheet("5. 리소스미설정위반군")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:M1")
ws["A1"] = "Resource Request / Limit 미설정 위반 컨테이너 명세"
ws["A1"].font = ft(bold=True, size=13, color=C["white"])
ws["A1"].fill = fill("E26B0A"); ws["A1"].alignment = center()
headers = ["날짜(KST)","클러스터","인프라도메인","네임스페이스","워크로드","Pod","컨테이너","Request 미설정","Limit 미설정","CPU Request","CPU Limit","Mem Request(GB)","Mem Limit(GB)"]
apply_header_row(ws, 2, headers, bg="E26B0A")
df_out = df_pod[df_pod["has_no_request"]|df_pod["has_no_limit"]].sort_values("minutes_running", ascending=False).copy()
df_out["req_flag"] = df_out["has_no_request"].map({True:"MISSING",False:"OK"})
df_out["lim_flag"] = df_out["has_no_limit"].map({True:"MISSING",False:"OK"})
cols = ["date","cluster","workload_domain","namespace","workload_type","pod","container","req_flag","lim_flag","cpu_request_max","cpu_limit_max","mem_request_max","mem_limit_max"]
apply_data_rows(ws, df_out[cols], start_row=3, num_formats={10:"0.0",11:"0.0",12:"0.0",13:"0.0"})
set_col_widths(ws, {"A":12,"B":14,"C":16,"D":18,"E":16,"F":28,"G":16,"H":14,"I":14,"J":14,"K":14,"L":14,"M":14})
freeze_and_filter(ws)
print(" ✅ '5. 리소스미설정위반군' 시트 구성 완수.")
# 6번 시트
def build_sheet_trends(wb, df_pod, infra_tag):
print(" [7/8] '6. 일별트렌드_KST' 전사 통계 시계열 매트릭스 롤업 중...")
ws = wb.create_sheet("6. 일별트렌드_KST")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:M1")
ws["A1"] = f"Daily Resource Governance Trend [{infra_tag}]"
ws["A1"].font = ft(bold=True, size=13, color=C["white"])
ws["A1"].fill = fill(C["hdr_dark"]); ws["A1"].alignment = center()
df_daily = df_pod.groupby("date").agg(
containers=("container","count"), cpu_alloc=("cpu_allocated_core_hours","sum"), cpu_used=("cpu_usage_core_hours","sum"), cpu_waste=("cpu_waste_core_hours","sum"),
mem_alloc=("mem_allocated_gb_hours","sum"), mem_waste=("mem_waste_gb_hours","sum"),
pv_alloc=("pv_allocated_gb_hours","sum"), pv_waste_sum=("pv_waste_gb_hours","sum"),
oom_cnt=("is_oom_killed","sum"), shortage_cnt=("cpu_shortage_cores", lambda x: (x>0).sum())
).reset_index()
df_daily["cpu_util_pct"] = (df_daily["cpu_used"]/df_daily["cpu_alloc"].clip(lower=0.001)*100).round(1)
df_daily["mem_util_pct"] = ((df_daily["mem_alloc"]-df_daily["mem_waste"])/df_daily["mem_alloc"].clip(lower=0.001)*100).round(1)
headers = ["날짜(KST)","컨테이너 볼륨","CPU 할당 Core-H","CPU 사용 Core-H","CPU 낭비 Core-H","Mem 할당 GB-H","Mem 낭비 GB-H","PV 할당 GB-H","PV 낭비 GB-H","OOM 사살","자원부족군","CPU활용률(%)","Mem활용률(%)"]
apply_header_row(ws, 3, headers, bg=C["hdr_mid"])
df_disp = df_daily[["date","containers","cpu_alloc","cpu_used","cpu_waste","mem_alloc","mem_waste","pv_alloc","pv_waste_sum","oom_cnt","shortage_cnt","cpu_util_pct","mem_util_pct"]]
end_row = apply_data_rows(ws, df_disp, start_row=4, num_formats={3:"#,##0",4:"#,##0",5:"#,##0",6:"#,##0",7:"#,##0",8:"#,##0",9:"#,##0",10:"#,##0",11:"#,##0",12:"0.1",13:"0.1"})
set_col_widths(ws, {"A":14,"B":12,"C":16,"D":16,"E":16,"F":16,"G":14,"H":16,"I":14,"J":12,"K":12,"L":14,"M":14})
freeze_and_filter(ws, row=4)
add_chart_image(ws, "chart3_daily_waste_stack.png", f"A{end_row+3}", 860, 400)
print(" ✅ '6. 일별트렌드_KST' 시트 구성 완수.")
# 7번 시트
def build_sheet_workload(wb, df_pod, infra_tag):
print(" [8/8] '7. 워크로드별_심층분석' 오픈소스 기술 스택 평점표 가공 중...")
ws = wb.create_sheet("7. 워크로드별_심층분석")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:N1")
ws["A1"] = f"Workload Type별 오픈소스 기술 스택 효율 분석 [{infra_tag}]"
ws["A1"].font = ft(bold=True, size=13, color=C["white"])
ws["A1"].fill = fill(C["purple"]); ws["A1"].alignment = center()
df_wl = df_pod.groupby("workload_type").agg(
containers=("container","count"), cpu_req_avg=("cpu_request_max","mean"), cpu_p95_avg=("cpu_usage_p95","mean"), cpu_waste_sum=("cpu_waste_core_hours","sum"), cpu_throttle_pk=("cpu_throttled_max","max"),
mem_req_avg=("mem_request_max","mean"), mem_p95_avg=("mem_usage_p95","mean"), mem_rss_avg=("mem_rss_p95","mean"), mem_waste_sum=("mem_waste_gb_hours","sum"),
pv_capacity_avg=("pv_capacity_max","mean"), pv_waste_sum=("pv_waste_gb_hours","sum"), oom_cnt=("is_oom_killed","sum")
).reset_index()
df_wl["cpu_util_pct"] = (df_wl["cpu_p95_avg"]/df_wl["cpu_req_avg"].replace(0,np.nan)*100).round(1).fillna(0)
df_wl["mem_util_pct"] = (df_wl["mem_p95_avg"]/df_wl["mem_req_avg"].replace(0,np.nan)*100).round(1).fillna(0)
df_wl = df_wl.sort_values("cpu_waste_sum", ascending=False).reset_index(drop=True)
headers2 = ["워크로드 타입","컨테이너 수","CPU Req(avg)","CPU P95(avg)","CPU 낭비 총합","Throttle Peak","Mem Req(avg)","Mem P95(avg)","Mem RSS(avg)","Mem 낭비 총합","PV Cap(avg)","PV 낭비 총합","CPU활용(%)","Mem활용(%)"]
apply_header_row(ws, 3, headers2, bg=C["purple"])
cols_out = ["workload_type","containers","cpu_req_avg","cpu_p95_avg","cpu_waste_sum","cpu_throttle_pk","mem_req_avg","mem_p95_avg","mem_rss_avg","mem_waste_sum","pv_capacity_avg","pv_waste_sum","cpu_util_pct","mem_util_pct"]
end_row = apply_data_rows(ws, df_wl[cols_out], start_row=4, num_formats={3:"0.0",4:"0.0",5:"#,##0",6:"0.00",7:"0.0",8:"0.0",9:"0.0",10:"#,##0",11:"0.0",12:"#,##0",13:"0.1",14:"0.1"})
set_col_widths(ws, {"A":18,"B":12,"C":14,"D":14,"E":14,"F":14,"G":14,"H":14,"I":14,"J":14,"K":14,"L":14,"M":12,"N":12})
freeze_and_filter(ws, row=4)
add_chart_image(ws, "chart15_oom_status_by_workload.png", f"A{end_row+3}", 860, 400)
print(" ✅ '7. 워크로드별_심층분석' 시트 구성 완수.")
# Main Engine
def main():
print("📢 [Step6 대기 개시] 고도화 데이터 정산 파일 로드 분석 시작...")
p1 = MERGED_DIR / "enriched_fixed_7d.parquet"
p2 = MERGED_DIR / "pareto_fixed_ns.parquet"
p3 = MERGED_DIR / "daily_ns_usage.parquet"
if not all([p1.exists(), p2.exists(), p3.exists()]):
print("❌ [중단 에러] 2단계 파레토 및 일별 사용량 가공 원부가 유실되었습니다.")
return
print(" -> Pandas 메모리 구조화 스트림 바인딩 중...")
df_pod = pd.read_parquet(p1)
df_ns = pd.read_parquet(p2)
df_daily_ns = pd.read_parquet(p3)
meta_file = MERGED_DIR / "meta_run_info.txt"
if meta_file.exists():
with open(meta_file, "r") as f:
meta_str = f.read().strip()
infra_tag, date_tag = meta_str.split("@")
excel_name = f"res_usage_report_{infra_tag.lower()}_{date_tag}.xlsx"
else:
infra_tag, date_tag = "ALL", "unknown"
excel_name = "res_usage_resource_governance_v2.xlsx"
print(f"🛠️ [Workbook 기동] openpyxl 시트 마스터 메모리 스트림 생성 중...")
wb = Workbook()
build_sheet_summary(wb, df_pod, df_ns, infra_tag)
build_sheet_pareto(wb, df_ns, infra_tag)
build_sheet_ns_daily(wb, df_daily_ns, infra_tag)
build_sheet_cpu(wb, df_pod, infra_tag)
build_sheet_memory(wb, df_pod, infra_tag)
build_sheet_oom(wb, df_pod, infra_tag)
build_sheet_violations(wb, df_pod, infra_tag)
build_sheet_trends(wb, df_pod, infra_tag)
build_sheet_workload(wb, df_pod, infra_tag)
out_path = OUT_DIR / excel_name
print(f"💾 [물리 쓰기] openpyxl 가공 셀 데이터를 로컬 디스크 파일로 저장 중... -> {out_path}")
wb.save(out_path)
print(f"📦 [로컬 컴파일 마감 완료]: {excel_name} ({out_path.stat().st_size/1024:.0f} KB)")
# 원격 백업 스토리지 배포 레이어
bucket_name = os.getenv("MINIO_REPORT_BUCKET", "devops-test")
endpoint = os.getenv("MINIO_ENDPOINT")
access_key = os.getenv("MINIO_ACCESS_KEY")
secret_key = os.getenv("MINIO_SECRET_KEY")
if all([endpoint, access_key, secret_key]):
try:
print("🪣 [원격 배포] 사내 AIStor 오브젝트 스토리지 싱크 세션 기동...")
s3_client = boto3.client(
"s3", endpoint_url=endpoint,
aws_access_key_id=access_key, aws_secret_access_key=secret_key,
region_name="us-east-1", config=boto3.session.Config(signature_version="s3v4")
)
object_key = f"reports/{infra_tag.lower()}/{excel_name}"
print(f" ⬆️ AIStor 업로드 전송 중... ➡️ S3://{bucket_name}/{object_key}")
s3_client.upload_file(str(out_path), bucket_name, object_key)
print("🏁 === [전사 배포 완료] 고도화 리포트 자산화가 최종 성공적으로 종결되었습니다. ===")
except Exception as e:
print(f"❌ [배포 에러] AIStor 원격 업로드 중 물리 세션 예외 크래시: {str(e)}")
else:
print("⚠️ [환경 알림] 원격 접속 정보(환경변수) 생략으로 로컬 저장 모드로 마감합니다.")
if __name__ == "__main__":
main()
engineer