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
CodePudding user response:
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