Configuring Materialized View database information

In CDP Public Cloud, PostgreSQL is automatically configured for SQL Stream Builder (SSB) when creating your Data Hub cluster using the Streaming Analytics cluster template. You can review the database configuration for SSB using Cloudera Manager.

SSB uses PostgreSQL database to store the queried data of the Materialized View feature. In CDP Public Cloud, you can only use PostgreSQL as a database for SSB. In case you need to connect to the PostgreSQL database of SSB, 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 Management Console > Environments, and select the environment where you have created your cluster.
Select Cloudera Manager from the list of services.
Select Clusters > 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 SSB.

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 Management Console > Environments, and select the environment where you have created your cluster.
Select Cloudera Manager from the list of services.
Select Clusters > 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.