CREATE TABLE

Create a new table.

note

To create a table inside a namespace like a folder, the namespace must already exist. Dremio does not support creating the namespace and a table inside it in a single command. Instead, you must explicitly create the namespace before you can create the table.

Syntax

CREATE TABLE [ IF NOT EXISTS ] <table_name>
  [ AT { REF[ERENCE] | BRANCH } <reference_name> ]
  [ PARTITION BY ( <column_name1>, <column_name2>, ... ) ]
  [ LOCALSORT BY ( <column_name> ) ]
   AS <select_statement>

-- Iceberg tables only
CREATE TABLE [ IF NOT EXISTS ] <table_name>
  ( <column_spec> [, ...] )
  [ PARTITION BY ( { <column_name> | <partition_transform> } [ , ... ] ) ]

-- Set a Column-Masking Policy
CREATE TABLE [ IF NOT EXISTS ] <table_name>
( <column_name> <data_type> MASKING POLICY <function_name> ( <column_name> [, ... ] ) )

-- Add a Row-Access Policy
CREATE TABLE [ IF NOT EXISTS ] <table_name>
( <column_name> <data_type> [, ...] )
ROW ACCESS POLICY <function_name> ( <column_name> [, ... ] )

Parameters

<table_name>

String

The name of the table that you want to create. The name of the table must be unique. The table can be in the scratch directory or a data lake source.

---

AS <select_statement>

String

Use the SELECT statement to populate the new table using data from an existing table or view.

---

[ IF NOT EXISTS ]

String

Causes Dremio Cloud to run the statement only if a table with the same name in the same path does not already exist.

---

AT { REF[ERENCE] | BRANCH } <reference_name>

String

Optional

Specifies the reference at which you want the new table to be created. When this parameter is omitted, the current reference is used.
• REF: Identifies the specific reference.
• BRANCH: Identifies the specific branch.

---

PARTITION BY ( <column_name1>, <column_name2>, ... )

String

The names of the columns that you want to partition the table by. When you designate column(s) using this clause, Dremio will store the rows containing the same hash partition values in their own Parquet files. Using this clause provides speed optimization for when your WHERE clause matches your PARTITION BY clause.

NOTE:
• If you partition a table by a column that has more distinct values than Dremio can partition by, you will receive an error message. If you receive this error message, try partitioning your table by a different column.
• If the table is not partitioned, you will receive an error when you attempt to refresh the metadata using the PARTITION BY clause.

---

LOCALSORT BY <column_name>

String

The name of the column that you want to sort the table by. Sorts each Parquet file fragment by the designated column. If your ORDER BY column matches your LOCALSORT column or you are doing a range filter on your LOCALSORT column, your query will be faster because the data is already sorted.

---