Build tables that maintain themselves

Hands-on exercise: a summary that keeps itself fresh

You will build a table you own, put a materialized view on top of it, watch the view refresh itself when the table changes, run a task by hand, then clean everything up. It takes about 40 minutes in Studio. Everything is created under your personal workspace, so nothing here is visible to anyone else.

Find your username first. Every statement below writes personal.<you>, and you substitute the value this returns:

SELECT USER();

1. A table of your own

CREATE TABLE personal.<you>.planet_log AS
SELECT name,
       mass,
       number_of_moons,
       'initial' AS load_batch
  FROM public.astronomy.planets;

One row per planet in the sample, committed as a single version. load_batch is a plain string column you will use to tell loads apart.

2. A view that maintains itself

CREATE MATERIALIZED VIEW personal.<you>.batch_totals AS
SELECT load_batch,
       COUNT(*)             AS planets,
       SUM(number_of_moons) AS moons
  FROM personal.<you>.planet_log
 GROUP BY load_batch;

SELECT * FROM personal.<you>.batch_totals;

One row, initial. Creating the view also registered a refresh trigger on planet_log. Look at it:

SHOW TRIGGERS FOR personal.<you>.planet_log;

3. Change the source, watch the view follow

INSERT INTO personal.<you>.planet_log (name, mass, number_of_moons, load_batch)
VALUES ('Ceres', 0.00094, 0, 'dwarf');

Commits within about a minute coalesce into one refresh, so wait a minute, then:

SELECT * FROM personal.<you>.batch_totals ORDER BY load_batch;

Two rows now. If there is still one, check how the last refresh went:

SELECT trigger_name, target, last_fired_at, last_fired_status
  FROM personal.information_schema.triggers;

4. Read the history

SHOW SNAPSHOTS FOR personal.<you>.planet_log;

Two commits: the CREATE and the INSERT. The refresh does not appear here because it was a commit to batch_totals, not to planet_log.

Read the table as it was before the insert:

SELECT load_batch, COUNT(*) AS planets
  FROM personal.<you>.planet_log VERSION AS OF PREVIOUS
 GROUP BY load_batch;

Only initial.

5. A task you run by hand

A materialized view re-runs one SELECT. A task runs any statement, with values supplied at run time:

CREATE TASK personal.<you>.add_body AS
    INSERT INTO personal.<you>.planet_log (name, mass, number_of_moons, load_batch)
    VALUES (:name, :mass, :moons, 'manual');

EXECUTE personal.<you>.add_body
    USING 'Eris' AS name,
          0.0166 AS mass,
          1 AS moons;

The task ran as you, gated by your permissions at that moment. Query batch_totals again after a minute: a manual row should have appeared, because the task's INSERT committed to a table the view's trigger watches.

6. Who can see it

SHOW GRANTS;

This lists the roles you hold and what each one permits. Your personal workspace cannot be shared with anyone else, so there is nothing to grant here. Read GRANT for the statement you would use on a shared workspace, then work out which role each of steps 1 to 5 needed. Creating the view and its trigger needed writer on planet_log, not just reader.

7. Clean up

DROP TASK personal.<you>.add_body;

DROP MATERIALIZED VIEW personal.<you>.batch_totals;

DROP TABLE personal.<you>.planet_log;

Drop the view before the table so nothing is left pointing at a table that has gone. DROP TABLE also removes the history you read in step 4.

Check your understanding