Hologres:Manage data permissions

Syntax

Execute the following SQL statement to view permissions on all loaded table groups in the connected instance:

SELECT * FROM hologres.hg_warehouse_table_groups;

Fields

The following table describes fields in the hg_warehouse_table_groups table.

To set a secondary virtual warehouse for a table group, load the table group to the virtual warehouse.

• 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

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

Usage notes

• DML operations, such as data writes, on shards in a table group can be performed only by using the primary virtual warehouse of the table group.

• Only one primary virtual warehouse can be configured for a table group. When you modify the primary virtual warehouse of a table group, the primary virtual warehouse will be restarted. Take note of the impact on your business.

• Only users with the superuser role can configure a primary virtual warehouse for a table group.

Syntax

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

Parameters

Usage notes

• Only users with the superuser role can unload a table group from a virtual warehouse.

• You cannot unload a table group from its primar y virtual warehouse. If you want to unload it, change the primary virtual warehouse.

Syntax

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

Parameters

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

Usage notes

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

Enable or disable automatic routing of DML statements to the primary 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 primary virtual warehouse.

• Session level

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

• Database level

  -- Enable automatic routing of DML statements to the primary virtual warehouse.
  ALTER DATABASE <database_name> SET hg_experimental_enable_warehouse_dml_auto_routing = ON;
  
  -- Disable automatic routing of DML statements to the primary 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 up 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 secondary 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 primary virtual warehouse is enabled or disabled.

   • When automatic routing of DML statements to the primary virtual warehouse is enabled, DML statements are automatically executed on the primary virtual warehouse init_warehouse of the table group.

     

   • When automatic routing of DML statements to the primary virtual warehouse is disabled, DML statements are executed on the current virtual warehouse read_wh1 of the table group instead of the primary virtual warehouse init_warehouse. However, only the primary virtual warehouse supports DML statements on shards in the table group. As a result, an error message is reported.