Hologres:Query the structure and lineage of a dynamic table

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 statement to query all dynamic tables in the current instance and the refresh modes configured for the dynamic tables in the system table hologres.hg_dynamic_table_properties:

SELECT dynamic_table_name, property_value FROM hologres.hg_dynamic_table_properties WHERE property_key = 'execution_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

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

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

Query the storage details of dynamic tables

You can use the hologres.hg_relation_size function to query the storage size and details of a dynamic table. For more information about how to use the hologres.hg_relation_size function, see the Query the storage details of a table section of the "Query the storage sizes of tables and databases" topic.

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 Overview of the dynamic table feature. 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>'));