Dynamic Table support and limitations

Limitations

• Resource usage

  Starting from Hologres V3.1, new tables use serverless resources by default to execute refresh tasks. If the instance does not have serverless resources enabled, it automatically switches to local resources. Tables created in Hologres V3.0 continue to use their originally specified resources and do not automatically switch to serverless resources.

• Limitations on base tables

  • Only Hologres internal tables, Paimon foreign tables, and other Dynamic Tables are supported as base tables. To create a Dynamic Table, you must have SELECT permissions on the base table.

  • By default, Hologres V3.1 incrementally consumes base table data by using the stream method. Compared with the Binlog method, the stream method offers better performance and does not incur extra storage costs. If your base table used the Binlog method before V3.1, we recommend that you disable Binlog promptly to prevent additional storage consumption. To disable Binlog, see Subscribe to Hologres Binlog.

  • In Hologres V3.0, when you create a Dynamic Table in incremental refresh mode, Binlog must be enabled for the base table. However, it is not required for dimension tables. For more information about enabling Binlog, see Subscribe to Hologres Binlog.

• Limitations on queries

  • Supported scenarios

    • Any scalar expression

    • WHERE conditions

    • Subqueries

    • Common Table Expressions (CTEs)

    • GROUP BY

    • CUBE

    • GROUPING SETS

    • HAVING clauses

    • Aggregate filters

    • UNION ALL

    • UNNEST

  • Unsupported scenarios

    • Window functions

    • IN subqueries

    • EXISTS or NOT EXISTS

    • EXCEPT or INTERSECT

    • ORDER BY

    • LIMIT or OFFSET

  • Multi-table join:

    • Hologres V3.0 supports only equi-joins (INNER JOIN or LEFT JOIN) on dimension tables, and these joins must use the FOR SYSTEM_TIME AS OF PROCTIME() syntax. Multi-table double-stream joins are not supported. For more information, see Lookup join statements.

      Note

      A lookup join associates each record with the latest version of the dimension table data at processing time. If the dimension table data changes after the join, the previously joined data is not updated.

    • Starting from Hologres V3.0.26, multi-table double-stream joins are supported. These are equivalent to standard OLAP joins or double-stream joins in Flink and include INNER JOIN and 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. Functions that run on the Parallel Query Engine (PQE) are not supported. The following table describes other supported functions.

    Function	Syntax	Example	Supported version
    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 Data types: column_expr must be TEXT, CHAR, or VARCHAR. const_expr must be a TEXT constant. The ORDER BY clause 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 Data types: The expr parameter supports BOOL, all numeric types, TEXT, and BYTEA. The ORDER BY clause 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 a GROUP BY clause, this function non-deterministically returns a value from one of the rows within each group. ANY_VALUE(expr) The ANY_VALUE function accepts only INT and BINARY data 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;	V3.1.5 and later

  • Starting from Hologres V3.1, you can configure a Dynamic Table as a logical partition. The associated partition properties and management settings are also supported.

Supported features

• Base tables: The functionality is the same as for standard Hologres tables. You can use Hologres internal tables and foreign tables, such as those from MaxCompute, Data Lake Formation (DLF), and Paimon, as base tables. You must have the required permissions on the base table to create a Dynamic Table. For more information, see Dynamic Table permissions.

• Queries: All functions, SQL expressions, and data types that are currently supported by Hologres are also supported in full refresh mode.

• Refresh resources: By default, refresh tasks use serverless resources. You can also configure the tasks to use the resources of your current instance.

Limitations

• You cannot change the refresh mode from full refresh to incremental refresh.

• In Hologres V3.0, if you create a VIEW on a Dynamic Table that is in full refresh mode, the refresh process for the Dynamic Table fails. This issue is resolved in V3.1 and later versions. We recommend that you upgrade your instance.

Limitations on dynamic tables

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

• Limitations on table properties: You cannot set a primary key or default field values. The engine automatically infers the table's index, but you can also set it manually based on your business requirements.

• Only full refresh and incremental refresh modes are supported. The supported features and limitations vary by mode. For details, see Full refresh and Incremental refresh.

Limitations on DDL and DML operations