Hologres:Manage virtual warehouses

Usage notes

• Only Hologres V2.0.4 and later support the virtual warehouse feature. Before you use the virtual warehouse feature, join a Hologres DingTalk group to apply for background configurations. 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.

• 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.
  cu	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

-- 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

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

-- 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"}}}');