CREATE TABLE

Create a new table.

{{< codeheader "Syntax" expand >}}

CREATE TABLE [ IF NOT EXISTS ] <table_name>
  [ PARTITION BY ( <column_name1>, <column_name2>, ... ) ]
  [LOCALSORT BY ( <column_name> ) ]
   AS <query>

-- Partition for Apache Iceberg Tables Only
CREATE TABLE [ IF NOT EXISTS ] <table_name>
  [ 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

{{< sql-section file="data/sql/tables.json" data="creatingATable" >}}

{{< sql-section file="data/sql/tables.json" data="creatingATableOptional" >}}

{{< sql-section file="data/sql/tables.json" data="creatingAnIcebergTable" >}}

{{< sql-section file="data/sql/row-column-policies.json" data="maskingNewParameters" >}}

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"

Creating a table as SELECT * from another table

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

Creating a table and partitioning it by month

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

Add a row-access policy to a table

CREATE TABLE officers
(name VARCHAR, assignment VARCHAR) ROW ACCESS POLICY hide_undercover(assignment)

Set a column-masking policy on a column

CREATE TABLE employees
(name VARCHAR, ssn_col VARCHAR MASKING POLICY protect_ssn (ssn_col),department VARCHAR)