Hologres:Manage virtual warehouses

Notes

• Only Alibaba Cloud accounts or RAM users with Superuser permission for the instance can create virtual warehouses.

• Creating a virtual warehouse is an asynchronous process that runs in the background. To confirm that the virtual warehouse is ready, use the hg_get_warehouse_status function to query its status.

Procedure

Notes

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

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

• You cannot use SQL commands to delete the leader virtual warehouse of a Table Group.

• Deleting a virtual warehouse is an asynchronous process that runs in the background. To confirm that it has been deleted, use the hg_get_warehouse_status function to query the status of the virtual warehouse.

Procedure

Permissions

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

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

  • RAM role permissions: AliyunHologresWarehouseFullAccess or AliyunHologresFullAccess.

  • Superuser permission within the instance.

For information about elastic scaling for virtual warehouses, see Overview of resource scalability.

Scale up a virtual warehouse

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

-- Wait for the virtual warehouse to finish scaling up
SELECT wait_until_warehouse_running ('read_warehouse_1');

-- Rebalance the shards
SELECT hg_rebalance_warehouse ('read_warehouse_1');

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

Scale down a virtual warehouse

Scale out a virtual warehouse (Add clusters)

Scale in a virtual warehouse (Reduce clusters)

View virtual warehouses

• Commands

  • The hologres.hg_warehouses system view stores information about all virtual warehouses in the current Instance.

    SELECT * FROM hologres.hg_warehouses;

  • View the current virtual warehouse:

    SELECT current_warehouse();

• Parameters

  The hologres.hg_warehouses system view includes the following fields:

  Field	Type	Description	Example
  warehouse_id	INTEGER	The unique ID of the virtual warehouse.	1
  warehouse_name	TEXT	The name of the virtual warehouse.	init_warehouse
  cpu	INTEGER	The vCPUs of the virtual warehouse.	32
  mem	INTEGER	The memory of the virtual warehouse in GB.	128
  cluster_min_count	INTEGER	The minimum number of clusters for the virtual warehouse.	1
  cluster_max_count	INTEGER	The maximum number of clusters for the virtual warehouse.	1
  target_status	INTEGER	The final status of the virtual warehouse. Due to asynchronous status updates, this field reflects the target status. Possible values: 1: Running 2: Manually stopped	1
  status	INTEGER	The current status of the virtual warehouse. States are: 0: Initializing 1: Running 2: Manually stopped 3: Processing failed. Check the status_detail field for the cause of failure. 4: Processing	1
  status_detail	TEXT	Details about the virtual warehouse's status.
  is_default	BOOLEAN	Indicates whether this is the default virtual warehouse. t: True f: False	t
  config	TEXT	The configuration of the virtual warehouse.
  comment	TEXT	Comments for the virtual warehouse.

Stop a virtual warehouse

Resume a virtual warehouse

Restart a virtual warehouse

Get virtual warehouse status

• Syntax

  • Get the current status of a virtual warehouse:

    select hg_get_warehouse_status('<warehouse_name>');

  • Operations like creating or scaling a virtual warehouse are asynchronous. Use the wait_until_warehouse_running function to wait for the virtual warehouse to reach Running and return a result:

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

• Parameters

  Parameter	Type	Description
  warehouse_name	TEXT	The name of the virtual warehouse.
  timeout_seconds	INTEGER	Optional. The timeout period in seconds.

• Example

  The following example scales up a virtual warehouse before importing data and scales it down afterward.

  -- Scale up warehouse_1 to 64 CUs.
  CALL hg_alter_warehouse ('warehouse_1',64);
  
  -- Wait for warehouse_1 to finish scaling 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);

Rename a virtual warehouse

Procedure

Rebalance a virtual warehouse

During runtime, the Worker Nodes of each Hologres virtual warehouse evenly load the shard metadata. However, in some scenarios, such as after a fast recovery or a scale-up operation, shards may become unevenly distributed across the Worker Nodes. You can then trigger a rebalance to redistribute the data shards evenly across the Worker Nodes.

• Permissions

  You need Superuser permission for the instance to trigger a rebalance.

• Notes

  • V2.1 onwards: Hologres automatically initiates a rebalance for virtual warehouses that have no Shards loaded on their Worker Nodes.

  • V2.2 onwards: Hologres now supports automatic retries for SELECT queries during virtual warehouse rebalancing. This feature enables queries to complete with a minor increase in execution time, preventing failures 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 to rebalance.

• Return values

  After you trigger a rebalance, Hologres returns one of the following results:

  • true: The rebalance was triggered successfully.

  • false: Rebalancing is not needed.

  • Error: Failed to trigger rebalancing due to causes such as faulty pods.

  Note

  • During a rebalance, Hologres assesses the need for redistribution and aims for optimal shard distribution. Specifically, the number of shards on any two Worker Nodes will not differ by more than one. For instance:

    • With 2 Worker Nodes and 2 shards, each node receives 1 shard.

    • With 2 Worker Nodes and 3 shards, one node receives 1 shard, and the other receives 2.

  • Rebalancing typically takes 2 to 3 minutes, with duration increasing based on the number of loaded Table Groups. Writes are interrupted for about 15 seconds during this process.

  • Rebalancing is an asynchronous process. Check its progress using this SQL command:

    SELECT hg_get_rebalance_warehouse_status('<warehouse_name>');

    This command returns DOING to indicate rebalancing is in progress; DONE indicates rebalancing is complete.

Set the timeout duration for a virtual warehouse

Timeout durations can be configured at the session, database, or virtual warehouse level using the statement_timeout parameter. For details, see GUC parameters.

The virtual warehouse-level timeout setting has lower priority than session-level or database-level configs. To ensure it takes effect, first remove any existing database-level timeout setting.

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

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