Use this reference to confirm which SQL constructs, functions, DDL/DML operations, and permissions apply to Dynamic Tables in Hologres before you create or debug one. Constraints differ by refresh mode and Hologres version.
Refresh mode comparison
Dynamic Tables support two refresh modes: incremental refresh and full refresh. The following table shows which SQL constructs each mode supports.
| SQL construct or feature | Incremental refresh | Full refresh |
|---|---|---|
| Scalar expressions | Supported | Supported |
| WHERE conditions | Supported | Supported |
| Subqueries | Supported | Supported |
| Common Table Expressions (CTEs) | Supported | Supported |
| GROUP BY | Supported | Supported |
| CUBE | Supported | Supported |
| GROUPING SETS | Supported | Supported |
| HAVING | Supported | Supported |
| Agg Filter | Supported | Supported |
| UNION ALL | Supported | Supported |
| UNNEST | Supported | Supported |
| All functions and data types | Not supported (see Supported functions) | Supported |
| Window functions | Not supported | Supported |
| IN subqueries | Not supported | Supported |
| EXISTS / NOT EXISTS | Not supported | Supported |
| EXCEPT / INTERSECT | Not supported | Supported |
| ORDER BY | Not supported | Supported |
| LIMIT / OFFSET | Not supported | Supported |
Incremental refresh
Resource usage
Starting from V3.1, new Dynamic Tables use serverless resources by default for refresh tasks. If serverless is not enabled for the instance, the system falls back to local resources automatically. Dynamic Tables created in V3.0 continue to use the refresh resources configured at creation time and do not switch to serverless.
Base table requirements
Access permissions on the base table are required to create a Dynamic Table.
| Version | Supported base tables | Binary logging requirement |
|---|---|---|
| V3.1 and later | Hologres internal tables, Paimon foreign tables, other Dynamic Tables | Not required. Data is consumed using the Stream method, which has better performance and no extra storage costs. If a base table used binary logging before upgrading to V3.1, disable binary logging to avoid extra storage costs. See Subscribe to Hologres binary logs. |
| V3.0 | Hologres internal tables, Paimon foreign tables, other Dynamic Tables | Required for the base table (not required for dimension tables). See Subscribe to Hologres binary logs. |
Multi-table JOINs
V3.0: Only dimension table equi-JOINs (INNER JOIN or LEFT JOIN) are supported. These JOINs must use the FOR SYSTEM_TIME AS OF PROCTIME() syntax. Multi-table dual-stream JOINs are not supported.
Dimension table JOIN semantics: each data record joins only the latest version of the dimension table at processing time. If dimension table data changes after the JOIN runs, those changes do not affect already-joined data.
V3.0.26 and later: Multi-table dual-stream JOINs are supported. These include the standard OLAP JOINs (INNER JOIN, LEFT/RIGHT/FULL OUTER JOIN). See Create dynamic table.
V3.1 and later: You can set a Dynamic Table as a logical partition with full partition-related properties and partition management support.
Supported functions (incremental refresh)
Aggregate functions supported in incremental refresh mode: COUNT, SUM, MIN/MAX, and COUNT DISTINCT. Functions executed by the Parallel Query Engine (PQE) are not supported.
The following specialized functions are also supported:
| Function | Syntax | Example | Supported versions |
|---|---|---|---|
| RB_BUILD_AGG | RB_BUILD_AGG(<column>) — column must be int32 or int64. 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) — column_expr must be TEXT, CHAR, or VARCHAR. const_expr must be a TEXT constant. ORDER BY is not supported. STRING_AGG([DISTINCT] is supported from V3.1.10. |
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; STRING_AGG([DISTINCT] from V3.1.10 |
| ARRAY_AGG | ARRAY_AGG([DISTINCT] expr) — expr supports BOOL, all numeric types, TEXT, and BYTEA. ORDER BY is not supported. ARRAY_AGG([DISTINCT] is supported from V3.1.10. |
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; ARRAY_AGG([DISTINCT] from V3.1.10 |
| ANY_VALUE | ANY_VALUE(expr) — returns a random value from each group in an aggregate query with GROUP BY. The result is non-deterministic. expr supports only INT and BINARY types. |
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; |
V3.1.5 and later |
Full refresh
Supported features
| Feature | Details |
|---|---|
| Base table support | All base table types that Hologres supports: Hologres internal tables and foreign tables including MaxCompute, DLF (Data Lake Formation), and Paimon. Access permissions on the base table are required. See Dynamic Table permissions. |
| Query support | All functions, SQL expressions, and data types that Hologres supports. |
| Refresh resources | Serverless resources by default. You can switch to the resources of the current instance. |
Limits
Converting a Dynamic Table from full refresh mode to incremental refresh mode is not supported.
General limits
Dynamic Table property limits
-
Requires Hologres V3.0 or later.
-
Cannot set a primary key or default field values on a Dynamic Table. The engine infers the table index automatically. Manual index configuration is also supported.
-
Only full refresh and incremental refresh modes are supported.
Supported DDL, DML, and other operations
| Operation | Supported |
|---|---|
| CREATE DYNAMIC TABLE | Yes |
| RENAME DYNAMIC TABLE | Yes |
| RENAME DYNAMIC TABLE column | Yes |
| SELECT | Yes |
| Refresh non-partitioned tables and child partitions | Yes |
| Refresh parent partitions | No |
| DROP DYNAMIC TABLE | Yes |
| DROP DYNAMIC TABLE column | No |
| TRUNCATE DYNAMIC TABLE | No |
| DML (INSERT/UPDATE/DELETE) on a Dynamic Table | No |
| ADD column | No |
| Resharding | No — resharding the base table is also not supported |
| CREATE TABLE AS/LIKE | No |
Permission requirements
| Operation | Permission requirements |
|---|---|
| CREATE DYNAMIC TABLE | Create Table permission. Select permission on the base table. |
| ALTER DYNAMIC TABLE | Create Table permission. Select permission on the base table. |
| DROP DYNAMIC TABLE | Table Owner of the Dynamic Table. |
| SELECT on a Dynamic Table | Select permission on the Dynamic Table. |
| REFRESH DYNAMIC TABLE | DML permission on the Dynamic Table. Parent partitions cannot be refreshed. |
For details on granting permissions, see Hologres permission model.
Effects of base table operations on Dynamic Tables
| Base table operation | Dynamic Table behavior |
|---|---|
RENAME <basetable_name> |
Queries execute normally. Refresh operations fail. |
RENAME <column_name> not used by the Dynamic Table |
Queries execute normally. Refresh operations execute normally. |
RENAME <column_name> used by the Dynamic Table |
Queries execute normally. Refresh operations execute normally. |
DROP <basetable_name> |
DROP fails. The Dynamic Table continues to execute normally. |
DROP <basetable_name> CASCADE |
The Dynamic Table is also deleted and the refresh task is canceled. |
DROP <column_name> not used by the Dynamic Table |
Queries execute normally. Refresh operations execute normally. |
DROP <column_name> used by the Dynamic Table |
DROP fails. |
TRUNCATE <basetable_name> |
If the base table is truncated before a Dynamic Table refresh, queries on the Dynamic Table return data. If truncated after a refresh, queries return no data. |
INSERT/DELETE/UPDATE/UPSERT on <basetable_name> |
You can perform INSERT, DELETE, UPDATE, or UPSERT operations on a dynamic table. |