使用DWS分区自动管理功能降低电商和物联网行业数据分区维护成本

场景介绍

对于分区列为时间的分区表，分区自动管理功能可以自动创建新分区和删除过期分区，降低分区表的维护成本，改善查询性能。为了便于查询和维护数据，用户通常使用分区列为时间的分区表来存储时间相关的数据，例如电商的订单信息、 物联网采集的实时数据。这些时间相关的数据导入分区表时，需要保证分区表要有对应时间的分区，由于普通的分区表不会自动创建新的分区和删除过期的分区，所以维护人员需要定期创建新分区和删除过期分区，提高了运维成本。

为解决上述问题，DWS引入了分区自动管理特性。可通过设置表级参数period、ttl开启分区自动管理功能，使分区表可以自动创建新分区和删除过期分区，降低分区表的维护成本，改善查询性能。

在此基础上，分区自动管理功能不仅支持时间类型分区列，也支持INT、BIGINT、VARCHAR、TEXT等非时间类型分区列，进一步提升了自动分区功能的适用范围和灵活性。

period：设置自动创建分区的间隔时间，默认值为1 day，取值范围：1 hour ~ 100 years。

ttl：设置自动淘汰分区的时间，取值范围：1 hour ~ 100 years。淘汰分区的策略是通过计算nowtime - 分区boundary > ttl，满足该条件的分区将被清理掉。

time_format:

当分区列为VARCHAR、TEXT、INT或BIGINT类型时，需要通过设置表级参数time_format指定分区列中的时间格式。该参数用于指示系统如何解析分区列中的时间值，以便进行自动分区管理。time_format选项仅当分区键为INT4/INT8/VARCHAR/TEXT，同时也指定period时才能成功设置。

不同类型的分区列在 time_format 上的可选格式及限制如下：

• 精度支持到秒级。
• 输入内容不能包含字母型元素（如 MONTH、AM/PM等）。
• 时间格式必须从大到小排列（如 YYYYMMDDHH24MISS）。

• 精度支持到小时级。
• 输入内容不得包含非数字元素。
• 时间格式必须从大到小排列（如 YYYYMMDDHH24）。

自动分区创建规则

• 自动创建新分区

  分区自动管理每隔period的时间就会自动创建分区，每次创建一个或多个时间范围为period的新分区，以推进最大的分区边界时间，保证其大于nowTime+30*period。由于每次创建分区时，都动态地为未来时间创建了预留分区，所以只要有一次自动创建新分区成功，就可以保证在未来30个period的时间之内，都不会出现实时数据因为没有对应分区而导入失败的情况。

  图1 自动创建分区示意图
  
• 自动删除过期分区

  边界时间早于nowTime-ttl的分区被认为是过期分区。分区自动管理每隔period的时间就会遍历检测所有分区，并删除其中的过期分区，如果所有的分区都是过期分区，则保留一个分区，并TRUNCATE该表。

约束限制

创建ECS

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

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

创建集群

1. 在DWS控制台上创建集群，具体操作步骤请参考创建DWS存算一体集群。

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

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

   1	wget https://obs.ap-southeast-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客户端连接DWS集群中的数据库，其中password为用户创建集群时自定义的密码。

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

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

   1

   gaussdb=>

分区自动管理

分区管理功能是和表级参数period、ttl绑定的，只要成功设置了表级参数period，即开启了自动创建新分区功能；成功设置了表级参数ttl，即开启了自动删除过期分区功能。第一次自动创建分区或删除分区的时间为设置period或ttl后30秒。

有如下两种开启分区管理功能的方式：

• 建表时指定period、ttl。

  该方式适用于新建分区管理表时使用。新建分区管理表有两种语法：一种是建表时指定分区，另一种是建表时不指定分区。

  建分区管理表时如果指定分区，则语法规则和建普通分区表相同，唯一的区别就是会指定表级参数period、ttl。

  示例：创建分区管理表CPU1，指定分区。

  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 26 27 28 29 30 31 32 33 34 35

  -- 时间类型 CREATE TABLE CPU1( id integer, IP text, time timestamp ) with (TTL='7 days',PERIOD='1 day') partition by range(time) ( PARTITION P1 VALUES LESS THAN('2023-02-13 16:32:45'), PARTITION P2 VALUES LESS THAN('2023-02-15 16:48:12') ); -- INT类型 CREATE TABLE CPU1( id integer, IP text, time integer ) with (TTL='7 days',PERIOD='1 day', TIME_FORMAT='YYYYMMDD') partition by range(time) ( PARTITION P1 VALUES LESS THAN('20230213'), PARTITION P2 VALUES LESS THAN('20230215') ); -- VARCHAR类型 CREATE TABLE CPU1( id integer, IP text, time varchar ) with (TTL='7 days',PERIOD='1 day', TIME_FORMAT='YYYY-MM-DD HH24:MI:SS') partition by range(time) ( PARTITION P1 VALUES LESS THAN('2023-02-13 16:32:45'), PARTITION P2 VALUES LESS THAN('2023-02-15 16:48:12') );

  对于INT、BIGINT、VARCHAR和TEXT类型的分区表，启用自动分区功能时，系统会根据表定义时设置的 ttl（过期周期）选项及已存在的最小分区边界（min_bound），自动补全ttl范围内缺失的分区。具体补全规则如下：

  根据最小分区值min_bound与当前时间cur_time之间的关系，自动补全行为分为以下几种情况：

  条件	自动补全行为说明
  min_bound > cur_time + 29 * period	当前已存在的最小分区边界足够大，系统认为不需要再向后补全分区，不进行自动建分区。
  min_bound > cur_time 且 min_bound < cur_time + 29 * period	系统将以 period 为步长，向前补全分区，直到最小分区边界小于 cur_time - ttl。
  min_bound < cur_time 且 min_bound > cur_time - ttl	系统将以 period 为步长，向前补全分区，直到最小分区边界小于 cur_time - ttl。
  min_bound < cur_time - ttl	当前最小分区已早于 ttl 范围，属于即将被淘汰的分区，因此不会继续向前补全分区。

  

  • cur_time 表示当前系统时间。
  • period 为自动分区的周期设置。
  • 自动补全逻辑确保分区在有效的时间窗口内完整存在，便于数据导入与查询。

  建分区管理表时可以只指定分区键不指定分区，此时将创建两个默认分区，这两个默认分区的分区时间范围均为period。其中，第一个默认分区的边界时间是大于当前时间的第一个整时/整天/整周/整月/整年的时间，具体选择哪种整点时间取决于period的最大单位；第二个默认分区的边界时间是第一个分区边界时间加period。假设当前时间是2023-02-17 16:32:45，各种情况的第一个默认分区的分区边界选择如下表：

  表3 period参数说明

  period	period最大单位	第一个默认分区的分区边界
  1hour	Hour	2023-02-17 17:00:00
  1day	Day	2023-02-18 00:00:00
  1month	Month	2023-03-01 00:00:00
  13months	Year	2024-01-01 00:00:00

  对于INT、BIGINT、VARCHAR和TEXT类型的分区表，当未预先定义任何分区时，系统在启用自动分区管理功能后将按照以下规则自动创建初始分区：

  • 向前补全 2 个分区（相对于当前时间）。
  • 向后补全 ttl / period 个分区（根据生命周期 ttl 和分区周期 period 计算得出）。

  创建分区管理表CPU2，不指定分区：

  1 2 3 4 5 6

  CREATE TABLE CPU2( id integer, IP text, time timestamp ) with (TTL='7 days',PERIOD='1 day') partition by range(time);

• 使用ALTER TABLE RESET的方式设置period、ttl。

  该方式适用于给一张满足分区管理约束的普通分区表增加分区管理功能。

  • 创建普通分区表CPU3：

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

    -- 时间类型 CREATE TABLE CPU3( id integer, IP text, time timestamp ) partition by range(time) ( PARTITION P1 VALUES LESS THAN('2023-02-14 16:32:45'), PARTITION P2 VALUES LESS THAN('2023-02-15 16:56:12') ); -- VARCHAR类型 CREATE TABLE CPU3( id integer, IP text, time varchar ) partition by range(time) ( PARTITION P1 VALUES LESS THAN('2023-02-13 16:32:45'), PARTITION P2 VALUES LESS THAN('2023-02-15 16:48:12') );

  • 同时开启自动创建和自动删除分区功能：

    1	ALTER TABLE CPU3 SET (PERIOD='1 day',TTL='7 days');

  • 只开启自动创建分区功能：

    1	ALTER TABLE CPU3 SET (PERIOD='1 day');

  • 只开启自动删除分区功能，如果没有提前开启自动创建分区功能，则开启失败：

    1	ALTER TABLE CPU3 SET (TTL='7 days');

  • 通过修改period和ttl修改分区管理功能：

    1	ALTER TABLE CPU3 SET (TTL='10 days',PERIOD='2 days');

• 关闭分区管理功能。

  使用ALTER TABLE RESET语句可以删除表级参数period、ttl，即可关闭相应的分区管理功能。

  

  • 不能在存在ttl的情况下，单独删除period。
  • 时序表不支持ALTER TABLE RESET。
  • 同时关闭自动创建和自动删除分区功能：

    1	ALTER TABLE CPU1 RESET (PERIOD,TTL);

  • 只关闭自动删除分区功能：

    1	ALTER TABLE CPU3 RESET (TTL);

  • 只关闭自动创建分区功能，如果该表有ttl参数，则关闭失败：

    1	ALTER TABLE CPU3 RESET (PERIOD);

  • 对于INT、BIGINT、VARCHAR和TEXT类型的分区表，需要根据提示关闭TIME_FORMAT选项：

    1	ALTER TABLE CPU3 RESET (TIME_FORMAT);