按自定义时间列冷热分离

设置表的冷热分界线

1. 设置自定义时间列及冷热分界线。

   • 方法一：建表时设置。

     创建表dt，设置冷热分离时间点为一天，按p2列冷热分离。

     CREATE TABLE dt (p1 integer, p2 bigint, p3 bigint, c1 varchar,  constraint pk primary key(p1, p2, p3)) WITH(COMPRESSION = 'ZSTD', CHS ='86400', CHS_L2 = 'storagetype=COLD', CHS_COLUMN = 'COLUMN=p2'); 

     参数说明

     • CHS：冷热分离时间点，单位为秒（s）。例如CHS为86400，代表自定义时间列的时间戳取值在86400秒（一天）前的数据会被自动归档到冷存储。

     • COMPRESSION：压缩算法。整张表生效。算法名称大小写不敏感。默认配置为none。

     • CHS_L2：配置第二层属性，一般配置存储属性：storagetype=COLD。

     • CHS_COLUMN：自定义时间列。

   • 方法二：建表时未开启冷热分离，通过ALTER TABLE语句添加相关属性。

     -- 已有数据表、建表时未开启冷热分离，例如：
     -- CREATE TABLE dt (p1 integer, p2 bigint, p3 bigint, c1 varchar,  constraint pk primary key(p1, p2, p3));
     
     -- 为表dt开启按自定义列冷热分离，设置冷热分离时间点为一天，按p2列冷热分离
     ALTER TABLE dt SET 'CHS' ='86400', 'CHS_L2' = 'storagetype=COLD', 'CHS_COLUMN' = 'COLUMN=p2';

2. 可选：修改表的冷热分界线和自定义时间列。

   • 修改冷热分界线。

     ALTER TABLE dt SET 'CHS'='1000';

   • 修改自定义时间列。

     ALTER TABLE dt SET 'CHS_COLUMN'='COLUMN=p3'; 

3. 可选：取消表的冷热分离。

   ALTER TABLE dt SET 'CHS'='', 'CHS_L2' = '', 'CHS_COLUMN'='';

   说明

   修改冷热分界线或取消冷热分离后，需要等待系统后台执行完compaction，数据才能从冷存储回到热存储中。如果需要数据立即回到热存储，请手动执行major_compact命令。

设置二级索引的冷热分界线

二级索引默认存储在一张表中，因此，二级索引可以像表一样进行冷热分离。

以上文创建的表dt为例，为表dt创建二级索引。

1. 创建二级索引并开启冷热分离。

   • 方法一：创建二级索引时设置自定义时间列及冷热分界线。

     CREATE INDEX idx on dt (c1) WITH(CHS = '86400', CHS_L2 = 'storagetype=COLD', CHS_COLUMN='COLUMN=p2');

     说明

     二级索引无法指定自定义时间列，CHS_COLUMN必须设置为主表的自定义时间列。

   • 方法二：创建二级索引时未设置，通过ALTER TABLE设置自定义时间列及冷热分界线。

     -- 已有二级索引、创建二级索引时未开启冷热分离，例如：
     -- CREATE INDEX idx on dt (c1);
     
     -- 为二级索引表开启冷热分离，设置冷热分离时间点为一天，按p2列冷热分离
     ALTER TABLE `dt.idx` SET 'CHS' = '86400', 'CHS_L2' = 'storagetype=COLD', 'CHS_COLUMN'='COLUMN=p2';

2. 可选：修改二级索引的冷热分界线和自定义时间列。

   • 修改冷热分界线。

     ALTER TABLE `dt.idx` SET 'CHS'='10000';

   • 修改自定义时间列。

     ALTER TABLE `dt.idx` SET 'CHS_COLUMN'='COLUMN=p2'; 

   说明

   二级索引表的表名格式为表名+点号（.）+二级索引名。由于二级索引表名中有特殊符号，使用时必须在表名前后添加反引号（`）进行转义。例如，原表名为test.idx，转义后的表名为`test.idx`。

3. 可选：取消二级索引的冷热分离。

   ALTER TABLE `dt.idx` SET 'CHS'='', 'CHS_L2' = '', 'CHS_COLUMN'='';

查询示例

• 随机查询Get

  -- p2为自定义时间列
  SELECT * FROM dt WHERE p1 = 10 AND p2 = 10;

• 范围查询Scan

  -- p2为自定义时间列
  SELECT * FROM dt WHERE p2 > 10 AND p2 < 1000;

  重要

  当执行SELECT语句被识别为低效查询时，宽表引擎默认不允许执行该类查询，同时会抛出异常。详细介绍及解决方法，请参见SELECT。

• 通过HINT仅查询热数据

  SELECT /*+ _l_hot_only_(true) */ * FROM dt WHERE p1>1;