按自定义时间列冷热分离

Lindorm宽表引擎支持按自定义时间列归档冷热数据。购买容量型云存储后,您可以指定表或二级索引中的某个时间列作为冷热分离的依据,将数据分别存储于不同的介质中,有效提升热数据查询效率,降低冷数据存储成本。本文介绍按自定义时间列冷热分离的具体操作步骤及相关注意事项。

前提条件

注意事项

  • 仅支持Lindorm SQL,不支持HBase兼容使用方式。

  • 当自定义时间列中的值越过设置的冷热分界线后,整行数据会被归档至冷存储中。

  • 自定义时间列的设置需遵循以下规则:

    • 自定义时间列必须为主键。

    • 自定义时间列不能作为主键第一列。

    • 自定义时间列必须为BIGINT类型,格式为Unix时间戳,单位为毫秒(ms)。如果指定列不存在、数据没有写入或写入的数据类型不正确,数据则无法冷热分离,将被全部写入热存储。

操作步骤

设置表的冷热分界线

  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'='';

数据写入

冷热分离的表与普通表的数据写入方式完全一致,数据会先存储在热存储(标准型/性能型)中。随着时间的推移,如果一行数据满足当前时间-时间列值>CHS设置的值条件,则会在执行compaction时被归档到冷存储中。

数据查询

由于冷热数据都在同一张表中,因此用户所有的查询操作都只需在一张表内进行。在查询时,建议通过自定义时间列来限定数据查询的时间范围,系统将会根据指定的时间范围决定查询模式,即仅查询热存储区、仅查询冷存储区或同时查询冷存储区和热存储区。如果查询时未限定时间范围,则会导致查询命中冷数据,进而使得查询吞吐受到冷存储的限制。详细内容,请参见冷存储介绍

如果查询条件中无法设置自定义时间列,也可以使用HINT设置_l_hot_only_参数的属性来避免查询冷数据。

查询示例

  • 随机查询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;

最佳实践

在车联网场景中,一般将表的主键设计为vin(车架号)和时间戳列,并将时间戳列定义为冷热分离的自定义时间列。在查询一辆车某段时间内的数据时,系统可以根据查询条件定位到是否需要查询冷数据,或是否仅查询热数据。

USE test;
CREATE TABLE dt (
  vin varchar, ts bigint, c1 varchar, c2 varchar, constraint pk primary key(vin, ts)) WITH
(COMPRESSION = 'ZSTD', CHS ='86400', CHS_L2 = 'storagetype=COLD', CHS_COLUMN = 'COLUMN=ts'); // 时间列必须是主键
-- 查询某辆车一段时间的数据
SELECT * FROM dt WHERE vin='xxxx' AND ts > 1675065122000 AND ts < 1675975222000;

常见问题

  • Q:如果更新了原有的冷数据,更新后的数据还是冷数据吗?

    A:如果更新的冷数据不是自定义时间列,那么更新后的数据依旧是冷数据;如果更新的是自定义时间列中的数据,则需要根据新写入的时间内容来重新划分冷热数据。假设一个表的主键列为p1,p2, 非主键列为c1,c2,某一行为p1=row1, p2=2023.1.28日, c1=”c1“, c2=”c2“,冷热分界线CHS=1天,当前时间为2023.1.30日,此时这一行为冷数据。如果更新这一行中的c1,c2列的值,那么这一行仍然为冷数据;如果将这一行中p2的值更新为2023.1.30日,那么这一行将变为热数据,直到2023年2月1日(2天后),才会重新变为冷数据。

    说明

    已设置为主键的自定义时间列中的值无法更新。

  • Q:如果在一行数据中未写入自定义时间列,那么这一行数据还会冷热分离吗?

    A:不会。自定义时间列是系统对数据冷热分离存储的依据。如果一行数据未写入时间,那么这行数据会被保留在热存储区。