Introduction to system tables related to the dynamic table feature of Hologres - Hologres

This topic describes system tables that are relevant to the dynamic table feature of Hologres.

System table	Description
hologres.hg_dynamic_table_properties	Stores the metadata of dynamic tables.
hologres.hg_dynamic_table_dependencies	Stores the data lineage information of dynamic tables.
hologres.hg_dynamic_table_refresh_activity	Stores the information about refresh tasks that are running for dynamic tables.
hologres.hg_dynamic_table_refresh_history	Stores the historical data of refresh tasks for dynamic tables.

`hologres.hg_dynamic_table_properties`

The hologres.hg_dynamic_table_properties system table is used to store the metadata of dynamic tables. The following table describes the fields in the system table.

Field	Description
dynamic_table_namespace	The schema in which the dynamic table resides.
dynamic_table_name	The name of the dynamic table.
property_key and property_value	The name and value of each property in the dynamic table. Property details: Common properties: execution_mode: the refresh mode. Valid values: full: indicates the full data refresh mode. incremental: indicates the incremental data refresh mode. none: indicates that no data refresh mode is specified. task_definition: the query definition in the dynamic table. auto_refresh_enable: specifies whether to enable automatic refresh. Valid values: true false task_definition_search_path: the schema of the query. state_time_to_live_in_seconds: the lifecycle of data in the state table. Parameters related to incremental data refresh: incremental_auto_refresh_schd_start_time: the start time of the refresh operation. Valid values: immediate: The refresh operation immediately starts after a dynamic table is created. Custom time: Specify a point in time. Example: 2024-08-27 15:00:00. incremental_auto_refresh_interval: the refresh interval. Valid values: [1 minute, 48 hours]. incremental_guc_hg_computing_resource: the computing resources used for a refresh operation. Set this parameter to serverless, which indicates that serverless computing resources are used for a refresh operation. incremental_guc_hg_experimental_serverless_computing_required_cores: the specifications of computing resources used for incremental data refresh. incremental_state_table_group: the table group to which the state table belongs in incremental data refresh mode. incremental_plan: the execution plan for incremental data refresh. Parameters related to full refresh: full_auto_refresh_schd_start_time: the start time of the refresh operation. Valid values: immediate: The refresh operation immediately starts after a dynamic table is created. Custom time: Specify a point in time. Example: 2024-08-27 15:00:00. full_auto_refresh_interval: the refresh interval. Valid values: [1 minute, 48 hours]. full_guc_hg_computing_resource: the computing resources used. Valid values: local: uses the resources of the current instance. serverless: uses serverless computing resources. For more information about serverless computing resources, see User guide on Serverless Computing. full_guc_hg_experimental_serverless_computing_required_cores: the specifications of computing resources used for full data refresh.

`hologres.hg_dynamic_table_dependencies`

The hologres.hg_dynamic_table_dependencies system table is used to store the data lineage information of dynamic tables. The following table describes the fields in the system table. Take note of the following items:

A dynamic table may correspond to multiple base tables. If a dynamic table corresponds to multiple base tables, multiple data records are generated in the system table.
A state table can also be considered as a base table and is stored in the hologres_streaming_mv system schema by default. We recommend that you ignore the state table in actual use.
You can use system tables such as pg_class to further distinguish base tables.

Field	Description
table_namespace	The schema in which the base table resides.
table_name	The name of the base table.
dynamic_table_namespace	The schema in which the dynamic table resides.
dynamic_table_name	The name of the dynamic table.
dependency	The base table type. Valid values: base_table: standard table. base_dimension_table: dimension table. internal_table: internal table. You can ignore this type of base table in actual use.

`hologres.hg_dynamic_table_refresh_activity`

The hologres.hg_dynamic_table_refresh_activity system table records only the information about refresh tasks that are running for dynamic tables. The following table describes the fields in the system table.

Field	Description
pid	The process ID of a refresh task for the dynamic table. You can use the pid field to cancel a running refresh task. For more information, see the Cancel refresh tasks section of the "Maintain refresh tasks for dynamic tables" topic.
datname	The database in which the dynamic table resides.
query_id	The query ID that corresponds to the refresh task for the dynamic table.
usename	The user who performs the refresh operation.
query	The refresh query.
refresh_mode	The data refresh mode. Valid values: full: indicates the full data refresh mode. incremental: indicates the incremental data refresh mode. none: indicates that no data refresh mode is specified.
refresh_start	The start time of the refresh task.
duration	The execution duration of the refresh task.
serverless_queue_time_ms	The time spent by the refresh task in waiting for serverless computing resources. Note This field is available only for refresh tasks that are run by using serverless computing resources.
serverless_resource_used_time_ms	The time consumed by the refresh task in using serverless computing resources. Note This field is available only for refresh tasks that are run by using serverless computing resources.
serverless_allocated_cores	The specifications of serverless computing resources used for the refresh task. Note This field is available only for refresh tasks that are run by using serverless computing resources.
serverless_allocated_workers	The number of workers used by the refresh task. Note This field is available only for refresh tasks that are run by using serverless computing resources.
table_write	The dynamic table for which the refresh task is run. Note This field is available only for refresh tasks that are run by using serverless computing resources.

`hologres.hg_dynamic_table_refresh_history`

The hologres.hg_dynamic_table_refresh_history system table is used to store the historical data of refresh tasks for dynamic tables. By default, the historical data is retained for one month. The following table describes the fields in the system table.

Field	Description
datname	The name of the database in which the dynamic table resides.
schema_name	The schema in which the dynamic table resides.
dynamic_table_name	The name of the dynamic table.
query_id	The ID of the refresh query. You can go to the Query Insight page and use the query ID to view detailed query information.
refresh_start	The start time of the refresh task.
refresh_end	The end time of the refresh task.
duration	The execution duration of the refresh task.
refresh_latency	The latency generated when the refresh task finishes running.
refresh_mode	The data refresh mode. Valid values: full: indicates the full data refresh mode. incremental: indicates the incremental data refresh mode. none: indicates that no data refresh mode is specified.
status	The status of the refresh task. Valid values: SUCCESS FAILED
queue_time_ms	The time spent in applying for serverless computing resources. Note This field is available only for refresh tasks that are run by using serverless computing resources.
serverless_allocated_cores	The amount of used serverless computing resources. Unit: CUs. Note This field is available only for refresh tasks that are run by using serverless computing resources.
serverless_allocated_workers	The number of used workers. Note This field is available only for refresh tasks that are run by using serverless computing resources.
serverless_resource_used_time_ms	The time consumed in performing the query by using serverless computing resources, in milliseconds. The time spent in waiting for resources is excluded. Note This field is available only for refresh tasks that are run by using serverless computing resources.

References

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