convert-bq-format.py

#!/usr/bin/env python3

import json
from datetime import datetime, timedelta, timezone
import sys

def parse_sadf_json(filename):
    with open(filename, encoding='utf-8') as f:
        text = f.read()
        data = json.loads(text)
    return data

def to_iso8601(date_str, time_str, tz_str):
    # 例: date_str = "2025-05-03", time_str = "18:20:01", tz_str = "JST"
    dt = datetime.strptime(date_str + " " + time_str, "%Y-%m-%d %H:%M:%S")
    # JST固定（UTC+9）と仮定
    if tz_str == "JST":
        jst = timezone(timedelta(hours=9))
        dt = dt.replace(tzinfo=jst)
    return dt.isoformat()

def main():
    if len(sys.argv) != 2:
        print("Usage: convert.py <input_file>")
        sys.exit(1)
    infile = sys.argv[1]
    outfile = "/tmp/for_bq.jsonl"
    data = parse_sadf_json(infile)

    hostinfo = data["sysstat"]["hosts"][0]
    hostname = hostinfo["nodename"]
    timezone_str = hostinfo["timezone"]
    statistics = hostinfo["statistics"]

    with open(outfile, "w", encoding="utf-8") as out:
        for stat in statistics:
            ts_obj = stat["timestamp"]
            timestamp = to_iso8601(ts_obj["date"], ts_obj["time"], timezone_str)

            cpu_load = stat["cpu-load"][0]
            if cpu_load["cpu"] != "all":
                continue  # "all"のみ対象
            cpu = 100.0 - cpu_load["idle"]

            kbavail = stat["memory"]["avail"]
            ldavg_15 = stat["queue"]["ldavg-15"]

            rxkb_s = 0.0
            txkb_s = 0.0
            for dev in stat["network"]["net-dev"]:
                if dev["iface"] == "lo":
                    continue
                rxkb_s += dev["rxkB"]
                txkb_s += dev["txkB"]

            record = {
                "timestamp": timestamp,
                "host": hostname,
                "cpu": cpu,
                "kbavail": kbavail,
                "ldavg_15": ldavg_15,
                "rxkb_s": rxkb_s,
                "txkb_s": txkb_s
            }
            out.write(json.dumps(record, ensure_ascii=False) + "\n")

if __name__ == "__main__":
    main()