How to use compute groups to share data and switch traffic - Hologres

Hologres V2.0 introduces a new, elastic, and high-availability instance type. This instance type divides compute resources into different compute groups (Virtual Warehouses) to better support high-availability deployments. This topic describes how to use compute groups.

Background information

Compute groups support multiple scenarios, such as read/write splitting, resource isolation, and business isolation. They provide core capabilities such as resource isolation and elasticity. For more information about the compute group architecture, see Compute group architecture.

Notes

Compute group instances are supported only in Hologres V2.0.4 and later. If your instance version is earlier than V2.0.4, see Common errors when preparing for an upgrade or join the Hologres DingTalk group to provide feedback. For more information, see How do I get more online support?.

Create a new compute group

This section describes how to create a new compute group named read_warehouse_1 to implement read/write splitting. You will use the init_warehouse compute group to write data and the read_warehouse_1 compute group for service queries.

image..png

Create a database

Log on to HoloWeb as a superuser and create a database named erp_database. For more information, see Create a database.

After you log on to the database, run the following SQL statements to import sample data.

DROP FOREIGN TABLE IF EXISTS odps_customer_10g;
DROP FOREIGN TABLE IF EXISTS odps_lineitem_10g;
DROP FOREIGN TABLE IF EXISTS odps_nation_10g;
DROP FOREIGN TABLE IF EXISTS odps_orders_10g;
DROP FOREIGN TABLE IF EXISTS odps_part_10g;
DROP FOREIGN TABLE IF EXISTS odps_partsupp_10g;
DROP FOREIGN TABLE IF EXISTS odps_region_10g;
DROP FOREIGN TABLE IF EXISTS odps_supplier_10g;


IMPORT FOREIGN SCHEMA "MAXCOMPUTE_PUBLIC_DATA#default" LIMIT to
(
    odps_customer_10g,
    odps_lineitem_10g,
    odps_nation_10g,
    odps_orders_10g,
    odps_part_10g,
    odps_partsupp_10g,
    odps_region_10g,
    odps_supplier_10g
) 
FROM SERVER odps_server INTO public OPTIONS(if_table_exist'error',if_unsupported_type'error');

DROP TABLE IF EXISTS LINEITEM;

BEGIN;
CREATE TABLE LINEITEM
(
    L_ORDERKEY      BIGINT      NOT NULL,
    L_PARTKEY       INT         NOT NULL,
    L_SUPPKEY       INT         NOT NULL,
    L_LINENUMBER    INT         NOT NULL,
    L_QUANTITY      DECIMAL(15,2) NOT NULL,
    L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
    L_DISCOUNT      DECIMAL(15,2) NOT NULL,
    L_TAX           DECIMAL(15,2) NOT NULL,
    L_RETURNFLAG    TEXT        NOT NULL,
    L_LINESTATUS    TEXT        NOT NULL,
    L_SHIPDATE      TIMESTAMPTZ NOT NULL,
    L_COMMITDATE    TIMESTAMPTZ NOT NULL,
    L_RECEIPTDATE   TIMESTAMPTZ NOT NULL,
    L_SHIPINSTRUCT  TEXT        NOT NULL,
    L_SHIPMODE      TEXT        NOT NULL,
    L_COMMENT       TEXT        NOT NULL,
    PRIMARY KEY (L_ORDERKEY,L_LINENUMBER)
);
CALL set_table_property('LINEITEM', 'clustering_key', 'L_SHIPDATE,L_ORDERKEY');
CALL set_table_property('LINEITEM', 'segment_key', 'L_SHIPDATE');
CALL set_table_property('LINEITEM', 'distribution_key', 'L_ORDERKEY');
CALL set_table_property('LINEITEM', 'bitmap_columns', 'L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_LINENUMBER,L_RETURNFLAG,L_LINESTATUS,L_SHIPINSTRUCT,L_SHIPMODE,L_COMMENT');
CALL set_table_property('LINEITEM', 'dictionary_encoding_columns', 'L_RETURNFLAG,L_LINESTATUS,L_SHIPINSTRUCT,L_SHIPMODE,L_COMMENT');
CALL set_table_property('LINEITEM', 'time_to_live_in_seconds', '31536000');
COMMIT;

DROP TABLE IF EXISTS ORDERS;

BEGIN;
CREATE TABLE ORDERS
(
    O_ORDERKEY      BIGINT      NOT NULL PRIMARY KEY,
    O_CUSTKEY       INT         NOT NULL,
    O_ORDERSTATUS   TEXT        NOT NULL,
    O_TOTALPRICE    DECIMAL(15,2) NOT NULL,
    O_ORDERDATE     timestamptz NOT NULL,
    O_ORDERPRIORITY TEXT        NOT NULL,
    O_CLERK         TEXT        NOT NULL,
    O_SHIPPRIORITY  INT         NOT NULL,
    O_COMMENT       TEXT        NOT NULL
);
CALL set_table_property('ORDERS', 'segment_key', 'O_ORDERDATE');
CALL set_table_property('ORDERS', 'distribution_key', 'O_ORDERKEY');
CALL set_table_property('ORDERS', 'bitmap_columns', 'O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY,O_COMMENT');
CALL set_table_property('ORDERS', 'dictionary_encoding_columns', 'O_ORDERSTATUS,O_ORDERPRIORITY,O_CLERK,O_COMMENT');
CALL set_table_property('ORDERS', 'time_to_live_in_seconds', '31536000');
COMMIT;

DROP TABLE IF EXISTS PARTSUPP;

BEGIN;
CREATE TABLE PARTSUPP
(
    PS_PARTKEY    INT    NOT NULL,
    PS_SUPPKEY    INT    NOT NULL,
    PS_AVAILQTY   INT    NOT NULL,
    PS_SUPPLYCOST DECIMAL(15,2) NOT NULL,
    PS_COMMENT    TEXT   NOT NULL,
    PRIMARY KEY(PS_PARTKEY,PS_SUPPKEY)
);
CALL set_table_property('PARTSUPP', 'distribution_key', 'PS_PARTKEY');
CALL set_table_property('PARTSUPP', 'colocate_with', 'LINEITEM');
CALL set_table_property('PARTSUPP', 'bitmap_columns', 'PS_PARTKEY,PS_SUPPKEY,PS_AVAILQTY,PS_COMMENT');
CALL set_table_property('PARTSUPP', 'dictionary_encoding_columns', 'PS_COMMENT');
CALL set_table_property('PARTSUPP', 'time_to_live_in_seconds', '31536000');
COMMIT;

DROP TABLE IF EXISTS PART;

BEGIN;
CREATE TABLE PART
(
    P_PARTKEY     INT    NOT NULL PRIMARY KEY,
    P_NAME        TEXT   NOT NULL,
    P_MFGR        TEXT   NOT NULL,
    P_BRAND       TEXT   NOT NULL,
    P_TYPE        TEXT   NOT NULL,
    P_SIZE        INT    NOT NULL,
    P_CONTAINER   TEXT   NOT NULL,
    P_RETAILPRICE DECIMAL(15,2) NOT NULL,
    P_COMMENT     TEXT   NOT NULL
);
CALL set_table_property('PART', 'distribution_key', 'P_PARTKEY');
CALL set_table_property('PART', 'bitmap_columns', 'P_PARTKEY,P_SIZE,P_NAME,P_MFGR,P_BRAND,P_TYPE,P_CONTAINER,P_COMMENT');
CALL set_table_property('PART', 'dictionary_encoding_columns', 'P_NAME,P_MFGR,P_BRAND,P_TYPE,P_CONTAINER,P_COMMENT');
CALL set_table_property('PART', 'time_to_live_in_seconds', '31536000');
COMMIT;



DROP TABLE IF EXISTS CUSTOMER;
BEGIN;
CREATE TABLE CUSTOMER
(
    C_CUSTKEY    INT    NOT NULL PRIMARY KEY,
    C_NAME       TEXT   NOT NULL,
    C_ADDRESS    TEXT   NOT NULL,
    C_NATIONKEY  INT    NOT NULL,
    C_PHONE      TEXT   NOT NULL,
    C_ACCTBAL    DECIMAL(15,2) NOT NULL,
    C_MKTSEGMENT TEXT   NOT NULL,
    C_COMMENT    TEXT   NOT NULL
);
CALL set_table_property('CUSTOMER', 'distribution_key', 'C_CUSTKEY');
CALL set_table_property('CUSTOMER', 'bitmap_columns', 'C_CUSTKEY,C_NATIONKEY,C_NAME,C_ADDRESS,C_PHONE,C_MKTSEGMENT,C_COMMENT');
CALL set_table_property('CUSTOMER', 'dictionary_encoding_columns', 'C_NAME,C_ADDRESS,C_PHONE,C_MKTSEGMENT,C_COMMENT');
CALL set_table_property('CUSTOMER', 'time_to_live_in_seconds', '31536000');
COMMIT;

DROP TABLE IF EXISTS SUPPLIER;

BEGIN;
CREATE TABLE SUPPLIER
(
    S_SUPPKEY   INT    NOT NULL PRIMARY KEY,
    S_NAME      TEXT   NOT NULL,
    S_ADDRESS   TEXT   NOT NULL,
    S_NATIONKEY INT    NOT NULL,
    S_PHONE     TEXT   NOT NULL,
    S_ACCTBAL   DECIMAL(15,2) NOT NULL,
    S_COMMENT   TEXT   NOT NULL
);
CALL set_table_property('SUPPLIER', 'distribution_key', 'S_SUPPKEY');
CALL set_table_property('SUPPLIER', 'bitmap_columns', 'S_SUPPKEY,S_NAME,S_ADDRESS,S_NATIONKEY,S_PHONE,S_COMMENT');
CALL set_table_property('SUPPLIER', 'dictionary_encoding_columns', 'S_NAME,S_ADDRESS,S_PHONE,S_COMMENT');
CALL set_table_property('SUPPLIER', 'time_to_live_in_seconds', '31536000');
COMMIT;

DROP TABLE IF EXISTS NATION;

BEGIN;
CREATE TABLE NATION(
  N_NATIONKEY INT NOT NULL PRIMARY KEY,
  N_NAME text NOT NULL,
  N_REGIONKEY INT NOT NULL,
  N_COMMENT text NOT NULL
);
CALL set_table_property('NATION', 'distribution_key', 'N_NATIONKEY');
CALL set_table_property('NATION', 'bitmap_columns', 'N_NATIONKEY,N_NAME,N_REGIONKEY,N_COMMENT');
CALL set_table_property('NATION', 'dictionary_encoding_columns', 'N_NAME,N_COMMENT');
CALL set_table_property('NATION', 'time_to_live_in_seconds', '31536000');
COMMIT;

DROP TABLE IF EXISTS REGION;

BEGIN;
CREATE TABLE REGION
(
    R_REGIONKEY INT  NOT NULL PRIMARY KEY,
    R_NAME      TEXT NOT NULL,
    R_COMMENT   TEXT
);
CALL set_table_property('REGION', 'distribution_key', 'R_REGIONKEY');
CALL set_table_property('REGION', 'bitmap_columns', 'R_REGIONKEY,R_NAME,R_COMMENT');
CALL set_table_property('REGION', 'dictionary_encoding_columns', 'R_NAME,R_COMMENT');
CALL set_table_property('REGION', 'time_to_live_in_seconds', '31536000');
COMMIT;

INSERT INTO public.customer SELECT * FROM public.odps_customer_10g ;
INSERT INTO public.lineitem SELECT * FROM public.odps_lineitem_10g ;
INSERT INTO public.nation SELECT * FROM public.odps_nation_10g ;
INSERT INTO public.orders SELECT * FROM public.odps_orders_10g ;
INSERT INTO public.part SELECT * FROM public.odps_part_10g ;
INSERT INTO public.partsupp SELECT * FROM public.odps_partsupp_10g ;
INSERT INTO public.region SELECT * FROM public.odps_region_10g ;
INSERT INTO public.supplier SELECT * FROM public.odps_supplier_10g ;

vacuum nation;
vacuum region;
vacuum supplier;
vacuum customer;
vacuum part;
vacuum partsupp;
vacuum orders;
vacuum lineitem;

analyze nation;
analyze region;
analyze lineitem;
analyze orders;
analyze customer;
analyze part;
analyze partsupp;
analyze supplier;
analyze lineitem (l_orderkey,l_partkey,l_suppkey);
analyze orders (o_custkey);
analyze partsupp(ps_partkey,ps_suppkey);

Create a compute group

Log on to the Hologres console and go to the details page of the target instance.
In the left navigation pane, click Compute Group Management. The Compute Group Resource Management tab opens and lists the existing compute groups.
Click Add Compute Group. In the dialog box, enter a Compute Group Name and select Compute Group Resources.
In this scenario, create a compute group named read_warehouse_1:
Click Confirm to complete the creation.
On the Compute Group Management page, the compute group is created when its Status changes to Running.

Load data into the compute group

A table group is a data container in Hologres. By default, a new compute group cannot access any table groups. To query data using a compute group, you must first grant it permissions on the required table groups.

View the table groups in the current database.
Run the following SQL statement to view the table groups in the current database:
```
SELECT tablegroup_name
FROM hologres.hg_table_group_properties
GROUP BY tablegroup_name;
```
The following result is an example:
The current database contains only one Table Group.
Load a table group for the compute group.
To use read_warehouse_1 to query data in the erp_database_tg_default table group, run the following SQL statement to load erp_database_tg_default for the read_warehouse_1 compute group:
```
CALL hg_table_group_load_to_warehouse('erp_database.erp_database_tg_default', 'read_warehouse_1', 1);
```
View the table groups loaded for the compute group.
```
SELECT * FROM hologres.hg_warehouse_table_groups;
```
The following result is an example:
This result shows that read_warehouse_1 has loaded the data from the erp_database_tg_default table group.

Set user permissions

By default, unauthorized users cannot access a new compute group. To allow other accounts to access the compute group, you must grant them permission. The following example shows how to grant the ram_test Resource Access Management (RAM) user permission to access the read_warehouse_1 compute group:

View the compute group permissions of a user.
```
SELECT * FROM hologres.hg_warehouse_users;
```

View the default compute group of a user.

SELECT * FROM hologres.hg_user_default_warehouse;

Add a user to the instance.
On the User Management page in HoloWeb, you can add the RAM user to the Hologres instance. For more information, see User management.
Grant the ram_test user query permissions on the erp_database database.
On the DB Authorization page in HoloWeb, you can grant the ram_test user query permissions on the database. For more information, see Quick start for RAM user authorization.
Grant the ram_test user permissions on the read_warehouse_1 compute group.
```
CALL hg_grant_warehouse_access_privilege ('read_warehouse_1', 'p4_2xxxxxxxxxxxxxxx');
```
For more information about the parameters, see Authorize users to use a compute group.
Set the read_warehouse_1 compute group as the default compute group for the ram_test user.
To implement read/write splitting, the ram_test user must use the resources of read_warehouse_1 instead of init_warehouse when connecting to Hologres. Run the following command to set the default compute group for ram_test to read_warehouse_1:
```
CALL hg_set_user_default_warehouse('p4_2xxxxxxxxxxxxxxx', 'read_warehouse_1');
```
View the compute group used by the current account.
After you connect to the instance as the ram_test user, run the following command to verify that the read_warehouse_1 compute group is used:
```
SELECT current_warehouse();
```

Switch traffic between compute groups

After you create the new read_warehouse_1 compute group, if it becomes faulty, you can switch the traffic for the ram_test account to the init_warehouse compute group.

image..png

Important

The traffic switch takes effect only after the application reconnects. Ensure that your application that connects to Hologres has a reconnection mechanism.
When you connect to Hologres, use the automatic routing logic of Hologres. Do not specify the compute group name in the connection string.

Grant the ram_test user permissions on the init_warehouse compute group.
If you have not granted the user ram_test permissions on the init_warehouse compute group, run the following command to grant these permissions to the user ram_test. This allows the user ram_test to use the resources in the init_warehouse compute group.
```
CALL hg_grant_warehouse_access_privilege('init_warehouse', 'p4_2xxxxxxxxxxxxxxx');
```
Set the init_warehouse compute group as the default compute group for the ram_test user.
Run the following command to set the default compute group for the ram_test user to init_warehouse. After the configuration is complete, new connections from the user to the instance will use the resources of the init_warehouse compute group.
```
CALL hg_set_user_default_warehouse('p4_2xxxxxxxxxxxxxxx', 'init_warehouse');
```

Disconnect all users from non-default compute groups.

SELECT hg_kill_non_default_warehouse_connections();

After you reconnect, the instance connection uses the new default compute group.