Hologres:Set dynamic table refresh resources

Choose a resource type

For details on Serverless Computing, see Serverless Computing overview. For details on virtual warehouses, see Getting started with virtual warehouses.

Parameter reference

The computing_resource parameter is optional. Set it in the WITH clause when creating a dynamic table, or pass it explicitly during a manual refresh.

Syntax

-- Specify refresh resources when creating the table.
CREATE [EXTERNAL] DYNAMIC TABLE [ IF NOT EXISTS ] [<schema_name>.]<table_name>
  [ (<col_name> [, ...] ) ]
  [LOGICAL PARTITION BY LIST(<partition_key>)]
  WITH (
    freshness = '<num> {minutes | hours}', -- Required.
    [computing_resource = {'local' | 'serverless' | '<warehouse_name>'},] -- Optional.
  )
  AS
  <query>; -- Definition of the query.

-- Specify refresh resources during a manual refresh.
REFRESH DYNAMIC TABLE [<schema_name>.]<table_name>
  WITH (computing_resource = 'local' | 'serverless' | '<warehouse_name>');

CREATE [EXTERNAL] DYNAMIC TABLE [ IF NOT EXISTS ] [<schema_name>.]<table_name>
  [ (<col_name> [, ...] ) ]
  [LOGICAL PARTITION BY LIST(<partition_key>)]
  WITH (
    freshness = '<num> {minutes | hours}', -- Required.
    [computing_resource = {'local' | 'serverless'},] -- Optional.
  )
  AS
  <query>; -- Definition of the query.

REFRESH DYNAMIC TABLE [<schema_name>.]<table_name>
  WITH ([computing_resource = 'local' | 'serverless']);

CREATE [EXTERNAL] DYNAMIC TABLE [ IF NOT EXISTS ] [<schema_name>.]<table_name>
  [ (<col_name> [, ...] ) ]
  [LOGICAL PARTITION BY LIST(<partition_key>)]
  WITH (
    freshness = '<num> {minutes | hours}', -- Required.
    [computing_resource = {'local' | 'serverless'},] -- Optional.
  )
  AS
  <query>; -- Definition of the query.

REFRESH DYNAMIC TABLE [<schema_name>.]<table_name>
  WITH ([computing_resource = 'local' | 'serverless']);

CREATE DYNAMIC TABLE [IF NOT EXISTS] <schema.tablename>(
  [col_name],
  [col_name]
) [PARTITION BY LIST (col_name)]
WITH (
  [refresh_mode='[full|incremental]',]
  [incremental_auto_refresh_schd_start_time='[immediate|<timestamptz>]',]
  [incremental_auto_refresh_interval='[<num> {minute|minutes|hour|hours}]',]
  [incremental_guc_hg_computing_resource='[local|serverless]',]
  [full_guc_hg_computing_resource='[local|serverless]',]
)
AS
<query>; -- Definition of the query.

REFRESH DYNAMIC TABLE [<schema_name>.]<table_name>
  WITH (
    [incremental_guc_hg_computing_resource = 'local' | 'serverless',]
    [full_guc_hg_computing_resource = 'local' | 'serverless']
  );

Default refresh resources by version

Usage notes

• If you set computing_resource to a warehouse_name and then delete that virtual warehouse, the refresh fails.

• If you set computing_resource to a warehouse_name and then rename that warehouse, the refresh continues normally. The new warehouse name appears in the DDL.

• If you set computing_resource to serverless but the dynamic table's query does not meet the conditions for serverless execution, the refresh automatically falls back to local resources: The DDL (viewable via hg_dump_script) still shows computing_resource = 'serverless' after a fallback. If the query later meets serverless execution conditions, subsequent refreshes use serverless resources. To check which resources were actually used for a specific refresh, check the slow query log.

  • On a virtual warehouse instance: local refers to the leader warehouse of the TG where the dynamic table resides. For an external dynamic table, local refers to the default warehouse of the instance.

  • On a non-virtual warehouse instance: local refers to the reserved resources of the instance.

Upgrade notes

The following tables summarize how refresh resource behavior changes when you upgrade Hologres. The key difference is between tables created with the new syntax (V3.1+) and tables that still use the legacy V3.0 syntax.