import { useEffect, useMemo, useRef, useState } from "react"; import { useSQLQuery, useDiveState } from "@motherduck/react-sql-query"; import * as d3 from "d3"; export const REQUIRED_DATABASES = []; const PRESETS = [ { label: "Hacker News 2021–22", url: "s3://us-prd-motherduck-open-datasets/hacker_news/parquet/hacker_news_2021_2022.parquet", }, { label: "NYC yellow taxi, Jan 2023", url: "https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2023-01.parquet", }, { label: "TPC-H lineitem", url: "https://shell.duckdb.org/data/tpch/0_01/parquet/lineitem.parquet", }, { label: "Job postings (hive)", url: "s3://us-prd-motherduck-open-datasets/job_postings/parquet/year=*/month=*/jobs.parquet", }, ]; const N = (v: unknown): number => (v == null ? 0 : Number(v)); const lit = (s: string) => s.replace(/'/g, "''"); const qi = (s: string) => `"${s.replace(/"/g, '""')}"`; const C = { bg: "#0b0e22", panel: "#111533", text: "#e9eaff", muted: "#8d92bf", line: "#262b52", blue: "#7c6cf6", orange: "#5ecbff", steel: "#8e9bff", grey: "#a3a8d6", footer: "#3b4077", empty: "#1f2448", zero: "#10132e", error: "#ff7a8a", soft: "#c3c7e6", }; const TYPE_COLOR: Record = { BYTE_ARRAY: C.blue, FIXED_LEN_BYTE_ARRAY: C.blue, INT64: C.steel, INT32: C.steel, INT96: C.steel, DOUBLE: "#3fd9b8", FLOAT: "#3fd9b8", BOOLEAN: C.orange, }; const MIN_H = 420; const W = 10; const H = 6; const D = 2.4; const MINW = 0.1; const RG_GAP = 0.2; const COL_GAP = 0.3; const FOOT_H = 0.14; const BINS = 12; const PAGE = 8; const MAX_RG = 200; const MAX_HIST_COLS = 40; const FACES = [ { n: [1, 0, 0], axis: "x", sign: 1, idx: [1, 3, 7, 5] }, { n: [-1, 0, 0], axis: "x", sign: -1, idx: [0, 4, 6, 2] }, { n: [0, 1, 0], axis: "y", sign: 1, idx: [2, 6, 7, 3] }, { n: [0, -1, 0], axis: "y", sign: -1, idx: [0, 1, 5, 4] }, { n: [0, 0, 1], axis: "z", sign: 1, idx: [4, 5, 7, 6] }, { n: [0, 0, -1], axis: "z", sign: -1, idx: [0, 2, 3, 1] }, ]; // Map a DuckDB column type to how its values become one number for the histogram. function kindOf(type: string): string | null { const t = type.toUpperCase(); if (/STRUCT|MAP|UNION|\[\]/.test(t)) return null; if (/^(TINYINT|SMALLINT|INTEGER|BIGINT|HUGEINT|UTINYINT|USMALLINT|UINTEGER|UBIGINT|UHUGEINT|FLOAT|DOUBLE|DECIMAL|REAL)/.test(t)) return "num"; if (/^(DATE|TIMESTAMP)/.test(t)) return "time"; if (t === "VARCHAR") return "len"; if (t === "BLOB") return "len"; if (t === "BOOLEAN") return "bool"; return null; } function histExpr(name: string, type: string): string | null { const q = qi(name); const k = kindOf(type); if (k === "num") return `${q}::DOUBLE`; if (k === "time") return `epoch(${q}::TIMESTAMP)::DOUBLE`; if (k === "len") return type.toUpperCase() === "BLOB" ? `octet_length(${q})::DOUBLE` : `length(${q})::DOUBLE`; if (k === "bool") return `${q}::INT::DOUBLE`; return null; } const ratioScale = d3.scaleLog().domain([1, 12]).range([0, 1]).clamp(true); const SPACE = ["#1e2257", "#6c5ce7", "#8fe3ff"]; const ratioInterp = d3.piecewise(d3.interpolateLab, SPACE); const valInterp = d3.piecewise(d3.interpolateLab, SPACE); const fmtB = (b: number) => b >= 1e9 ? `${(b / 1e9).toFixed(2)} GB` : b >= 1e6 ? `${(b / 1e6).toFixed(1)} MB` : b >= 1e3 ? `${(b / 1e3).toFixed(1)} kB` : `${b} B`; const fmtN = d3.format(","); const trunc = (s: string, n: number) => (s.length > n ? s.slice(0, n - 1) + "…" : s); const baseName = (u: string) => u.split(/[/?#]/).filter(Boolean).pop() ?? u; const isGlob = (u: string) => /[*?\[]/.test(u); const rgRowsScale = d3.scaleLog().domain([1e3, 1e6]).range([0, 1]).clamp(true); const GROUP_COLORS = ["#f472b6", "#fbbf24", "#34d399", "#60a5fa", "#c084fc", "#fb923c", "#2dd4bf", "#f87171"]; const natural = (a: string, b: string) => a.localeCompare(b, undefined, { numeric: true }); // Dataset view. First partition level = rows (and color); every deeper level + file name = columns, // with wider gaps wherever the second-level folder changes. function dsLayout(files: any[], e: number) { const tR = ease(Math.min(1, e * 2)); const tC = ease(Math.max(0, e * 2 - 1)); const depth = Math.max(0, ...files.map((f: any) => f.parts.length)); const rowOf = (f: any) => (f.parts.length ? f.parts[0].join("=") : f.dir); const colOf = (f: any) => [...f.parts.slice(1).map((p: string[]) => p.join("=")), f.name].join("/"); const runOf = (f: any) => (f.parts.length >= 2 ? f.parts[1].join("=") : f.name); const rows = [...new Set(files.map(rowOf))].sort(natural); const colKeys = [...new Set(files.map(colOf))].sort(natural); const colRun = new Map(); files.forEach((f: any) => colRun.set(colOf(f), runOf(f))); const FW = 1, FD = 1, HH = 5; const xGap = 0.25 + 0.35 * tC; const runGap = depth >= 3 ? 0.6 + 0.6 * tC : 0; const colX = new Map(); const runs: { key: string; x0: number; x1: number }[] = []; let x = 0; colKeys.forEach((k, i) => { const r = colRun.get(k)!; const newRun = !runs.length || runs[runs.length - 1].key !== r; if (i > 0) x += xGap + (newRun ? runGap : 0); colX.set(k, x); if (newRun) runs.push({ key: r, x0: x, x1: x + FW }); else runs[runs.length - 1].x1 = x + FW; x += FW; }); const totW = x; const zGap = 0.6 + 1.2 * tR; const slabGap = 0.14 * tR; const plateH = 0.04, footH = 0.06, footGap = 0.04 + 0.2 * tR; const maxRows = Math.max(1, ...files.map((f: any) => f.rows)); const maxRg = Math.max(1, ...files.map((f: any) => f.rgs.length)); const totD = rows.length * FD + (rows.length - 1) * zGap; const totH = HH + (maxRg - 1) * slabGap + footH + footGap + plateH; const ox = totW / 2, oz = totD / 2, oy = totH / 2; const rowColor = new Map(rows.map((r, i) => [r, GROUP_COLORS[i % GROUP_COLORS.length]])); const rowZ = new Map(rows.map((r, i) => [r, i * (FD + zGap) - oz])); const rowSpan = new Map(); files.forEach((f: any) => { const r = rowOf(f); const cx = colX.get(colOf(f))!; const s = rowSpan.get(r); rowSpan.set(r, s ? [Math.min(s[0], cx), Math.max(s[1], cx + FW)] : [cx, cx + FW]); }); const boxes: any[] = []; const labels: any[] = []; rows.forEach((r) => { const z0 = rowZ.get(r)!; const [a, b] = rowSpan.get(r)!; const col = rowColor.get(r)!; boxes.push({ x0: a - ox - 0.2, x1: b - ox + 0.2, y0: -oy, y1: -oy + plateH, z0: z0 - 0.2, z1: z0 + FD + 0.2, plate: true, color: (d3.color(col) as any).darker(1.8).formatHex(), }); labels.push({ x: a - ox - 0.45, y: -oy, z: z0 + FD / 2, text: r || "(root)", align: "right", color: col, bold: true }); }); files.forEach((f: any) => { const r = rowOf(f); const z0 = rowZ.get(r)!; const x0 = colX.get(colOf(f))! - ox; let y = -oy + plateH; boxes.push({ x0, x1: x0 + FW, y0: y, y1: y + footH, z0, z1: z0 + FD, file: f.file, f, kind: "foot", color: rowColor.get(r) }); y += footH + footGap; f.rgs.forEach((rg: any) => { const h = (HH * rg.n) / maxRows; boxes.push({ x0, x1: x0 + FW, y0: y, y1: y + h, z0, z1: z0 + FD, file: f.file, f, rg, kind: "rg", color: ratioInterp(rgRowsScale(Math.max(1, rg.n))), }); y += h + slabGap; }); }); const frontZ = rowZ.get(rows[rows.length - 1])! + FD + 0.5; const step = Math.ceil(runs.length / 24); runs.forEach((run, i) => { if (i % step === 0) labels.push({ x: (run.x0 + run.x1) / 2 - ox, y: -oy, z: frontZ, text: run.key, align: "center" }); }); return { boxes, totW, totH, totD, labels, depth, groups: rows.map((r) => ({ key: r || "(root)", color: rowColor.get(r) })), keys: files.length ? files[0].parts.map((p: string[]) => p[0]) : [], }; } function fmtBin(range: any, x: number) { const v = range.lg ? Math.expm1(x) : x; if (range.kind === "time") return new Date(v * 1000).toISOString().slice(0, 10); if (range.kind === "bool") return v < 0.5 ? "false" : "true"; const s = Math.abs(v) >= 100 ? d3.format(",.0f")(v) : d3.format(".3~g")(v); return range.kind === "len" ? `${s} chars` : s; } const ease = (t: number) => (t < 0.5 ? 2 * t * t : 1 - Math.pow(-2 * t + 2, 2) / 2); function inPoly(x: number, y: number, pts: number[][]) { let c = false; for (let i = 0, j = pts.length - 1; i < pts.length; j = i++) { const xi = pts[i][0], yi = pts[i][1], xj = pts[j][0], yj = pts[j][1]; if (yi > y !== yj > y && x < ((xj - xi) * (y - yi)) / (yj - yi) + xi) c = !c; } return c; } function layout(chunks: any[], cols: any[], rgs: any[], widthMode: string, e: number) { const tR = ease(Math.min(1, e * 2)); const tC = ease(Math.max(0, e * 2 - 1)); // Gaps scale with the number of parts, so a 4-row-group file opens as wide as a 32-row-group one. const rgGap = tR * Math.min(1.4, (H * 0.9) / Math.max(1, rgs.length - 1)); const colGap = tC * Math.min(0.9, (W * 0.6) / Math.max(1, cols.length - 1)); const tot = d3.sum(cols, (c: any) => c.comp) || 1; let w = cols.map((c: any) => widthMode === "bytes" ? Math.max(MINW, (W * c.comp) / tot) : W / cols.length ); const ws = d3.sum(w); w = w.map((v: number) => (v * W) / ws); const x0: number[] = []; let x = 0; w.forEach((v: number) => { x0.push(x); x += v + colGap; }); const totW = x - colGap; const totRows = d3.sum(rgs, (r: any) => r.n) || 1; const yTop: number[] = []; const hs: number[] = []; let acc = 0; rgs.forEach((r: any) => { const h = (H * r.n) / totRows; yTop.push(-acc); hs.push(h); acc += h + rgGap; }); const footGap = 0.08 + 1.0 * tR; const footTop = -(acc - rgGap) - footGap; const totH = -(footTop - FOOT_H); const ox = totW / 2; const oy = totH / 2; const colIdx = new Map(cols.map((c: any, i: number) => [c.col, i])); const rgIdx = new Map(rgs.map((r: any, i: number) => [r.rg, i])); const boxes: any[] = chunks.map((ch: any) => { const ci = colIdx.get(ch.col) as number; const ri = rgIdx.get(ch.rg) as number; return { x0: x0[ci] - ox, x1: x0[ci] + w[ci] - ox, y0: yTop[ri] - hs[ri] + oy, y1: yTop[ri] + oy, chunk: ch, }; }); boxes.push({ x0: -ox, x1: totW - ox, y0: footTop - FOOT_H + oy, y1: footTop + oy, chunk: null }); return { boxes, totW, totH }; } function baseColor(ch: any, colorBy: string, hasHist: boolean) { if (!ch) return C.footer; if (colorBy === "values") return hasHist ? C.zero : C.empty; if (colorBy === "ratio") return ratioInterp(ratioScale(Math.max(1, ch.unc / Math.max(1, ch.comp)))); if (colorBy === "encoding") return ch.enc.includes("DICTIONARY") ? C.orange : C.blue; return TYPE_COLOR[ch.type] ?? C.grey; } function Seg({ options, value, onChange }: any) { return (

{options.map((o: any) => { const active = o.value === value; return ( ); })}

); } function Swatch({ color, label }: any) { return ( {label} ); } function MiniHist({ bins, range }: any) { const m = Math.max(1, ...bins); const bw = 196 / BINS; return (

{range && (

{fmtBin(range, range.lo)} {fmtBin(range, range.hi)}

)}

); } const Btn = ({ onClick, disabled, children }: any) => ( ); export default function ParquetAnatomy() { const [fileUrl, setFileUrl] = useDiveState("file", PRESETS[0].url); const [colorBy, setColorBy] = useDiveState("colorBy", "values"); const [widthMode, setWidthMode] = useDiveState("widthMode", "bytes"); const [sel, setSel] = useDiveState("sel", null); const [listUrl, setListUrl] = useDiveState("list", null); const [ds, setDs] = useDiveState("ds", null); const [draft, setDraft] = useState(""); const [page, setPage] = useState(0); const [explode, setExplode] = useState(0); const [rot, setRot] = useState({ yaw: -0.65, pitch: 0.42 }); const [zoom, setZoom] = useState(1); const [pan, setPan] = useState({ x: 0, y: 0 }); const zoomRef = useRef(1); const panRef = useRef({ x: 0, y: 0 }); zoomRef.current = zoom; panRef.current = pan; const [hover, setHover] = useState(null); const [width, setWidth] = useState(760); const [HEIGHT, setCanvasH] = useState(560); const F = lit(fileUrl); const preset = PRESETS.find((p) => p.url === fileUrl); const inDs = isGlob(fileUrl); const chunkQ = useSQLQuery(` SELECT row_group_id::INT AS rg, column_id::INT AS col, path_in_schema AS name, type, row_group_num_rows::BIGINT AS n_rows, total_compressed_size::BIGINT AS comp, total_uncompressed_size::BIGINT AS unc, compression, encodings, data_page_offset::BIGINT AS off, stats_min_value AS smin, stats_max_value AS smax FROM parquet_metadata('${F}') ORDER BY rg, col `, { enabled: !inDs }); const metaQ = useSQLQuery(` SELECT num_rows::BIGINT AS num_rows, num_row_groups::BIGINT AS num_row_groups, file_size_bytes::BIGINT AS file_size, footer_size::BIGINT AS footer_size, created_by, format_version::BIGINT AS format_version FROM parquet_file_metadata('${F}') `, { enabled: !inDs }); const dsQ = useSQLQuery( `SELECT file_name AS f, row_group_id::INT AS rg, any_value(row_group_num_rows)::BIGINT AS n, sum(total_compressed_size)::BIGINT AS comp FROM parquet_metadata('${F}') GROUP BY ALL ORDER BY f, rg`, { enabled: inDs } ); const dsMetaQ = useSQLQuery( `SELECT file_name AS f, file_size_bytes::BIGINT AS size, footer_size::BIGINT AS foot FROM parquet_file_metadata('${F}') ORDER BY f`, { enabled: inDs } ); const schemaQ = useSQLQuery(`DESCRIBE SELECT * FROM read_parquet('${F}')`, { enabled: !inDs }); // Path lists (e.g. Common Crawl's *.paths.gz): gzipped text, one Parquet path per line. const LF = lit(listUrl ?? ""); const listQ = useSQLQuery( `SELECT column0 AS p FROM read_csv('${LF}', header = false, delim = '\t', quote = '', columns = {'column0': 'VARCHAR'}) WHERE column0 ILIKE '%.parquet' LIMIT 5000`, { enabled: !!listUrl } ); const listBase = ((listUrl ?? "").match(/^([a-z0-9]+:\/\/[^/]+)/i) || ["", ""])[1]; const listPaths = useMemo( () => (Array.isArray(listQ.data) ? listQ.data : []).map((r: any) => { const p = String(r.p).trim(); return /^[a-z0-9]+:\/\//i.test(p) ? p : `${listBase}/${p.replace(/^\//, "")}`; }), [listQ.data, listBase] ); useEffect(() => { if (listUrl && listPaths.length && !listPaths.includes(fileUrl)) setFileUrl(listPaths[0]); }, [listPaths]); const schema = useMemo( () => (Array.isArray(schemaQ.data) ? schemaQ.data : []).map((r: any) => ({ name: String(r.column_name), type: String(r.column_type), })), [schemaQ.data] ); // Histogram columns get safe aliases c0, c1, … so any column name works inside UNPIVOT. const histCols = useMemo( () => schema .map((c: any) => ({ ...c, expr: histExpr(c.name, c.type), kind: kindOf(c.type) })) .filter((c: any) => c.expr) .slice(0, MAX_HIST_COLS) .map((c: any, i: number) => ({ ...c, alias: `c${i}` })), [schema] ); const histSelect = histCols.map((c: any) => `${c.expr} AS ${c.alias}`).join(", ") || "1::DOUBLE AS c0"; const histQ = useSQLQuery( ` WITH rg AS MATERIALIZED ( SELECT rg, sum(n) OVER (ORDER BY rg) - n AS start FROM (SELECT DISTINCT row_group_id AS rg, row_group_num_rows AS n FROM parquet_metadata('${F}')) ), raw AS MATERIALIZED ( SELECT r, col, v FROM ( UNPIVOT ( SELECT file_row_number AS r, ${histSelect} FROM read_parquet('${F}', file_row_number = true) ) ON COLUMNS(* EXCLUDE r) INTO NAME col VALUE v ) ), s AS MATERIALIZED ( SELECT col, (min(v) >= 0 AND max(v) > 50 * (approx_quantile(v, 0.5) + 1)) AS lg FROM raw GROUP BY col ), t AS MATERIALIZED ( SELECT r, col, s.lg, CASE WHEN s.lg THEN ln(1 + v) ELSE v END AS x FROM raw JOIN s USING (col) ), b AS MATERIALIZED (SELECT col, min(x) AS lo, max(x) AS hi FROM t GROUP BY col) SELECT rg.rg::INT AS rg, t.col, least(11, floor(12 * (x - lo) / nullif(hi - lo, 0)))::INT AS bin, count(*)::BIGINT AS c, any_value(lo) AS lo, any_value(hi) AS hi, any_value(t.lg) AS lg FROM t JOIN b USING (col) ASOF JOIN rg ON t.r >= rg.start GROUP BY ALL `, { enabled: colorBy === "values" && histCols.length > 0 && !inDs } ); const wrapRef = useRef(null); const canvasRef = useRef(null); const hitRef = useRef([]); const dragRef = useRef(null); const animRef = useRef(0); const playedRef = useRef(false); const prevFileRef = useRef(fileUrl); const allChunks = useMemo( () => (Array.isArray(chunkQ.data) ? chunkQ.data : []).map((r: any) => { const name = String(r.name); return { rg: N(r.rg), col: N(r.col), name, top: name.split(".")[0], type: String(r.type), n: N(r.n_rows), comp: N(r.comp), unc: N(r.unc), codec: String(r.compression), enc: String(r.encodings), off: N(r.off), smin: r.smin == null ? null : String(r.smin), smax: r.smax == null ? null : String(r.smax), }; }), [chunkQ.data] ); const totalRg = useMemo(() => new Set(allChunks.map((c: any) => c.rg)).size, [allChunks]); const chunks = useMemo(() => allChunks.filter((c: any) => c.rg < MAX_RG), [allChunks]); const cols = useMemo( () => d3 .rollups( allChunks, (v: any[]) => ({ col: v[0].col, name: v[0].name, comp: d3.sum(v, (d: any) => d.comp), unc: d3.sum(v, (d: any) => d.unc), }), (d: any) => d.col ) .map((d: any) => d[1]) .sort((a: any, b: any) => a.col - b.col), [allChunks] ); const rgs = useMemo( () => d3 .rollups(chunks, (v: any[]) => ({ rg: v[0].rg, n: v[0].n }), (d: any) => d.rg) .map((d: any) => d[1]) .sort((a: any, b: any) => a.rg - b.rg), [chunks] ); const rgStart = useMemo(() => { const m = new Map(); let a = 0; d3.rollups(allChunks, (v: any[]) => v[0].n, (d: any) => d.rg) .sort((x: any, y: any) => x[0] - y[0]) .forEach(([rg, n]: any) => { m.set(rg, a); a += n; }); return m; }, [allChunks]); const hist = useMemo(() => { const m = new Map(); const rng = new Map(); const byAlias = new Map(histCols.map((c: any) => [c.alias, c])); (Array.isArray(histQ.data) ? histQ.data : []).forEach((r: any) => { if (r.bin == null) return; const hc: any = byAlias.get(String(r.col)); if (!hc) return; const key = `${N(r.rg)}|${hc.name}`; let a = m.get(key); if (!a) { a = new Array(BINS).fill(0); m.set(key, a); } a[N(r.bin)] += N(r.c); if (!rng.has(hc.name)) rng.set(hc.name, { lo: N(r.lo), hi: N(r.hi), lg: !!r.lg, kind: hc.kind }); }); return { m, rng }; }, [histQ.data, histCols]); const meta = Array.isArray(metaQ.data) && metaQ.data[0] ? metaQ.data[0] : null; const dsFiles = useMemo(() => { const m = new Map(); (Array.isArray(dsQ.data) ? dsQ.data : []).forEach((r: any) => { const f = String(r.f); if (!m.has(f)) m.set(f, { file: f, rgs: [], rows: 0, comp: 0 }); const o = m.get(f); o.rgs.push({ rg: N(r.rg), n: N(r.n), comp: N(r.comp) }); o.rows += N(r.n); o.comp += N(r.comp); }); const extra = new Map( (Array.isArray(dsMetaQ.data) ? dsMetaQ.data : []).map((r: any) => [ String(r.f), { size: N(r.size), foot: N(r.foot) }, ]) ); return [...m.values()].slice(0, 400).map((o: any) => ({ ...o, ...((extra.get(o.file) as any) || {}), parts: [...o.file.matchAll(/([^/=]+)=([^/]+)/g)].map((x: any) => [x[1], x[2]]), dir: o.file.slice(0, o.file.lastIndexOf("/")), name: baseName(o.file), })); }, [dsQ.data, dsMetaQ.data]); const dsStats = useMemo(() => { if (!dsFiles.length) return null; const rows = d3.sum(dsFiles, (f: any) => f.rows); const rgCount = d3.sum(dsFiles, (f: any) => f.rgs.length); return { rows, rgCount, size: d3.sum(dsFiles, (f: any) => f.size ?? f.comp), keys: dsFiles[0].parts.map((p: string[]) => p[0]), medRows: d3.median(dsFiles, (f: any) => f.rows) as number, avgRg: rows / Math.max(1, rgCount), }; }, [dsFiles]); const totComp = d3.sum(cols, (c: any) => c.comp); const totUnc = d3.sum(cols, (c: any) => c.unc); // Which column is best clustered: smallest average row-group range relative to the file-wide range. const clustering = useMemo(() => { let best: any = null; for (const c of schema) { const kind = kindOf(c.type); if (kind !== "num" && kind !== "time") continue; const ch = allChunks.filter((x: any) => x.name === c.name && x.smin != null && x.smax != null); if (ch.length < 2) continue; const parse = kind === "time" ? (s: string) => Date.parse(s.replace(" ", "T")) : (s: string) => Number(s); const mins = ch.map((x: any) => parse(x.smin)); const maxs = ch.map((x: any) => parse(x.smax)); if (mins.some((v: number) => isNaN(v)) || maxs.some((v: number) => isNaN(v))) continue; const g = Math.max(...maxs) - Math.min(...mins); if (!g) continue; const cov = d3.mean(ch.map((_: any, i: number) => (maxs[i] - mins[i]) / g)) as number; if (!best || cov < best.cov) best = { name: c.name, cov }; } return best; }, [allChunks, schema]); const geo = useMemo( () => inDs ? dsFiles.length ? dsLayout(dsFiles, explode) : null : chunks.length ? layout(chunks, cols, rgs, widthMode, explode) : null, [inDs, dsFiles, chunks, cols, rgs, widthMode, explode] ); const colors = useMemo( () => geo ? geo.boxes.map((b: any) => d3.rgb(b.color ?? baseColor(b.chunk, colorBy, !!(b.chunk && hist.m.has(`${b.chunk.rg}|${b.chunk.top}`)))) ) : [], [geo, colorBy, hist] ); const strips = useMemo(() => { if (!geo || colorBy !== "values") return null; return geo.boxes.map((b: any) => { if (!b.chunk) return null; const a = hist.m.get(`${b.chunk.rg}|${b.chunk.top}`); if (!a) return null; const m = Math.max(...a); return a.map((c: number) => (c > 0 ? d3.rgb(valInterp(Math.sqrt(c / m))) : null)); }); }, [geo, colorBy, hist]); // Row drill-down const footerSel = !!(sel && sel.footer); const selChunk = sel && !sel.footer ? allChunks.find((c: any) => c.rg === sel.rg && c.col === sel.col) : null; const selTop = selChunk ? selChunk.top : null; const ctxCols = schema.filter((c: any) => c.name !== selTop && kindOf(c.type) != null).slice(0, 4); const rowSelect = [ ...ctxCols.map((c: any, i: number) => `left(CAST(${qi(c.name)} AS VARCHAR), 80) AS k${i}`), `left(CAST(${qi(selTop ?? (schema[0]?.name ?? "x"))} AS VARCHAR), 600) AS val`, ].join(", "); const rgBase = selChunk ? rgStart.get(selChunk.rg) ?? 0 : 0; const rowA = selChunk ? rgBase + page * PAGE : 0; const rowB = selChunk ? Math.min(rowA + PAGE, rgBase + selChunk.n) : 0; const rowsQ = useSQLQuery( `SELECT file_row_number::BIGINT AS rn, ${rowSelect} FROM read_parquet('${F}', file_row_number = true) WHERE file_row_number >= ${rowA} AND file_row_number < ${rowB} ORDER BY rn`, { enabled: !!selChunk && schema.length > 0 } ); const rows = Array.isArray(rowsQ.data) ? rowsQ.data : []; // Footer drill-down const footSchemaQ = useSQLQuery( `SELECT name, type, repetition_type AS rep, num_children::BIGINT AS kids, logical_type AS logical, converted_type AS conv FROM parquet_schema('${F}')`, { enabled: footerSel } ); const kvQ = useSQLQuery( `SELECT coalesce(try(decode(key)), key::VARCHAR) AS k, octet_length(value)::BIGINT AS len, left(coalesce(try(decode(value)), value::VARCHAR), 300) AS v FROM parquet_kv_metadata('${F}')`, { enabled: footerSel } ); const footSchema = (Array.isArray(footSchemaQ.data) ? footSchemaQ.data : []).slice(1); const kv = Array.isArray(kvQ.data) ? kvQ.data : []; const rgSummary = useMemo( () => d3 .rollups( allChunks, (v: any[]) => ({ rg: v[0].rg, n: v[0].n, comp: d3.sum(v, (d: any) => d.comp) }), (d: any) => d.rg ) .map((d: any) => d[1]) .sort((a: any, b: any) => a.rg - b.rg), [allChunks] ); const maxRgComp = (d3.max(rgSummary, (r: any) => r.comp) as number) || 1; const statsCount = allChunks.filter((c: any) => c.smin != null).length; const drawerOpen = !!selChunk || footerSel; const tableCols = [ { k: "rn", label: "row" }, ...ctxCols.map((c: any, i: number) => ({ k: `k${i}`, label: c.name })), ...(selTop ? [{ k: "val", label: selTop }] : []), ]; const maxPage = selChunk ? Math.ceil(selChunk.n / PAGE) - 1 : 0; // Reset view when switching files (not on first mount, so shared links keep their selection). useEffect(() => { if (prevFileRef.current === fileUrl) return; prevFileRef.current = fileUrl; setSel(undefined); setPage(0); setHover(null); cancelAnimationFrame(animRef.current); setExplode(0); setZoom(1); setPan({ x: 0, y: 0 }); playedRef.current = false; }, [fileUrl]); useEffect(() => { const el = wrapRef.current; if (!el) return; const ro = new ResizeObserver((entries) => { setWidth(Math.max(320, Math.floor(entries[0].contentRect.width))); }); ro.observe(el); return () => ro.disconnect(); }, []); // Fill the rest of the viewport below the controls. useEffect(() => { const fit = () => { const el = wrapRef.current; if (!el) return; const top = el.getBoundingClientRect().top + window.scrollY; setCanvasH(Math.max(MIN_H, Math.floor(window.innerHeight - top - 16))); }; fit(); window.addEventListener("resize", fit); const ro = new ResizeObserver(fit); ro.observe(document.body); return () => { window.removeEventListener("resize", fit); ro.disconnect(); }; }, []); const explodeRef = useRef(0); explodeRef.current = explode; const animateTo = (target: number) => { cancelAnimationFrame(animRef.current); const start = explodeRef.current; const t0 = performance.now(); const dur = 1600 * Math.abs(target - start) + 150; const step = (t: number) => { const p = Math.min(1, (t - t0) / dur); setExplode(start + (target - start) * p); if (p < 1) animRef.current = requestAnimationFrame(step); }; animRef.current = requestAnimationFrame(step); }; useEffect(() => { if ((inDs ? dsFiles.length : chunks.length) && !playedRef.current) { playedRef.current = true; const id = setTimeout(() => animateTo(1), 400); return () => clearTimeout(id); } }, [chunks.length, dsFiles.length, fileUrl]); useEffect(() => () => cancelAnimationFrame(animRef.current), []); useEffect(() => { if (!drawerOpen) return; const onKey = (e: KeyboardEvent) => { if (e.key === "Escape") setSel(undefined); }; window.addEventListener("keydown", onKey); return () => window.removeEventListener("keydown", onKey); }, [drawerOpen]); const reduceMotion = typeof window !== "undefined" && window.matchMedia?.("(prefers-reduced-motion: reduce)").matches; const hoverCol = hover && geo && geo.boxes[hover.i]?.chunk ? geo.boxes[hover.i].chunk.col : null; const hoverFile = hover && geo ? geo.boxes[hover.i]?.file ?? null : null; useEffect(() => { const cv = canvasRef.current; if (!cv) return; const dpr = window.devicePixelRatio || 1; cv.width = width * dpr; cv.height = HEIGHT * dpr; cv.style.width = `${width}px`; cv.style.height = `${HEIGHT}px`; const ctx = cv.getContext("2d"); if (!ctx) return; ctx.setTransform(dpr, 0, 0, dpr, 0, 0); ctx.clearRect(0, 0, width, HEIGHT); if (!geo) return; const ca = Math.cos(rot.yaw), sa = Math.sin(rot.yaw); const cb = Math.cos(rot.pitch), sb = Math.sin(rot.pitch); const GD = geo.totD ?? D; const scale = Math.min(width / Math.hypot(geo.totW, GD), HEIGHT / Math.hypot(geo.totH, GD)) * 0.82; const cx = width / 2, cy = HEIGHT / 2, F0 = 40; const P = (x: number, y: number, z: number) => { const x1 = x * ca + z * sa; const z1 = -x * sa + z * ca; const y2 = y * cb - z1 * sb; const z2 = y * sb + z1 * cb; const s = F0 / (F0 - z2); const S = s * scale * zoom; return [cx + pan.x + x1 * S, cy + pan.y - y2 * S]; }; const rotN = (n: number[]) => { const x1 = n[0] * ca + n[2] * sa; const z1 = -n[0] * sa + n[2] * ca; return [x1, n[1] * cb - z1 * sb, n[1] * sb + z1 * cb]; }; const Lr = [-0.35, 0.85, 0.4]; const Ll = Math.hypot(Lr[0], Lr[1], Lr[2]); const L = Lr.map((v) => v / Ll); const faces = FACES.map((f) => ({ ...f, rn: rotN(f.n) })) .filter((f) => f.rn[2] > 0.001) .map((f) => ({ ...f, k: Math.min(1.35, 0.45 + 0.6 * Math.max(0, f.rn[0] * L[0] + f.rn[1] * L[1] + f.rn[2] * L[2])), })); const order = geo.boxes .map((b: any, i: number) => { const x = (b.x0 + b.x1) / 2; const y = (b.y0 + b.y1) / 2; const z = b.z0 != null ? (b.z0 + b.z1) / 2 : 0; // Dataset boxes sort by floor position (towers stay intact); plates always go first. if (b.plate) return [-1e9 + (-x * sa + z * ca) * cb, i]; if (b.file) return [(-x * sa + z * ca) * cb + y * sb * 1e-3, i]; return [y * sb + (-x * sa + z * ca) * cb, i]; }) .sort((a: number[], b: number[]) => a[0] - b[0]); const shade = (c: any, k: number) => `rgb(${Math.min(255, c.r * k) | 0},${Math.min(255, c.g * k) | 0},${Math.min(255, c.b * k) | 0})`; const path = (pts: number[][]) => { ctx.beginPath(); ctx.moveTo(pts[0][0], pts[0][1]); for (let k = 1; k < pts.length; k++) ctx.lineTo(pts[k][0], pts[k][1]); ctx.closePath(); }; const hits: any[] = []; const z0 = -D / 2, z1 = D / 2; for (const [, i] of order) { const b = geo.boxes[i]; const base = colors[i]; const sc = strips ? strips[i] : null; const V: number[][] = []; const bz0 = b.z0 ?? z0, bz1 = b.z1 ?? z1; for (let j = 0; j < 8; j++) V.push(P(j & 1 ? b.x1 : b.x0, j & 2 ? b.y1 : b.y0, j & 4 ? bz1 : bz0)); const isHover = hover && hover.i === i; const isSel = (footerSel && !b.chunk && !b.file) || (selChunk && b.chunk && b.chunk.rg === selChunk.rg && b.chunk.col === selChunk.col); const sameCol = (hoverCol != null && b.chunk && b.chunk.col === hoverCol) || (hoverFile != null && b.file === hoverFile); for (const f of faces) { const pts = f.idx.map((j) => V[j]); path(pts); ctx.fillStyle = shade(base, f.k); ctx.fill(); if (sc && f.axis !== "x") { const bw = (b.x1 - b.x0) / BINS; for (let q = 0; q < BINS; q++) { const col = sc[q]; if (!col) continue; const xa = b.x0 + bw * q; const xb = Math.min(b.x1, xa + bw * 1.04); let Q: number[][]; if (f.axis === "z") { const zf = f.sign > 0 ? z1 : z0; Q = [P(xa, b.y0, zf), P(xb, b.y0, zf), P(xb, b.y1, zf), P(xa, b.y1, zf)]; } else { const yf = f.sign > 0 ? b.y1 : b.y0; Q = [P(xa, yf, z0), P(xb, yf, z0), P(xb, yf, z1), P(xa, yf, z1)]; } path(Q); ctx.fillStyle = shade(col, f.k); ctx.fill(); } } path(pts); ctx.strokeStyle = isSel ? C.orange : isHover ? "#ffffff" : sameCol ? "rgba(255,255,255,0.6)" : "rgba(4,5,20,0.6)"; ctx.lineWidth = isSel ? 2 : isHover ? 1.6 : sameCol ? 1 : 0.5; ctx.stroke(); hits.push({ pts, i }); } } if (geo.labels) { ctx.textBaseline = "middle"; for (const l of geo.labels) { const p = P(l.x, l.y, l.z); ctx.font = l.bold ? "600 12px system-ui, -apple-system, sans-serif" : "11px system-ui, -apple-system, sans-serif"; ctx.fillStyle = l.color ?? C.muted; ctx.textAlign = l.align; ctx.fillText(l.text, p[0], p[1]); } } hitRef.current = hits; }, [geo, colors, strips, rot, width, HEIGHT, hover, hoverCol, hoverFile, selChunk, footerSel, zoom, pan]); // Zoom toward the cursor with the wheel or trackpad pinch. const zoomAt = (mx: number, my: number, factor: number) => { const z = zoomRef.current; const nz = Math.max(0.5, Math.min(8, z * factor)); const cx = width / 2, cy = HEIGHT / 2; const p = panRef.current; setPan({ x: mx - cx - ((mx - cx - p.x) * nz) / z, y: my - cy - ((my - cy - p.y) * nz) / z, }); setZoom(nz); }; const resetView = () => { setZoom(1); setPan({ x: 0, y: 0 }); setRot({ yaw: -0.65, pitch: 0.42 }); }; useEffect(() => { const cv = canvasRef.current; if (!cv) return; const onWheel = (e: WheelEvent) => { e.preventDefault(); const r = cv.getBoundingClientRect(); zoomAt(e.clientX - r.left, e.clientY - r.top, Math.exp(-e.deltaY * 0.0015)); }; cv.addEventListener("wheel", onWheel, { passive: false }); return () => cv.removeEventListener("wheel", onWheel); }, [width, HEIGHT]); const hitAt = (mx: number, my: number) => { const hits = hitRef.current; for (let k = hits.length - 1; k >= 0; k--) if (inPoly(mx, my, hits[k].pts)) return hits[k].i as number; return null; }; const onDown = (e: any) => { dragRef.current = { x: e.clientX, y: e.clientY, yaw: rot.yaw, pitch: rot.pitch, px: pan.x, py: pan.y, panMode: e.shiftKey || e.button === 1 || e.button === 2, moved: false, }; e.currentTarget.setPointerCapture(e.pointerId); }; const onMove = (e: any) => { const r = e.currentTarget.getBoundingClientRect(); const mx = e.clientX - r.left, my = e.clientY - r.top; const d = dragRef.current; if (d) { const dx = e.clientX - d.x, dy = e.clientY - d.y; if (!d.moved && Math.hypot(dx, dy) < 4) return; d.moved = true; if (d.panMode) { setPan({ x: d.px + dx, y: d.py + dy }); } else { setRot({ yaw: d.yaw + dx * 0.008, pitch: Math.max(-1.45, Math.min(1.45, d.pitch + dy * 0.008)) }); } setHover(null); return; } const found = hitAt(mx, my); setHover(found == null ? null : { i: found, x: mx, y: my }); }; const onUp = (e: any) => { const d = dragRef.current; dragRef.current = null; if (!d || d.moved || !geo) return; const r = e.currentTarget.getBoundingClientRect(); const found = hitAt(e.clientX - r.left, e.clientY - r.top); if (found == null || geo.boxes[found].plate) return; if (geo.boxes[found].file) { setFileUrl(geo.boxes[found].file); return; } const ch = geo.boxes[found].chunk; setPage(0); setSel(ch ? { rg: ch.rg, col: ch.col } : { footer: true }); }; const loadDraft = () => { const u = draft.trim(); if (!u) return; if (/\.(paths|txt|csv)(\.gz)?$/i.test(u)) { setListUrl(u); } else { setListUrl(undefined); setDs(isGlob(u) ? u : undefined); setFileUrl(u); } setDraft(""); }; const hb = hover && geo ? geo.boxes[hover.i] : null; const hc = hb?.chunk; const hBins = hc ? hist.m.get(`${hc.rg}|${hc.top}`) : null; return (

Anatomy of a Parquet file

{preset ? preset.label : inDs ? trunc(fileUrl.replace(/^[a-z0-9]+:\/\//i, ""), 70) : trunc(baseName(fileUrl), 60)} {inDs ? `, ${dsFiles.length} files read live from their footers` : ", read live from the file's footer"}

{(inDs ? [ { label: "Files", value: dsFiles.length ? fmtN(dsFiles.length) : null }, { label: "Rows", value: dsStats ? fmtN(dsStats.rows) : null }, { label: "Row groups", value: dsStats ? fmtN(dsStats.rgCount) : null }, { label: "Total size", value: dsStats ? fmtB(dsStats.size) : null }, ] : [ { label: "File size", value: meta ? fmtB(N(meta.file_size)) : null }, { label: "Rows", value: meta ? fmtN(N(meta.num_rows)) : null }, { label: "Row groups × columns", value: cols.length ? `${totalRg} × ${cols.length}` : null }, { label: "Compression", value: totComp ? `${(totUnc / totComp).toFixed(2)}×` : null }, ] ).map((k) => (

{k.value == null ? ( (inDs ? dsQ.isError : chunkQ.isError) ? (

–

) : (

) ) : (

{k.value}

)}

{k.label}

))}

{ if (v === "custom") return; setListUrl(undefined); setDs(isGlob(v) ? v : undefined); setFileUrl(v); }} options={[ ...PRESETS.map((p) => ({ value: p.url, label: p.label })), ...(preset ? [] : [{ value: "custom", label: trunc(baseName(fileUrl), 28) }]), ]} /> setDraft(e.target.value)} onKeyDown={(e) => e.key === "Enter" && loadDraft()} placeholder="Paste a Parquet URL, or a .paths.gz list of them" aria-label="Parquet file URL" className="px-2 py-1 flex-1" style={{ minWidth: 240, background: "transparent", border: `1px solid ${C.line}`, borderRadius: 4, color: C.text, }} /> Load file

{listUrl && (

{baseName(listUrl)} {listPaths.length ? `, ${fmtN(listPaths.length)} Parquet files` : ""} {listQ.isLoading ? ( Reading list… ) : listQ.isError ? ( Couldn't read this list: {trunc(String(listQ.error), 120)} ) : listPaths.length === 0 ? ( No .parquet paths found in this list. ) : ( )} setListUrl(undefined)}>Close list

)}

{ds && !inDs && setFileUrl(ds)}>All files in dataset} {!inDs && ( <> Color Width )} Explode { cancelAnimationFrame(animRef.current); setExplode(Number(e.target.value)); }} style={{ accentColor: C.blue, width: 120 }} /> animateTo(explode > 0.5 ? 0 : 1)}>{explode > 0.5 ? "Assemble" : "Explode"} Zoom

zoomAt(width / 2, HEIGHT / 2, 1 / 1.4)} disabled={zoom <= 0.5}> − zoomAt(width / 2, HEIGHT / 2, 1.4)} disabled={zoom >= 8}> + Reset view

{zoom.toFixed(1)}×

{inDs ? ( <>

Rows per row group 1k 1M+

{geo?.groups && (

{geo.keys?.[0] ?? "Folder"} (rows, footer color)

{geo.groups.slice(0, 16).map((g: any) => ( ))} {geo.groups.length > 16 && +{geo.groups.length - 16} more}

)} {geo?.keys && geo.keys.length > 1 && (

Columns: {geo.keys.slice(1).join(" / ")}, sorted by path. {geo.keys.length > 2 ? ` Wider gaps separate ${geo.keys[1]} folders; deeper levels show in the tooltip.` : ""}

)} {dsStats && (

{dsStats.keys.length ? `Partitioned by ${dsStats.keys.join(", ")}. ` : ""} {`${fmtN(dsFiles.length)} files, median ${fmtN(Math.round(dsStats.medRows))} rows each, averaging ${fmtN( Math.round(dsStats.avgRg) )} rows per row group. `} {dsStats.avgRg < 60000 ? `That's well under DuckDB's default of 122,880 rows per row group. A full scan pays for ${fmtN( dsFiles.length )} footers and requests, so fewer, larger files would help unless queries filter on the partition keys.` : "Row groups are a healthy size."}

)}

Each tower is one file: row groups stacked on its footer, height ∝ rows. Hover a tower to see the file, click to open it.

) : ( <>

{colorBy === "values" && ( <> fewer more rows per bin )} {colorBy === "ratio" && ( 1× 12×+ compression )} {colorBy === "encoding" && ( <> )} {colorBy === "type" && ( <> )}

{colorBy === "values" ? "Each face is a 12-bin histogram of that chunk's values, from column min on the left to max on the right. Skewed columns use log bins; strings show value length. " : ""} {clustering ? clustering.cov < 0.5 ? `${clustering.name} is the best-clustered column: each row group spans ${(clustering.cov * 100).toFixed(0)}% of its range, so filters on it can skip row groups. ` : `No column is clustered. Even the best, ${clustering.name}, spans ${(clustering.cov * 100).toFixed(0)}% of its range per row group, so min/max stats can't skip much. ` : ""} {totalRg > MAX_RG ? `Showing the first ${MAX_RG} of ${fmtN(totalRg)} row groups. ` : ""} Height ∝ rows, width ∝ compressed bytes.

Drag to rotate, shift-drag to pan, scroll to zoom, double-click to reset. Click a block for its rows, or the footer for file metadata.

)}

{selChunk && (

{selChunk.name}

Row group {selChunk.rg} of {totalRg}

setSel(undefined)}>Close

{[ { label: "Rows", value: fmtN(selChunk.n) }, { label: "Compressed", value: fmtB(selChunk.comp) }, { label: "Ratio", value: `${(selChunk.unc / Math.max(1, selChunk.comp)).toFixed(2)}×` }, ].map((s) => (

{s.value}

{s.label}

))}

{selChunk.type}, {selChunk.codec}, {selChunk.enc} {selChunk.smin != null && ( <>
min {trunc(selChunk.smin, 40)}
max {trunc(selChunk.smax ?? "", 40)} )}

{hist.m.get(`${selChunk.rg}|${selChunk.top}`) && (

)}

Rows {fmtN(rowA)}–{fmtN(Math.max(rowA, rowB - 1))}

setPage(Math.max(0, page - 1))} disabled={page === 0}> Previous setPage(Math.min(maxPage, page + 1))} disabled={page >= maxPage}> Next

{rowsQ.isLoading ? ( Array.from({ length: PAGE }).map((_, i) => (

)) ) : rowsQ.isError ? (

{String(rowsQ.error)}

) : ( rows.map((r: any) => (

Row {fmtN(N(r.rn))}

{r.val == null ? "∅" : String(r.val)}

{ctxCols.map((c: any, i: number) => { const v = r[`k${i}`]; const s = v == null ? "∅" : String(v); return (

{c.name} {s}

); })}

)) )}

)} {footerSel && (

Footer

File metadata. Readers fetch this first, then only the chunks they need.

setSel(undefined)}>Close

{[ { label: "Footer size", value: meta ? fmtB(N(meta.footer_size)) : "–" }, { label: "Share of file", value: meta && N(meta.file_size) ? `${d3.format(".2~g")((100 * N(meta.footer_size)) / N(meta.file_size))}%` : "–", }, { label: "Format version", value: meta && meta.format_version != null ? String(meta.format_version) : "–" }, ].map((s) => (

{s.value}

{s.label}

))}

{meta?.created_by ? <>Written by {String(meta.created_by)}
: null} {fmtN(statsCount)} of {fmtN(allChunks.length)} column chunks carry min/max statistics.

Schema {footSchema.length} fields

{footSchemaQ.isLoading ? (

) : footSchemaQ.isError ? (

{String(footSchemaQ.error)}

) : ( footSchema.map((s: any, i: number) => { const desc = [ s.type ?? (N(s.kids) > 0 ? "group" : null), s.logical ? trunc(String(s.logical), 36) : s.conv, s.rep === "OPTIONAL" ? "nullable" : s.rep === "REPEATED" ? "repeated" : null, ] .filter(Boolean) .join(", "); return (

{String(s.name)} {desc}

); }) )}

Row groups {fmtN(rgSummary.length)}

Group Rows Bytes

{rgSummary.slice(0, 60).map((r: any) => (

{r.rg} {fmtN(r.n)} {fmtB(r.comp)}

))} {rgSummary.length > 60 && (

and {fmtN(rgSummary.length - 60)} more

)}

Key-value metadata {kv.length}

{kvQ.isLoading ? (

) : kvQ.isError ? (

{String(kvQ.error)}

) : kv.length === 0 ? (

This file has no key-value metadata.

) : ( kv.map((r: any, i: number) => (

{String(r.k)} {fmtB(N(r.len))}

{trunc(String(r.v ?? ""), 300)}

)) )}

)}

); }

Anatomy of a Parquet file

Comments