Connect to an On-Premises Teradata database via REST, ODBC and JDBC from Cloud

In this tutorial, let’s see how you can connect to your On-premises Teradata using Hybrid Data Pipeline’s new third party JDBC feature. If you are new to Progress, Hybrid Data Pipeline (HDP) is our self-hostable hybrid connectivity solution that you can run in the cloud or on-premises. With HDP, you can access data in the cloud or on-premises behind a firewall. Connect through a standard interface—SQL (ODBC, JDBC) or REST (OData 2, OData 4).

This tutorial will require you to set up two components of Hybrid Data Pipeline for you to be able to access your On-premises Teradata. The first one is the Hybrid Data Pipeline Server, which you can host in any cloud service you want and the next one is Progress On-Premises connector, which you must install on one of the On-Premises machine, which is in the same network as of the Teradata server. This On-premises connector helps the Hybrid Data Pipeline server to connect to Teradata database and provides you with ODBC, JDBC or REST (OData 2, OData 4). Let’ see how you can do this.

1. Download Progress Hybrid Data Pipeline and Progress On-premises connector by visiting this page.
2. Install Progress Hybrid Data Pipeline by following the below tutorials. Note that these are in generally applicable to any CentOS/RHEL machine, each of them varies a bit on specific cloud service settings.
   1. Azure
   2. AWS
   3. Google Cloud
3. After you have completed the installation, copy the re-distributable files from the below path to your on-premises machine, where you intend to install Progress DataDirect On-premises connector.
   
   /path/to/Progress/DataDirect/Hybrid_Data_Pipeline/Hybrid_Server/redist/
   
   

Important: Before you begin installing the On-premises connector on your On-premise machine, you would have to configure firewall for Hybrid Data Pipeline running in the cloud to allow traffic on 11235,11280,40501.

1. Start the On-premises connector installer. Click next on the Introduction window.

   
   
   

2. On the next screen you would be asked to choose installation directory. You can either keep it as a default or choose your own location.

   
   
   

3. On the next screen, choose standard installation if you don’t need any customization.

   
   
   

4. On the next screen, you would have to provide the credentials that you created while installing the Hybrid Data Pipeline and a unique connector label and click on Next. Remember this Connector Label, as you will be using it later.

   
   
   
   

5. Your credentials will be verified at this point and if it’s success authenticated, you will be shown Pre-installation summary. Click on Install to start the installation.

   
   
   

6. Click on Done, after the installation is complete to close the installer. You should now see a Configuration Tool as shown below
   
   
   

7. Go to Status tab, click on Test to make sure everything is properly configured, and you should see green for every service.

    
   
   

8. Close the configuration tool.

1. To enable connectivity to Teradata database which is on your premise, you need to download Teradata JDBC drivers and make sure that the drivers are available to Progress On-premise connector for the server to be able to connect to Teradata.
2. Copy tdgssconfig.jar and terajdbc4.jar from the JDBC package you downloaded from Teradata to the below location on your On-premise machine, where Progress On-premises connector is installed.
   C:\Program Files\Progress\DataDirect\Hybrid_Data_Pipeline_OnPremise_Connector\OPDAS\server\drivers

   
   

3. Restart Progress On-premises connector, by opening Task Manager, go to Services, Locate HDPCloudOPCon and restart the service.

1. Open your browser and go to http://<Server-Address>:8080 and log in to Hybrid Data Pipeline. Go to DataSources tab and create a New Data Source. Choose JDBC Data Store. If you are looking for creating a service for any other data store, feel free to choose it.

    
   
   

2. Configure the Teradata database as shown below to connect to your data.

    

   DataSource Name

   Any name you want

   Driver Class

   com.teradata.jdbc.TeraDriver

   UserName

   <username>

   Password

   <password>

   Connector ID

   Your connector label from on-premises connector

   Connection URL

   jdbc:teradata://<Server>/DATABASE=<db name>,DBS_PORT=1025

    
   
   

3. For the connector ID drop down, you should see the connector label, you configured when installing the Progress On-premises connector on your On-premise machine. In my case, work-laptop is my connector label I have configured for my instance.
4. Click on Test Connection and if your connection configuration is correct, you should be able to connect successfully.

Now you can access your On-premises Teradata database using ODBC and JDBC drivers from your cloud applications.

1. Download and Install the ODBC or JDBC driver for Progress Hybrid Data Pipeline.
2. Follow this documentation on how to install and use the ODBC and JDBC drivers.

1. Go to OData tab, click on button “Configure Schema”.

   
   
   

2. On the next screen you should see list of schemas in your Teradata database. Choose your schema.

   
   
   

3. You should now a see list of tables as shown below. Select the tables you want to expose through OData REST API. I chose Employee table in this case to expose through OData API. If your table doesn't have Primary key, please choose a column in the table as a primary key as shown below.
   
   
   

    

4. Click on Save and Close to save the settings.
5. Back on OData tab, you should find OData Access URI. Copy the URI to another tab and open it. As you might not have proper SSL certificate, you might see unsafe exception. To overcome it, change the URL to http://<Server>:8080/api/odata4/<yourdatasourcename>
6. You should now be prompted for your Hybrid Data Pipeline Credentials, after you enter it you should see the following response, which basically lists out all the tables that are exposed.
   
   
   
   
   
   

It is so easy to connect to Teradata database on-premise with Hybrid Data pipeline and connect to it using standards-based connectivity such as ODBC, JDBC and OData (REST) without you having to write a single line of code or having to deal with any network/firewall issues. Similarly, you can connect to any database on-premise and if you don’t see the data source, that you want to connect to, you can bring in your own JDBC driver and Progress Hybrid Data Pipeline will take care of everything.