How to use JDBC and PSQL to connect to a virtual warehouse instance - Hologres

When a Hologres instance has multiple virtual warehouses, you can target a specific one at connection time — or switch warehouses mid-session without reconnecting. This page explains how to specify a virtual warehouse in JDBC and psql connection strings, and how to switch warehouses using SQL after connecting.

Connect using JDBC

Connection string format

Without a virtual warehouse (uses your default virtual warehouse):

jdbc:postgresql:<Endpoint>:<Port>/<database_name>

With a virtual warehouse:

jdbc:postgresql://<Endpoint>:<Port>/<database_name>@<warehouse_name>

Parameters

Parameter	Required	Description
`Endpoint`	Yes	The endpoint of the Hologres instance. Go to the Hologres console, click Instances in the left navigation pane, click the target instance, and find the endpoint in the Network Information section on the Instance Details page. Select the endpoint that matches your network environment — using the wrong endpoint causes the connection to fail.
`Port`	Yes	The port of the Hologres instance. Found in the same Network Information section.
`database_name`	Yes	The name of the Hologres database. After an instance is created, the system automatically creates the postgres database. The postgres database has limited resources; for business workloads, create a dedicated database instead. Example: `demo`.
`warehouse_name`	No	The virtual warehouse to use for this connection. If omitted, the connection uses your default virtual warehouse. To switch warehouses after connecting, use `SET current_warehouse`.

Examples

Without a virtual warehouse — connects to the demo database using the default virtual warehouse:

jdbc:postgresql://hgpostcn-cn-zz4xxxxxxxxxx-cn-shenzhen-vpc-st.hologres.aliyuncs.com:80/demo

With a virtual warehouse — connects to the demo database using warehouse_1:

jdbc:postgresql://hgpostcn-cn-zz4xxxxxxxxxx-cn-shenzhen-vpc-st.hologres.aliyuncs.com:80/demo@warehouse_1

Virtual warehouse route priority

When a connection string does not explicitly specify a virtual warehouse, Hologres resolves the virtual warehouse for the connection in the following priority order:

The virtual warehouse specified in the connection string, as shown in the preceding examples.
Your user default virtual warehouse, if you have set one by calling hg_set_user_default_warehouse.
The instance default virtual warehouse, init_warehouse, which is used only if neither of the above is set.

To check which virtual warehouse the current connection is actually using, run:

SELECT current_warehouse();

Automatic routing for write requests (DML)

The Grand Unified Configuration (GUC) parameter hg_experimental_enable_warehouse_dml_auto_routing controls how write requests (DML) are routed across virtual warehouses. This parameter is on by default. To confirm the current value, run SHOW hg_experimental_enable_warehouse_dml_auto_routing;. While the parameter is on, write requests are automatically routed to the leader virtual warehouse for execution, so writes succeed even when your connection uses a non-leader virtual warehouse.

If you turn this parameter off, write requests issued on a non-leader virtual warehouse are rejected. Write requests issued directly on the leader virtual warehouse are unaffected and continue to succeed.

Connect using psql

Connection string format

Without a virtual warehouse (uses your default virtual warehouse):

PGUSER="<AccessKey_ID>" PGPASSWORD="<AccessKey_Secret>" psql -h <Endpoint> -p <Port> -d <database_name>

With a virtual warehouse:

PGUSER="<AccessKey_ID>" PGPASSWORD="<AccessKey_Secret>" psql -h <Endpoint> -p <Port> -d <database_name>@<warehouse_name>

Note

The -d flag accepts <database_name>@<warehouse_name> as a single value. The @<warehouse_name> part is not a separate flag — it is appended directly to the database name.

Parameters

Parameter	Required	Description
`AccessKey_ID`	Yes	Alibaba Cloud account: Your AccessKey ID. Get it from AccessKey Management. Custom account: Your username, for example `BASIC$abc`.
`AccessKey_Secret`	Yes	Alibaba Cloud account: Your AccessKey secret. Custom account: Your password.
`Endpoint`	Yes	The endpoint of the Hologres instance. Found on the Instance Details page in the Hologres console.
`Port`	Yes	The port of the Hologres instance. Found on the same Instance Details page.
`database_name`	Yes	The name of the Hologres database. After an instance is created, the system automatically creates the postgres database. The postgres database has limited resources; for business workloads, create a dedicated database instead. Example: `demo`.
`warehouse_name`	No	The virtual warehouse to use for this connection. Appended to `database_name` as `<database_name>@<warehouse_name>`. If omitted, the connection uses your default virtual warehouse.

Examples

Command line — without a virtual warehouse:

PGUSER="xxx" PGPASSWORD="xxx" psql -h hgpostcn-cn-zz4xxxxxxxxxx-cn-shenzhen-vpc-st.hologres.aliyuncs.com -p 80 -d demo

Command line — with a virtual warehouse:

PGUSER="xxx" PGPASSWORD="xxx" psql -h hgpostcn-cn-zz4xxxxxxxxxx-cn-shenzhen-vpc-st.hologres.aliyuncs.com -p 80 -d demo@warehouse_1

Switch the virtual warehouse using SQL

Available in Hologres V4.0 and later.

Use SET current_warehouse to switch the virtual warehouse for the current connection without reconnecting. To check the active warehouse at any point, run SHOW current_warehouse.

Basic usage:

-- Switch to a different virtual warehouse
SET current_warehouse = <warehouse_name>;

-- Verify the active virtual warehouse
SHOW current_warehouse;

Switch and restore — if you need to temporarily use a different warehouse and then return to the original:

-- Record the current warehouse
SHOW current_warehouse;

-- Switch to a different warehouse
SET current_warehouse = warehouse_2;

-- Verify the switch
SHOW current_warehouse;

-- Restore the original warehouse when done
SET current_warehouse = <original_warehouse_name>;

Usage notes

Switching warehouses preserves all session state, including Grand Unified Configuration (GUC) variables and prepared statements.
This setting applies at the session level only. Attempting to set it at the database or user level returns the error: This can only be set at session level.
The current user must have permissions on the target virtual warehouse. Without the required permissions, the command returns: Permission denied for warehouse "xxx".
Switching is not supported for connections that have an active temporary table (TEMP TABLE).