Configuring Materialized View database information

In CDP Private Cloud Base, PostgreSQL is configured during the installation of SQL Stream Builder (SSB) as a service. 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 Private Cloud Base, 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?

  1. Go to your cluster in Cloudera Manager.
  2. Select SQL Stream Builder from the list of services
  3. Click Configuration.
  4. 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.
  1. Connect to the database host using ssh.
    ssh [***USERNAME***]@[***DATABASE HOST***]
    Password: [***PASSWORD***]
  2. Connect to PostgreSQL.
    sudo -U postgres psql
  3. 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***];
    After changing the password or username, you need to update the database information in Cloudera Manager.
  4. Navigate to back to your cluster in Cloudera Manager.
  5. Click SQL Stream Builder > Configuration.
  6. Filter down the configuration parameters to Materialized View Engine.
    The list of Materialized View Engine paramaters are displayed.
  7. Update the Database user or the Database password field based on the new parameters you provided in PostgreSQL.