创建时序表

本节使用一个真实场景，来告诉大家如何在MatrixDB中创建时序表：设备表与指标表。

本课程教学视频请参考MatrixDB数据建模与时空分布模型

1. 时序表模型

场景中要采集风机的转速、温度和湿度。风机设备包含名称、编号、区域、坐标等属性。

1.1 风机信息表

字段	类型	描述
device_id	int	device_id为风机的唯一id，用自增主键（SERIAL类型）
name	text	风机名称
serial_number	text	风机序列号
region	text	风机所属区域
longitude	float4	风机经度
latitude	float4	风机纬度

1.2 风机指标表

字段	类型	描述
device_id	int	风机id
time	timestamp	时序数据的时间戳
speed	float4	风机转速
temperature	float4	风机温度
humidity	float4	风机湿度

2. 创建数据库与表

2.1 创建数据库

和其他数据库一样，在MatrixDB中，数据表是按库组织起来的，所以先创建数据库stats。

可以连接默认数据库postgres，然后使用CREATE DATABASE创建：

[mxadmin@mdw ~]$ psql postgres
psql (12)
Type "help" for help.

postgres=# CREATE DATABASE stats;
CREATE DATABASE

也可以直接使用MatrixDB的命令行工具createdb创建：

[mxadmin@mdw ~]$ createdb stats

2.2 连接数据库

创建好数据库后，要连接进去才能使用。在MatrixDB中每个连接只能隶属于一个固定的库。

可以在执行psql命令尾随数据库参数连接：

[mxadmin@mdw ~]$ psql stats
psql (12)
Type "help" for help.

stats=#

也可以从其他数据库连接中，使用\c元命令，切换连接到新的数据库中：

postgres=# \c stats
You are now connected to database "stats" as user "mxadmin".

2.3 创建表

连接数据库后，开始创建数据表：

创建设备表：

CREATE TABLE device(
 device_id serial,
 name text,
 serial_number text,
 region text,
 longitude float4,
 latitude float4
)
Distributed replicated;

创建指标表：

CREATE TABLE metrics(
 time timestamp with time zone,
 device_id int,
 speed float4,
 temperature float4,
 humidity float4
)
Distributed by (device_id);

从上面的建表语句会发现，两个表的分布方式不同。

device表使用得是Distributed replicated
metrics使用得是Distributed by (device_id)

有关表的分布方式稍后介绍，下面为device表建立唯一索引：

因为device表中设备信息必须唯一，所以需要为标识设备唯一属性的信息建立唯一索引，便于应用端维护设备信息。这里假设设备编号用来标志设备的唯一性，所以建立如下唯一索引：

CREATE UNIQUE INDEX ON device(serial_number);

3. 数据写入与查询

创建完表之后，下面介绍一下简单的数据写入与查询操作。

3.1 写入数据

应用方在进行指标数据接入的时候，需要按如下步骤进行：

将设备标识转化为设备ID
将设备ID和指标信息发送到MatrixDB

那么设备标识如何转换成ID呢？设备ID并不是设备自身的一个属性，不会随指标数据发送过来，需要应用方进行转换。

最简单的做法就是每次根据设备编号查库，如果存在则直接读取；不存在则插入新设备并生成ID。

这种方法需要每个指标在插入前都要查库，大大增加数据库负载，影响吞吐量。所以推荐做法是，物联网网关或者Kafka消费程序维护一个内存哈希表。每条数据在到来的时候从内存哈希表查询设备ID，存在则直接使用；不存在则插库获取设备ID并更新哈希表。应用方

3.2 查询数据

既然设备信息和指标数据分开到两个表存储。在做查询的时候，如果既要获得指标统计信息又要获得设备信息，要基于设备ID做连接。

数据连接

如下SQL统计了'2021-07-01'日每个风机的平均温度、平均湿度和最大转速：

SELECT device.name,
        AVG(metrics.temperature),
        AVG(metrics.humidity),
        MAX(metrics.speed)
    FROM device JOIN metrics USING (device_id)
    WHERE metrics.time >= '2021-07-01'
        AND metrics.time < '2021-07-02'
    GROUP BY device.device_id, device.name;

4. 数据分布模型

4.1 master-segment架构

MatrixDB是一款中心化架构的分布式数据库，包含master和segment两种类型节点。

master-segment master是中心控制节点，用来接收用户请求并生成查询计划。master节点并不存储数据，数据存储在segment节点上。

master节点只有一个，segment节点至少有一个，可以更多，几十个甚至上百个。segment节点越多，集群可以存储越多数据，计算能力也越强。

4.2 数据分布策略

MatrixDB数据表在segment上有两种分布策略，分片和冗余；分片又分为哈希和随机两种分片方式：

分片分布
- 哈希分片
- 随机分片
冗余分布

分片分布即将数据水平切分，一份数据只存储在其中一个节点上。哈希分片需要定义哈希键，可以是一个或多个，数据库对哈希键计算键值来决定存储在哪个节点上。随机分片则将数据随机分配到一个节点上。

冗余分布则将数据冗余存储在每个数据节点上，即每个数据节点都包含表中的所有数据。这种方式会占用大量存储空间，所以只有经常需要做连接操作的小表才使用冗余分布。

综上，数据分布总共3种策略，不同的分布策略，在创建表的时候可以通过Distributed by关键字来设置：

哈希：Distributed by(column)
随机：Distributed randomly
复制：Distributed replicated

distribute策略

4.3 分布策略对查询的影响

分布式架构下的MatrixDB，大部分数据表都采用分片存储，但是可以支持全部关系查询。从用户角度来看，就是一个空间无穷大的PostgreSQL。

那么，对于跨节点的数据连接如何做呢？这得益于MatrixDB的Motion操作，当满足连接条件的数据不在同一个节点上时，会将它们移动到相同节点上做连接。 motion

当然，做移动操作是有成本的。所以，在设计表的时候，要充分考虑数据分布特征与后期要进行的查询类型来综合决定分布策略。

只有满足连接条件的数据完全分布在相同的节点上才可以避免移动操作。所以经常要做连接操作的大表，最好将连接键设置为分布键。

4.4 时序数据分布策略实战

了解了MatrixDB数据分布方式与利弊，下面讨论一下时序数据表应该采用哪种分布策略。

指标表

首先考虑一下指标表，指标数据量非常大，不可能采用冗余分布方式，而随机分布又没有任何规律，不利于后面的统计分析，所以哈希分布是首选。那又该如何确定分布键呢？

时序表数据包含3个维度：

时间戳
设备ID
采集指标

后面的统计分析基本都是以设备并连接设备表的其他属性作为分组键，所以使用设备ID作为分布键。

设备表

设备表数据规模相对固定，不会像指标数据那样无限增长，所以一般采用冗余分布方式。这样，在和指标表做连接操作时无需做跨节点的数据移动。