Hologres:Alter dynamic table

Syntax

The following statements summarize all supported ALTER DYNAMIC TABLE operations. Detailed usage, parameters, and version constraints are covered in each section below.

-- Pause or resume auto-refresh
ALTER DYNAMIC TABLE [IF EXISTS] [<schema_name>.]<table_name> SET (auto_refresh_enable = false | true);

-- Modify freshness (V3.1+ syntax only)
ALTER DYNAMIC TABLE [IF EXISTS] [<schema_name>.]<table_name> SET (freshness = '<num> {minutes | hours}');

-- Modify computing resource
ALTER [DYNAMIC] TABLE [IF EXISTS] [<schema>.]<table_name> SET (['local' | 'serverless' | '<warehouse_name>']); -- V4.0.7+
ALTER DYNAMIC TABLE [IF EXISTS] [<schema>.]<table_name> SET (auto_refresh_guc_hg_experimental_serverless_computing_required_cores = '<num>'); -- V4.0.7+

-- Modify refresh mode
ALTER DYNAMIC TABLE <schema_name>.<table_name> SET (auto_refresh_mode = '<full/auto>');  -- V3.1+
ALTER DYNAMIC TABLE <schema_name>.<table_name> SET (refresh_mode = 'full');                -- V3.0

-- Modify active partition time range (V3.1+ only)
ALTER DYNAMIC TABLE [IF EXISTS] [<schema_name>.]<table_name> SET (auto_refresh_partition_active_time = '<num> {minutes | hours | days}');

-- Modify query definition (V3.1.18+)
ALTER DYNAMIC TABLE [IF EXISTS] [<schema_name>.]<table_name> AS <query>;

-- Rename the table (V3.1.20+)
ALTER DYNAMIC TABLE [IF EXISTS] <table_name> RENAME TO <new_name>;

-- Modify dictionary encoding, bitmap index, TTL, and schema
ALTER DYNAMIC TABLE [<schema_name>.]<table_name> SET (dictionary_encoding_columns = '[<columnName>{:[on|off|auto]}[,...]]');
ALTER DYNAMIC TABLE [<schema_name>.]<table_name> SET (bitmap_columns = '[<columnName>{:[on|off]}[,...]]');
ALTER DYNAMIC TABLE [<schema_name>.]<table_name> SET (time_to_live_in_seconds = '<num>');
ALTER DYNAMIC TABLE [IF EXISTS] [<schema_name>.]<table_name> SET SCHEMA <new_schema>;

Manage refresh tasks

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

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

ALTER DYNAMIC TABLE [IF EXISTS] [<schema_name>.]<table_name> SET (freshness = '<num> {minutes | hours}');

-- Switch computing resource type.
ALTER [DYNAMIC] TABLE [IF EXISTS] [<schema>.]<table_name> SET (['local' | 'serverless' | '<warehouse_name>']);

-- Set Serverless compute size (number of vCPUs).
ALTER DYNAMIC TABLE [IF EXISTS] [<schema>.]<table_name>
  SET (auto_refresh_guc_hg_experimental_serverless_computing_required_cores = '<num>');

-- Switch computing resource type.
ALTER DYNAMIC TABLE [IF EXISTS] [<schema_name>.]<table_name>
  SET (computing_resource = '[local|serverless]');

-- Set serverless compute size.
ALTER DYNAMIC TABLE [IF EXISTS] [<schema_name>.]<table_name>
  SET (refresh_guc_hg_experimental_serverless_computing_required_cores = '<num>');

-- Switch computing resource type.
ALTER DYNAMIC TABLE [IF EXISTS] [<schema_name>.]<table_name>
  SET (<incremental/full>_guc_hg_computing_resource = '[local|serverless]');

-- Set serverless compute size.
ALTER DYNAMIC TABLE [IF EXISTS] [<schema_name>.]<table_name>
  SET (<incremental/full>_guc_hg_experimental_serverless_computing_required_cores = '<num>');

ALTER DYNAMIC TABLE <schema_name>.<table_name> SET (auto_refresh_mode = '<full/auto>');

ALTER DYNAMIC TABLE <schema_name>.<table_name> SET (refresh_mode = 'full');

ALTER DYNAMIC TABLE [IF EXISTS] [<schema_name>.]<table_name>
  SET (auto_refresh_partition_active_time = '<num> {minutes | hours | days}');

ALTER DYNAMIC TABLE [IF EXISTS] [<schema_name>.]<table_name>
  SET (<incremental/full>_auto_refresh_schd_start_time = '<timestamptz>');

ALTER DYNAMIC TABLE [IF EXISTS] [<schema_name>.]<table_name>
  SET (<incremental/full>_auto_refresh_interval = '[<num> { minutes | hours}]');

Modify the query definition

ALTER DYNAMIC TABLE [IF EXISTS] [<schema_name>.]<table_name>
AS
<query>;

-- Create a source table.
CREATE TABLE employees (
    department_id INT,
    employee_name VARCHAR
);

-- Insert sample data.
INSERT INTO employees (department_id, employee_name) VALUES
(1, 'John'),
(1, 'Jane'),
(1, 'John'),   -- duplicate
(2, 'Alice'),
(2, 'Bob'),
(2, 'Alice');  -- duplicate

-- Create a dynamic table that groups employees by department.
CREATE DYNAMIC TABLE dt_employees
WITH (
    freshness            = '1 minutes',
    auto_refresh_mode    = 'auto',
    distribution_key     = 'department_id'
)
AS
SELECT department_id
FROM employees
GROUP BY department_id;

-- Verify the initial data.
SELECT * FROM dt_employees;

ALTER DYNAMIC TABLE dt_employees
AS
SELECT
    department_id,
    STRING_AGG(DISTINCT employee_name, ', ') AS unique_employees
FROM employees
GROUP BY department_id;

department_id | unique_employees
--------------+------------------
1             | \N
2             | \N

department_id | unique_employees
--------------+------------------
1             | Jane, John
2             | Bob, Alice

ALTER DYNAMIC TABLE [IF EXISTS] [<schema>.]<table_name>
  SET (task_definition = $$<new_query>$$);

Modify table properties

The following table properties can be modified. Properties not listed here are not supported. For other table alterations, see ALTER TABLE.

ALTER DYNAMIC TABLE RENAME and ALTER DYNAMIC TABLE SET SCHEMA are supported in V3.1.20 and later. In earlier versions, use ALTER TABLE RENAME and ALTER TABLE SET SCHEMA.

-- Rename the table (V3.1.20+).
ALTER DYNAMIC TABLE [IF EXISTS] <table_name> RENAME TO <new_name>;

-- Modify dictionary encoding.
ALTER DYNAMIC TABLE [<schema_name>.]<table_name>
  SET (dictionary_encoding_columns = '[<columnName>{:[on|off|auto]}[,...]]');

-- Modify bitmap index.
ALTER DYNAMIC TABLE [<schema_name>.]<table_name>
  SET (bitmap_columns = '[<columnName>{:[on|off]}[,...]]');

-- Modify the time-to-live (TTL) in seconds.
ALTER DYNAMIC TABLE [<schema_name>.]<table_name>
  SET (time_to_live_in_seconds = '<num>');

-- Move the table to a different schema.
ALTER DYNAMIC TABLE [IF EXISTS] [<schema_name>.]<table_name> SET SCHEMA <new_schema>;