Introduction to Hologres Dynamic Table system tables - Hologres

This topic describes the system tables related to Hologres Dynamic Tables.

Table name	Description
The hologres.hg_dynamic_table_properties system table	Stores the metadata of Dynamic Tables.
The hologres.hg_dynamic_table_dependencies system table	Stores the data lineage of Dynamic Tables.
The hologres.hg_dynamic_table_refresh_activity system table	Stores information about running Dynamic Table refresh tasks. Note This system table is not supported for Hologres instances of V3.1 and later.
The hologres.hg_dynamic_table_refresh_history system table	Stores the history of Dynamic Table refresh tasks.

The `hologres.hg_dynamic_table_properties` system table

The hologres.hg_dynamic_table_properties system table stores the metadata of Dynamic Tables. The following table describes its fields.

Field	Description
dynamic_table_namespace	The schema where the Dynamic Table resides.
dynamic_table_name	The name of the Dynamic Table.
property_key and property_value	The properties and property values of the Dynamic Table. The properties are described as follows: General properties: execution_mode: The refresh mode. Valid values: full: full refresh. incremental: incremental refresh. none: No refresh mode is set. task_definition: The query definition of the Dynamic Table. auto_refresh_enable: Specifies whether to enable auto-refresh. Valid values: true: enabled. false: Cancel. task_definition_search_path: The schema of the query. state_time_to_live_in_seconds: The lifecycle of data in the state table. Parameters for incremental refresh: incremental_auto_refresh_schd_start_time: The start time of the refresh. Valid values: immediate: The refresh starts immediately after the table is created. A custom time: A specific time value, such as 2024-08-27 15:00:00. incremental_auto_refresh_interval: The refresh interval. The value ranges from 1 minute to 48 hours. incremental_guc_hg_computing_resource: The computing resource for the refresh. The value is serverless, which indicates that serverless computing resources are used for the refresh. incremental_guc_hg_experimental_serverless_computing_required_cores: The specifications of computing resources for incremental refresh. incremental_state_table_group: The table group where the state table resides in incremental refresh mode. incremental_plan: The execution plan for incremental refresh. Parameters for full refresh: full_auto_refresh_schd_start_time: The start time of the refresh. Valid values: immediate: The refresh starts immediately after the table is created. A custom time: A specific time value, such as 2024-08-27 15:00:00. full_auto_refresh_interval: The refresh interval. The value ranges from 1 minute to 48 hours. full_guc_hg_computing_resource: The computing resource used. Valid values: local: The resources of the current instance are used. serverless: Serverless resources are used. For more information about serverless resources, see Serverless Computing. full_guc_hg_experimental_serverless_computing_required_cores: The specifications of computing resources for full refresh.

The `hologres.hg_dynamic_table_dependencies` system table

The hologres.hg_dynamic_table_dependencies system table stores the data lineage of Dynamic Tables. The following table describes its fields. Note the following points:

A Dynamic Table can correspond to multiple base tables. Therefore, multiple records may exist.
A state table is also considered a base table of a Dynamic Table. By default, it is placed in the hologres_streaming_mv system schema. You can ignore it in practice.
You can use system tables such as pg_class to further differentiate the base tables of a Dynamic Table.

Field	Description
table_namespace	The schema where the base table resides.
table_name	The name of the base table.
dynamic_table_namespace	The schema where the Dynamic Table resides.
dynamic_table_name	The name of the Dynamic Table.
dependency	The type of the base table. Valid values: base_table: standard table. base_dimension_table: dimension table. internal_table: internal table. You can ignore this value in practice.

The `hologres.hg_dynamic_table_refresh_activity` system table

Note

This system table is not supported in Hologres instances of V3.1 and later.

The hologres.hg_dynamic_table_refresh_activity system table records currently running Dynamic Table refresh tasks. The following table describes its fields:

Field	Description
pid	The process ID of the Dynamic Table refresh task. You can use the PID to cancel a running refresh task. For more information, see Cancel a refresh task.
datname	The database where the Dynamic Table resides.
query_id	The query ID of the Dynamic Table refresh task.
usename	The user who performs the Dynamic Table refresh.
query	The refresh query.
refresh_mode	The refresh mode of the Dynamic Table. Valid values: full: full refresh. incremental: incremental refresh. none: No refresh mode is set.
refresh_start	The start time of the refresh task.
duration	The running time of the refresh task.
serverless_queue_time_ms	The queuing time for the refresh task to use serverless resources. Note This field is displayed only for refreshes that are performed using Serverless Computing resources.
serverless_resource_used_time_ms	The time that the refresh task uses serverless resources. Note This field is displayed only for refreshes that are performed using Serverless Computing resources.
serverless_allocated_cores	The specifications of the serverless resources used by the refresh task. Note This field is displayed only for refreshes that are performed using Serverless Computing resources.
serverless_allocated_workers	The number of serverless workers used by the refresh task. Note This field is displayed only for refreshes that are performed using Serverless Computing resources.
table_write	The Dynamic Table on which the refresh task is performed. Note This field is displayed only for refreshes that are performed using Serverless Computing resources.

The `hologres.hg_dynamic_table_refresh_history` system table

The hologres.hg_dynamic_table_refresh_history system table stores the historical data of Dynamic Table refresh tasks. By default, the data is retained for one month. The following table describes its fields.

Field	Description
datname	The name of the database where the Dynamic Table resides.
schema_name	The schema where the Dynamic Table resides.
dynamic_table_name	The name of the Dynamic Table.
query_id	The query ID of the refresh. You can use the query ID to go to Query Insight to view more detailed query information.
refresh_start	The start time of the refresh.
refresh_end	The end time of the refresh.
duration	The duration of the refresh.
refresh_latency	The data latency when the refresh was completed.
refresh_mode	The refresh mode of the Dynamic Table. Valid values: full: full refresh. incremental: incremental refresh. none: No refresh mode is set.
status	The status of the refresh. Valid values: SUCCESS: Success. FAILED: The task failed.
queue_time_ms	The queuing time to request serverless resources. Note This field is displayed only for refreshes that are performed using Serverless Computing resources.
serverless_allocated_cores	The amount of serverless resources used, in CUs. Note This field is displayed only for refreshes that are performed using Serverless Computing resources.
serverless_allocated_workers	The number of serverless workers used. Note This field is displayed only for refreshes that are performed using Serverless Computing resources.
serverless_resource_used_time_ms	The actual time that serverless resources were used to execute the query, in milliseconds. This does not include the queuing time for resources. Note This field is displayed only for refreshes that are performed using Serverless Computing resources.

References

For more information about how to use these system tables, see the following topics:

The hologres.hg_dynamic_table_properties system table

The hologres.hg_dynamic_table_dependencies system table

The hologres.hg_dynamic_table_refresh_activity system table

The hologres.hg_dynamic_table_refresh_history system table

References

The `hologres.hg_dynamic_table_properties` system table

The `hologres.hg_dynamic_table_dependencies` system table

The `hologres.hg_dynamic_table_refresh_activity` system table

The `hologres.hg_dynamic_table_refresh_history` system table