Hologres:Grant data access to virtual warehouses

How it works

Each table group has exactly one leader virtual warehouse — the virtual warehouse in which the table group was created. Only the leader can perform DML operations (INSERT, UPDATE, DELETE) on tables in that table group.

Any other virtual warehouse can become a follower virtual warehouse by loading the table group into it. A follower can read tables in the table group but cannot write to them directly. To write from a follower, enable DML auto-routing, which transparently forwards write operations to the leader.

Memory state is synchronized from the leader to all followers in real time at the millisecond level. This synchronization adds a small amount of CPU and memory overhead to follower virtual warehouses. Keep the size difference between virtual warehouses within a 4x ratio to avoid performance degradation.

Version differences:

• Hologres V2.0: A virtual warehouse must have a table group loaded to access it. Without loading, neither reads nor writes are possible.

• Hologres V4.0: After you enable the GUC parameter hg_warehouse_enable_use_local_resource, virtual warehouses without the table group loaded can run read and write tasks that are not optimized by Fixed Plan. Real-time writes (Fixed Plan-optimized) still require the leader virtual warehouse. All write tasks, such as real-time and batch writes, can still be routed to the leader virtual warehouse for execution using the DML auto-routing feature.

The following table summarizes read/write capabilities by virtual warehouse type in Hologres V4.0:

Only the default virtual warehouse of an instance can write data to foreign tables.

Prerequisites

Before you begin, ensure that you have:

• Superuser permissions for the Hologres instance (required for all management operations in this topic)

• An existing table group (see Manage table groups)

View table group access permissions for a virtual warehouse

Run the following SQL statement to view the table group permissions for all virtual warehouses in the current instance:

SELECT * FROM hologres.hg_warehouse_table_groups;

To check the permissions of a specific virtual warehouse, filter by warehouse name:

SELECT * FROM hologres.hg_warehouse_table_groups
WHERE warehouse_name = '<warehouse_name>';

The hg_warehouse_table_groups table contains the following columns:

Load a table group into a virtual warehouse

Loading a table group into a virtual warehouse makes that virtual warehouse a follower for the table group, enabling it to read tables in the group.

If a follower virtual warehouse executes a write operation with DML auto-routing disabled, the operation fails with an error. Enable DML auto-routing to forward writes to the leader automatically.

Syntax:

CALL hg_table_group_load_to_warehouse ('<database_name>.<table_group_name>', '<warehouse_name>', <replica_count>);

Parameters:

Examples:

-- Load table_group_1 in the db1 database into the warehouse_1 virtual warehouse with one replica.
CALL hg_table_group_load_to_warehouse ('db1.table_group_1', 'warehouse_1');

-- Load table_group_1 in the db1 database into the warehouse_1 virtual warehouse with two replicas.
CALL hg_table_group_load_to_warehouse ('db1.table_group_1', 'warehouse_1', 2);

Set the leader virtual warehouse for a table group

Each table group has exactly one leader virtual warehouse. To change the leader, run the following command. The new leader virtual warehouse restarts during the operation — plan for potential service impact before proceeding.

Syntax:

CALL hg_table_group_set_leader_warehouse ('<database_name>.<table_group_name>', '<warehouse_name>');

Parameters:

Unload a table group from a virtual warehouse

Unloading removes follower access from the virtual warehouse. Before unloading, verify that the target virtual warehouse is not the current leader — you cannot unload a table group from its leader. Change the leader first if needed.

Syntax:

CALL hg_table_group_unload_from_warehouse ('<database_name>.<table_group_name>', '<warehouse_name>');

Parameters:

Change the number of replicas for a table group in a virtual warehouse

Syntax:

CALL hg_table_group_set_warehouse_replica_count ('<database_name>.<table_group_name>', <replica_count>, '<warehouse_name>');

Parameters:

DML auto-routing for virtual warehouses (Beta)

DML auto-routing automatically forwards write operations from a follower virtual warehouse to the leader virtual warehouse of the table group. This lets applications connect to any follower and issue writes without manually routing to the leader.

Available starting with Hologres V2.2.

How it works: When enabled, write tasks run on the leader's resources. However, queries per second (QPS) metrics for those write operations are recorded on the follower, not the leader.

-- Enable DML auto-routing.
SET hg_experimental_enable_warehouse_dml_auto_routing = ON;

-- Disable DML auto-routing.
SET hg_experimental_enable_warehouse_dml_auto_routing = OFF;

-- Enable DML auto-routing.
ALTER DATABASE <database_name> SET hg_experimental_enable_warehouse_dml_auto_routing = ON;

-- Disable DML auto-routing.
ALTER DATABASE <database_name> SET hg_experimental_enable_warehouse_dml_auto_routing = OFF;

What's next

• Manage table groups

• GUC parameters reference

• Accelerate SQL execution with Fixed Plan

• SQL transaction capabilities