Setting up PostgreSQL database for SSB

After installing PostgreSQL server, you must rename the JDBC connector, and create a database with credentials for SQL Stream Builder (SSB) before installing the service on your cluster. You also must install the PostgreSQL Python connector to integrate with the Streaming SQL Engine.

Before you begin

You need to install and configure PostgreSQL, before setting up the databases for SSB. To install and configure PostgreSQL, you must complete the basic steps mentioned in the Private Cloud Base documentation.

Install and configure PostgreSQL for Cloudera Software

Installing the PostgreSQL JDBC connector

Download the PostgreSQL JDBC Driver from the PostgreSQL website.
Rename the JDBC jar file to postgresql-connector-java.jar.
```
mv postgresql-jdbc.jar postgresql-connector-java.jar
```
Copy the PostgreSQL JDBC jar file to your host.
This host must be the same host where you plan to assign the Streaming SQL Engine service role. The service roles are assigned as a next step when installing SQL Stream Builder as a service in Cloudera Manager.
```
scp <location>/postgresql-connector-java.jar root@<your_hostname>:
```
You will be prompted to provide your password.
Access the host on your cluster.
This host must be the same host where you have added the JDBC jar file.
```
ssh root@<your_hostname>
```
You will be prompted to provide your password.
Copy the PostgreSQL JDBC jar file to /usr/share/java folder using the following command:
```
sudo mkdir -p /usr/share/java
sudo cp <location>/postgresql-connector-java.jar /usr/share/java
```
Check if the PostgreSQL connector is in the folder with ls command.

Creating PostgreSQL database for SSB

Access a host on your cluster.
This host must be the same host where you plan to assign the Materialized Views Engine and Streaming SQL Engine service role. The service roles are assigned as a next step when installing SQL Stream Builder as a service in Cloudera Manager.
```
ssh root@<your_hostname>
```
You will be prompted to provide your password.
Connect to PostgreSQL:
```
sudo -u postgres psql
```

Create a database for the SQL Stream Builder metadata:

CREATE ROLE ssb_admin LOGIN PASSWORD '<password>';
CREATE DATABASE ssb_admin OWNER ssb_admin ENCODING 'UTF8';

Create a database for the Materialized View Engine:

CREATE ROLE ssb_mve LOGIN PASSWORD '<password>';
CREATE DATABASE ssb_mve OWNER ssb_mve ENCODING 'UTF8';

Confirm that you have created the Streaming SQL Engine and Materialized View Engine database using the \l command.