26Y07a2
"""
step6 Excel Builder v2 — Adaptive Multi-Day FinOps Master Report Compiler
[최종 통합 완결판: 전기간 데이터 동적 병합 + 10만 행 OOM 세이프 하이퍼 가드레일 장착]
"""
import os
import glob
import re
import sys
import argparse
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
from minio import Minio
# ─── 📂 모든 디렉토리 경로 ./data 상대 경로 표준화 ──────────────────────────
BASE_DATA_DIR = Path("./data")
MERGED_DIR = BASE_DATA_DIR / "merged"
PLOT_DIR = BASE_DATA_DIR / "output" / "plots"
OUT_DIR = BASE_DATA_DIR / "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="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, infra_tag, w=860, h=400, label=None):
path = PLOT_DIR / infra_tag / path_key
if not path.exists():
print(f" ⚠️ [차트 유실] {path_key} 파일이 {PLOT_DIR / infra_tag}에 없어 삽입을 건너뜁니다.")
return
row_num = int(''.join(filter(str.isdigit, anchor_cell)))
if label:
col_letter = "".join(filter(str.isalpha, anchor_cell))
col_idx = 1 if col_letter == "A" else (9 if col_letter == "I" else 1)
ws.cell(row=row_num-1, column=col_idx, 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(f"⏳ [0/9] '0. 전사종합요약' 통합 대시보드 구축 중... ({infra_tag})")
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()
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"]),
]
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})
chart_row = 16
add_chart_image(ws, "chart6_status_donut.png", f"A{chart_row}", infra_tag, w=420, h=300)
add_chart_image(ws, "chart5_pareto_ns_waste.png", f"E{chart_row}", infra_tag, w=560, h=300)
# ─── Sheet 1: 파레토 분석 NS ──────────────────────────────
def build_sheet_pareto(wb, df_ns, infra_tag):
print(f"⏳ [1/9] '1. 파레토분석_NS' 전기간 롤업 장표 작성 중...")
ws = wb.create_sheet("1. 파레토분석_NS")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:I1")
t = ws["A1"]
t.value = f"전체 분석기간 Namespace별 CPU Waste 파레토 분석 [{infra_tag}]"
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)
# ─── Sheet 1-2: 일단위 Namespace별 자원 사용량 탭 ───────────────────
def build_sheet_ns_daily(wb, df_daily_ns, infra_tag):
print(f"⏳ [2/9] '1-2. 일단위_NS별_사용량' 종합 정산 피벗 장표 마크 중... (행 수: {len(df_daily_ns):,}건)")
ws = wb.create_sheet("1-2. 일단위_NS별_사용량")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:J1")
t = ws["A1"]
t.value = f"Namespace 일일 자원 소모량 및 통합 사용량 점수 지표 [{infra_tag}]"
t.font = ft(bold=True, size=12, color=C["white"])
t.fill = fill("1F4E79"); t.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"])
ws.row_dimensions[2].height = 26
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)
ws.conditional_formatting.add(f"J3:J{end_row}", DataBarRule(start_type="min", end_type="max", color="375623", showValue=True))
widths = {"A":14, "B":22, "C":18, "D":18, "E":18, "F":18, "G":18, "H":18, "I":18, "J":24}
set_col_widths(ws, widths)
freeze_and_filter(ws, row=2)
# ─── Sheet 2: CPU 분석 (OOM-Safe 하이퍼 가드레일 장착) ───────────────────
def build_sheet_cpu(wb, df_pod, infra_tag, max_rows=100000):
print(f"⏳ [3/9] '2. CPU Request_Usage 분석' 시트 컴파일 (상위 낭비군 최대 {max_rows}행 격리)...")
ws = wb.create_sheet("2. CPU Request_Usage 분석")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:M1")
t = ws["A1"]
t.value = f"CPU Resource Efficiency Analysis [{infra_tag}] — Multi-Day Top Waste Ranking"
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","Throttle Peak","활용률(%)","낭비 Core-H","상태"]
apply_header_row(ws, 2, headers, bg=C["hdr_mid"])
# 💡 전기간 정렬 후 최대 10만건 슬라이싱 (엑셀 다운 방어)
df_out = df_pod.sort_values("cpu_waste_core_hours", ascending=False).head(max_rows).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)
cols = ["date","cluster_type","workload_domain","workload_type","pod","container",
"cpu_request_max","cpu_limit_max","cpu_usage_p95","cpu_throttled_max","util","cpu_waste_core_hours","status"]
df_disp = df_out[cols].reset_index(drop=True)
nf = {7:"0.000",8:"0.000",9:"0.000",10:"0.000",11:"0.1",12:"#,##0.0"}
end_row = apply_data_rows(ws, df_disp, start_row=3, num_formats=nf, status_col_idx=13)
ws.conditional_formatting.add(f"K3:K{max(4, 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":16,"C":18,"D":16,"E":26,"F":14,"G":12,"H":12,"I":12,"J":14,"K":12,"L":14,"M":16})
freeze_and_filter(ws)
add_chart_image(ws, "chart1_cpu_req_vs_usage_by_workload.png", f"A{end_row+3}", infra_tag, 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+26}", infra_tag, w=860, h=400, label="[ CPU Utilization Boxplot by Workload ]")
# ─── Sheet 3: Memory 및 PV 스토리지 분석 (OOM-Safe) ───────────────────────
def build_sheet_memory(wb, df_pod, infra_tag, max_rows=100000):
print(f"⏳ [4/9] '3. Memory_PV 입체분석' 마스터 시트 추출 (최대 {max_rows}행 가드레일)...")
ws = wb.create_sheet("3. Memory_PV 입체분석")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:O1")
t = ws["A1"]
t.value = f"Memory RSS & Persistent Volume Storage Cross-Governance Analysis [{infra_tag}]"
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","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(max_rows).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)
cols = ["date","cluster_type","workload_domain","workload_type","pod","container",
"mem_request_max","mem_usage_p95","mem_rss_p95","pv_capacity_max","pv_usage_p95","pv_waste_gb_hours","util","mem_waste_gb_hours","status"]
df_disp = df_out[cols].reset_index(drop=True)
nf = {7:"0.00", 8:"0.00", 9:"0.00", 10:"0.00", 11:"0.00", 12:"#,##0.0", 13:"0.1", 14:"#,##0.0"}
end_row = apply_data_rows(ws, df_disp, start_row=3, num_formats=nf, status_col_idx=15)
ws.conditional_formatting.add(f"M3:M{max(4, 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":16,"C":18,"D":16,"E":26,"F":14,"G":12,"H":12,"I":12,"J":12,"K":12,"L":14,"M":12,"N":12,"O":16})
freeze_and_filter(ws)
add_chart_image(ws, "chart2_mem_req_vs_usage_by_workload.png", f"A{end_row+3}", infra_tag, w=860, h=400, label="[ Memory Specs vs WorkingSet vs RSS Peak ]")
add_chart_image(ws, "chart20_daily_mem_per_workload.png", f"A{end_row+26}", infra_tag, w=960, h=540, label="[ Daily Memory & PV Provisioning Trends ]")
# ─── Sheet 4: OOM 및 자원 부족 리스크 ───────────────────────────
def build_sheet_oom(wb, df_pod, infra_tag, max_rows=50000):
print(f"⏳ [5/9] '4. 자원부족및OOM장애군' 리스크 인덱스 통합 명세 작성 중...")
ws = wb.create_sheet("4. 자원부족및OOM장애군")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:L1")
t = ws["A1"]
t.value = f"OOMKilled / CPU CFS Throttled 병목 리스크 전기간 통합 누적 명세 [{infra_tag}]"
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","Throttle Peak","Mem Limit(GB)","Mem P95(GB)"]
apply_header_row(ws, 2, headers, bg=C["red"])
df_out = df_pod[
df_pod["status"].str.contains("OOM|Shortage|부족", na=False) | (df_pod["cpu_throttled_max"] > 0.2)
].sort_values(["is_oom_killed","cpu_throttled_max","cpu_shortage_cores"], ascending=[False,False,False]).head(max_rows).copy()
cols = ["date","cluster_type","workload_domain","workload_type","pod","container",
"status","cpu_request_max","cpu_usage_p95","cpu_throttled_max","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.00",12:"0.00"}
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":12,"I":12,"J":14,"K":14,"L":14})
freeze_and_filter(ws)
add_chart_image(ws, "chart8_shortfall_footprint.png", f"A{end_row+3}", infra_tag, w=860, h=400, label="[ CPU CFS Throttling Risk Heatmap Footprint ]")
# ─── Sheet 5: 리소스 미설정 규격 위반 ─────────────────────────
def build_sheet_violations(wb, df_pod, infra_tag, max_rows=50000):
print(f"⏳ [6/9] '5. 리소스미설정위반군' 규격 미설정 목록 마크 중...")
ws = wb.create_sheet("5. 리소스미설정위반군")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:L1")
t = ws["A1"]
t.value = f"Resource Request / Limit 미설정 위반 컨테이너 전기간 백로그 [{infra_tag}]"
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).head(max_rows).copy()
df_out["req_flag"] = df_out["has_no_request"].map({True:"VIOLATION",False:"OK"})
df_out["lim_flag"] = df_out["has_no_limit"].map({True:"VIOLATION",False:"OK"})
cols = ["date","cluster_type","workload_domain","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.00",12:"0.00"}
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, infra_tag):
print(f"⏳ [7/9] '6. 일별트렌드_KST' 누적 통계 가동 중...")
ws = wb.create_sheet("6. 일별트렌드_KST")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:M1")
t = ws["A1"]
t.value = f"Daily Resource Governance & Loss Trend Timeline [{infra_tag}]"
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
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().sort_values("date")
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"])
nf = {3:"#,##0.0",4:"#,##0.0",5:"#,##0.0",6:"#,##0.0",7:"#,##0.0",8:"#,##0.0",9:"#,##0.0",10:"#,##0",11:"#,##0",12:"0.1",13:"0.1"}
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=nf)
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}", infra_tag, 860, 400, "[ Daily CPU Waste Stacked Timeline ]")
add_chart_image(ws, "chart4_cpu_efficiency_heatmap.png", f"A{end_row+26}", infra_tag, 860, 400, "[ CPU Utilization Heatmap Grid ]")
# ─── Sheet 7: 워크로드별 심층 분석 ─────────────────
def build_sheet_workload(wb, df_pod, infra_tag):
print(f"⏳ [8/9] '7. 워크로드별_심층분석' 전사 평점표 동적 집계 중...")
ws = wb.create_sheet("7. 워크로드별_심층분석")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:N1")
t = ws["A1"]
t.value = f"Workload Type별 기술 스택 누적 자원 효율화 입체 평점표 [{infra_tag}]"
t.font = ft(bold=True, size=13, color=C["white"])
t.fill = fill(C["purple"]); t.alignment = center()
ws.row_dimensions[1].height = 32
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"]
df_disp = df_wl[cols_out]
nf = {3:"0.000", 4:"0.000", 5:"#,##0.0", 6:"0.000", 7:"0.00", 8:"0.00", 9:"0.00", 10:"#,##0.0", 11:"0.00", 12:"#,##0.0", 13:"0.1", 14:"0.1"}
end_row = apply_data_rows(ws, df_disp, start_row=4, num_formats=nf)
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}", infra_tag, 860, 400, "[ Status Distribution count per Open-Source Stack ]")
add_chart_image(ws, "chart14_cpu_mem_waste_scatter.png", f"A{end_row+26}", infra_tag, 760, 480, "[ Multi-Resource Loss Correlation Bubble Map ]")
# ─── Sheet 8: 추가 차트 모음 ─────────────────
def build_sheet_extra_charts(wb, infra_tag):
print(f"⏳ [9/9] '8. 추가차트모음' 격리 배치 중...")
ws = wb.create_sheet("8. 추가차트모음")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:Q1")
t = ws["A1"]
t.value = f"Step 3 Additional Unused Dashboards & Reports [{infra_tag}]"
t.font = ft(bold=True, size=13, color=C["white"])
t.fill = fill(C["hdr_dark"]); t.alignment = center()
ws.row_dimensions[1].height = 35
used_ids = [1, 2, 3, 5, 6, 9, 10, 15]
remaining_ids = [i for i in range(1, 20) if i not in used_ids]
current_row = 3
col_positions = ["A", "I"]
col_idx = 0
for cid in remaining_ids:
found_file = None
patterns = [f"chart{cid}_*.png", f"chart_{cid}_*.png", f"chart{cid}.png", f"chart_{cid}.png"]
for pat in patterns:
matches = list((PLOT_DIR / infra_tag).glob(pat))
if matches:
found_file = matches[0].name
break
if found_file:
anchor = f"{col_positions[col_idx]}{current_row}"
add_chart_image(ws, found_file, anchor, infra_tag, w=480, h=280, label=f"Chart {cid}: {found_file}")
col_idx += 1
if col_idx >= len(col_positions):
col_idx = 0
current_row += 16
else:
print(f" ℹ️ [추가 차트] Chart ID {cid} 번 파일이 {infra_tag} 폴더에 없어 생략합니다.")
# ─── Main ─────────────────────────────────────────────────
def main():
parser = argparse.ArgumentParser(description="FinOps Master Excel Report Builder")
parser.add_argument("--cluster", type=str, required=True, choices=["COMPUTE", "STORAGE"], help="Target cluster type")
args = parser.parse_args()
infra_tag = args.cluster.upper()
print(f"🚀 [Step6 개시] {infra_tag} 파티션 전기간 하이퍼 병합 엑셀 리포터 엔진 가동...")
cl_dir = MERGED_DIR / infra_tag
if not cl_dir.exists():
print(f"❌ 오류: 정산 데이터 폴더가 없습니다: {cl_dir}")
return
# 1. 🗂️ 지정 클러스터 폴더 하방의 모든 날짜 파티션 동적 강제 Concat
pod_files = list(cl_dir.glob("daily_enriched_*.parquet"))
ns_files = list(cl_dir.glob("daily_ns_usage_*.parquet"))
if not pod_files:
print(f"❌ 오류: 분석할 Parquet 원부가 존재하지 않습니다. 앞단 파이프라인을 기동하세요.")
return
print(f"🔍 [스캔 완수] {infra_tag} 하방 총 {len(pod_files)}개 일자 정산본 취합 병합 스케줄러 가동.")
df_pod = pd.concat([pd.read_parquet(f) for f in pod_files], ignore_index=True)
df_daily_ns = pd.concat([pd.read_parquet(f) for f in ns_files], ignore_index=True)
# 2. 📉 전기간 통합 파레토 마스터 테이블 동적 역컴파일 생성
print("📉 [Pareto Re-compiling] 전기간 데이터를 기반으로 전사 파레토 등급을 재산정합니다...")
df_ns = df_pod.groupby("namespace").agg(
minutes_running_sum=("minutes_running", "sum"),
container_cnt=("container", "count"),
total_allocated_core_hours=("cpu_allocated_core_hours", "sum"),
total_waste_core_hours=("cpu_waste_core_hours", "sum")
).reset_index().sort_values("total_waste_core_hours", ascending=False)
global_total_waste = df_ns["total_waste_core_hours"].sum() or 0.1
df_ns["waste_share_pct"] = (df_ns["total_waste_core_hours"] / global_total_waste * 100).round(2)
df_ns["waste_cumsum_pct"] = df_ns["waste_share_pct"].cumsum().round(2)
# 3. 엑셀 워크북 빌드
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)
# 🛡️ 엑셀 104만행 규격 파괴 예방 및 OOM 세이프 하이퍼 가드레일 (상위 10만건 슬라이싱 처리)
build_sheet_cpu(wb, df_pod, infra_tag, max_rows=100000)
build_sheet_memory(wb, df_pod, infra_tag, max_rows=100000)
build_sheet_oom(wb, df_pod, infra_tag, max_rows=50000)
build_sheet_violations(wb, df_pod, infra_tag, max_rows=50000)
build_sheet_trends(wb, df_pod, infra_tag)
build_sheet_workload(wb, df_pod, infra_tag)
build_sheet_extra_charts(wb, infra_tag)
# 4. 파일 세트 저장 마감 명세 동기화
excel_name = f"res_usage_report_{infra_tag.lower()}_integrated_master.xlsx"
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/1024:.1f} MB)")
# 🪣 사내 MinIO AIStor 'devops-test' 버킷 자동 배포 레이어
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", "devops-test")
if all([minio_endpoint, minio_access_key, minio_secret_key]):
try:
endpoint_clean = minio_endpoint.replace("http://", "").replace("https://", "")
secure_flag = minio_endpoint.startswith("https://")
minio_client = Minio(
endpoint_clean,
access_key=minio_access_key,
secret_key=minio_secret_key,
secure=secure_flag
)
if not minio_client.bucket_exists(bucket_name):
minio_client.make_bucket(bucket_name)
object_key = f"reports/{infra_tag.lower()}/{excel_name}"
print(f"🪣 [오브젝트 스토리지 싱크] devops-test 버킷 배포 ➡️ MinIO://{object_key} 파일 스트림 업로드 중...")
# fput_object 표준 메서드로 원격 전송 완수
minio_client.fput_object(bucket_name, object_key, str(out_path))
print(f"🏁 === [전사 배포 마감 성공] 고도화 {infra_tag} 통합 마스터 엑셀 리포트 배포 자산화가 완료되었습니다. ===")
except Exception as e:
print(f"❌ [배포 에러] 사내 MinIO AIStor 원격 업로드 중 예외 발생: {str(e)}")
else:
print("⚠️ [안내] 접속 환경변수 생략으로 로컬 아카이빙 처리 후 파티션을 마감합니다.")
if __name__ == "__main__":
main()
engineer