Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Sheet9// File : Packages/SheetData/Sources/orix-data-bench/main.swift10// Module : orix-data-bench11// Repository : https://git.spboucher.ai/orix-sheet12// Created : 2026-11-2113// Last Modified : 2026-11-2114//15// =============================================================================16// AUTHOR & PROFESSIONAL AFFILIATION17// =============================================================================18//19// Author : Simon-Pierre Boucher20// Position : Professor of Finance21// Institution : Université du Québec en Outaouais (UQO)22// Department : Département des sciences administratives23// Organization : SP Boucher24//25// Website : https://spboucher.ai26// Git : https://git.spboucher.ai27//28// =============================================================================29// OFFICIAL CONTACT DIRECTORY30// =============================================================================31//32// General : info@spboucher.ai33// Professional : contact@spboucher.ai34// Direct : simon@spboucher.ai35// Research : research@spboucher.ai36// Development : dev@spboucher.ai37// Support : support@spboucher.ai38// Security/Admin : admin@spboucher.ai39// Notifications : notifications@spboucher.ai40//41// Preferred Contact: support@spboucher.ai42//43// =============================================================================44// COPYRIGHT & INTELLECTUAL PROPERTY45// =============================================================================46//47// Copyright (c) 202648// Simon-Pierre Boucher49//50// All rights reserved where applicable.51//52// This file is governed by the applicable project license,53// copyright notices, institutional agreements and relevant law.54//55// Unless expressly authorized by the applicable license or56// rights holder, copying, redistribution, sublicensing,57// publication and commercial exploitation are not permitted.58//59// Existing third-party rights and notices remain unaffected.60//61// =============================================================================62// CONFIDENTIALITY & SECURITY63// =============================================================================64//65// Classification : Public66// Distribution : Public repository; use is governed by the project license67// Security Contact : admin@spboucher.ai68//69// Access to this source does not independently grant permission70// to redistribute or disclose confidential materials.71//72// Do not embed credentials, private keys, access tokens,73// certificates or confidential personal information.74//75// =============================================================================76// LICENSE & MAINTENANCE77// =============================================================================78//79// License : None specified (no license file in this repository)80// Maintainer : Simon-Pierre Boucher81// Technical Contact: dev@spboucher.ai82//83// =============================================================================84// END OF SP BOUCHER SOURCE HEADER85// =============================================================================8687import Foundation88import SheetCore89import SheetData9091// orix-data-bench: generates a synthetic dataset and measures ORIX Data Core on it.92// orix-data-bench --dir /tmp/orix-bench [--rows 10000000] [--threads N] [--memory-gb N] [--keep]93// Prints a JSON report on stdout (progress on stderr). The numbers are measurements of one machine, not guarantees.9495func argument(_ name: String) -> String? {96 let args = CommandLine.arguments97 guard let i = args.firstIndex(of: name), i + 1 < args.count else { return nil }98 return args[i + 1]99}100func log(_ s: String) { FileHandle.standardError.write(Data((s + "\n").utf8)) }101102func sysctlString(_ name: String) -> String {103 var size = 0104 sysctlbyname(name, nil, &size, nil, 0)105 guard size > 0 else { return "" }106 var buffer = [CChar](repeating: 0, count: size)107 sysctlbyname(name, &buffer, &size, nil, 0)108 return String(decoding: buffer.prefix { $0 != 0 }.map { UInt8(bitPattern: $0) }, as: UTF8.self)109}110111/// Peak resident set size of this process so far, in MiB (macOS reports ru_maxrss in bytes).112func peakRSSMiB() -> Double {113 var usage = rusage()114 getrusage(RUSAGE_SELF, &usage)115 return Double(usage.ru_maxrss) / 1_048_576116}117118func seconds<T>(_ body: () throws -> T) rethrows -> (T, Double) {119 let start = DispatchTime.now().uptimeNanoseconds120 let value = try body()121 return (value, Double(DispatchTime.now().uptimeNanoseconds - start) / 1e9)122}123124func fileSize(_ url: URL) -> Int {125 ((try? FileManager.default.attributesOfItem(atPath: url.path)[.size]) as? NSNumber)?.intValue ?? 0126}127128guard let dirPath = argument("--dir") else {129 log("usage: orix-data-bench --dir <directory> [--rows N] [--threads N] [--memory-gb N] [--keep]")130 exit(2)131}132let rows = Int(argument("--rows") ?? "10000000") ?? 10_000_000133let dir = URL(fileURLWithPath: dirPath, isDirectory: true)134try FileManager.default.createDirectory(at: dir, withIntermediateDirectories: true)135let csv = dir.appendingPathComponent("orders.csv"), parquet = dir.appendingPathComponent("orders.parquet")136let customersCSV = dir.appendingPathComponent("customers.csv")137let keep = CommandLine.arguments.contains("--keep")138139var steps: [[String: Any]] = []140@MainActor func record(_ name: String, _ secs: Double, _ extra: [String: Any] = [:]) {141 var entry: [String: Any] = ["step": name, "seconds": (secs * 10000).rounded() / 10000, "peak_rss_mib": peakRSSMiB().rounded()]142 for (k, v) in extra { entry[k] = v }143 steps.append(entry)144 log(String(format: "%-34@ %9.3f s peak RSS %6.0f MiB %@", name as NSString, secs, peakRSSMiB(), extra.isEmpty ? "" : "\(extra)" as NSString))145}146/// Runs a query three times and reports the best and the median wall time.147@MainActor func measure(_ name: String, _ extra: [String: Any] = [:], _ body: () throws -> [String: Any]) rethrows {148 var times = [Double](), info: [String: Any] = [:]149 for _ in 0..<3 {150 let (r, t) = try seconds(body)151 times.append(t)152 info = r153 }154 times.sort()155 var merged = extra156 for (k, v) in info { merged[k] = v }157 merged["best_seconds"] = (times[0] * 10000).rounded() / 10000158 record(name, times[1], merged)159}160161// 1. synthetic data, streamed to disk162let (csvBytes, genTime) = try seconds { try SyntheticGenerator.writeOrders(to: csv, rows: rows) }163record("generate_csv", genTime, ["rows": rows, "bytes": csvBytes])164_ = try SyntheticGenerator.writeCustomers(to: customersCSV)165166var options = DataEngineOptions()167if let t = argument("--threads").flatMap(Int.init) { options.threads = t }168if let g = argument("--memory-gb").flatMap(Double.init) { options.memoryLimitBytes = Int64(g * 1_073_741_824) }169options.tempDirectory = dir.appendingPathComponent("engine-tmp", isDirectory: true)170let engine = try DataEngine(options: options)171172// 2. CSV as a lazy view: aggregation straight from the file, then conversion to Parquet (streamed by DuckDB)173let (_, viewTime) = try seconds { try engine.registerView(csv, as: "orders_csv") }174record("register_csv_view", viewTime)175try measure("csv_view_aggregate") {176 let t = try engine.query("SELECT region, sum(amount) FROM orders_csv GROUP BY region")177 return ["groups": t.rowCount]178}179let (pqRows, pqTime) = try seconds { try engine.exportQuery("SELECT * FROM orders_csv", to: parquet, format: .parquet) }180record("generate_parquet_from_csv", pqTime, ["rows": pqRows, "bytes": fileSize(parquet)])181182// 3. import into the engine183let (info, importTime) = try seconds { try engine.importFile(csv, as: "orders") }184record("import_csv", importTime, ["rows": info.rowCount ?? -1, "columns": info.columns.count,185 "rows_per_second": Int(Double(info.rowCount ?? 0) / importTime)])186_ = try engine.importFile(customersCSV, as: "customers")187try measure("row_count") { ["rows": try engine.rowCount(of: "orders")] }188189// 4. queries on the imported table190try measure("filtered_aggregation") {191 let t = try engine.query("""192 SELECT category, count(*) AS n, sum(amount) AS total, avg(quantity) AS avg_qty FROM orders193 WHERE region IN ('north', 'online') AND order_date >= DATE '2023-01-01' AND amount > 500 GROUP BY category194 """)195 return ["groups": t.rowCount]196}197let pivot = PivotSpec(source: .dataset("orders"), rows: [PivotField("region"), PivotField("category")],198 columns: [PivotField("order_date", grouping: .datePart(.year))],199 values: [PivotValue("amount", .sum), PivotValue("id", .count, showAs: .percentOfGrandTotal)])200try measure("pivot_region_category_by_year") {201 let r = try PivotEngine.compute(pivot, workbook: nil, data: engine)202 return ["row_items": r.rowItems.count, "column_items": r.columnItems.count, "grouped_rows": r.groupCount]203}204let join = TransformPipeline(name: "bench join", source: .dataset("orders"), steps: [205 .join(dataset: "customers", kind: .inner, keys: [JoinKey("customer_id")], suffix: "_c"),206 .groupBy(keys: ["segment", "status"], aggregations: [Aggregation(.sum, "amount", as: "total"), Aggregation(.countRows, as: "n")]),207])208try measure("join_customers_group") {209 let t = try TransformRunner.run(join, engine: engine)210 return ["groups": t.rowCount]211}212try measure("preview_window_middle", ["offset": rows / 2, "limit": 200]) {213 ["rows": try engine.preview("orders", offset: rows / 2, limit: 200).rowCount]214}215try measure("preview_window_sorted", ["order_by": "amount DESC", "offset": 1000, "limit": 200]) {216 ["rows": try engine.preview("orders", offset: 1000, limit: 200, orderBy: [SortSpec("amount", ascending: false)]).rowCount]217}218try measure("preview_window_filtered", ["filter": "status = 'returned' AND quantity > 40", "offset": 10_000, "limit": 200]) {219 let f = FilterGroup(.all, [FilterCondition("status", .equal, "returned"), FilterCondition("quantity", .greater, 40)])220 return ["rows": try engine.preview("orders", offset: 10_000, limit: 200, filter: f).rowCount]221}222let (profile, profileTime) = try seconds { try engine.profile("orders", topValues: 3) }223record("profile_all_columns", profileTime, ["columns": profile.count])224225// 4b. the door to the grid: an aggregated result and a 100,000-row extract written into a worksheet (one undo step each)226let workbook = Workbook(sheetNames: ["Pivot", "Extract"])227let calc = CalcEngine(workbook: workbook)228let (pivotRange, pivotWriteTime) = try seconds {229 let r = try PivotEngine.compute(pivot, workbook: nil, data: engine)230 return try PivotEngine.write(PivotEngine.renderGrid(r, spec: pivot), into: calc, sheet: 0, at: CellRef(0, 0))231}232record("pivot_compute_and_write_to_sheet", pivotWriteTime, ["range": pivotRange.a1])233let extractRows = min(100_000, rows)234let (extractRange, extractTime) = try seconds {235 try engine.materializeQuery("SELECT * FROM orders WHERE status = 'returned' LIMIT \(extractRows)", into: calc, sheet: 1, at: CellRef(0, 0), includeHeader: true)236}237record("materialize_extract_to_sheet", extractTime, ["range": extractRange.a1, "cells": extractRange.count])238var refused = "not refused"239do { _ = try engine.materializeQuery("SELECT * FROM orders", into: calc, sheet: 1, at: CellRef(0, 0), includeHeader: true) } catch { refused = "\(error)" }240record("materialize_full_table_refused", 0, ["error": refused])241242// 5. Parquet: lazy scan with pushdown243_ = try engine.registerView(parquet, as: "orders_pq")244let lo = rows - rows / 10, hi = lo + 10_000245try measure("parquet_pushdown_filter", ["predicate": "id BETWEEN \(lo) AND \(hi)"]) {246 let t = try engine.query("SELECT count(*), sum(amount) FROM orders_pq WHERE id BETWEEN \(lo) AND \(hi)")247 return ["matched": Int(t.value(row: 0, column: 0).doubleValue ?? 0)]248}249try measure("parquet_projection_aggregate") {250 let t = try engine.query("SELECT category, sum(amount) FROM orders_pq GROUP BY category")251 return ["groups": t.rowCount]252}253try measure("parquet_full_filtered_aggregation") {254 let t = try engine.query("""255 SELECT category, count(*) AS n, sum(amount) AS total, avg(quantity) AS avg_qty FROM orders_pq256 WHERE region IN ('north', 'online') AND order_date >= DATE '2023-01-01' AND amount > 500 GROUP BY category257 """)258 return ["groups": t.rowCount]259}260261// 6. sanity: the three copies agree (amounts compared as integer cents: double sums depend on the summation order)262let cents = "sum(CAST(round(amount * 100) AS BIGINT))"263let check = try engine.query("""264 SELECT (SELECT count(*) FROM orders), (SELECT count(*) FROM orders_pq), (SELECT count(*) FROM orders_csv),265 (SELECT \(cents) FROM orders) = (SELECT \(cents) FROM orders_pq) AND (SELECT \(cents) FROM orders) = (SELECT \(cents) FROM orders_csv)266 """).row(0)267268let report: [String: Any] = [269 "tool": "orix-data-bench",270 "date": ISO8601DateFormatter().string(from: Date()),271 "duckdb": DataEngine.duckDBVersion,272 "hardware": [273 "model": sysctlString("hw.model"), "cpu": sysctlString("machdep.cpu.brand_string"),274 "cores": ProcessInfo.processInfo.activeProcessorCount,275 "memory_gib": Double(ProcessInfo.processInfo.physicalMemory) / 1_073_741_824,276 "os": ProcessInfo.processInfo.operatingSystemVersionString,277 ],278 "engine": ["threads": options.threads ?? ProcessInfo.processInfo.activeProcessorCount,279 "memory_limit_gib": Double(options.memoryLimitBytes ?? Int64(ProcessInfo.processInfo.physicalMemory / 2)) / 1_073_741_824],280 "dataset": [281 "rows": rows, "columns": ["id BIGINT", "category VARCHAR (20 values)", "region VARCHAR (8)", "order_date DATE (2020–2024)",282 "amount DOUBLE", "quantity BIGINT (1–50)", "customer_id BIGINT (100,000)", "status VARCHAR (5)"],283 "csv_bytes": csvBytes, "parquet_bytes": fileSize(parquet), "customers_rows": SyntheticGenerator.customerCount,284 ],285 "consistency": ["table_rows": check[0].text, "parquet_rows": check[1].text, "csv_rows": check[2].text, "amount_cents_equal": check[3].text],286 "steps": steps,287 "peak_rss_mib": peakRSSMiB().rounded(),288]289let json = try JSONSerialization.data(withJSONObject: report, options: [.prettyPrinted, .sortedKeys])290print(String(decoding: json, as: UTF8.self))291if !keep {292 for url in [csv, parquet, customersCSV, dir.appendingPathComponent("engine-tmp")] { try? FileManager.default.removeItem(at: url) }293}294