Hologres:Event time column (segment key)

Overview

In Hologres V0.9, the name of the segment_key property is renamed event_time_column by default. However, the segment_key property can still be used in Hologres V0.9 and earlier.

Event time columns are applicable to the following scenarios:

• Queries that contain range filter conditions, including equivalent conditions.

• UPDATE operations based on primary keys.

-- Syntax supported in Hologres V2.1 and later
CREATE TABLE <table_name> (...) WITH (event_time_column = '[<columnName>[,...]]');

-- Syntax supported in all Hologres versions
BEGIN;
CREATE TABLE <table_name> (...);
call set_table_property('<table_name>', 'event_time_column', '[<columnName> [,...]]');
COMMIT;

Usage notes

• We recommend that you configure the columns where data monotonically increases or decreases, such as timestamp columns, as event time columns. The event_time_column property is applicable to columns that are strongly correlated with time, such as log and traffic columns. Appropriate setting of this property can improve query performance. After multiple files are merged, if data in the event time column is unordered, the data in each file has no distinguishing features and remains unordered. In this case, the data filtering effect is poor.

• If a table does not contain columns where data monotonically increases or decreases, you can add an update_time column and use it as the event time column. Each time you perform the UPSERT operation, the current time is written to the update_time column.

• Data queries based on event time columns follow the leftmost matching principle. Therefore, we recommend that you do not specify multiple event time columns for a table. Otherwise, data queries based on the event time columns cannot be accelerated in some scenarios. In most cases, we recommend that you specify one or two event time columns for a table.

Limits

• The event time columns of a table must meet the NOT NULL constraint. In Hologres V1.3.20 to V1.3.27, you can specify a column that contains null values as an event time column. In Hologres V1.3.28 and later, you cannot specify a column that contains null values as an event time column. This is because if an event time column contains null values, data accuracy may be adversely affected. If you must specify a column that contains null values as an event time column based on your business requirements, you can add the following SET command before the CREATE TABLE statement:

  set hg_experimental_enable_nullable_segment_key = true;

  You can execute SQL statements to check whether event time columns contain null values. Sample statements:

  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;

• You cannot modify the event_time_column property. If you want to modify the property, create another table.

• You cannot configure the event_time_column property for a row-oriented table.

• By default, the first non-null column of the TIMESTAMP or TIMESTAMPTZ data type in a column-oriented table is used as an event time column. If such a column does not exist, the first non-null column of the DATE data type is used as an event time column by default. In versions earlier than Hologres V0.9, no event time column is specified for a table by default.

• The columns of DECIMAL, NUMERIC, FLOAT, DOUBLE, ARRAY, JSON, JSONB, BIT, and MONEY and other complex data types are not supported.

How it works

Examples

• Configure a column as the event time column when you create a table.

  • Syntax supported in Hologres V2.1 and later:

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

  • Syntax supported in all Hologres versions:

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

  You can query the execution plan by executing the EXPLAIN statement. If the execution plan contains Segment Filter, the query hits the event time column.

• Configure multiple columns as event time columns when you create a table.

  • Syntax supported in Hologres V2.1 and later:

    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')
    ;
    
    -- The query cannot hit the event time columns.
    SELECT * FROM tbl_segment_test_2 WHERE b > '2022-09-05 10:24:54+08';
    
    -- The query hits the event time columns.
    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';

  • Syntax supported in all Hologres versions:

    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')
    ;
    
    -- The query cannot hit the event time columns.
    SELECT * FROM tbl_segment_test_2 WHERE b > '2022-09-05 10:24:54+08';
    
    -- The query hits the event time columns.
    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';

References

For more information about how to configure table properties based on business query scenarios, see Guide on scenario-specific table creation and tuning.