cancel
Showing results for 
Search instead for 
Did you mean: 

Sybase ESP to HANA

Wineman
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi all,

I am working in a partner project, where streams should be loaded to HANA via Sybase ESP. Up to now I found no information, how to connect Sybase ESP to HANA.

Has anybody experience in this topic?

Thanks a lot

Wolfgang

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Wolfgang,

Sybase ESP 5.1 SP01 is scheduled for release within the next couple of weeks (maybe sooner).  It contains a specialized HANA Input Adapter.  We seen some incredible numbers for uploading data to HANA.

Until ESP 5.1 SP01 is released, you should use the Generic Database Input Adapter along with the HANA ODBC driver to load data into HANA.  I was going to attach a cheat sheet for this method but it won't allow me to upload Word documents so I will email it to you separately.

Thanks,

  Neal

Former Member
0 Kudos

Neal,

I'm using ESP 5.1 SP01 currently but struggling to configure the connection from Sybase ESP to SAP HANA via a ODBC. Can you please provide some insight on how I can work this out?

I'm very new to ESP and HANA - only started this week.

Thanks.

regards,

Deen

Former Member
0 Kudos

Hello,

Very strange, I did not get a notification about your post.  ESP 5.1 SP01 ships the new HANA Adapter.  There are instructions here for how to configure it:

   http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc01615.0511/doc/html/cgo1347651414221...

You will still need to configure the HANA ODBC driver to connect to your instance of HANA.

Let me know if you are still having problems.  If so, provide as much detail as possible (error messages, operating system, etc.).

Thanks,

  Neal

Former Member
0 Kudos

Neal,

Thanks. I've referred to the guide to get an idea of it the adapter settings but I'm struggling with the HANA ODBC driver/plugin for windows. I can't find the HANA ODBC under the setup data sources panel in Windows - any idea where I can download this?

Also, any tips of the configuring the service.xml file would also be very helpful.

Thanks.

regards,

Deen

Wineman
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi, have you installed the HANA client on your machine? I get the ODBC connector for HANA listed  in the ODBC data source administrator.....

JWootton
Advisor
Advisor
0 Kudos

Here's a HANA entry from my ESP service.xml file that I have been using for demos.  Note that I'm running ESP on a Windows 64 laptop (for some platforms you will need to use the other DriverLibrary - see the comments in the service.xml file):

<Service Name="hanaservice" Type="DB">

  <Parameter Name="DriverLibrary">esp_db_odbc_lib</Parameter>

  <Parameter Name="DSN">HANAdb</Parameter>

  <Parameter Name="User">nnnn</Parameter>

  <Parameter Name="Password" encrypted="false">zzzz</Parameter>

  <Parameter Name="Database">ESP_PM</Parameter>

</Service>

Former Member
0 Kudos

Hello,

I have installed the client (32bit version) and there is a driver named HDBODBC32 which I think is the one you're talking about - how do I proceed to configure this to connect my project in ESP and the HANA instance?

Thanks.

Deen

Former Member
0 Kudos

Hello,

If you installed the 32bit HANA ODBC Client, be sure to use the 32bit ESP installation as well.  You can't mix 32bit and 64bit.

First you will need to configure the HANA ODBC data source.  At a bare minumum, you need to define a "Data Source Name" and the "Server:Port" combination.  Then you can click on the "Connect" button to test it.  It will prompt you for the HANA user name and password.  If it connects, you are done with this step.

Next in your %ESP_HOME%\bin\service.xml file you will need to add a matching entry:

<Service Name="HANAODBCService" Type="DB">

  <Parameter Name="DriverLibrary">esp_db_odbc_lib</Parameter>

  <Parameter Name="DSN">HANATEST</Parameter>

  <Parameter Name="User">SYSTEM</Parameter>

  <Parameter Name="Password">Password1</Parameter>

</Service>

After that is complete, you can test ESP and see if it is able to use the HANA ODBC driver.  The

first test you might try is to start ESP Studio and do into Visual Authoring mode on a new project (hit the F4 key to toggle between Visual and Text Authoring modes).

* From the list of Output Adapters on the right, click on the HANA Output Adapter and then click on the canvas where you want it to be placed.

* Click on the little green icon in the output adapter and that will bring up the adapter's property screen where you just need to enter the name from the service.xml entry above (HANAODBCService):

* Click OK to save the change

* Click on the little magnifing glass icon and the adapter will use the HANA ODBC driver to communicate with HANA and try to discover a list of tables.  If you select a table from the list, it will give you the option to create a window or stream and you will end up with something that looks like this:

Now you are able to run your project.  From the "Run-Test" screen, you can try manually inserting a row of data and see if it shows up in HANA.

Thanks,

  Neal

Former Member
0 Kudos

Hi Neal,

I've managed to setup the ODBC driver on my machine and the connection to my HANA instance was successful. However, after I configured the HANA adapter on ESP and click on scheme discovery I get the error attached below:

I have also created a mirror of the tables on the HANA database without specifying any values.

The configuration of the service.xml file is attached below. Do the entries specified for DSN, Username and Password have to sync with any of these paraamenters on the ESP/HANA instances that I am currently running?

Thanks for all the help guys! Really appreciate it.

regards,

Deen

Former Member
0 Kudos

Hi Jeff,

Thanks for that. I've configured my service.xml in a similar format but running a 32bit version - any comments?

Thanks.

Deen

Former Member
0 Kudos

Hello,

Yes, you want to make sure that:

DSN == the name of the HANA ODBC DSN you created

User == a valid HANA user name

Password == a valid HANA Password

If you make changes to the service.xml file while the server is running, it won't recognize those changes so you will have to restart the server.  If you are running the what we call the "local" cluster that Studio starts by itself, that means you will probably have to close Studio too.

In your screen shot, it looks like there is a dash in front of the "<Service Name=" lines that should not be there.

If you are still stuck, you can send an email to me neal.stack @ sap.com and we can do an SAP Connect session where you can share your desktop with me and we can walk through it.

Thanks,

  Neal

Former Member
0 Kudos

Neal,

Thanks a lot for your help and offer to do a walkthrough. Just connected ESP to HANA tables.

Thanks to everyone else for their support!

regards,

Deen

Former Member
0 Kudos

Hi Neal,

Quick question - when you are creating the tables/input streams on ESP do the same instances have to be created in the HANA database or is there any workaround that will eliminate the duplication of effort to create one database on ESP and the same on HANA?

Thanks.

Deen

Former Member
0 Kudos

Hello,

If you create the tables that you are going to read from or write to in HANA first, ESP provides a "discovery" tool that will create a schema, stream or window that matches the HANA definition.

In one of my screen shots above you can see the little icon with a magnifying glass.  If you click on that, ESP will go fetch a list of the HANA tables.  Then you can choose the table you want in ESP.

Thanks,

  Neal

Former Member
0 Kudos

Neal,

I want to write to HANA tables by streaming csv files using ESP. My question might not have been clear - I wanted to know if I should create the same database model as in ESP again or should I just create the two instances of the same DB model? and then use the ESP-HANA connection to link the two...

cheers.

Former Member
0 Kudos

Hello,

I'm not sure I entirely follow your question.  If you want to write to HANA with ESP, you must have an ESP STREAM or WINDOW that matches the definition of the HANA table.  Then you attach the HANA Output adapter to that stream or window.

So in general, you do the following:

1) Create a table in HANA.  Columnar tables are supposed to provide the best performance for loading data:

CREATE COLUMN TABLE "SYSTEM"."ALERI" ("C1" INTEGER CS_INT NOT NULL ,

  "C2" INTEGER CS_INT NOT NULL ,

  "C3" INTEGER CS_INT NOT NULL ,

  "C4" CHAR(5) CS_FIXEDSTRING NOT NULL ,

  "C5" LONGDATE CS_LONGDATE,

  PRIMARY KEY ("C1")) WITH PARAMETERS ('PARTITION_SPEC' = 'HASH 4 C1')

2) In ESP's Visual Authoring mode, use the "Schema Discovery" icon to create a schema, window or stream.  Attach the HANA Output Adapter to the stream:

// Remember that WINDOWS have a default retention of keeping ALL messages that are received.

// When using WINDOWS you may want to define a different retention clause so memory does not grow unbounded.

CREATE INPUT WINDOW SourceWindow SCHEMA (Column1 integer , Column2 integer , Column3 integer , Column4 string )
Primary KEY (Column1);

CREATE OUTPUT WINDOW OutWindow SCHEMA ( C1 integer , C2 integer , C3 integer , C4 string , C5 bigdatetime ) Primary KEY (C1) AS
        SELECT SW.Column1, SW.Column2, SW.Column3, SW.Column4, now() as C5 FROM  SourceWindow SW;

ATTACH OUTPUT ADAPTER Adapter1 TYPE hana_out TO OutWindow PROPERTIES service = 'HANAODBCService' ,
table = 'ALERI' ,
bulkBatchSize = 80000 ,
bulkInsertArraySize = 10000 ,
threadCount = 4 ;

3) There are several ways you could send your CSV data into ESP:

  a) You could attach a CSV input adapter to the input stream/window (SourceWindow above).

  b) You could use the esp_convert/esp_upload command line utilities:

% more testdata.csv

SourceWindow,i,0,0,27,MSFT

SourceWindow,i,1,10,85,SY

SourceWindow,i,2,20,67,SAP

SourceWindow,i,3,30,39,IBM

SourceWindow,i,4,40,79,EBAY

SourceWindow,i,5,50,90,MSFT

SourceWindow,i,6,60,32,SY

SourceWindow,i,7,70,64,SAP

SourceWindow,i,8,80,73,IBM

SourceWindow,i,9,90,23,EBAY

SourceWindow,i,10,90,23,EBAY

% cat testdata.csv | esp_convert -c espadm:Password1 -d, -p archer:51011/default_workspace/hana_test_project | esp_upload -c espadm:Password1 -p archer:51011/default_workspace/hana_test_project

   c) You could write your own publisher (using the C, Java or .NET APIs) that parses the CSV file and publishes to the input stream/window.

Thanks,

  Neal

Former Member
0 Kudos

Hi Neal,

Hope you're doing alright. Quick follow up question - I've completed all the steps above and connected my ESP to HANA, and my HANA output adapter is successfully detecting the tables on the HANA DB but on running the project the data from the CSV files are loading into ESP but I can't get this pushed into HANA. Would you know what the issue is here?

Thanks.

Regards,

Deen

Former Member
0 Kudos

Hello,

If ESP is unable to publish the data to HANA, I would expect an error to be raised.  Each project will have an "esp_server.log" and "stdstreams.log".

Normally the "stdstreams.log" will just have information about license problems.  Sometimes it contains other messages that were written to stderr.

The "esp_server.log" is the main log file that will have errors related to the project.

I'm not sure which cluster configuration you are using.  If you are just running the project with Studio's default cluster configuration, look for the log files in this directory:

%USERPROFILE%\Documents\SybaseESP\5.1\workspace\<workspace_name>.<project_name>.<project_instance>

For example:

%USERPROFILE%\Documents\SybaseESP\5.1\workspace\default.project1.0

If you are starting a cluster manually instead of letting Studio do it for you (for example you are using %ESP_HOME%\cluster\nodes\node1\node1.xml), you would typically find the "esp_server.log" in this directory:

%ESP_HOME%\cluster\projects\test-name-1\default.project1.0

If there isn't anything helpful in the log file, you may need to increase the debug-level to 4 for your project by editing the project's CCR file:

  http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc01613.0511/doc/html/eli1307139168745...

Thanks,

  Neal

Answers (0)