Sync External Dynamic Tables via REFRESH in Hologres - Hologres

Use REFRESH EXTERNAL DYNAMIC TABLE to manually trigger a data refresh on an external dynamic table. For automatic refreshes, configure a refresh policy when you run CREATE EXTERNAL DYNAMIC TABLE.

Syntax

REFRESH [OVERWRITE] EXTERNAL DYNAMIC TABLE {external_db}.{external_schema}.{table_name}
[PARTITION (<partition_key> = '<partition_value>') ]
[WITH (
  refresh_mode = '{full|incremental}'
)];

Parameters

Parameter	Description
`external_db`	The name of the external database.
`external_schema`	The name of the external schema.
`table_name`	The name of a non-partitioned table or a child table of a partitioned table.
`OVERWRITE`	Resets the incremental refresh state before refreshing. In incremental refresh mode, this discards the previous incremental state and restarts from the beginning. In full refresh mode, the behavior is the same as a standard `REFRESH`.
`PARTITION (<partition_key> = '<partition_value>')`	Specifies the partition to refresh when the target Paimon table is partitioned. `<partition_key>` is the partition key. `<partition_value>` is the corresponding partition value.
`WITH (refresh_mode = '{full\|incremental}')`	Overrides the refresh mode for this command. `full` replaces all previously materialized data. `incremental` merges only the changes since the last refresh.

Refresh modes

Before reading the behavior table, note how each mode works:

Incremental refresh: Calculates the changes since the last refresh and merges only those changes into the table.
Full refresh: Runs the table query and completely replaces the previously materialized data.

The following table shows how each REFRESH command variant behaves depending on the refresh mode configured on the table.

Command	Behavior in incremental refresh mode	Behavior in full refresh mode
`refresh`	Incremental refresh	Full refresh
`refresh overwrite`	Restarts the incremental refresh and discards the previous state.	Full refresh
`refresh with full`	An error occurs.	Full refresh
`refresh with incremental`	Incremental refresh	An error occurs.
`refresh overwrite with incremental`	Restarts the incremental refresh and discards the previous state.	An error occurs.
`refresh overwrite with full`	Performs a one-time full refresh and stops the incremental refresh.	Full refresh

Note: For partitioned tables, specify a partition in each REFRESH command. Only one partition can be refreshed per command.

Limitations

Partitioned tables: Refresh a specific child table partition — refreshing the parent table directly is not supported. To refresh all partitions, run a separate REFRESH command for each partition.
Execution context: Run REFRESH only from a non-external database. Running it inside an external database context returns an error.

Examples

Refresh a non-partitioned table

Run a full refresh on a non-partitioned external dynamic table:

REFRESH EXTERNAL DYNAMIC TABLE my_ext_db.my_schema.orders;

Run an incremental refresh explicitly:

REFRESH EXTERNAL DYNAMIC TABLE my_ext_db.my_schema.orders
WITH (refresh_mode = 'incremental');

Refresh a partition

When the source Paimon table is partitioned, specify the target partition. The following example refreshes the dt='2024-01-15' partition:

REFRESH EXTERNAL DYNAMIC TABLE my_ext_db.my_schema.events
PARTITION (dt = '2024-01-15');

To perform a one-time full refresh on a partition in a table configured for incremental mode (note: this also stops the incremental refresh):

REFRESH OVERWRITE EXTERNAL DYNAMIC TABLE my_ext_db.my_schema.events
PARTITION (dt = '2024-01-15')
WITH (refresh_mode = 'full');