# 使用DWS分区自动管理功能降低电商和物联网行业数据分区维护成本
#### 场景介绍
在电商、物联网行业，用户常采用**分区列为时间**的分区表存储时间相关数据（如电商订单信息、物联网实时采集数据），以便于数据查询与维护。这类数据导入时，分区表需匹配对应时间的分区；但普通分区表无法自动创建新分区或清理过期分区，需维护人员定期手动操作，导致运维成本居高不下。
为解决该问题，DWS 推出**分区自动管理特性** ：通过**配置表级参数period、ttl即可开启功能**，实现分区的自动创建与过期分区的自动删除，既降低分区表维护成本，又优化查询性能。
此外，该功能不仅支持时间类型分区列，**还兼容INT、BIGINT、VARCHAR、TEXT等非时间类型分区列**，进一步拓展了自动分区功能的适用范围，提升了使用灵活性。
核心建表语法示例如下：
```
CREATE TABLE CPU1(
    id integer,
    IP text,
    time integer
) with (TTL='7 days',PERIOD='1 day', TIME_FORMAT='YYYYMMDD')     ---每天自动创建新分区， 当nowtime减去分区边界时间大于7天，该分区自动淘汰
partition by range(time)
(
    PARTITION P1 VALUES LESS THAN('20230213'),
    PARTITION P2 VALUES LESS THAN('20230215')
);
```
- **period**：设置自动创建分区的间隔时间，默认值为1 day，取值范围：1 hour \~ 100 years。例如period为1 day，则每过一天，就会创建新的分区。
- **ttl** ：设置自动淘汰分区的时间，取值范围：1 hour \~ 100 years。淘汰分区的策略是通过计算nowtime - 分区boundary time\> ttl，满足该条件的分区将被清理掉。**分区boundary** **（** **分区边界** **）** 指的是创建分区表时定义的某个分区对应的分区边界值，如以上语法所示，20230213就是分区P1的**boundary**，如果ttl是7 days，那么当nowtime为20230220时，则该分区被清理掉。

- **time_format:**
  当分区列的数据类型非标准timestamp，而为VARCHAR、TEXT、INT或BIGINT类型时，需要通过设置表级参数time_format指定分区列中的时间格式。该参数用于指示系统如何解析分区列中的时间值，以便进行自动分区管理。time_format选项仅当分区键为INT4/INT8/VARCHAR/TEXT，同时也指定period时才能成功设置。
  不同类型的分区列在 time_format 上的可选格式及限制如下：
  表1VARCHAR/TEXT 类型支持的时间格式元素 
  | 格式元素   | 说明                 | 示例输入值 |
  |:---|:---|:---|
  | YYYY | 四位数的年份（0000--9999） | 2024 |
  | MM    | 两位数的月份（01--12）         | 05    |
  | DD    | 两位数的日期（01--31）        | 17     |
  | HH24 | 两位数的小时（00--23）         | 14   |
  | MI   | 两位数的分钟（00--59）       | 32   |
  | SS     | 两位数的秒（00--59）         | 45    |
     
  ![](https://support.huaweicloud.com/bestpractice-dws/public_sys-resources/caution_3.0-zh-cn.png)
  - 精度支持到秒级。
  
  - 输入内容不能包含字母型元素（如 MONTH、AM/PM等）。
  
  - 时间格式必须从大到小排列（如 YYYYMMDDHH24MISS）。
   
  表2INT/BIGINT类型支持的时间格式元素 
  | 格式元素 | 说明                | 示例输入值 |
  |:---|:---|:---|
  | YYYY  | 四位数的年份（0000--9999） | 2024  |
  | MM      | 两位数的月份（01--12）     | 05  |
  | DD    | 两位数的日期（01--31）      | 17   |
  | HH24   | 两位数的小时（00--23）     | 14    |
     
  ![](https://support.huaweicloud.com/bestpractice-dws/public_sys-resources/caution_3.0-zh-cn.png)
  - 精度支持到小时级。
  
  - 输入内容不得包含非数字元素。
  
  - 时间格式必须从大到小排列（如 YYYYMMDDHH24）。
    
 
#### 约束限制
在使用分区管理功能时，需要满足如下约束：
- 不支持在小型机、加速集群上使用。
- 支持在8.1.3及以上集群版本中使用。
- 仅支持行存范围分区表、列存范围分区表、时序表以及冷热表。
- 分区键必须保持唯一性，其支持的数据类型包括TIMESTAMP、TIMESTAMPTZ、DATE，以及在9.1.0.200版本中新增的INT、BIGINT、VARCHAR和TEXT类型。
- 不支持存在maxvalue分区。
- (nowTime - boundaryTime) / period需要小于分区个数上限，其中nowTime为当前时间，boundaryTime为现有分区中最早的分区边界时间。
- period、ttl取值范围为1 hour \~ 100 years。另外，在兼容Teradata或MySQL的数据库中，分区键类型为date时，period不能小于1 day。
- 表级参数ttl不支持单独存在，必须要提前或同时设置period，并且要大于或等于period。
- 集群在线扩容期间，自动增加分区会失败，但是由于每次增分区时，都预留了足够的分区，所以不影响使用。
- time_format选项不支持SET修改。当period被RESET时（表示已经关闭自动分区，会报出提示），此时可以RESET此选项。
 
#### 自动分区创建规则
分区管理功能是和表级参数period、ttl绑定的，只要成功设置了表级参数period，即开启了自动创建新分区功能；成功设置了表级参数ttl，即开启了自动删除过期分区功能。**第一次自动创建分区或删除分区的时间为设置period或ttl后30秒**。
- **自动创建新分区**
  **分区自动管理每隔period的时间就会自动创建分区，每次创建一个或多个时间范围为period的新分区，以推进最大的分区边界时间**，保证其大于nowTime+30\*period。由于每次创建分区时，都动态地为未来时间创建了预留分区，所以只要有一次自动创建新分区成功，就可以保证在未来30个period的时间之内，都不会出现实时数据因为没有对应分区而导入失败的情况。
  图1自动创建分区示意图   
  ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000001500194885.png "点击放大") 
- **自动删除过期分区**
  边界时间早于nowTime-ttl的分区被认为是过期分区。分区自动管理每隔period的时间就会遍历检测所有分区，并删除其中的过期分区，如果所有的分区都是过期分区，则保留一个分区，并TRUNCATE该表。
  
 
#### 创建ECS
参见[自定义购买ECS](https://support.huaweicloud.com/usermanual-ecs/ecs_03_7002.html)购买。购买后，参见[登录Linux弹性云服务器](https://support.huaweicloud.com/usermanual-ecs/ecs_03_0134.html)进行登录。
![](https://support.huaweicloud.com/bestpractice-dws/public_sys-resources/notice_3.0-zh-cn.png)
创建ECS过程中，注意选择与后续的DWS在同一个区域、可用区和同一个VPC子网下，ECS的操作系统选择与gsql客户端（本例以CentOS 7.6为例），并选择以密码方式登录。
#### 创建集群
1. 在[DWS控制台](https://console.huaweicloud.com/dws)上创建集群，具体操作步骤请参考[创建DWS存算一体集群](https://support.huaweicloud.com/mgtg-dws/dws_01_0019.html)。
#### 使用gsql命令行客户端连接集群
1. 使用root用户远程登录到需要安装gsql的Linux主机，然后在Linux命令窗口，执行以下命令下载gsql客户端： 
   ```
   wget https://obs.cn-north-1.myhuaweicloud.com/dws/download/dws_client_8.1.x_redhat_x64.zip --no-check-certificate
   ```
   
   
2. 执行以下命令解压客户端工具。 
   ```
   cd <客户端存放路径> unzip dws_client_8.1.x_redhat_x64.zip
   ```
   其中：
   - \<客户端存放路径\>：请替换为实际的客户端存放路径。
   
   - dws_client_*8.1.x*_redhat_x64.zip：这是"RedHat x64"对应的客户端工具包名称，请替换为实际下载的包名。
   
   
   
   
3. 执行以下命令配置客户端。 
   ```
   source gsql_env.sh
   ```
   提示以下信息表示客户端已配置成功。
   ```
   All things done.
   ```
   
   
4. 执行以下命令，使用gsql客户端连接DWS集群中的数据库，其中password为用户创建集群时自定义的密码。 
   ```
   gsql -d gaussdb -p 8000 -h 192.168.0.86 -U dbadmin -W password -r
   ```
   显示如下信息表示gsql工具已经连接成功：
   ```
   gaussdb=>
   ```
   
   
 
#### 新创建分区表时指定period、ttl开启自动分区功能
新创建分区表时，分**指定分区** 和**不指定分区**两种场景：
- **新增分区表时指定分区** ：
  新建分区管理表时如果指定分区，则语法规则和建普通分区表相同，唯一的区别就是会指定表级参数**period、ttl**。
  **示例**：创建分区管理表CPU1，指定分区，分区列为时间类型timestamp。
  ```
  -- 时间类型
  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')
  );
  ```
  **示例**：创建分区管理表CPU2和CPU3并指定分区，分区列分别为INT、VARCHAR类型。
  ```
  -- INT类型
  CREATE TABLE CPU2(
      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 CPU3(
      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 范围，属于即将被淘汰的分区，因此不会继续向前（过去）补全分区。        |
     
  ![](https://support.huaweicloud.com/bestpractice-dws/public_sys-resources/caution_3.0-zh-cn.png)
  - cur_time表示当前系统时间。
  
  - period为自动分区的周期设置。
  
  - 自动补全逻辑确保分区在有效的时间窗口内完整存在，便于数据导入与查询。
  
  - 对于INT、BIGINT、VARCHAR、TEXT 类型的分区表，如果在建表时同时指定较大的TTL和较小的period，且手动指定的首个分区边界位于当前时间与cur_time - ttl之间，系统可能会根据自动分区补全规则，向前补齐TTL生命周期内缺失的历史分区。 例如，当TTL='100 years'、period='1 day' 时，可能触发大量历史分区自动创建，导致建表耗时显著增加。因此，不建议在建表阶段直接配置"超长TTL + 小粒度period"组合。TTL主要用于过期数据清理和控制有效生命周期，应结合实际数据保留周期合理设置。
    
  
  - 对于INT、BIGINT、VARCHAR、TEXT类型分区表，如果要避免在建表阶段因TTL回补历史分区而一次性创建大量分区，建议采用以下方式：
    1. 建表时先不设置TTL。
    
    2. 待表创建完成后，再通过ALTER TABLE ... SET (...)语法设置TTL（以及需要的PERIOD）开启自动分区管理。
    
    
    该方式可以避免在建表阶段向前补齐TTL生命周期内的大量历史分区，从而降低建表耗时。
    
    

- **新建分区表时不指定分区** ：
  **建分区管理表时可以只指定分区键不指定分区，此时将创建两个默认分区，这两个默认分区的分区时间范围均为period** 。其中，**第一个默认分区的边界时间是大于当前时间的第一个整时/整天/整周/整月/整年的时间**，具体选择哪种整点时间取决于period的最大单位；第二个默认分区的边界时间是第一个分区边界时间加period。假设当前时间是2023-02-17 16:32:45，各种情况的第一个默认分区的分区边界选择如下表：
  表3period参数说明 
  | 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 计算得出）。
  
  
  **示例**：创建分区管理表CPU4，不指定分区，分区列为timestamp时间类型。
  ```
  -- 时间类型
  CREATE TABLE CPU4(
      id integer,
      IP text,
      time timestamp
  ) with (TTL='7 days',PERIOD='1 day')
  partition by range(time);
  ```
  
  **示例**：创建分区管理表CPU5和CPU6不指定分区，分区列分别为INT和VARCHAR时间类型。
  ```
  -- INT类型
  CREATE TABLE CPU5(
      id integer,
      IP text,
      time integer
  ) with (TTL='7 days',PERIOD='1 day', TIME_FORMAT='YYYYMMDD')
  partition by range(time);
  -- VARCHAR类型
  CREATE TABLE CPU6(
      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);
  ```
  
 
#### 存量分区表通过ALTER TABLE RESET语法设置period、ttl来开启自动分区管理功能
该方式适用于给一张满足分区管理约束的普通分区表增加分区管理功能。
- 创建普通分区表CPU7：
  ```
  -- 时间类型
  CREATE TABLE CPU7(
      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 CPU7(
      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')
  );
  ```
  
- 同时开启自动创建和自动删除分区功能：
  ```
  ALTER TABLE CPU7 SET (PERIOD='1 day',TTL='7 days');
  ```
  
- 只开启自动创建分区功能：
  ```
  ALTER TABLE CPU7 SET (PERIOD='1 day');
  ```
  
- 只开启自动删除分区功能，如果没有提前开启自动创建分区功能，则开启失败：
  ```
  ALTER TABLE CPU7 SET (TTL='7 days');
  ```
  
- 通过修改period和ttl修改分区管理功能：
  ```
  ALTER TABLE CPU7 SET (TTL='10 days',PERIOD='2 days');
  ```
  
 
#### 查询分区信息
需要查询自动创建的分区情况，可以通过以下语法查询分区信息。
```
SELECT * FROM DBA_TAB_PARTITIONS WHERE table_name = 'CPU1';
```
#### 关闭分区自动管理
使用ALTER TABLE RESET语句可以删除表级参数period、ttl，即可关闭相应的分区管理功能。
![](https://support.huaweicloud.com/bestpractice-dws/public_sys-resources/note_3.0-zh-cn.png)
- 不能在存在ttl的情况下，单独删除period。
- 时序表不支持ALTER TABLE RESET。
 
- 同时关闭自动创建和自动删除分区功能：
  ```
  ALTER TABLE CPU1 RESET (PERIOD,TTL);
  ```
  
- 只关闭自动删除分区功能：
  ```
  ALTER TABLE CPU7 RESET (TTL);
  ```
  
- 只关闭自动创建分区功能，如果该表有ttl参数，则关闭失败：
  ```
  ALTER TABLE CPU7 RESET (PERIOD);
  ```
  
- 对于INT、BIGINT、VARCHAR和TEXT类型的分区表，需要根据提示关闭TIME_FORMAT选项：
  ```
  ALTER TABLE CPU7 RESET (TIME_FORMAT);
  ```