GaussDB(DWS)带你走近IoT时代-时序表建表最佳实践

创建第一张时序表

我们首先来介绍一下如何创建一张时序表

语法格式

CREATE TABLE [ IF NOT EXISTS ] table_name
({ column_name data_type [ kv_type ] 
    | LIKE source_table [like_option [...] ] }
}
    [, ... ])
[ WITH ( {storage_parameter = value}  [, ... ] ) ]

[ TABLESPACE tablespace_name ]
[ DISTRIBUTE BY  HASH ( column_name [,...])]
[ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ]
[ table_partitioning_clauses ]
[ PARTITION BY { 
        {RANGE (partition_key) ( partition_less_than_item [, ... ] )} 
 } [ { ENABLE | DISABLE } ROW MOVEMENT ] ]; 
其中like选项like_option为：{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | PARTITION | RELOPTIONS | DISTRIBUTION | ALL }

时序表的建表语法，在很大程度上继承了行存和列存的语法，降低了用户的学习成本，能够更容易理解和使用。我们上篇博客中介绍到，将时序表的列分为三种kv_type类型（tag、field、time），那么如何将对应的列指定为合适的类型，帮助我们更好的提高导入、查询等场景的性能，让业务场景运行的更加高效呢？

我们继续以发电机组的场景作为示例，创建一张存储发电机组采样数据的时序表：

CREATE TABLE IF NOT EXISTS GENERATOR(
dynamo text TSTag,
manufacturer text TSTag,
model text TSTag,
location text TSTag,
ID bigint TSTag,
voltage numeric TSField,
power bigint TSTag,
frequency numeric TSField,
angle numeric TSField,
time timestamptz TSTime) with (orientation=TIMESERIES, period='7 days', ttl='1 month') distribute by hash(model);

我们可以看到，上述建表语句中：

• 对于不随时间的变化而变化，描述发电机的属性信息的列（发电机信息、生产厂商、型号、位置、ID）被设置为tag列，在建表时需要将对应的列后面指定为TSTag；

• 对于采样数据的维度（电压、功率、频率、电流相角）这些对应的采样数值随时间的变化而变，我们将这些维度设置为field列，建表语句数据类型后面指定为TSField；

• 最后一列我们指定为时间列time，存储field列数据对应的时间信息，建表时将指定为TSTime。

在写建表语句时，对于tag列的顺序，我们可以适当优化一下，将唯一性（distinct值）较高的列尽量写在前面，这样对于时序场景的性能有一些提升。如果用户没有手动优化，GaussDB(DWS) IoT数仓也可以自适应的帮助用户提高时序场景的性能，这后面我们会专门文章介绍这一黑科技。

另外，创建时序表时一定要指定表级参数orientation属性设置为timeseries。时序表不需要手动指定DISTRIBUTE BY和PARTITION BY, 默认按照所有tag列分布，且分区健默认为tstim指定的时间列。

对于create table like语法，该语法需要自动从源表中继承列名和对应的kv_type类型。因此如果源表是非时序表，新表是时序表，对应的列的kv_type类型无法确定，则无法创建成功。

时序表列类型

上面对时序表的三种kv_type属性进行了简单的说明：（维度属性(TSTag)，指标属性(TSField)，时间属性(TSTime)，那么每个列他们支持的类型（类似建表语句中的text、int、numeric等）分别都是什么呢？是否可以不设置kv_type类型？

首先时序表必须指定一个时间属性（TSTime），且只能指定一个，且TSTime类型的列不能被删除。至少存在一个TSTag和TSField列，否则建表报错。

TSTag列支持类型：text, char, bool, int, big int。

TSTime列支持类型：timestamp with time zone, timestamp without time zone。在兼容Oracle语法的数据库中，也支持date类型。涉及到时区相关操作时，请选择带时区的时间类型。

TSField列支持的数据类型同列存表保持一致

自动设置分区边界

时序表具备数据生命周期管理的能力。每天数以亿计的数据不间断涌入，对于很久之前的数据，其价值较低不经常访问，可以定期将无用的老数据删除。并且由于最新的数据不断进来，需要定期给表增加新的分区，避免新数据无法存储。因此时序表需要具备定时增加分区和定时删除分区的能力。

时序表以TSTIME列为分区键，创建具有自动分区管理功能的分区表，帮助我们大大减少运维操作的工作。在上面的建表语句中，在表级参数项中可以看到，时序表指定了自动分区管理两个参数period和ttl。

• period：设置自动创建分区的间隔时间，默认值为1 day, 取值范围：1 hour ~ 100 years。默认会为时序表创建自增分区任务。自增分区任务动态为我们创建分区，保证当前时刻有足够充裕的分区用于导入数据。
• ttl：设置自动淘汰分区的时间，取值范围：1 hour ~ 100 years。默认不创建淘汰分区任务，需要用户自己在建表手动指定，或者建表后通过ALTER TABLE语法设置。淘汰分区的策略是通过计算 nowtime - 分区boundary > ttl，满足该条件的分区将被drop掉。帮助用户定时清理过期的旧数据。

上面举得例子中，时序表建表语句没有指定分区，那么分区的起始时间是怎么样的。为了方便用户的使用，我们的分区边界的设置分为了一下几种情况

• period设置为“小时” , 分区起始边界值为下个小时整点，分区的间隔为period的值
• period设置为“天” , 分区起始边界值为第二天零点，分区的间隔为period的值
• period设置为“月” , 分区起始边界值为下个月零点，分区的间隔为period的值
• period设置为“年” , 分区起始边界值为明年零点，分区的间隔为period的值

tsdb=# CREATE TABLE IF NOT EXISTS GENERATOR(
tsdb(# dynamo text TSTag,
tsdb(# manufacturer text TSTag,
tsdb(# model text TSTag,
tsdb(# location text TSTag,
tsdb(# ID bigint TSTag,
tsdb(# voltage numeric TSField,
tsdb(# power bigint TSTag,
tsdb(# frequency numeric TSField,
tsdb(# angle numeric TSField,
tsdb(# time timestamptz TSTime) with (orientation=TIMESERIES, period='1 hour', ttl='1 month') distribute by hash(model);
CREATE TABLE

tsdb=# select now();
              now
-------------------------------
 2022-05-25 15:28:38.520757+08
(1 row)
tsdb=# select relname, boundaries from pg_partition where parentid=(select oid from pg_class where relname='generator') order by boundaries ;
    relname     |         boundaries
----------------+----------------------------
 default_part_1 | {"2022-05-25 16:00:00+08"}
 default_part_2 | {"2022-05-25 17:00:00+08"}
 p1653505200    | {"2022-05-26 03:00:00+08"}
 p1653541200    | {"2022-05-26 13:00:00+08"}
 p1653577200    | {"2022-05-26 23:00:00+08"}
 ......

这样的使用方式，能够使得用户能够便捷快速的创建时序表。当然我们也支持在建表时用户手动指定分区边界的起始值。关于自动分区管理功能更详细的描述，可以参考《GaussDB(DWS) 分区自动管理介绍》。

tsdb=# select now();
              now
-------------------------------
 2022-05-31 20:36:09.700096+08
(1 row)

tsdb=# CREATE TABLE IF NOT EXISTS GENERATOR(
tsdb(# dynamo text TSTag,
tsdb(# manufacturer text TSTag,
tsdb(# model text TSTag,
tsdb(# location text TSTag,
tsdb(# ID bigint TSTag,
tsdb(# voltage numeric TSField,
tsdb(# power bigint TSTag,
tsdb(# frequency numeric TSField,
tsdb(# angle numeric TSField,
tsdb(# time timestamptz TSTime) with (orientation=TIMESERIES, period='1 day') distribute by hash(model)
tsdb-# partition by range(time)
tsdb-# (
tsdb(# PARTITION P1 VALUES LESS THAN('2022-05-30 16:32:45'),
tsdb(# PARTITION P2 VALUES LESS THAN('2022-05-31 16:56:12')
tsdb(# );
WARNING:  partition boundary is less than current time.
CREATE TABLE

tsdb=# select relname, boundaries from pg_partition where parentid=(select oid from pg_class where relname='generator') order by boundaries ;
   relname   |         boundaries
-------------+----------------------------
 p1          | {"2022-05-30 16:32:45+08"}
 p2          | {"2022-05-31 16:56:12+08"}
 p1654073772 | {"2022-06-01 16:56:12+08"}
 p1654160172 | {"2022-06-02 16:56:12+08"}
 ......

至此，我们已经建成了自己的第一张时序表，接下来我们会针对时序表的DDL各种操作进行详细的实践，帮助大家学好、用好时序数据库，更好的为用户的业务服务。