Configuring Materialized View database information

In Cloudera on cloud, PostgreSQL is automatically configured for Cloudera SQL Stream Builder when creating your Cloudera Data Hub cluster using the Cloudera Streaming Analytics cluster template. You can review the database configuration for Cloudera SQL Stream Builder using Cloudera Manager.

Cloudera SQL Stream Builder uses PostgreSQL database to store the queried data of the Materialized View feature. In Cloudera on cloud, you can only use PostgreSQL as a database for Cloudera SQL Stream Builder. In case you need to connect to the PostgreSQL database of Cloudera SQL Stream Builder, you can review the necessary information of the database in Cloudera Manager. You can also configure the user and the password of the database using CLI, and then updating the existing information in Cloudera Manager.

Where to find the PostgreSQL database information?

Navigate to Cloudera Management Console > Environments, and select the environment where you have created your cluster.
Select Cloudera Manager from the list of services.
Select Clusters > Cloudera SQL Stream Builder.
Click Configuration.
Filter down the configuration parameters to Materialized View Engine.
The following information is listed about the PostgreSQL database:
- Database Name
- Database Host
- Database Port
- Database User
- Database Password

When you want to change the default configuration of PostgreSQL database, first you need to change the information in PostgreSQL and then update the configuration in Cloudera Manager for Cloudera SQL Stream Builder.

Connect to the database host using ssh.

ssh [***WORKLOAD USERNAME***]@[***DATABASE HOST***]
Password: [***YOUR WORKLOAD PASSWORD***]

Connect to PostgreSQL.
```
sudo -U postgres psql
```
note
As an EnvironmentUser, you will be prompted to provide the password related to the PostgreSQL client. In this case, please reach out to your administrator to provide the correct password for the PostgrSQL client connection.
Change the username or the password for the database.
```
ALTER USER eventador_admin WITH PASSWORD [***NEW PASSWORD***];
ALTER USER eventador_admin RENAME TO [***NEW USERNAME***];
```
note
When changing the database username, the existing password for the username is deleted. You must change the password as well when renaming the user of the database.
After changing the password or username, you need to update the database information in Cloudera Manager.
Navigate to Cloudera Management Console > Environments, and select the environment where you have created your cluster.
Select Cloudera Manager from the list of services.
Select Clusters > Cloudera SQL Stream Builder.
Click Configuration.
Filter down the configuration parameters to Materialized View Engine.
The list of Materialized View Engine paramaters are displayed.
Update the Database user or the Database password field based on the new parameters you provided in PostgreSQL.