A custom engine receives each query Studio needs answered, translates it into the backend's own query language, and returns the results in an agreed shape.
Implement the optional executeCube() method as well if your dashboard uses Pivot Grid, Treemap, Sunburst, or legend-grouped charts. See Pivot and Hierarchy Queries.
Query Anatomy Copy Link
Studio calls execute() with one or more AgExecuteRequest objects, each carrying an AgStudioQuery to translate. Every field reference in an AgStudioQuery is an AgStudioQueryField object carrying key, fieldId, sourceId, and optional properties like aggregation.
Core Fields Copy Link
| Field | Purpose | Shape |
|---|---|---|
axes | Group-by dimensions for aggregation | [{ dimensions: [{ field: { key, fieldId, sourceId } }] }] |
measures | Aggregated columns | [{ field: { key, fieldId, sourceId, aggregation: 'sum' } }] |
projection | Raw columns when not aggregating (mutually exclusive with axes/measures) | [{ field: { key, fieldId, sourceId } }] |
filter | WHERE clause tree (recursive groups with combinator, leaves with operator) | { combinator: 'and', conditions: [{ field, operator: 'equals', value }] } |
sort | ORDER BY specification | [{ field: { key, fieldId, sourceId }, direction: 'desc' }] |
limit | LIMIT and OFFSET | { count: 100, offset: 20 } |
computedFields | Computed columns with expression ASTs and evaluation phase | See Computed Fields |
Honouring limit (count and offset) is mandatory. Server-side row models fetch data page by page and expect each page to return the rows starting at its own offset - an engine that ignores it and always returns the same page makes every page look identical, which surfaces as a row-identity warning (see Row Identity) rather than as a pagination bug.
Additional Fields Copy Link
These fields appear when the dashboard uses features that require them. If your engine does not support a given field, the corresponding UI feature will not work correctly.
| Field | Purpose |
|---|---|
having | Post-aggregation filter (SQL HAVING) |
window | Window functions (rank, denseRank, rowNumber) |
joins | Multi-source joins (see Sources and Joins) |
scope | Dimension-member slice for slicer/page-filter semantics |
distinct | Row deduplication for projection-mode queries |
from | Derived-table composition (SQL FROM (SELECT ...)) |
Properties available on the AgStudioQuery interface.
Dimension axes for OLAP aggregation. Empty axes: [] combined with measures yields a single grand-total group. Mutually exclusive with projection.
|
Measures aggregated at each cell. Required when axes is present.
|
Output columns for detail/raw-row queries. Mutually exclusive with axes/measures.
|
Ordered join chain - the query's source topology. Omit for a single-source query. Engines must consume clauses in order and must not perform schema lookup to reconstruct the topology. The FROM clause's base source is joins[0].leftSourceId (see AgJoinClause); with no joins, the query's single source is the base source.
|
Row-level predicate applied BEFORE any aggregation (SQL WHERE semantics). Leaves reference source fields on the joined plan and values computed before aggregation. Affects both the result set and the denominator used by measures with totalsScope: 'filtered'. Conditions on measures and post-aggregation computed fields belong in having. On a single-source query, a filter condition that reads a value only available after aggregation - at any depth of an AND, OR or NOT group - is reported as a validation issue naming the field, and the query returns no rows. The exception is a predicate leaf that reads an aggregated value of this query (see AgStudioFilterPredicate): it is applied after aggregation, as in having.
|
Post-aggregation predicate applied to cells, AFTER measures are computed (SQL HAVING semantics). Leaves must reference measure aliases or dimension fields (via AgExprFieldRef); row-level source fields are rejected. Cells the predicate rejects are dropped; the measure denominators are NOT recomputed.
|
Dimension-member slice (MDX-inspired). Restricts the visible cells to the chosen members but - unlike filter - does NOT remove the excluded rows from the denominator used by measures with totalsScope: 'unrestricted'. Use for slicer/page-filter semantics where "% of total" should still divide by the full dataset. A window function over row-level fields is computed over every row, including the rows scope hides; one over measures sees only the cells scope keeps.
|
Result ordering (SQL ORDER BY). Applied AFTER aggregation, computed fields, and windows. Multiple entries form a tie-break chain in array order. Sort fields must reference output columns (measure aliases, dimension fields, or computed-field outputs).
|
Window function evaluations (SQL OVER (...)). Each entry produces an output column computed over a partition of rows defined by partitionBy and ordered by orderBy. Evaluated after aggregation; outputs may be referenced by post-aggregation computed fields and having.
|
Hard cap on returned rows, with optional offset (SQL LIMIT / OFFSET). Applied as the final step after sort; pagination is therefore stable only when sort produces a total ordering.
|
Projection-mode row deduplication (SQL SELECT DISTINCT). In aggregation mode, use distinct-within-measure aggregations (countd etc.) instead.
|
How text values that differ only in case form groups, for each dimension, distinct projection entry and countd measure of this query and its sub-queries that sets no textGrouping of its own, and for each countd aggregate a window reads that is not a measure. Defaults to 'exact'. See AgTextGrouping.
|
Evaluation schedule for computed expression fields. Topologically sorted - engines evaluate in array order. Each entry carries the expression in AgStudioExpression AST form and an explicit evaluation phase. Synthetic entries decompose cross-source expressions into single-source intermediates. Absent when the query has no expression fields.
|
Derived-table composition (SQL FROM (SELECT ...)). Engines execute the inner query, materialise its result, and run the outer query over those rows. Each materialised level costs memory and latency, so engines should consider imposing a depth limit and rejecting deeper nesting with an error rather than silently truncating.
|
How the query's own comparisons, in, and, or, not, notContains and switch operations and filter conditions treat a null value, in filter, having, the filter predicates and the expressions of computedFields; see AgNullLogic. A computed field whose field is a calculation defined in the data model uses that calculation, which keeps the engine's nullHandling.nullLogic. A condition or operation that sets its own nullLogic keeps it, and a sub-query that sets none uses the enclosing query's. Defaults to the engine's nullHandling.nullLogic, which defaults to 'twoValued'.
|
How the query's own add and subtract operations treat a null input, in filter, having, the filter predicates and the expressions of computedFields; see AgNullOperands. A computed field whose field is a calculation defined in the data model uses that calculation, which keeps the engine's nullHandling.nullOperands. An operation that sets its own nullOperands keeps it, and a sub-query that sets none uses the enclosing query's. A join condition follows 'sql'. Defaults to the engine's nullHandling.nullOperands, which defaults to 'sql'.
|
Computed Fields Copy Link
computedFields is a topologically sorted evaluation schedule. Each entry carries an expression AST and an explicit evaluation phase:
phase: 'pre-agg': evaluated on source rows before grouping (SQL column expression)phase: 'post-agg': evaluated on grouped output after measures are computedsynthetic: true: internally generated intermediates; exclude from user-facing result columns
Engines evaluate entries in array order. Each entry's dependencies are satisfied by prior entries or source scans.
Expression nodes are either operations ({ operator, inputs, options? }) or leaves: field references ({ field: AgStudioQueryField }), values ({ type, value }, e.g. { type: 'number', value: 2 }), or alias references ({ ref: string }).
Field Identifiers Copy Link
Every field reference in a query is an AgStudioQueryField:
| Property | Where it comes from | What to use it for |
|---|---|---|
fieldId | Source-qualified column ID from getDataSources(): "sales.revenue". For computed fields (sourceId: ''), a bare identifier like "pct_of_total". | Map to a backend column. Strip the source prefix for source fields; use as-is for computed fields. |
sourceId | The id of the source the field belongs to: "sales". | Pick which backend table or endpoint the query targets. |
key | An opaque string Studio builds per-field per-query. | The column name in your result rows. Your result rows[i][field.key] must round-trip cleanly. Treat key as opaque; do not parse it. |
Measure fields also carry aggregation (e.g. 'sum', 'avg', 'count'). Access it via measure.field.aggregation.
Properties available on the AgStudioQueryField interface.
Composite identity key and output column alias. Treat as opaque. When the wrapping definition (window / measure / dimension / projection entry) carries an as, that alias - not this key - becomes the output column identity. This key then only carries the field's *source* identity for resolution.
|
Owning source ID; '' for computed/inline fields.
|
The underlying field identifier within the source.
|
Aggregation function for measure fields; omitted for raw dimensions.
|
Additional identity component for fields that would otherwise collide on (sourceId, fieldId, aggregation) - e.g. window outputs with different orderings.
|
Alias for self-joins - disambiguates multiple instances of the same underlying source in one query.
|
Inline expression AST - present for computed / inline fields (where sourceId === '') and also the mechanism for granularity transforms such as dateTrunc and numeric bucketing.
|
Inline-field intent disambiguator. For inline fields, true marks a measure (lives under AgStudioQuery.measures), false marks a dimension (lives under axes[].dimensions). Validator rejects mismatched slot/intent pairs.
|
Optional data-type hint. Studio-produced fields set this from the schema; hand-constructed queries may omit it.
|
Filter Structure Copy Link
The filter and having fields share the same recursive tree structure:
AgStudioFilterGroup: a boolean combinator ('and', 'or', or 'not') wrapping child nodes:
{
combinator: 'and',
conditions: [/* AgStudioFilterCondition | AgStudioFilterGroup */],
}AgStudioFilterCondition: a leaf predicate on a single field:
{
field: { key, fieldId, sourceId },
operator: 'equals',
value: 'EMEA',
}Walk the tree recursively: groups become parenthesised boolean expressions; conditions become backend predicates. See the Reference Examples for complete filter translation.
Properties available on the AgStudioFilterCondition interface.
The field tested by the predicate.
|
The comparison applied between field and value. rank is excludedAgDimensionDefinition.topN. like and notLike match a text field against a pattern, keeping the rows that match or do not match it respectively. In the pattern, % matches any sequence of characters (including none) and _ matches exactly one character; every other character matches itself. The match ignores case unless options.matchCase is true. On a text field, lessThan, lessThanOrEqual, greaterThan and greaterThanOrEqual order text by letter, ignoring case but not accents as equals does, so a value that equals value is neither less nor greater than it. When options.matchCase is true they compare by character code instead.
|
undefined for isNull / isNotNull / isTrue / isFalse; a 2-element array for between (check operator to distinguish from isIn); an array for isIn; a relative-range descriptor for inRelativeRange; a pattern string for like / notLike; single primitive otherwise.
|
How this condition treats a null value; see AgNullLogic. Defaults to the query's nullLogic.
|
Routing metadata carried to the engine and explain output.
|
Properties available on the AgStudioFilterGroup interface.
How conditions are combined. not negates the conjunction of its children (NOT (c1 AND c2 AND ...)); to negate a single predicate, wrap it in a not group with one child.
|
Child filters - leaf conditions or nested groups.
|
Filter and Expression Operators Copy Link
A filter condition and an expression can test the same thing. Each row of the table below pairs a filter operator with the expression operator that gives the same result on non-null data. Use the expression form inside a computed field, or inside a { predicate } filter leaf.
| Filter operator | Expression operator | Notes |
|---|---|---|
equals | equals | |
notEqual | notEqual | |
greaterThan | greaterThan | |
greaterThanOrEqual | greaterThanOrEqual | |
lessThan | lessThan | |
lessThanOrEqual | lessThanOrEqual | |
between | between | Inclusive at both ends. Numbers, dates and date-times only, not text. |
isIn | in | Tests membership in a list of values. |
contains | contains | Text only. |
notContains | notContains | Text only. |
startsWith | startsWith | Text only. |
endsWith | endsWith | Text only. |
like | like | Text only. In the pattern, % matches any run of characters and _ matches one character. Accepts an escape character. |
notLike | notLike | The reverse of like, with the same pattern rules and escape character. |
isTrue | isTrue | |
isFalse | isFalse | |
isNull | isNull | |
isNotNull | isNotNull | |
inRelativeRange | None | Filter only for now. Use the filter condition for a relative date window. |
and, or, not groups | and, or, not | A filter group combines child filters with combinator. The expression operators take the child tests as inputs. |
isBlank | None | Filter only for now. |
isNotBlank | None | Filter only for now. |
isNaN | None | Filter only for now. |
isUndefined | None | Filter only for now. |
isNotUndefined | None | Filter only for now. |
Any other expression operator, such as arithmetic or a string function, can also be used in a filter. Put the expression in a predicate condition, { predicate: expression }. A row passes when the expression is true.
{
predicate: {
operator: 'contains',
inputs: [{ field: { key, fieldId, sourceId } }, { type: 'string', value: 'north' }],
},
} Differences Between the Two Forms Copy Link
The two forms give the same result on data where case does not matter. They differ in how they compare text and date-times.
- Text: filter comparisons ignore case, unless the condition sets
options.matchCase. They do not ignore accents.lessThan,lessThanOrEqual,greaterThanandgreaterThanOrEqualorder text by letter, as a sort does, so a value thatequalsthe filter value is neither less nor greater than it; withoptions.matchCasethey compare by character code. Expression comparisons are exact, so they are case sensitive. ThelikeandnotLikeexpressions can ignore case: givefalseas their fourth input,matchCase, after the escape character, and they match as the filter does by default. A nullmatchCasealso ignores case. To setmatchCasewith no escape character, give a null escape. - Date-times: filter comparisons work to the minute. Expression comparisons work to the millisecond.
Pattern Conditions in Data Sources Copy Link
A data source that declares capabilities.filter can receive like and notLike conditions in the filter it is sent. Both take the pattern as value. The options object can carry escapeChar, the character that makes the next character in the pattern literal, and matchCase, which makes the match case sensitive. Without matchCase, the match ignores case.
Result Format Copy Link
Return one AgExecuteResult per request, in the same order, discriminated on dataShape. Return 'rows' (default) or 'columns' depending on options.shape.
The dataShape discriminator on each result must match the data you return. If you set dataShape: 'columns' but populate rows (or vice versa), widgets will render empty.
Both shapes carry an AgResultMetadata object. rowCount is the number of rows in the returned result. When your engine applies a limit, set totalRowCount to the pre-limit row count.
Row Identity Copy Link
Grid widgets need a stable, unique identifier per row. Studio injects a computed field into relevant queries to support this (using all the group fields). If your engine does not evaluate computed fields, you must provide your own unique row id as part of execution results. If your result rows already carry one, you can set metadata.rowIdField to its name.
return {
dataShape: 'rows',
rows: [
{ id: 'ord-1', 'sales.region': 'EMEA', 'sales.revenue': 123456 },
{ id: 'ord-2', 'sales.region': 'APAC', 'sales.revenue': 234567 },
],
metadata: { rowCount: 2, rowIdField: 'id' },
}; Rows Format Copy Link
The default shape. Return each row as an object keyed by field identifier.
return {
dataShape: 'rows',
rows: [
{ 'sales.region': 'EMEA', 'sales.revenue': 123456 },
{ 'sales.region': 'APAC', 'sales.revenue': 234567 },
],
metadata: { rowCount: 2, totalRowCount: 5432 },
}; Columns Format Copy Link
Requested through options.shape. Return one array of values per field, all the same length.
const columns = new Map<string, ReadonlyArray<AgPrimitive | null>>();
columns.set('sales.region', ['EMEA', 'APAC']);
columns.set('sales.revenue', [123456, 234567]);
return {
dataShape: 'columns',
columns,
metadata: { rowCount: 2 },
};Properties available on the AgExecuteRequest<TShape extends AgResultShape = AgResultShape> interface.
The query to execute against the data source.
|
Per-request options controlling result shape, cancellation, and validation.
|
Advisory metadata for logging, tracing, and batch coalescing.
|
Properties available on the AgRowsResult interface.
Row count and optional pre-limit total.
|
Discriminator - always 'rows' for this shape.
|
Result rows keyed by output field alias.
|
Properties available on the AgColumnsResult interface.
Row count and optional pre-limit total.
|
Discriminator - always 'columns' for this shape.
|
Column arrays keyed by output field alias.
|
Sources and Joins Copy Link
When a widget pulls fields from multiple related sources (declared via Relationships), Studio produces a single AgStudioQuery with joins populated. Your execute() receives one query that spans multiple sources. Each field's sourceId and fieldId identify which backend table it belongs to, so your translator can resolve every reference.
If your backend does not support joins, throw a descriptive error when a query contains joins.
Batching and Cancellation Copy Link
Studio calls execute(...requests) with every request in the current render cycle. Each request carries an info object:
batchId: All requests in the sameexecute()call share abatchId. Use it to coalesce backend round-trips (e.g. one combined request per batch). When absent, treat each request independently.queryId: Distinguishes independent queries from the same widget (e.g.'rows'vs'grandTotal'). Studio pairs it withwidgetIdto decide which in-flight query a new one replaces, so two queries the same widget runs under differentqueryIds never cancel each other.
info is advisory. Its values are not stable across re-renders, so mint your own identifiers if you need to tie a request to a backend call in your logs.
Cancelling Superseded Queries Copy Link
Each request carries an optional options.signal: AbortSignal. Studio aborts it in two cases:
- A newer query replaces this one. The same widget asking again under the same
queryIdsupersedes the earlier request. This happens whenever a filter changes before the previous queries have returned. - The request exceeds
dataOptions.queryTimeout, 60 seconds by default. Set it to0orInfinityto disable the timeout.
Check the signal before starting work, then hand it to your backend call:
private async runOne(request: AgExecuteRequest<AgResultShape>): Promise<AgExecuteResult> {
const { query, options } = request;
if (options?.signal?.aborted) {
return { dataShape: 'rows', rows: [], metadata: { rowCount: 0 } };
}
const res = await fetch(this.toBackendUrl(query), { signal: options?.signal });
const rows = await res.json();
return { dataShape: 'rows', rows, metadata: { rowCount: rows.length } };
}A query can be superseded before the batch it belongs to reaches your engine, so execute() is sometimes handed a request whose signal is already aborted.
Honouring the signal is optional. Studio replaces the result of any request it aborted with an empty one, so a late response from an engine that ignored the signal never reaches a widget. What ignoring it costs is backend work nobody reads, which adds up quickly when a user steps through a date picker or drags a slider and every move issues a fresh query.
Errors Copy Link
Requests in one execute() call come from unrelated widgets, so settle each of them on its own. When one request fails, return a result for it with no rows and the reason in metadata.error. Studio treats that request as failed rather than as a query that matched no rows, and a grid or chart widget reports the message. The other requests in the call still return their rows.
Here settleOne wraps runOne from Cancelling Superseded Queries so that it never rejects:
async execute(...requests: AgExecuteRequest<AgResultShape>[]): Promise<AgExecuteResult[]> {
return Promise.all(requests.map((request) => this.settleOne(request)));
}
private async settleOne(request: AgExecuteRequest<AgResultShape>): Promise<AgExecuteResult> {
try {
return await this.runOne(request);
} catch (error) {
const message = error instanceof Error ? error.message : String(error);
return { dataShape: 'rows', rows: [], metadata: { rowCount: 0, error: { message } } };
}
}A failure that covers the whole call, such as an unreachable backend, then fails each request on its own, and each grid or chart widget reports it. To report it once instead, reject the returned promise: Studio resolves every request in the call with empty results and reports one failure. Reject with an Error, because Studio reads message off it.
Pivot and Hierarchy Queries Copy Link
Some widgets need cube-aggregated data that execute() can't express: multiple independent grouping axes with dense cell coverage, and subtotal placement within a hierarchy. For these, Studio calls an optional second method, executeCube(...requests: AgCubeResolvedExecuteRequest[]), and expects one AgCubeResult per request - axis tuples and a sparse cell store, not flat rows.
executeCube is required for:
- Pivot Grid widgets, always.
- Treemap and Sunburst widgets, always - hierarchy subtotal placement (
includeSubtotals) has no flat equivalent. - Any chart configured with a legend field - legend grouping is a pivot query under the hood.
A chart's own group-by fields, without a legend field, never need executeCube: they're a flat execute() query regardless of how many are configured.
Omitting executeCube Copy Link
executeCube is optional. If you omit it:
- Pivot Grid, Treemap, and Sunburst widget types are omitted from the widget picker entirely, with a validation warning explaining why.
- A chart configured with a legend field still renders, but without the legend grouping - one ungrouped series, plus a validation warning.
Properties available on the AgCubeResolvedExecuteRequest interface.
AgCubeResolvedStudioQuery |
AgCubeExecuteOptions |
AgEngineCallInfo |
Properties available on the AgCubeResult<TValue = AgPrimitive> interface.
"cube" |
Axes in query order; length ≥ 2.
|
Ordered measure descriptors; each key matches a cell record key.
|
Sparse N-D cell store keyed by axis-tuple index vector.
|
Row count and optional pre-limit total.
|
Reference Examples Copy Link
These are reference implementations for learning purposes. They are not production-ready integrations and will not cover every query feature. Use them as starting points for your own engine.
ClickHouse over HTTP Copy Link
Translates AgStudioQuery to ClickHouse SQL, posts it over HTTP, and returns the response. The dashboard queries ClickHouse's uk_price_paid dataset (~28M rows) without downloading any of it.
import {
AgReportState,
AgStudioApi,
AgStudioProperties,
createStudio,
enableStudioDevValidations,
} from "ag-studio";
import { ClickHouseDataEngine } from "./engine.ts";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableStudioDevValidations();
}
// Dashboard over the `uk_price_paid` dataset on ClickHouse's public
// playground. Fields are prefixed with the source id (`uk_price_paid.*`).
const initialState: AgReportState = {
panels: {
filters: {
collapsed: true,
},
},
pages: [
{
id: "properties-dashboard",
widgets: {
"county-filter": {
type: "list-filter",
dataMapping: { value: [{ id: "uk_price_paid.county" }] },
format: { title: { enabled: true, text: "Filter by County" } },
},
"type-filter": {
type: "list-filter",
dataMapping: { value: [{ id: "uk_price_paid.type" }] },
format: { title: { enabled: true, text: "Filter by Property Type" } },
},
"avg-by-county": {
type: "bar-chart-grouped",
dataMapping: {
categoryKey: [{ id: "uk_price_paid.county" }],
valueKey: [{ id: "uk_price_paid.price", aggregation: "avg" }],
},
format: {
title: {
enabled: true,
text: "Average Price by County",
typography: { fontSize: 16, fontWeight: "bold" },
},
},
},
"avg-by-type": {
type: "bar-chart-grouped",
dataMapping: {
categoryKey: [{ id: "uk_price_paid.type" }],
valueKey: [{ id: "uk_price_paid.price", aggregation: "avg" }],
},
format: {
title: {
enabled: true,
text: "Average Price by Property Type",
typography: { fontSize: 16, fontWeight: "bold" },
},
},
},
"summary-grid": {
type: "grid",
dataMapping: {
cols: [
{ id: "uk_price_paid.county" },
{ id: "uk_price_paid.type" },
{ id: "uk_price_paid.price", aggregation: "count" },
{ id: "uk_price_paid.price", aggregation: "avg" },
{ id: "uk_price_paid.price", aggregation: "min" },
{ id: "uk_price_paid.price", aggregation: "max" },
],
},
format: {
title: {
enabled: true,
text: "Price Summary",
typography: { fontSize: 16, fontWeight: "bold" },
},
style: {
grandTotalRow: { enabled: true },
theme: { rowHeight: 28 },
},
},
},
},
widgetLayout: {
"county-filter": { xTrack: 0, yTrack: 0, xSpan: 6, ySpan: 12 },
"type-filter": { xTrack: 0, yTrack: 12, xSpan: 6, ySpan: 12 },
"avg-by-county": { xTrack: 6, yTrack: 0, xSpan: 9, ySpan: 24 },
"avg-by-type": { xTrack: 15, yTrack: 0, xSpan: 9, ySpan: 24 },
"summary-grid": { xTrack: 0, yTrack: 24, xSpan: 24, ySpan: 14 },
},
filter: { page: [] },
},
],
selectedPageId: "properties-dashboard",
};
let studioApi: AgStudioApi;
function toggleMode() {
const currentMode = studioApi.getProperty("mode");
const newMode = currentMode === "edit" ? "view" : "edit";
studioApi.setProperty("mode", newMode);
document.getElementById("toggleMode")!.textContent =
`${newMode === "edit" ? "View" : "Edit"} Mode`;
}
function loadData() {
document.body.dataset.loaded = "true";
studioApi.setProperty("data", new ClickHouseDataEngine());
}
const studioProperties: AgStudioProperties = {
mode: "view",
initialState,
};
// setup Studio after the page has finished loading
const studioDiv = document.querySelector<HTMLElement>("#myStudio")!;
studioApi = createStudio(studioDiv, studioProperties);
if (typeof window !== "undefined") {
// Attach external event handlers to window so they can be called from index.html
(<any>window).toggleMode = toggleMode;
(<any>window).loadData = loadData;
}
Contains HM Land Registry data © Crown copyright and database right 2021. This data is licensed under the Open Government Licence v3.0. (Source, Fields)
ClickHouse Server-Side Pivot Copy Link
Adds executeCube (see Pivot and Hierarchy Queries) to the ClickHouse engine above, so it can also serve Pivot Grid, Treemap, and Sunburst widgets. The implementation groups by both axes' dimensions in a single query, then reshapes the flat result into the cube shape.
import {
AgReportState,
AgStudioApi,
AgStudioProperties,
createStudio,
enableStudioDevValidations,
} from "ag-studio";
import { ClickHousePivotDataEngine } from "./engine.ts";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableStudioDevValidations();
}
// Pivot over the `uk_price_paid` dataset on ClickHouse's public playground,
// served entirely server-side via `AgDataEngine.executeCube`. Fields are
// prefixed with the source id (`uk_price_paid.*`).
const initialState: AgReportState = {
panels: {
filters: {
collapsed: true,
},
},
pages: [
{
id: "properties-pivot",
widgets: {
"price-pivot": {
type: "pivot-grid",
dataMapping: {
rows: [{ id: "uk_price_paid.county" }],
columns: [{ id: "uk_price_paid.type" }],
values: [
{ id: "uk_price_paid.price", aggregation: "avg" },
{ id: "uk_price_paid.price", aggregation: "count" },
],
},
format: {
title: {
enabled: true,
text: "Average Price and Sales Count by County and Property Type",
typography: { fontSize: 16, fontWeight: "bold" },
},
style: { totalColumns: true },
},
},
"price-treemap": {
type: "treemap-chart",
dataMapping: {
categoryKey: [
{ id: "uk_price_paid.duration" },
{ id: "uk_price_paid.type" },
],
valueKey: [{ id: "uk_price_paid.price", aggregation: "sum" }],
},
format: {
title: {
enabled: true,
text: "Total Price by Property Type and Tenure",
typography: { fontSize: 16, fontWeight: "bold" },
},
},
},
"price-sunburst": {
type: "sunburst-chart",
dataMapping: {
categoryKey: [
{ id: "uk_price_paid.duration" },
{ id: "uk_price_paid.type" },
],
valueKey: [{ id: "uk_price_paid.price", aggregation: "sum" }],
},
format: {
title: {
enabled: true,
text: "Total Price by Property Type and Tenure",
typography: { fontSize: 16, fontWeight: "bold" },
},
},
},
},
widgetLayout: {
"price-pivot": { xTrack: 0, yTrack: 0, xSpan: 24, ySpan: 19 },
"price-treemap": { xTrack: 0, yTrack: 19, xSpan: 12, ySpan: 19 },
"price-sunburst": { xTrack: 12, yTrack: 19, xSpan: 12, ySpan: 19 },
},
filter: { page: [] },
},
],
selectedPageId: "properties-pivot",
};
let studioApi: AgStudioApi;
function toggleMode() {
const currentMode = studioApi.getProperty("mode");
const newMode = currentMode === "edit" ? "view" : "edit";
studioApi.setProperty("mode", newMode);
document.getElementById("toggleMode")!.textContent =
`${newMode === "edit" ? "View" : "Edit"} Mode`;
}
function loadData() {
document.body.dataset.loaded = "true";
studioApi.setProperty("data", new ClickHousePivotDataEngine());
}
const studioProperties: AgStudioProperties = {
mode: "view",
initialState,
};
// setup Studio after the page has finished loading
const studioDiv = document.querySelector<HTMLElement>("#myStudio")!;
studioApi = createStudio(studioDiv, studioProperties);
if (typeof window !== "undefined") {
// Attach external event handlers to window so they can be called from index.html
(<any>window).toggleMode = toggleMode;
(<any>window).loadData = loadData;
}
Contains HM Land Registry data © Crown copyright and database right 2021. This data is licensed under the Open Government Licence v3.0. (Source, Fields)
ClickHouse Live Data Copy Link
Implements createLiveQuery (see Live Data). The engine joins house sales in ClickHouse to a small table of homes for sale that it holds in memory. After Load Data, the application applies a transaction to the local table every second. The live query fetches each query's sales from ClickHouse once. Every 250 milliseconds it checks the local table, and when the table has changed it joins the sales it holds to the new rows and delivers the result, so the table and chart update without querying ClickHouse again.
import {
AgReportState,
AgStudioApi,
AgStudioProperties,
createStudio,
enableStudioDevValidations,
} from "ag-studio";
import { createListingsTransaction } from "./data.ts";
import { ClickHouseLiveDataEngine } from "./engine.ts";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableStudioDevValidations();
}
const initialState: AgReportState = {
pages: [
{
id: "market",
widgets: {
"county-grid": {
type: "grid",
dataMapping: {
cols: [
{ id: "listings.county" },
{ id: "listings.listings", aggregation: "sum" },
{ id: "listings.asking_price", aggregation: "avg" },
{ id: "sales.price", aggregation: "avg" },
],
},
format: {
title: { enabled: true, text: "Homes for Sale" },
style: { grandTotalRow: { enabled: false } },
},
},
"price-chart": {
type: "column-chart-grouped",
dataMapping: {
categoryKey: [{ id: "listings.county" }],
valueKey: [
{ id: "sales.price", aggregation: "avg" },
{ id: "listings.asking_price", aggregation: "avg" },
],
tooltipKey: [],
},
format: {
title: { enabled: true, text: "Sold and Asking Prices by County" },
},
},
},
widgetLayout: {
"county-grid": { xTrack: 0, yTrack: 0, xSpan: 24, ySpan: 18 },
"price-chart": { xTrack: 0, yTrack: 18, xSpan: 24, ySpan: 18 },
},
},
],
selectedPageId: "market",
};
const studioProperties: AgStudioProperties = {
mode: "view",
initialState,
};
let studioApi: AgStudioApi;
function loadData() {
document.body.dataset.loaded = "true";
const engine = new ClickHouseLiveDataEngine();
studioApi.setProperty("data", engine);
// Change a county's listings every second, for as long as Studio uses this engine.
const timer = setInterval(() => {
if (studioApi.getDataEngine() === engine) {
engine.applyTransaction(createListingsTransaction());
} else {
clearInterval(timer);
}
}, 1000);
}
// setup Studio after the page has finished loading
const studioDiv = document.querySelector<HTMLElement>("#myStudio")!;
studioApi = createStudio(studioDiv, studioProperties);
if (typeof window !== "undefined") {
// Attach external event handlers to window so they can be called from index.html
(<any>window).loadData = loadData;
}
Contains HM Land Registry data © Crown copyright and database right 2021. This data is licensed under the Open Government Licence v3.0. (Source, Fields)
REST API: World Bank Countries Copy Link
Queries the World Bank Open Data API. Supported predicates are sent as URL parameters; remaining filters, aggregation, and sorting are applied locally.
import {
AgReportState,
AgStudioApi,
AgStudioProperties,
createStudio,
enableStudioDevValidations,
} from "ag-studio";
import { RestCountriesDataEngine } from "./engine.ts";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableStudioDevValidations();
}
// Dashboard over the World Bank countries API. Fields are prefixed with the
// source id (`countries.*`). The dashboard's filters on region / income
// level / lending type become URL query parameters on the fetch - this is
// the core pedagogy of the example: a filter change produces a narrower
// REST call.
const initialState: AgReportState = {
panels: {
filters: {
collapsed: true,
},
},
pages: [
{
id: "countries-dashboard",
widgets: {
"region-filter": {
type: "list-filter",
dataMapping: { value: [{ id: "countries.region" }] },
format: { title: { enabled: true, text: "Filter by Region" } },
},
"income-filter": {
type: "list-filter",
dataMapping: { value: [{ id: "countries.incomeLevel" }] },
format: { title: { enabled: true, text: "Filter by Income Level" } },
},
"count-by-region": {
type: "bar-chart-grouped",
dataMapping: {
categoryKey: [{ id: "countries.region" }],
valueKey: [{ id: "countries.country", aggregation: "count" }],
},
format: {
title: {
enabled: true,
text: "Countries by Region",
typography: { fontSize: 16, fontWeight: "bold" },
},
},
},
"count-by-income": {
type: "bar-chart-grouped",
dataMapping: {
categoryKey: [{ id: "countries.incomeLevel" }],
valueKey: [{ id: "countries.country", aggregation: "count" }],
},
format: {
title: {
enabled: true,
text: "Countries by Income Level",
typography: { fontSize: 16, fontWeight: "bold" },
},
},
},
"countries-grid": {
type: "grid",
dataMapping: {
cols: [
{ id: "countries.region" },
{ id: "countries.incomeLevel" },
{ id: "countries.lendingType" },
{ id: "countries.country", aggregation: "count" },
],
},
format: {
title: {
enabled: true,
text: "Countries Summary",
typography: { fontSize: 16, fontWeight: "bold" },
},
style: {
grandTotalRow: { enabled: true },
theme: { rowHeight: 28 },
},
},
},
},
widgetLayout: {
"region-filter": { xTrack: 0, yTrack: 0, xSpan: 6, ySpan: 12 },
"income-filter": { xTrack: 0, yTrack: 12, xSpan: 6, ySpan: 12 },
"count-by-region": { xTrack: 6, yTrack: 0, xSpan: 9, ySpan: 24 },
"count-by-income": { xTrack: 15, yTrack: 0, xSpan: 9, ySpan: 24 },
"countries-grid": { xTrack: 0, yTrack: 24, xSpan: 24, ySpan: 14 },
},
filter: { page: [] },
},
],
selectedPageId: "countries-dashboard",
};
let studioApi: AgStudioApi;
function toggleMode() {
const currentMode = studioApi.getProperty("mode");
const newMode = currentMode === "edit" ? "view" : "edit";
studioApi.setProperty("mode", newMode);
document.getElementById("toggleMode")!.textContent =
`${newMode === "edit" ? "View" : "Edit"} Mode`;
}
function loadData() {
document.body.dataset.loaded = "true";
studioApi.setProperty("data", new RestCountriesDataEngine());
}
const studioProperties: AgStudioProperties = {
mode: "view",
initialState,
};
// setup Studio after the page has finished loading
const studioDiv = document.querySelector<HTMLElement>("#myStudio")!;
studioApi = createStudio(studioDiv, studioProperties);
if (typeof window !== "undefined") {
// Attach external event handlers to window so they can be called from index.html
(<any>window).toggleMode = toggleMode;
(<any>window).loadData = loadData;
}
Contains data from The World Bank: Countries API, licensed under Creative Commons Attribution 4.0 (CC BY 4.0). (Terms of Use)