Application scope and limits of dynamic tables - Hologres

You can use dynamic tables to implement automatic data forwarding and tiering with higher efficiency and at a lower cost. This topic describes the application scope and limits of dynamic tables.

Full data refresh

This section describes the supported features and limits of dynamic tables that are in full data refresh mode.

Supported features

Supported base tables: Base tables have the same features as common Hologres tables. You can use Hologres internal tables and foreign tables, such as MaxCompute, Data Lake Formation (DLF), and Paimon foreign tables, as base tables of dynamic tables. To create a dynamic table, you must have the permissions to access the corresponding base tables. For more information, see Permissions on dynamic tables.
Supported queries: All functions, SQL expressions, and data types that are supported by Hologres are supported by dynamic tables in full data refresh mode.
Supported resources: By default, serverless resources are used to run data refresh tasks. You can also use the resources of the current instance to run refresh tasks.

Limits

Automatic partition creation is not supported.
You cannot switch the data refresh mode from full to incremental for dynamic tables.

Incremental data refresh

This section describes the supported features and limits of dynamic tables that are in incremental data refresh mode.

Supported features

Supported base tables: You can use only Hologres internal tables, Paimon foreign tables, and dynamic tables as base tables. To create a dynamic table, you must have the permissions to access the corresponding base tables.
Supported queries:
- Arbitrary scalar expressions
- WHERE condition
- GROUP BY, CUBE, GROUPING SETS, and HAVING clauses, and aggregate functions, such as COUNT, SUM, MIN, MAX, and COUNT DISTINCT
- Agg Filter
- Subqueries and common table expressions (CTEs)
- UNION ALL and UNION
- UNNEST
- Equi-joins such as INNER JOIN and LEFT JOIN performed on dimension tables, in which FOR SYSTEM_TIME AS OF PROCTIME() is used
Supported resources: By default, serverless resources are used to run refresh tasks. You can also use the resources of the current instance to run refresh tasks.
You can switch the data refresh mode from incremental to full for dynamic tables.

Limits

Limits on base tables: When you create a dynamic table in incremental data refresh mode for a base table, you must enable binary logging for the base table. However, you do not need to enable binary logging for a dimension table. For information about how to enable binary logging, see Subscribe to Hologres binary logs.
Limits on queries:
- JOIN operations on non-dimension tables, such as common JOIN operations in Online Analytical Processing (OLAP) or dual-stream JOIN operations in Flink, are not supported.
  Note
  In semantics of a JOIN operation performed on a dimension table, each data record in a base table is associated with the latest version of data in the dimension table. This indicates that the JOIN operation is performed at the time when data is processed. In this case, if the data in the dimension table is added, updated, or deleted after the JOIN operation is performed, the associated data remains unchanged.
- Window functions are not supported.
- IN subqueries are not supported.
- EXISTS or NOT EXISTS is not supported.
- EXCEPT or INTERSECT is not supported.
- ORDER BY is not supported.
- LIMIT or OFFSET is not supported.
- Roaring bitmap functions are not supported.
For partitioned dynamic tables, automatic partition creation is not supported.

General limits

Limits on dynamic tables

The version of the Hologres instance that you use must be V3.0 or later.
Limits on the properties of a dynamic table: You cannot specify the primary key or the default values of fields in a dynamic table. The engine automatically infers the indexes of a dynamic table. You can specify the indexes of a dynamic table based on your business requirements.
Only the full data refresh mode and incremental data refresh mode are supported. The application scope and limits of dynamic tables vary based on the data refresh mode. For more information, see the Full data refresh and Incremental data refresh sections in this topic.

Limits on DDL and DML operations on dynamic tables

Operation	Supported
CREATE DYNAMIC TABLE	Yes
RENAME DYNAMIC TABLE	Yes
RENAME DYNAMIC TABLE Column	Yes
SELECT	Yes
Refresh	The refresh operation is supported for non-partitioned tables and child partitioned tables. The refresh operation is not supported for parent partitioned tables.
DROP DYNAMIC TABLE	Yes
DROP DYNAMIC TABLE Column	No
TRUNCATE DYNAMIC TABLE	No
DML (INSERT/UPDATE/DELETE) DYNAMIC TABLE	No
ADD Column	No
Resharding	No
CREATE TABLE AS/LIKE	No

Permissions on a dynamic table

Operation	Required permission
CREATE DYNAMIC TABLE	The permission to create a table The permission to select a base table
ALTER DYNAMIC TABLE	The permission to create a table The permission to select a base table
DROP DYNAMIC TABLE	The permissions of the owner of a dynamic table
SELECT DYNAMIC TABLE	The permission to select a dynamic table
REFRESH DYNAMIC TABLE	The permission to perform DML operations on a dynamic table Note Parent partitioned tables cannot be refreshed.

For more information about the required permissions to perform operations on a dynamic table, see Overview.

Impacts of the operations performed on base tables on dynamic tables

Operation on base tables	Impact on dynamic tables
RENAME <basetable_name>	Queries for dynamic tables can be executed as expected. An error occurs when the refresh operation is performed.
RENAME <Unused column names of a dynamic table in a base table>	Queries for dynamic tables can be executed as expected. The refresh operation is performed as expected.
RENAME <Used column names of a dynamic table in a base table>	Queries for dynamic tables can be executed as expected. The refresh operation is performed as expected.
DROP <basetable_name>	An error occurs when the drop operation is performed. Queries for dynamic tables can be executed as expected.
DROP <basetable_name> CASCADE	The dynamic table is also dropped and the task is canceled.
DROP <Unused column names of a dynamic table in a base table>	Queries for dynamic tables can be executed as expected. The refresh operation is performed as expected.
DROP <Used column names of a dynamic table in a base table>	An error occurs when the drop operation is performed.
TRUNCATE <basetable_name>	If the TRUNCATE TABLE statement is executed on the base table before the dynamic table is refreshed, data is returned by a query on the dynamic table. If the TRUNCATE TABLE statement is executed on the base table after the dynamic table is refreshed, no data is returned by a query on the dynamic table.
INSERT/DELETE/UPDATE/UPSERT <basetable_name>	The INSERT, DELETE, UPDATE, or UPSERT statement is executed on the dynamic table.