Connect Exasol to SAP HANA Database using JDBC Driver
Using JDBC connection, Exasol data warehouse SQL developers can connect to SAP HANA databases and import data or execute SQLScript commands on remote SAP HANA data sources. Before creating a successfull JDBC connection to SAP HANA database from Exasol cluster, Exasol data warehouse administrators should define a JDBC driver definition and upload SAP HANA JDBC driver to ExaCluster.
In this Exasol tutorial, I want to show the steps how to create a JDBC connection on Exasol Data Warehouse using EXAoperation Web UI administration tool and how to use the JDBC connection in SQL to query data from remote SAP HANA databases.
Download SAP HANA JDBC Driver
First of all, to create a JDBC connection to SAP HANA database, Exasol administrators require the SAP HANA JDBC driver from SAP. One of the methods to download the SAP HANA JDBC drivers is to download SAP HANA Database Client Software Components.
If you follow the steps, SQL developers can find the SAP HANA JDBC driver file ngdbc.jar within "C:\Program Files\SAP\hdbclient" folder by default.
Upload JDBC Driver to Exasol Cluster using EXAoperation
After the SAP HANA JDBC driver is downloaded Exasol administrators can use EXAoperation Web UI tool to upload the JDBC driver to the data warehouse cluster.
First launch Exasol's EXAoperation web based management tool and logon to the system.
Under Configuration go to Software node. Switch to JDBC Drivers tab where database administrator or SQL developers can define a new JDBC connection by uploading the .jar driver binary files.
Within JDBC Drivers tab click the Add button
Fill the Driver Name, Main Class and Prefix input text as follows:
Driver Name: SAPHANADriver
Main Class: com.sap.db.jdbc.Driver
Prefix: jdbc:sap:
To make it more clear, for driver name the Exasol admin is free to define any alias here.
The main class name for the JDBC driver, it is important and this information is basically provided by the developer of the JDBC driver.
If you go to help.sap.com for Connect to SAP HANA via JDBC, you will see that the JDBC driver class name is com.sap.db.jdbc.Driver
For the Prefix, the only restriction seems to be it should start with "jdbc:" and end with ":"
As Exasol professionals can realize easily on above screenshot, Disable Security Manager checkbox which is marked as "Dangerous" in case it is selected is already checked. Although, this is an optional field, for SAP HANA JDBC driver to be used successfully it must be marked. Disable Security Manager allows JDBC Drivers to access certificate and additional information which is required as I said for SAP HANA JDBC driver.
Click Add button to return back to previous JDBC Drivers tab
Please note that on Comments columnd you will see following warning message: "? no security manager"
Make sure that the option box next to SAP HANA Driver entry is selected. Then click on Browse button to select the JDBC driver file ngdbc.jar for SAP HANA database using File Explorer.
Go to the file folder where the ngdbc.jar file is stored. Select the .jar file and click Open
When you are back on JDBC Drivers tab page, be sure the SAP HANA driver definition option is still selected then click Upload
When the .jar driver file upload is completed, SQL developers can see the file name under Files column for the recently created SAP HANA JDBC driver entry.
Now Exasol SQL developers can use the JDBC driver to connect SAP HANA databases to query data from HANA database table and views, etc.
Execute Query on SAP HANA Database from Exasol using JDBC Connection
To access data on remote data sources including SAP HANA databases using JDBC connections, first of all a connection object should be created by SQL code. Here is a sample SQL command
CREATE or REPLACE CONNECTION saphana_connection
'jdbc:sap://10.130.160.100:30215/dbname'
USER 'hanauser'
IDENTIFIED BY 'password';
After the above connection creation SQL script is executed successfully, database developers can use the SAP HANA JDBC connection in their SQL commands as follows:
SELECT tbl.*
FROM (
IMPORT FROM JDBC AT saphana_connection STATEMENT '
SELECT current_date from dummy;
' ) as tbl;
Here is the output of the above SQL query executed on Exasol database to connect SAP HANA database and fetch current date information
Troubleshoot Exasol JDBC Connection to SAP HANA Database
While I was trying to connect Exasol Data Warehouse to SAP HANA database, I experienced e few errors. I want to share Exasol SQL developer how they could get rid of and solve these connection errors.
An error that Exasol SQL developers or administrators can experience is below issue:
[Code: 0, SQL State: ETL-5] JDBC-Client-Error: Failed loading driver 'com.sap.db.jdbc.Driver': null, access denied ("java.io.FilePermission" "/home/exasolution/.sdb" "read") (Session: 1658236934628654287)
This error can be resolved simply by marking the checkbox "Disable Security Manager " on JDBC driver configuration screen where we have defined the driver name, main class of the driver and the prefix. I hope you have remembered from previous steps illustrated above in this Exasol tutorial.
The second error I experienced while trying to execute SQL queries on DbVisualizer is below problem:
[Code: 0, SQL State: ETL-1] No default DRIVER registered for jdbc:sap://10.130.160.100:30215. Please specify DRIVER or add a default via EXAoperation (Session: 1658236901856311001)
This error was interesting and took some time to identify the problem. It is unbelievable. Please check if there are whitespaces or space characters on prefix definition where we have entered "jdbc:sap:" :)
I hope SQL developers building applications on Exasol Analytic Data Warehouse will find this Exasol tutorial useful