Batch Mode - Oracle driver

KNIME Analytics Platform
1

Since I upgraded to KNIME 4 version I can’t run workflows accessing Oracle DB anymore. I’d appreciate any help.

My workflows are expected to run on a linux machine without graphic user interface. In previous versions, it was OK to place ojdbc7.jar somewhere and point it from a preferences file mentioned as a batch mode command parameter.

With 4.1.2 my scenario was as follows:

  1. Workflow designed and successfully tested in KNIME IDE on virtual Linux Ubuntu.
  2. Workflow extracted as a knwf file, moved to the target machine, extracted to directory (see here Batch Execution on Unix like systems - File System permission why)
  3. Preferences extracted, copied to the target machine. Clutter removed. See below.
  4. KNIME knime_4.1.2.linux.gtk.x86_64.tar.gz downloaded to the target machine, extracted.
  5. Workflow invoked.

Log file contains:

2020-03-23 15:17:11,145 : INFO  : main :  : NodeLogger :  :  : #########################################################################################
2020-03-23 15:17:11,151 : INFO  : main :  : NodeLogger :  :  : #                                                                                       #
2020-03-23 15:17:11,151 : INFO  : main :  : NodeLogger :  :  : # Welcome to KNIME Analytics Platform v4.1.2.v202003050920 (Build March 05, 2020)       #
2020-03-23 15:17:11,151 : INFO  : main :  : NodeLogger :  :  : # Based on Eclipse, http://www.eclipse.org                                              #
2020-03-23 15:17:11,151 : INFO  : main :  : NodeLogger :  :  : #                                                                                       #
2020-03-23 15:17:11,152 : INFO  : main :  : NodeLogger :  :  : #########################################################################################
2020-03-23 15:17:11,152 : INFO  : main :  : NodeLogger :  :  : #                                                                                       #
2020-03-23 15:17:11,152 : INFO  : main :  : NodeLogger :  :  : # Copyright by KNIME AG, Zurich, Switzerland and others.                                #
2020-03-23 15:17:11,152 : INFO  : main :  : NodeLogger :  :  : # Website: http://www.knime.com                                                         #
2020-03-23 15:17:11,152 : INFO  : main :  : NodeLogger :  :  : # E-mail: contact@knime.com                                                             #
2020-03-23 15:17:11,153 : INFO  : main :  : NodeLogger :  :  : #                                                                                       #
2020-03-23 15:17:11,153 : INFO  : main :  : NodeLogger :  :  : #########################################################################################
2020-03-23 15:17:11,153 : INFO  : main :  : NodeLogger :  :  : # For more details see the KNIME log file:                                              #
2020-03-23 15:17:11,153 : INFO  : main :  : NodeLogger :  :  : # /home/ders/knime-workspace/.metadata/knime/knime.log
2020-03-23 15:17:11,153 : INFO  : main :  : NodeLogger :  :  : #---------------------------------------------------------------------------------------#
2020-03-23 15:17:11,163 : INFO  : main :  : NodeLogger :  :  : # logging date=Mon Mar 23 15:17:11 CET 2020
2020-03-23 15:17:11,163 : INFO  : main :  : NodeLogger :  :  : # java.version=1.8.0_202
2020-03-23 15:17:11,163 : INFO  : main :  : NodeLogger :  :  : # java.vm.version=25.202-b08
2020-03-23 15:17:11,164 : INFO  : main :  : NodeLogger :  :  : # java.vendor=AdoptOpenJdk
2020-03-23 15:17:11,164 : INFO  : main :  : NodeLogger :  :  : # os.name=Linux
2020-03-23 15:17:11,164 : INFO  : main :  : NodeLogger :  :  : # os.arch=amd64
2020-03-23 15:17:11,165 : INFO  : main :  : NodeLogger :  :  : # number of CPUs=2
2020-03-23 15:17:11,165 : INFO  : main :  : NodeLogger :  :  : # assertions=off
2020-03-23 15:17:11,172 : INFO  : main :  : NodeLogger :  :  : # host=phaeton.mnd.ders.cz
2020-03-23 15:17:11,176 : INFO  : main :  : NodeLogger :  :  : # username=ders
2020-03-23 15:17:11,176 : INFO  : main :  : NodeLogger :  :  : # max mem=3641MB
2020-03-23 15:17:11,184 : INFO  : main :  : NodeLogger :  :  : # application=org.knime.product.KNIME_APPLICATION
2020-03-23 15:17:11,194 : INFO  : main :  : NodeLogger :  :  : # ID=01-a100318def0d5ba6
2020-03-23 15:17:11,194 : INFO  : main :  : NodeLogger :  :  : #########################################################################################
2020-03-23 15:17:11,255 : INFO  : main :  : StringHistory :  :  : History file '/home/ders/knime-workspace/.metadata/knime/history_database_drivers.txt' does not exist.
2020-03-23 15:17:11,256 : INFO  : main :  : StringHistory :  :  : History file '/home/ders/knime-workspace/.metadata/knime/history_database_urls.txt' does not exist.
2020-03-23 15:17:11,257 : DEBUG : main :  : DatabaseConnectionSettings :  :  : Settings database timeout to 15 seconds
2020-03-23 15:17:11,362 : DEBUG : main :  : DatabaseConnectionSettings :  :  : Database concurrency (sync via database connection) is true.
2020-03-23 15:17:11,371 : DEBUG : main :  : KNIMECorePlugin :  :  : Ignoring thread count from preference page (4), since it has set by java property "org.knime.core.maxThreads" (2)
2020-03-23 15:17:11,371 : DEBUG : main :  : KNIMECorePlugin :  :  : Setting KNIME temp dir to "/tmp"
2020-03-23 15:17:33,607 : WARN  : main :  : ServerDBConnectorSettings : Oracle Connector : 0:2 : Due to restrictive licence terms we can not distribute the Oracle driver with the KNIME Analytics Platform.
 You can download the latest driver at http://www.oracle.com/technetwork/database/application-development/jdbc/downloads/index.html
2020-03-23 15:17:33,608 : WARN  : main :  : UnauthenticatedServerDBConnectorSettings : Oracle Connector : 0:2 : To register the driver go to File->Preferences. In the preferences go to KNIME->Databases and register a new driver for the database type oracle by clicking on the Add button.
2020-03-23 15:17:33,608 : WARN  : main :  : Node : Oracle Connector : 0:2 : Please register a compatible driver via the KNIME preference page. (For details see the log file)
2020-03-23 15:17:33,614 : WARN  : main :  : Node : MISSING Empty Table Creator : 0:4 : Node can't be executed - Node "Empty Table Creator" not available from extension "KNIME Data Generation" (provided by "KNIME AG, Zurich, Switzerland"; plugin "org.knime.datageneration" is not installed)
2020-03-23 15:17:34,840 : WARN  : main :  : ServerDBConnectorSettings : Oracle Connector : 0:2 : Due to restrictive licence terms we can not distribute the Oracle driver with the KNIME Analytics Platform.
 You can download the latest driver at http://www.oracle.com/technetwork/database/application-development/jdbc/downloads/index.html
2020-03-23 15:17:34,841 : WARN  : main :  : UnauthenticatedServerDBConnectorSettings : Oracle Connector : 0:2 : To register the driver go to File->Preferences. In the preferences go to KNIME->Databases and register a new driver for the database type oracle by clicking on the Add button.
2020-03-23 15:17:34,841 : WARN  : main :  : Node : Oracle Connector : 0:2 : Please register a compatible driver via the KNIME preference page. (For details see the log file)
2020-03-23 15:17:34,842 : WARN  : main :  : Node : MISSING Empty Table Creator : 0:4 : Node can't be executed - Node "Empty Table Creator" not available from extension "KNIME Data Generation" (provided by "KNIME AG, Zurich, Switzerland"; plugin "org.knime.datageneration" is not installed)
2020-03-23 15:17:34,855 : WARN  : main :  : ServerDBConnectorSettings : Oracle Connector : 0:2 : Due to restrictive licence terms we can not distribute the Oracle driver with the KNIME Analytics Platform.
 You can download the latest driver at http://www.oracle.com/technetwork/database/application-development/jdbc/downloads/index.html
2020-03-23 15:17:34,855 : WARN  : main :  : UnauthenticatedServerDBConnectorSettings : Oracle Connector : 0:2 : To register the driver go to File->Preferences. In the preferences go to KNIME->Databases and register a new driver for the database type oracle by clicking on the Add button.
2020-03-23 15:17:34,856 : WARN  : main :  : Node : Oracle Connector : 0:2 : Please register a compatible driver via the KNIME preference page. (For details see the log file)
2020-03-23 15:17:34,857 : WARN  : main :  : Node : MISSING Empty Table Creator : 0:4 : Node can't be executed - Node "Empty Table Creator" not available from extension "KNIME Data Generation" (provided by "KNIME AG, Zurich, Switzerland"; plugin "org.knime.datageneration" is not installed)
2020-03-23 15:17:35,550 : WARN  : main :  : WorkflowManager :  :  : Workflow contains nodes with duplicate parameter name (will be made unique by appending node IDs): "string-input", "sequence_name"

A shell script invoking the workflow:

#!/bin/bash

workflow_name=test_workflow_01
run_identifier=knime_wf_${workflow_mame}_run_$(date +%Y-%m-%d-%H-%M-%S)

current_dir="$PWD"
nohup /opt/ders/knime  \
	-nosplash \
	-application org.knime.product.KNIME_BATCH_APPLICATION \
	-workflowDir="/home/ders/sify_int/${workflow_name}" \
	-workflow.variable=delay_in_sec,3,int \
	-workflow.variable=correlation_id,correlation_id_not_set,String \
	-workflow.variable=job_name,"unnamed_job",String \
        -credential="sify_new_vavdev;*****;****" \
	-reset \
	-preferences=/home/ders/phaeton.epf \
	-consoleLog \
	-destDir=$current_dir/out/${run_identifier}-out/ \
	-vmargs -Dorg.knime.core.maxThreads=2 -Xmx4g > ${run_identifier}.log 2>&1 &

Username, password replaced with asterisks.[test_workflow_01.knwf|attachment]

File /opt/ders/knime is a symbolic link to `knime_4.1.2/knime.

Preferences file looks as follows:

[ders@phaeton.mnd.ders.cz ~]# cat /home/ders/phaeton.epf 
#Thu Mar 19 15:19:17 CET 2020
\!/=
/instance/org.knime.database/drivers/OracleSN/database_type=oracle
/instance/org.knime.database/drivers/OracleSN/description=
/instance/org.knime.database/drivers/OracleSN/driver_class=oracle.jdbc.OracleDriver
/instance/org.knime.database/drivers/OracleSN/name=Oracle Service Name
/instance/org.knime.database/drivers/OracleSN/paths/0=/home/ders/ojdbc7.jar
/instance/org.knime.database/drivers/OracleSN/url_template=jdbc\:oracle\:thin\:@<host>\:<port>/<database>
/instance/org.knime.database/drivers/OracleSN/version=12.1.0
/instance/org.knime.database/drivers/odbc7/database_type=oracle
/instance/org.knime.database/drivers/odbc7/description=
/instance/org.knime.database/drivers/odbc7/driver_class=oracle.jdbc.OracleDriver
/instance/org.knime.database/drivers/odbc7/name=Oracle SID
/instance/org.knime.database/drivers/odbc7/paths/0=/home/ders/ojdbc7.jar
/instance/org.knime.database/drivers/odbc7/url_template=jdbc\:oracle\:thin\:@<host>\:<port>\:<database>
/instance/org.knime.database/drivers/odbc7/version=12.1.0

test_workflow_01.knwf (151.4 KB)

ojdbc7.jar is where it is expected:

[ders@phaeton.mnd.ders.cz ~]# ls -l /home/ders/ojdbc7.jar
-rwxr-xr-x. 1 ders ders 3397734 Jan 21 14:39 /home/ders/ojdbc7.jar
[ders@phaeton.mnd.ders.cz ~]# file /home/ders/ojdbc7.jar
/home/ders/ojdbc7.jar: Zip archive data, at least v1.0 to extract
[ders@phaeton.mnd.ders.cz ~]#
1 Like
2

It seems to me exporting / importing emf is not enough anymore to register Oracle driver in target KNIME instance.

3

Finally I solved it by adding DB driver parameters to KNIME configuration.

  1. I exported epf configuration from source KNIME instance.
  2. I filtered lines of the epf file related to my driver. In my case it were lines starting with /instance/org.knime.database/drivers/odbc7 since ID of my DB driver was “odbc7”
  3. I stripped leading /instance/org.knime.database/ from the lines so they started with drivers/odbc7
  4. I updated path to ojdbc7.jar file the configuration referenced. It’s obvious the jar must exist in the target environment.
  5. I created file org.knime.database.prefs within knime-workspace/.metadata/.plugins/org.eclipse.core.runtime/.settings The knime-workspace locates in user’s home directory. KNIME creates the directory when it invokes a workflow in batch mode.

It is my understanding KNIME failed processing the epf file it was given using -preferences parameters.

1 Like
4

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.