Updated on 2024-10-09 GMT+08:00

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)