Statements that are used to query, create, delete, scale in, or scale out virtual warehouses - Hologres

This topic describes the statements that are used to manage virtual warehouses, such as the statements that are used to query, create, delete, scale in, or scale out virtual warehouses.

Usage notes

Only Hologres V2.0.4 and later support the virtual warehouse feature. Before you can use the virtual warehouse feature, you must join the Hologres DingTalk group to apply for background configurations for this feature. For more information, see Obtain online support for Hologres.
When you purchase an instance with the virtual warehouse feature enabled, the system creates a default virtual warehouse named init_warehouse. Only one default virtual warehouse can be configured for an instance. You can modify but cannot delete the default virtual warehouse.
If you create a table group by using Virtual Warehouse A, Virtual Warehouse A is the leader virtual warehouse of the table group by default.
You can create up to 10 virtual warehouses in an instance. Each virtual warehouse can have a minimum of 32 compute units (CUs) and a maximum of 512 CUs. In Hologres V3.0.10 and later, the maximum specifications of a virtual warehouse are adjusted to 1,024 CUs.
You cannot suspend or resume the default virtual warehouse of an instance by executing SQL statements.
By default, all users of an instance have the permissions to use the default virtual warehouse.
All virtual warehouses support Data definition language (DDL) statements.
The resource group feature is in the beta phase and is not supported by virtual warehouse instances. For more information about the resource group feature, see Manage resource groups (beta). We recommend that you configure multiple virtual warehouses for resource isolation.
For more information about the billing of virtual warehouse instances, see Billing overview.

Query information about virtual warehouses

Syntax
- The system table named hologres.hg_warehouses stores the information about all virtual warehouses in the connected instance. You can execute the following SQL statement to query the information about all virtual warehouses in the connected instance.
```
SELECT * FROM hologres.hg_warehouses;
```
- You can execute the following SQL statement to query information about the virtual warehouse in use. After the SQL statement is executed, the name of the virtual warehouse in use is returned.
```
SELECT current_warehouse();
```

Fields

The following table describes the fields in the hologres.hg_warehouses system table.

Field	Data 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 number of CPU cores that are allocated to the virtual warehouse.	32
mem	INTEGER	The memory size of the virtual warehouse. Unit: GB.	128
cluster_min_count	INTEGER	The minimum number of shards that are processed by using the virtual warehouse.	1
cluster_max_count	INTEGER	The maximum number of shards that are processed by using the virtual warehouse.	1
target_status	INTEGER	The expected status of the virtual warehouse. Operations on a virtual warehouse are performed asynchronously. For example, when you execute an SQL statement to create a virtual warehouse, the system immediately returns a result. However, the creation process is not complete. It takes time for the system to create a virtual warehouse in the background. Valid values: 1: The virtual warehouse is running. 2: The virtual warehouse is manually suspended.	1
status	INTEGER	The current status of the virtual warehouse. Valid values: 0: The virtual warehouse is being initialized. 1: The virtual warehouse is running. 2: The virtual warehouse is manually suspended. 3: The virtual warehouse fails to be processed. The failure cause is displayed in the status_detail field. 4: The virtual warehouse is being processed.	1
status_detail	TEXT	The details of the virtual warehouse status.	None
is_default	BOOLEAN	Specifies whether the virtual warehouse is the default virtual warehouse. Valid values: t: The virtual warehouse is the default virtual warehouse. f: The virtual warehouse is not the default virtual warehouse.	t
config	TEXT	The configuration of the virtual warehouse.	None
comment	TEXT	The description of the virtual warehouse.	None

Create a virtual warehouse

Usage notes
- You can create a virtual warehouse only by using an Alibaba Cloud account or a RAM user.
- You must be granted superuser permissions of the instance before you can create a virtual warehouse for the instance.
- The process of creating a virtual warehouse is asynchronous. It takes time for the system to create a virtual warehouse after the related SQL statement is executed. You can use the hg_get_warehouse_status function to query the status of the virtual warehouse.

Syntax

CALL hg_create_warehouse ('<warehouse_name>', <cu>);

Parameters

Parameter

Data type

Description

warehouse_name

TEXT

The name of the virtual warehouse that you want to create. The name must meet the following requirements:

The name can contain only digits, letters, and underscores (_).
The name can be up to 64 characters in length.

INTEGER

The number of CUs that you want to allocate to the virtual warehouse.

Maximum value: 512
Minimum value: 32
Step: 16

Suspend a virtual warehouse

Usage notes
- You must be granted superuser permissions of the instance before you can suspend a virtual warehouse.
- You cannot suspend or resume the default virtual warehouse of an instance by executing SQL statements. If you want to suspend or resume a default virtual warehouse, you must suspend or resume the instance to which the default virtual warehouse belongs. For more information, see Instances.
- If a table group that is loaded to a leader virtual warehouse is also loaded to a follower virtual warehouse, you cannot suspend the leader virtual warehouse. For example, Virtual Warehouse A is the leader virtual warehouse of Table Group A and is used to write data to shards in Table Group A. Virtual Warehouse B is the follower virtual warehouse of Table Group A. In this case, you cannot suspend Virtual Warehouse A.
- The process of suspending a virtual warehouse is asynchronous. It takes time for a virtual warehouse to suspend after the related SQL statement is executed. You can use the hg_get_warehouse_status function to query the status of the virtual warehouse.

Syntax

CALL hg_suspend_warehouse ('<warehouse_name>');

Parameters
Parameter
Data type
Description
warehouse_name
TEXT
The name of the virtual warehouse that you want to suspend.

Resume a virtual warehouse

Usage notes
- You must be granted superuser permissions of the instance before you can resume a virtual warehouse.
- You cannot suspend or resume the default virtual warehouse of an instance by executing SQL statements. If you want to suspend or resume a default virtual warehouse, you must suspend or resume the instance to which the default virtual warehouse belongs. For more information, see Instances.
- The process of resuming a virtual warehouse is asynchronous. It takes time for a virtual warehouse to resume after the related SQL statement is executed. You can use the hg_get_warehouse_status function to query the status of the virtual warehouse.

Syntax

CALL hg_resume_warehouse ('<warehouse_name>');

Parameters
Parameter
Data type
Description
warehouse_name
TEXT
The name of the virtual warehouse that you want to resume.

Drop a virtual warehouse

Usage notes
- You can drop a virtual warehouse only by using an Alibaba Cloud account or a RAM user.
- You must be granted superuser permissions of the instance before you can drop a virtual warehouse.
- You cannot drop a default virtual warehouse of an instance by executing SQL statements.
- You cannot drop a leader virtual warehouse of a table group by executing SQL statements.
- The process of dropping a virtual warehouse is asynchronous. It takes time for a virtual warehouse to be dropped after the related SQL statement is executed. You can use the hg_get_warehouse_status function to query the status of the virtual warehouse.

Syntax

CALL hg_drop_warehouse ('<warehouse_name>');

Parameters
Parameter
Data type
Description
warehouse_name
TEXT
The name of the virtual warehouse that you want to drop.

Scale out a virtual warehouse

Permissions

You can scale out a virtual warehouse only by using an Alibaba Cloud account or a RAM user instead of a Security Token Service (STS) account.
If you want to scale out a virtual warehouse, you must have the following permissions:
- Permissions of a RAM role: AliyunHologresWarehouseFullAccess or AliyunHologresFullAccess
- Superuser permissions on a Hologres instance

Usage notes

Before you scale out a virtual warehouse, you must scale out the virtual warehouse instance in the Hologres console to ensure that the virtual warehouse instance has sufficient unallocated resources. After the virtual warehouse instance is scaled out, the system creates a pod to which no shard metadata is loaded. In this case, the created pod is not used for computing. The instance scale-out operation does not affect data reads and writes on virtual warehouses.
After the virtual warehouse instance is scaled out to provide sufficient unallocated resources, you can use the resources to scale out virtual warehouses. If the unallocated resources are insufficient, virtual warehouse scale-out fails.
- In Hologres V2.2 and later, if a pod to which no shard metadata is loaded is available in a virtual warehouse, the system automatically rebalances shards to load shard metadata to the pod. After the rebalancing is complete, you can use the new pod resources. During the rebalancing process, data queries and writes are interrupted for 15 seconds. If the version of your Hologres instance is V2.2 or later, you do not need to manually rebalance shards after the scale-out. If the version of your Hologres instance is earlier than V2.2, you must manually rebalance shards after the scale-out.
- In Hologres V3.0 and later, data queries and writes on a virtual warehouse are not interrupted when the virtual warehouse is scaled out.

Procedure

Scale out a virtual warehouse. Syntax:

CALL hg_alter_warehouse ('<warehouse_name>',<cu>);

The following table describes parameters in the preceding syntax.

Parameter

Data type

Description

warehouse_name

TEXT

The name of the virtual warehouse for which you want to adjust resources.

INTEGER

The number of CUs that you want to allocate to the virtual warehouse.

Maximum value: 512
Minimum value: 32
Step: 16

Query the status of the virtual warehouse.
The process of adjusting the resources of a virtual warehouse is asynchronous. It takes time for the adjustment to finish after the related SQL statement is executed. You can use the hg_get_warehouse_status function to query the status of the virtual warehouse.
```
select hg_get_warehouse_status('<warehouse_name>');
```
If Running is returned, proceed with the following steps.
Rebalance shards.
Execute the following statement to redistribute shards to the virtual warehouse that you scale out:
```
SELECT hg_rebalance_warehouse ('<warehouse_name>');
```
Query the status of the virtual warehouse.
The rebalancing process is asynchronous. It takes time for the rebalancing process to finish after the related SQL statement is executed. You can use one of the following methods to query the status of the virtual warehouse.
- Use the hg_get_rebalance_warehouse_status function to return the status of the current virtual warehouse.
```
SELECT hg_get_rebalance_warehouse_status ('<warehouse_name>');
```
  If t is returned, the rebalancing process succeeds.
- Use the hg_wait_until_rebalance_warehouse_done function to return a result only after the virtual warehouse enters the Running state.
```
select hg_wait_until_rebalance_warehouse_done('<warehouse_name>',<timeout_seconds>);
```
  In the preceding statement, the timeout_seconds parameter is optional. This parameter specifies the response timeout period. Unit: seconds. The value of this parameter is of the INTEGER type.
  If DONE is returned, the rebalancing process succeeds.

Examples

Scale out the virtual warehouse named read_warehouse_1 to 64 CUs.

-- Increase the number of CUs that are allocated to the virtual warehouse.
CALL hg_alter_warehouse ('read_warehouse_1',64);

-- Check the scale-out result.
SELECT wait_until_warehouse_running ('read_warehouse_1');

-- rebalance
SELECT hg_rebalance_warehouse ('read_warehouse_1');

-- Check the rebalancing result.
SELECT hg_wait_until_rebalance_warehouse_done ('read_warehouse_1');

Scale in a virtual warehouse

Permissions

You can scale in a virtual warehouse only by using an Alibaba Cloud account or a RAM user instead of an STS account.
If you want to scale in a virtual warehouse, you must have the following permissions:
- Permissions of a RAM role: AliyunHologresWarehouseFullAccess or AliyunHologresFullAccess
- Superuser permissions on a Hologres instance

Usage notes

During the scale-in process, the system performs shard rebalancing by migrating the shards of the pods that you want to remove to other pods. During the rebalancing process, data reads and writes are interrupted for 15 seconds. After the rebalancing process is complete, the redundant pods are directly removed.
After the scale-in, the redundant computing resources become available for the instance and can be used for creating virtual warehouses or scaling out existing virtual warehouses. You can also scale in the virtual warehouse instance to release the resources.

Procedure

Scale in a virtual warehouse. Syntax:

CALL hg_alter_warehouse ('<warehouse_name>',<cu>);

The following table describes parameters in the preceding syntax.

Parameter

Data type

Description

warehouse_name

TEXT

The name of the virtual warehouse for which you want to adjust resources.

INTEGER

The number of CUs that you want to allocate to the virtual warehouse.

Maximum value: 512
Minimum value: 32
Step: 16

Query the status of the virtual warehouse.
The process of adjusting the resources of a virtual warehouse is asynchronous. It takes time for the adjustment to finish after the related SQL statement is executed. You can use the hg_get_warehouse_status function to query the status of the virtual warehouse.
```
select hg_get_warehouse_status('<warehouse_name>');
```
In this example, Running is returned.

Query the status of a virtual warehouse

Syntax
- Execute the following SQL statement to query the status of a virtual warehouse:
```
select hg_get_warehouse_status('<warehouse_name>');
```
- The process of creating, scaling out, or scaling in a virtual warehouse is asynchronous. Some operations need to be performed after the virtual warehouse status becomes Running. You can use the wait_until_warehouse_running function to specify that a result is returned only after the virtual warehouse status becomes Running. Sample statement:
```
select wait_until_warehouse_running('<warehouse_name>',<timeout_seconds>);
```

Parameters

Parameter	Data type	Description
warehouse_name	TEXT	The name of the virtual warehouse whose status you want to query.
timeout_seconds	INTEGER	The timeout period of a response. Unit: seconds. This parameter is optional.

Examples

Perform the following operations in sequence each time you import data from the table org_data to the table res_data: virtual warehouse scale-out, data import, virtual warehouse scale-in.

-- Scale out the virtual warehouse named warehouse_1 to 64 CUs.
CALL hg_alter_warehouse ('warehouse_1',64);

-- Wait until the scale-out of the virtual warehouse named warehouse_1 is complete.
SELECT wait_until_warehouse_running('warehouse_1');

-- Insert data into the table named res_data.
INSERT INTO res_data SELECT * FROM org_data;

-- Scale in the virtual warehouse named warehouse_1 to 32 CUs.
CALL hg_alter_warehouse ('warehouse_1',32);

Change the name of a virtual warehouse

Permissions
You must be granted superuser permissions of the instance before you can change the name of a virtual warehouse.

Syntax

CALL hg_rename_warehouse('<org_warehouse_name>','<new_warehouse_name>');

Parameters
Parameter
Data type
Description
org_warehouse_name
TEXT
The virtual warehouse name that you want to change.
new_warehouse_name
TEXT
The new name of the virtual warehouse.

Restart a virtual warehouse

Permissions
You must be granted superuser permissions of the instance before you can restart a virtual warehouse.
Usage notes
The process of restarting a virtual warehouse is asynchronous. It takes time for a virtual warehouse to restart after the related SQL statement is executed. You can use the hg_get_warehouse_status function to query the status of the virtual warehouse.

Syntax

CALL hg_restart_warehouse('<warehouse_name>');

Parameters
Parameter
Data type
Description
warehouse_name
TEXT
The name of the virtual warehouse that you want to restart.

Advanced operations

Rebalance shards for a virtual warehouse

During the Hologres instance runtime, shard metadata is evenly loaded to the worker nodes of each virtual warehouse. However, in some scenarios such as quick recovery or scale-out, shards are not evenly loaded to the worker nodes in a virtual warehouse. In this case, shard rebalancing is required to enable the system to reallocate shards. This way, shard metadata can be evenly loaded to the worker nodes in the virtual warehouse.

Permissions
You must be granted superuser permissions of the instance before you can trigger shard rebalancing for a virtual warehouse.
Usage notes
- In Hologres V2.1 and later, shard rebalancing is automatically triggered if no shard metadata is loaded to a worker node in a virtual warehouse.
- In Hologres V2.2 and later, the system automatically re-executes SELECT statements that fail during shard rebalancing of virtual warehouses. This way, SELECT statements can be successfully executed, and the error message ERROR: fail to execute query internal error: Get rundown is not allowed in recovering state. is not reported.

Syntax

SELECT hg_rebalance_warehouse ('<warehouse_name>');

Parameters
Parameter
Data type
Description
warehouse_name
TEXT
The name of the virtual warehouse for which you want to trigger shard rebalancing.
Returned results
After shard rebalancing is triggered, one of the following result is returned:
- true: Shard rebalancing is triggered and the system starts to rebalance shards.
- false: Shard rebalancing is not required.
- Error: Shard rebalancing failed. For example, if a pod is faulty, an error is returned when you try to trigger shard rebalancing.
Note
- The system stops shard rebalancing until the number of shards that are loaded to each worker node is basically the same. For example, the difference of the numbers of shards between worker nodes is less than or equal to 1. Examples:
  If you configure two worker nodes and two shards, one shard is loaded to each worker node.
  If you configure two worker nodes and three shards, one shard is loaded to one worker node, and two shards are loaded to the other worker node.
- Shard rebalancing may take 2 to 3 minutes. The duration varies based on the number of table groups in the virtual warehouse. A large number of table groups indicates a long duration. During shard rebalancing, data writes may be interrupted for about 15s.
- The shard rebalancing process is asynchronous. You can execute the following SQL statement to query the shard rebalancing progress:
  SELECT hg_get_rebalance_warehouse_status('<warehouse_name>');
  If DOING is returned, shard rebalancing is being performed. If DONE is returned, shard rebalancing is complete.

Configure the timeout period for a virtual warehouse

You can configure the timeout period at the session level or database level by using the statement_timeout parameter. For more information about the parameter, see GUC parameters.

You can also configure the timeout period at the virtual warehouse level. The virtual warehouse-level configuration has a lower priority than the session-level and database-level configurations. Therefore, if you want to configure the timeout period for a virtual warehouse, you must remove the session-level and database-level configurations first.

-- Remove the database-level configuration.
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"}}}');