全部產品
Search
文件中心

Hologres:Event Time Column(Segment Key)

更新時間:Jun 30, 2024

如果您需對大規模資料集使用基於主鍵的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為例,資料寫入時的步驟如下圖所示:分段鍵技術原理

  1. 在一個Shard內,資料會先寫入至一個記憶體表,為了保證寫入效率最高,會使用Append Only的方式寫入。記憶體表有一定的大小,當記憶體表寫滿之後,系統會將記憶體表中的資料逐漸非同步Flush到檔案。

  2. 寫入時為了追求寫入效能,都為Append Only方式寫入,檔案數會越來越多。因此系統會在後台周期將檔案進行合并。如果設定了Event_time_column(Segment Key),系統將檔案基於Segment Key範圍排序後,選擇Segment Key範圍相鄰的檔案進行合并,減少檔案之間的重疊,這樣就使得查詢時能夠過濾掉儘可能多的檔案,從而提升查詢效率。

  3. 檔案基於Segment Key排序,因此Segment Key也具有左匹配原則,即abc三個欄位設定了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命令組合實現。在基於主鍵的UPDATEINSERT 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。

    • V2.1版本起支援的建表文法:

      CREATE TABLE tbl_segment_test_2 (
          a int NOT NULL,
          b timestamptz NOT NULL
      )
      WITH (
          event_time_column = 'a,b'
      );
      
      INSERT INTO tbl_segment_test_2 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')
      ;
      
      --不可命中segment key
      SELECT * FROM tbl_segment_test_2 WHERE b > '2022-09-05 10:24:54+08';
      
      --可命中segment key
      SELECT * FROM tbl_segment_test_2 WHERE a = 3 and b > '2022-09-05 10:24:54+08';
      SELECT * FROM tbl_segment_test_2 WHERE a > 3 and b < '2022-09-05 10:26:54+08';
      SELECT * FROM tbl_segment_test_2 WHERE a > 3 and b > '2022-09-05 10:24:54+08';
    • 所有版本支援的建表文法:

      BEGIN;
      CREATE TABLE tbl_segment_test_2 (
          a int NOT NULL,
          b timestamptz NOT NULL
          );
      CALL set_table_property('tbl_segment_test_2', 'event_time_column', 'a,b');
      COMMIT;
      
      INSERT INTO tbl_segment_test_2 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')
      ;
      
      --不可命中segment key
      SELECT * FROM tbl_segment_test_2 WHERE b > '2022-09-05 10:24:54+08';
      
      --可命中segment key
      SELECT * FROM tbl_segment_test_2 WHERE a = 3 and b > '2022-09-05 10:24:54+08';
      SELECT * FROM tbl_segment_test_2 WHERE a > 3 and b < '2022-09-05 10:26:54+08';
      SELECT * FROM tbl_segment_test_2 WHERE a > 3 and b > '2022-09-05 10:24:54+08';

相關文檔

根據業務查詢情境設定合適的表屬性指南,請參見情境化建表調優指南