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 services.
Select Configuration>Database Settings.
Select sql_stream_builder from Scope to filter down the configuration properties.
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 services.
You are redirected to the Cloudera Manager user interface.
Select Configuration>Database Settings.
Select sql_stream_builder from Scope to filter down the configuration properties.
Update the Database user or the Database password field based on the new parameters you provided in PostgreSQL.

Configuring Materialized View database information

Where to find the PostgreSQL database information?🔗

We want your opinion

How can we improve this page?