Connecting to Netezza using Kafka-JDBC-Sink-Connector

[J bhatt]

Hi ,

I am trying to connect to Netezza using the JDBC sink connector and seeing this exception.

I already have the driver on the classpath .

Here is my sink-jdbc-netezza.properties file 

# A simple example that copies from a topic to a SQLite database.

# The first few settings are required for all connectors:

# a name, the connector class to run, and the maximum number of tasks to create:

name=test-netezza

connector.class=io.confluent.connect.jdbc.JdbcSinkConnector

tasks.max=1

# The topics to consume from - required for sink connectors like this one

topics=ordersTopic

jdbc.sink.error.policy=NOOP

connect.jdbc.sink.export.mappings=INSERT INTO ADMIN.orders_ka select * from ordersTopic  AUTOCREATE AUTOEVOLVE PK id

# We want to connect to a Netezza database.

connection.url=jdbc:netezza08://XXXX/OPI_STG?user=xxx&password=xxxx

connection.user=xxx

connection.password=xxx

connection.database=OPI_STG

driver.class=org.netezza.Driver

table.name.format=orders_ka

auto.create=true

Can you please help me ?

Thanks

2017-05-19 17:17:03,711] INFO Initializing writer using SQL dialect: GenericDialect (io.confluent.connect.jdbc.sink.JdbcSinkTask:52)

[2017-05-19 17:17:03,712] INFO Sink task WorkerSinkTask{id=test-netezza-0} finished initialization and start (org.apache.kafka.connect.runtime.WorkerSinkTask:232)

[2017-05-19 17:17:03,838] INFO Discovered coordinator localhost:9093 (id: 2147483647 rack: null) for group connect-test-netezza. (org.apache.kafka.clients.consumer.internals.AbstractCoordinator:586)

[2017-05-19 17:17:03,841] INFO Revoking previously assigned partitions [] for group connect-test-netezza (org.apache.kafka.clients.consumer.internals.ConsumerCoordinator:397)

[2017-05-19 17:17:03,841] INFO (Re-)joining group connect-test-netezza (org.apache.kafka.clients.consumer.internals.AbstractCoordinator:420)

[2017-05-19 17:17:03,855] INFO Successfully joined group connect-test-netezza with generation 1 (org.apache.kafka.clients.consumer.internals.AbstractCoordinator:388)

[2017-05-19 17:17:03,857] INFO Setting newly assigned partitions [ordersTopic-0] for group connect-test-netezza (org.apache.kafka.clients.consumer.internals.ConsumerCoordinator:256)

[2017-05-19 17:17:04,173] ERROR Task test-netezza-0 threw an uncaught and unrecoverable exception (org.apache.kafka.connect.runtime.WorkerSinkTask:449)

org.apache.kafka.connect.errors.ConnectException: java.sql.SQLException: No suitable driver found for jdbc:netezza08://xxx:5480/DBName?user=xxx&password=xxx

at io.confluent.connect.jdbc.util.CachedConnectionProvider.getValidConnection(CachedConnectionProvider.java:59)

at io.confluent.connect.jdbc.sink.JdbcDbWriter.write(JdbcDbWriter.java:52)

at io.confluent.connect.jdbc.sink.JdbcSinkTask.put(JdbcSinkTask.java:66)

at org.apache.kafka.connect.runtime.WorkerSinkTask.deliverMessages(WorkerSinkTask.java:429)

at org.apache.kafka.connect.runtime.WorkerSinkTask.poll(WorkerSinkTask.java:250)

at org.apache.kafka.connect.runtime.WorkerSinkTask.iteration(WorkerSinkTask.java:179)

at org.apache.kafka.connect.runtime.WorkerSinkTask.execute(WorkerSinkTask.java:148)

at org.apache.kafka.connect.runtime.WorkerTask.doRun(WorkerTask.java:139)

at org.apache.kafka.connect.runtime.WorkerTask.run(WorkerTask.java:182)

at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)

at java.util.concurrent.FutureTask.run(FutureTask.java:266)

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.sql.SQLException: No suitable driver found for jdbc:netezza08://10.205.85.158:5480/OPI_STG?user=aalntzadm&password=Kafka9Nifi

at java.sql.DriverManager.getConnection(DriverManager.java:689)

at java.sql.DriverManager.getConnection(DriverManager.java:247)

at io.confluent.connect.jdbc.util.CachedConnectionProvider.newConnection(CachedConnectionProvider.java:66)

at io.confluent.connect.jdbc.util.CachedConnectionProvider.getValidConnection(CachedConnectionProvider.java:52)

... 13 more

[Gwen Shapira]

Connect definitely can't find the driver (The error says exactly that), can you show the classpath that Connect is using with the driver in it?

Gwen

--
You received this message because you are subscribed to the Google Groups "Confluent Platform" group.
To unsubscribe from this group and stop receiving emails from it, send an email to confluent-platf...@googlegroups.com.
To post to this group, send email to confluent...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/confluent-platform/c9783ca0-9fd3-4939-a11d-b92b6e6c2b32%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[J bhatt]

Here is the classpath 

$ echo $CLASSPATH

:/nz/lib/nzjdbc3.jar

[J bhatt]

I also tried this 

export CLASSPATH=/nz/lib/nzjdbc3.jar

./bin/connect-standalone etc/kafka/connect-jdbc-standalone.properties /app/confluent/etc/kafka-connect-jdbc/sink-jdbc-netezza.properties

Please let me know what I am missing 

Thanks !

On Friday, May 19, 2017 at 6:27:45 PM UTC-4, J bhatt wrote:

[Robin Moffatt]

Hi, 

The driver that connect is going to look for will be derived from the JDBC url -- in this case you've got netezza08. I'm not familar with Netezza myself, but a quick google suggests this should be just netezza, i.e. : 

connection.url=jdbc:netezza://10.205.85.158:5480/OPI_STG?user=xxxx&password=yyyyy

Can you try this, and see if it works? 

BTW you exposed your password in your original log paste...

--

You received this message because you are subscribed to the Google Groups "Confluent Platform" group.

To unsubscribe from this group and stop receiving emails from it, send an email to confluent-platform+unsub...@googlegroups.com.
To post to this group, send email to confluent-platform@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/confluent-platform/45856a56-dbc3-4af1-b3c2-eea91943f286%40googlegroups.com.

[J bhatt]

I tried what you suggested . Still same error 

[2017-05-23 08:32:01,891] INFO (Re-)joining group connect-test-netezza (org.apache.kafka.clients.consumer.internals.AbstractCoordinator:420)

[2017-05-23 08:32:01,907] INFO Successfully joined group connect-test-netezza with generation 1 (org.apache.kafka.clients.consumer.internals.AbstractCoordinator:388)

[2017-05-23 08:32:01,909] INFO Setting newly assigned partitions [ordersTopic-0] for group connect-test-netezza (org.apache.kafka.clients.consumer.internals.ConsumerCoordinator:256)

[2017-05-23 08:32:02,246] ERROR Task test-netezza-0 threw an uncaught and unrecoverable exception (org.apache.kafka.connect.runtime.WorkerSinkTask:449)

org.apache.kafka.connect.errors.ConnectException: java.sql.SQLException: No suitable driver found for jdbc:netezza://10.205.85.158:5480/OPI_STG?user=xxx&password=yyy

at io.confluent.connect.jdbc.util.CachedConnectionProvider.getValidConnection(CachedConnectionProvider.java:59)

at io.confluent.connect.jdbc.sink.JdbcDbWriter.write(JdbcDbWriter.java:52)

at io.confluent.connect.jdbc.sink.JdbcSinkTask.put(JdbcSinkTask.java:66)

at org.apache.kafka.connect.runtime.WorkerSinkTask.deliverMessages(WorkerSinkTask.java:429)

at org.apache.kafka.connect.runtime.WorkerSinkTask.poll(WorkerSinkTask.java:250)

at org.apache.kafka.connect.runtime.WorkerSinkTask.iteration(WorkerSinkTask.java:179)

at org.apache.kafka.connect.runtime.WorkerSinkTask.execute(WorkerSinkTask.java:148)

at org.apache.kafka.connect.runtime.WorkerTask.doRun(WorkerTask.java:139)

at org.apache.kafka.connect.runtime.WorkerTask.run(WorkerTask.java:182)

at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)

at java.util.concurrent.FutureTask.run(FutureTask.java:266)

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)

On Friday, May 19, 2017 at 6:27:45 PM UTC-4, J bhatt wrote:

Caused by: java.sql.SQLException: No suitable driver found for jdbc:netezza08://10.205.85.158:5480/OPI_STG?user=xxx&password=yyy

[潘义文]

I just resolve this problem,you should add mysql jdbc driver ,download url: https://mvnrepository.com/artifact/mysql/mysql-connector-java

在 2017年5月20日星期六 UTC+8上午6:27:45，J bhatt写道：

[J bhatt]

I am trying to connect to Netezza , not mysql .

Could it be the kafka-jdbc-connector does not  support Netezza ?

Gwen - Could you please confirm ?

Thanks!

On Friday, May 19, 2017 at 6:27:45 PM UTC-4, J bhatt wrote:

[J bhatt]

I was able to connect to Mysql using the similar configuration , and the driver was found and works fine . 

I still get a "No Suitable Driver" error , when i try with Netezza . If its incompatible with the jdbc connector - why doesnt error say so ?

On Friday, May 19, 2017 at 6:27:45 PM UTC-4, J bhatt wrote:

[Gwen Shapira]

JDBC connector should work with Netezza as long as JDBC can find the right driver.

--

You received this message because you are subscribed to the Google Groups "Confluent Platform" group.

To unsubscribe from this group and stop receiving emails from it, send an email to confluent-platf...@googlegroups.com.
To post to this group, send email to confluent...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/confluent-platform/23072578-6b10-404b-b7f5-e204f3608abf%40googlegroups.com.

[J bhatt]

Thanks for the Reply Gwen !.

 I have placed the driver in this location

/app/confluent/share/java/kafka-connect-jdbc

# ls

kafka-connect-jdbc-3.2.1.jar  mysql-connector-java-5.1.42-bin.jar  nzjdbc3.jar  postgresql-9.4-1206-jdbc41.jar  sqlite-jdbc-3.8.11.2.jar

The msql connection works perfectly fine and can find the driver . 

Also - I use the same jar to connect from Nifi to Netezza ( so the driver ) is ok . 

I was using a avro producer to send data to the topic , i dont see connection error until there is data to be written . Is the JDBC connection established only when there is some action to be taken ?

What else do you want me to check ?

Thanks for your help 

To unsubscribe from this group and stop receiving emails from it, send an email to confluent-platform+unsub...@googlegroups.com.

[Shankar Menon]

Bhatt

Modern JDBC driver packages includes the class name of the Driver in the jar file under META-INF/services/java.sql.Driver file. DriverManager class scans for this file for every jar file in the classpath, registering each one by one and attempts to connect.

Neither nzjdbc.jar nor nzjdbc3.jar has mentioned what their default Driver class is, hence the DriverManager is unable to load the Driver.

Do the following,

1. jar xvf nzjdbc.jar

2. mkdir META-INF/services

3. echo "org.netezza.Driver" | tee META-INF/services/java.sql.Driver

4. jar uf nzjdbc.jar META-INF/services/java.sql.Driver

5. Add the updated nzjdbc.jar to the classpath.

[Eva Khalil]

All,

Has this been resolved ?

Regards,

Eva

[Piyush Muthal]

Hi, 

I am facing the same issue with Netezza connection.

I have tried the latest approach to add the java.sql.Driver file in jar but it did not work.

Thanks,

Piyush

[Eva Khalil]

I have successfully connected to netezza and pumped data into it by simply following instructions on netezza connection specs (IBM Knowledge Center)

--

You received this message because you are subscribed to the Google Groups "Confluent Platform" group.

To unsubscribe from this group and stop receiving emails from it, send an email to confluent-platf...@googlegroups.com.

To post to this group, send email to confluent...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/confluent-platform/ac0e4931-3b61-4766-b073-7afd296c9d8c%40googlegroups.com.

[Subbarayudu]

Bhatt,

Could you please let me know the query syntax to fetch table details on NIFI to fetch from Netezza.