how to connect from oracle origin with streamsets

Question

i want to create an origin source from oracle. so I choos as origin oracle cdc. then I configured each parameter:

Schema Name
Table
Username
Password
JDBC Connection String

but when I run the process, i find into my log:

2017-08-22 11:07:22,447 test/testb156f588-dbd7-4e4c-8896-caf658d14d77   ERROR   Error while connecting to DB

com.streamsets.pipeline.api.StageException: JDBC_06 - Failed to initialize connection pool: java.lang.RuntimeException: Unable to get driver instance for jdbcUrl=jdbc:oracle:thin:@(DESCRIPTION =
(ENABLE=BROKEN)
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = myport))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.WORLD)))
    at com.streamsets.pipeline.lib.jdbc.JdbcUtil.createDataSourceForRead(JdbcUtil.java:638)
    at com.streamsets.pipeline.stage.origin.jdbc.cdc.oracle.OracleCDCSource.init(OracleCDCSource.java:643)
    at com.streamsets.pipeline.api.base.BaseStage.init(BaseStage.java:52)
    at com.streamsets.pipeline.configurablestage.DStage.init(DStage.java:40)
    at com.streamsets.datacollector.runner.StageRuntime.init(StageRuntime.java:156)
    at com.streamsets.datacollector.runner.StagePipe.init(StagePipe.java:105)
    at com.streamsets.datacollector.runner.StagePipe.init(StagePipe.java:53)
    at com.streamsets.datacollector.runner.Pipeline.initPipe(Pipeline.java:299)
    at com.streamsets.datacollector.runner.Pipeline.init(Pipeline.java:214)
    at com.streamsets.datacollector.execution.runner.common.ProductionPipeline.run(ProductionPipeline.java:96)
    at com.streamsets.datacollector.execution.runner.common.ProductionPipelineRunnable.run(ProductionPipelineRunnable.java:79)
    at com.streamsets.datacollector.execution.runner.standalone.StandaloneRunner.start(StandaloneRunner.java:646)
    at com.streamsets.datacollector.execution.runner.common.AsyncRunner.lambda$start$3(AsyncRunner.java:143)
    at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeCallable.call(SafeScheduledExecutorService.java:233)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.RuntimeException: Unable to get driver instance for jdbcUrl=jdbc:oracle:thin:@(DESCRIPTION =
(ENABLE=BROKEN)
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = myport))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.WORLD)))
    at com.zaxxer.hikari.util.DriverDataSource.<init>(DriverDataSource.java:88)
    at com.zaxxer.hikari.pool.PoolElf.initializeDataSource(PoolElf.java:157)
    at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:113)
    at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:73)
    at com.streamsets.pipeline.lib.jdbc.JdbcUtil.createDataSourceForRead(JdbcUtil.java:630)
    ... 19 more
Caused by: java.sql.SQLException: No suitable driver
    at java.sql.DriverManager.getDriver(DriverManager.java:315)
    at com.zaxxer.hikari.util.DriverDataSource.<init>(DriverDataSource.java:81)
    ... 23 more

Do you have any idea?

score 1 · Accepted Answer · answered Sep 11 '17 at 14:11

1

I had to add the ojdbc jar under the repository

/opt/streamsets-extra/streamsets-datacollector-jdbc-lib/lib

answered Sep 11 '17 at 14:11

a.moussa

2,977
7
34
56

since i m running on docker , how would it be cfg ? – Tiago Medici Nov 01 '20 at 21:16

Barbaros Özhan · Answer 2 · 2020-05-19T17:32:11.603

Seems that it's missing to set environment variable STREAMSETS_LIBRARIES_EXTRA_DIR which's a part of installing external library installation process.

There are three types of installation depending on initialization style of Data Collector :

Manually start
Using SysV Init ( supported by CentOS 6, Oracle Linux 6, Red Hat Enterprise Linux 6 or Ubuntu 14.04 LTS )
Using Systemd Init ( supported by CentOS 7, Oracle Linux 7, Red Hat Enterprise Linux 7 or Ubuntu 16.04 LTS )

If you're installing SDC

being started manually, then variable STREAMSETS_LIBRARIES_EXTRA_DIR is expected to be set from the command line by

export STREAMSETS_LIBRARIES_EXTRA_DIR="/opt/streamsets-data-collector/streamsets-data-collector-3.15.0/streamsets-libs-extras/"

starting as a service, then this parameter already would already exist within the $SDC_DIST/libexec/_sdc file as
```
STREAMSETS_LIBRARIES_EXTRA_DIR="${STREAMSETS_LIBRARIES_EXTRA_DIR:=${SDC_DIST}/streamsets-libs-extras}" 
```

where $SDC_DIST variable is the extraction directory for SDC installation file(tarball or RPM).

and the same path should be added to the file $SDC_CONF/sdc-security.policy such as

grant codebase "file:///opt/streamsets-data-collector/streamsets-data-collector-3.15.0/streamsets-libs-extras/-" {
  permission java.security.AllPermission;
};

where $SDC_CONF variable is typically defined by the path /etc/sdc

Now, we're ready to log in Data Collector Console in order to add JDBC External library following these steps :

In Data Collector, in the top right toolbar, click the Package Manager icon:
In the navigation panel, click External Libraries :

Data Collector lists any currently installed external libraries.

Immediately under the top right toolbar, click the Install External Libraries icon:

In the Install External Libraries dialog box, select the stage library JDBC from the file system (assuming you were already registered and downloaded streamsets-datacollector-jdbc-lib);

and the choose the .jar file such as ojdbc8.jar which can be downloaded from JDBC and UCP Downloads page ( in my case I've chosen the link named Oracle Database 12c Release 2 (12.2.0.1) drivers due to my remote DB version ).

As the last step, Don't forget to click Cancel in the Install External Libraries window, and then run the following command :
```
service sdc restart ( for SysV Init)
```
or
```
systemctl restart sdc ( for Systemd Init )
```

( You can click Restart Data Collector in the Install External Libraries window provided that you started the Data Collector manually from the command line. )

score 0 · Answer 3 · answered Aug 24 '17 at 18:24

0

Under the Legacy configuration tab try specifying the JDBC Driver Class name as oracle.jdbc.driver.OracleDriver

answered Aug 24 '17 at 18:24

ramblingpolak

632
5
4

how to connect from oracle origin with streamsets

3 Answers3