Hologres:Maintain refresh tasks for dynamic tables in Hologres

Query running refresh tasks

Use the hologres.hg_dynamic_table_refresh_activity system table

You can query full and incremental data refresh tasks that are running and resource consumption of the refresh tasks from the hologres.hg_dynamic_table_refresh_activity system table.

-- Query running refresh tasks
SELECT
    pid,
    query_id,
    refresh_mode,
    'RUNNING' as status,
    refresh_start,
    extract(epoch from duration) as duration, -- seconds
    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;

Use the hg_stat_activity system view

You can query running refresh tasks from the hg_stat_activity system view. The content that can be displayed varies based on the refresh mode that you configured.

• Full data refresh: INSERT statements are displayed.

• Incremental data refresh: Refresh tasks are displayed.

Use metrics to check the status of refresh tasks

You can use metrics such as the queries per second (QPS), records per second (RPS), and latency to check the status of refresh tasks. The value refresh of the Command Type parameter indicates a refresh task for a dynamic table. For more information about metrics, see Hologres metrics.

If you use serverless computing resources to run a refresh task for a dynamic table, you can also view serverless computing-related metrics to check the status of the task.

Query historical refresh tasks

Use the hologres.hg_dynamic_table_refresh_history system table

The hologres.hg_dynamic_table_refresh_history system table records all historical refresh tasks within the most recent month, including full data refresh, incremental data refresh, and manual refresh tasks.

• Example 1: Query all incremental data refresh tasks in the past day.

  -- Query all incremental data refresh tasks in 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: Query all refresh tasks in the current instance in the past day.

  -- Query all refresh tasks in the current instance in 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: Query the refresh tasks for a specific table in the past day.

  -- Query the refresh tasks for a specific table in 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'

Use slow query logs

You can query refresh tasks of a dynamic table in slow query logs. The value refresh of the Command Type parameter indicates a refresh task of a dynamic table. For more information, see Query and analyze slow query logs.

Table-level settings

If you configure a timeout period when you create a dynamic table,.the setting takes effect for all refresh tasks of the created table. In the following SQL statement, the public dataset tpch_10g is used as an example. Before you execute the statement, make sure that the data in the tpch_10g public dataset is imported to Hologres. For more information, see Create a public dataset import task.

-- Configure a timeout period for refresh tasks when you create a 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 timeout period is 30 minutes.
       ) 
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(*) asAScount_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

Manually configure a timeout period. Sample SQL code:

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

For more information, see Change the timeout period of active queries.

Cancel running refresh tasks

If a refresh task has been running for a long time or is stuck, you can use the pg_cancel_backend function to cancel the task.

You can cancel a single running refresh task by executing the following statement:

// The pid parameter specifies the ID of the refresh task that you want to cancel.
SELECT pg_cancel_backend(<pid>);

The pid parameter specifies the ID of a refresh task. You can query the refresh task to obtain the ID (query_id). For more information, see the Query refresh tasks section in this topic.

Cancel all refresh tasks of a dynamic table

If refresh tasks are configured for a dynamic table, you can cancel all subsequent refresh tasks at the table level.

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