CREATE TABLE AS

Create a new table as a select statement from another 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>
  [ AT { REF[ERENCE] | BRANCH } <reference_name> ]
  [ PARTITION BY ( { <column_name> | <partition_transform> } [ , ... ] )
  [ LOCALSORT BY ( <column_name> [ , ... ] ) ]
  [ TBLPROPERTIES ( '<property_name>' = '<property_value>' [ , ... ] ) ]
   AS <select_statement>

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. The table can be in the scratch directory or in a data lake source.

---

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_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.

• <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. Example: truncate(10, i) produces 0, 10, 20, and so on.

  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 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. See Properties of Apache Iceberg Tables for a list of the supported properties.

---

AS <select_statement> String

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

Examples

Create a table in your project store

CREATE TABLE demo_table
  AS SELECT * FROM Samples."samples.dremio.com"."zips.json"

Create a table in your project store using PARTITION BY and LOCALSORT

CREATE TABLE demo_table2
  PARTITION BY (state)
  LOCALSORT BY (city)
  AS SELECT * FROM Samples."samples.dremio.com"."zips.json"

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"

Create a table as SELECT * from another table

CREATE TABLE myAmazonS3Source.myFolder.myTable
  AS SELECT * FROM myAmazonS3Source.anotherFolder.anotherTable