Connect to Data Lake Relational Engine Using the ODBC Driver
- How to create an ODBC data source for a data lake Relational Engine connection
- How to use the configured data source with other applications
- You have completed the first tutorial in this group.
- Step 1
The ODBC Data Source Administrator can be used to view the installed ODBC drivers and to create data sources for an installed driver.
Open the administrator by entering ODBC after clicking on the Microsoft Windows start icon.
Click the Drivers tab and view the installed drivers.
The SAP IQ driver is visible.
Click the User DSN tab to view the data sources.
Click Add to create a new data source to connect to a data lake Relational Engine database.
Select SAP IQ and click Finish.
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 option.
Verify the connection by clicking on Test Connection.
Note that if the test fails, try pressing the OK button to save the data source, then try the test again.
Press OK to save the data source.
Note that the saved values can also be viewed using the Microsoft Windows registry editor under the key
For additional details see Connection Properties.
- Step 2
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.
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
Navigate to the directory where the
.odbc.inifile is located, similar to the one highlighted in the screenshot above. Open or create the
.odbc.inifile with the following command:Shell (Linux)Copy
Configure the values of
hostso that they conform with your setup..odbc.iniCopy
[HC_DL_Trial] driver=/home/dan/dlclient/IQ-17_1/lib64/libdbodbc17.so encryption=TLS(trusted_certificates=*;direct=yes) host=XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX.iq.hdl.trial-XXXX.hanacloud.ondemand.com:443 integrated=NO log=/tmp/hdlclientlog.txt
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_Trial" -nogui isql -v HC_DL_Trial USER1 Password1
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_Trial USER1 Password1 -m6
- Step 3
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.
Open Microsoft Excel.
In the Data tab, select Get Data | From Other Sources | From ODBC.
Select the previously created data source that contains the connection information to data lake Relational Engine.
Select Database, enter the credentials, and press Connect.
Select a schema and table, then press Load.
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.
- Step 4
DBeaveris a free and open source database tool and can be used with the data lake Relational Engine ODBC driver.
Note that the ODBC connectivity option is available in
DBeaveron Microsoft Windows only.
The following steps demonstrate how to configure
DBeaverto connect to data lake Relational Engine.
Download and install the community edition of
Create a new database connection.
Search for ODBC and select the ODBC option.
DBeaveris written in Java, so it uses a JDBC to ODBC bridge.
Specify the name of the ODBC data source previously configured for Database/Schema.
After finishing the wizard, the catalog of the database can be viewed, and SQL statements can be executed.
DBeavercan also be used to create an entity relationship (ER) diagram, perform a comparison of two selected objects, execute import and export operations, view spatial data on a map, and perform data analysis with its grouping and
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 Microsoft Excel and
Which of the following statements are true?