create_view¶

The create_view command creates a new view that will be incrementally maintained by epsio. Much like Postgres' materialized views, the results of the given query will be pre-calculated and materialized into a table. However, with epsio - when the underlying data changes, epsio updates the query results accordingly.

Syntax¶

PostgreSQLMySQL

CALL epsio.create_view(
    <view_name>, 
    <query>, 
    <block_until_running (optional)>, 
    <use_existing_table (optional)>
);

Parameter	Information
view_name	A unique name for the view (Can include a schema).
query	Full SQL query.
block_until_running (Optional, default `true`)	Whether or not to wait for population to end before returning.
use_existing_table (Optional, default `false`)	Whether to create a table for the Epsio view or to use an existing one.

Details¶

After creating a new view, Epsio processes the data that already exists in the relevant tables. This phase is known as the population phase and can take time depending on the size of your dataset. When the population is over, the view's status will be changed to running and the view will be ready to be queried and process changes in realtime.

When creating a view on tables that you haven't created views on before, you'll need to add those tables to the publication that was created during deployment and set a replica identity for the table:
ALTER TABLE <table_name> REPLICA IDENTITY FULL;
ALTER PUBLICATION epsio_publication ADD TABLE <table_name>;

Examples¶

Creating a view:

CALL epsio.create_view('countries_population',
    'SELECT country.name, sum(city.population) AS total_population
    FROM playground.country
    JOIN playground.city ON country.code = city.country_code
    GROUP BY country.name');

Querying from the newly created view:

SELECT * FROM countries_population;

CALL epsio.create_view(
    <dbname>.<view_name>, 
    <query>,
    <block_until_running>
);

Parameter	Information
view_name	A unique name for the view (Must include the database- e.g. `my_db.my_view`).
query	Full SQL query.
block_until_running	Whether or not to wait for population to end before returning.

Details¶

After creating a new view, Epsio processes the data that already exists in the relevant tables. This phase is known as the population phase and can take time depending on the size of your dataset. When the population is over, the view's status will be changed to running and the view will be ready to be queried and process changes in realtime.

Examples¶

Creating a view:

CALL epsio.create_view('maindb.countries_population',
    'SELECT country.name, sum(city.population) AS total_population
    FROM playground.country
    JOIN playground.city ON country.code = city.country_code
    GROUP BY country.name');

Querying from the newly created view:

SELECT * FROM maindb.countries_population;