Hologres:Dynamic Table scope and limits

Limits

• Resource usage

  Starting from V3.1, new tables use serverless resources by default to execute refresh tasks. If serverless resources are not enabled for the instance, the system automatically switches to local resources. Tables created in V3.0 continue to use the refresh resources that were set at creation and do not default to serverless resources.

• Base table limits

  • Only Hologres internal tables, Paimon foreign tables, and other Dynamic Tables are supported as base tables. You must have access permissions to the base table to create a Dynamic Table.

  • V3.1: By default, data from the base table is incrementally consumed using the Stream method. Compared with the binary logging method, the Stream method provides better performance and does not incur extra storage costs. If your base table used binary logging before V3.1, you must disable binary logging to avoid additional storage costs. For more information about how to disable binary logging, see Subscribe to Hologres binary logs.

  • V3.0: When you create an incremental Dynamic Table, binary logging must be enabled for the base table. Binary logging does not need to be enabled for dimension tables. For more information about how to enable binary logging, see Subscribe to Hologres binary logs.

• Query limits

  • Any scalar expression is supported.

  • WHERE conditions, subqueries, Common Table Expressions (CTEs), GROUP BY, CUBE, GROUPING SETS, HAVING statements, Agg Filter, UNION ALL, and UNNEST are supported.

  • Window functions, IN subqueries, EXISTS or NOT EXISTS, EXCEPT or INTERSECT, ORDER BY, LIMIT, or OFFSET are not supported.

  • Multi-table JOIN:

    • V3.0 supports only dimension table equi-joins (INNER JOIN or LEFT JOIN) and must use the FOR SYSTEM_TIME AS OF PROCTIME() method. Multi-table dual-stream joins are not supported. For more information, see Dimension table JOIN statements.

      Note

      The semantics of a dimension table JOIN are as follows: Each data record is joined only with the latest version of the dimension table data at the processing time. The JOIN operation occurs only at the processing time. If the data in the dimension table is added, updated, or deleted after the JOIN operation, the joined dimension table data is not updated.

    • Starting from V3.0.26, multi-table dual-stream joins are supported. These joins are regular joins in online analytical processing (OLAP) or dual-stream joins in Flink, and include INNER JOIN, LEFT/RIGHT/FULL OUTER JOIN. For more information, see CREATE DYNAMIC TABLE.

  • Functions: Aggregate functions such as COUNT, SUM, MIN/MAX, and COUNT DISTINCT are supported. The following table describes more supported functions:

    Function name	Syntax	Dynamic Table example	Supported versions
    RB_BUILD_AGG	RB_BUILD_AGG(<column>) Note The column parameter supports the int32 and int64 data types. For more information, see RoaringBitmap functions.	CREATE DYNAMIC TABLE daily_uv PARTITION BY list (day) WITH ( freshness = '5 minutes', refresh_mode = 'incremental') AS SELECT day, game_id, gameversion, RB_BUILD_AGG(user_id) AS user_rb FROM base_table GROUP BY day, game_id, gameversion;	V3.1 and later.
    STRING_AGG	STRING_AGG([DISTINCT] column_expr, const_expr) Note Parameter types: column_expr must be of the TEXT, CHAR, or VARCHAR type. const_expr must be a TEXT constant. The ORDER BY syntax is not supported. Starting from Hologres V3.1.10, STRING_AGG([DISTINCT] is supported.	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;	V3.1 and later. Starting from V3.1.10, STRING_AGG([DISTINCT] is supported.
    ARRAY_AGG	ARRAY_AGG([DISTINCT] expr) Note expr parameter type: Supports BOOL, all numeric types, TEXT, and BYTEA. The ORDER BY syntax is not supported. Starting from Hologres V3.1.10, ARRAY_AGG([DISTINCT] is supported.	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;	V3.1 and later. Starting from Hologres V3.1.10, ARRAY_AGG([DISTINCT] is supported.
    ANY_VALUE	In an aggregate query that contains GROUP BY, this function returns a random result from a row in each aggregation group. The result is non-deterministic. ANY_VALUE(expr) The input parameter for ANY_VALUE supports only the INT and BINARY types.	CREATE DYNAMIC TABLE dt_t0 WITH ( -- Properties of the dynamic table freshness = '1 minutes', auto_refresh_mode = 'auto' ) AS SELECT a,any_value(c),SUM(b) FROM t0 GROUP BY a;	Supported in V3.1.5 and later.

  • Starting from V3.1, you can set a Dynamic Table as a logical partition. Partition properties and partition management settings that are related to logical partitions are supported.

Supported features

• Base table support: The support for base tables is the same as for regular Hologres tables. Hologres internal tables and foreign tables, such as MaxCompute, DLF, and Paimon, are supported as base tables for Dynamic Tables. You must have access permissions to the base table to create a Dynamic Table. For more information, see Dynamic Table permissions.

• Query support: The full refresh mode supports all functions, SQL expressions, and data types that Hologres supports.

• Refresh resource support: By default, serverless resources are used to execute refresh tasks. You can also switch to the resources of the current instance.

Limits

You cannot convert a Dynamic Table from the full refresh mode to the incremental refresh mode.

Dynamic Table limits

• The Hologres instance must be V3.0 or later.

• Dynamic Table property limits: You cannot set a primary key or default field values. The engine automatically infers the table index. You can also manually set the index if required.

• Only the full refresh and incremental refresh modes are supported. The scope and limits differ between the modes. For more information, see Full refresh and Incremental refresh.

Limits on DDL, DML, and other operations on Dynamic Tables