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

This topic describes how to use Java Database Connectivity (JDBC) and PSQL to connect to a virtual warehouse instance.

Connect to a Hologres instance using JDBC

Command format

If a virtual warehouse is not specified in the connection string, the connection uses the user's default virtual warehouse.
```
jdbc:postgresql:<Endpoint>:<Port>/<database_name>
```

Specify a virtual warehouse.

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

Parameter descriptions

Parameter	Required	Description
Endpoint	Yes	The network address and port of the Hologres instance. Go to the Hologres console. In the navigation pane on the left, click Instances. Click the target instance. On the Instance Details page, find the endpoint and port in the Network Information section. Important Select the correct network address and port for the network environment where the SQL commands run. Otherwise, the connection fails.
Port	Yes
database_name	Yes	The name of the Hologres database. After a Hologres instance is created, the system automatically creates the postgres database. Use the postgres database to connect to Hologres. However, this database is allocated few resources. For business development, create a new database. For more information, see Create a database. Example: demo.
warehouse_name	No	The name of the virtual warehouse used to connect to the instance.

Usage examples

Virtual warehouse not specified
The following JDBC connection string connects to the demo database of the hgpostcn-cn-zz4xxxxxxxxxx instance. Because a virtual warehouse is not specified, the system uses the user's default virtual warehouse for the connection.
```
jdbc:postgresql://hgpostcn-cn-zz4xxxxxxxxxx-cn-shenzhen-vpc-st.hologres.aliyuncs.com:80/demo
```
Virtual warehouse specified
The following JDBC connection string connects to the demo database of the hgpostcn-cn-zz4xxxxxxxxxx instance. The connection uses the specified virtual warehouse `warehouse_1`.
```
jdbc:postgresql://hgpostcn-cn-zz4xxxxxxxxxx-cn-shenzhen-vpc-st.hologres.aliyuncs.com:80/demo@warehouse_1
```

Connect to a Hologres instance using PSQL

Command format

If a virtual warehouse is not specified in the connection string, the connection uses the user's default virtual warehouse.
```
PGUSER="<AccessKey_ID>" PGPASSWORD="<AccessKey_Secret>" psql -h <Endpoint> -p <Port> -d <database_name>
```

Specify a virtual warehouse.

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

Parameter descriptions

Parameter	Required	Description
AccessKey_ID	Yes	Alibaba Cloud account: The AccessKey ID of your Alibaba Cloud account. Click AccessKey Management to obtain the AccessKey ID. Custom account: The username of the custom account, such as BASIC$abc.
AccessKey_Secret	Yes	Alibaba Cloud account: The AccessKey secret of your Alibaba Cloud account. Custom account: The password of the custom account.
Endpoint	Yes	The network address of the Hologres instance. Go to the Instance Details page in the Hologres console to obtain the network address.
Port	Yes	The network port of the Hologres instance. Go to the Instance Details page in the Hologres console to obtain the network port.
database_name	Yes	The name of the Hologres database. After a Hologres instance is created, the system automatically creates the postgres database. Use the postgres database to connect to Hologres. However, this database is allocated few resources. For business development, create a new database. For more information, see Create a database. Example: demo.
warehouse_name	No	The name of the virtual warehouse used to connect to the instance.

Usage examples

Virtual warehouse not specified
The following PSQL connection string connects to the demo database of the hgpostcn-cn-zz4xxxxxxxxxx instance. Because a virtual warehouse is not specified, the system uses the user's default virtual warehouse for the connection.
```
PGUSER="xxx" PGPASSWORD="xxx" psql -h hgpostcn-cn-zz4xxxxxxxxxx-cn-shenzhen-vpc-st.hologres.aliyuncs.com -p 80 -d demo
```
Virtual warehouse specified
The following PSQL connection string connects to the demo database of the hgpostcn-cn-zz4xxxxxxxxxx instance. The connection uses the specified virtual warehouse `warehouse_1`.
```
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 for the current connection using SQL

In Hologres V4.0 and later, you can switch the virtual warehouse for the current connection using SQL.

Command format

-- Switch the virtual warehouse for the current connection
SET current_warehouse = <warehouse_name>;

-- Verification: Query the virtual warehouse for the current connection
SHOW current_warehouse;

Precautions

When you switch the virtual warehouse for a connection, states such as Grand Unified Configuration (GUC) variables and prepared statements for the connection remain unchanged.
This setting is supported only at the session level, not at the database or user level. If you attempt to apply this setting at the database or user level, an error is reported: 'This can only be set at session level.'
The user for the current connection must have permissions on the destination virtual warehouse. If the user does not have the required permissions, an error is reported: 'Permission denied for warehouse "xxx".'
You cannot switch the virtual warehouse for a connection that contains a temporary table (TEMP TABLE).