DataWorks:Use Hologres dynamic tables

Step 1: Prepare the source table

Run the following SQL in a DataWorks Hologres SQL node or any Hologres client to create the orders source table. The table must have Binlog enabled to support incremental refresh.

-- 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', -- Enable Binlog
    binlog_ttl = '86400'      -- Binlog retention in seconds (1 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 wizard

1. Go to the Workspaces page in the DataWorks console. Select a region in the top navigation bar, then find your workspace and choose Shortcuts > Data Studio in the Actions column.

2. In the left navigation pane, click the icon to open the Data Catalog module.

3. In the Data Catalog area, locate the target Hologres instance, expand its folders, and click the icon next to Dynamic Table to open the creation page.

In a workspace running in standard mode, the Data Catalog lists both a Development database and a Production database. Create and test the dynamic table in the development database first. Dynamic tables created in the development database are not automatically synchronized to the production database — you must create the same table in production separately.

Step 3: Define the query logic and precompile

1. Enter basic information:

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

   • Description: Enter a brief description, such as "Aggregated order statistics by user".

2. Write the data generation SQL: On the Data Generation SQL tab in the Field Information area, enter the SELECT statement that defines the dynamic table's data logic.

   Important

   Enter only the SELECT statement. Do not include the CREATE DYNAMIC TABLE DDL command. The system generates the full DDL automatically and displays it on the right.

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

3. Precompile: Click Precompile above the SQL editor. Precompilation is a required step — it contacts the Hologres engine in real time to: If precompilation succeeds, the system lists the supported refresh modes. If it fails, an error message explains what to fix — update the SQL and precompile again.

   • Validate that the SELECT statement is syntactically correct

   • Infer which refresh modes (auto, incremental, or full) are supported for this query

   • Parse the output fields and data types

4. (Optional) Review field details: After precompilation succeeds, click the Field Details tab to view the parsed field names, data types, and null constraints. Add descriptions in the Description column to improve metadata readability.

Step 4: Configure the refresh policy

In the Refresh Policy panel on the right side of the page, configure the following:

For Advanced Parameters, keep the defaults unless you need to set specific GUC parameters. See GUC parameters.

For a full explanation of each parameter, see Configuration reference.

Step 5: Publish

1. Click Publish at the top of the page.

2. In the confirmation dialog, click Publish again.

The dynamic table dt_user_orders_agg is now live. When new rows are inserted into or updated in orders, the aggregated results are automatically refreshed within approximately five minutes.

Step 1: Prepare the source table

-- Create the user registration table and enable Binlog
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    user_name TEXT,
    region TEXT,
    registration_time TIMESTAMPTZ
) WITH (
    binlog_level = 'replica',
    binlog_ttl = '86400'
);

-- Insert test data
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);

Step 2: Open the dynamic table creation wizard

Follow the same steps as in the quick start: open Data Studio, navigate to Data Catalog, and click the icon next to Dynamic Table.

Step 3: Define the query logic and precompile

1. Enter basic information:

   • Dynamic Table Name: dwd_user_new_user_detail_di

   • Description: Daily new user detail statistics

2. Write the data generation SQL: Cast registration_time to DATE to create the partition key ds. The partitioning mechanism automatically routes each row to the correct date partition — no WHERE clause filtering by date is needed.

   SELECT
       user_id,
       user_name,
       region,
       registration_time,
       CAST(registration_time AS DATE) AS ds -- Partition key: convert timestamp to date
   FROM public.users;

3. Precompile: Click Precompile and wait for it to succeed.

Step 4: Select the partition field

In the Partition Field Information area, open the Partition Field dropdown and select ds. DataWorks identifies the table as partitioned and reveals the partition configuration options.

Step 5: Configure partitions and the refresh policy

For more information about partition parameters, see Hologres partition properties.

Partition configurations:

Refresh policy configurations:

Step 6: Publish

Click Publish at the top of the page, then confirm.

Verify the partitioned table

1. View partitions: Expand dwd_user_new_user_detail_di in the Data Catalog. Date-based partition sub-tables appear, such as 2026-02-27.

2. Insert new data and query:

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

   Wait approximately 10 minutes, then run a query on the partition key for an efficient lookup:

   -- Query by partition key
   -- To verify immediately, manually refresh: REFRESH DYNAMIC TABLE dwd_user_new_user_detail_di PARTITION (ds = '2026-02-27');
   SELECT * FROM dwd_user_new_user_detail_di WHERE ds = '2026-02-27';

   The new user 'David' appears in the partition for 2026-02-27.

   

View refresh status

In the Data Catalog, click a dynamic table to open its details page. The details page has the following tabs:

• Details: Schema information — field names, data types, and primary key.

• Basic Information: Core configurations — owner, data generation SQL, and refresh policy.

• DDL: The complete DDL statement used to create the table. Copy it for migration or replication.

For operational monitoring, switch to the Output Information tab.

Monitor on the Output Information tab

The Output Information tab is the primary page for tracking refresh health.

Action buttons:

Data Refresh in Progress: If a refresh is currently running, this section shows real-time details including the PID, Query ID, status, and duration.

Output History: A record of every past refresh task. Key fields:

If you see repeated Failed entries, check the Query ID in Hologres for error details. If the dynamic table falls behind its Data Freshness target, check whether the source table's Binlog is active and whether Serverless Resources are available.

Modify or delete a dynamic table

• Modify: Open the dynamic table's details page and click Edit in the upper-right corner. Most configuration items can be updated. For a full list of modifiable parameters, see Alter dynamic table.

• Delete: In the Data Catalog list, right-click the target table and select Delete.

Basic information and field configuration

Refresh policy

Refresh policy options:

Refresh trigger and freshness:

Computing resource options:

Partition configurations

All new dynamic tables use logical partitions. Partition settings appear only after you select a partition field.

Advanced settings (table properties)

<table> <thead> <tr> <td><p><b>Parameter</b></p></td> <td><p><b>Description</b></p></td> </tr> </thead> <colgroup></colgroup> <colgroup></colgroup> <tbody> <tr> <td><p><b>Storage Mode</b></p></td> <td><p>Hologres supports three storage modes: <b>Column Store</b>, <b>Row Store</b>, and <b>Row-Column Hybrid Store</b>. The default mode is <b>Column Store</b>. For more information, see <a href="https://www.alibabacloud.com/help/en/document_detail/468043.html">Table storage formats: column store, row store, and row-column hybrid store</a>.</p> <ul> <li><p>Column store is suitable for complex queries in various OLAP scenarios.</p></li> <li><p>Row store is suitable for key-value (KV) query scenarios based on primary keys.</p></li> <li><p>Row-Column Hybrid Store is suitable for scenarios where both column store and row store can be used.</p></li> </ul></td> </tr> <tr> <td><p><b>Table Group</b></p></td> <td><p>Select the name of the <code>Table Group</code> that is generated when you create an internal table in the Hologres data source. For more information, see <a href="https://www.alibabacloud.com/help/en/document_detail/2399543.html">Manage table groups</a>.</p></td> </tr> <tr> <td><p><b>Storage Policy</b></p></td> <td><p>Hologres supports two storage policies: Standard storage (<b>Hot Storage</b>) and Infrequent Access storage (<b>Cold Storage</b>).</p> <ul> <li><p>Hot storage (all-SSD) is the default. It meets requirements for low-latency, high-performance data access and is the most effective choice for most scenarios.</p></li> <li><p>Cold storage (all-HDD) meets requirements for low-cost storage of infrequently accessed data. It is suitable for large datasets that are not latency-sensitive.</p></li> </ul><p>For more information, see <a href="https://www.alibabacloud.com/help/en/document_detail/469054.html">Tiered Storage of Hot and Cold Data</a>.</p></td> </tr> <tr> <td><p><b>Table Data Lifecycle</b></p></td> <td><p>Set a custom maximum time to live (TTL) for the dynamic table.</p></td> </tr> <tr> <td><p><b>Binlog</b></p></td> <td><p>Enable (<b>replica</b>) or disable (<b>none</b>) Binlog subscription. Disabled by default. For more information, see <a href="https://www.alibabacloud.com/help/en/document_detail/201024.html">Subscribe to Hologres Binlog</a>.</p></td> </tr> <tr> <td><p><b>Binlog Lifecycle</b></p></td> <td><p>Sets the maximum retention period for Binlog data. Available only when Binlog is set to <b>replica</b>. For more information, see <a href="https://www.alibabacloud.com/help/en/document_detail/201024.html#concept-2037122">Subscribe to Hologres Binlog</a>.</p></td> </tr> <tr> <td><p><b>Field Properties</b></p></td> <td><p>Set physical properties for individual fields: <b>Distribution Column</b>, <b>Segment Column</b>, <b>Clustering Column</b>, <b>Bitmap Column</b>, and <b>Dictionary Encoding Column</b>. For more information, see <a href="https://www.alibabacloud.com/help/en/document_detail/175292.html">Manage internal tables</a>.</p></td> </tr> </tbody> </table>