How to set up ODBC connectivity for IBM Cloud SQL Query using the Progress DataDirect Hybrid Data Pipeline product.

IBM Cloud SQL Query is an IBM Cloud service that allows users to access and process data on IBM Cloud Object Storage using standard SQL. IBM Cloud SQL Query provides a Web UI, a REST API, SDKs for Python, Go, Node.js and a JDBC driver for access.

The JDBC driver enables BI Tools to integrate data on IBM Cloud Object Storage into business reports. But, there are a couple of BI tools out there that do not provide JDBC connectors — instead, they have ODBC connectors. In this blog post, we’re going to describe an easy way to set up ODBC connectivity for IBM Cloud SQL Query using the Progress DataDirect Hybrid Data Pipeline product.

Progress DataDirect Hybrid Data Pipeline

Progress DataDirect offers Hybrid Data Pipeline (HDP) as a connectivity solution that comes with several connectors, including a JDBC connector to which you can plug in a custom JDBC driver, such as the one provided by IBM Cloud SQL Query. Additionally, it provides an ODBC driver that applications with ODBC connectors can use to connect to a Hybrid Data Pipeline server. Hybrid Data Pipeline acts as a middleman to map ODBC requests to JDBC requests:

Setting up Progress DataDirect Hybrid Data Pipeline

The easiest way to set up ODBC connectivity to IBM Cloud SQL Query with Progress DataDirect Hybrid Data Pipeline is to use the Docker image provided by the free Hybrid Data Pipeline trial. It contains a Hybrid Data Pipeline server pre-installed on a Linux 64-bit system. A prerequisite for using it is that you have downloaded and installed Docker on your machine (Windows, Linux or Mac). Once you have done so, download the Hybrid Data Pipeline Docker image from the Progress DataDirect download page:

To download the Hybrid Data Pipeline Docker image, load it into your Docker repository and start the Hybrid Data Pipeline server, see the instructions in the Progress DataDirect tutorial.

Setting up JDBC connectivity from HDP to IBM Cloud SQL Query 

The next step is to plug the custom JDBC driver provided by IBM Cloud SQL Query into the Hybrid Data Pipeline and  create a JDBC data source for connectivity for your IBM Cloud SQL Query instance. Download the latest JDBC driver ibmcloudsql-jdbc-version.jar.

To make the driver available to the Hybrid Data Pipeline server, copy the driver into the Docker container using the following command:

docker cp ibmcloudsql-jdbc-.jar:/opt/Progress/DataDirect/Hybrid_Data_Pipeline/Hybrid_Server/ddcloud/keystore/drivers

Next, log in to the Docker container, stop and start the server and exit the container again, as follows:

docker exec -it <containerID> /bin/bash
cd /opt/Progress/DataDirect/Hybrid_Data_Pipeline/Hybrid_Server/ddcloud
./stop.sh
./start.sh
exit

You are now all set to create a JDBC data source. In order to do so, start a web browser and connect to http://<hostname>:8080/hdpui/. You should see the following log-on page:

Log on as user d2cadmin using the password you set up when starting the Docker container:

Next, you will see the following page:

Navigate on the left sidebar to Manage Data Sources:

Create a JDBC data source to connect to your IBM Cloud SQL Query instance. On the top of the page, click the New Data Source button. This brings up the following page where you can select the data store to which you want to connect. As you want to connect to an IBM Cloud SQL Query instance using the custom JDBC driver, select JDBC:

This opens up a page to collect the information needed to connect to your IBM Cloud SQL Query instance: 

Fill in the following information:

• Data Source Name: A name of your choice.
• Driver Class: com.ibm.cloud.sql.jdbc.Driver
• User ID: Either the word apikey or blank.
• Password: Your IBM Cloud apikey.
• Connection URL: The connection URL for the JDBC Connection to IBM Cloud SQL Query has the general form  jdbc:ibmcloudsql:{instance-crn}[?{key1}={value1}&{key2}={value2}…].

The instance-crn is the Cloud Resource Name of your IBM Cloud SQL Query instance and can be obtained from the service dashboard in the IBM Cloud console. The following is an example URL: 

jdbc:ibmcloudsql:crn:v1:bluemix:public:sql-query:eu-de:a/290ec9931c0737248f3dc2aa57187d14%3AAf86d20c4-7646-441e-ad4b-182c57008471::?targetcosurl=cos://us-geo/sample-result-bucket/hdpdatasets/&password=*******

Connection properties can be specified as part of the URL, as key=value pairs separated by an ampersand (&). Specify the following properties:

• Targetcosurl: Cloud Object Storage URL in SQL query style, where the results should be stored. Ensure that the specified Cloud Object Storage bucket exists.
• Password: Your IBM Cloud API key.

Click on Test in the bottom right corner to validate that you can connect to your IBM Cloud SQL Service instance. You should see the message “connection established successfully.”

Setting up ODBC connectivity to IBM Cloud SQL Query through HDP 

When you run the Hybrid Data Pipeline Docker image, four configuration and certificate files are generated. These files are located in the following Hybrid Data Pipeline installation directory:

/opt/Progress/DataDirect/Hybrid_Data_Pipeline/Hybrid_Server/redist

Before installing a component, such as the ODBC driver, these files must be copied to the installer directory of the component you are installing (the ODBC client installer). Create the temporary installer directory with the following command:

/<path-to-odbc-client-installer>.

Copy the files using the following command:

docker cp <container_name>:/opt/Progress/DataDirect/Hybrid_Data_Pipeline/Hybrid_Server/redist /<path-to-odbc-client-installer>

The four configuration and certificate files are as follows:

• config.properties
• OnPremise.properties
• ddcloud.pem
• ddcloudTrustStore.jks

Next, download the ODBC driver installer (your trial version) from the Progress DataDirect Hybrid Data Pipeline ODBC Driver download page.

Download the ODBC driver installer appropriate for your platform into the <path-to-odbc-client-installer> folder and install it. The creation of an ODBC data source is described by a sample for the Windows version. Go to the Windows start menu and look for the Progress DataDirect Hybrid Data Pipeline folder. Open the folder and select ODBC Administrator. This will open up the following tool to configure an ODBC Data Source:

Click on the Add button. The following selection menu opens:

Select the ODBC Driver DataDirect HDP 4.6 and click Finish. This will bring up the configuration menu:

Provide the following information:

• Data Source Name: Name of your choice for the ODBC Data Source.
• Hybrid Data Pipeline Source: Name of the JDBC Data Source you created with the Progress HDP Administrator to connect to the IBM Cloud SQL Query service.
• Service: IP address or the hostname of the host.
• Port Number: 8080 for the port on which the Hybrid Data Pipeline server listens for requests.

Click on Test Connect to validate the configuration. You should see the message “Connection Established.”

Now you are ready to use the ODBC data source to connect from an application to your IBM Cloud SQL Query instance. 

Learn more

Learn more about IBM Cloud SQL Query  and Progress DataDirect Hybrid Data Pipeline.