CREATE VIEW
Create or replace a view.
SyntaxCREATE [ 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
<view_name> String
The path of the view that you want to create. The name of the view should be unique.
<select_statement> String
The query used to populate the view.
If the query refers to a table in a remote catalog, then that table must be fully qualified with an AT specification. For example:
CREATE VIEW StudentCatalog.first quarter
AS SELECT * FROM StudentCatalog.studentName
JOIN EnrollmentCatalog.sourceName AT BRANCH firstQuarter
ON TRUE
[ OR REPLACE ] String Optional
If specified, any table/view with the same name will be replaced. This is primarily used to create new tables/views with security policies applied for restricted access. You cannot specify this parameter with the IF NOT EXISTS
qualifier.
<table_name> String
The name of the object. Object names within a project must be unique, cannot conflict with system-defined objects, and are case-insensitive.
<column_name> String
The unique name of the column. Multiple columns may be specified, provided they include their data type and column-name/data-type pairs are separated by commas.
<data_type> String
The data type associated with the column and its underlying data.
<function_name> String
The name of the UDF you wish to associate with this policy. Function names within a project must unique and are case-insensitive.
<column_name> String
The column names to which this row-access policy will apply. Multiple columns may be specified if they are separated by commas.
Examples
Create a viewCREATE VIEW demo.example_view AS
SELECT *
FROM "oracle_tpch".DREMIO.JOBS
CREATE VIEW demo.example_view AS
SELECT *
FROM "oracle_tpch".DREMIO.JOBS AT TAG Jan2020
CREATE OR REPLACE VIEW demo.example_view AS
SELECT *
FROM "oracle_tpch".DREMIO.INVENTORY
CREATE VIEW demo.example_view AS
SELECT *
FROM "oracle_tpch".DREMIO.JOBS AT COMMIT "ff2fe50fef5a030c4fc8e61b252bdc33c72e2b6f929d813833d998b8368302e2"
CREATE VIEW myAmazonS3Source.myFolder.myTable
AS SELECT * FROM myAmazonS3Source.anotherFolder.anotherTable
CREATE TABLE myTable (col1 int, col2 date)
PARTITION BY (month(col2))
CREATE VIEW officers
(name VARCHAR, assignment VARCHAR)
ROW ACCESS POLICY hide_undercover(assignment)
CREATE VIEW employees (
name VARCHAR,
ssn_col VARCHAR MASKING POLICY protect_ssn (ssn_col),
department VARCHAR)