Hologres:Manage virtual warehouses

Notes

• Only Alibaba Cloud accounts or Resource Access Management (RAM) users can create virtual warehouses. The Superuser permission for the instance is required.

• Because creating a virtual warehouse is an asynchronous process, running the SQL command to create a virtual warehouse does not mean the creation is complete. You must use the hg_get_warehouse_status function to query the status of the virtual warehouse.

Methods

Notes

• Only Alibaba Cloud accounts or RAM users can delete virtual warehouses. The Superuser permission for the instance is required.

• You cannot delete the default virtual warehouse of an instance.

• You cannot use SQL commands to delete the primary virtual warehouse of a table group.

• Because deleting a virtual warehouse is an asynchronous process, running the SQL command does not mean the deletion is complete. You must use the hg_get_warehouse_status function to query the status of the virtual warehouse.

Methods

Permissions

• Before V3.0.28, only Alibaba Cloud accounts or RAM users could scale virtual warehouses. Starting from V3.0.28, RAM roles (STS accounts) can also perform this operation.

• To scale a virtual warehouse, you need the following permissions:

  • RAM role permissions: AliyunHologresWarehouseFullAccess or AliyunHologresFullAccess.

  • The Superuser permission within the instance.

For information about how elastic scaling of virtual warehouses works, see Overview of resource elasticity.

Scale up a virtual warehouse (vertical scaling)

-- Increase the resources of the virtual warehouse.
CALL hg_alter_warehouse ('read_warehouse_1',64);

-- Wait for the virtual warehouse to be scaled up.
SELECT wait_until_warehouse_running ('read_warehouse_1');

-- Rebalance.
SELECT hg_rebalance_warehouse ('read_warehouse_1');

-- Wait for the rebalancing to complete.
SELECT hg_wait_until_rebalance_warehouse_done ('read_warehouse_1');

Scale down a virtual warehouse (vertical scaling)

Scale out a virtual warehouse (horizontal scaling)

Scaling in a virtual warehouse cluster

View virtual warehouse

• Syntax

  • The hologres.hg_warehouses system table stores information about all virtual warehouses in the current instance. You can run the following SQL statement to view information about all virtual warehouses in the current instance.

    SELECT * FROM hologres.hg_warehouses;

  • Run the following SQL statement to view the currently used virtual warehouse. The system returns the name of the current virtual warehouse.

    SELECT current_warehouse();

• Parameters

  The hologres.hg_warehouses system table contains the following columns:

  Field	Data Type	Description	Example
  warehouse_id	INTEGER	The virtual warehouse ID uniquely identifies a virtual warehouse.	1
  warehouse_name	TEXT	The name of the virtual warehouse.	init_warehouse
  cpu	INTEGER	The number of CPU cores for the virtual warehouse.	32
  mem	INTEGER	Virtual warehouse memory (GB)	128
  cluster_min_count	INTEGER	The minimum number of shards for a virtual warehouse.	1
  cluster_max_count	INTEGER	The maximum number of shards for the virtual warehouse.	1
  target_status	INTEGER	The target status of the virtual warehouse. Because changing the status of a virtual warehouse is an asynchronous process, when you run an SQL statement to create a virtual warehouse, the system immediately returns the execution result, but the virtual warehouse is not yet created. The system creates the virtual warehouse asynchronously in the background. This status records the final state that the virtual warehouse should reach. Valid values: 1: Running. 2. Manually shut down the instance.	1
  status	INTEGER	The virtual warehouse can have one of the following statuses: 0: Initialization. 1: Running. 2: Manually stopped. 3: Processing failed. If the status is Processing failed, the status_detail column shows the specific reason. 4: Processing.	1
  status_detail	TEXT	Details of virtual warehouse status.	None
  is_default	BOOLEAN	Indicates whether it is the default virtual warehouse. Valid values: t is the default virtual warehouse. f: This is not the default virtual warehouse.	t
  config	TEXT	virtual warehouse configuration	None
  comment	TEXT	Notes on virtual warehouses.	None

Stop virtual warehouse

Resume virtual warehouse

Restart virtual warehouse

Retrieve virtual warehouse Status

• Syntax

  • You can use the following SQL statement to retrieve the current status of the virtual warehouse:

    select hg_get_warehouse_status('<warehouse_name>');

  • Because operations such as creating and scaling a virtual warehouse are performed asynchronously, you may need to perform other operations after the status of the virtual warehouse changes. In this case, you can use the wait_until_warehouse_running function. This function waits until the specified virtual warehouse is in the Running state before it returns a result. The command is as follows:

    select wait_until_warehouse_running('<warehouse_name>',<timeout_seconds>);

• Parameters

  Parameter	Type	Description
  warehouse_name	TEXT	The name of the virtual warehouse whose status you want to retrieve.
  timeout_seconds	INTEGER	The timeout period in seconds. This parameter is optional.

• Example

  Run the following SQL statements to scale up a virtual warehouse before importing data from the org_data table to the res_data table, and then scale down the virtual warehouse after the import is complete:

  -- Scale up warehouse_1 to 64 CUs.
  CALL hg_alter_warehouse ('warehouse_1',64);
  
  -- Wait for warehouse_1 to be scaled up.
  SELECT wait_until_warehouse_running('warehouse_1');
  
  -- Write data.
  INSERT INTO res_data SELECT * FROM org_data;
  
  -- Scale down warehouse_1 to 32 CUs.
  CALL hg_alter_warehouse ('warehouse_1',32);

Modify virtual warehouse name

Methods

Virtual warehouse rebalancing

When a Hologres instance is running, the worker nodes in each virtual warehouse evenly load the metadata of data shards. However, in some scenarios—such as after a fast recovery or scale-out—the data shards may be unevenly distributed across worker nodes. In this case, you must trigger a shard rebalancing operation to proactively redistribute the data shards and ensure that the worker nodes in the virtual warehouse load shard metadata evenly.

• Permissions

  You must have the Superuser permission for the instance to trigger a shard rebalancing operation.

• Notes

  • Starting from Hologres V2.1, if a virtual warehouse contains empty worker nodes—that is, nodes that have not loaded any shards—the virtual warehouse automatically triggers rebalancing.

  • Starting from Hologres V2.2, the system supports automatic retries for SELECT queries that run during virtual warehouse rebalancing. This allows the queries to complete successfully after a slightly longer running time and prevents errors such as ERROR: fail to execute query internal error: Get rundown is not allowed in recovering state..

• Syntax

  SELECT hg_rebalance_warehouse ('<warehouse_name>');

• Parameters

  Parameter	Type	Description
  warehouse_name	TEXT	The name of the virtual warehouse that triggers shard balancing.

• Return values

  After you trigger a shard rebalancing operation, one of the following results is returned:

  • true: The rebalancing is successfully triggered, and the system starts the rebalancing operation.

  • false: Rebalancing is not required.

  • Error: The rebalancing failed to be triggered. For example, if a pod is faulty, an error is reported when you trigger a rebalancing operation.

  Note

  • During rebalancing, the system calculates whether rebalancing is needed to achieve a balanced state—a state where the difference in the number of shards loaded by each worker node is less than or equal to 1. For example:

    • If there are 2 workers and 2 shards, each worker is assigned 1 shard.

    • If there are 2 workers and 3 shards, one worker is assigned 1 shard and the other is assigned 2 shards.

  • A rebalancing operation typically takes 2 to 3 minutes. The duration depends on the number of table groups in the virtual warehouse—the more table groups, the longer the rebalancing takes. During rebalancing, write operations are interrupted for approximately 15 seconds.

  • Because rebalancing is performed asynchronously, you can run the following SQL statement to monitor execution progress:

    SELECT hg_get_rebalance_warehouse_status('<warehouse_name>');

    A return value of DOING indicates that rebalancing is in progress. A return value of DONE indicates that rebalancing is complete.

Set the timeout duration for the virtual warehouse

You can set the statement_timeout parameter to configure a timeout period at the session level or database level. For more information, see GUC parameters.

For a virtual warehouse, you can run the following commands to set a timeout period. This configuration has lower priority than session-level and database-level configurations. Therefore, you must first cancel the database-level configuration.

-- Cancel the database-level setting.
ALTER DATABASE <db_name> RESET statement_timeout;

-- Set the timeout period for the virtual warehouse to 600s.
CALL hg_alter_warehouse ('<warehouse_name>', <cu>, '{"configs":{"gucs":{"statement_timeout":"600000"}}}');