RESTify Vertica: Generate OData REST API for Vertica database in 10 minutes

Introduction

In this tutorial we will walk you through how you can RESTify your Vertica database in 10 minutes using Progress Hybrid Data Pipeline. Our hybrid connectivity services—Hybrid Data Pipeline—can help you RESTify your database via OData. We support a wide range of data sources including EMR Hive, Cloudera, IBM DB2, SQL Server, Oracle, Postgres, Hubspot and Marketo. Recently, we have introduced a new feature where you can bring in your own JDBC driver, plug it in to Hybrid Data Pipeline to RESTify your database and/or to access to your data source behind firewall. We will be using this feature to RESTify Vertica database and below is the tutorial on how you can do that.

Download and Install Hybrid Data Pipeline

  1. Download Progress Hybrid Data Pipeline 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.

     

  3. If your Vertica database is behind a firewall and you would have to install our On-Premises Connector, which lets you access data behind any firewall without complex network configurations such as SSH tunnels, reverse proxies or VPNs. Progress On-Premises connector, as the name suggests must be installed on a server behind your firewall. Below are the tutorials for installing Hybrid Data Pipeline’s On-Premise Connector, if your Hybrid Data Pipeline Server is installed on

Enable JDBC feature

The first thing you need to do is open Postman and send a PUT request, to enable JDBC driver feature in Hybrid Data Pipeline. You need to authenticate using basic auth using the “d2cadmin” user you have configured while installing Hybrid Data Pipeline.

PUT https://<server>/api/admin/configurations/5
Body
{
    "id": 5,
    "description": "Enable JDBC DataStore, when value is set to true, JDBC DataStore will be enabled.",
    "value": "true"
}

This will enable third party JDBC feature in Hybrid Data Pipeline.

Add Vertica JDBC driver to classpath

  1. Copy the Vertica JDBC driver to the following location in Hybrid Data Pipeline Server’s install location

    /install-path/Progress/DataDirect/Hybrid_Data_Pipeline/Hybrid_Server/ddcloud/keystore/drivers

  2. Navigate to the below path in your Hybrid Data Pipeline Server and restart the server, by running the following commands.
    $pwd
    /install-path/Progress/DataDirect/Hybrid_data_Pipeline/Hybrid_Server/ddcloud
    $ ./stop.sh
    $ ./start.sh

Configure OData for Vertica

  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.1
  2. Configure the Vertica JDBC Data Source as shown below to connect to your Vertica database.2
  3. Click on Test Connection and check if your configuration is correct.
  4. Now, Metadata Exposed Schemas should be populated with all the schemas available in your database. Choose your schema.
  5. Now go to OData tab and Click on button “Configure Schema”.
  6. Choose your schema and you should see list of tables as shown below. Select the tables you want to expose through OData REST API. I chose product_dimesion table in this case to expose through OData API3
  7. Click on Save and Close to save the settings.
  8. 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>
  9. 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.4
  10. To access the data in product_dimensions table, you can navigate to the UR: http://<server>:8080/api/odata4/Vertica/product_dimension5

That’s it you have just RESTified your Vertica database. You can now use the OData base URI with any of the Visualization and analytical tools like Tableau, Power BI, SAP Lumira etc or for any of your custom uses. Feel free to try Progress Hybrid Data Pipeline and contact us if you have any questions or issues while using it.


OData, JDBC TUTORIAL

RESTify Vertica: Generate OData REST API for Vertica database in 10 minutes

DataDirect
connectors

Connect any data source to any application

DataDirect
Tutorials

Discussions, tips and tricks for
DataDirect Connect drivers