Views
This page gives an overview on creating, altering and dropping views and materialized views in Polypheny using PolySQL.
Views
createViewStatement:
CREATE VIEW [ OR REPLACE ] name
[ '(' tableElement [, tableElement ]* ')' ]
[ AS query ]
dropViewStatement:
DROP VIEW [ IF EXISTS ] name
alterStatement:
ALTER VIEW [ schemaName . ] viewName RENAME TO newViewName
| ALTER VIEW [ schemaName . ] viewName RENAME COLUMN columnName TO newColumnName
CREATE VIEW
creates a view of a query. Views have no data placements on a store but are references to an underlying table.CREATE OR REPLACE VIEW
the 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 VIEW
deletes an existing view.DROP VIEW IF EXISTS
deletes an existing view but checks first if the view to drop is present.ALTER VIEW RENAME TO
allows changing the name of a view.ALTER VIEW RENAME COLUMN
allows changing the name of a specific column of the view.
Materialized Views
createMaterializedViewStatement:
CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] name
[ '(' tableElement [, tableElement ]* ')' ]
[ AS query ]
[ <ON> <STORE> storeName ( <COMMA> storeName )*]
[FRESHNESS (INTERVAL interval timeUnit | UPDATE interval | MANUAL ) ]
dropMaterializedViewStatement:
DROP MATERIALIZED VIEW [ IF EXISTS ] name
alterStatement:
ALTER MATERIALIZED VIEW [ schemaName . ] viewName RENAME TO newViewName
| ALTER MATERIALIZED VIEW [ schemaName . ] viewName RENAME COLUMN columnName TO newColumnName
| ALTER MATERIALIZED VIEW [ schemaName . ] viewName FRESHNESS MANUAL
| ALTER MATERIALIZED VIEW [ schemaName . ] viewName ADD [UNIQUE] INDEX indexName ON ( columnName | '(' columnName [ , columnName ]* ')' ) [ USING indexMethod ] [ ON STORE storeUniqueName ]
| ALTER MATERIALIZED VIEW [ schemaName . ] viewName DROP INDEX indexName
CREATE MATERIALIZED VIEW
creates a materialized view of a query.ON STORE
one or many stores can be selected. The data from the selected query is placed on the chosen Data Store.FRESHNESS
describes how often and with which method the materialized view should be refreshed.FRESHNESS INTERVAL
allows specifying after what time the materialized should be updated. For example,FRESHNESS INTERVAL 10 "hours"
this refreshes the materialized view every 10 hours.FRESHNESS UPDATE
allows specifying after how many updates to the underlying table the materialized view is updated. For example,FRESHNESS UPDATE 2
after 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 MANUAL
with 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 INDEX
allows adding indices for materialized views.ALTER MATERIALIZED VIEW DROP INDEX
to drop indices from materialized views.DROP MATERIALIZED VIEW
deletes an existing materialized view.DROP MATERIALIZED VIEW IF EXISTS
deletes an existing materialized view but checks first if the view to drop is present.ALTER MATERIALIZED VIEW RENAME TO
allows changing the name of a materialized view.ALTER MATERIALIZED VIEW RENAME COLUMN
allows changing the name of a specific column of the materialized view.