How to Work with PostgreSQL Data in ETL Validator JDBC

ETL Validator provides data movement and transformation capabilities for integrating data platforms across your organization. CData's JDBC driver seamlessly integrates with ETL Validator and extends its native connectivity to include PostgreSQL data.

This tutorial explains how to build a simple ETL validator data flow to extract data from PostgreSQL data and load it into an example data storage solution: SQL Server.

Add a new ETL Validator data source via CData

CData extends ETL Validator's data connectivity capabilities by providing the ability to add data sources that connect via CData's JDBC drivers. Connecting to PostgreSQL data simply requires creating a new data source in ETL Validator through CData's connectiviy suite as described below.

Login to ETL Validator

Begin by logging into ETL Validator to view the application dashboard.

👁 Access the ETL Validator dashboard

Click on Add a DataSource

CData extends the data source options within ETL Validator.

👁 Create a new DataSource

Click on CData

CData's connectivity is embedded within ETL Validator's data source options.

👁 CData data source

Configure the CData Driver Connection String

You will need a JDBC connection string to establish a connection to PostgreSQL in ETL Validator.

👁 CData data source

To connect to PostgreSQL, set the Server, Port (the default port is 5432), and Database connection properties and set the User and Password you wish to use to authenticate to the server. If the Database property is not specified, the data provider connects to the user's default database.

SSH Connectivity for PostgreSQL

You can use SSH (Secure Shell) to authenticate with PostgreSQL, whether the instance is hosted on-premises or in supported cloud environments. SSH authentication ensures that access is encrypted (as compared to direct network connections).

SSH Connections to PostgreSQL in Password Auth Mode

To connect to PostgreSQL via SSH in Password Auth mode, set the following connection properties:

• User: PostgreSQL User name
• Password: PostgreSQL Password
• Database: PostgreSQL database name
• Server: PostgreSQL Server name
• Port: PostgreSQL port number like 3306
• UserSSH: "true"
• SSHAuthMode: "Password"
• SSHPort: SSH Port number
• SSHServer: SSH Server name
• SSHUser: SSH User name
• SSHPassword: SSH Password

SSH Connections to PostgreSQL in Public Key Auth Mode

To connect to PostgreSQL via SSH in Password Auth mode, set the following connection properties:

• User: PostgreSQL User name
• Password: PostgreSQL Password
• Database: PostgreSQL database name
• Server: PostgreSQL Server name
• Port: PostgreSQL port number like 3306
• UserSSH: "true"
• SSHAuthMode: "Public_Key"
• SSHPort: SSH Port number
• SSHServer: SSH Server name
• SSHUser: SSH User name
• SSHClientCret: the path for the public key certificate file

Built-in Connection String Designer

For assistance in constructing the JDBC URL, use the connection string designer built into the PostgreSQL JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.

java -jar cdata.jdbc.postgresql.jar

👁 Using the built-in connection string designer to generate a JDBC URL (postgresql is shown.)

A typical connection string looks like this:

jdbc:postgresql:User=postgres;Password=admin;Database=postgres;Server=127.0.0.1;Port=5432;

Licensing the Driver

To ensure the JDBC driver is licensed appropriately, copy the license file to the appropriate location:

Copy the JDBC Driver for PostgreSQL and lic file from "C:\Program Files\CData[product_name]\lib" to "C:\Datagaps\ETLValidator\Server\apache-tomcat\bin".

 cdata.jdbc.postgresql.jar
 cdata.jdbc.postgresql.lic
 

Note: If you do not copy the .lic file with the jar, you will see a licensing error that indicates you do not have a valid license installed. This is true for both the trial and full versions.

Save the connection

Should you encounter any difficulties loading the CData JDBC driver class, please contact DataGap's team, and they will provide you instructions on how to load the jar file for the relevant driver.

Add SQL Server as a Target

This example will use SQL Server as a destination for PostgreSQL data data, but any preferred destination can be used instead.

Go to DataSources and select MS_SQL_SERVER

This option is the default.

👁 Add SQL Server

Fill in the necessary connection details and test the connection

The details will depend on the specific target, but these details may include a URL, authentiation credentials, etc.

👁 Add SQL Server

Create a Dataflow in ETL Validator

Open the Dataflows tab

Configured data flows will appear in this window.

👁 Dataflows tab

Select Create Dataflow

Name your new dataflow and save it.

Open the Dataflow to view the Dataflow Diagram

The details of the data movement will be configured in this panel.

👁 Dataflow diagram

Drag & drop the JDBC as a source from the right side

Give the new source an appropriate name and save it.

👁 Jira example source

Fill in the Query section of the new source

Select the Table from the Schema option that reflects which data should be pulled from PostgreSQL data.

View the expected results of your query

The anticipated outcome of the configured query is displayed in the Result tab.

👁 Query results

Add the destination to the Dataflow

Select Switch to Diagram, then drag & drop the DB Sink as a target from the right side (under Sink options). Give the sink an appropriate name and save it.

👁 Data sink

Set the appropriate Schema for the destination

Choose the Schema and table that matches the structure of the source table. For this example, the table on the target side was created to match the Source so that data flow seamlessly. More advanced schema transformation operations are beyond the scope of this article.

👁 Destination schema

Hit the RUN option to begin replication

Running the job will take some time.

👁 Destination schema

View the finished Dataflow

Return to the diagram to see the finished data replication job from PostgreSQL data to SQL Server.

👁 Full dataflow

Get Started Today

Download a free, 30-day trial of the CData JDBC Driver for PostgreSQL and start building PostgreSQL-connected applications with ETL Validator. Reach out to our Support Team if you have any questions.