Hologres:O&M for Hologres dynamic table refresh tasks

Monitoring metrics

Starting from Hologres V4.0.8, dynamic tables provide the following monitoring metrics to help you manage refresh tasks:

Alerting

You can configure alert rules for dynamic table refresh tasks in Cloud Monitor to detect anomalies promptly. For more information, see Cloud Monitor.

View running refresh tasks

View using hologres.hg_dynamic_table_refresh_activity

You can use the hologres.hg_dynamic_table_refresh_activity system table to view running refresh tasks—including full and incremental refreshes—and their resource consumption. For more information about the fields in the hologres.hg_dynamic_table_refresh_activity system table, see hologres.hg_dynamic_table_refresh_activity system table.

-- View currently running refresh tasks
SELECT
    pid,
    query_id,
    refresh_mode,
    'RUNNING' as status,
    refresh_start,
    extract(epoch from duration) as duration, -- milliseconds
    serverless_queue_time_ms::bigint / 1000 AS serverless_queue_time_sec,
    serverless_resource_used_time_ms::bigint / 1000 AS serverless_resource_used_time_sec,
    serverless_allocated_cores
FROM
    hologres.hg_dynamic_table_refresh_activity
WHERE datname = '${database}'
  AND table_write = quote_ident('${schema}') || '.' || quote_ident('${tableName}')
ORDER BY refresh_start DESC
limit 2000;

View using hg_stat_activity

You can use the hg_stat_activity system view to view running refresh tasks. The display differs depending on the refresh mode in hg_stat_activity:

• Full refresh: An `INSERT` statement appears.

• Incremental refresh: A `Refresh` task appears.

View refresh tasks using monitoring metrics

You can check metrics such as QPS, records per second (RPS), and latency to confirm the execution status of dynamic table refresh tasks. A `Command Type` of `refresh` indicates a dynamic table refresh task. For more information about monitoring metrics, see Hologres console monitoring metrics.

If a dynamic table refresh task runs on Serverless Computing resources, you can also view its status in the relevant Serverless Computing metrics.

View historical refresh tasks

View using hologres.hg_dynamic_table_refresh_history

The hologres.hg_dynamic_table_refresh_history system table records the history of all dynamic table refresh tasks for the last month, including full, incremental, and manual refreshes. For more information about the fields in the hologres.hg_dynamic_table_refresh_history system table, see hologres.hg_dynamic_table_refresh_history system table.

• Example 1: View incremental refresh records from the past day.

  -- Example 1: View the records of incremental refreshes over the past day
  SELECT
      query_id,
      refresh_mode,
      status,
      refresh_start,
      duration,
      refresh_latency / 1000 AS refresh_latency_second,
      serverless_allocated_cores,
      queue_time_ms::bigint / 1000 AS queue_time_second,
      serverless_resource_used_time_ms::bigint / 1000 AS serverless_resource_used_time_second
  FROM
      hologres.hg_dynamic_table_refresh_history
  WHERE
      refresh_start >= CURRENT_DATE - INTERVAL '1 day'
      AND dynamic_table_name = '<dynamic_table>'
      AND refresh_mode = 'incremental'
  ORDER BY
      refresh_start DESC
  limit 100;
  

• Example 2: View all refresh tasks in the current instance from the past day.

  -- View all refresh records in the instance over the past day
  SELECT 
  query_id,
      refresh_mode,
      status,
      refresh_start,
      duration,
      refresh_latency / 1000 AS refresh_latency_second,
      serverless_allocated_cores,
      queue_time_ms::bigint / 1000 AS queue_time_second,
      serverless_resource_used_time_ms::bigint / 1000 AS serverless_resource_used_time_second
  FROM hologres.hg_dynamic_table_refresh_history where refresh_start >= CURRENT_DATE - INTERVAL '1 day'

• Example 3: View refresh records for a specific table from the past day.

  -- View the refresh records for a specific table over the past day
  SELECT 
  query_id,
      refresh_mode,
      status,
      refresh_start,
      duration,
      refresh_latency / 1000 AS refresh_latency_second,
       serverless_allocated_cores,
      queue_time_ms::bigint / 1000 AS queue_time_second,
      serverless_resource_used_time_ms::bigint / 1000 AS serverless_resource_used_time_second
  FROM hologres.hg_dynamic_table_refresh_history where schema_name='<scehma_name>' and dynamic_table_name='<dynamic_table>' and  refresh_start >= CURRENT_DATE - INTERVAL '1 day'

-- Get the cron_job_name
SELECT property_value AS cron_job_name
FROM hologres.hg_dynamic_table_properties
WHERE dynamic_table_name = '<dt_name>' AND property_key = 'cron_job_name';

-- Query Cron task execution records by cron_job_name
SELECT *
FROM hologres.hg_user_cron_tasks
WHERE jobname = '<cron_job_name>'
ORDER BY start_time DESC;

View using slow query logs

You can view dynamic table refresh tasks in the slow query logs. The `Command Type` is `refresh`. For more information about viewing slow query logs, see View and analyze slow query logs.

Table-level settings

When you create a dynamic table, you can set a refresh task timeout duration that applies to all refresh tasks for that table. The following SQL code uses the tpch_10g public dataset as an example. Before you run the code, make sure you have successfully imported the tpch_10g public dataset. For more information, see Create a task to import a public dataset.

-- Set the refresh task timeout duration when creating the table.
CREATE DYNAMIC TABLE tpch_q1_batch
  WITH (
    refresh_mode='full',
    auto_refresh_enable='true',
    full_auto_refresh_interval='1 hours',
    refresh_guc_statement_timeout='30 mins'-- The refresh timeout duration is 30 mins
       ) 
AS
  SELECT
        l_returnflag,
        l_linestatus,
        SUM(l_quantity) AS sum_qty,
        SUM(l_extendedprice) AS sum_base_price,
        SUM(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
        SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
        AVG(l_quantity) AS avg_qty,
        AVG(l_extendedprice) AS avg_price,
        AVG(l_discount) AS avg_disc,
        COUNT(*) AS count_order
FROM
        hologres_dataset_tpch_10.lineitem
WHERE
        l_shipdate <= DATE '1998-12-01' - INTERVAL '120' DAY
GROUP BY
        l_returnflag,
        l_linestatus;

Session-level settings

When you perform a manual refresh, you can set the timeout duration using a session-level Grand Unified Configuration (GUC) parameter. The following is a sample SQL statement:

SET statement_timeout = <time>;
refresh DYNAMIC TABLE <dynamic_schema_name.dynamic_table_name>;

For more information about settings, see Modify the timeout duration for active queries.

Set using `refresh with option`

When you perform a manual refresh, you can also use refresh ... with (refresh_guc_statement_timeout = '...') to specify a timeout duration for the current refresh. The following is an example.

REFRESH DYNAMIC TABLE <schema_name.table_name> WITH (
    refresh_guc_statement_timeout = '30 mins'
);

Dynamic tables created with the new 3.1 syntax

-- Get the query_job_id
SELECT query_job_id
FROM hologres.hg_dynamic_table_refresh_log('<dt_name>')
WHERE status = 'Running';

-- Cancel the refresh task based on the query_job_id
SELECT hologres.hg_internal_cancel_query_job('<query_job_id>');

ALTER DYNAMIC TABLE [ IF EXISTS ] [<schema>.]<table_name> SET (auto_refresh_enable=false);

Dynamic tables created with the 3.0 syntax

-- pid is the refresh task ID. You can obtain the query_id by viewing the refresh task.
SELECT pg_cancel_backend(<pid>);

ALTER DYNAMIC TABLE [IF EXISTS ] [<schema>.]<table_name> set (auto_refresh_enable=false);