CREATE TABLE statement

The CREATE TABLE statement defines a table. The definition must include its name and the names and attributes of its columns.

The Hive catalog supports two different types of tables:

Managed : Indicates that the data in the table is owned and managed by the database manager. If you drop the table, the table definition and the data are removed from both the database manager and Hive catalogs.
External : Indicates that the data in the table is not managed by the database manager. If you drop the table, the table definition is removed from both the database manager and Hive catalogs, but the data remains unaffected.

Invocation

This statement can be embedded in an application program or issued by using dynamic SQL statements.

Syntax

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 [, ...] ) ]

Syntax Diagram

For information on reading syntax diagrams, see How to read syntax diagrams

Description

IF NOT EXISTS

Specifies that an error message is suppressed when the table cannot be created because a table with the specified name exists in the current database and schema.

table_name

Names the table. The name, including the implicit or explicit qualifier, must not identify a table, view, nickname, or alias described in the catalog.

Note: The following conditions apply for table_name:

table_name with square brackets '[]' must contain some character inside it. For example: 'p![ab]&'.
table_name with open square bracket '[' must also contain a closed square bracket ']'.
table_name with only closed square bracket ']' is valid.

column-definition

Defines the attributes of a column.

column_name: Names a column of the table. The name cannot be qualified, and the same name cannot be used for more than one column of the table.
data_type: Specifies the data type of the column.

DATA TYPE

LIKE existing_table_name [ { INCLUDING | EXCLUDING } PROPERTIES ]

Specifies that the columns of the table have the same name and description as the columns of the specified table (existing_table_name). The specified table must either exist in the catalog or must be a declared temporary table.

Multiple LIKE clauses can be specified, which allows copying the columns from multiple tables.

If INCLUDING PROPERTIES is specified, all the table properties are copied to the new table. The default behavior is EXCLUDING PROPERTIES. The INCLUDING PROPERTIES option must be specified for at most one table.

WITH property_name = expression [, ...]

Sets properties on the newly created table or on single columns.

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

SELECT * FROM system.metadata.table_properties

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

SELECT * FROM system.metadata.column_properties

Note: Currently, no column properties are supported.

Note: If the WITH clause specifies the same property name as one of the copied properties, the value from the WITH clause is used.

Examples

Create the table ORDERS.

CREATE TABLE ORDERS (
  ORDERKEY bigint,
  STATUS varchar,
  PRICE double,
  DATE date
)
WITH (format = 'ORC')

Create the table ORDERS if it does not exist, adding a table comment and a column comment.

CREATE TABLE IF NOT EXISTS ORDERS (
  ORDERKEY bigint,
  STATUS varchar,
  PRICE double COMMENT 'Price in cents.',
  DATE date
)
COMMENT 'A table to keep track of orders.'

Create the table EXPAND_ORDERS by using the columns from ORDERS with more columns at the start and end.

CREATE TABLE EXPAND_ORDERS (
  NEW_ORDERKEY bigint,
  LIKE orders,
  NEW_DATE date
)