Search code examples
javasslapache-nifitimescaledb

Error when trying to connect to Timescale Cloud with SSL by using Apache Ni-Fi

I have created a data pipeline with Apache Ni-Fi (version 1.15.2) to transfer data from a MySQL database in a AWS RDS instance into Timescale (postgreSQL). Ni-Fi runs in Linux Ubuntu 18.04 in Virtual Box on top of Windows 10. The pipeline has been tested successfully by using as a target the Timescale database located in my local Linux instance, without using SSL.

Then I have modified the pipeline to use Timescale Cloud as a target (https://portal.timescale.cloud/login). The database name is: periodic-measurements

In Timescale Cloud service page I can see these credentials:

  • Host: <host-string>
  • Port: 10250
  • User: tsdbadmin
  • Password: <timescale-cloud-service-password>
  • Service URI: postgres://tsdbadmin:<timescale-cloud-service-password>@<host-string>:10250/defaultdb?sslmode=require
  • SSL mode: require
  • Allowed IP addresses: 0.0.0.0/0

I use PutDatabaseRecord processor to write data in the postgreSQL database which uses the DBCPConnectionPool controller service.

I have copied the CA certificate got from Timescale Cloud service page into the following directory in my Linux instance with the following file name:

/etc/ca-certificates/ts-cloud-ca.pem

These are the configuration parameters in the the DBCPConnectionPool controller service:

• Database Connection URL: jdbc:postgres://<host-string>:10250/periodic-measurements

• Database Driver Class Name: org.postgresql.Driver

• Database Driver Location: /usr/share/java/postgresql/postgresql-42.3.1.jar

• Database User: tsdbadmin

• Password: <timescale-cloud-service-password>

• sslmode: require

• sslrootcert: /etc/ca-certificates/ts-cloud-ca.pem

When enabling the controller service I get the following error:

StandardControllerServiceNode[service=DBCPConnectionPool[id=017e1005-818e-1423-7951-125faf9dff4d], name=Connector to Timescale Cloud, active=true] Failed to invoke @OnEnabled method due to java.sql.SQLException: No suitable driver

causes: org.apache.nifi.processor.exception.ProcessException: No suitable driver for the given Database Connection URL: No suitable driver for the given Database Connection URL

I get the same result if I change the DBCPConnectionPool controller service settings with sslmode: verify-ca

Note that I have managed to connect successfully to the Timescale Cloud database with Grafana from my Linux instance by using the same credentials.

Any hint what could be wrong?

Thanks,

Bernardo

Solution

  • The error was in the configuration parameters in the the DBCPConnectionPool controller service in the Database Connection URL that was starting with jdbc:postgres rather than with jdbc:postgresql

    Correct configuration:

    • Database Connection URL: jdbc:postgresql://:10250/periodic-measurements