Manage permissions on table groups that are loaded to virtual warehouses - Hologres

This topic describes the statements related to table group authorization based on virtual warehouses, such as the statements that are used to view permissions on table groups that are loaded to virtual warehouses, load a table group to a virtual warehouse, and configure a leader virtual warehouse for a table group.

Usage notes

You need to apply for background configurations before you use the virtual warehouse feature. To apply for background configurations, manually upgrade your Hologres instance in the Hologres console or join the Hologres DingTalk group for technical support. For more information about how to manually upgrade your Hologres instance in the Hologres console, see Upgrade instances. For more information about how to join the Hologres DingTalk group, see Obtain online support for Hologres.
After you configure a leader virtual warehouse for a table group, the leader virtual warehouse restarts. We recommend that you load primary table groups to default virtual warehouses. This ensures read/write splitting.
If you use Virtual Warehouse A to create a table group, Virtual Warehouse A is used as the leader virtual warehouse of the table group by default.
Memory status is automatically synchronized across virtual warehouses in milliseconds in real time. When you use a leader virtual warehouse to write data, the system automatically synchronizes data in the memory of the leader virtual warehouse to the follower virtual warehouses. A small amount of CPU and memory overheads are generated for follower virtual warehouses. We recommend that specification differences among virtual warehouses be no more than four times.
If you want to write data to foreign tables, you can only use the default virtual warehouse of a virtual warehouse instance.

View permissions on table groups that are loaded to virtual warehouses

Syntax
You can execute the following SQL statement to view permissions on table groups that are loaded to all virtual warehouses in the connected instance:
```
SELECT * FROM hologres.hg_warehouse_table_groups;
```

Fields

The following table describes fields in the hg_warehouse_table_groups table.

Field	Data type	Description	Example
warehouse_name	TEXT	The name of the virtual warehouse.	init_warehouse
warehouse_id	INTEGER	The unique ID of the virtual warehouse.	1
database_name	TEXT	The name of the database.	wh_demo
tablegroup_name	TEXT	The name of the table group.	wh_demo_tg_default
leader	BOOLEAN	Specifies whether the virtual warehouse is the leader virtual warehouse of the table group.	t
replica_count	INTEGER	The number of replicas.	1

Load a table group to a virtual warehouse

Usage notes
- You can perform operations on shards in a table group by using a virtual warehouse only after the table group is loaded to the virtual warehouse.
- Only users with the superuser role have the permission to load a table group to a virtual warehouse.

Syntax

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

Parameters

Parameter	Data type	Description
database_name	TEXT	The name of the database.
table_group_name	TEXT	The name of the table group.
warehouse_name	TEXT	The name of the virtual warehouse to which you want to load a table group.
replica_count	INTEGER	The number of replicas. Default value: 1. This parameter is optional.

Examples

-- Load the table group table_group_1 in the database db1 to the virtual warehouse warehouse_1 and set the number of replicas to 1.
CALL hg_table_group_load_to_warehouse ('db1.table_group_1', 'warehouse_1');
-- Load the table group table_group_1 in the database db1 to the virtual warehouse warehouse_1, and set the number of replicas to 2.
CALL hg_table_group_load_to_warehouse ('db1.table_group_1', 'warehouse_1',2);

Configure a leader virtual warehouse for a table group

Usage notes
- DML operations, such as data writes, on shards in a table group can be performed only by using the leader virtual warehouse of the table group.
- Only one leader virtual warehouse can be configured for a table group.
- Only users with the superuser role can configure a leader virtual warehouse for a table group.

Syntax

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

Parameters

Parameter	Data type	Description
database_name	TEXT	The name of the database.
table_group_name	TEXT	The name of the table group.
warehouse_name	TEXT	The name of the virtual warehouse that you want to configure as the leader virtual warehouse.

Uninstall a table group from a virtual warehouse

Usage notes
- Only users with the superuser role can uninstall a table group from a virtual warehouse.
- You cannot uninstall a table group from its leader virtual warehouse. If you want to uninstall it, you need to change the leader virtual warehouse.

Syntax

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

Parameters

Parameter	Data type	Description
database_name	TEXT	The name of the database.
table_group_name	TEXT	The name of the table group.
warehouse_name	TEXT	The name of the virtual warehouse from which you want to uninstall a table group.

Modify the number of replicas for a table group that is loaded to a virtual warehouse

Usage notes
Only users with the superuser role have the permission to modify the number of replicas for a table group that is loaded to a virtual warehouse.

Syntax

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

Parameters

Parameter	Data type	Description
database_name	TEXT	The name of the database.
table_group_name	TEXT	The name of the table group.
replica_count	INTEGER	The number of replicas.
warehouse_name	TEXT	The name of the virtual warehouse to which the table group is loaded. You want to change the number of replicas for the table group.

Manage automatic routing of DML statements to the leader virtual warehouse (beta)

Usage notes
Only one leader virtual warehouse can be configured for a table group. DML operations on a table group can be performed only by using the leader virtual warehouse of the table group. In Hologres V2.2 and later, DML statements are automatically routed to the leader virtual warehouse of the table group. After you enable this feature, the leader virtual warehouse is automatically used for data writes. However, metrics related to data writes, such as the queries per second (QPS), are calculated for follower virtual warehouses.

Enable or disable automatic routing of DML statements to the leader virtual warehouse

You can use the GUC parameter at the session or database level to specify whether to enable automatic routing of DML statements to the leader virtual warehouse.

Note

By default, the GUC parameter hg_experimental_enable_warehouse_dml_auto_routing is set to ON.

Session level

-- Enable automatic routing of DML statements to the leader virtual warehouse.
SET hg_experimental_enable_warehouse_dml_auto_routing = ON;

-- Disable automatic routing of DML statements to the leader virtual warehouse.
SET hg_experimental_enable_warehouse_dml_auto_routing = OFF;

Database level

-- Enable automatic routing of DML statements to the leader virtual warehouse.
ALTER DATABASE <database_name> SET hg_experimental_enable_warehouse_dml_auto_routing = ON;

-- Disable automatic routing of DML statements to the leader virtual warehouse.
ALTER DATABASE <database_name> SET hg_experimental_enable_warehouse_dml_auto_routing = OFF;

Parameters

Parameter	Data type	Description
database_name	TEXT	The name of the metadatabase.

Example
1. Log on to the HoloWeb console. For more information, see Connect to HoloWeb and perform queries.
2. In the top navigation bar, click Security Center.
3. On the Security Center tab, click Compute Group Management in the left-side navigation pane.
4. On the Management on Resources in Virtual Warehouses tab, click Create Compute Group to create a virtual warehouse named read_wh1.
  Note
  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. If the amount of remaining computing resources is less than 32 CUs, you cannot create a virtual warehouse. For more information, see Scale out a virtual warehouse in the "Manage virtual warehouses" topic.
5. On the Management on Permissions of Virtual Warehouses on Table Groups tab, click Grant Permissions to Virtual Warehouse and configure the virtual warehouse read_wh1 as the follower virtual warehouse of the table group.
  Note
  For more information about how to create a table group, see Manage table groups.
6. In the top navigation bar, click SQL Editor. Select read_wh1 from the Current Compute Group drop-down list. Then, execute DML statements when automatic routing of DML statements to the leader virtual warehouse is enabled or disabled.
  - When automatic routing of DML statements to the leader virtual warehouse is enabled, DML statements are automatically executed on the leader virtual warehouse init_warehouse of the table group.
  - When automatic routing of DML statements to the leader virtual warehouse is disabled, DML statements are executed on the current virtual warehouse read_wh1 of the table group instead of the leader virtual warehouse init_warehouse. However, only the leader virtual warehouse supports DML statements on shards in the table group. As a result, an error message is reported.