CREATE TABLE
This section describes the basic SQL syntax and usage of Doris for creating tables.
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]
Use Example
- To create an ordinary table named table1, run the following command:
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 as the partition column and create three partitions: 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 uses siteid for hash bucketing. The number of buckets is 10.
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" = "1");
- When Doris creates a table, at least two copies must be specified to ensure high availability.
- You can add a rollup table to a table to improve query performance.
- By default, the Null attribute of a column in a table is true, which affects the query performance.
- The bucket column must be specified for the Doris table.
- View the table content.
- SHOW TABLES;
+----------------------+ | Tables_in_example_db | +----------------------+ | table1 | | table2 | +----------------------+ 2 rows in set (0.01 sec)
- 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)
- 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)
- SHOW TABLES;
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.