Render charts and dashboards straight from SQL — the ggplot-rs grammar of graphics, emitted as SVG.
Maintainer(s):
sipemu
Compatibility Matrix
This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.
| Platform | 1.4.4 | 1.4.5 | 1.5.5 | 1.5.6 | 2.0.0 |
|---|---|---|---|---|---|
| Linux AMD64 | |||||
| Linux ARM64 | |||||
| macOS AMD64 | |||||
| macOS ARM64 | |||||
| Windows AMD64 | |||||
| Windows ARM64 | |||||
| WebAssembly |
Installing and Loading
INSTALL anofox_visualization FROM community;
LOAD anofox_visualization;
Example
-- One scalar plus convenience macros. Pass columns; get an SVG per group.
WITH sales AS (SELECT * FROM (VALUES ('app', 30), ('web', 22), ('api', 12)) t(ch, n))
SELECT anofox_bar(ch, n) FROM sales; -- <svg> bar chart
-- Any chart kind via anofox_xy(..., kind := ...), or the raw JSON spec:
SELECT anofox_render('{"rows":[{"c0":"a","c1":3}],"roles":[[0,"XAXIS"],[1,"BARCHART"]]}');
About anofox_visualization
anofox_visualization renders charts as SVG from inside DuckDB, using the ggplot-rs grammar-of-graphics engine. This community build provides the rendering surface:
- anofox_render(spec VARCHAR) -> VARCHAR — a JSON panel spec -> SVG
- anofox_bar / anofox_line / anofox_scatter / anofox_area (x, y)
- anofox_xy(x, y, kind := 'BARCHART', width := 640, height := 400)
- anofox_xyc(x, y, series, kind := 'BARCHART_STACKED')
The convenience macros aggregate rows via list(), so
SELECT anofox_bar(x, y) FROM t returns one SVG for the group. Chart kinds
include bars/lines/areas/points, distributions (histogram, boxplot, violin),
heatmaps, and WKT-geometry choropleth maps.
The full toolkit — an interactive in-browser dashboard builder, locked read-only dashboard serving, and a CLI — is available from a source build of the project at https://github.com/DataZooDE/anofox-visualization.
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| anofox_area | macro | Render an area chart of two columns as SVG. Shorthand for anofox_xy(x, y, kind := 'AREACHART'). | NULL | [SELECT anofox_area(x, y) FROM (VALUES ('Jan', 10), ('Feb', 20)) t(x, y)] |
| anofox_bar | macro | Render a bar chart of two columns as SVG. Shorthand for anofox_xy(x, y, kind := 'BARCHART'). | NULL | [SELECT anofox_bar(x, y) FROM (VALUES ('Jan', 10), ('Feb', 20)) t(x, y)] |
| anofox_contract_version | scalar | Returns the version of the anofox integration contract (output schemas and duckdb_functions() tags) whose tables the anofox_plot_* macros consume. | NULL | [anofox_contract_version()] |
| anofox_line | macro | Render a line chart of two columns as SVG. Shorthand for anofox_xy(x, y, kind := 'LINECHART'). | NULL | [SELECT anofox_line(x, y) FROM (VALUES ('Jan', 10), ('Feb', 20)) t(x, y)] |
| anofox_plot | macro | Plot any anofox contract table (contract version 1), choosing the chart from its columns: term+estimate -> terms, curve_type -> curve, yhat -> prediction, fitted+residual -> diagnostics 2x2, metric+value -> summary, check_name|thresholds + status + value -> checks, partial_residual -> partial (component + residual), fold_id + (split | test_start | train_end) -> cv (provisional), resource+start+end -> schedule (Gantt), component+value | trend -> decomposition, p_value/test_id -> tests, row+col | column_a+column_b -> matrix, kind|severity + ds|period -> events, (bin|wave)+item+size -> assignment, option+probability -> distribution. Use the specific anofox_plot_* macro for options. | NULL | [SELECT anofox_plot(c) FROM (VALUES ('x1', 0.5, 0.2, 0.8)) c(term, estimate, conf_low, conf_high)] |
| anofox_plot_assignment | macro | An optimizer's item -> bin/wave assignment from an assignment table (anofox-optimize's pack_* / wave_* as a long-form bridge): one row per item (model_id, bin | wave, item, size, capacity). Items are stacked per bin/wave (CATEGORY: item, BARCHART_STACKED: size), the capacity drawn as a quiet reference line so the fill level is visible; several model_id's are faceted to compare algorithms. x := overrides bin/wave when both are present. |
NULL | [SELECT anofox_plot_assignment(a) FROM (VALUES (1, 'x1', 3, 10), (1, 'x2', 4, 10), (2, 'x3', 6, 10)) a(bin, item, size, capacity)] |
| anofox_plot_checks | macro | Data-quality checks from a check table (anofox-tabular run_checks / agg_check as they come; the other checks with their measure as value): one panel per check (own scale), the value as a dot coloured by status (fail red, warn amber, pass green) against the lower_threshold..upper_threshold acceptance band, one lane per identifier, the message on hover. Over several run_ts / ds values (or kind := 'strip') a status strip: one lane per check, a dot per run. x := names the time column. |
NULL | [SELECT anofox_plot_checks(c) FROM (VALUES ('rows', 'pass', 120.0, 100.0, NULL), ('customer present', 'fail', 0.083, NULL, 0.05)) c(check_name, status, value, lower_threshold, upper_threshold)] |
| anofox_plot_compare | macro | Compare a numeric column across groups with significance brackets (ggpubr's stat_pvalue_manual): pass the raw rows, value := ' |
NULL | [SELECT anofox_plot_compare(r, value := 'y', factor := 'g', tests := [{'group1': 'a', 'group2': 'b', 'p_adj': 0.01}]) FROM (VALUES ('a', 1.0), ('a', 1.4), ('b', 2.1), ('b', 2.6)) r(g, y)] |
| anofox_plot_curve | macro | Plot a curve table; the geom follows curve_type: roc/calibration (line + diagonal), pr/pdp/lift/lowess (line), km (step), acf/pacf (lollipops + bounds from y_low/y_high), lambda_cv (point ± y_low..y_high on a log x), qq (points + identity). One curve_type per call (GROUP BY curve_type); several model_id/series are coloured. |
NULL | [SELECT anofox_plot_curve(r) FROM (VALUES ('roc', 0.0, 0.0), ('roc', 0.2, 0.7), ('roc', 1.0, 1.0)) r(curve_type, x, y)] |
| anofox_plot_cv | macro | Cross-validation layout of a fold table (provisional schema): one lane per fold_id, the train and test windows as bars along ds (or an integer period), so the number of folds, where each training window ends and whether the test windows overlap or leave gaps (said under the plot) show at a glance. Reads ts_cv_folds_by / ts_cv_split_by (id, ds, fold_id, split) and ts_cv_split_folds_by (fold_id, train_start, train_end, test_start, test_end) as they come. Series with identical folds are drawn once; different folds get a panel per id (up to 6). | NULL | [SELECT anofox_plot_cv(f) FROM (SELECT 1 AS fold_id, 'train' AS split, i AS period FROM range(10) r(i) UNION ALL SELECT 1, 'test', i FROM range(10, 13) r(i)) f] |
| anofox_plot_decomposition | macro | Stacked panels of a time-series decomposition sharing one x axis, each with its own y scale: observed, level, trend, each seasonal (seasonal_ |
NULL | [SELECT anofox_plot_decomposition(d) FROM (SELECT i AS period, 'trend' AS component, 0.5 * i AS value FROM range(20) r(i) UNION ALL SELECT i, 'seasonal', sin(i) FROM range(20) r(i)) d] |
| anofox_plot_diagnostics | macro | The plot.lm 2x2 from an obs (augment) table as one SVG: residuals vs fitted, normal QQ, scale-location and residuals vs leverage (or Cook's distance). |
NULL | [SELECT anofox_plot_diagnostics(o) FROM (VALUES (1, 1.0, 0.1), (2, 2.0, -0.3), (3, 3.0, 0.2), (4, 4.0, 0.0)) o(row_id, fitted, residual)] |
| anofox_plot_diagram | macro | A pipeline, lineage or process diagram from a graph table (one row per node: id, title, mainstat, status, group, lane, story, x, y, sparkline, seq) and its edges := a LIST of edge STRUCTs (source, target, value, label, style, status) — a subquery (SELECT list(e) FROM edges e). Grafana node-graph names; producer spellings are read too (stage / label / from_stage / to_stage / count, …), or name the columns: id :=, mainstat :=, source :=, target :=, value :=. format := 'compact' (default) | 'money' | 'percent' | 'metric' for mainstat; direction := 'LR' | 'TB'; style := the edges' default ('flow', 'flow-volume', …). Edges are as wide as their value and coloured by status; loops run back. |
NULL | [SELECT anofox_plot_diagram(n, edges := [{'source': 'a', 'target': 'b', 'value': 3}]) FROM (VALUES ('a', 'Load', 10), ('b', 'Clean', 8)) n(id, title, mainstat)] |
| anofox_plot_distribution | macro | A categorical distribution from a distribution table (anofox-decide's decide_choice_distribution MAP / decide_table's distribution column, reached with SELECT t.question_id AS id, u.e.key AS option, u.e.value AS probability FROM t, unnest(map_entries(t.distribution)) AS u(e); question_id and model are also read as id and model_id): one row per option (id, option, probability, model_id optional). One id draws a plain bar per option; several ids draw a 100% stacked bar per id (CATEGORY: option). Several model_id's are faceted to compare models. |
NULL | [SELECT anofox_plot_distribution(d) FROM (VALUES ('q1', 'yes', 0.7), ('q1', 'no', 0.3)) d(id, option, probability)] |
| anofox_plot_events | macro | Timeline of an events table (alarms, parameter changes, calibration proposals): a dot per event at its ds (or integer period) in one lane per series (else per kind), coloured by severity (alarm red, warning amber), with kind, label, value and threshold on hover. To draw the events over a forecast, pass them to anofox_plot_prediction(p, events := (SELECT list(e) FROM alarms e)). |
NULL | [SELECT anofox_plot_events(e) FROM (VALUES (3, 'stockout', 'alarm', 'on hand 0'), (7, 'drift', 'warning', 'CUSUM 4.2')) e(period, kind, severity, label)] |
| anofox_plot_matrix | macro | Heatmap of a matrix table (row, col, value; anofox-tabular profile_correlations as it comes: column_a x column_b, pearson): one tile per cell, a diverging scale around 0, -1..1 for correlations (domain := [lo, hi] overrides), a pair list mirrored to the full matrix with a diagonal of 1 (mirror := false keeps it as given). Cell values are written when there are at most 144 cells (labels := overrides). value := picks another measure (value := 'spearman'). Confusion and setup matrices (row, col already the full grid) are drawn the same way, without mirroring. |
NULL | [SELECT anofox_plot_matrix(m) FROM (VALUES ('a', 'b', 0.4), ('a', 'c', -0.2), ('b', 'c', 0.7)) m(row, col, value)] |
| anofox_plot_obs | macro | One regression diagnostic panel from an obs (augment) table. kind: resid_fitted (residual vs fitted + loess), qq (normal QQ of std_residual), scale_location (sqrt|std_residual| vs fitted), leverage (std_residual vs leverage, 2p/n line when n_params is set) or cooks (Cook's distance by row_id with the 4/n line). The label_top rows with the largest cooks_d are labelled by row_id. |
NULL | [SELECT anofox_plot_obs(o, kind := 'qq') FROM (VALUES (1, 1.0, 0.1), (2, 2.0, -0.3), (3, 3.0, 0.2)) o(row_id, fitted, residual)] |
| anofox_plot_partial | macro | Partial-residual plots of a |
NULL | [SELECT anofox_plot_partial(p) FROM (VALUES ('x1', 1.0, 0.4, 0.5), ('x1', 2.0, 1.1, 1.0), ('x1', 3.0, 1.4, 1.5)) p(term, x, partial_residual, component)] |
| anofox_plot_prediction | macro | Fit / forecast plot of a prediction table: observed y as points, the yhat line and the yhat_lower..yhat_upper band over x (or ds, or an integer period), a nested fan when there are several levels, split boundaries as labelled vertical lines (train/test/future; derived from is_training or a missing y), or by model_id/model_name when there are several models. x := / y := name other columns (e.g. x := 'x1' for a *_fit_predict_by output); several id series become panels (facet := overrides). events := (SELECT list(e) FROM alarms e) draws an events table (ds | period, kind, label, severity) as vertical lines coloured by severity. statsforecast's wide |
NULL | [SELECT anofox_plot_prediction(p) FROM (VALUES (1, 1.0, 1.1, 0.9, 1.3), (2, 2.0, 1.9, 1.6, 2.2), (3, NULL, 3.1, 2.6, 3.6)) p(x, y, yhat, yhat_lower, yhat_upper)] |
| anofox_plot_schedule | macro | Gantt chart of a schedule table: one lane per resource, a bar per task from start to end, coloured by status (on time/late, else muted), a late task's connector to its due date, faceted by model_id (algorithm) when there are several (up to 6; else GROUP BY model_id or WHERE model_id = …). Columns: resource, task, start, end, model_id, due, status — one row per task. Replaces drawing a schedule as a scatter with xmin/xmax error bars. |
NULL | [SELECT anofox_plot_schedule(s) FROM (VALUES ('L1', 'O1', 0, 10, 'on time'), ('L1', 'O2', 10, 25, 'late'), ('L2', 'O3', 0, 15, 'on time')) s(resource, task, start, "end", status)] |
| anofox_plot_summary | macro | Model comparison from a summary (glance) table: a grid of small panels, one per metric with its own scale, the models (model_id, or model_id · id for several groups) on the vertical axis, the value as a dot (± conf_low..conf_high) with its number beside it and the best model highlighted for R², AIC, BIC, RMSE, …. Bookkeeping metrics (n_observations, n_features, iterations) are left out unless metrics := ['r_squared', 'n_observations', …] picks the panels. |
NULL | [SELECT anofox_plot_summary(s) FROM (VALUES ('ols', 'r_squared', 0.81), ('ridge', 'r_squared', 0.79), ('ols', 'aic', 72.4), ('ridge', 'aic', 75.0)) s(model_id, metric, value)] |
| anofox_plot_terms | macro | Coefficient plot of a terms (tidy) table, aggregated over its rows: pass the row, SELECT anofox_plot_terms(c) FROM coefs c. Columns: term, estimate, conf_low, conf_high (optional), model_id (several models are dodged and coloured). With index_value (+ index_name) it draws each term's estimate and band over the index instead: a λ path in one panel with direct labels (highlight := […], lambda_min := / lambda_1se := mark λ.min / λ.1se, facet := true for a panel per term), a LARS path on the |β|₁ fraction, the quantile process and rolling coefficients (window_end dates) per term with a full-sample reference (reference := a terms LIST). title := / x_label := / y_label := label the chart (no inner title by default). |
NULL | [SELECT anofox_plot_terms(c) FROM (VALUES ('x1', 0.5, 0.2, 0.8), ('x2', -0.3, -0.7, 0.1)) c(term, estimate, conf_low, conf_high)] |
| anofox_plot_tests | macro | Comparison forest of a test table (tukey_hsd_by, pairwise_t_test_by, dunn_test_by, or an unnested *_test_agg STRUCT): one row per test (test_id, else 'group1 vs group2', else the group / method), the estimate with conf_low..conf_high when the test has one, else the statistic, else -log10(p), coloured by significance at alpha, the (adjusted) p-value and stars beside each row. Several groups (id, model_id or the GROUP BY column of an unnested aggregate) get one panel each. Several methods (ADF and KPSS) get one panel per method (own x, groups on a shared y) unless the axis is -log10(p). value := 'estimate' | 'statistic' | 'p' overrides the measure. A column holding the aggregate's STRUCT or LIST is expanded: SELECT anofox_plot_tests(s) FROM (SELECT tukey_hsd_agg(y, g) AS r FROM t) s. |
NULL | [SELECT anofox_plot_tests(t) FROM (VALUES ('b-a', 'a', 'b', 1.2, 0.4, 2.0, 0.003), ('c-a', 'a', 'c', 0.3, -0.5, 1.1, 0.41)) t(test_id, group1, group2, estimate, conf_low, conf_high, p_adj)] |
| anofox_render | scalar | Render a chart specification to an SVG string. The spec is JSON carrying 'rows' (the data), 'roles' (which column plays which part: XAXIS, BARCHART, CATEGORY, …) and optional 'width' and 'height' – or 'plot' (terms, prediction, curve, summary, obs, diagnostics, tests, compare, partial, auto) with rows named by the anofox contract. The anofox_bar/xy/… and anofox_plot* macros build this spec for you. | NULL | [anofox_render(json_object('rows', to_json([{c0: 'Jan', c1: 10}]), 'roles', '[[0,"XAXIS"],[1,"BARCHART"]]'::JSON))] |
| anofox_scatter | macro | Render a scatter plot of two columns as SVG. Shorthand for anofox_xy(x, y, kind := 'SCATTER'). | NULL | [SELECT anofox_scatter(x, y) FROM (VALUES (1.5, 10), (2.5, 20)) t(x, y)] |
| anofox_xy | macro | Aggregate two columns into a single-series chart and render it as SVG. 'kind' selects the mark (BARCHART, LINECHART, SCATTER, AREACHART); x becomes the axis and y the value. | NULL | [SELECT anofox_xy(x, y, kind := 'LINECHART') FROM (VALUES ('Jan', 10), ('Feb', 20)) t(x, y)] |
| anofox_xyc | macro | Aggregate three columns into a multi-series chart and render it as SVG, with 'series' splitting the data into categories. | NULL | [SELECT anofox_xyc(x, y, s) FROM (VALUES ('Jan', 10, 'EU'), ('Jan', 8, 'US')) t(x, y, s)] |
Overloaded Functions
This extension does not add any function overloads.
Added Types
This extension does not add any types.
Added Settings
| name | description | input_type | scope | aliases | typed_value |
|---|---|---|---|---|---|
| datazoo_banner | Show the DataZoo feedback banner when an extension is loaded in an interactive terminal (at most once a day per extension). | BOOLEAN | GLOBAL | [] | true |
| temp_directory | Set the directory to which temporary files are written. | VARCHAR | GLOBAL | [] | post.db.tmp |