On this page

    Arctic Tables preview

    The SQL commands for Arctic tables enable you to create, show, and drop tables in your data source. Tables created in Arctic are Iceberg tables that support snapshots.

    note:

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

    Creating a Table

    Syntax
    CREATE TABLE <table_path> AS 
      <select_statement>
    

    Parameters

    <table_path>

    String

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


    <select_statement>

    String

    The query used to populate the table.

    Examples

    Create a table
    CREATE TABLE demo.example_table AS 
      SELECT * 
      FROM "oracle_tpch".DREMIO.JOBS
    
    Create a table from a specified tag
    CREATE TABLE demo.example_table AS 
      SELECT * 
      FROM "oracle_tpch".DREMIO.JOBS AT TAG Jan2020
    
    Create a table from a specified commit
    CREATE TABLE demo.example_table AS 
      SELECT * 
      FROM "oracle_tpch".DREMIO.JOBS AT COMMIT "ff2fe50fef5a030c4fc8e61b252bdc33c72e2b6f929d813833d998b8368302e2"
    

    Showing Tables

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

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

    Parameters

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

    String

    Optional

    Specify a reference point where you want to show the available tables from. When this parameter is omitted, the current reference point is used.

    • REF or REFERENCE: Identifies the branch, tag, or commit that you want to show the available tables from.
    • BRANCH: Shows the available tables in the specified branch.
    • TAG: Shows the available tables in the specified tag.
    • COMMIT: Shows the available tables 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 logs in. When this parameter is omitted, the current selected Nessie repository is used.

    Examples

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

    Dropping a Table

    Syntax
    DROP TABLE <table_path>
    

    Parameters

    <table_path>

    String

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

    Examples

    Drop a table
    DROP TABLE demo.example_table