Help Center/
CloudTable Service/
User Guide/
Using Doris/
Common SQL Commands of Doris/
Creating a Table
Updated on 2025-07-24 GMT+08:00
Creating a Table
This section describes the basic syntax and usage of the SQL statements for creating a table in a Doris cluster.
Basic Syntax
CREATE TABLE [IF NOT EXISTS] [database.]table ( column_definition_list, [index_definition_list] ) [engine_type] [keys_type] [table_comment] [partition_info] distribution_desc [rollup_list] [properties] [extra_properties]
Usage Example
- Create a common table named table1.
CREATE TABLE example_db.table1 ( k1 TINYINT, k2 DECIMAL(10, 2) DEFAULT "10.5", k3 CHAR(10) COMMENT "string column", k4 INT NOT NULL DEFAULT "1" COMMENT "int column" ) COMMENT "table comment" DISTRIBUTED BY HASH(k1) BUCKETS 32;
- Create a partitioned table named table2. Use the event_day column to partition the table into p201706, p201707, and p201708. The values are as follows:
- p201706: The value range is [Minimum value, 2017-07-01).
- p201707: The value range is [2017-07-01, 2017-08-01).
- p201708: The value range is [2017-08-01, 2017-09-01).
Each partition is hashed into 10 buckets based on siteid. The command for creating a table is as follows:
CREATE TABLE table2 ( event_day DATE, siteid INT DEFAULT '10', citycode SMALLINT, username VARCHAR(32) DEFAULT '', pv BIGINT SUM DEFAULT '0' ) AGGREGATE KEY(event_day, siteid, citycode, username) PARTITION BY RANGE(event_day) ( PARTITION p201706 VALUES LESS THAN ('2017-07-01'), PARTITION p201707 VALUES LESS THAN ('2017-08-01'), PARTITION p201708 VALUES LESS THAN ('2017-09-01') ) DISTRIBUTED BY HASH(siteid) BUCKETS 10 PROPERTIES("replication_num" = "3");
- You must specify at least three replicas when creating a table to ensure high availability.
- For a non-node cluster, you do not need to specify replicas.
- You can add a rollup to a table to improve query performance.
- By default, the Null property of a column in a table is true, which affects the query performance.
- The bucket column must be specified for a Doris table.
- View the table content.
- View tables in the current database.
SHOW TABLES;
+----------------------+ | Tables_in_example_db | +----------------------+ | table1 | | table2 | +----------------------+ 2 rows in set (0.01 sec)
- View the structure of table1.
DESC table1;
+----------+-------------+------+-------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-------+---------+-------+ | siteid | int(11) | Yes | true | 10 | | | citycode | smallint(6) | Yes | true | N/A | | | username | varchar(32) | Yes | true | | | | pv | bigint(20) | Yes | false | 0 | SUM | +----------+-------------+------+-------+---------+-------+ 4 rows in set (0.00 sec)
- View the structure of table2.
DESC table2;
+-----------+-------------+------+-------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-------+---------+-------+ | event_day | date | Yes | true | N/A | | | siteid | int(11) | Yes | true | 10 | | | citycode | smallint(6) | Yes | true | N/A | | | username | varchar(32) | Yes | true | | | | pv | bigint(20) | Yes | false | 0 | SUM | +-----------+-------------+------+-------+---------+-------+ 5 rows in set (0.00 sec)
- View tables in the current database.
Parent topic: Common SQL Commands of Doris
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
The system is busy. Please try again later.
For any further questions, feel free to contact us through the chatbot.
Chatbot