How to Connect Pentaho Data Integration to SAP HANA

pentaho-saphana

Recently I had to connect Pentaho Data Integration to SAP HANA and I made some notes along the way:

The first step is to get the SAP HANA JDBC driver, a file called ngdbc.jar. The quickest way is to download and install the SAP HANA client from the SAP Software Download Center, and then extract the file from your installation directory
(C:\Program Files\sap\hdbclient\ on Windows, /usr/sap/hdbclient/ on Linux)

If you don’t have a user account for the SAP Software Download Center, you’ll need to get the driver from some other source. For example:

  1. Install the SAP HANA tools into eclipse (oh, yeah, you may need to install eclipse first)
  2. Look for the eclipse plugin files that were installed with the SAP HANA tools  (mine were in <home_folder>/.p2/pool/plugins)
  3. Look for any jar file with jdbc on its name. I found one called com.sap.ndb.studio.jdbc_2.3.5.jar.
  4. Change the jar extension to zip and unpack it. Cross your fingers.
  5. Look for a lib directory and see if the ngdbc.jar file is in it.
  6. Repeat 4,5 until you find the file.

(I don’t get why SAP wants to hinder development for their platform by making it almost impossible to get their JDBC driver but anyways)

Go to the folder where you installed Pentaho Data Integration and copy your recently obtained ngdbc.jar file into the lib directory.

Restart (or start) Pentaho Data Integration.

Pentaho Enterprise Edition: If you have the Enterprise Edition of Pentaho Data Integration, doing a bulk load in SAP HANA is pretty straightforward. Just follow the instructions here.

Pentaho Community Edition: I believe Pentaho doesn’t provide the SAP HANA bulk load plugin for you. In that case, you need to set up a Generic Database connection and use a regular table output step.

Create a new transformation, and then create a new connection.

Screen Shot 2016-06-21 at 11.16.56 PM

Per this page, we learn that the connection string for a SAP HANA connection is

jdbc:sap://<server>:<port>

Where the port is

3<instance_number>15

So if your instance number is 10, the port would  be 31015.

The custom driver class name is com.sap.db.jdbc.Driver

Screen Shot 2016-06-21 at 11.17.47 PM

Fill the form, click test, Ok and you’re good to go!

You may now use this connection to read/write data from/to SAP HANA.

I tested this with PDI 4.4, 5.4 and 6.1. Help me keep this post updated by pinging me at twitter if any of this is no longer valid.

 

By Daniel Pradilla

Soy arquitecto de software y ayudo a la gente a mejorar sus vidas usando la tecnología. En este sitio, intento escribir sobre cómo aprovechar las oportunidades que nos ofrece un mundo hiperconectado. Cómo vivir vidas más simples y a la vez ser más productivos. Te invito a revisar la sección de artículos destacados. Si quieres contactarme, o trabajar conmigo, puedes usar los links sociales que encontrarás abajo.

Discover more from Daniel Pradilla

Subscribe now to keep reading and get access to the full archive.

Continue reading