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

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.

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.

    

   • Azure
   • AWS
   • Google Cloud
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
   • Azure
   • AWS
   • Google Cloud

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.

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

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

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.
2. Configure the Vertica JDBC Data Source as shown below to connect to your Vertica database.
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 API
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.
10. To access the data in product_dimensions table, you can navigate to the UR: http://<server>:8080/api/odata4/Vertica/product_dimension

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.