How to Work with HubDB 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 HubDB data.

This tutorial walks through the process of building a simple ETL validator data flow to extract data from HubDB 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 HubDB 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.

Click on Add a DataSource

CData extends the data source options within ETL Validator.

Click on CData

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

Configure the CData Driver Connection String

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

There are two authentication methods available for connecting to HubDB data source: OAuth Authentication with a public HubSpot application and authentication with a Private application token.

Using a Custom OAuth App

AuthScheme must be set to "OAuth" in all OAuth flows. Be sure to review the Help documentation for the required connection properties for you specific authentication needs (desktop applications, web applications, and headless machines).

Follow the steps below to register an application and obtain the OAuth client credentials:

1. Log into your HubSpot app developer account.
   • Note that it must be an app developer account. Standard HubSpot accounts cannot create public apps.
2. On the developer account home page, click the Apps tab.
3. Click Create app.
4. On the App info tab, enter and optionally modify values that are displayed to users when they connect. These values include the public application name, application logo, and a description of the application.
5. On the Auth tab, supply a callback URL in the "Redirect URLs" box.
   • If you're creating a desktop application, set this to a locally accessible URL like http://localhost:33333.
   • If you are creating a Web application, set this to a trusted URL where you want users to be redirected to when they authorize your application.
6. Click Create App. HubSpot then generates the application, along with its associated credentials.
7. On the Auth tab, note the Client ID and Client secret. You will use these later to configure the driver.

8. Under Scopes, select any scopes you need for your application's intended functionality.

   A minimum of the following scopes is required to access tables:

   • hubdb
   • oauth
   • crm.objects.owners.read
9. Click Save changes.
10. Install the application into a production portal with access to the features that are required by the integration.
    • Under "Install URL (OAuth)", click Copy full URL to copy the installation URL for your application.
    • Navigate to the copied link in your browser. Select a standard account in which to install the application.
    • Click Connect app. You can close the resulting tab.

Using a Private App

To connect using a HubSpot private application token, set the AuthScheme property to "PrivateApp."

You can generate a private application token by following the steps below:

1. In your HubDB account, click the settings icon (the gear) in the main navigation bar.
2. In the left sidebar menu, navigate to Integrations > Private Apps.
3. Click Create private app.
4. On the Basic Info tab, configure the details of your application (name, logo, and description).
5. On the Scopes tab, select Read or Write for each scope you want your private application to be able to access.
6. A minimum of hubdb and crm.objects.owners.read is required to access tables.
7. After you are done configuring your application, click Create app in the top right.
8. Review the info about your application's access token, click Continue creating, and then Show token.
9. Click Copy to copy the private application token.

To connect, set PrivateAppToken to the private application token you retrieved.

Built-in Connection String Designer

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

A typical connection string looks like this:

jdbc:hubdb:AuthScheme=OAuth;OAuthClientID=MyOAuthClientID;OAuthClientSecret=MyOAuthClientSecret;CallbackURL=http://localhost:33333;InitiateOAuth=GETANDREFRESH

Licensing the Driver

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

Copy the JDBC Driver for HubDB and lic file from "C:\Program Files\CData[product_name]\lib" to "C:\Datagaps\ETLValidator\Server\apache-tomcat\bin". cdata.jdbc.hubdb.jar cdata.jdbc.hubdb.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 HubDB data data, but any preferred destination can be used instead.

Go to DataSources and select MS_SQL_SERVER

This option is the default.

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.

Create a Dataflow in ETL Validator

Open the Dataflows tab

Configured data flows will appear in this window.

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.

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

Give the new source an appropriate name and save it.

Fill in the Query section of the new source

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

View the expected results of your query

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

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.

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.

Hit the RUN option to begin replication

Running the job will take some time.

View the finished Dataflow

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

Get Started Today

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