All Products
Search

This Product

    Hologres:Quickstart: Use virtual warehouses

    Document Center

    Hologres:Quickstart: Use virtual warehouses

    Last Updated:Jul 29, 2026

    Hologres V2.0 separates computing resources into independent virtual warehouses for elastic, high-availability deployments. This guide covers warehouse creation, data loading, user access, and traffic switching.

    Background information

    Virtual warehouses provide resource isolation, elasticity, read/write splitting, and workload isolation. Virtual warehouse architecture.

    Prerequisites

    Only Hologres instances of V2.0.4 or later support virtual warehouses. If your instance runs a version earlier than V2.0.4, see Common errors during upgrade preparation or join the Hologres DingTalk group for support. For more information, see How do I get more online support?.

    Create a new virtual warehouse

    This example creates a virtual warehouse named read_warehouse_1 for read/write splitting. init_warehouse handles writes, and read_warehouse_1 handles queries.

    image..png

    Create a database

    1. Log on to HoloWeb as a superuser and create a database named erp_database. Create a database.

      In the Create Database dialog box, configure the following parameters:

      • Instance: Select the target instance.

      • Database Name: Enter a database name, for example, erp_database.

      • Permission Policy: Select SPM.

      • Log On Immediately: Select Yes. Logging on to a database closes all open pages under the current database. Make sure you have saved your work.

      Click OK to complete the creation.

    2. Connect to the database and run the following SQL 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 virtual warehouse

    1. Log on to the Hologres console and go to the details page of the target instance.

    2. In the left-side navigation pane, click Virtual Warehouse Management to open the Virtual Warehouse Resource Management tab.

      The page displays the reserved and elastic resource allocation of the instance, and provides an Add Virtual Warehouse button for creating a virtual warehouse.

    3. Click Create Virtual Warehouse. In the dialog box, enter a Virtual Warehouse Name and select a Virtual Warehouse Resource.

      In this scenario, create a virtual warehouse named read_warehouse_1.

      In this scenario, select 32 CU (32 Core 128 GB, nodes: 2) for Virtual Warehouse Resources, and click OK to complete the creation.

    4. Click OK.

    5. On the Virtual Warehouse Management page, the virtual warehouse is created successfully when its status in the Status column is Running.

    Load data into the virtual warehouse

    Table groups are data containers in Hologres. To query data through a virtual warehouse, first load the required table groups into it.

    1. View the table groups in the current database.

      Run the following SQL statement:

      SELECT tablegroup_name
FROM hologres.hg_table_group_properties
GROUP BY tablegroup_name;

      Sample result: The query result shows that the Table Group name is erp_database_tg_default.

      The database has one table group.

    2. Load a table group for the virtual warehouse.

      Load erp_database_tg_default into read_warehouse_1 so it can query the data:

      CALL hg_table_group_load_to_warehouse('erp_database.erp_database_tg_default', 'read_warehouse_1', 1);

    3. Check the loaded table groups.

      SELECT * FROM hologres.hg_warehouse_table_groups;

      Sample result:image..png

      read_warehouse_1 now has the erp_database_tg_default table group loaded.

    Set user permissions

    Users cannot access a new virtual warehouse without explicit permissions. The following example grants a RAM user named ram_test access to read_warehouse_1.

    1. View a user's virtual warehouse permissions.

      SELECT * FROM hologres.hg_warehouse_users;

    2. View a user's default virtual warehouse.

      SELECT * FROM hologres.hg_user_default_warehouse;

    3. Add a user to the instance.

      On the Users page in HoloWeb, add the RAM user to the Hologres instance. User management.

      The added RAM user is displayed in the User Account list, with the default role type set to Normal. You can adjust permissions through the Modify Role Type option in the Actions column.

    4. Grant the ram_test user query permissions on the erp_database database.

      On the Database authorization page in HoloWeb, grant query permissions on the database to the ram_test user. Grant permissions to a RAM user.

    5. Grant the ram_test user access to the read_warehouse_1 virtual warehouse.

      CALL hg_grant_warehouse_access_privilege ('read_warehouse_1', '"p4_2xxxxxxxxxxxxxxx"');

      Authorize users to use a virtual warehouse.

    6. Set read_warehouse_1 as the default virtual warehouse for the ram_test user.

      For read/write splitting, ram_test must connect through read_warehouse_1 instead of init_warehouse. Set the default warehouse for the ram_test user:

      CALL hg_set_user_default_warehouse('"p4_2xxxxxxxxxxxxxxx"', 'read_warehouse_1');

    7. Check the virtual warehouse used by the current account.

      After connecting as ram_test, verify the active warehouse:

      SELECT current_warehouse();

    Switch traffic between virtual warehouses

    If read_warehouse_1 becomes faulty, switch ram_test traffic back to init_warehouse.

    image..png

    Important

    • Traffic switches take effect on reconnection. Ensure your application has a reconnection mechanism.

    • Use automatic routing when connecting to Hologres. Do not specify a virtual warehouse name in the connection string.

    1. Grant the ram_test user access to the init_warehouse virtual warehouse.

      If ram_test does not already have access to init_warehouse, grant ram_test permissions so ram_test can use init_warehouse resources:

      CALL hg_grant_warehouse_access_privilege('init_warehouse', '"p4_2xxxxxxxxxxxxxxx"');

    2. Set init_warehouse as the default virtual warehouse for the ram_test user.

      Set the default warehouse for ram_test to init_warehouse. New connections will use init_warehouse.

      CALL hg_set_user_default_warehouse('"p4_2xxxxxxxxxxxxxxx"', 'init_warehouse');

    3. Terminate connections not using the default virtual warehouse.

      SELECT hg_kill_non_default_warehouse_connections();

    4. After reconnecting, your new connection uses the default virtual warehouse.