Dynamic Table
A dynamic table stores the result of a query in Fuse storage. The query can join multiple source tables and aggregate their data. Creation populates the table. Later changes to source tables become visible only after an explicit full refresh.
Dynamic tables support manual full refresh. Run REFRESH DYNAMIC TABLE to update the stored result after source data changes. For periodic refresh, issue this command from your application or an external scheduler. Between refreshes, queries return the last stored result.
Commands
| Command | Description |
|---|---|
| CREATE DYNAMIC TABLE | Defines a dynamic table and populates its initial result. |
| REFRESH DYNAMIC TABLE | Rebuilds the complete stored result from the source tables. |
| SHOW CREATE TABLE | Shows the re-executable CREATE DYNAMIC TABLE statement. |
| DESCRIBE | Shows the output columns. |
| DROP TABLE | Removes a dynamic table. |
Query a dynamic table by name with SELECT. Use SHOW TABLES to list tables. In system.tables, its table_type is DYNAMIC TABLE.
Choose a derived object
| Object | Data returned by a query | Maintenance |
|---|---|---|
| View | Evaluates the stored query against its sources. | No stored result to refresh. |
| Materialized view | Returns current results with read-time correction for unrefreshed source changes. | Supports incremental refresh for append-only changes on one source table. |
| Dynamic table | Returns the result stored by creation or the last successful refresh, even if sources have changed. | Requires an explicit full refresh. Supports joins across source tables. |
The optimizer does not substitute a dynamic table for a query against its sources. Reference the dynamic table directly.
For a complete example, see Dynamic Tables.