This page gives an overview on creating, altering and dropping views and materialized views in Polypheny using PolySQL.
CREATE VIEWcreates a view of a query. Views have no data placements on a store but are references to an underlying table.
CREATE OR REPLACE VIEWthe only difference to the previous option is that if a view with this name already exists, it is replaced. That means the old view is dropped and a new view will be created.
DROP VIEWdeletes an existing view.
DROP VIEW IF EXISTSdeletes an existing view but checks first if the view to drop is present.
ALTER VIEW RENAME TOallows changing the name of a view.
ALTER VIEW RENAME COLUMNallows changing the name of a specific column of the view.
CREATE MATERIALIZED VIEWcreates a materialized view of a query.
ON STOREone or many stores can be selected. The data from the selected query is placed on the chosen Data Store.
FRESHNESSdescribes how often and with which method the materialized view should be refreshed.
FRESHNESS INTERVALallows specifying after what time the materialized should be updated. For example,
FRESHNESS INTERVAL 10 "hours"this refreshes the materialized view every 10 hours.
FRESHNESS UPDATEallows specifying after how many updates to the underlying table the materialized view is updated. For example,
FRESHNESS UPDATE 2after two updates on the underlying entities, the materialized view is automatically updated. For now, it is only possible to use this option if the underlying tables do include a modifiable table.
FRESHNESS MANUALwith this type of freshness, the materialized view is not automatically updated. Every update of the data needs to be triggered manually. It is triggered with the following command:
ALTER MATERIALIZED VIEW name FRESHNESS MANUAL
ALTER MATERIALIZED VIEW ADD INDEXallows adding indices for materialized views.
ALTER MATERIALIZED VIEW DROP INDEXto drop indices from materialized views.
DROP MATERIALIZED VIEWdeletes an existing materialized view.
DROP MATERIALIZED VIEW IF EXISTSdeletes an existing materialized view but checks first if the view to drop is present.
ALTER MATERIALIZED VIEW RENAME TOallows changing the name of a materialized view.
ALTER MATERIALIZED VIEW RENAME COLUMNallows changing the name of a specific column of the materialized view.