26J30h2
"""
step6 Excel Builder v2 — KST 보정 + 차트 20개 + 워크로드별 분석 시트 + MinIO AIStor 자동 배포
"""
import os
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
# ─── 🛡️ [DevOps 경로 호환성 레이어] ──────────────────────────────────────────
# 로컬 개발 환경(/home/claude)과 K8s Pod 작업 환경(/app/data) 모두에서 경로 크래시를 방지합니다.
try:
from config import MERGED_DIR, PLOT_DIR, OUT_DIR
except ImportError:
MERGED_DIR = Path("/home/claude/data/merged")
PLOT_DIR = Path("/home/claude/data/output/plots")
OUT_DIR = Path("/home/claude/data/output")
OUT_DIR.mkdir(parents=True, exist_ok=True)
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="00B0A0",
)
WL_COLORS_HEX = {
"SPARK_EXECUTOR":"1F4E79","SPARK_DRIVER":"2E75B6",
"AIRFLOW_WORKER":"ED7D31","AIRFLOW_SCHEDULER":"FFC000",
"STARROCKS_BE":"70AD47","STARROCKS_FE":"375623",
"JUPYTERLAB":"7030A0","GENERAL_APPS":"888888","POSTGRESQL":"C00000",
}
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():
return
row_num = int(''.join(filter(str.isdigit, anchor_cell)))
if label:
col = anchor_cell[0]
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):
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 = "FinOps Resource Governance Master Report (KST 기준)"
t.font = ft(bold=True, size=16, color=C["white"])
t.fill = fill(C["hdr_dark"]); t.alignment = center()
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()
top30 = max(1, int(len(df_pod)*0.30))
kst_dates = sorted(df_pod["date"].unique())
date_range = f"{kst_dates[0]} ~ {kst_dates[-1]} (KST)"
kpis = [
("분석 기간 (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"{no_req_cnt:,} 개", "E26B0A", C["white"]),
("전사 CPU 낭비 총량", f"{waste_ch:,.1f} Core-H", C["hdr_mid"], C["white"]),
("전사 CPU 평균 활용률", f"{eff_pct:.1f} %", "375623", C["white"]),
("전사 Memory 낭비 총량", f"{mem_waste:,.1f} GB-H", "6B4F9B", C["white"]),
("최적화 권고 대상 (Top 30%)", f"{top30:,} 개", C["accent"], C["white"]),
("분석 네임스페이스 수", f"{df_pod['namespace'].nunique()} 개", C["hdr_dark"], C["white"]),
("분석 워크로드 타입 수", f"{df_pod['workload_type'].nunique()} 종", C["hdr_dark"], C["white"]),
]
ws.row_dimensions[2].height = 6
for i, (label, value, bg, fg) in enumerate(kpis):
row = 3 + i
ws.row_dimensions[row].height = 28
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})
# Charts on summary sheet
chart_row = 15
ws.cell(row=chart_row-1, column=1, value="[ 거버넌스 현황 분포 ]").font = ft(bold=True, size=11, color=C["hdr_dark"])
ws.cell(row=chart_row-1, column=5, value="[ 네임스페이스 파레토 분석 ]").font = ft(bold=True, size=11, color=C["hdr_dark"])
add_chart_image(ws, "chart6_status_donut.png", f"A{chart_row}", w=420, h=300)
add_chart_image(ws, "chart5_pareto_ns_waste.png", f"E{chart_row}", w=560, h=300)
chart_row2 = chart_row + 19
ws.cell(row=chart_row2-1, column=1, value="[ Waste Footprint Bubble ]").font = ft(bold=True, size=11, color=C["hdr_dark"])
ws.cell(row=chart_row2-1, column=5, value="[ Workload 파레토 분석 ]").font = ft(bold=True, size=11, color=C["hdr_dark"])
add_chart_image(ws, "chart7_waste_footprint_bubble.png", f"A{chart_row2}", w=480, h=340)
add_chart_image(ws, "chart11_pareto_workload_waste.png", f"E{chart_row2}", w=540, h=340)
# ─── Sheet 1: 파레토 분석 NS ──────────────────────────────
def build_sheet_pareto(wb, df_ns):
ws = wb.create_sheet("1. 파레토분석_NS")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:I1")
t = ws["A1"]
t.value = "Namespace별 CPU Waste 파레토 분석 (KST 기준)"
t.font = ft(bold=True, size=13, color=C["white"])
t.fill = fill(C["hdr_dark"]); t.alignment = center()
ws.row_dimensions[1].height = 32
headers = ["Namespace","실행시간 합계(분)","컨테이너 수","할당 Core-H","낭비 Core-H","낭비 비중(%)","누적 비중(%)","등급"]
apply_header_row(ws, 2, headers, bg=C["hdr_mid"])
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","등급"]
df_out = df_disp[col_order]
nf = {4:"#,##0.0", 5:"#,##0.0", 6:"0.00", 7:"0.00"}
end_row = apply_data_rows(ws, df_out, start_row=3, num_formats=nf)
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)
add_chart_image(ws, "chart5_pareto_ns_waste.png", f"A{end_row+3}", w=820, h=400,
label="[ 네임스페이스 파레토 차트 ]")
# ─── Sheet 2: CPU 분석 ────────────────────────────────────
def build_sheet_cpu(wb, df_pod):
ws = wb.create_sheet("2. CPU Request_Usage 분석")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:L1")
t = ws["A1"]
t.value = "CPU Resource Efficiency Analysis — Request / Limit / P95 Usage (KST)"
t.font = ft(bold=True, size=13, color=C["white"])
t.fill = fill(C["hdr_dark"]); t.alignment = center()
ws.row_dimensions[1].height = 32
headers = ["날짜(KST)","클러스터","네임스페이스","워크로드","Pod","컨테이너",
"CPU Request","CPU Limit","CPU P95","활용률(%)","낭비 Core-H","상태"]
apply_header_row(ws, 2, headers, bg=C["hdr_mid"])
top30 = max(1, int(len(df_pod)*0.30))
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","namespace","workload_type","pod","container",
"cpu_request_max","cpu_limit_max","cpu_usage_p95","util","cpu_waste_core_hours","status_en"]
df_disp = df_out[cols].reset_index(drop=True)
nf = {7:"0.000",8:"0.000",9:"0.000",10:"0.0",11:"#,##0.0"}
end_row = apply_data_rows(ws, df_disp, start_row=3, num_formats=nf, status_col_idx=12)
ws.conditional_formatting.add(f"J3:J{end_row}",
ColorScaleRule(start_type="num", start_value=0, start_color="FF0000",
mid_type="num", mid_value=50, mid_color="FFFF00",
end_type="num", end_value=100, end_color="00B050"))
set_col_widths(ws, {"A":12,"B":18,"C":20,"D":18,"E":28,"F":16,"G":14,"H":14,"I":14,"J":14,"K":14,"L":16})
freeze_and_filter(ws)
add_chart_image(ws, "chart1_cpu_req_vs_usage_by_workload.png", f"A{end_row+3}", w=860, h=400,
label="[ CPU Request / Limit / P95 by Workload ]")
add_chart_image(ws, "chart9_boxplot_cpu_util_by_workload.png", f"A{end_row+30}", w=860, h=400,
label="[ CPU Utilization Boxplot by Workload ]")
add_chart_image(ws, "chart13_violin_cpu_util.png", f"A{end_row+57}", w=860, h=400,
label="[ CPU Utilization Violin Plot ]")
add_chart_image(ws, "chart17_cpu_limit_request_ratio.png", f"A{end_row+84}", w=860, h=400,
label="[ CPU Limit/Request Ratio Boxplot ]")
add_chart_image(ws, "chart19_daily_cpu_per_workload.png", f"A{end_row+111}",w=960, h=540,
label="[ Daily CPU Request vs P95 per Workload (KST) ]")
# ─── Sheet 3: Memory 분석 ─────────────────────────────────
def build_sheet_memory(wb, df_pod):
ws = wb.create_sheet("3. Memory Request_Usage 분석")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:L1")
t = ws["A1"]
t.value = "Memory Resource Efficiency Analysis — Request / Limit / P95 Usage (KST)"
t.font = ft(bold=True, size=13, color=C["white"])
t.fill = fill(C["hdr_dark"]); t.alignment = center()
ws.row_dimensions[1].height = 32
headers = ["날짜(KST)","클러스터","네임스페이스","워크로드","Pod","컨테이너",
"Mem Request(GB)","Mem Limit(GB)","Mem P95(GB)","활용률(%)","낭비 GB-H","상태"]
apply_header_row(ws, 2, headers, bg=C["hdr_mid"])
top30 = max(1, int(len(df_pod)*0.30))
df_out = df_pod.sort_values("mem_waste_gb_hours", ascending=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","namespace","workload_type","pod","container",
"mem_request_max","mem_limit_max","mem_usage_p95","util","mem_waste_gb_hours","status_en"]
df_disp = df_out[cols].reset_index(drop=True)
nf = {7:"0.000",8:"0.000",9:"0.000",10:"0.0",11:"#,##0.0"}
end_row = apply_data_rows(ws, df_disp, start_row=3, num_formats=nf, status_col_idx=12)
ws.conditional_formatting.add(f"J3:J{end_row}",
ColorScaleRule(start_type="num", start_value=0, start_color="FF0000",
mid_type="num", mid_value=50, mid_color="FFFF00",
end_type="num", end_value=100, end_color="00B050"))
set_col_widths(ws, {"A":12,"B":18,"C":20,"D":18,"E":28,"F":16,"G":14,"H":14,"I":14,"J":14,"K":14,"L":16})
freeze_and_filter(ws)
add_chart_image(ws, "chart2_mem_req_vs_usage_by_workload.png", f"A{end_row+3}", w=860, h=400,
label="[ Memory Request / Limit / P95 by Workload ]")
add_chart_image(ws, "chart10_boxplot_mem_util_by_workload.png", f"A{end_row+30}", w=860, h=400,
label="[ Memory Utilization Boxplot by Workload ]")
add_chart_image(ws, "chart18_mem_waste_heatmap.png", f"A{end_row+57}", w=860, h=400,
label="[ Memory Waste Heatmap (Workload x KST Date) ]")
add_chart_image(ws, "chart20_daily_mem_per_workload.png", f"A{end_row+84}", w=960, h=540,
label="[ Daily Memory Request vs P95 per Workload (KST) ]")
# ─── Sheet 4: OOM 및 자원 부족 ───────────────────────────
def build_sheet_oom(wb, df_pod):
ws = wb.create_sheet("4. 자원부족및OOM장애군")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:K1")
t = ws["A1"]
t.value = "OOMKilled / CPU Request 부족 컨테이너 명세 (KST)"
t.font = ft(bold=True, size=13, color=C["white"])
t.fill = fill(C["red"]); t.alignment = center()
ws.row_dimensions[1].height = 32
headers = ["날짜(KST)","클러스터","네임스페이스","워크로드","Pod","컨테이너",
"상태","CPU Request","CPU P95","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"])
].sort_values(["is_oom_killed","cpu_shortage_cores"], 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","namespace","workload_type","pod","container",
"status_en","cpu_request_max","cpu_usage_p95","mem_limit_max","mem_usage_p95"]
df_disp = df_out[cols].reset_index(drop=True)
nf = {8:"0.000",9:"0.000",10:"0.000",11:"0.000"}
end_row = apply_data_rows(ws, df_disp, start_row=3, num_formats=nf, status_col_idx=7)
set_col_widths(ws, {"A":12,"B":18,"C":20,"D":18,"E":28,"F":16,"G":16,"H":14,"I":14,"J":14,"K":14})
freeze_and_filter(ws)
add_chart_image(ws, "chart15_oom_status_by_workload.png", f"A{end_row+3}", w=860, h=400,
label="[ Governance Status by Workload ]")
add_chart_image(ws, "chart8_shortfall_footprint.png", f"A{end_row+30}", w=860, h=400,
label="[ CPU Shortfall Footprint Heatmap ]")
# ─── Sheet 5: 리소스 미설정 위반 ─────────────────────────
def build_sheet_violations(wb, df_pod):
ws = wb.create_sheet("5. 리소스미설정위반군")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:L1")
t = ws["A1"]
t.value = "Resource Request / Limit 미설정 위반 컨테이너 (KST)"
t.font = ft(bold=True, size=13, color=C["white"])
t.fill = fill("E26B0A"); t.alignment = center()
ws.row_dimensions[1].height = 32
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","namespace","workload_type","pod","container",
"req_flag","lim_flag","cpu_request_max","cpu_limit_max","mem_request_max","mem_limit_max"]
df_disp = df_out[cols].reset_index(drop=True)
nf = {9:"0.000",10:"0.000",11:"0.000",12:"0.000"}
end_row = apply_data_rows(ws, df_disp, start_row=3, num_formats=nf)
set_col_widths(ws, {"A":12,"B":18,"C":20,"D":18,"E":28,"F":16,"G":14,"H":14,"I":14,"J":14,"K":14,"L":14})
freeze_and_filter(ws)
# ─── Sheet 6: 일별 트렌드 (KST) ──────────────────────────
def build_sheet_trends(wb, df_pod):
ws = wb.create_sheet("6. 일별트렌드_KST")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:J1")
t = ws["A1"]
t.value = "Daily Resource Trend — KST 기준 (UTC+9 보정 완료)"
t.font = ft(bold=True, size=13, color=C["white"])
t.fill = fill(C["hdr_dark"]); t.alignment = center()
ws.row_dimensions[1].height = 32
# KST note
note_cell = ws.cell(row=2, column=1,
value="※ 모든 날짜는 KST(UTC+9) 기준입니다. Prometheus 수집 시 UTC timestamp에 +9h 변환 후 일자 집계됨.")
note_cell.font = ft(size=9, color="7F7F7F", bold=False)
ws.merge_cells("A2:J2")
# Daily aggregate
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"),
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","OOM 발생","Request 부족","CPU 활용률(%)","Mem 활용률(%)"]
apply_header_row(ws, 3, headers, bg=C["hdr_mid"])
ws.cell(row=2, column=1) # already set as note
nf = {3:"#,##0.0",4:"#,##0.0",5:"#,##0.0",6:"#,##0.0",7:"#,##0.0",8:"#,##0",9:"#,##0",10:"0.0",11:"0.0"}
# Re-add shortage_cnt col
df_disp = df_daily[["date","containers","cpu_alloc","cpu_used","cpu_waste",
"mem_alloc","mem_waste","oom_cnt","shortage_cnt","cpu_util_pct","mem_util_pct"]]
end_row = apply_data_rows(ws, df_disp, start_row=4, num_formats=nf)
for col_letter, label in [("J","CPU"), ("K","Mem")]:
col_idx = ord(col_letter) - ord('A') + 1
ws.conditional_formatting.add(
f"{col_letter}4:{col_letter}{end_row}",
ColorScaleRule(start_type="num",start_value=0, start_color="FF0000",
mid_type="num", mid_value=50, mid_color="FFFF00",
end_type="num", end_value=100, end_color="00B050"))
set_col_widths(ws, {"A":14,"B":12,"C":16,"D":16,"E":16,"F":16,"G":14,"H":12,"I":14,"J":14,"K":14})
ws.freeze_panes = "A5"
# Charts
for chart_key, anchor, w_img, h_img, lbl in [
("chart3_daily_waste_stack.png", f"A{end_row+3}", 860, 400, "[ Daily CPU Waste Stacked — KST ]"),
("chart12_daily_waste_trend_by_workload.png", f"A{end_row+30}", 860, 400, "[ Daily CPU Waste Trend by Workload — KST ]"),
("chart4_cpu_efficiency_heatmap.png", f"A{end_row+57}", 860, 400, "[ CPU Utilization Heatmap — KST ]"),
("chart18_mem_waste_heatmap.png", f"A{end_row+84}", 860, 400, "[ Mem Waste Heatmap — KST ]"),
]:
add_chart_image(ws, chart_key, anchor, w=w_img, h=h_img, label=lbl)
# ─── Sheet 7: 워크로드별 심층 분석 (NEW) ─────────────────
def build_sheet_workload(wb, df_pod):
ws = wb.create_sheet("7. 워크로드별_심층분석")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:K1")
t = ws["A1"]
t.value = "Workload Type별 심층 자원 효율화 분석 (KST)"
t.font = ft(bold=True, size=13, color=C["white"])
t.fill = fill(C["purple"]); t.alignment = center()
ws.row_dimensions[1].height = 32
# Per-workload summary table
df_wl = df_pod.groupby("workload_type").agg(
containers=("container","count"),
cpu_req_avg=("cpu_request_max","mean"),
cpu_lim_avg=("cpu_limit_max","mean"),
cpu_p95_avg=("cpu_usage_p95","mean"),
cpu_waste_sum=("cpu_waste_core_hours","sum"),
mem_req_avg=("mem_request_max","mean"),
mem_p95_avg=("mem_usage_p95","mean"),
mem_waste_sum=("mem_waste_gb_hours","sum"),
oom_cnt=("is_oom_killed","sum"),
shortage_cnt=("cpu_shortage_cores", lambda x: (x>0).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["lim_req_ratio"] = (df_wl["cpu_lim_avg"]/df_wl["cpu_req_avg"].replace(0,np.nan)).round(2).fillna(0)
df_wl = df_wl.sort_values("cpu_waste_sum", ascending=False).reset_index(drop=True)
headers2 = ["워크로드 타입","컨테이너 수","CPU Request(avg)","CPU Limit(avg)","CPU P95(avg)",
"CPU 활용률(%)","CPU 낭비 Core-H","Limit/Req 배율",
"Mem P95(avg GB)","Mem 활용률(%)","OOM 건수","CPU 부족 건수"]
apply_header_row(ws, 3, headers2, bg=C["purple"])
ws.cell(row=2, column=1, value="[ 워크로드별 집계 요약 ]").font = ft(bold=True, size=11, color=C["purple"])
cols_out = ["workload_type","containers","cpu_req_avg","cpu_lim_avg","cpu_p95_avg",
"cpu_util_pct","cpu_waste_sum","lim_req_ratio",
"mem_p95_avg","mem_util_pct","oom_cnt","shortage_cnt"]
df_disp = df_wl[cols_out]
nf = {3:"0.000",4:"0.000",5:"0.000",6:"0.0",7:"#,##0.0",8:"0.00",9:"0.000",10:"0.0"}
end_row = apply_data_rows(ws, df_disp, start_row=4, num_formats=nf)
# Color scale for utilization
for col_letter in ["F","J"]:
ws.conditional_formatting.add(f"{col_letter}4:{col_letter}{end_row}",
ColorScaleRule(start_type="num",start_value=0, start_color="FF0000",
mid_type="num", mid_value=50, mid_color="FFFF00",
end_type="num", end_value=100, end_color="00B050"))
set_col_widths(ws, {"A":20,"B":12,"C":14,"D":14,"E":14,"F":14,"G":14,"H":14,"I":14,"J":14,"K":12,"L":14})
ws.freeze_panes = "A5"
# All workload charts
for chart_key, anchor, w_img, h_img, lbl in [
("chart15_oom_status_by_workload.png", f"A{end_row+3}", 860, 400, "[ Status Distribution by Workload ]"),
("chart9_boxplot_cpu_util_by_workload.png", f"A{end_row+30}", 860, 400, "[ CPU Utilization Boxplot ]"),
("chart10_boxplot_mem_util_by_workload.png",f"A{end_row+57}", 860, 400, "[ Memory Utilization Boxplot ]"),
("chart13_violin_cpu_util.png", f"A{end_row+84}", 860, 400, "[ CPU Utilization Violin ]"),
("chart17_cpu_limit_request_ratio.png", f"A{end_row+111}",860, 400, "[ CPU Limit/Request Ratio ]"),
("chart7_waste_footprint_bubble.png", f"A{end_row+138}",760, 480, "[ Waste Footprint Bubble ]"),
("chart14_cpu_mem_waste_scatter.png", f"A{end_row+167}",760, 480, "[ CPU vs Mem Waste Scatter ]"),
("chart19_daily_cpu_per_workload.png", f"A{end_row+196}",960, 540, "[ Daily CPU per Workload (KST) ]"),
("chart20_daily_mem_per_workload.png", f"A{end_row+232}",960, 540, "[ Daily Mem per Workload (KST) ]"),
]:
add_chart_image(ws, chart_key, anchor, w=w_img, h=h_img, label=lbl)
# ─── Main ─────────────────────────────────────────────────
def main():
df_pod = pd.read_parquet(MERGED_DIR / "enriched_fixed_7d.parquet")
df_ns = pd.read_parquet(MERGED_DIR / "pareto_fixed_ns.parquet")
wb = Workbook()
build_sheet_summary(wb, df_pod, df_ns)
build_sheet_pareto(wb, df_ns)
build_sheet_cpu(wb, df_pod)
build_sheet_memory(wb, df_pod)
build_sheet_oom(wb, df_pod)
build_sheet_violations(wb, df_pod)
build_sheet_trends(wb, df_pod)
build_sheet_workload(wb, df_pod)
# ─── ⏱️ [고도화 반영] KST 일배치 및 Ad-hoc 구동 정보 파싱 ───
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"finops_report_{infra_tag.lower()}_{date_tag}.xlsx"
else:
excel_name = "finops_resource_governance_v2.xlsx"
out_path = OUT_DIR / excel_name
wb.save(out_path)
print(f"Local Build Done: {out_path} ({out_path.stat().st_size/1024:.0f} KB)")
# ─── 🪣 [신규 추가] 사내 MinIO AIStor 오브젝트 스토리지 자동 배포 레이어 ───
minio_endpoint = os.getenv("MINIO_ENDPOINT")
minio_access_key = os.getenv("MINIO_ACCESS_KEY")
minio_secret_key = os.getenv("MINIO_SECRET_KEY")
# 정산 보고서 전용 마스터 버킷 바인딩 (누락 시 기본값 적용)
bucket_name = os.getenv("MINIO_REPORT_BUCKET", "enterprise-finops-reports")
if all([minio_endpoint, minio_access_key, minio_secret_key]):
try:
print(f"🔗 사내 MinIO AIStor 연결 수립 중... ({minio_endpoint})")
s3_client = boto3.client(
"s3",
endpoint_url=minio_endpoint,
aws_access_key_id=minio_access_key,
aws_secret_access_key=minio_secret_key,
config=boto3.session.Config(signature_version="s3v4")
)
object_key = f"reports/{excel_name}"
print(f"⏳ [오브젝트 스토리지 싱크] 버킷 [{bucket_name}] ➡️ {object_key} 업로드 중...")
s3_client.upload_file(str(out_path), bucket_name, object_key)
print("✅ [성공] v2 마스터 거버넌스 엑셀 리포트가 사내 MinIO AIStor로 안전하게 배포되었습니다.")
except Exception as e:
print(f"❌ MinIO AIStor 오브젝트 스토리지 업로드 중 에러 발생: {str(e)}")
else:
print("⚠️ [안내] MinIO 접속을 위한 환경변수가 설정되지 않아 로컬 저장만 마감하고 종료합니다.")
if __name__ == "__main__":
main()
engineer