Hologres:Supported functions for incremental refresh

Syntax

hg_id_encoding_int4(<user_id>, '<mapping_tablename>')
hg_id_encoding_int8(<user_id>, '<mapping_tablename>')

Arguments

Behavior

Writes new UID-to-integer mappings to the user mapping table. If a UID already exists in the table, the existing mapping is preserved and no duplicate is inserted.

Usage notes

• Supported in Hologres V4.1 and later.

• The user mapping table must have a primary key and exactly one Serial field (besides the primary key).

• Only a single-column primary key of the text type is supported.

Example

-- Create the source table
CREATE TABLE base_table(user_id text);
INSERT INTO base_table VALUES('a');

-- Create the user mapping table
CREATE TABLE uid_mapping(user_id text PRIMARY KEY, id serial);

-- Map UIDs to integers and write mappings to uid_mapping
SELECT user_id, hg_id_encoding_int4(user_id, 'uid_mapping') AS res
FROM base_table;

-- Query the mapping table to verify
-- user_id | id
-- --------+----
--   a     |  1

Syntax

min_by(expr1, expr2)
max_by(expr1, expr2)

Arguments

Returns

The value of expr1 from the row where expr2 is at its minimum (min_by) or maximum (max_by).

Usage notes

Supported in Hologres V4.0 and later.

Example

The following example creates a Dynamic Table that tracks each user's first and last events, based on event timestamps.

-- Create the source table
DROP TABLE IF EXISTS detail;
CREATE TABLE detail (
  userid       text,
  event_id     text,
  create_time  timestamptz
);

INSERT INTO detail(userid, event_id, create_time) VALUES
  ('user_1', 'e1', '2024-12-20 10:00:00+08'),
  ('user_1', 'e2', '2024-12-20 11:30:00+08'),
  ('user_1', 'e3', '2024-12-21 09:15:00+08'),
  ('user_2', 'e4', '2024-12-20 08:05:00+08'),
  ('user_2', 'e5', '2024-12-22 14:20:00+08'),
  ('user_3', 'e6', '2024-12-21 16:45:00+08');

-- Create the Dynamic Table
DROP TABLE IF EXISTS detail_user_first_last_event;

CREATE DYNAMIC TABLE detail_user_first_last_event
WITH (
  auto_refresh_mode = 'incremental',
  computing_resource = 'local',
  freshness = '3 minutes'
)
AS
SELECT
  userid,
  min_by(event_id, create_time) AS first_event_id,
  max_by(event_id, create_time) AS last_event_id,
  date_trunc('day', max(create_time))::date AS dt
FROM detail
GROUP BY userid;

Syntax

string_agg([distinct] column_expr, const_expr)

Arguments

Usage notes

• The ORDER BY clause is not supported.

• string_agg([distinct]) syntax requires Hologres V3.1.10 or later.

• Basic string_agg is supported in Hologres V3.1 and later.

Example

CREATE DYNAMIC TABLE string_agg_test_dt
  WITH (
    freshness = '3 minutes',
    refresh_mode = 'incremental')
  AS
  SELECT day,
         string_agg(gameversion, ',') AS gameversion_list
    FROM base_table GROUP BY day;

Syntax

array_agg([distinct] expr)

Arguments

Usage notes

• The ORDER BY clause is not supported.

• array_agg([distinct]) syntax requires Hologres V3.1.10 or later.

• Basic array_agg is supported in Hologres V3.1 and later.

Example

CREATE DYNAMIC TABLE array_agg_test_dt
  WITH (
    freshness = '3 minutes',
    refresh_mode = 'incremental')
  AS
  SELECT day,
         array_agg(gameversion) AS gameversion_list
    FROM base_table GROUP BY day;

Syntax

any_value(expr)

Arguments

Usage notes

• Supported in Hologres V3.1.5 and later.

• expr supports only the int and binary types.

• The return value is non-deterministic.

Example

CREATE DYNAMIC TABLE dt_t0
WITH (
  freshness = '1 minutes',
  auto_refresh_mode = 'auto'
)
AS
SELECT a, any_value(c), sum(b) FROM t0 GROUP BY a;