Dremio Cloud
Dremio Software
 
On this page

    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

    <view_name>

    String

    The path of the view that you want to create. The name of the view should be unique. The view can be in your home space.

    <select_statement>

    String

    The query used to populate the view.

    [ OR REPLACE ]

    String

    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 are separated by a comma.

    <data_type>

    String

    Specifies 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 separated by a comma.

    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)