on 12-13-2012 1:21 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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:
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
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
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>
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
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
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
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
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.
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
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
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:
Thanks,
Neal
User | Count |
---|---|
84 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.