Connect to Data Lake Relational Engine Using the ODBC Driver

The ODBC Data Source Administrator can be used to view the installed ODBC drivers and to create data sources for an installed driver.

1. Open the administrator by entering ODBC after clicking on the Microsoft Windows start icon.

   

2. Click the Drivers tab and view the installed drivers. The SAP HANA Cloud, data lake Relational Engine driver is visible.

   

3. Click the User DSN tab to view the data sources.

4. Click Add to create a new data source to connect to a data lake Relational Engine database.

   

5. Select SAP IQ and click Finish.

   

6. Configure the data source.

   

   Connect using USER1. The host and port values can be copied from the SAP BTP Cockpit or SAP HANA Cloud Central via the Copy SQL Endpoint menu option and then click on Login.

   

7. Verify the connection by clicking on Test Connection.

   

   If the test fails, try pressing the OK button to save the data source, then try the test again.

8. Press OK to save the data source.

   The saved values can also be viewed using the Microsoft Windows registry editor under the key Computer\HKEY_CURRENT_USER\Software\ODBC\ODBC.INI.

For additional details see Connection Properties.

1. On SUSE Linux, unixODBC can be installed using Zypper or YaST.

   Shell (Linux)

   Copy

   sudo zypper install unixODBC
   

   

   For more details on how to accomplish this, please follow the second step of this tutorial.

2. The following commands can be used to confirm that unixODBC is installed and determine the location of the .odbc.ini file (if it exists).

   Shell (Linux)

   Copy

   cd /etc/unixODBC
   odbcinst -j
   

   

3. Navigate to the directory where the .odbc.ini file is located, similar to the one highlighted in the screenshot above. Open or create the .odbc.ini file with the following command:

   Shell (Linux)

   Copy

   pico .odbc.ini
   

4. Configure the values of driver and host so that they conform with your setup.

   .odbc.ini

   Copy

   [HC_DL]
   driver=/home/XXXXX/sap/dlclient/IQ-17_1/lib64/libdbodbc17.so
   encryption=TLS(trusted_certificates=*;direct=yes)
   host=XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX.iq.hdl.XXXX-XXXX.hanacloud.ondemand.com:443
   integrated=NO
   log=/tmp/hdlclientlog.txt
   

   Here the driver path is the path to your libdodbc17.so file.

5. DBISQL or isql, which is a tool provided by unixODBC can be used to try out the data source. Ensure you are using the correct username and password.

   Shell (Linux)

   Copy

   dbisql -hdl -c "uid=USER1;pwd=Password1;dsn=HC_DL" -nogui
   isql -v HC_DL USER1 Password1
   

   dsn is the name set in the odbc.ini file in the previous step.

   DBISQL

   Some example queries you can run are listed below.

   SQL

   Copy

   SELECT * FROM HOTELS.CUSTOMER;
   

   SQL

   Copy

   SELECT * FROM HOTELS.ROOM;
   

   

   isql

   

   To exit dbisql or isql type quit.

   ---

   The column width when displaying results in isql can be set using the -m parameter.

   Shell (Linux)

   Copy

   isql -v HC_DL USER1 Password1 -m6
   

An application that supports ODBC can now make use of the created data source. One example on Windows is Microsoft Excel.

The following steps demonstrate how to use Microsoft Excel to query data in data lake Relational Engine using the ODBC connector.

1. Open Microsoft Excel.

2. In the Data tab, select Get Data | From Other Sources | From ODBC.

   

3. Select the previously created data source that contains the connection information to data lake Relational Engine.

   

4. Select Database, enter the credentials, and press Connect.

   

5. Select a schema and table, then press Load.

   

6. The selected data is now imported into a Microsoft Excel spreadsheet.

   

   Note, if an error occurs that mentions, you do not have permission to select from SYSINDEX, a newer version of the driver may be needed.

For further information on programming an application to use the ODBC client driver, see ODBC CLI.

Congratulations! You have configured an ODBC data source to contain connection information for a SAP HANA Cloud, data lake Relational Engine database and used that data source from unixODBC and Microsoft Excel.