Views and materialized views

The engine has two kinds of named query:

• A logical view stores a canonical plan and no data. Each read runs the plan.
• A materialized view is maintained continuously into an engine-owned table, and is read by its name.

A parameterized materialized view is a template; CREATE MATERIALIZATION turns it into a concrete, maintained instance.

Logical views

CREATE [OR REPLACE] VIEW <name> AS SELECT …
DROP VIEW [IF EXISTS] <name>
SHOW CREATE VIEW <name>

A view body may declare typed parameters inline with ${name:TYPE}:

CREATE VIEW address_balances AS
SELECT token, amount, updated_at
FROM balances
WHERE chain_id = 1 AND address = ${address:STRING};

SHOW CREATE VIEW address_balances;

CREATE OR REPLACE VIEW installs the next definition revision (the reply carries "definition_revision":2). SHOW CREATE VIEW returns one row with a create_statement column.

Reading a view

A view is read like a table, with filters, grouping, ordering and limits of its own. Parameter values travel in the request's parameters list.

SELECT token, amount
FROM address_balances
ORDER BY amount DESC
LIMIT 5;

STREAM SELECT over a view follows its result live. A view survives a restart and is read the same way afterwards.

Dropping a view

DROP VIEW address_balances;

With IF EXISTS, a missing view answers success with "applied":false.

Materialized views

CREATE [OR REPLACE] MATERIALIZED VIEW <name>[(${param:TYPE}, …)] AS SELECT …
DROP MATERIALIZED VIEW [IF EXISTS] <name>
SHOW CREATE MATERIALIZED VIEW <name>

CREATE MATERIALIZED VIEW token_volume AS
SELECT chain_id, token, SUM(amount) AS volume, COUNT(*) AS transfers
FROM transfers
GROUP BY chain_id, token;

A materialized view:

• is maintained continuously from its sources through PSI; inserts, updates and deletes on a source reach it, and it is exact after a restart;
• is read by its name, like a table (SELECT * FROM token_volume);
• is written only by the engine;
• keeps its rows in an engine-owned table.

Replacing a materialized view

CREATE OR REPLACE MATERIALIZED VIEW installs a new revision (the reply carries "ddl_kind":8), and the view's rows are rebuilt from the new definition.

Parameterized templates and CREATE MATERIALIZATION

A materialized view with a parameter list is a template: it holds no data by itself. CREATE MATERIALIZATION fixes its arguments and creates a maintained instance.

CREATE MATERIALIZATION <instance> OF <template>(<param> => <literal>, …)
DROP MATERIALIZATION [IF EXISTS] <instance>
SHOW CREATE MATERIALIZATION <instance>

CREATE MATERIALIZED VIEW balances_by_chain(${chain:UINT16}) AS
SELECT address, token, amount
FROM balances
WHERE chain_id = ${chain};

CREATE MATERIALIZATION balances_chain_1 OF balances_by_chain(chain => 1);

The instance is read by its own name (SELECT * FROM balances_chain_1), follows source changes and is maintained again after a restart.

Dropping materialized views and instances

Instances of a template are dropped before the template:

DROP MATERIALIZATION balances_chain_1;

DROP MATERIALIZED VIEW balances_by_chain;

After the drop, a read of the name answers HTTP 404.

Changing a definition

Use CREATE OR REPLACE VIEW and CREATE OR REPLACE MATERIALIZED VIEW to change a definition in place.