Creates a materialized or non-materialized view from existing tables or views.

Syntax

CREATE [ MATERIALIZED ] VIEW viewName ( columnList ) AS SELECT selectQuery { WITH ROLE_ESCALATION [ true / false ] } ;

Arguments

  • MATERIALIZED: Specifies a materialized view created on a target database. Materialized views are persisted until dropped. The user must have the CREATE TABLE privilege on the destination location in order to create a materialized view.

  • viewName: The name for the new view. If this is a simple view name, the view will be created for the current user session only and will be dropped automatically at the end of the session. Views can also be created within a Cirro system of type ‘HUB’ which has been created for this purpose. In that case, the view will persist until dropped.

  • columnList: A list of columns for the new view. This list must match the number or columns returned from selectQuery. columnList is only required when specifying column names other than those returned from selectQuery.

  • selectQuery: A SELECT statement.

  • WITH ROLE_ESCALATION: Allows the grantee of SELECT privileges on a view to see the data even when they do not have SELECT permission on the underlying data sources

    • true - a Cirro user with SELECT privileges on the view can view data when either the Cirro user has SELECT privileges on the data sources, or the owner of the view has SELECT privileges on the data sources.

    • false - a Cirro user with SELECT privileges on the view can only view data if they also have SELECT privileges on the data sources.

Additional Information

  • ROLE ESCALATION is not available on MATERIALIZED VIEWS.

  • ROLE_ESCALATION may default to true or false at the server level. CREATE VIEW can be used to set the alternate option when creating a non-materialized view.

Examples

Create a materialized view on a target datasource.

create materialized view dataxchange.postgres.public.doctest_view
AS SELECT * FROM dataxchange.postgres.public.doctest

Create a view on the cirro server with role escalation.

create view doctest_view
AS SELECT * FROM dataxchange.postgres.public.doctest
WITH ROLE_ESCALATION true;
CREATE VIEW customer2012 AS SELECT * FROM MYSQL.inventory.customer WHERE createDate > '2012-01-01';
CREATE VIEW customer2012 AS SELECT * FROM MYSQL.inventory.customer WHERE createDate > '2012-01-01' WITH ROLE_ESCALATION true;
CREATE MATERIALIZED VIEW ORACLE1.oracle.INVENTORY.custview22 AS SELECT S.* FROM SQLSERVERSUPPORT.inventory.dbo.customer S INNER JOIN MYSQLSUPPORT.inventory.inventory.cust_sales2 M ON M.cust_id=S.cust_id;

See Also