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.
Table names cannot include the following special characters: /, :, [, or ].

Syntax

CREATE TABLE [ IF NOT EXISTS ] <table_name>
  ( <column_name> [ , ... ] )
  [ AT { REF[ERENCE] | BRANCH } <reference_name> ]
  [ MASKING POLICY <function_name> ( <column_name> [ , ... ] ) ]
  [ ROW ACCESS POLICY <function_name> ( <column_name> [ , ... ] ) ]
  [ PARTITION BY ( { <column_name> | <partition_transform> } [ , ... ] ) ]
  [ CLUSTER BY ( <column_name> [ , ... ] ) ]
  [ LOCALSORT BY ( <column_name> [ , ... ] ) ]
  [ TBLPROPERTIES ( '<property_name>' = '<property_value>' [ , ... ] ) ]

Parameters

IF NOT EXISTS Optional

Include if Dremio should run the statement only if a table with the same name in the same path does not already exist.

<table_name> String

The name of the table that you want to create. The name of the table must be unique and cannot include the following special characters: /, :, [, or ].

( <column_name> [ , ... ] ) String

Creates one or more columns that have the specified names, data types, and character limits. The size is an attribute of the data type.

These are the supported primitive types:

BOOLEAN
VARBINARY
DATE
FLOAT
DECIMAL
DOUBLE
INTERVAL
INT
BIGINT
TIME
TIMESTAMP
VARCHAR (The length is always 65536 bytes. If a length is specified, it is ignored.)

You can define complex types by using either of these two sets of syntax:

Set 1

struct_type: ROW( name primitive_or_complex_type, .. )
list_type: ARRAY(primitive_or_complex_type)

Examples:

ROW(innerfield INT, anotherinnerfield DOUBLE)
ARRAY(INT)
ROW(innerfield ARRAY(INT))
ARRAY(ROW(innerfield INT))

Set 2

struct_type: STRUCT <name : primitive_or_complex_type, ... >
list_type: { LIST | ARRAY } < primitive_or_complex_type >

Examples:

STRUCT<innerfield : INT, anotherinnerfield : DOUBLE>
LIST<INT>
ARRAY<INT>
STRUCT<innerfield : LIST<INT>>
LIST<STRUCT<innerfield : INT>>

You cannot use the INSERT command to insert data into columns that use a complex data type.

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.

MASKING POLICY <function_name> ( <column_name> [ , ... ] ) String Optional

Sets a policy for masking data. The UDF serving as the masking policy must accept and return the same data type as the column it is masking. If a UDF with the given name does not exist, then the affected table will not be reachable until the policy is dropped or a UDF created. Available only in the Enterprise edition.

ROW ACCESS POLICY <function_name> ( <column_name> [ , ... ] ) String Optional

Adds a row-access policy to the table. The UDF serving as the row-access policy must return BOOLEAN. If the UDF specified in the policy does not exist, then Dremio gives an error when attempting to query the table. Available only in the Enterprise edition.

PARTITION BY ( { <column_name> | <partition_transform> } [ , ... ] ) String Optional

The columns or partition-transformation functions on which to partition the table data.

<column_name>: The unique name of the column. Multiple columns may be specified as long as the column names include their data type. Use commas to separate each column name and data-type pair. If a column is listed as a partition column, it cannot also be listed as a sort column for the same table. Each column specified must also be listed as a dimension column.

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.

<partition_transform>: Applies only to Iceberg tables. Use one of these partition-transformation functions:

Transform	Description
identity( <col> )	Explicitly specified identity transform.
year( <col> )	Partition by year. The column uses either the TIMESTAMP or DATE data type.
month( <ts_col> )	Partition by month. The column uses either the TIMESTAMP or DATE data type.
day( <ts_col> )	Partition by day. The column uses either the TIMESTAMP or DATE data type.
hour( <ts_col> )	Partition by hour. The column uses either the TIMESTAMP or DATE data type.
bucket( <count>, <col> )	Partition by hashed value into <count> buckets.
truncate( <length>, <col> )	Partition by truncated value. Strings are truncated to the specified length. Integer and biginteger values are truncated to bins (for example: truncate(10, i) produces 0, 10, 20, and so on).

CLUSTER BY ( <column_name> [ , ... ] ) String Optional

The column(s) to cluster the table on.

Sets the clustering keys for the specified table. To specify multiple columns, use commas to separate each column name. Available only in the Enterprise edition.

Clustering cannot be used in conjunction with PARTITION BY or LOCAL SORT.

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 you are doing a range filter on your LOCALSORT column, your query will be faster because the data is already sorted.

TBLPROPERTIES ( '<property_name>' = '<property_value>' [ , ... ] )

Sets one or more table properties for controlling the behavior of the table. Applies only to Iceberg tables. See Properties of Apache Iceberg Tables for a list of the supported properties.

Examples

Create a table from a branch

CREATE TABLE employees
  (PersonID int, LastName varchar, FirstName varchar, Address varchar, City varchar)
  AT BRANCH main

Create a table

CREATE TABLE employees (name VARCHAR, age INT)

Create a struct table

CREATE TABLE struct_type ( a struct<x: varchar> )

Creating a table and partitioning it by month

CREATE TABLE myTable (col1 int, col2 date)
  PARTITION BY (month(col2))

Parameters​

Examples​

Parameters

Examples