CREATE TABLE

CREATE TABLE

Synopsis

CREATE TABLE [ IF NOT EXISTS ]
table_name (
  { column_name data_type [ COMMENT comment ] [ WITH ( property_name = expression [, ...] ) ]
  | LIKE existing_table_name [ { INCLUDING | EXCLUDING } PROPERTIES ] }
  [, ...]
)
[ COMMENT table_comment ]
[ WITH ( property_name = expression [, ...] ) ]

Description

Create a new, empty table with the specified columns. Use create-table-as to create a table with data.

The optional IF NOT EXISTS clause causes the error to be suppressed if the table already exists.

The optional WITH clause can be used to set properties on the newly created table or on single columns. To list all available table properties, run the following query:

SELECT * FROM system.metadata.table_properties

For example, to hive connector, below are some of available and frequently used table properties:

Property Name	data type	Description	Default
`format`	varchar	Hive storage format for the table. Possible values: [ORC, PARQUET, AVRO, RCBINARY, RCTEXT, SEQUENCEFILE, JSON, TEXTFILE, CSV]	ORC
`bucket_count`	integer	Number of buckets
`bucketed_by`	array(varchar)	Bucketing columns
`sorted_by`	array(varchar)	Bucket sorting columns
`external`	boolean	Is the table an external table	`false`
`location`	varchar	File system location URI for the table location value must be provided if `external`=`true`
`partitioned_by`	array(varchar)	Partition columns
`transactional`	boolean	Is transactional property enabled There is a limitation that only ORC Storage format support creating an transactional table	`false`

To list all available column properties, run the following query:

SELECT * FROM system.metadata.column_properties

The LIKE clause can be used to include all the column definitions from an existing table in the new table. Multiple LIKE clauses may be specified, which allows copying the columns from multiple tables.

If INCLUDING PROPERTIES is specified, all of the table properties are copied to the new table. If the WITH clause specifies the same property name as one of the copied properties, the value from the WITH clause will be used. The default behavior is EXCLUDING PROPERTIES. The INCLUDING PROPERTIES option maybe specified for at most one table.

Examples

Create a new table orders:

CREATE TABLE orders (
  orderkey bigint,
  orderstatus varchar,
  totalprice double,
  orderdate date
)
WITH (format = 'ORC')

Create a new transactional table orders:

CREATE TABLE orders (
  orderkey bigint,
  orderstatus varchar,
  totalprice double,
  orderdate date
)
WITH (format = 'ORC',
transactional=true)

Create an external table orders:

CREATE TABLE orders (
  orderkey bigint,
  orderstatus varchar,
  totalprice double,
  orderdate date
)
WITH (format = 'ORC',
external=true,
location='hdfs://hdcluster/tmp/externaltbl')

Create the table orders if it does not already exist, adding a table comment and a column comment:

CREATE TABLE IF NOT EXISTS orders (
  orderkey bigint,
  orderstatus varchar,
  totalprice double COMMENT 'Price in cents.',
  orderdate date
)
COMMENT 'A table to keep track of orders.'

Create the table bigger_orders using the columns from orders plus additional columns at the start and end:

CREATE TABLE bigger_orders (
  another_orderkey bigint,
  LIKE orders,
  another_orderdate date
)

Limitations

Different connector might support different data type, and different table/column properties. See connector documentation for more details.

Create Table

CREATE TABLE

Synopsis

Description

Examples

Limitations

See Also