Getting started with virtual warehouse instances - Hologres

Hologres V2.0 introduces virtual warehouse instances as a new type of elastic and high-availability instances. Computing resources are divided into multiple virtual warehouses that are more suitable for high-availability scenarios. This topic describes how to use virtual warehouses.

Background information

Hologres V1.1 supports the multi-instance high-availability deployment mode in which storage resources are shared among instances. A primary instance can be associated with multiple read-only secondary instances. The primary instance and all its secondary instances share storage resources, but their computing resources are isolated. This ensures read/write splitting and implements multi-instance high-availability deployment. For more information, see Configure read/write splitting for primary and secondary instances (shared storage).

However, this deployment mode has the following limits:

Each instance has a separate endpoint. You need to change the endpoint if traffic switches between instances.
Read-only secondary instances share a set of metadata with the primary instance. You cannot configure parameters for a specific read-only secondary instance based on business requirements. For example, you cannot configure different number of replicas for different read-only secondary instances to achieve high availability of queries.

Virtual warehouses are introduced in Hologres V2.0 to resolve these issues. Virtual warehouses are a new type of elastic and high-availability instances that provide computing resources. Compared with read-only secondary instances, virtual warehouses have the following benefits:

Virtual warehouses share data and metadata.
Virtual warehouses share an endpoint and allow traffic switching without endpoint change.

For more information about the architecture of virtual warehouses, see Architecture of virtual warehouse instances.

Precautions

Only Hologres V2.0.4 and later support the virtual warehouse feature. If the version of your Hologres instance is earlier than V2.0.4, 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 Instance upgrades. For more information about how to join the Hologres DingTalk group, see Obtain online support for Hologres.

Scenarios

Scenario 1: Create a virtual warehouse

Implementation diagram
In the following figure, the virtual warehouse init_warehouse is used to write data, and the virtual warehouse read_warehouse_1 is used to read data.

Procedure

Create a database.

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

Execute the following SQL statements to import sample data to the database:

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 virtual warehouse.
1. Log on to the HoloWeb console as a superuser. In the top navigation bar, click Security Center. In the left-side navigation pane of the page that appears, click Compute Group Management.
  You can view existing virtual warehouses on the Compute Group Management page.
2. In the upper-right corner of the Compute Group Management page, click Create Compute Group.
3. In the Create Compute Group dialog box, configure the Compute Group Name and Compute Group Resources parameters and click OK.
  In this scenario, a virtual warehouse named read_warehouse_1 is created.
4. On the Compute Group Management page, view the status of the virtual warehouse. If the status of the virtual warehouse is Running, the virtual warehouse is successfully created.
Load data to the virtual warehouse.
Table groups are used to store data in Hologres. By default, you cannot use a new virtual warehouse to access a table group. If you want to use a virtual warehouse to access a table group, you need to grant the virtual warehouse required permissions on the table group.
1. View the table groups that are configured for the current database.
  Execute the following SQL statement to view the table groups that are configured for the current database:
```
SELECT tablegroup_name
FROM hologres.hg_table_group_properties
GROUP BY tablegroup_name;
```
  The following figure shows the returned result.
  Only one table group is configured for the current database.
2. Load the table group to the virtual warehouse.
  In this example, you need to use the virtual warehouse read_warehouse_1 to read data in the table group erp_database_tg_default in the database. In this case, you need to execute the following SQL statement to load the table group erp_database_tg_default to the virtual warehouse read_warehouse_1:
```
CALL hg_table_group_load_to_warehouse ('erp_database.erp_database_tg_default', 'read_warehouse_1', 1);
```
3. Check whether the table group is loaded to the virtual warehouse.
```
select * from hologres.hg_warehouse_table_groups;
```
  The following figure shows the returned result.
  Data in the table group erp_database_tg_default is loaded to the virtual warehouse read_warehouse_1.
Grant permissions to a RAM user.
By default, you need to grant permissions on a new virtual warehouse to a RAM user before you can use the virtual warehouse by using the RAM user. This section describes how to grant permissions on the virtual warehouse read_warehouse_1 to the RAM user ram_test.
1. View permissions on the virtual warehouse.
```
select * from hologres.hg_warehouse_users;
```
2. View the configurations of the default virtual warehouse.
```
select * from hologres.hg_user_default_warehouse;
```
3. Add the RAM user to the Hologres instance.
  Log on to the HoloWeb console. On the User Management page, add the RAM user to your Hologres instance. For more information, see Manage users.
4. Grant the read permissions on the database erp_database to the RAM user ram_test.
  On the Database Authorization page of the HoloWeb console, grant the read permissions on the database to the RAM user ram_test. For more information, see Grant permissions to a RAM user.
5. Grant permissions on the virtual warehouse read_warehouse_1 to the RAM user ram_test.
```
CALL hg_grant_warehouse_access_privilege ('read_warehouse_1', 'p4_2xxxxxxxxxxxxxxx');
```
  For more information, see Manage permissions on virtual warehouses.
6. Configure the virtual warehouse read_warehouse_1 as the default virtual warehouse for the RAM user ram_test.
  In this example, the RAM user ram_test needs to access the Hologres instance and use the virtual warehouse read_warehouse_1 to implement read/write splitting. In this case, you need to execute the following statement to configure read_warehouse_1 as the default virtual warehouse for ram_test:
```
CALL hg_set_user_default_warehouse ('p4_2xxxxxxxxxxxxxxx', 'read_warehouse_1');
```
7. View the virtual warehouse that is used by the RAM user.
  Execute the following statement to check whether the RAM user ram_test uses the virtual warehouse read_warehouse_1:
```
select current_warehouse();
```

Scenario 2: Switch traffic between virtual warehouses

If the virtual warehouse read_warehouse_1 is faulty in Scenario 1, you need to switch traffic of the RAM user ram_test to the virtual warehouse init_warehouse.

Implementation diagram
Precautions
- Traffic switchover takes effect only for new connections. Therefore, you must make sure that the connection between your application and the Hologres instance can be reestablished.
- When you connect your application to Hologres, you must use the automatic routing logic of Hologres. Do not specify a virtual warehouse in the connection string.
Procedure
1. Grant permissions on the virtual warehouse init_warehouse to the RAM user ram_test.
  If you have not granted permissions on init_warehouse to ram_test, you must execute the following statement to grant ram_test the permissions on init_warehouse:
```
CALL hg_grant_warehouse_access_privilege ('init_warehouse', 'p4_2xxxxxxxxxxxxxxx');
```
2. Configure init_warehouse as the default virtual warehouse for ram_test.
  Execute the following statement to configure init_warehouse as the default virtual warehouse for ram_test. After the configuration is complete, init_warehouse is used if a connection is reestablished between ram_test and the Hologres instance.
```
CALL hg_set_user_default_warehouse ('p4_2xxxxxxxxxxxxxxx', 'init_warehouse');
```
3. Disconnect connections between all users and non-default virtual warehouses.
```
SELECT hg_kill_non_default_warehouse_connections(); 
```
4. After the connections are reestablished, ram_test uses the new default virtual warehouse.