Skip to Content

Connect to Data Lake IQ Using the ODBC Driver

test
0 %
Connect to Data Lake IQ Using the ODBC Driver
Details

Connect to Data Lake IQ Using the ODBC Driver

July 8, 2021
Created by
May 13, 2021
Configure a data source to connect to the previously created data lake IQ 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 IQ connection
  • How to use the configured data source with other applications
QR code

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: Configure a data source using Mirosoft Windows ODBC Data Source Administrator

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 Sybase 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 IQ database.

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

    New data source
  6. Configure an example data source for data lake IQ.

    specify the data source name

    Connect using the HDLADMIN user. 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

    On the advanced tab, enter the following parameters.

    ENC=TLS(tls_type=rsa;direct=yes)
    
    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.

Log on to answer question
Step 2: Configure a data source on Linux with unixODBC
  1. On SUSE Linux, unixODBC can be installed using Zypper or YaST.

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

    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:

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

    [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.hana.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.

    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.

    isql -v HC_DL_Trial USER1 Password1 -m6
    
Log on to answer question
Step 3: Use data lake data source from Microsoft Excel

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

    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 data lake IQ driver may be needed.

Log on to answer question
Step 4: Browse data lake IQ using DBeaver with ODBC

DBeaver is a free and open source database tool and can be used with the data lake IQ ODBC driver.

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

  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.

Which of the following statements are true?
×

Next Steps

Back to top