CREATE VIEW

Create or replace a view.

Syntax

CREATE [ OR REPLACE ] VIEW <view_name> AS
  <select_statement> <table_name>

-- Set a Column-Masking Policy
CREATE [ OR REPLACE ] VIEW <view_name>
( <column_name> <data_type> MASKING POLICY function_name ( <column_name> [, ... ] ) )

-- Add a Row-Access Policy
CREATE [ OR REPLACE ] VIEW <view_name>
( <column_name> <data_type> [, ... ] )
ROW ACCESS POLICY <function_name> ( <column_name> [, ... ] )

Parameters

{{< sql-section file="data/sql/views.json" data="creatingAView" >}}

{{< sql-section file="data/sql/row-column-policies.json" data="createViewNewParameters" >}}

Examples

Create a view

CREATE VIEW demo.example_view AS
  SELECT *
  FROM "oracle_tpch".DREMIO.JOBS

Create a view from a specified tag

CREATE VIEW demo.example_view AS
  SELECT *
  FROM "oracle_tpch".DREMIO.JOBS AT TAG Jan2020

Replace view if it already exists or create a new one

CREATE OR REPLACE VIEW demo.example_view AS
  SELECT *
  FROM "oracle_tpch".DREMIO.INVENTORY

Create a view from a specified commit

CREATE VIEW demo.example_view AS
  SELECT *
  FROM "oracle_tpch".DREMIO.JOBS AT COMMIT "ff2fe50fef5a030c4fc8e61b252bdc33c72e2b6f929d813833d998b8368302e2"

Creating a view as SELECT * from another table

CREATE VIEW myAmazonS3Source.myFolder.myTable
  AS SELECT * FROM myAmazonS3Source.anotherFolder.anotherTable

Creating a view and partitioning it by month

CREATE TABLE myTable (col1 int, col2 date)
  PARTITION BY (month(col2))

Add a row-access policy to a view

CREATE VIEW officers
(name VARCHAR, assignment VARCHAR)
ROW ACCESS POLICY hide_undercover(assignment)

Set a column-masking policy on a column

CREATE VIEW employees (
 name VARCHAR,
 ssn_col VARCHAR MASKING POLICY protect_ssn (ssn_col),
 department VARCHAR)