3 建表（Create Table）

　　

　　在 Doris 中，数据都以表（Table）的形式进行逻辑上的描述。

　　Doris 采用 MySQL 协议进行通信，用户可通过 MySQL client 或者 MySQL JDBC连接到 Doris 集群。选择 MySQL client 版本时建议采用5.1 之后的版本，因为 5.1 之前不能支持长度超过 16 个字符的用户名。

　　一张表包括行（Row）和列（Column）。Row 即用户的一行数据。Column 用于描述一行数据中不同的字段。

　　Column 可以分为两大类：Key 和 Value。从业务角度看，Key 和 Value 可以分别对应维度列和指标列。从聚合模型的角度来说，Key 列相同的行，会聚合成一行。其中 Value 列的聚合方式由用户在建表时指定。关于更多聚合模型的介绍，可以参阅 Doris 数据模型

　　在 Doris 的存储引擎中，用户数据被水平划分为若干个数据分片（*Tablet，也称作数据分桶）。每个 Tablet 包含若干数据行。各个 Tablet 之间的数据没有交集，并且在物理上是独立存储的。

　　多个 Tablet 在逻辑上归属于不同的分区（Partition）。一个 Tablet 只属于一个 Partition。而一个 Partition 包含若干个 Tablet。因为 Tablet 在物理上是独立存储的，所以可以视为 Partition 在物理上也是独立。Tablet 是数据移动、复制等操作的最小物理存储单元。

　　若干个 Partition 组成一个 Table。Partition 可以视为是逻辑上最小的管理单元。数据的导入与删除，都可以或仅能针对一个 Partition 进行。

　　◆ Root 用户登录与密码修改

　　Doris 内置 root 和 admin 用户，密码默认都为空。启动完 Doris 程序之后，可以通过 root 或 admin 用户连接到 Doris 集群。 使用下面命令即可登录 Doris：

　　mysql -h node1 -P9030 -uroot

　　登陆后，可以通过以下命令修改 root 密码

　　SET PASSWORD FOR ‘root’ = PASSWORD(‘your_password’);

　　◆ 创建新用户

　　通过下面的命令创建一个普通用户

　　CREATE USER ‘test’ IDENTIFIED BY ‘test_passwd’;

　　后续登录时就可以通过下列连接命令登录。

　　mysql -h node1 -P9030 -utest -ptest_passwd

　　*注意：

　　? 新创建的普通用户默认没有任何权限。权限授予可以参考后面的权限授予。

　　◆ 创建数据库

　　初始可以通过 root 或 admin 用户创建数据库：

　　CREATE DATABASE test_db;

　　注意：

　　? 所有命令都可以使用 ‘HELP command;’ 查看到详细的语法帮助。如：HELP CREATE DATABASE;? 如果不清楚命令的全名，可以使用 “help 命令某一字段” 进行模糊查询。如键入 ‘HELP CREATE’，可以匹配到 CREATE DATABASE, CREATE TABLE, CREATE USER 等命令。 数据库创建完成之后，可以通过 SHOW DATABASES; 查看数据库信息。

　　show databases; information_schema是为了兼容MySQL协议而存在，实际中信息可能不是很准确，所以关于具体数据库的信息建议通过直接查询相应数据库而获得。

　　◆ 权限赋予

　　test_db 创建完成之后，可以通过 root/admin 账户将 test_db 读写权限授权给普通账户，如 test。授权之后采用 test 账户登录就可以操作 test_db 数据库了。

　　GRANT AL◆ ON test_db TO test;

　　可以通过在 mysql-client 中执行以下 help 命令获得更多帮助：

　　◆ help create table

　　◆ help load

　　◆ help mini load

　　◆ help delete

　　◆ help alter table

　　使用 CREATE TABLE 命令建立一个表(Table)。更多详细参数可以查看:

　　HELP CREATE TABLE;

　　首先切换数据库:

　　USE test_db;

　　Doris 的建表是一个同步命令，命令返回成功，即表示建表成功。

　　可以通过 *HELP CREATE TABLE; 查看更多帮助。

　　CREATE TABLE IF NOT EXISTS test_db.table0( LARGEINT NOT NUL◆ COMMENT “用户id”, DATE NOT NULL COMMENT “数据灌入日期时间”, DATETIME NOT NUL◆ COMMENT “数据灌入的时间戳”, VARCHAR(20) COMMENT “用户所在城市”, SMALLINT COMMENT “用户年龄”, TINYINT COMMENT “用户性别”, DATETIME REPLACE DEFAULT “1970-01-01 00:00:00” COMMENT “用户最后一次访问时间”, BIGINT SUM DEFAULT “0” COMMENT “用户总消费”, INT MAX DEFAULT “0” COMMENT “用户最大停留时间”, INT MIN DEFAULT “99999” COMMENT “用户最小停留时间”)ENGINE=olapAGGREGATE KEY(, , , , , )PARTITION BY RANGE()( PARTITION VALUES LESS THAN (“2020-02-01”), PARTITION VALUES LESS THAN (“2020-03-01”), PARTITION VALUES LESS THAN (“2020-04-01”))DISTRIBUTED BY HASH() BUCKETS 16PROPERTIES( “replication_num” = “3”, “storage_medium” = “SSD”, “storage_cooldown_time” = “2021-05-01 12:00:00”);

　　

　　◆ TINYINT数据类型

　　长度: 长度为1个字节的有符号整型。

　　范围: [-128, 127]

　　转换: Doris可以自动将该类型转换成更大的整型或者浮点类型。使用CAST()函数可以将其转换成CHAR。

　　举例:

　　select cast(100 as char); ◆ SMALLINT数据类型

　　长度: 长度为2个字节的有符号整型。

　　范围: [-32768, 32767]

　　转换: Doris可以自动将该类型转换成更大的整型或者浮点类型。使用CAST()函数可以将其转换成TINYINT，CHAR。

　　举例:

　　select cast(10000 as char);select cast(10000 as tinyint); ◆ INT数据类型

　　长度: 长度为4个字节的有符号整型。

　　范围: [-2147483648, 2147483647]

　　转换: Doris可以自动将该类型转换成更大的整型或者浮点类型。使用CAST()函数可以将其转换成TINYINT，SMALLINT，CHAR

　　举例:

　　select cast(111111111 as char); ◆ BIGINT数据类型

　　长度: 长度为8个字节的有符号整型。

　　范围: [-9223372036854775808, 9223372036854775807]

　　转换: Doris可以自动将该类型转换成更大的整型或者浮点类型。使用CAST()函数可以将其转换成TINYINT，SMALLINT，INT，CHAR

　　举例:

　　select cast(9223372036854775807 as char); ◆ LARGEINT数据类型

　　长度: 长度为16个字节的有符号整型。

　　范围: [-2^127, 2^127-1]

　　转换: Doris可以自动将该类型转换成浮点类型。使用CAST()函数可以将其转换成TINYINT，SMALLINT，INT，BIGINT，CHAR

　　举例:

　　select cast(922337203685477582342342 as double); ◆ FLOAT数据类型

　　长度: 长度为4字节的浮点类型。

　　范围: -3.40E+38 ~ +3.40E+38。

　　转换: Doris会自动将FLOAT类型转换成DOUBLE类型。用户可以使用CAST()将其转换成TINYINT, SMALLINT, INT, BIGINT, STRING, TIMESTAMP。

　　◆ DOUBLE数据类型

　　长度: 长度为8字节的浮点类型。

　　范围: -1.79E+308 ~ +1.79E+308。

　　转换: Doris不会自动将DOUBLE类型转换成其他类型。用户可以使用CAST()将其转换成TINYINT, SMALLINT, INT, BIGINT, STRING, TIMESTAMP。用户可以使用指数符号来描述DOUBLE 类型，或通过STRING转换获得。

　　◆ DECIMAL数据类型

　　DECIMAL[M, D]

　　保证精度的小数类型。M代表一共有多少个有效数字，D代表小数点后最多有多少数字。M的范围是[1,27]，D的范围是[1,9]，另外，M必须要大于等于D的取值。默认取值为decimal[10,0]。

　　precision: 1 ~ 27

　　scale: 0 ~ 9

　　◆ DATE数据类型

　　范围: [0000-01-01~9999-12-31]。默认的打印形式是’YYYY-MM-DD’。

　　◆ DATETIME数据类型

　　范围: [0000-01-01 00:00:00~9999-12-31 23:59:59]。默认的打印形式是’YYYY-MM-DD HH:MM:SS’。

　　◆ CHAR数据类型

　　范围: char[(length)]，定长字符串，长度length范围1~255，默认为1。

　　转换：用户可以通过CAST函数将CHAR类型转换成TINYINT,，SMALLINT，INT，BIGINT，LARGEINT，DOUBLE，DATE或者DATETIME类型。

　　示例：

　　select cast(1234 as bigint); ◆ VARCHAR数据类型

　　范围: char(length)，变长字符串，长度length范围1~65535。

　　转换：用户可以通过CAST函数将CHAR类型转换成TINYINT,，SMALLINT，INT，BIGINT，LARGEINT，DOUBLE，DATE或者DATETIME类型。

　　示例：

　　select cast(‘2011-01-01’ as date);select cast(‘2011-01-01’ as datetime);select cast(3423 as bigint); ◆ HLL数据类型

　　范围：char(length),长度length范围1~16385。用户不需要指定长度和默认值、长度根据数据的聚合程度系统内控制，并且HLL列只能通过配套的hll_union_agg、hll_cardinality、hll_hash进行查询或使用

　　Doris支持单分区和复合分区两种建表方式。

　　在复合分区中：

　　◆ 第一级称为 Partition，即分区。用户可以指定某一维度列作为分区列（*当前只支持整型和时间类型的列），并指定每个分区的取值范围。

　　◆ 第二级称为 Distribution，即分桶。用户可以指定一个或多个维度列以及桶数对数据进行 HASH 分布。

　　以下场景推荐使用复合分区

　　◆ 有时间维度或类似带有有序值的维度，可以以这类维度列作为分区列。分区粒度可以根据导入频次、分区数据量等进行评估。

　　◆ 历史数据删除需求：如有删除历史数据的需求（比如仅保留最近N 天的数据）。使用复合分区，可以通过删除历史分区来达到目的。也可以通过在指定分区内发送 DELETE 语句进行数据删除。

　　◆ 解决数据倾斜问题：每个分区可以单独指定分桶数量。如按天分区，当每天的数据量差异很大时，可以通过指定分区的分桶数，合理划分不同分区的数据,分桶列建议选择区分度大的列。

　　用户也可以不使用复合分区，即使用单分区。则数据只做 HASH 分布。

　　下面以聚合模型为例，分别演示两种分区的建表语句。

　　◆ Partition

　　

　　◆ Bucket

　　

　　◆ PROPERTIES

　　

　　◆ ENGINE

　　

　　◆ 一个表的 Tablet 总数量等于 (Partition num * Bucket num)。

　　◆ 一个表的 Tablet 数量，在不考虑扩容的情况下，推荐略多于整个集群的磁盘数量。

　　◆ 单个 Tablet 的数据量理论上没有上下界，但建议在 1G - 10G 的范围内。如果单个 Tablet 数据量过小，则数据的聚合效果不佳，且元数据管理压力大。如果数据量过大，则不利于副本的迁移、补齐，且会增加 Schema Change 或者 Rollup 操作失败重试的代价（这些操作失败重试的粒度是 Tablet）。

　　◆ 当 Tablet 的数据量原则和数量原则冲突时，建议优先考虑数据量原则。

　　◆ 在建表时，每个分区的 Bucket 数量统一指定。但是在动态增加分区时（ADD PARTITION），可以单独指定新分区的 Bucket 数量。可以利用这个功能方便的应对数据缩小或膨胀。

　　◆ 一个 Partition 的 Bucket 数量一旦指定，不可更改。所以在确定 Bucket 数量时，需要预先考虑集群扩容的情况。比如当前只有 3 台 host，每台 host 有 1 块盘。如果 Bucket 的数量只设置为 3 或更小，那么后期即使再增加机器，也不能提高并发度。

　　◆ 举一些例子：假设在有10台BE，每台BE一块磁盘的情况下。如果一个表总大小为 500MB，则可以考虑4-8个分片。5GB：8-16个。50GB：32个。500GB：建议分区，每个分区大小在 50GB 左右，每个分区16-32个分片。5TB：建议分区，每个分区大小在 50GB 左右，每个分区16-32个分片。

　　注：表的数据量可以通过 show data 命令查看，结果除以副本数，即表的数据量。

　　建立一个名字为 table1 的逻辑表。分桶列为 siteid，桶数为 10。

　　这个表的 schema 如下：

　　◆ siteid：类型是INT（4字节）, 默认值为10

　　◆ citycode：类型是SMALLINT（2字节）

　　◆ username：类型是VARCHAR, 最大长度为32, 默认值为空字符串

　　◆ pv：类型是BIGINT（8字节）, 默认值是0; 这是一个指标列, Doris内部会对指标列做聚合操作, 这个列的聚合方法是求和（SUM）

　　建表语句如下:

　　CREATE TABLE table1( siteid INT DEFAULT ‘10’, citycode SMALLINT, username VARCHAR(32) DEFAULT ‘’, pv BIGINT SUM DEFAULT ‘0’)AGGREGATE KEY(siteid, citycode, username)DISTRIBUTED BY HASH(siteid) BUCKETS 10PROPERTIES(“replication_num” = “1”);将 table1_data 导入 table1 中：vim table1_data10,101,jim,211,101,grace,212,102,tom,213,102,bush,314,103,helen,3curl --location-trusted -u root:123456 -H “label:table1_20210210” -H “column_separator:,” -T table1_data http://node1:8030/api/test_db/table1/_stream_loadselect * from table1; 建立一个名字为 table2 的逻辑表。

　　这个表的 schema 如下：

　　◆ event_day：类型是DATE，无默认值

　　◆ siteid：类型是INT（4字节）, 默认值为10

　　◆ citycode：类型是SMALLINT（2字节）

　　◆ username：类型是VARCHAR, 最大长度为32, 默认值为空字符串

　　◆ pv：类型是BIGINT（8字节）, 默认值是0; 这是一个指标列, Doris 内部会对指标列做聚合操作, 这个列的聚合方法是求和（SUM）

　　我们使用 event_day 列作为分区列，建立3个分区: p202006, p202007, p202008

　　◆ p202006：范围为 [最小值, 2020-07-01)

　　◆ p202007：范围为 [2020-07-01, 2020-08-01)

　　◆ p202008：范围为 [2020-08-01, 2020-09-01)

　　? 注意区间为左闭右开。

　　每个分区使用 siteid 进行哈希分桶，桶数为10

　　建表语句如下:

　　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 p202006 VALUES LESS THAN (‘2020-07-01’), PARTITION p202007 VALUES LESS THAN (‘2020-08-01’), PARTITION p202008 VALUES LESS THAN (‘2020-09-01’))DISTRIBUTED BY HASH(siteid) BUCKETS 10PROPERTIES(“replication_num” = “1”);将 table2_data 导入 table2 中：vim table2_data2020-07-03cur◆ --location-trusted -u root:123456 -H “label:table2_20200707” -H "column_separator:select * from table2; *注意事项：

　　? 上述表通过设置 replication_num 建的都是单副本的表，Doris建议用户采用默认的 3 副本设置，以保证高可用。? 可以对复合分区表动态的增删分区。详见 HELP ALTER TABLE 中 Partition 相关部分。? 数据导入可以导入指定的 Partition。详见 HELP LOAD。? 可以动态修改表的 Schema。? 可以对 Table 增加上卷表（Rollup）以提高查询性能，这部分可以参见高级使用指南关于 Rollup 的描述。? 表的列的Null属性默认为true，会对查询性能有一定的影响。