Hologres:ALTER DYNAMIC TABLE

V4.0.7 and later

-- Modify the computing resource for the refresh. You can choose local execution or serverless execution.
ALTER [DYNAMIC] TABLE [ IF EXISTS ] [<schema>.]<table_name> SET (['local' | 'serverless' | '<warehouse_name>'])

-- Modify the computing specifications for serverless execution.
ALTER DYNAMIC TABLE [ IF EXISTS ] [<schema>.]<table_name> SET (auto_refresh_guc_hg_experimental_serverless_computing_required_cores='<num>')

V3.1

-- Modify the computing resource for the refresh. You can choose local execution or serverless execution.
ALTER DYNAMIC TABLE [ IF EXISTS ] [<schema_name>.]<table_name> 
SET (computing_resource ='[local|serverless]');

-- Modify the computing specifications for serverless execution.
ALTER DYNAMIC TABLE [ IF EXISTS ] [<schema_name>.]<table_name> 
SET (refresh_guc_hg_experimental_serverless_computing_required_cores='<num>');

V3.0

-- Modify the refresh resource for a single table.
ALTER DYNAMIC TABLE [ IF EXISTS ] [<schema_name>.]<table_name> 
SET (<incremental/full>_guc_hg_computing_resource ='[local|serverless]');

-- If it is in serverless mode, you can also modify the serverless computing specifications.
ALTER DYNAMIC TABLE [ IF EXISTS ] [<schema_name>.]<table_name> 
SET (<incremental/full>_guc_hg_experimental_serverless_computing_required_cores='<num>');

V3.1 syntax

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

V3.0 syntax

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

V3.1 syntax

Starting from Hologres V3.1.18, you can modify the query definition of a dynamic table and add columns to the dynamic table by adding columns to the query.

ALTER DYNAMIC TABLE [ IF EXISTS ] [<schema_name>.]<table_name> 
AS
<query>; -- The new query definition.

• Usage notes:

  • The new query definition must meet the following requirements:

    • The dynamic table generated by the new query must have a table schema that is compatible with the dynamic table generated by the original query. This includes the following requirements:

      • Both tables must be partitioned tables or non-partitioned tables.

      • The primary keys (PKs) of both tables must be the same.

      • The distribution keys of both tables must be the same. You cannot modify the distribution key.

      • The partition keys of both tables must be the same. You cannot modify the partition key.

      • The clustering key, segment key, storage mode (such as column store or row-column hybrid store), dictionary encoding, and bitmap index of both tables must be the same.

      • The field names of both tables must be the same. You cannot modify the field names.

      • Fields with the same name must have the same data type and NULLABLE property.

    • You can only add columns in the query. You cannot drop columns. When you add a column, existing data remains unchanged. The new column is populated with NULL values by default. The data is updated after the next refresh.

  • After you modify the query definition, the refresh behavior is as follows:

    • Non-partitioned dynamic table: The next refresh, whether it is an auto-refresh or a manual refresh, triggers a Refresh Overwrite.

    • Dynamic table with logical partitions: Active partitions automatically trigger a Refresh Overwrite during the next auto-refresh. Historical partitions require a manual refresh.

    After you modify the query definition, if the query contains operators that are not supported by incremental refresh, the refresh behavior is as follows:

    Refresh mode	New query definition supports incremental refresh	New query definition does not support incremental refresh
    auto_refresh_mode = 'auto'	The refresh_mode specified during table creation is retained.	The refresh_mode automatically changes to full refresh.
    auto_refresh_mode = 'incremental'	Incremental refresh is retained.	The refresh fails. You must manually change the mode to full refresh.
    auto_refresh_mode = 'full'	Full refresh is retained.	Full refresh is retained.

• Examples

  1. Prepare the sample data.

     -- Prepare a source table.
     CREATE TABLE employees (
         department_id INT,
         employee_name VARCHAR
     );
     -- Insert data.
     INSERT INTO employees (department_id, employee_name) VALUES
     (1, 'John'),
     (1, 'Jane'),
     (1, 'John'),  -- Duplicate value
     (2, 'Alice'),
     (2, 'Bob'),
     (2, 'Alice'); -- Duplicate value
     
     -- Create a dynamic table.
     CREATE  DYNAMIC TABLE dt_employees
     WITH (
       -- Properties of the dynamic table
       freshness = '1 minutes', 
       auto_refresh_mode = 'auto',
       distribution_key = 'department_id'
     )
     AS 
     SELECT
         department_id
     FROM employees   
     GROUP BY department_id;
     
     -- Query data in the dynamic table.
     SELECT * FROM dt_employees;

  2. Add a column to the dynamic table.

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

     After the statement is successfully executed, you can query the data in the dynamic table. The data in the new column is NULL.

     department_id	｜unique_employees
     -----------｜--------
     1	｜\N
     2	｜\N

     After the next refresh is successful, the data in the new column is refreshed.

     department_id	｜unique_employees
     -------------｜---------
     1	｜Jane, John
     2 ｜	Bob, Alice

V3.0 syntax

Hologres V3.0 supports modifying a query definition. The syntax is as follows. Currently, only modifications in full refresh mode are supported. Modifications in incremental refresh mode are not.

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