Connect to Data Lake Relational Engine Using the ODBC Driver

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

   Shell 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 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 Copy

   pico .odbc.ini

4. Configure the values of driver and host so that they conform with your setup. Note that with the developer licensed version of the data lake client, the driver path below is slightly different.

   .odbc.ini Copy

   [HC_DL]
   driver=/home/XXXXX/sap/dlclient/IQ-17_1/lib64/libdbodbc17.so
   driver=/home/dan/sap/hdlclient/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 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 Copy

   isql -v HC_DL USER1 Password1 -m6

   Code Copy

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 HANA Cloud, data lake Relational Engine and click Finish.

   

6. Configure the data source.

   

   In the ODBC tab of the configuration window, fill in the Data source name.

   Switch to the Login tab and enter in the USER1 credentials.

   Retrieve the SQL Endpoint for your data lake instance. You can find this via the SAP BTP Cockpit or by using the Copy SQL Endpoint menu option in SAP HANA Cloud Central and input into Host field.

   

   Select the Connect to SAP HANA CLOUD, data lake Relational Engine action.

   

7. Verify the connection by clicking on Test Connection in the ODBC tab.

   

   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.

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.