Prepare Oracle Driver vsolution in SAP Data Intelligence

In case you want to connect SAP Data Intelligence to Oracle, the oracle driver needs to be prepared according to the Data Intelligence: Administration Guide -> ORACLE Connection.

The steps, which are mentioned in the Prerequisites section, require an UNIX environment. If you try to execute the steps on a windows machine, symbolic links are lost and the oracle driver vsolution is not recognized.

The following guide explains how the Prerequisite steps can be executed in the SAP Data Intelligence environment within 3 steps.

[Guide created & tested with Data Intelligence Cloud Version 2308.14.35]

Step 1: Prepare a Dockerfile

Create a new Dockerfile (You can refer creating dockerfiles for the detailed steps of creating a Dockerfile in SAP Data Intelligence). The first instructions are copied from the org.opensuse sample in Data Intelligence and are needed to run a graph with this Docker container.

FROM opensuse/leap:15.4

# Install required tools and Python 3.9
RUN zypper --non-interactive update && \
 zypper --non-interactive install --no-recommends --force-resolution \
     tar \
     gzip \
     gcc \
     gcc-c++ \
     wget \
     libgthread-2_0-0 \
     python39 \
     python39-pip

 # Install required python modules
RUN python3.9 -m pip --no-cache install \
   tornado==6.1.0 \
   jsonpath-python==1.0.5 \
   oauthlib==3.1.0 \
   requests-oauthlib==1.3.1 \
   pandas==1.2.5 \
   pyarrow==4.0.1 \
   u-msgpack-python==2.7.1
   
# Setup dedicated vflow user   
RUN groupadd -g 1972 vflow && useradd -g 1972 -u 1972 -m vflow
USER 1972:1972
WORKDIR /home/vflow
ENV HOME=/home/vflow

Next we add the instructions for the oracle driver preparation to the Dockerfile (Hint: Here we use Oracle driver version 19.17.0.0 here). The instructions are explained in Data Intelligence: Administration Guide -> ORACLE Connection.

The output of the instructions is a zip file within the Docker container in the current working directory.

## Start preparing Oracle driver

USER root
# Install zip and unzip
RUN zypper --non-interactive install --no-recommends --force-resolution zip unzip

USER 1972

# Download Oracle driver package
RUN curl -s --location https://download.oracle.com/otn_software/linux/instantclient/1917000/instantclient-basiclite-linux.x64-19.17.0.0.0dbru.zip --output instantclient-basiclite-linux.x64-19.17.0.0.0dbru.zip
RUN mkdir -p oracle_vsolution/content/files/flowagent
RUN unzip instantclient-basiclite-linux.x64-19.17.0.0.0dbru.zip -d oracle_vsolution/content/files/flowagent/

RUN echo '{    "name": "vsolution_oracle",    "version": "19.17.0",    "format": "2",    "dependencies": []}' > oracle_vsolution/manifest.json

RUN mkdir -p oracle_vsolution/content/files/flowagent/orapki
# Download Oracle JDBC
RUN curl -s --location https://download.oracle.com/otn-pub/otn_software/jdbc/1917/ojdbc8-full.tar.gz > ojdbc8-full.tar.gz
RUN tar -xzvf ojdbc8-full.tar.gz
RUN cp ojdbc8-full/oraclepki.jar oracle_vsolution/content/files/flowagent/orapki/
RUN cp ojdbc8-full/osdt_core.jar oracle_vsolution/content/files/flowagent/orapki/
RUN cp ojdbc8-full/osdt_cert.jar oracle_vsolution/content/files/flowagent/orapki/

RUN echo 'ORACLE_INSTANT_CLIENT=./instantclient_19_17' > oracle_vsolution/content/files/flowagent/oracle.properties
RUN echo 'NLS_LANG=AMERICAN_AMERICA.UTF8' >> oracle_vsolution/content/files/flowagent/oracle.properties
RUN echo 'ORACLE_ORAPKI_PATH=./orapki' >> oracle_vsolution/content/files/flowagent/oracle.properties

# create oracle solution
RUN cd oracle_vsolution && zip -y -r oracle_vsolution.zip ./
RUN mv oracle_vsolution/oracle_vsolution.zip ./

Last add the following tags to the Dockerfile:

• oracle_driver
• python36
• tornado 5.0.2

Step 2: Create a Pipeline

Next, a Data Intelligence Pipeline is needed to copy the oracle_vsolution zip archive from the Docker container into the Data Intelligence filesystem.

The first python operator is used to send a cp oracle_vsolution.zip /vrep/oracle_vsolution.zip statement to the Command Executor operator. After the successful copy operation, the graph is terminated.

{
	"properties": {},
	"description": "Copy oracle driver solution from the Docker container to the DI filesystem.",
	"processes": {
		"commandexecutor1": {
			"component": "com.sap.system.commandExecutor",
			"metadata": {
				"label": "Command Executor",
				"x": 292,
				"y": 40,
				"height": 80,
				"width": 120,
				"generation": 1,
				"config": {
					"cmdLine": "bash"
				}
			}
		},
		"python3operator1": {
			"component": "com.sap.system.python3Operator",
			"metadata": {
				"label": "Python3: Trigger command",
				"x": 24,
				"y": 40,
				"height": 80,
				"width": 120,
				"extensible": true,
				"filesRequired": [
					"script.py"
				],
				"generation": 1,
				"config": {
					"script": "def gen():\n    api.send(\"output\", \"cp oracle_vsolution.zip /vrep/oracle_vsolution.zip\")\n\napi.add_generator(gen)\n"
				},
				"additionaloutports": [
					{
						"name": "output",
						"type": "string"
					}
				]
			}
		},
		"toblobconverter1": {
			"component": "com.sap.util.toBlobConverter",
			"metadata": {
				"label": "ToBlob Converter",
				"x": 193,
				"y": 55,
				"height": 50,
				"width": 50,
				"generation": 1,
				"config": {}
			}
		},
		"graphterminator1": {
			"component": "com.sap.util.graphTerminator",
			"metadata": {
				"label": "Graph Terminator",
				"x": 476.9999990463257,
				"y": 40,
				"height": 80,
				"width": 120,
				"generation": 1,
				"config": {}
			}
		}
	},
	"groups": [
		{
			"name": "group1",
			"nodes": [
				"commandexecutor1",
				"python3operator1",
				"toblobconverter1",
				"graphterminator1"
			],
			"metadata": {
				"description": "Group"
			},
			"tags": {
				"oracle_driver": ""
			}
		}
	],
	"connections": [
		{
			"metadata": {
				"points": "148,80 188,80"
			},
			"src": {
				"port": "output",
				"process": "python3operator1"
			},
			"tgt": {
				"port": "ininterface",
				"process": "toblobconverter1"
			}
		},
		{
			"metadata": {
				"points": "247,80 287,80"
			},
			"src": {
				"port": "outbytearray",
				"process": "toblobconverter1"
			},
			"tgt": {
				"port": "stdin",
				"process": "commandexecutor1"
			}
		},
		{
			"metadata": {
				"points": "416,89 443.99999952316284,89 443.99999952316284,80 471.9999990463257,80"
			},
			"src": {
				"port": "stderr",
				"process": "commandexecutor1"
			},
			"tgt": {
				"port": "stop",
				"process": "graphterminator1"
			}
		},
		{
			"metadata": {
				"points": "416,71 443.99999952316284,71 443.99999952316284,80 471.9999990463257,80"
			},
			"src": {
				"port": "stdout",
				"process": "commandexecutor1"
			},
			"tgt": {
				"port": "stop",
				"process": "graphterminator1"
			}
		}
	],
	"inports": {},
	"outports": {},
	"metadata": {
		"generation": 1
	}
}

Step 3: Run the Pipeline and download the Oracle vsolution

Start the Pipeline and wait for completion. It takes 3-5 minutes until the Dockerfile is build.

After the pipeline completed, go to the System Management -> Files explorer and download the oracle_vsolution.zip file by clicking on “Export File”.

Once the download of the oracle_vsolution.tgz file completed, extract the archive so that you have an oracle_vsolution.zip file in the end.

Now you successfully prepared the oracle driver solution and can continue with step 7 in the Data Intelligence: Administration Guide -> ORACLE Connection.