On this page

    Arctic Views preview

    The SQL commands for Arctic views enable you to create, replace, show, and drop views in your data source. Views created in Arctic have the notion of versions and are Iceberg views (see 0.14 Iceberg View Spec and the Project Nessie GitHub repo for the experimental implementation).

    notes:

    If you are using these SQL commands in engines other than Dremio Sonar, the SQL syntax may differ:

    Creating or Replacing a View

    Syntax
    CREATE [ OR REPLACE ] VIEW <view_path> AS 
      <select_statement>
    

    Parameters

    <view_path>

    String

    The path of the view that you want to create. The name of the view should be unique. The view is stored in the selected Context.


    <select_statement>

    String

    The query used to populate the view.

    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
    
    Create a view from a specified commit
    CREATE VIEW demo.example_view AS 
      SELECT * 
      FROM "oracle_tpch".DREMIO.JOBS AT COMMIT "ff2fe50fef5a030c4fc8e61b252bdc33c72e2b6f929d813833d998b8368302e2"
    
    Replace a view
    CREATE OR REPLACE VIEW demo.example_view AS 
      SELECT * 
      FROM "oracle_tpch".DREMIO.INVENTORY
    

    Showing a View

    The SHOW VIEWS command enables you to show all the views that are available in a project’s Nessie repository and within a specific reference point.

    Syntax
    SHOW VIEWS
       [ AT ( REF[ERENCE] | BRANCH | TAG | COMMIT ) <refValue> ]
       [ IN <nessie_name> ]
    
    

    Parameters

    <view_path>

    String

    The path of the view that you want to create. The name of the view should be unique. The view is stored in the selected Context.


    AT ( REF[ERENCE] | BRANCH | TAG | COMMIT ) <refValue>

    String

    Optional

    Specify a reference point where you want the view:

    • REF[ERENCE]: Identifies the branch, tag, or commit that you want to show the available views from.
    • BRANCH: Shows the available views in the specified branch.
    • TAG: Shows the available views in the specified tag.
    • COMMIT: Shows the available views in the specified commit. Commit hashes must be enclosed in double quotes (for example, “ff2fe50fef5a030c4fc8e61b252bdc33c72e2b6f929d813833d998b8368302e2”).
    .


    IN <nessie_name>

    String

    Optional

    The name of the Nessie repository that you want to show the available tables in. If not specified, the default is the current selected Nessie repository.

    Examples

    Show the available views in the current Nessie repository
    SHOW VIEWS
    
    Show the available views in a specified reference point in the current Nessie repository
    SHOW VIEWS
       AT REF <main_branch>
    
    Show the available views in a specified reference point in the specified Nessie repository
    SHOW VIEWS 
       AT BRANCH <main_branch>
       IN <another_nessie_repo>
    
    

    Dropping a View

    Syntax
    DROP VIEW <view_path>
    

    Parameters

    <view_path>

    String

    The path of the view that you want to drop. The view will be dropped from the selected Context.

    Examples

    Drop a view
    DROP VIEW demo.example_view