Skip to Content

Connect to Data Lake Relational Engine Using the ODBC Driver

Configure a data source to connect to the previously created data lake Relational Engine and then use the data source in unixODBC, Microsoft Excel and DBeaver.
You will learn
  • How to create an ODBC data source for a data lake Relational Engine connection
  • How to use the configured data source with other applications
danielvaDan van LeeuwenJune 20, 2022
Created by
danielva
May 13, 2021
Contributors
danielva

Prerequisites

  • You have completed the first tutorial in this group.

Open Database Connectivity (ODBC) provides an API for accessing databases. Database vendors provide ODBC drivers for their database products. An application written to the ODBC standard can be ported to other databases that also provide an ODBC interface.

  • 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.

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

      start ODBC Administrator
    2. Click the Drivers tab and view the installed drivers.

      odbc admin drivers

      The SAP IQ 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.

      Add ODBC user data source
    5. Select SAP IQ and click Finish.

      New data source
    6. Configure the data source.

      specify the data source name

      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.

      specify the credentials, host and port
    7. Verify the connection by clicking on Test Connection.

      test connection

      Note that 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.

      Note that 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.

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

      Shell (Linux)
      Copy
      sudo zypper install unixODBC
      
      unixODBC install

      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
      
      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_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
      
    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_Trial" -nogui
      isql -v HC_DL_Trial USER1 Password1
      

      DBISQL

      ODBC example with dbisql

      isql

      isql query

      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.

    1. Open Microsoft Excel.

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

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

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

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

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

      Browse Schema

      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

    DBeaver is 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 DBeaver on Microsoft Windows only.

    The following steps demonstrate how to configure DBeaver to connect to data lake Relational Engine.

    1. Download and install the community edition of DBeaver.

      Install DBeaver
    2. Create a new database connection.

      New Connection

      Search for ODBC and select the ODBC option.

      New Connection

      DBeaver is written in Java, so it uses a JDBC to ODBC bridge.

    3. Specify the name of the ODBC data source previously configured for Database/Schema.

      New Connection
    4. After finishing the wizard, the catalog of the database can be viewed, and SQL statements can be executed.

      Query

      DBeaver can 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 calc panels.

    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 DBeaver.

    Which of the following statements are true?

Back to top