DataWorks:Use Hologres Dynamic Tables

Step 1: Prepare the source data

Prepare a source table in your Hologres database. Run the following SQL statements to create an order table named orders and to insert initial data:

-- Create the source table and enable Binlog to support incremental refresh
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    user_name TEXT,
    price FLOAT,
    order_time TIMESTAMPTZ
) WITH (
    binlog_level = 'replica', -- Key setting: enable Binlog
    binlog_ttl = '86400'      -- Binlog retention period in seconds, for example, one day
);
-- Insert initial data
INSERT INTO orders VALUES 
(1, 101, 'Alice', 99.9, NOW()),
(2, 102, 'Bob', 19.5, NOW()),
(3, 101, 'Alice', 25.0, NOW());

Step 2: Open the dynamic table creation page

Open the dynamic table creation page for the target Hologres database. For instructions, see Open the dynamic table creation page.

Step 3: Define the query logic and run precompilation

1. Enter the basic information:

   • Dynamic table name: Enter a unique table name, such as dt_user_orders_agg.

   • Description: Enter the purpose of the table, such as "Order statistics aggregated by user".

2. In the Field Information section, on the Data Generation SQL tab, write the SELECT query that defines the data logic of the dynamic table:

   SELECT 
       user_id,
       user_name,
       COUNT(*) AS order_count,
       SUM(price) AS total_price
   FROM public.orders
   GROUP BY user_id, user_name;

   Note

   Enter only the SELECT statement. You do not need to include the CREATE DYNAMIC TABLE DDL command. The system parses the complete DDL statement from your configuration and displays it in the DDL area on the right.

3. Click Precompile above the SQL editor.

   Precompilation is a required step. After precompilation succeeds, the system lists the supported refresh modes and parses the output columns of the dynamic table. If precompilation fails, the system returns the specific cause. Modify the SQL statement based on the message and run precompilation again. For what precompilation checks and how to read its result, see Appendix: configuration reference.

4. (Optional) Click the Field Details tab to view all columns, data types, and non-null properties that the system parsed, and add a comment for each column in the Description column to make the metadata easier to read.

For this example, the Field Details tab displays four columns: user_id (integer), user_name (text), order_count (bigint), and total_price (double precision). All columns are non-null.

This example does not use the partition settings or the advanced parameter settings. For parameter descriptions, see Appendix: configuration reference.

Step 4: Configure the data refresh policy

After precompilation succeeds, the Refresh Policy panel on the right side of the page becomes editable.

Select the refresh policy that matches your scenario. Auto-Refresh is the recommended default when you do not need to force a specific refresh mode. Incremental Refresh forces incremental refresh only, and Full Refresh applies when the source table has no primary key or no Binlog. This example uses Incremental Refresh because the scenario requires low-latency data synchronization.

For the full description of each parameter, see Appendix: configuration reference and Hologres refresh modes and resources.

• Basic Parameters:

  • Table Type: Because no partition column is selected, the system identifies the table as a Non-Partitioned Table.

  • Refresh Policy: Select Incremental Refresh.

  • Refresh trigger method: Keep Hologres auto refresh, which is the only supported value.

  • Automatically Refresh Data: Keep the default value Yes.

  • Data Freshness: Set this parameter to 5 minutes. The latency of the dynamic table relative to the source table then always stays within 5 minutes.

  • Base Table Consumption Mode for Incremental Refresh: Select stream.

  • Hologres computing resources: Select Serverless resources so that refresh tasks do not consume the resources of your own instance.

• Advanced Parameters (optional): Keep the default values in this example. To set special GUC parameters, see GUC parameters.

Step 5: Publish the dynamic table

1. After you confirm that all configurations are correct, click Publish at the top of the page.

2. In the confirmation dialog box that appears, click Publish again.

The Hologres dynamic table is created and published. When data is inserted into or updated in the source table orders, the dynamic table dt_user_orders_agg updates its aggregated results within about 5 minutes.

Step 6: Verify the refresh

Insert or update rows in the source table orders. After the configured data freshness window of 5 minutes elapses, query dt_user_orders_agg. The aggregated results include the new data. To check whether the refresh tasks run as expected, see View and monitor dynamic tables.

Step 1: Prepare the source data

Create a source table named users that records user registration information. The table must contain a timestamp column to serve as the partitioning basis.

-- Create the user registration table and enable Binlog
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    user_name TEXT,
    region TEXT,
    registration_time TIMESTAMPTZ  -- User registration time, of the TIMESTAMPTZ type
) WITH (
    binlog_level = 'replica',
    binlog_ttl = '86400'
);
-- Insert test data. Replace the dates with the current data timestamp.
INSERT INTO users VALUES 
(1, 'Alice', 'CN-Hangzhou', '2026-02-27 10:00:00+08'),
(2, 'Bob', 'CN-Shanghai', '2026-02-27 14:30:00+08'),
(3, 'Charlie', 'CN-Beijing', (NOW() - INTERVAL '1 day')::TIMESTAMPTZ);

In this sample data, Alice and Bob are registered on the current date, and Charlie is registered one day earlier. The two registration dates show how records are distributed across day partitions.

Step 2: Open the dynamic table creation page

Open the dynamic table creation page for the target Hologres database. For instructions, see Open the dynamic table creation page.

Step 3: Define the query logic and run precompilation

1. Enter the basic information:

   • Dynamic table name: dwd_user_new_user_detail_di

   • Description: Daily new user detail statistics

2. Write a SQL statement that extracts all user records from the users table. You do not need to filter the time manually in the WHERE clause, because the partitioning mechanism of the dynamic table handles this automatically.

   SELECT 
       user_id,
       user_name,
       region,
       registration_time, -- Keep the original precise time column for queries
       CAST(registration_time AS DATE) AS ds -- [Key] Cast the timestamp to the DATE type to use as the partition key
   FROM public.users;

3. Click Precompile. After precompilation succeeds, the system parses the column information.

Step 4: Select the partition column

Selecting a partition column is the step that makes the dynamic table a partitioned table.

1. In the Partition Field Information section, click the Partition Column drop-down list.

2. Select the new column ds as the partition column.

After you select the partition column, DataWorks identifies the table as a partitioned table and displays the partition-related configuration items.

Step 5: Configure the partition and refresh policies

For the full description of each parameter, see Appendix: configuration reference and Hologres partition properties.

• Partition Configurations:

  • Partition Properties: The system displays Logical Partition automatically.

  • Partition Format: Select yyyy-mm-dd to create partitions by day, which matches the DATE values of the partition key ds.

  • Partition Refresh Scope: Set this parameter to 7 days.

  • Partition Management > Partition Retention Period: Set this parameter to 30 days.

• Refresh Policy Configurations:

  • Refresh Policy: Select Auto-Refresh to let Hologres decide between incremental refresh and full refresh.

  • Refresh trigger method: Keep Hologres auto refresh, which is the only supported value.

  • Data Freshness: Set this parameter to 10 minutes.

  • Hologres computing resources: Select Serverless resources.

Step 6: Publish the dynamic table

Click Publish at the top of the page.

Step 7: View the partitions and verify the data

1. Expand the dwd_user_new_user_detail_di table. The partition subtables that are created based on the date appear, such as 2026-02-27.

2. In a Hologres SQL node, insert new data for the current day into the users table:

   INSERT INTO users VALUES (4, 'David', 'CN-Shenzhen', '2026-02-27 18:00:00+08');

3. Wait about 10 minutes for the automatic refresh to run. To verify the result immediately, refresh the partition manually instead:

   REFRESH DYNAMIC TABLE dwd_user_new_user_detail_di PARTITION (ds = '2026-02-27');

4. Query the data by the new partition key ds, which is more efficient:

   -- Efficient query: filter directly on the partition key
   SELECT * FROM dwd_user_new_user_detail_di WHERE ds = '2026-02-27';

The query returns the records whose registration time falls on the queried date, including the new user David (user_id=4, CN-Shenzhen). Each returned record contains the user_id, user_name, region, registration_time, and ds columns, and the ds value of each returned record is the queried date. Charlie (user_id=3, CN-Beijing) is registered one day earlier, so that record belongs to the partition of the previous day and the query does not return it.

View the static information

The following items on the details page describe how the dynamic table is defined:

• Details: The schema information of the table, such as the column list, data types, and primary key.

• Basic information: The core configurations of the table, such as the owner, the data refresh logic (SQL), and the refresh policy.

• DDL: The complete DDL statement that creates the dynamic table. You can copy the statement to migrate or reproduce the table.

Monitor the refresh tasks

Click the Output information tab to monitor the refresh status of the dynamic table.

• Data refresh: If a refresh task is running, this area displays real-time information about the task, such as the PID, query ID, status, and running duration.

• Output History: This area records the history of every refresh task. The following table describes the fields of each record.

Control the refresh of a dynamic table

The following action buttons are available on the Output information tab:

• Pause Refresh / Recovery Refresh: Temporarily stops or resumes the Hologres automatic refresh.

• Manual Refresh: Triggers a refresh task manually to update the data immediately.

• Convert to Full Table: Changes the refresh mode of the dynamic table to Full Refresh and stops automatic refresh. This button appears only when the refresh policy of the dynamic table is Auto-Refresh or Incremental Refresh.

Modify a dynamic table

Find the dynamic table in the Data Directory module, and then click Edit in the upper-right corner of the details page to open the editing page. Most configuration items support modification. For the parameters that you can modify, see the Hologres documentation: Modify a dynamic table.

Delete a dynamic table

In the dynamic table list in the Data Directory module, right-click the target table and select Delete.

Column information and partition column

• Column information (SQL) — The SELECT query that defines the data source of the dynamic table. Precompilation is a required step.

• Partition column

  • Not selected: Creates a non-partitioned table.

  • A time or date column selected: Creates a partitioned table. The system switches to the partitioned table configuration mode automatically.

Data refresh configurations

Partition configurations

The following configuration items appear only after you select a partition column. Dynamic tables of the new version all use logical partitions.

• Partition format — (Required) Defines how partitions are generated from the values of the partition column. For example, if the column type is TIMESTAMPTZ and you select the yyyy-mm-dd format, partitions are created by day.

• Partition refresh scope — Defines the range of active partitions for automatic refresh. For example, if you set the range to the last 7 days, Hologres automatically refreshes only the partitions of the last 7 days. Earlier partitions are no longer refreshed automatically, which saves resources.

• Partition management (collapsed by default)

  • Partition retention period — Sets the time-to-live (TTL) of a partition. Expired partitions are deleted automatically.

  • Hot storage retention period for partition data — Works with tiered hot and cold storage to define how long partition data stays in hot storage (SSD).

Advanced settings (table properties)

The following physical properties apply to the dynamic table itself, and are similar to the settings of a regular Hologres table.