All Products
Search

This Product

    Hologres:Getting started with virtual warehouses

    Document Center

    Hologres:Getting started with virtual warehouses

    Last Updated:Apr 28, 2025

    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 V2.0 introduces virtual warehouse instances as a new type of instance. Computing resources are divided into different virtual warehouses, which effectively support multiple scenarios such as read/write splitting, resource isolation, and business isolation. This provides users with core capabilities such as resource isolation and elasticity. For more information about the architecture of virtual warehouses, see Architecture of virtual warehouse instances.

    Precautions

    Only Hologres V2.0.4 and later support virtual warehouse instances. 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.

    Create a virtual warehouse instance

    In this section, the virtual warehouse read_warehouse_1 is created to implement read/write splitting. The virtual warehouse init_warehouse is used to write data, and the virtual warehouse read_warehouse_1 is used to read data.

    image..png

    Create a database

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

    2. 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 Hologres console and go to the Instance Details page of a specific instance.

    2. In the left-side navigation pane, click Virtual Warehouse Management to go to the Virtual Warehouse Resource Management tab. You can view the list of existing virtual warehouses on this tab.

      jisuan1

    3. Click Create Virtual Warehouse. In the dialog box that appears, configure the Virtual Warehouse Name and Virtual Warehouse Resources parameters to create a virtual warehouse.

      In this example, a virtual warehouse named read_warehouse_1 is created.

      w1

    4. Click OK.

    5. On the Virtual Warehouse Management page, view the status of the virtual warehouse. When the status of the virtual warehouse becomes Running, the virtual warehouse is successfully created.

      e33

    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.image..png

      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.image..png

      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. The following steps describe 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.

      image..png

    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.image..png

    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 the RAM user 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();

    Switch traffic between virtual warehouses

    If the virtual warehouse read_warehouse_1 is found faulty after you create it, you need to switch traffic of the RAM user ram_test to the virtual warehouse init_warehouse.

    image..png

    Important

    • Traffic switchover takes effect only after connections are re-established. Therefore, you must make sure that your application connecting to Hologres has a reconnection mechanism.

    • 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.

    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 the RAM user 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 the RAM user ram_test.

      Execute the following statement to configure init_warehouse as the default virtual warehouse for the RAM user ram_test. After the configuration is complete, init_warehouse will be used if a connection is re-established between the RAM user 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 re-established, the RAM user ram_test uses the new default virtual warehouse.