Query the structure and lineage of a dynamic table - Hologres

This topic describes how to query the structure of a dynamic table, all dynamic tables in the current instance, and the data lineage and storage details of dynamic tables, along with how to manage state tables.

Query the structure of a dynamic table

Syntax

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

Parameters
- schema_name: optional. The name of the schema.
- table_name: required. The name of the dynamic table.

Query all dynamic tables in the current instance

You can execute the following SQL command on the hologres.hg_dynamic_table_properties system table to view all dynamic tables in the current instance and their configured refresh modes.

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

You can also execute the following statement to query all metadata of a specified dynamic table:

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

Query the data lineage of dynamic tables

Visualization

You can view the data lineage of a dynamic table in HoloWeb and DataWorks Data Map.

HoloWeb: In HoloWeb, from the Diagnosis and Optimization menu, choose Table Metadata Management > Dynamic Table Management. You can then view the data lineage on the Data Lineage tab.
DataWorks Data Map: DataWorks Data Map automatically collects and displays the data lineage of dynamic tables. For more information, see Data lineage.

SQL

View the data lineage of a single dynamic table

You can execute the following statement to query the data lineage of a specified dynamic table:

-- Query 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

You can execute the following statement to query the data lineage of all dynamic tables:

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';

Note

The following information describes the values of the base_table_type parameter:

r: indicates a standard table.
v: indicates a view.
m: indicates a materialized view.
f: indicates a foreign table.
d: indicates a dynamic table.

Query the storage details of dynamic tables

You can use the hologres.hg_relation_size function to view the storage size and details of a dynamic table. For more information about the hologres.hg_relation_size function, see View table storage details.

Manage state tables

When you create a dynamic table in incremental data refresh mode, the system generates a state table at the underlying layer to store data aggregation results. The state table helps accelerate data computing. By default, the state table is stored in the default table group and does not support resharding. For more information, see Dynamic table. If you change the refresh mode from incremental data refresh to full data refresh, the state table is deleted by default.

Query the storage size of a state table

You can execute the following statement to query the storage size of a state table. The storage billing rules for a state table are the same as those for a standard table. For more information, see Billing overview.

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