快速创建时序表

场景介绍

时序表继承普通表的行存和列存语法，降低了用户学习成本，易理解和使用；

时序表具备数据生命周期管理的能力，每天各种维度的数据爆炸式增长，需要定期给表增加新的分区，避免新数据无法存储。而对于很久之前的数据，其价值较低且不经常访问，可以定期删除无用的数据。因此时序表需要具备定时增加分区和定时删除分区的能力。

本实践主要讲解如何快速创建适合自己业务的时序表，并对时序表进行分区管理，从而真正发挥时序表的优势。将对应的列指定为合适的类型，能够帮助我们更好的提高导入、查询等场景的性能，让业务场景运行的更加高效。如下图所示，以发电机组数据采样为例：

图1 发电机组数据采样示意图

基本流程

本实践预计时长：30分钟，基本流程如下：

1. 创建ECS。
2. 创建IoT数仓。
3. 使用gsql命令行客户端连接集群。
4. 创建时序表。

创建ECS

参见自定义购买弹性云服务器购买。购买后，参见登录Linux弹性云服务器进行登录。

创建ECS过程中，注意选择与后续的IoT数仓在同一个区域、可用区和同一个VPC子网下，ECS的操作系统选择与gsql客户端（本例以CentOS 7.6为例），并选择以密码方式登录。

创建IoT数仓

1. 登录华为云管理控制台。
2. 在“服务列表”中，选择“大数据 > 数据仓库服务”，单击右上角“创建数据仓库集群”。
3. 参见表1进行参数配置。

   表1 软件配置

   参数名称	配置方式
   区域	选择“华北-北京四”。 说明： 本指导以“华北-北京四”为例进行介绍，如果您需要选择其他区域进行操作，请确保所有操作均在同一区域进行。 请确保DWS跟ECS在同一个区域、可用区和同一个VPC子网下。
   可用分区	可用区2
   产品类型	IoT数仓
   计算类型	弹性云服务器
   存储类型	SSD云盘
   CPU架构	X86
   节点规格	dwsx2.rt.2xlarge.m6（8 vCPU | 64GB | 100~4,000 GB SSD） 说明： 如规格售罄，可选择其他可用区或规格。
   热数据存储	200 GB / 节点
   节点数量	3
   集群名称	dws-demo01
   管理员用户	dbadmin
   管理员密码	用户自定义
   确认密码	再次输入自定义的管理员密码
   数据库端口	8000
   虚拟私有云	vpc-default
   子网	subnet-default(192.168.0.0/24) 须知： 请确保与ECS在同一个VPC。
   安全组	自动创建安全组
   公网访问	现在购买
   企业项目	default
   高级配置	默认配置

4. 信息核对无误，单击“立即购买”，单击“提交”。
5. 等待约10分钟，待集群创建成功后，单击集群名称进入“基本信息”，在“网络”区域，单击安全组名称，确认安全组规则已添加，以IP为192.168.0.x的客户端网段为例（本例gsql所在ECS的内网IP为192.168.0.90），需要添加192.168.0.0/24，端口为8000的安全组规则。
6. 返回到集群“基本信息”界面，记录下“内网IP”。

   

使用gsql命令行客户端连接集群

1. 使用root用户远程登录到需要安装gsql的Linux主机，然后在Linux命令窗口，执行以下命令下载gsql客户端：

   1	wget https://obs.cn-north-1.myhuaweicloud.com/dws/download/dws_client_8.1.x_redhat_x64.zip --no-check-certificate

2. 执行以下命令解压客户端工具。

   1	cd <客户端存放路径> unzip dws_client_8.1.x_redhat_x64.zip

   其中：

   • <客户端存放路径>：请替换为实际的客户端存放路径。
   • dws_client_8.1.x_redhat_x64.zip：这是“RedHat x64”对应的客户端工具包名称，请替换为实际下载的包名。

3. 执行以下命令配置客户端。

   1	source gsql_env.sh

   提示以下信息表示客户端已配置成功。

   1	All things done.

4. 执行以下命令，使用gsql客户端连接GaussDB(DWS)集群中的数据库，其中password为用户创建集群时自定义的密码。

   1	gsql -d gaussdb -p 8000 -h 192.168.0.86 -U dbadmin -W password -r

   显示如下信息表示gsql工具已经连接成功：

   1

   gaussdb=>

创建时序表

1. 以发电机组的场景作为示例，创建一张存储发电机组采样数据的时序表GENERATOR：

   1 2 3 4 5 6 7 8 9 10 11

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

2. 查询当前时间：

   1 2 3 4 5

   select now(); now ------------------------------- 2022-05-25 15:28:38.520757+08 (1 row)

3. 查询默认的分区与分区边界：

   1 2 3 4 5 6 7 8 9

   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-25 18:00:00+08"} p1653541200 | {"2022-05-25 19:00:00+08"} p1653577200 | {"2022-05-25 20:00:00+08"} ......

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

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

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

   

   • 写建表语句时，对于tag列的顺序，可以适当进行优化，将唯一性（distinct值）较高的列尽量写在前面，这样对于时序场景的性能有一些提升。
   • 创建时序表时需要指定表级参数orientation属性设置为timeseries。
   • 时序表不需要手动指定DISTRIBUTE BY和PARTITION BY, 默认按照所有tag列分布，且分区健默认为tstime指定的时间列。
   • 对于create table like语法，需要自动从源表中继承列名和对应的kv_type类型。因此如果源表是非时序表，新表是时序表，对应的列的kv_type类型无法确定，则无法创建成功。
   • 时序表必须指定一个时间属性（TSTime），且只能指定一个，且TSTime类型的列不能被删除。至少存在一个TSTag和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的值。

创建时序表（手动设置分区边界）

1. 手动指定分区边界的起始值，例如手动设置默认的分区边界时间P1为“2022-05-30 16:32:45”、P2为“2022-05-31 16:56:12”，创建时序表GENERATOR1如下：

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

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

2. 查询当前时间：

   1 2 3 4

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

3. 查询分区以及分区边界：

   1 2 3 4 5 6 7 8

   SELECT relname, boundaries FROM pg_partition where parentid=(SELECT oid FROM pg_class where relname='generator1') 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"} ......