Inspect Dynamic Table Schema and Trace Data Lineage - Hologres

When you inspect, audit, or debug Dynamic Tables in Hologres, you need to retrieve DDL definitions, list all Dynamic Tables in an instance, trace data lineage, check storage consumption, and manage state tables generated by incremental refresh. This page covers all of these tasks.

Task	Description
View the schema of a Dynamic Table	Retrieve the DDL statement for a specific Dynamic Table.
List Dynamic Tables in the current instance	Query all Dynamic Tables and their refresh modes.
View data lineage	Trace upstream and downstream dependencies using the console or SQL.
View storage details	Check the storage size of a Dynamic Table.
Manage state tables	Understand and monitor the state table generated for incremental refresh.

View the schema of a Dynamic Table

Use hg_dump_script to retrieve the DDL statement for a Dynamic Table.

SELECT hg_dump_script('[<schema_name>.]<table_name>');

Parameters

Parameter	Required	Description
`schema_name`	No	The name of the schema that contains the Dynamic Table.
`table_name`	Yes	The name of the Dynamic Table.

List Dynamic Tables in the current instance

The hologres.hg_dynamic_table_properties system table stores metadata for all Dynamic Tables in the current instance.

To list all Dynamic Tables and their refresh modes:

SELECT dynamic_table_name, property_value
FROM hologres.hg_dynamic_table_properties
WHERE property_key = 'refresh_mode';

To view all metadata for a specific Dynamic Table:

SELECT *
FROM hologres.hg_dynamic_table_properties
WHERE dynamic_table_name = '<dynamic_table_name>';

Columns in `hg_dynamic_table_properties`

Column	Description
`dynamic_table_name`	The name of the Dynamic Table.
`dynamic_table_namespace`	The schema that contains the Dynamic Table.
`property_key`	The metadata property name. For example, `refresh_mode`.
`property_value`	The value of the property.

View data lineage

Data lineage shows which tables a Dynamic Table reads from (upstream) and which tables or Dynamic Tables depend on it (downstream). Lineage is useful when troubleshooting refresh delays or assessing the impact of schema changes across a pipeline.

Visualization

View lineage as an interactive graph in HoloWeb or DataWorks Data Map:

HoloWeb: In the HoloWeb console, go to Table Metadata Management > Dynamic Table Management. On the Lineage Information tab, view the lineage graph for the Dynamic Table.
DataWorks [Data Map](https://www.alibabacloud.com/help/en/dataworks/user-guide/data-map/): DataWorks Data Map automatically collects and displays the data lineage of Dynamic Tables. For more information, see Data lineage.

SQL method

All lineage queries use the hologres.hg_dynamic_table_dependencies system view, which records the upstream dependencies of each Dynamic Table.

Columns in `hg_dynamic_table_dependencies`

Column	Description
`dynamic_table_namespace`	The schema that contains the Dynamic Table.
`dynamic_table_name`	The name of the Dynamic Table.
`table_namespace`	The schema of the upstream dependency.
`table_name`	The name of the upstream dependency.
`dependency`	The dependency type. The value `internal_table` indicates an internal system dependency; exclude this value from lineage queries.

View the data lineage of a single Dynamic Table

SELECT
    d.*,
    CASE WHEN k.dynamic_table_namespace IS NOT NULL THEN
        'd'
    ELSE
        c.relkind
    END
FROM
    hologres.hg_dynamic_table_dependencies d
    LEFT JOIN pg_namespace n ON n.nspname = d.table_namespace
    LEFT JOIN pg_class c ON c.relnamespace = n.oid
        AND c.relname = d.table_name
    LEFT JOIN (
        SELECT
            dynamic_table_namespace,
            dynamic_table_name
        FROM
            hologres.hg_dynamic_table_properties
        GROUP BY
            1,
            2) k ON k.dynamic_table_namespace = d.table_namespace
        AND k.dynamic_table_name = d.table_name
WHERE
    d.dynamic_table_namespace = '<schema_name>'
    AND d.dynamic_table_name = '<dynamic_table_name>'
    AND d.dependency <> 'internal_table';

View the data lineage of all Dynamic Tables

Remove the dynamic_table_namespace and dynamic_table_name filters to return dependencies for all Dynamic Tables in the instance:

SELECT
    d.*,
    CASE WHEN k.dynamic_table_namespace IS NOT NULL THEN
        'd'
    ELSE
        c.relkind
    END
FROM
    hologres.hg_dynamic_table_dependencies d
    LEFT JOIN pg_namespace n ON n.nspname = d.table_namespace
    LEFT JOIN pg_class c ON c.relnamespace = n.oid
        AND c.relname = d.table_name
    LEFT JOIN (
        SELECT
            dynamic_table_namespace,
            dynamic_table_name
        FROM
            hologres.hg_dynamic_table_properties
        GROUP BY
            1,
            2) k ON k.dynamic_table_namespace = d.table_namespace
        AND k.dynamic_table_name = d.table_name
WHERE
    d.dependency <> 'internal_table';

Understanding `base_table_type`

The CASE expression derives base_table_type from two sources: if the dependency is itself a Dynamic Table (found in hg_dynamic_table_properties), the type is 'd'; otherwise, the type comes from pg_class.relkind. The following table describes the possible values.

Value	Table type
`r`	Ordinary table
`v`	View
`m`	Materialized view
`f`	Foreign table
`d`	Dynamic Table

View storage details

Use the hologres.hg_relation_size function to view the storage size and details of a Dynamic Table. For more information, see View table storage details.

Manage state tables

When a Dynamic Table uses incremental refresh, Hologres automatically creates a state table at the underlying layer to accelerate data computing. The state table stores aggregation results.

Key behaviors:

The state table is stored in the default table group and does not support resharding.
If you switch the refresh mode from incremental to full refresh, the state table is cleared by default.

For more information about incremental refresh and full refresh, see Dynamic Table.

View state table storage usage

Run the following SQL command to get the storage size of the state table. Storage is billed at the same rate as a standard table. For more information, see Billing overview.

SELECT pg_size_pretty(hologres.hg_dynamic_table_state_size('<dynamic_table_name>'));