CREATE TABLE

description

This statement is used to create table Syntax:

  1.     CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [database.]table_name
  2.     (column_definition1[, column_definition2, ...]
  3.     [, index_definition1[, ndex_definition12,]])
  4.     [ENGINE = [olap|mysql|broker|hive|iceberg]]
  5.     [key_desc]
  6.     [COMMENT "table comment"]
  7.     [partition_desc]
  8.     [distribution_desc]
  9.     [rollup_index]
  10.     [PROPERTIES ("key"="value", ...)]
  11.     [BROKER PROPERTIES ("key"="value", ...)];

  1. column_definition Syntax: col_name col_type [agg_type] [NULL | NOT NULL] [DEFAULT "default_value"] Explain: col_name: Name of column col_type: Type of column

    1.     BOOLEAN(1 Byte)
    2.         Range: {0,1}
    3.     TINYINT(1 Byte)
    4.         Range: -2^7 + 1 ~ 2^7 - 1
    5.     SMALLINT(2 Bytes)
    6.         Range: -2^15 + 1 ~ 2^15 - 1
    7.     INT(4 Bytes)
    8.         Range: -2^31 + 1 ~ 2^31 - 1
    9.     BIGINT(8 Bytes)
    10.         Range: -2^63 + 1 ~ 2^63 - 1
    11.     LARGEINT(16 Bytes)
    12.         Range: -2^127 + 1 ~ 2^127 - 1
    13.     FLOAT(4 Bytes)
    14.         Support scientific notation
    15.     DOUBLE(8 Bytes)
    16.         Support scientific notation
    17.     DECIMAL[(precision, scale)] (16 Bytes)
    18.         Default is DECIMAL(10, 0)
    19.         precision: 1 ~ 27
    20.         scale: 0 ~ 9
    21.         integer part: 1 ~ 18
    22.         fractional part: 0 ~ 9
    23.         Not support scientific notation
    24.     DATE(3 Bytes)
    25.         Range: 0000-01-01 ~ 9999-12-31
    26.     DATETIME(8 Bytes)
    27.         Range: 0000-01-01 00:00:00 ~ 9999-12-31 23:59:59
    28.     CHAR[(length)]
    29.         Fixed length string. Range: 1 ~ 255. Default: 1
    30.     VARCHAR[(length)]
    31.         Variable length string. Range: 1 ~ 65533
    32.     HLL (1~16385 Bytes)
    33.         HLL tpye, No need to specify length.
    34.         This type can only be queried by hll_union_agg, hll_cardinality, hll_hash functions.
    35.     BITMAP
    36.         BITMAP type, No need to specify length. Represent a set of unsigned bigint numbers, the largest element could be 2^64 - 1

    agg_type: Aggregation type. If not specified, the column is key column. Otherwise, the column is value column.

    1. * SUM、MAX、MIN、REPLACE
    2. * HLL_UNION: Only for HLL type
    3. * REPLACE_IF_NOT_NULL: The meaning of this aggregation type is that substitution will occur if and only if the newly imported data is a non-null value. If the newly imported data is null, Doris will still retain the original value. Note: if NOT NULL is specified in the REPLACE_IF_NOT_NULL column when the user creates the table, Doris will convert it to NULL and will not report an error to the user. Users can leverage this aggregate type to achieve importing some of columns .**It should be noted here that the default value should be NULL, not an empty string. If it is an empty string, you should replace it with an empty string**.
    4. * BITMAP_UNION: Only for BITMAP type

    Allow NULL: Default is NOT NULL. NULL value should be represented as \N in load source file. Notice: The origin value of BITMAP_UNION column should be TINYINT, SMALLINT, INT, BIGINT.

  2. index_definition Syntax: INDEX index_name (col_name[, col_name, ...]) [USING BITMAP] COMMENT 'xxxxxx' Explain: index_name: index name col_name: column name Notice: Only support BITMAP index in current version, BITMAP can only apply to single column

  3. ENGINE type Default is olap. Options are: olap, mysql, broker, hive, iceberg

    1. For mysql, properties should include:

      1. PROPERTIES (
      2.     "host" = "mysql_server_host",
      3.     "port" = "mysql_server_port",
      4.     "user" = "your_user_name",
      5.     "password" = "your_password",
      6.     "database" = "database_name",
      7.     "table" = "table_name"
      8. )

    Notice: “table_name” is the real table name in MySQL database. table_name in CREATE TABLE stmt is table is Doris. They can be different or same. MySQL table created in Doris is for accessing data in MySQL database. Doris does not maintain and store any data from MySQL table. 2) For broker, properties should include:

    1.

    PROPERTIES (

    1.  "broker_name" = "broker_name",
    2.  "path" = "file_path1[,file_path2]",
    3.  "column_separator" = "value_separator"
    4.  "line_delimiter" = "value_delimiter"

    )

    BROKER PROPERTIES(

    1.  "username" = "name",
    2.  "password" = "password"

    )

    2.  For different broker, the broker properties are different
    3.  Notice:
    4.  Files name in "path" is separated by ",". If file name includes ",", use "%2c" instead.     If file name includes "%", use "%25" instead.
    5.  Support CSV and Parquet. Support GZ, BZ2, LZ4, LZO(LZOP)

    1. For hive, properties should include:

      1. PROPERTIES (
      2.     "database" = "hive_db_name",
      3.     "table" = "hive_table_name",
      4.     "hive.metastore.uris" = "thrift://127.0.0.1:9083"
      5. )

      “database” is the name of the database corresponding to the hive table, “table” is the name of the hive table, and “hive.metastore.uris” is the hive metastore service address.

    2. For iceberg, properties should include:

      1. PROPERTIES (
      2.     "iceberg.database" = "iceberg_db_name",
      3.     "iceberg.table" = "iceberg_table_name",
      4.     "iceberg.hive.metastore.uris" = "thrift://127.0.0.1:9083",
      5.     "iceberg.catalog.type" = "HIVE_CATALOG"
      6.     )

      database is the name of the database corresponding to Iceberg.
      table is the name of the table corresponding to Iceberg. hive.metastore.uris is the address of the hive metastore service.
      catalog.type defaults to HIVE_CATALOG. Currently, only HIVE_CATALOG is supported, more Iceberg catalog types will be supported later.

  4. key_desc Syntax: key_type(k1[,k2 …]) Explain: Data is order by specified key columns. And has different behaviors for different key desc. AGGREGATE KEY: value columns will be aggregated is key columns are same. UNIQUE KEY: The new incoming rows will replace the old rows if key columns are same. DUPLICATE KEY: All incoming rows will be saved. the default key_type is DUPLICATE KEY, and key columns are first 36 bytes of the columns in define order. If the number of columns in the first 36 is less than 3, the first 3 columns will be used. NOTICE: Except for AGGREGATE KEY, no need to specify aggregation type for value columns.

  5. partition_desc Currently, both RANGE and LIST partitioning methods are supported. 5.1 RANGE partition RANGE Partition has two ways to use: 1) LESS THAN Syntax:

    1.  
    1.  PARTITION BY RANGE (k1, k2, ...)
    2.  (
    3.  PARTITION partition_name1 VALUES LESS THAN MAXVALUE|("value1", "value2", ...),
    4.  PARTITION partition_name2 VALUES LESS THAN MAXVALUE|("value1", "value2", ...)
    5.  ...
    6.  )
    7.  ```

    Explain:

    1.  Use the specified key column and the specified range of values for partitioning.
    2.  1) Partition name only support [A-z0-9_]
    3.  2) Partition key column's type should be:
    4.      TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DATE, DATETIME
    5.  3) The range is [closed, open). And the lower bound of first partition is MIN VALUE of  specified column type.
    6.  4) NULL values should be save in partition which includes MIN VALUE.
    7.  5) Support multi partition columns, the the default partition value is MIN VALUE.

    2）Fixed Range Syntax:

    1.  ```
    2.  PARTITION BY RANGE (k1, k2, k3, ...)
    3.  (
    4.  PARTITION partition_name1 VALUES [("k1-lower1", "k2-lower1", "k3-lower1",...),  ("k1-upper1", "k2-upper1", "k3-upper1", ...)),
    5.  PARTITION partition_name2 VALUES [("k1-lower1-2", "k2-lower1-2", ...), ("k1-upper1-2",  MAXVALUE, ))
    6.  "k3-upper1-2", ...
    7.  )
    8.  ```

    Explain:

    1.  1）The Fixed Range is more flexible than the LESS THAN, and the left and right intervals    are completely determined by the user.
    2.  2）Others are consistent with LESS THAN.
    2. 5.2 LIST partition LIST partition is divided into single column partition and multi-column partition 1) Single column partition Syntax.
    1.  ```
    2.      PARTITION BY LIST(k1)
    3.      (
    4.      PARTITION partition_name1 VALUES IN ("value1", "value2", ...) ,
    5.      PARTITION partition_name2 VALUES IN ("value1", "value2", ...)
    6.      ...
    7.      )
    8.  ```
    10.  Explain:
    11.      Use the specified key column and the formulated enumeration value for partitioning.
    12.      1) Partition name only support [A-z0-9_]
    13.      2) Partition key column's type should be:
    14.          BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DATE, DATETIME, CHAR, VARCHAR
    15.      3) Partition is a collection of enumerated values, partition values cannot be duplicated between partitions
    16.      4) NULL values cannot be imported
    17.      5) partition values cannot be defaulted, at least one must be specified

    2) Multi-column partition

    1.  Syntax.
    3.  ```
    4.      PARTITION BY LIST(k1, k2)
    5.      (
    6.      PARTITION partition_name1 VALUES IN (("value1", "value2"), ("value1", "value2"), ...) ,
    7.      PARTITION partition_name2 VALUES IN (("value1", "value2"), ("value1", "value2"), ...)
    8.      ...
    9.      )
    10.  ```
    12.  Explain:
    13.      1) the partition of a multi-column partition is a collection of tuple enumeration values
    14.      2) The number of tuple values per partition must be equal to the number of columns in the partition
    15.      3) The other partitions are synchronized with the single column partition

    ```

  6. distribution_desc

    1. Hash Syntax: DISTRIBUTED BY HASH (k1[,k2 ...]) [BUCKETS num] Explain: The default buckets is 10.

  7. PROPERTIES

    1. If ENGINE type is olap. User can specify storage medium, cooldown time and replication number:

      1. PROPERTIES (
      2.     "storage_medium" = "[SSD|HDD]",
      3.     ["storage_cooldown_time" = "yyyy-MM-dd HH:mm:ss"],
      4.     ["replication_num" = "3"],
      5.     ["replication_allocation" = "xxx"]
      6.     )

      storage_medium: SSD or HDD, The default initial storage media can be specified by default_storage_medium= XXX in the fe configuration file fe.conf, or, if not, by default, HDD. Note: when FE configuration ‘enable_strict_storage_medium_check’ is’ True ‘, if the corresponding storage medium is not set in the cluster, the construction clause ‘Failed to find enough host in all backends with storage medium is SSD|HDD’. storage_cooldown_time: If storage_medium is SSD, data will be automatically moved to HDD when timeout. Default is 30 days. Format: “yyyy-MM-dd HH:mm:ss” replication_num: Replication number of a partition. Default is 3. replication_allocation: Specify the distribution of replicas according to the resource tag.

      If table is not range partitions. This property takes on Table level. Or it will takes on Partition level. User can specify different properties for different partition by ADD PARTITION or MODIFY PARTITION statements.

    2. If Engine type is olap, user can set bloom filter index for column. Bloom filter index will be used when query contains IN or EQUAL. Bloom filter index support key columns with type except TINYINT FLOAT DOUBLE, also support value with REPLACE aggregation type.

      1. PROPERTIES (
      2.     "bloom_filter_columns"="k1,k2,k3"
      3. )

    3. For Colocation Join:

      1. PROPERTIES (
      2.     "colocate_with"="table1"
      3. )

    4. if you want to use the dynamic partitioning feature, specify it in properties. Note: Dynamic partitioning only supports RANGE partitions

      1. PROPERTIES (
      2.     "dynamic_partition.enable" = "true|false",
      3.     "dynamic_partition.time_unit" = "HOUR|DAY|WEEK|MONTH",
      4.     "dynamic_partition.end" = "${integer_value}",
      5.     "dynamic_partition.prefix" = "${string_value}",
      6.     "dynamic_partition.buckets" = "${integer_value}
      7. )

      dynamic_partition.enable: specifies whether dynamic partitioning at the table level is enabled dynamic_partition.time_unit: used to specify the time unit for dynamically adding partitions, which can be selected as HOUR, DAY, WEEK, and MONTH. Attention: When the time unit is HOUR, the data type of partition column cannot be DATE. dynamic_partition.end: used to specify the number of partitions created in advance dynamic_partition.prefix: used to specify the partition name prefix to be created, such as the partition name prefix p, automatically creates the partition name p20200108 dynamic_partition.buckets: specifies the number of partition buckets that are automatically created dynamic_partition.create_history_partition: specifies whether create history partitions, default value is false dynamic_partition.history_partition_num: used to specify the number of history partitions when enable create_history_partition dynamic_partition.reserved_history_periods: Used to specify the range of reserved history periods

    5. You can create multiple Rollups in bulk when building a table grammar:

    1.   ROLLUP (rollup_name (column_name1, column_name2, ...)
    2.                  [FROM from_index_name]
    3.                   [PROPERTIES ("key"="value", ...)],...)

    1. if you want to use the inmemory table feature, specify it in properties

      1. PROPERTIES (
      2.    "in_memory"="true"
      3. )

example

  1. Create an olap table, distributed by hash, with aggregation type.

    1. CREATE TABLE example_db.table_hash
    2. (
    3. k1 BOOLEAN,
    4. k2 TINYINT,
    5. k3 DECIMAL(10, 2) DEFAULT "10.5",
    6. v1 CHAR(10) REPLACE,
    7. v2 INT SUM
    8. )
    9. ENGINE=olap
    10. AGGREGATE KEY(k1, k2, k3)
    11. COMMENT "my first doris table"
    12. DISTRIBUTED BY HASH(k1) BUCKETS 32;

  2. Create an olap table, distributed by hash, with aggregation type. Also set storage medium and cooldown time.

    1. CREATE TABLE example_db.table_hash
    2. (
    3. k1 BIGINT,
    4. k2 LARGEINT,
    5. v1 VARCHAR(2048) REPLACE,
    6. v2 SMALLINT SUM DEFAULT "10"
    7. )
    8. ENGINE=olap
    9. AGGREGATE KEY(k1, k2)
    10. DISTRIBUTED BY HASH (k1, k2) BUCKETS 32
    11. PROPERTIES(
    12. "storage_medium" = "SSD",
    13. "storage_cooldown_time" = "2015-06-04 00:00:00"
    14. );

  3. Create an olap table, with range partitioned, distributed by hash. Records with the same key exist at the same time, set the initial storage medium and cooling time, use default column storage.

  4. LESS THAN

    1. CREATE TABLE example_db.table_range
    2. (
    3. k1 DATE,
    4. k2 INT,
    5. k3 SMALLINT,
    6. v1 VARCHAR(2048),
    7. v2 DATETIME DEFAULT "2014-02-04 15:36:00"
    8. )
    9. ENGINE=olap
    10. DUPLICATE KEY(k1, k2, k3)
    11. PARTITION BY RANGE (k1)
    12. (
    13. PARTITION p1 VALUES LESS THAN ("2014-01-01"),
    14. PARTITION p2 VALUES LESS THAN ("2014-06-01"),
    15. PARTITION p3 VALUES LESS THAN ("2014-12-01")
    16. )
    17. DISTRIBUTED BY HASH(k2) BUCKETS 32
    18. PROPERTIES(
    19. "storage_medium" = "SSD", "storage_cooldown_time" = "2015-06-04 00:00:00"
    20. );

    Explain: This statement will create 3 partitions:

    1. ( {    MIN     },   {"2014-01-01"} )
    2. [ {"2014-01-01"},   {"2014-06-01"} )
    3. [ {"2014-06-01"},   {"2014-12-01"} )

    Data outside these ranges will not be loaded.

  5. Fixed Range

    1. CREATE TABLE table_range
    2. (
    3. k1 DATE,
    4. k2 INT,
    5. k3 SMALLINT,
    6. v1 VARCHAR(2048),
    7. v2 DATETIME DEFAULT "2014-02-04 15:36:00"
    8. )
    9. ENGINE=olap
    10. DUPLICATE KEY(k1, k2, k3)
    11. PARTITION BY RANGE (k1, k2, k3)
    12. (
    13. PARTITION p1 VALUES [("2014-01-01", "10", "200"), ("2014-01-01", "20", "300")),
    14. PARTITION p2 VALUES [("2014-06-01", "100", "200"), ("2014-07-01", "100", "300"))
    15. )
    16. DISTRIBUTED BY HASH(k2) BUCKETS 32
    17. PROPERTIES(
    18. "storage_medium" = "SSD"
    19. );

  6. Create an olap table, with list partitioned, distributed by hash. Records with the same key exist at the same time, set the initial storage medium and cooling time, use default column storage.

    1. Single column partition
    1. CREATE TABLE example_db.table_list
    2. (
    3. k1 INT,
    4. k2 VARCHAR(128),
    5. k3 SMALLINT,
    6. v1 VARCHAR(2048),
    7. v2 DATETIME DEFAULT "2014-02-04 15:36:00"
    8. )
    9. ENGINE=olap
    10. DUPLICATE KEY(k1, k2, k3)
    11. PARTITION BY LIST (k1)
    12. (
    13. PARTITION p1 VALUES IN ("1", "2", "3"),
    14. PARTITION p2 VALUES IN ("4", "5", "6"),
    15. PARTITION p3 VALUES IN ("7", "8", "9")
    16. )
    17. DISTRIBUTED BY HASH(k2) BUCKETS 32
    18. PROPERTIES(
    19. "storage_medium" = "SSD", "storage_cooldown_time" = "2022-06-04 00:00:00"
    20. );

    Explain: This statement will divide the data into 3 partitions as follows.

    1. ("1", "2", "3")
    2. ("4", "5", "6")
    3. ("7", "8", "9")

    Data that does not fall within these partition enumeration values will be filtered as illegal data

    1. Multi-column partition
    1. CREATE TABLE example_db.table_list
    2. (
    3. k1 INT,
    4. k2 VARCHAR(128),
    5. k3 SMALLINT,
    6. v1 VARCHAR(2048),
    7. v2 DATETIME DEFAULT "2014-02-04 15:36:00"
    8. )
    9. ENGINE=olap
    10. DUPLICATE KEY(k1, k2, k3)
    11. PARTITION BY LIST (k1, k2)
    12. (
    13. PARTITION p1 VALUES IN (("1", "beijing"), ("1", "shanghai")),
    14. PARTITION p2 VALUES IN (("2", "beijing"), ("2", "shanghai")),
    15. PARTITION p3 VALUES IN (("3", "beijing"), ("3", "shanghai"))
    16. )
    17. DISTRIBUTED BY HASH(k2) BUCKETS 32
    18. PROPERTIES(
    19. "storage_medium" = "SSD", "storage_cooldown_time" = "2022-06-04 00:00:00"
    20. );

    Explain: This statement will divide the data into 3 partitions as follows.

    1. (("1", "beijing"), ("1", "shanghai"))
    2. (("2", "beijing"), ("2", "shanghai"))
    3. (("3", "beijing"), ("3", "shanghai"))

    Data that is not within these partition enumeration values will be filtered as illegal data

  7. Create a mysql table 5.1 Create MySQL table directly from external table information

  1.     CREATE EXTERNAL TABLE example_db.table_mysql
  2.     (
  3.     k1 DATE,
  4.     k2 INT,
  5.     k3 SMALLINT,
  6.     k4 VARCHAR(2048),
  7.     k5 DATETIME
  8.     )
  9.     ENGINE=mysql
  10.     PROPERTIES
  11.     (
  12.     "host" = "127.0.0.1",
  13.     "port" = "8239",
  14.     "user" = "mysql_user",
  15.     "password" = "mysql_passwd",
  16.     "database" = "mysql_db_test",
  17.     "table" = "mysql_table_test"
  18.     )

5.2 Create MySQL table with external ODBC catalog resource

  1.    CREATE EXTERNAL RESOURCE "mysql_resource" 
  2.    PROPERTIES
  3.    (
  4.      "type" = "odbc_catalog",
  5.      "user" = "mysql_user",
  6.      "password" = "mysql_passwd",
  7.      "host" = "127.0.0.1",
  8.      "port" = "8239"            
  9.    );
  1.     CREATE EXTERNAL TABLE example_db.table_mysql
  2.     (
  3.     k1 DATE,
  4.     k2 INT,
  5.     k3 SMALLINT,
  6.     k4 VARCHAR(2048),
  7.     k5 DATETIME
  8.     )
  9.     ENGINE=mysql
  10.     PROPERTIES
  11.     (
  12.     "odbc_catalog_resource" = "mysql_resource",
  13.     "database" = "mysql_db_test",
  14.     "table" = "mysql_table_test"
  15.     )

  1. Create a broker table, with file on HDFS, line delimit by “|”, column separated by “\n”

    1. CREATE EXTERNAL TABLE example_db.table_broker (
    2. k1 DATE,
    3. k2 INT,
    4. k3 SMALLINT,
    5. k4 VARCHAR(2048),
    6. k5 DATETIME
    7. )
    8. ENGINE=broker
    9. PROPERTIES (
    10. "broker_name" = "hdfs",
    11. "path" = "hdfs://hdfs_host:hdfs_port/data1,hdfs://hdfs_host:hdfs_port/data2,hdfs://hdfs_host:hdfs_port/data3%2c4",
    12. "column_separator" = "|",
    13. "line_delimiter" = "\n"
    14. )
    15. BROKER PROPERTIES (
    16. "username" = "hdfs_user",
    17. "password" = "hdfs_password"
    18. );

  2. Create table will HLL column

    1. CREATE TABLE example_db.example_table
    2. (
    3. k1 TINYINT,
    4. k2 DECIMAL(10, 2) DEFAULT "10.5",
    5. v1 HLL HLL_UNION,
    6. v2 HLL HLL_UNION
    7. )
    8. ENGINE=olap
    9. AGGREGATE KEY(k1, k2)
    10. DISTRIBUTED BY HASH(k1) BUCKETS 32;

  3. Create a table will BITMAP_UNION column

    1. CREATE TABLE example_db.example_table
    2. (
    3. k1 TINYINT,
    4. k2 DECIMAL(10, 2) DEFAULT "10.5",
    5. v1 BITMAP BITMAP_UNION,
    6. v2 BITMAP BITMAP_UNION
    7. )
    8. ENGINE=olap
    9. AGGREGATE KEY(k1, k2)
    10. DISTRIBUTED BY HASH(k1) BUCKETS 32;

  4. Create 2 colocate join table.

    1. CREATE TABLE `t1` (
    2. `id` int(11) COMMENT "",
    3. `value` varchar(8) COMMENT ""
    4. ) ENGINE=OLAP
    5. DUPLICATE KEY(`id`)
    6. DISTRIBUTED BY HASH(`id`) BUCKETS 10
    7. PROPERTIES (
    8. "colocate_with" = "group1"
    9. );
    10. CREATE TABLE `t2` (
    11. `id` int(11) COMMENT "",
    12. `value` varchar(8) COMMENT ""
    13. ) ENGINE=OLAP
    14. DUPLICATE KEY(`id`)
    15. DISTRIBUTED BY HASH(`id`) BUCKETS 10
    16. PROPERTIES (
    17. "colocate_with" = "group1"
    18. );

  5. Create a broker table, with file on BOS.

    1. CREATE EXTERNAL TABLE example_db.table_broker (
    2. k1 DATE
    3. )
    4. ENGINE=broker
    5. PROPERTIES (
    6. "broker_name" = "bos",
    7. "path" = "bos://my_bucket/input/file",
    8. )
    9. BROKER PROPERTIES (
    10.  "bos_endpoint" = "http://bj.bcebos.com",
    11.  "bos_accesskey" = "xxxxxxxxxxxxxxxxxxxxxxxxxx",
    12.  "bos_secret_accesskey"="yyyyyyyyyyyyyyyyyyyy"
    13. );

  6. Create a table with a bitmap index

    1. CREATE TABLE example_db.table_hash
    2. (
    3. k1 TINYINT,
    4. k2 DECIMAL(10, 2) DEFAULT "10.5",
    5. v1 CHAR(10) REPLACE,
    6. v2 INT SUM,
    7. INDEX k1_idx (k1) USING BITMAP COMMENT 'xxxxxx'
    8. )
    9. ENGINE=olap
    10. AGGREGATE KEY(k1, k2)
    11. COMMENT "my first doris table"
    12. DISTRIBUTED BY HASH(k1) BUCKETS 32;

  7. Create a dynamic partitioning table (dynamic partitioning needs to be enabled in FE configuration), which creates partitions 3 days in advance every day. For example, if today is’ 2020-01-08 ‘, partitions named ‘p20200108’, ‘p20200109’, ‘p20200110’, ‘p20200111’ will be created.

    1. [types: [DATE]; keys: [2020-01-08]; ‥types: [DATE]; keys: [2020-01-09]; )
    2. [types: [DATE]; keys: [2020-01-09]; ‥types: [DATE]; keys: [2020-01-10]; )
    3. [types: [DATE]; keys: [2020-01-10]; ‥types: [DATE]; keys: [2020-01-11]; )
    4. [types: [DATE]; keys: [2020-01-11]; ‥types: [DATE]; keys: [2020-01-12]; )
    1.   CREATE TABLE example_db.dynamic_partition
    2.   (
    3.   k1 DATE,
    4.   k2 INT,
    5.   k3 SMALLINT,
    6.   v1 VARCHAR(2048),
    7.   v2 DATETIME DEFAULT "2014-02-04 15:36:00"
    8.   )
    9.   ENGINE=olap
    10.   DUPLICATE KEY(k1, k2, k3)
    11.   PARTITION BY RANGE (k1) ()
    12.   DISTRIBUTED BY HASH(k2) BUCKETS 32
    13.   PROPERTIES(
    14.   "storage_medium" = "SSD",
    15.   "dynamic_partition.time_unit" = "DAY",
    16.   "dynamic_partition.end" = "3",
    17.   "dynamic_partition.prefix" = "p",
    18.   "dynamic_partition.buckets" = "32"
    19.    );

  8. Create a table with rollup index

  1.     CREATE TABLE example_db.rolup_index_table
  2.     (
  3.         event_day DATE,
  4.         siteid INT DEFAULT '10',
  5.         citycode SMALLINT,
  6.         username VARCHAR(32) DEFAULT '',
  7.         pv BIGINT SUM DEFAULT '0'
  8.     )
  9.     AGGREGATE KEY(event_day, siteid, citycode, username)
  10.     DISTRIBUTED BY HASH(siteid) BUCKETS 10
  11.     rollup (
  12.     r1(event_day,siteid),
  13.     r2(event_day,citycode),
  14.     r3(event_day)
  15.     )
  16.     PROPERTIES("replication_num" = "3");
  1. Create a inmemory table:
  1.     CREATE TABLE example_db.table_hash
  2.     (
  3.     k1 TINYINT,
  4.     k2 DECIMAL(10, 2) DEFAULT "10.5",
  5.     v1 CHAR(10) REPLACE,
  6.     v2 INT SUM,
  7.     INDEX k1_idx (k1) USING BITMAP COMMENT 'xxxxxx'
  8.     )
  9.     ENGINE=olap
  10.     AGGREGATE KEY(k1, k2)
  11.     COMMENT "my first doris table"
  12.     DISTRIBUTED BY HASH(k1) BUCKETS 32
  13.     PROPERTIES ("in_memory"="true");
  1. Create a hive external table
  1.     CREATE TABLE example_db.table_hive
  2.     (
  3.       k1 TINYINT,
  4.       k2 VARCHAR(50),
  5.       v INT
  6.     )
  7.     ENGINE=hive
  8.     PROPERTIES
  9.     (
  10.       "database" = "hive_db_name",
  11.       "table" = "hive_table_name",
  12.       "hive.metastore.uris" = "thrift://127.0.0.1:9083"
  13.     );
  1. Specify the replica distribution of the table through replication_allocation
  1.     CREATE TABLE example_db.table_hash
  2.     (
  3.     k1 TINYINT,
  4.     k2 DECIMAL(10, 2) DEFAULT "10.5"
  5.     )
  6.     DISTRIBUTED BY HASH(k1) BUCKETS 32
  7.     PROPERTIES (
  8.         "replication_allocation"="tag.location.group_a:1, tag.location.group_b:2"
  9.     );
  11.     CREATE TABLE example_db.dynamic_partition
  12.     (
  13.     k1 DATE,
  14.     k2 INT,
  15.     k3 SMALLINT,
  16.     v1 VARCHAR(2048),
  17.     v2 DATETIME DEFAULT "2014-02-04 15:36:00"
  18.     )
  19.     PARTITION BY RANGE (k1) ()
  20.     DISTRIBUTED BY HASH(k2) BUCKETS 32
  21.     PROPERTIES(
  22.     "dynamic_partition.time_unit" = "DAY",
  23.     "dynamic_partition.start" = "-3",
  24.     "dynamic_partition.end" = "3",
  25.     "dynamic_partition.prefix" = "p",
  26.     "dynamic_partition.buckets" = "32",
  27.     "dynamic_partition."replication_allocation" = "tag.location.group_a:3"
  28.      );
  1. Create an Iceberg external table
  1.     CREATE TABLE example_db.t_iceberg 
  2.     ENGINE=ICEBERG
  3.     PROPERTIES (
  4.     "iceberg.database" = "iceberg_db",
  5.     "iceberg.table" = "iceberg_table",
  6.     "iceberg.hive.metastore.uris"  =  "thrift://127.0.0.1:9083",
  7.     "iceberg.catalog.type"  =  "HIVE_CATALOG"
  8.     );

keyword

  1. CREATE,TABLE