Undercurrent Analytics

Custom views

A view is a named query over your events that you define once and then use like a table in any panel, on any dashboard. Views are the way to share filtering (and column-shaping) logic across dashboards instead of copying the same WHERE clause into every panel.

Views work like database views: CREATE VIEW, then SELECT … FROM view_name. They are stored server-side with your project, so every dashboard and every panel sees the same definition — change the view once and every panel that uses it follows.

Creating a view

Run a CREATE VIEW statement anywhere you can run a query — Grafana Explore is the natural place:

CREATE VIEW prod_events AS
SELECT * FROM $__table
WHERE json_get_str(properties, 'env') = 'prod'

The definition is validated and test-executed before it is saved, so a typo or an unsupported expression comes back as a normal query error and nothing is stored.

Using a view

Reference it like a table, anywhere you would write $__table:

SELECT date_trunc('hour', time) AS hour, COUNT(*) AS events
FROM prod_events
WHERE $__timeFilter(time)
GROUP BY date_trunc('hour', time)

Keep $__timeFilter in the panel query — the view supplies the what, the panel supplies the when (see the rules at the bottom of this page).

Managing views

Statement Effect
SHOW VIEWS Lists your views with their definitions.
CREATE VIEW name AS SELECT … Creates a view; errors if the name exists.
CREATE OR REPLACE VIEW name AS SELECT … Creates or redefines a view.
DROP VIEW name Deletes a view (refused while another view depends on it).
DROP VIEW IF EXISTS name Deletes a view; no error if it doesn’t exist.

Shaping columns, not just filtering

A view body is a full SELECT, so it can also pull JSON properties out into real columns once, instead of in every panel:

CREATE VIEW purchases AS
SELECT time, distinct_id, os,
       json_get_str(properties, 'plan')  AS plan,
       json_get_str(properties, 'price') AS price
FROM $__table
WHERE event = 'purchase'

Panels then read plan and price like ordinary columns:

SELECT plan, COUNT(*) FROM purchases WHERE $__timeFilter(time) GROUP BY plan

Views on views

Views can build on other views, so shared filters compose:

CREATE VIEW ios_prod AS
SELECT * FROM prod_events WHERE os = 'iOS'

Dropping prod_events while ios_prod depends on it is refused — drop or redefine the dependent view first.

A dashboard-wide view switcher

Because SHOW VIEWS returns view names as its first column, you can add a dropdown to a dashboard that switches every panel between views:

  1. In the dashboard settings, add a Query variable named view with the query SHOW VIEWS.
  2. Write panel queries as … FROM ${view} ….

The variable dropdown at the top of the dashboard now re-points all panels at whichever view you select — for example flipping a whole dashboard between prod_events and ios_prod.

Rules and limits

  • Names are lowercase identifiers: letters, digits, and underscores, starting with a letter or underscore, at most 64 characters. The r2sql_ prefix is reserved.
  • Bodies are a single SELECT reading from $__table and/or your other views, under the same dialect rules as panel queries.
  • No time macros in view bodies. $__timeFilter, $__timeFrom, and $__timeTo come from a panel’s time picker, which a stored view never sees — creating a view with one is rejected with a reminder to keep the time filter in the panel query.
  • Nesting is capped at 5 levels of view-on-view, and circular definitions are rejected.
  • A WITH CTE in a query that has the same name as a view shadows the view for that query.
  • Views belong to your project: they are private to you, and only you can create, list, or drop them.

← Back to undercurrentanalytics.dev