本文分享自华为云社区《GaussDB(DWS)带你走近IoT时代-时序表建表最佳实践-云社区-华为云》，作者：AndyCao。
创建第一张时序表我们首先来介绍一下如何创建一张时序表
语法格式
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 TABLEtsdb=# 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 TABLEtsdb=# 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各种操作进行详细的实践，帮助大家学好、用好时序数据库，更好的为用户的业务服务。
点击下方，第一时间了解华为云新鲜技术~
华为云博客_大数据博客_AI博客_云计算博客_开发者中心-华为云
