涛思 TDengine 表设计及SQL

一、 表设计

1、特点：

要充分利用数据的时序性等特点，TDengine 采取一只股票一张表的策略，建议用数股票的代码（如上表中的 600000）来做表名。

表的第一列必须是时间戳，即数据类型为 timestamp。TDengine 将自动按照时间戳建立索引。

好处：

1. 无锁方式写入，写入速度就能大幅提升
2. 数据是按照时间排序的，因此写的操作可用追加的方式实现。
3. 一张表的数据是以块为单位连续存储的。读取一个时间段的数据，能大幅减少随机读取操作。

2、超级表

普通标的特点，是同一类型数据的表，其表结构是完全一样的，但每个表的静态属性（标签）是不一样的。
超级表是指某一特定类型的数据表的集合。
描述一个超级表，除需要定义实时变化数据(采集量)的表结构之外，还需要定义静态不变数据(标签)的 schema。
标签的数据类型可以是整数、浮点数、字符串，标签可以有多个，可以事后增加、删除或修改。
如果整个系统有 N 个不同类型的数据，就需要建立 N 个超级表。

3、子表

通过超级表创建的表称之为子表

1. 子表就是表，因此所有正常表的SQL操作都可以在子表上执行。
2. 一张超级表包含有多张子表，这些子表具有相同的采集量 schema，但带有不同的标签值
3. 不能通过子表调整数据或标签的模式，对于超级表的数据模式修改立即对所有的子表生效。
4. 超级表只定义一个模板，自身不存储任何数据或标签信息。因此，不能向一个超级表写入数据，只能将数据写入子表中。

4、查询

查询既可以在表上进行，也可以在超级表上进行。针对超级表的查询，TDengine 将把所有子表中的数据视为一个整体数据集进行处理，会先把满足标签过滤条件的表从超级表中找出来，然后再扫描这些表的时序数据，进行聚合操作

5、库

库是指一组表的集合。TDengine 容许一个运行实例有多个库，而且每个库可以配置不同的存储策略。
TDengine 建议将不同数据特征的超级表创建在不同的库里。

二、SQL

1、创建库

数据库名最大长度为 33，一条 SQL 语句的最大长度为 65480 个字符。

#语法
CREATE DATABASE [IF NOT EXISTS] db_name [KEEP keep] [DAYS days] [UPDATE 1];

#创建库
CREATE DATABASE power KEEP 365 DAYS 10 BLOCKS 6 UPDATE 1;

#删除库
DROP DATABASE [IF EXISTS] db_name;

#修改库
ALTER DATABASE db_name COMP 2;

#切换库
USE power;
SHOW DATABASES;
SHOW CREATE DATABASE db_name;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2、创建超级表

TAGS 列名不能与其他列名相同；TAGS 列名不能为预留关键字，TAGS 最多允许 128 个，至少 1 个，总长度不超过 16 KB。

#语法
CREATE STABLE [IF NOT EXISTS] stb_name (timestamp_field_name TIMESTAMP, field1_name data_type1 [, field2_name data_type2 ...]) TAGS (tag1_name tag_type1, tag2_name tag_type2 [, tag3_name tag_type3]);

#创建超级表
CREATE STABLE meters (ts timestamp, current float, voltage int, phase float) TAGS (location binary(64), groupId int);

DROP STABLE [IF EXISTS] stb_name;
SHOW STABLES [LIKE tb_name_wildcard];
SHOW CREATE STABLE stb_name;
DESCRIBE stb_name;
ALTER STABLE stb_name ADD COLUMN field_name data_type;
ALTER STABLE stb_name DROP COLUMN field_name;
ALTER STABLE stb_name MODIFY COLUMN field_name data_type(length);
ALTER STABLE stb_name ADD TAG new_tag_name tag_type;
ALTER STABLE stb_name DROP TAG tag_name;
ALTER STABLE stb_name CHANGE TAG old_tag_name new_tag_name;
ALTER STABLE stb_name MODIFY TAG tag_name data_type(length);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

超级表查询

使用 SELECT 语句可以完成在超级表上的投影及聚合两类查询，在 WHERE 语句中可以对标签及列进行筛选及过滤。

如果在超级表查询语句中不加 ORDER BY, 返回顺序是先返回一个子表的所有数据，然后再返回下个子表的所有数据，所以返回的数据是无序的。如果增加了 ORDER BY 语句，会严格按 ORDER BY 语句指定的顺序返回的

3、创建表

TDengine 对每个数据采集点需要独立建表。与标准的关系型数据库一样，一张表有表名，Schema，但除此之外，还可以带有一到多个标签。创建时，需要使用超级表做模板，同时指定标签的具体值。以表 1中的智能电表为例，可以使用如下的 SQL 命令建表：

表的第一个字段必须是 TIMESTAMP，并且系统自动将其设为主键；表的每行长度不能超过 48KB;

#语法-创建普通表
CREATE TABLE [IF NOT EXISTS] tb_name (timestamp_field_name TIMESTAMP, field1_name data_type1 [, field2_name data_type2 ...]);

#语法-以超级表为模板创建数据表
CREATE TABLE [IF NOT EXISTS] tb_name USING stb_name TAGS (tag_value1, ...);

#语法-批量创建数据表
#批量建表方式要求数据表必须以超级表为模板。 2.在不超出 SQL 语句长度限制的前提下，单条语句中的建表数量建议控制在 1000 ～ 3000 之间，将会获得比较理想的建表速度。
CREATE TABLE [IF NOT EXISTS] tb_name1 USING stb_name TAGS (tag_value1, ...) [IF NOT EXISTS] tb_name2 USING stb_name TAGS (tag_value2, ...) ...;

#示例
CREATE TABLE d1001 USING meters TAGS ("California.SanFrancisco", 2);

#插入时自动建表
INSERT INTO d1001 USING meters TAGS ("California.SanFrancisco", 2) VALUES (now, 10.2, 219, 0.32);

DROP TABLE [IF EXISTS] tb_name;
SHOW CREATE TABLE tb_name;
SHOW CREATE TABLE tb_name;
DESCRIBE tb_name;
ALTER TABLE tb_name ADD COLUMN field_name data_type;
ALTER TABLE tb_name DROP COLUMN field_name;
ALTER TABLE tb_name MODIFY COLUMN field_name data_type(length);
ALTER TABLE tb_name SET TAG tag_name=new_tag_value;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

4、插入数据

文档参考：https://docs.taosdata.com/develop/insert-data/sql-writing

一条记录不能超过 48K，一条 SQL 语句总长度不能超过 1M ，一个客户端需打开不大于 20 个以上的线程同时写。

对同一张表，如果新插入记录的时间戳已经存在，在update=0模式下，新数据被抛弃；在update=1的模式下，用新数据覆盖旧数据。写入的数据的时间戳必须大于当前时间减去配置参数 keep 的时间。

#插入一条
INSERT INTO d1001 VALUES (1538548685000, 10.3, 219, 0.31);

#批量插入
INSERT INTO d1001 VALUES (1538548684000, 10.2, 220, 0.23) (1538548696650, 10.3, 218, 0.25);

#写入多表
INSERT INTO d1001 VALUES (1538548685000, 10.3, 219, 0.31) (1538548695000, 12.6, 218, 0.33) d1002 VALUES (1538548696800, 12.3, 221, 0.31);

1
2
3
4
5
6
7
8
9

5、查询

支持

• 单列、多列数据查询
• 标签和数值的多种过滤条件：>, <, =, <>, like 等
• 聚合结果的分组（Group by）、排序（Order by）、约束输出（Limit/Offset）
• 数值列及聚合结果的四则运算
• 时间戳对齐的连接查询（Join Query: 隐式连接）操作
• 多种聚合/计算函数: count, max, min, avg, sum, twa, stddev, leastsquares, top, bottom, first, last, percentile, apercentile, last_row, spread, diff 等

TDengine 仅容许对属于同一个超级表的表之间进行聚合查询，不同超级表之间的聚合查询不支持

#文档参考：https://docs.taosdata.com/taos-sql/select/

#查询语法
SELECT select_expr [, select_expr ...]
    FROM {tb_name_list}
    [WHERE where_condition]
    [SESSION(ts_col, tol_val)]
    [STATE_WINDOW(col)]
    [INTERVAL(interval_val [, interval_offset]) [SLIDING sliding_val]]
    [FILL(fill_mod_and_val)]
    [GROUP BY col_list]
    [ORDER BY col_list { DESC | ASC }]
    [SLIMIT limit_val [SOFFSET offset_val]]
    [LIMIT limit_val [OFFSET offset_val]]
    [>> export_file];

#单表查询
select * from d1001 where voltage > 215 order by ts desc limit 2;
#超级表聚合查询
SELECT AVG(voltage) FROM meters GROUP BY location;
SELECT count(*), max(current) FROM meters where groupId = 2 and ts > now - 24h;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19