Hologres中为表设置分段键Event_time_column_实时数仓 Hologres(Hologres)-阿里云帮助中心

如果您需对大规模数据集使用基于主键的UPDATE或含范围过滤条件的查询，可以考虑为表设置Event Time Column（Segment Key），系统将数据文件基于Event Time Column范围排序后进行合并，减少文件之间的重叠，使得查询时能够过滤掉尽可能多的文件，从而提升查询效率。合理地应用Event Time Column有助于提高数据库的处理效率、查询速度和整体性能，本文为您介绍在Hologres中为表设置分段键Event_time_column。

Event Time Column介绍

Event_time_column原名为Segment Key，在Hologres V0.9版本默认改名为Event_time_column，Segment Key依旧向下兼容使用。

Segment Key主要适用的场景如下：

含范围过滤条件（包括等值条件）的查询场景。
基于主键的UPDATE。

Event_time_column在设置时，需要在建表时指定，语法如下：

-- Hologres V2.1版本起支持的语法
CREATE TABLE <table_name> (...) WITH (event_time_column = '[<columnName>[,...]]');

-- 所有版本支持的语法
BEGIN;
CREATE TABLE <table_name> (...);
call set_table_property('<table_name>', 'event_time_column', '[<columnName> [,...]]');
COMMIT;

参数说明：

参数	说明
table_name	设置分段键的表名称。
columnName	设置分段键的列。

使用建议

Event_time_column适用于数据为单调递增或单调递减的有序字段，例如时间戳字段。非常适用于日志、流量等和时间强相关的数据，合理设置可极大提升性能。完全无序的Event_time_column会导致合并之后每个文件缺乏区分度，达不到任何文件过滤效果。
如果表不存在明显的单调递增或单调递减的字段，可以选择额外扩充一列update_time，每次UPSERT时将当前时间写入该新增字段。
Event_time_column具备左匹配原则，因此不建议将多个字段设置为Event_time_column，否则使得查询场景受限，达不到加速效果，一般情况建议选择设置两个或者两个以内字段设置为Event_time_column。

使用限制

Event_time_column必须为not nullable的列或者列组合，可以不设置，但不能设置为空。Hologres V1.3.20~1.3.27版本支持Event_time_column对应列的约束为nullable，从V1.3.28版本开始不支持Event_time_column对应列的约束为nullable，为nullable的Event_time_column可能会影响数据正确性，如果业务有强需求设置Event_time_column为null，可以在SQL前添加如下参数：

set hg_experimental_enable_nullable_segment_key = true;

您可以使用如下SQL检查当前数据库是否有属性为nullable的Event_time_column（Segment Key）：

WITH t_base AS (
    SELECT
        *
    FROM
        hologres.hg_table_info
    WHERE
        collect_time::date = CURRENT_DATE
),
t1 AS (
    SELECT
        db_name,
        schema_name,
        table_name,
        jsonb_array_elements(table_meta::jsonb -> 'columns') cols
    FROM
        t_base
),
t2 AS (
    SELECT
        db_name,
        schema_name,
        table_name,
        cols ->> 'name' col_name
    FROM
        t1
    WHERE
        cols -> 'nullable' = 'true'::jsonb
),
t3 AS (
    SELECT
        db_name,
        schema_name,
        table_name,
        regexp_replace(regexp_split_to_table(table_meta::jsonb ->> 'segment_key', ','), ':asc|:desc$', '') segment_key_col
    FROM
        t_base
    WHERE
        table_meta::jsonb -> 'segment_key' IS NOT NULL
)
SELECT
    CURRENT_DATE,
    t3.db_name,
    t3.schema_name,
    t3.table_name,
    jsonb_build_object('nullable_segment_key_column', string_agg(t3.segment_key_col, ',')) as nullable_segment_key_column
FROM
    t2,
    t3
WHERE
    t3.db_name = t2.db_name
    AND t3.schema_name = t2.schema_name
    AND t3.table_name = t2.table_name
    AND t2.col_name = t3.segment_key_col
GROUP BY
    t3.db_name,
    t3.schema_name,
    t3.table_name;

不支持修改Event_time_column，如需修改请重新建表。
行存表不能设置Event_time_column。
列存表默认将表中的第一个非空的Timestamp或Timestamptz类型字段作为Event_time_column，如果不存在这样的字段，则默认将第一个非空的Date类型字段作为Event_time_column（Hologres V0.9之前的版本默认为空）。
不支持Decimal、Numeric、Float、Double、Array、Json、Jsonb、Bit、Money及其他复杂数据类型。

技术原理

以一个Shard为例，数据写入时的步骤如下图所示：分段键技术原理

在一个Shard内，数据会先写入至一个内存表，为了保证写入效率最高，会使用Append Only的方式写入。内存表有一定的大小，当内存表写满之后，系统会将内存表中的数据逐渐异步Flush到文件。
写入时为了追求写入性能，都为Append Only方式写入，文件数会越来越多。因此系统会在后台周期将文件进行合并。如果设置了Event_time_column（Segment Key），系统将文件基于Segment Key范围排序后，选择Segment Key范围相邻的文件进行合并，减少文件之间的重叠，这样就使得查询时能够过滤掉尽可能多的文件，从而提升查询效率。
文件基于Segment Key排序，因此Segment Key也具有左匹配原则，即a、b、c三个字段设置了Segment Key，查询时查a,b,c或者查a,b可以命中Segment Key，如果查a,c则只有a可以命中Segment Key，查b,c则无法命中。

从以上的介绍中可以看出Segment Key可以对以下场景进行加速：

含范围过滤条件（包括等值条件）的查询场景。
如果查询字段设置为Segment Key，那么Hologres在扫描数据时，会将范围查询条件同文件内列的统计信息（min/max）进行匹配，快速过滤出所需的文件，加速查询。
基于主键的UPDATE。
Hologres的UPDATE命令原理是由DELETE命令和INSERT命令组合实现。在基于主键的UPDATE或INSERT ON CONFLICT(UPSERT)场景中，会先根据主键找到目标表（旧数据）的Segment Key值，再根据旧数据的Segment Key找到旧数据所在文件，最终定位旧数据所在位置进而标记为DELETE。如果设置了合理的Segment Key，那么就会快速定位到旧数据的文件，提高写入性能。相反，如果这张列存表没有配置Segment Key、Segment Key配置了不合理的字段或者Segment Key对应的字段在写入时没有与时间有强相关性（比如基本乱序），那在查找旧数据时需要扫描的文件将会非常多，不仅会有大量的IO操作，而且会大量占用CPU，影响写入性能和整个实例的负载。

使用示例

在建表时创建一个Event_time_column。

V2.1版本起支持的建表语法：

CREATE TABLE tbl_segment_test (
    a int NOT NULL,
    b timestamptz NOT NULL
)
WITH (
    event_time_column = 'b'
);

INSERT INTO tbl_segment_test values
(1,'2022-09-05 10:23:54+08'),
(2,'2022-09-05 10:24:54+08'),
(3,'2022-09-05 10:25:54+08'),
(4,'2022-09-05 10:26:54+08');

EXPLAIN SELECT * FROM tbl_segment_test WHERE b > '2022-09-05 10:24:54+08';

所有版本支持的建表语法：

BEGIN;
CREATE TABLE tbl_segment_test (
    a int NOT NULL,
    b timestamptz NOT NULL
    );
CALL set_table_property('tbl_segment_test', 'event_time_column', 'b');
COMMIT;

INSERT INTO tbl_segment_test VALUES 
(1,'2022-09-05 10:23:54+08'),
(2,'2022-09-05 10:24:54+08'),
(3,'2022-09-05 10:25:54+08'),
(4,'2022-09-05 10:26:54+08');

EXPLAIN SELECT * FROM tbl_segment_test WHERE b > '2022-09-05 10:24:54+08';

同时通过查询执行计划（explain SQL），如果出现Segment Filter，说明有查询命中Event_time_column。分段键执行计划

建表时创建多个Event_time_column。