Grant data access to virtual warehouses

Usage notes

• When you create a Table Group by using virtual warehouse A, it becomes the default leader virtual warehouse for the Table Group.

• Memory state is synchronized across virtual warehouses with millisecond-level latency. When you write data to a leader virtual warehouse, the system synchronizes the in-memory data to follower virtual warehouses. This process consumes a small amount of CPU and memory resources on the follower virtual warehouses. We recommend that you keep the specification difference between virtual warehouses within a 4x ratio.

• Only the default virtual warehouse of an instance can write to foreign tables.

• In Hologres V2.0, virtual warehouses support read and write tasks as follows:

  • A follower virtual warehouse—a warehouse that has loaded a Table Group—can only perform read tasks on tables within that Table Group. Write tasks are not supported.

  • A leader virtual warehouse—a warehouse designated as the leader for a Table Group—can perform both read and write tasks on tables within that Table Group.

  • A virtual warehouse that has not loaded a Table Group cannot read from or write to it.

• Hologres V4.0 upgrades the instance architecture for virtual warehouses. Support for read and write tasks is as follows:

  • You must enable the hg_warehouse_enable_use_local_resource GUC parameter. For more information, see GUC parameters.

  • For read and write tasks not optimized by Fixed Plan, you can execute them without loading a Table Group into a virtual warehouse.

  • To learn which read and write tasks can be optimized by Fixed Plan, see Accelerate SQL execution with Fixed Plan.

  • All write tasks, including real-time writes and batch writes, can still be routed to the leader virtual warehouse for execution by using the DML auto-routing feature.

View Table Group access permissions

• Syntax

  To view Table Group permission information for all virtual warehouses in the current instance, run the following SQL statement.

  SELECT * FROM hologres.hg_warehouse_table_groups;

• Parameters

  The following table describes the columns in the hg_warehouse_table_groups table.

  Parameter	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	Indicates whether the virtual warehouse is the leader virtual warehouse for the Table Group.	t
  replica_count	INTEGER	The replica count.	1

Load a table group into a virtual warehouse

Loading a Table Group into a virtual warehouse makes it a follower virtual warehouse for that Table Group.

• After a virtual warehouse loads a Table Group, you can use the virtual warehouse to operate on tables in the Table Group.

• Loading a Table Group into a virtual warehouse requires Superuser permissions for the instance.

• Syntax:

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

• Parameters:

  Parameter	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 load the Table Group into.
  replica_count	INTEGER	The replica count. This parameter is optional. Default value: 1.

• Examples:

  -- Load table_group_1 in the db1 database into the warehouse_1 virtual warehouse with one replica.
  CALL hg_table_group_load_to_warehouse ('db1.table_group_1', 'warehouse_1');
  -- Load table_group_1 in the db1 database into the warehouse_1 virtual warehouse with two replicas.
  CALL hg_table_group_load_to_warehouse ('db1.table_group_1', 'warehouse_1',2);

Set the leader virtual warehouse

• Usage notes

  • Only the leader virtual warehouse can perform DML operations, such as writing data, on tables in a Table Group.

  • A Table Group can have only one leader virtual warehouse. Changing the leader causes it to restart, so plan for the business impact.

  • Setting the leader virtual warehouse for a Table Group requires Superuser permissions for the instance.

• Syntax

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

• Parameters

  Parameter	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 set as the leader.

Unload a table group

• Usage notes

  • Unloading a Table Group from a virtual warehouse requires Superuser permissions for the instance.

  • You cannot unload a Table Group from its leader virtual warehouse. You must first change the leader.

• Syntax

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

• Parameters

  Parameter	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 the Table Group is unloaded.

Change the replica count

• Note: Changing the replica count for a Table Group loaded in a virtual warehouse requires Superuser permissions for the instance.

• Syntax

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

• Parameters

  Parameter	Type	Description
  database_name	TEXT	The name of the database.
  table_group_name	TEXT	The name of the Table Group.
  replica_count	INTEGER	The replica count.
  warehouse_name	TEXT	The name of the virtual warehouse for which you want to change the replica count.

DML auto-routing for virtual warehouses (Beta)

• Usage notes

  A Table Group has only one leader virtual warehouse, which is the only warehouse that can perform DML operations on it. Starting with Hologres V2.2, you can automatically route DML operations to a Table Group's leader virtual warehouse. After you enable this feature, write tasks automatically use the resources of the leader virtual warehouse for execution, but write task metrics, such as QPS, are still recorded on the follower virtual warehouse.

  Note

  After you enable the multi-DML mixed transaction feature, you cannot use the DML auto-routing feature for virtual warehouses. For more information about the multi-DML mixed transaction feature, see SQL transaction capabilities.

• Enable or disable DML auto-routing

  You can use the following GUC parameter to enable or disable DML auto-routing for virtual warehouses at the session or database level.

  Note

  The GUC parameter for DML auto-routing for virtual warehouses, hg_experimental_enable_warehouse_dml_auto_routing, is enabled by default.

  • Session level

    -- Enable DML auto-routing for virtual warehouses.
    SET hg_experimental_enable_warehouse_dml_auto_routing = ON;
    -- Disable DML auto-routing for virtual warehouses.
    SET hg_experimental_enable_warehouse_dml_auto_routing = OFF;

  • Database level

    -- Enable DML auto-routing for virtual warehouses.
    ALTER DATABASE <database_name> SET hg_experimental_enable_warehouse_dml_auto_routing = ON;
    -- Disable DML auto-routing for virtual warehouses.
    ALTER DATABASE <database_name> SET hg_experimental_enable_warehouse_dml_auto_routing = OFF;

    Parameters

    Parameter	Type	Description
    database_name	TEXT	The name of the database.

• Example

  1. Go to the HoloWeb development page. For more information, see Connect to HoloWeb and run queries.

  2. In the top menu bar of the HoloWeb page, click Security Center.

  3. In the left-side navigation pane of the Security Center page, click Virtual Warehouse Management.

  4. On the Virtual Warehouse Resource Management tab, click Create Virtual Warehouse to create a virtual warehouse named read_wh1.

     Note

     An instance can have a maximum of 10 virtual warehouses. The resources for each virtual warehouse must be a minimum of 32 CU and a maximum of 512 CU. If you have less than 32 CU of unallocated computing resources, you cannot create a new virtual warehouse. To scale up, see Scale up a virtual warehouse.

     After the virtual warehouse is created, the Virtual Warehouse Resource Management tab shows the read_wh1 virtual warehouse with a status of Running. You can perform operations such as Scale, Restart, and Stop.

  5. On the Management on Permissions of Virtual Warehouses on Table Groups tab, click Grant Permissions to Virtual Warehouse to set the read_wh1 virtual warehouse as a follower virtual warehouse for the target Table Group.

     Note

     To create a Table Group, see Manage Table Groups.

     After the permissions are granted, the list shows init_warehouse as the leader virtual warehouse. In the Actions column for read_wh1, Set as Leader and Revoke Permissions are displayed.

  6. In the SQL Editor, set the current virtual warehouse to read_wh1 and run DML statements when DML auto-routing for virtual warehouses is enabled and disabled.

     • When DML auto-routing for virtual warehouses is enabled, DML statements are automatically routed to the leader virtual warehouse of the Table Group, which is init_warehouse, for execution.

       Switch the current virtual warehouse to read_wh1, run SET hg_experimental_enable_warehouse_dml_auto_routing = ON; to enable DML auto-routing, and then run an INSERT statement to write data. The statement runs successfully (99 rows, about 53 ms), confirming that the DML operations were automatically routed to the leader virtual warehouse for execution.

     • With DML auto-routing disabled, DML statements are not routed to the leader, init_warehouse. Instead, the current virtual warehouse, read_wh1, attempts to execute them. This fails with an error because only the leader virtual warehouse can perform DML operations on the Table Group.

       The command to disable DML auto-routing is SET hg_experimental_enable_warehouse_dml_auto_routing = OFF;. The following error message is returned: ERROR: Dispatch query failed: internal error: Failed to get available shards for query. The HINT message suggests checking whether the Table Group (for example, demo.demo_tg_default) is the leader in the current virtual warehouse.