Setting up Oracle database for SSB

After installing Oracle database 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 Oracle Python connector to integrate with the Streaming SQL Console.

Before you begin

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

Install and configure Oracle Database for Cloudera Software

Installing the Oracle JDBC connector

Download the Oracle JDBC Driver from the Oracle website.
Rename the JDBC jar file to oracle-connector-java.jar.
```
mv ojdbc8-19.3.0.0.jar oracle-connector-java.jar
```

Copy the Oracle JDBC jar file to /usr/share/java folder using the following command:

sudo mkdir -p /usr/share/java
sudo cp <location>/oracle-connector-java.jar /usr/share/java
sudo chmod 644 /usr/share/java/oracle-connector-java.jar

Check if the Oracle connector is in the folder with ls command.

Creating Oracle database for SSB

Create a user and schema for Streaming SQL Console:

create user ssb_admin identified by <password> default tablespace ssb_admin;
grant CREATE SESSION to ssb_admin;
grant CREATE TABLE to ssb_admin;
grant CREATE SEQUENCE to ssb_admin;
grant EXECUTE on sys.dbms_lob to ssb_admin;

Grant a quota on the ssb_admin tablespace where the tables will be created:
```
ALTER USER ssb_admin quota 100m on ssb_admin;
```
You can also create unlimited space with the following command:
```
ALTER USER ssb_admin quota unlimited on ssb_admin;
```
Confirm that you have created the Streaming SQL Console and Materialized View Engine database using the \l command.

Installing the Oracle Python connector

Use the following sample commands to install the correct version for Oracle:

RHEL

Install the python-pip package:
```
sudo yum install python3-pip
```
Create a directory where you install the package:
```
mkdir -p /usr/share/python3
```
Install the 2.8.5 version for psycopg2 using pip:
```
sudo pip3 install psycopg2-binary==8.1.0 -t /usr/share/python3
```

Ubuntu

Install the python-pip package:
```
sudo apt-get install python3-pip
```
Create a directory where you install the package:
```
mkdir -p /usr/share/python3
```
Install the 2.8.5 version for psycopg2 using pip:
```
sudo pip3 install psycopg2-binary==8.1.0 -t /usr/share/python3
```

SLES 12

Install the python-pip package:
```
sudo zypper install python3-pip
```
Create a directory where you install the package:
```
mkdir -p /usr/share/python3
```
Install the 2.8.5 version for psycopg2 using pip:
```
sudo pip3 install psycopg2-binary==8.1.0 -t /usr/share/python3
```