26Y01a5
"""
step6 Excel Builder v2 — KST 보정 + 차트 20개 + 워크로드별 분석 시트
[최종 고도화 패치: res_usage_ 프리픽스 + devops-test 버킷 + 11대 메트릭 입체 리포팅]
"""
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
# ─── 📂 [경로 정합성] 모든 디렉토리 경로 ./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="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():
print(f" ⚠️ [차트 유실] {path_key} 파일이 {PLOT_DIR}에 없어 삽입을 건너뜁니다.")
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)
print(f" -> 🎨 차트 맵핑 완료: {path_key} ➡️ {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()
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
# 💡 [고도화 반영] 신규 징수 메트릭(PV 스토리지 및 Throttling 위험군) 요약 평점화
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())
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 = [
("정산 대상 인프라 도메인", 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"]),
("연산 스로틀링(Throttling) 위험군",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"]), # ◀ PV 낭비 신설
("전사 CPU 평균 실효 활용률", f"{eff_pct:.1f} %", "375623", 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 = 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
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=f"[ {infra_tag} 네임스페이스 파레토 ]").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)
# ─── Sheet 1: 파레토 분석 NS ──────────────────────────────
def build_sheet_pareto(wb, df_ns, infra_tag):
print(f"⏳ [1/8] '1. 파레토분석_NS' 시트 렌더링 중... (대상 테넌트 수: {len(df_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 2: CPU 분석 ────────────────────────────────────
def build_sheet_cpu(wb, df_pod, infra_tag):
top30 = max(1, int(len(df_pod)*0.30))
print(f"⏳ [2/8] '2. CPU Request_Usage 분석' 시트 빌드 중... (CFS Throttling 컬럼 확장 수록)")
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}] — Request / Limit / Usage / Throttling"
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
# 💡 [고도화 반영] cpu_throttled_max 지표를 "Throttle Peak" 컬럼으로 전격 추가 명시
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","namespace","workload_type","pod","container",
"cpu_request_max","cpu_limit_max","cpu_usage_p95","cpu_throttled_max","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.000",11:"0.0",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{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":12,"H":12,"I":12,"J":14,"K":12,"L":14,"M":16})
freeze_and_filter(ws)
# ─── Sheet 3: Memory & PV 스토리지 입체 분석 ─────────────────────────
def build_sheet_memory(wb, df_pod, infra_tag):
top30 = max(1, int(len(df_pod)*0.30))
print(f"⏳ [3/8] '3. Memory & PV 분석' 고도화 탭 병합 빌드 중... (순수 RSS 및 PV 할당/낭비량 동시 롤업)")
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
# 💡 [고도화 반영] 순수 물리 메모리(RSS), PV 스토리지 용량(PV Cap), 실제 디스크 적재량(PV Used), 볼륨 낭비(PV Waste) 다차원 결합 수록
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"])
# 메모리 및 PV 복합 누수 내림차순 가치 정렬
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","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"]
df_disp = df_out[cols].reset_index(drop=True)
# 전체 리사이징 완료된 기가바이트(GB) 소수점 포맷 지정
nf = {7:"0.00", 8:"0.00", 9:"0.00", 10:"0.00", 11:"0.00", 12:"#,##0.0", 13:"0.0", 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{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)
# ─── Sheet 4: OOM 및 자원 부족 ───────────────────────────
def build_sheet_oom(wb, df_pod, infra_tag):
print("⏳ [4/8] '4. 자원부족및OOM장애군' 잠재적 병목 컨테이너 색출 중...")
ws = wb.create_sheet("4. 자원부족및OOM장애군")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:L1")
t = ws["A1"]
t.value = "OOMKilled / CPU CFS Throttled 병목 위험 워크로드 명세 (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
# 💡 [고도화 반영] CFS 스로틀링 피크 컬럼을 장애 원인 규명 레이어로 결합 수록
headers = ["날짜(KST)","클러스터","네임스페이스","워크로드","Pod","컨테이너",
"상태","CPU Request","CPU P95","Throttle Peak","Mem Limit(GB)","Mem P95(GB)"]
apply_header_row(ws, 2, headers, bg=C["red"])
# OOM 또는 부족 및 스로틀링 유발군 전수 격리
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","cpu_shortage_cores"], ascending=[False,False,False]).copy()
print(f" -> 💥 가용성 저하 리스크 감지 총량: {len(df_out)}건")
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","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)
# ─── Sheet 5: 리소스 미설정 위반 ─────────────────────────
def build_sheet_violations(wb, df_pod, infra_tag):
print("⏳ [5/8] '5. 리소스미설정위반군' 스케줄러 노이즈 방지 블랙리스트 렌더링...")
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.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("⏳ [6/8] '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 [{infra_tag}] — 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
note_cell = ws.cell(row=2, column=1, value=f"※ 본 시트의 모든 정산 추이는 PV 스토리지 볼륨 누수액 계산이 연동 완료된 스케일 통계입니다.")
note_cell.font = ft(size=9, color="7F7F7F")
ws.merge_cells("A2:M2")
# 💡 [고도화 반영] 일별 집계 롤업 시 PV 스토리지 공급 및 낭비 지표 전격 가중합 통합
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"])
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)
for col_letter in ["L", "M"]:
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":16,"I":14,"J":12,"K":12,"L":14,"M":14})
ws.freeze_panes = "A5"
# 시각화 이미지 앵커링
add_chart_image(ws, "chart3_daily_waste_stack.png", f"A{end_row+3}", 860, 400, "[ Daily CPU Waste Stacked — KST ]")
add_chart_image(ws, "chart4_cpu_efficiency_heatmap.png", f"A{end_row+26}", 860, 400, "[ CPU Utilization Heatmap — KST ]")
# ─── Sheet 7: 워크로드별 심층 분석 (NEW) ─────────────────
def build_sheet_workload(wb, df_pod, infra_tag):
print("⏳ [7/8] '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
# 💡 [고도화 반영] 기술 스택별 순수 RSS 물리 메모리 및 PV 스토리지 누수 총량 서열화 집계 통합
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"), # ◀ 순수 RSS 평균
mem_waste_sum=("mem_waste_gb_hours","sum"),
pv_capacity_avg=("pv_capacity_max","mean"), # ◀ PV 용량 평균
pv_waste_sum=("pv_waste_gb_hours","sum"), # ◀ PV 스토리지 낭비 누적합
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"])
ws.cell(row=2, column=1, value=f"[ {infra_tag} 기술 도메인별 거버넌스 롤업 ]").font = ft(bold=True, size=11, color=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)
for col_letter in ["M", "N"]:
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":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})
ws.freeze_panes = "A5"
# 마스터 분석 차트 연쇄 매핑
add_chart_image(ws, "chart15_oom_status_by_workload.png", f"A{end_row+3}", 860, 400, "[ Status Distribution by Workload ]")
add_chart_image(ws, "chart9_boxplot_cpu_util_by_workload.png", f"A{end_row+26}", 860, 400, "[ CPU P95 Utilization Boxplot ]")
# ─── 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")
# ⏱️ [동적 파일 인덱싱] meta_run_info 로부터 조건 파싱
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("@")
# 💡 [요청 반영] 최종 엑셀 파일명 프리픽스를 res_usage_ 로 전격 전환
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"\n🚀 [로컬 빌드 개시] openpyxl 엔진 가동 ➡️ 대상 파일명: {excel_name}")
wb = Workbook()
build_sheet_summary(wb, df_pod, df_ns, infra_tag)
build_sheet_pareto(wb, df_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
wb.save(out_path)
print(f"✅ Local Save Done: {out_path} ({out_path.stat().st_size/1024:.0f} KB)")
# ─── 🪣 [요청 반영] 사내 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")
# 💡 기본값 대상 버킷명을 devops-test 로 원천 교체 지정
bucket_name = os.getenv("MINIO_REPORT_BUCKET", "devops-test")
if all([minio_endpoint, minio_access_key, minio_secret_key]):
try:
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")
)
# 가상 객체 주소 매핑 파싱 (reports/compute/res_usage_...)
object_key = f"reports/{infra_tag.lower()}/{excel_name}"
print(f"🪣 [오브젝트 스토리지 싱크] devops-test 버킷 배포 ➡️ S3://{object_key} 전송 중...")
s3_client.upload_file(str(out_path), bucket_name, object_key)
print("🏁 === [전사 배포 마감 완수] 고도화 res_usage_ 마스터 엑셀 리포트가 devops-test 버킷에 업로드되었습니다. ===")
except Exception as e:
print(f"❌ [배포 에러] 사내 MinIO AIStor 전송 중 물리 예외 발생: {str(e)}")
else:
print("⚠️ [안내] MinIO 접속을 위한 DevOps 환경변수가 생략되어 로컬 마감 후 종료합니다.")
if __name__ == "__main__":
main()
engineer