Setting up Oracle database for Cloudera SQL Stream Builder

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

Before you begin

You need to install and configure Oracle database, before setting up the databases for Cloudera SQL Stream Builder. To install and configure Oracle DB, you must complete the basic steps mentioned in the Cloudera Base on premises 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 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 Cloudera SQL Stream Builder as a service in Cloudera Manager.
```
scp <location>/oracle-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 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 Cloudera SQL Stream Builder

Access a host on your cluster.
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 Cloudera SQL Stream Builder as a service in Cloudera Manager.
```
ssh root@<your_hostname>
```
You will be prompted to provide your password.
Log in to the Oracle client:
```
sqlplus system@localhost
Enter password: ******
```

Create a user and schema for Streaming SQL Engine:

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 Engine and Materialized View Engine database using the \l command.