CREATE TABLE Statement Syntax for Hive

A CREATE TABLE statement defines a new table with a specified schema in a Treasure Data CDP database. Data engineers use it to create tables directly from HiveQL — for example, before running an ingestion workflow or a scheduled query — without switching to Trino.

CREATE TABLE [IF NOT EXISTS] [<database_name>.]<table_name> (
  <column_name> <data_type> [COMMENT '<column_comment>'],
  ...
)
[COMMENT '<table_comment>']
[TBLPROPERTIES ('<key>' = '<value>', ...)]

Where:

• <table_name> is the name of the table to create.
• <data_type> is the data type of the column. See Supported Data Types.
• IF NOT EXISTS suppresses the error that is raised when a table with the same name already exists.
• COMMENT sets an optional description for a column or the table.
• TBLPROPERTIES sets optional table properties. See Supported Table Properties.

Create a basic table:

CREATE TABLE my_database.users (
  user_id BIGINT,
  name STRING,
  score DOUBLE
);

Create a table only if it does not already exist, with column and table comments:

CREATE TABLE IF NOT EXISTS users (
  user_id BIGINT COMMENT 'Unique identifier',
  email STRING COMMENT 'User email address'
)
COMMENT 'User master table';

Set a data retention period with expire_days:

CREATE TABLE user_events (
  user_id BIGINT,
  event_type STRING,
  created_at BIGINT
)
TBLPROPERTIES (
  'comment' = 'User event tracking table',
  'expire_days' = '90'
);

Distribute data with User-Defined Partitioning (UDP):

CREATE TABLE user_events (
  user_id BIGINT,
  session_id STRING,
  event_type STRING
)
TBLPROPERTIES (
  'udp.bucket_count' = '128',
  'udp.bucketed_on' = 'user_id,session_id'
);

You can create a new table that copies the schema of an existing table with the LIKE clause. Only the table structure is copied; no data is copied.

CREATE TABLE [IF NOT EXISTS] <new_table_name> LIKE <source_table_name>;

The column definitions of the source table are copied to the new table, along with table properties such as expire_days and User-Defined Partitioning (UDP) configuration. The source table must already exist; otherwise the statement fails with an error.

Hive data types are mapped to Treasure Data CDP column types as follows. Types that are not listed are not supported.

Only the following properties are processed. All other properties are silently ignored.

CREATE TABLE creates a managed table. Creating an external table with the EXTERNAL clause is not supported.

The following clauses are not supported and are rejected with an error:

• TEMPORARY
• EXTERNAL
• PARTITIONED BY
• CLUSTERED BY / SORTED BY / INTO ... BUCKETS
• ROW FORMAT
• STORED AS
• LOCATION

Creating a table from the result of a query with CREATE TABLE AS SELECT is not supported. Support for CTAS is planned for a future release.