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.

Categories

More from Cloud

IBM Tech Now: October 2, 2023

< 1 min read - ​Welcome IBM Tech Now, our video web series featuring the latest and greatest news and announcements in the world of technology. Make sure you subscribe to our YouTube channel to be notified every time a new IBM Tech Now video is published. IBM Tech Now: Episode 86 On this episode, we're covering the following topics: AI on IBM Z IBM Maximo Application Suite 8.11 IBM NS1 Connect Stay plugged in You can check out the IBM Blog Announcements for a…

IBM Cloud inactive identities: Ideas for automated processing

4 min read - Regular cleanup is part of all account administration and security best practices, not just for cloud environments. In our blog post on identifying inactive identities, we looked at the APIs offered by IBM Cloud Identity and Access Management (IAM) and how to utilize them to obtain details on IAM identities and API keys. Some readers provided feedback and asked on how to proceed and act on identified inactive identities. In response, we are going lay out possible steps to take.…

IBM Cloud VMware as a Service introduces multitenant as a new, cost-efficient consumption model

4 min read - Businesses often struggle with ongoing operational needs like monitoring, patching and maintenance of their VMware infrastructure or the added concerns over capacity management. At the same time, cost efficiency and control are very important. Not all workloads have identical needs and different business applications have variable requirements. For example, production applications and regulated workloads may require strong isolation, but development/testing, training environments, disaster recovery sites or other applications may have lower availability requirements or they can be ephemeral in nature,…

IBM accelerates enterprise AI for clients with new capabilities on IBM Z

5 min read - Today, we are excited to unveil a new suite of AI offerings for IBM Z that are designed to help clients improve business outcomes by speeding the implementation of enterprise AI on IBM Z across a wide variety of use cases and industries. We are bringing artificial intelligence (AI) to emerging use cases that our clients (like Swiss insurance provider La Mobilière) have begun exploring, such as enhancing the accuracy of insurance policy recommendations, increasing the accuracy and timeliness of…