StarRocks创建表

create table 语法

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [database.]table_name
(column_definition1[, column_definition2, ...]
[, index_definition1[, ndex_definition12,]])
[ENGINE = [olap|mysql|broker|hive]]
[key_desc]
[COMMENT "table comment"];
[partition_desc]
[distribution_desc]
[rollup_index]
[PROPERTIES ("key"="value", ...)]
[BROKER PROPERTIES ("key"="value", ...)]

注解 一：column_definition

(column_definition1[, column_definition2, …]
语法：col_name col_type [agg_type] [NULL | NOT NULL] [DEFAULT "default_value"]
col_name：列名称

col_type：列类型

agg_type：聚合类型，如果不指定，则该列为 key 列。否则，该列为 value 列

1. SUM、MAX、MIN、REPLACE
2. HLL_UNION(仅用于HLL列，为HLL独有的聚合方式)、
3. BITMAP_UNION(仅用于 BITMAP 列，为 BITMAP 独有的聚合方式)、
4. REPLACE_IF_NOT_NULL：这个聚合类型的含义是当且仅当新导入数据是非NULL值时会发生替换行为，如果新导入的数据是NULL，那么StarRocks仍然会保留原值。注意：如果用在建表时REPLACE_IF_NOT_NULL列指定了NOT NULL，那么StarRocks仍然会将其转化NULL，不会向用户报错。用户可以借助这个类型完成部分列导入的功能。 * 该类型只对聚合模型(key_desc的type为AGGREGATE KEY)有用，其它模型不需要指这个。
5. 注意： BITMAP_UNION聚合类型列在导入时的原始数据类型必须是TINYINT,SMALLINT,INT,BIGINT。

是否允许为NULL: 默认不允许为 NULL。NULL 值在导入数据中用 \N 来表示

注解 二：index_definition

[, index_definition1[, ndex_definition12,]])
语法：INDEX index_name (col_name[, col_name, ...]) [USING BITMAP] COMMENT 'xxxxxx'index_name：索引名称
col_name：列名
注意： 当前仅支持BITMAP索引， BITMAP索引仅支持应用于单列

注解三：ENGINE 和 PROPERTIES

[ENGINE = [olap|mysql|broker|hive]] 和 [PROPERTIES (“key”=”value”, …)] 和 [BROKER PROPERTIES (“key”=”value”, …)]
默认为 olap。可选 mysql, broker, hive

1. 如果是mysql则需要在 properties 提供以下信息 ```sql PROPERTIES ( “host” = “mysql_server_host”, “port” = “mysql_server_port”, “user” = “your_user_name”, “password” = “your_password”, “database” = “database_name”, “table” = “table_name” )

—1.”table” 条目中的 “table_name” 是 mysql 中的真实表名。 — 而 CREATE TABLE 语句中的 table_name 是该 mysql 表在 StarRocks 中的名字，可以不同。

—2.在 StarRocks 创建 mysql 表的目的是可以通过 StarRocks 访问 mysql 数据库。 — 而 StarRocks 本身并不维护、存储任何 mysql 数据。

2. 如果是boker则需要在 properties 提供以下信息
```sql
PROPERTIES (
"broker_name" = "broker_name",
"path" = "file_path1[,file_path2]",
"column_separator" = "value_separator"
"line_delimiter" = "value_delimiter"
)
--另外还需要提供Broker需要的Property信息，通过BROKER PROPERTIES来传递，例如HDFS需要传入
BROKER PROPERTIES(
"username" = "name",
"password" = "password"
)
--1. 这个根据不同的Broker类型，需要传入的内容也不相同 
--2. "path" 中如果有多个文件，用逗号[,]分割。
--3. 如果文件名中包含逗号，那么使用 %2c 来替代。
--4. 如果文件名中包含 %，使用 %25 代替 
--5. 现在文件内容格式支持CSV，支持GZ，BZ2，LZ4，LZO(LZOP) 压缩格式。

1. 如果是hive则需要在 properties 提供以下信息 ```sql PROPERTIES ( “database” = “hive_db_name”, “table” = “hive_table_name”, “hive.metastore.uris” = “thrift://127.0.0.1:9083” )

—1. database 是 hive 表对应的库名字， —2. table 是 hive 表的名字， —3. hive.metastore.uris 是 hive metastore 服务地址。 —4. 目前hive外部表仅用于Spark Load使用，不支持查询。

<a name="VuZh6"></a>
#### 注解四：key_desc
[key_desc]<br />语法：`key_type(k1[,k2 ...])`<br />数据按照指定的key列进行排序，且根据不同的key_type具有不同特性。 <br />key_type支持以下类型： 
| key_type | 解释 | 使用场景 |
| --- | --- | --- |
| AGGREGATE KEY | key列相同的记录，value列按照指定的聚合类型进行聚合 | 适合报表、多维分析等业务场景 |
| UNIQUE KEY | key列相同的记录，value列按导入顺序进行覆盖 | 适合按key列进行增删改查的点查询业务 |
| DUPLICATE KEY | key列相同的记录，同时存在于StarRocks中 | 适合存储明细数据或者数据无聚合特性的业务场景 |
 默认为DUPLICATE KEY，key列为列定义中前36个字节, 如果前36个字节的列数小于3，将使用前三列。 <br />注意： 除AGGREGATE KEY外，其他key_type在建表时，value列不需要指定聚合类型。<br />[有关模型讲解的链接：](https://forum.starrocks.com/t/topic/54)[https://forum.starrocks.com/t/topic/54](https://forum.starrocks.com/t/topic/54)
<a name="LSmwc"></a>
#### 注解五：partition_desc
[partition_desc]<br />partition描述有两种使用方式
1. LESS THAN
```sql
PARTITION BY RANGE (k1, k2, ...)
(
PARTITION partition_name1 VALUES LESS THAN MAXVALUE|("value1", "value2", ...),
PARTITION partition_name2 VALUES LESS THAN MAXVALUE|("value1", "value2", ...)
...
)
--1. 说明：使用指定的 key 列和指定的数值范围进行分区。
--2. 分区名称仅支持字母开头，字母、数字和下划线组成
--3. 目前仅支持以下类型的列作为 Range 分区列，且只能指定一个分区列 
--     TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DATE, DATETIME
--4. 分区为左闭右开区间，首个分区的左边界为做最小值
--5. NULL 值只会存放在包含最小值的分区中。当包含最小值的分区被删除后，NULL 值将无法导入。
--   可以指定一列或多列作为分区列。如果分区值缺省，则会默认填充最小值。
--6. 分区一般用于时间维度的数据管理有数据回溯需求的，可以考虑首个分区为空分区，以便后续增加分区

1. Fixed Range

   PARTITION BY RANGE (k1, k2, k3, ...)
   (
   PARTITION partition_name1 VALUES [("k1-lower1", "k2-lower1", "k3-lower1",...), ("k1-upper1", "k2-upper1", "k3-upper1", ...)),
   PARTITION partition_name2 VALUES [("k1-lower1-2", "k2-lower1-2", ...), ("k1-upper1-2", MAXVALUE, ))
   "k3-upper1-2", ...
   )
   --1. Fixed Range比LESS THAN相对灵活些，左右区间完全由用户自己确定
   --2. 其他与LESS THAN保持同步

注解六：distribution_desc

[distribution_desc]
Hash 分桶
语法：DISTRIBUTED BY HASH (k1[,k2 ...]) [BUCKETS num]
说明： 使用指定的 key 列进行哈希分桶。默认分区数为10
建议: 建议使用Hash分桶方式

注解七：PROPERTIES

[PROPERTIES (“key”=”value”, …)]

1. 如果 ENGINE 类型为 olap,可以在 properties 设置该表数据的初始存储介质、存储到期时间和副本数。 ```sql PROPERTIES ( “storage_medium” = “[SSD|HDD]”, [“storage_cooldown_time” = “yyyy-MM-dd HH:mm:ss”], [“replication_num” = “3”] )

—1. storage_medium：用于指定该分区的初始存储介质，可选择 SSD 或 HDD。 — 默认初始存储介质可通过fe的配置文件 fe.conf 中指定 default_storage_medium=xxx， — 如果没有指定，则默认为 HDD。

—2. 当FE配置项 enable_strict_storage_medium_check 为 True 时，若集群中没有设置对应的存储介质时， — 建表语句会报错 Failed to find enough host in all backends with storage medium is SSD|HDD.

—3. storage_cooldown_time： 当设置存储介质为 SSD 时，指定该分区在 SSD 上的存储到期时间。 — 默认存放 30 天。 — 格式为：”yyyy-MM-dd HH:mm:ss”

—4. replication_num: 指定分区的副本数。默认为 3

—5. 当表为单分区表时，这些属性为表的属性。 — 当表为两级分区时，这些属性为附属于每一个分区。 — 如果希望不同分区有不同属性,可以通过 ADD PARTITION 或 MODIFY PARTITION 进行操作

2. 如果 Engine 类型为 olap, 可以指定某列使用 bloom filter 索引 
   1. bloom filter 索引仅适用于查询条件为 in 和 equal 的情况，
   1. 该列的值越分散效果越好 
   1. 目前只支持以下情况的列:
      1. 除了 TINYINT FLOAT DOUBLE 类型以外的 key 列
      1. 聚合方法为 REPLACE 的 value 列
```sql
PROPERTIES (
"bloom_filter_columns"="k1,k2,k3"
)

1. 如果希望使用 Colocate Join 特性，需要在 properties 中指定

   PROPERTIES (
   "colocate_with"="table1"
   )

2. 如果希望使用动态分区特性，需要在properties 中指定

   PROPERTIES (
   "dynamic_partition.enable" = "true|false",
   "dynamic_partition.time_unit" = "DAY|WEEK|MONTH",
   "dynamic_partition.start" = "${integer_value}",
   "dynamic_partitoin.end" = "${integer_value}",
   "dynamic_partition.prefix" = "${string_value}",
   "dynamic_partition.buckets" = "${integer_value}"
   )
   --1. dynamic_partition.enable:         用于指定表级别的动态分区功能是否开启。默认为 true。 
   --2. dynamic_partition.time_unit: 用于指定动态添加分区的时间单位，可选择为DAY（天），WEEK(周)，MONTH（月） 
   --3. dynamic_partition.start:         用于指定向前删除多少个分区。值必须小于0。默认为 Integer.MIN_VALUE。 
   --4. dynamic_partition.end:             用于指定提前创建的分区数量。值必须大于0。 
   --5. dynamic_partition.prefix:         用于指定创建的分区名前缀，例如分区名前缀为p，则自动创建分区名为p20200108 
   --6. dynamic_partition.buckets:     用于指定自动创建的分区分桶数量

3. 建表时可以批量创建多个 Rollup

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

4. 如果希望使用 内存表 特性，需要在 properties 中指定

   PROPERTIES (
   "in_memory"="true"
   )
   --1. 当 in_memory 属性为 true 时，StarRocks会尽可能将该表的数据和索引Cache到BE 内存中

   create table 举例

5. 创建一个 olap 表，使用 HASH 分桶，使用列存，相同key的记录进行聚合

   CREATE TABLE example_db.table_hash
   (
   k1 TINYINT,
   k2 DECIMAL(10, 2) DEFAULT "10.5",
   v1 CHAR(10) REPLACE,
   v2 INT SUM
   )
   ENGINE=olap
   AGGREGATE KEY(k1, k2)
   COMMENT "my first starrocks table"
   DISTRIBUTED BY HASH(k1) BUCKETS 32
   PROPERTIES ("storage_type"="column");

6. 创建一个 olap 表，使用 Hash 分桶，使用列存，相同key的记录进行覆盖， 设置初始存储介质和冷却时间

   CREATE TABLE example_db.table_hash
   (
   k1 BIGINT,
   k2 LARGEINT,
   v1 VARCHAR(2048) REPLACE,
   v2 SMALLINT SUM DEFAULT "10"
   )
   ENGINE=olap
   UNIQUE KEY(k1, k2)
   DISTRIBUTED BY HASH (k1, k2) BUCKETS 32
   PROPERTIES(
   "storage_type"="column"，
   "storage_medium" = "SSD",
   "storage_cooldown_time" = "2015-06-04 00:00:00"
   );

7. 创建一个 olap 表，使用 Range 分区，使用Hash分桶，默认使用列存，相同key的记录同时存在，设置初始存储介质和冷却时间

LESS THAN

CREATE TABLE example_db.table_range
(
k1 DATE,
k2 INT,
k3 SMALLINT,
v1 VARCHAR(2048),
v2 DATETIME DEFAULT "2014-02-04 15:36:00"
)
ENGINE=olap
DUPLICATE KEY(k1, k2, k3)
PARTITION BY RANGE (k1)
(
PARTITION p1 VALUES LESS THAN ("2014-01-01"),
PARTITION p2 VALUES LESS THAN ("2014-06-01"),
PARTITION p3 VALUES LESS THAN ("2014-12-01")
)
DISTRIBUTED BY HASH(k2) BUCKETS 32
PROPERTIES(
"storage_medium" = "SSD", "storage_cooldown_time" = "2015-06-04 00:00:00"
);

说明： 这个语句会将数据划分成如下3个分区：

( {    MIN     },   {"2014-01-01"} )
[ {"2014-01-01"},   {"2014-06-01"} )
[ {"2014-06-01"},   {"2014-12-01"} )

不在这些分区范围内的数据将视为非法数据被过滤
Fixed Range

CREATE TABLE table_range
(
k1 DATE,
k2 INT,
k3 SMALLINT,
v1 VARCHAR(2048),
v2 DATETIME DEFAULT "2014-02-04 15:36:00"
)
ENGINE=olap
DUPLICATE KEY(k1, k2, k3)
PARTITION BY RANGE (k1, k2, k3)
(
PARTITION p1 VALUES [("2014-01-01", "10", "200"), ("2014-01-01", "20", "300")),
PARTITION p2 VALUES [("2014-06-01", "100", "200"), ("2014-07-01", "100", "300"))
)
DISTRIBUTED BY HASH(k2) BUCKETS 32
PROPERTIES(
"storage_medium" = "SSD"
);

1. 创建一个 mysql 表

   CREATE TABLE example_db.table_mysql
   (
   k1 DATE,
   k2 INT,
   k3 SMALLINT,
   k4 VARCHAR(2048),
   k5 DATETIME
   )
   ENGINE=mysql
   PROPERTIES
   (
   "host" = "127.0.0.1",
   "port" = "8239",
   "user" = "mysql_user",
   "password" = "mysql_passwd",
   "database" = "mysql_db_test",
   "table" = "mysql_table_test"
   )

2. 创建一个数据文件存储在HDFS上的 broker 外部表, 数据使用 “|” 分割，”\n” 换行

   CREATE EXTERNAL TABLE example_db.table_broker (
   k1 DATE,
   k2 INT,
   k3 SMALLINT,
   k4 VARCHAR(2048),
   k5 DATETIME
   )
   ENGINE=broker
   PROPERTIES (
   "broker_name" = "hdfs",
   "path" = "hdfs://hdfs_host:hdfs_port/data1,hdfs://hdfs_host:hdfs_port/data2,hdfs://hdfs_host:hdfs_port/data3%2c4",
   "column_separator" = "|",
   "line_delimiter" = "\n"
   )
   BROKER PROPERTIES (
   "username" = "hdfs_user",
   "password" = "hdfs_password"
   )

3. 创建一张含有HLL列的表

   CREATE TABLE example_db.example_table
   (
   k1 TINYINT,
   k2 DECIMAL(10, 2) DEFAULT "10.5",
   v1 HLL HLL_UNION,
   v2 HLL HLL_UNION
   )
   ENGINE=olap
   AGGREGATE KEY(k1, k2)
   DISTRIBUTED BY HASH(k1) BUCKETS 32
   PROPERTIES ("storage_type"="column");

4. 创建一张含有BITMAP_UNION聚合类型的表（v1和v2列的原始数据类型必须是TINYINT,SMALLINT,INT）

   CREATE TABLE example_db.example_table
   (
   k1 TINYINT,
   k2 DECIMAL(10, 2) DEFAULT "10.5",
   v1 BITMAP BITMAP_UNION,
   v2 BITMAP BITMAP_UNION
   )
   ENGINE=olap
   AGGREGATE KEY(k1, k2)
   DISTRIBUTED BY HASH(k1) BUCKETS 32
   PROPERTIES ("storage_type"="column");

5. 创建两张支持Colocat Join的表t1 和t2 ``sql CREATE TABLEt1(idint(11) COMMENT "",valuevarchar(8) COMMENT "" ) ENGINE=OLAP DUPLICATE KEY(id) DISTRIBUTED BY HASH(id`) BUCKETS 10 PROPERTIES ( “colocate_with” = “t1” );

CREATE TABLE t2 ( id int(11) COMMENT “”, value varchar(8) COMMENT “” ) ENGINE=OLAP DUPLICATE KEY(id) DISTRIBUTED BY HASH(id) BUCKETS 10 PROPERTIES ( “colocate_with” = “t1” );

9. 创建一个数据文件存储在BOS上的 broker 外部表
```sql
CREATE EXTERNAL TABLE example_db.table_broker (
k1 DATE
)
ENGINE=broker
PROPERTIES (
"broker_name" = "bos",
"path" = "bos://my_bucket/input/file",
)
BROKER PROPERTIES (
"bos_endpoint" = "http://bj.bcebos.com",
"bos_accesskey" = "xxxxxxxxxxxxxxxxxxxxxxxxxx",
"bos_secret_accesskey"="yyyyyyyyyyyyyyyyyyyy"
)

1. 创建一个带有bitmap 索引的表

   CREATE TABLE example_db.table_hash
   (
   k1 TINYINT,
   k2 DECIMAL(10, 2) DEFAULT "10.5",
   v1 CHAR(10) REPLACE,
   v2 INT SUM,
   INDEX k1_idx (k1) USING BITMAP COMMENT 'xxxxxx'
   )
   ENGINE=olap
   AGGREGATE KEY(k1, k2)
   COMMENT "my first starrocks table"
   DISTRIBUTED BY HASH(k1) BUCKETS 32
   PROPERTIES ("storage_type"="column");

2. 创建一个动态分区表(需要在FE配置中开启动态分区功能)，该表每天提前创建3天的分区，并删除3天前的分区。例如今天为2020-01-08，则会创建分区名为p20200108, p20200109, p20200110, p20200111的分区. 分区范围分别为:

   [types: [DATE]; keys: [2020-01-08]; ‥types: [DATE]; keys: [2020-01-09]; )
   [types: [DATE]; keys: [2020-01-09]; ‥types: [DATE]; keys: [2020-01-10]; )
   [types: [DATE]; keys: [2020-01-10]; ‥types: [DATE]; keys: [2020-01-11]; )
   [types: [DATE]; keys: [2020-01-11]; ‥types: [DATE]; keys: [2020-01-12]; )

   CREATE TABLE example_db.dynamic_partition
   (
   k1 DATE,
   k2 INT,
   k3 SMALLINT,
   v1 VARCHAR(2048),
   v2 DATETIME DEFAULT "2014-02-04 15:36:00"
   )
   ENGINE=olap
   DUPLICATE KEY(k1, k2, k3)
   PARTITION BY RANGE (k1)
   (
   PARTITION p1 VALUES LESS THAN ("2014-01-01"),
   PARTITION p2 VALUES LESS THAN ("2014-06-01"),
   PARTITION p3 VALUES LESS THAN ("2014-12-01")
   )
   DISTRIBUTED BY HASH(k2) BUCKETS 32
   PROPERTIES(
   "storage_medium" = "SSD",
   "dynamic_partition.time_unit" = "DAY",
   "dynamic_partition.start" = "-3",
   "dynamic_partition.end" = "3",
   "dynamic_partition.prefix" = "p",
   "dynamic_partition.buckets" = "32"
   );

3. Create a table with rollup index

   CREATE TABLE example_db.rolup_index_table
   (
   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)
   DISTRIBUTED BY HASH(siteid) BUCKETS 10
   rollup (
   r1(event_day,siteid),
   r2(event_day,citycode),
   r3(event_day)
   )
   PROPERTIES("replication_num" = "3");

4. 创建一个内存表

   CREATE TABLE example_db.table_hash
   (
   k1 TINYINT,
   k2 DECIMAL(10, 2) DEFAULT "10.5",
   v1 CHAR(10) REPLACE,
   v2 INT SUM,
   INDEX k1_idx (k1) USING BITMAP COMMENT 'xxxxxx'
   )
   ENGINE=olap
   AGGREGATE KEY(k1, k2)
   COMMENT "my first starrocks table"
   DISTRIBUTED BY HASH(k1) BUCKETS 32
   PROPERTIES ("in_memory"="true");

5. 创建一个hive外部表

   CREATE TABLE example_db.table_hive
   (
   k1 TINYINT,
   k2 VARCHAR(50),
   v INT
   )
   ENGINE=hive
   PROPERTIES
   (
   "database" = "hive_db_name",
   "table" = "hive_table_name",
   "hive.metastore.uris" = "thrift://127.0.0.1:9083"
   );