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

Note
This page describes the `CREATE TABLE` syntax supported by the Hive query engine. The Trino query engine also supports `CREATE TABLE`, but with different syntax and capabilities.

## Syntax

```sql
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](#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](#supported-table-properties).


## Examples

Create a basic table:

```sql
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:

```sql
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`:

```sql
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):

```sql
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'
);
```

## Copying a Table Schema (CREATE TABLE LIKE)

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.

```sql
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.

## Supported Data Types

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

| Hive Type | CDP Column Type | Notes |
|  --- | --- | --- |
| `STRING`, `VARCHAR(n)`, `CHAR(n)` | `string` | Length constraints are ignored. |
| `BIGINT` | `long` |  |
| `INT`, `INTEGER`, `SMALLINT`, `TINYINT` | `long` |  |
| `DOUBLE`, `FLOAT` | `double` |  |
| `ARRAY<type>` | `array` |  |


## Supported Table Properties

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

| Property | Type | Description |
|  --- | --- | --- |
| `comment` | String | A description of the table. This is equivalent to the table-level `COMMENT` clause. |
| `expire_days` | Integer | The number of days after which data is automatically deleted. Must be a positive integer. |
| `udp.bucket_count` | Integer | The number of buckets for User-Defined Partitioning. Must be a power of 2 between 4 and 4096. |
| `udp.bucketed_on` | String | A comma-separated list of columns to bucket on. Up to 3 columns, which must exist in the table schema and cannot include the `time` column. |


Note
`udp.bucket_count` and `udp.bucketed_on` must be specified together, or neither. For more details on User-Defined Partitioning, see the [User-Defined Partitioning (UDP)](/products/customer-data-platform/data-workbench/queries/sql-reference/udp) page.

## Limitations

### Managed Tables Only

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

### Unsupported Clauses

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`


### CREATE TABLE AS SELECT (CTAS)

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.