cancel
Showing results for 
Search instead for 
Did you mean: 

Executing Streaming Project involving both input and output adapter

vinaya_hg
Participant
0 Kudos

Hi Folks,

As I am beginner in HANA Streaming ,was trying to execute below attached scenario. But I am not getting require output, the data from HANA input is not filled into input stream.

Is there any particular method to be followed to execute project involving both input and output adapter? ?

Accepted Solutions (0)

Answers (2)

Answers (2)

RobertWaywell
Product and Topic Expert
Product and Topic Expert
0 Kudos

Here is a working example that will continuously move data from the STREAMING_USER.VBAP_SOURCE table to the STREAMING_USER.VBAP_DESTINATION table on a 10 second polling interval. Note that there is no logic implemented to filter the data, so the same records from the source table are reprocessed each polling interval.

I will note once again that this is not a meaningful streaming analytics use case. Nor is it a good getting started example. For new streaming analytics users I strongly recommend working through the tutorials available on the HANA streaming analytics Developer Center.

To run the example:

1) Setup the HANA tables and initial data. You will either need to create a schema called STREAMING_USER or modify the script to using an existing schema in your database:

DROP TABLE STREAMING_USER.VBAP_SOURCE;


DROP TABLE STREAMING_USER.VBAP_DESTINATION;


CREATE COLUMN TABLE STREAMING_USER.VBAP_SOURCE
(	MATNR INTEGER,
	MATKL CHAR(30) ,
	ARKTX CHAR(30) ,
	NETWR decimal(15, 2 )
 ) ;
 
 CREATE COLUMN TABLE STREAMING_USER.VBAP_DESTINATION
(	ROW_ID	BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
	MATNR INTEGER,
	MATKL CHAR(30) ,
	ARKTX CHAR(30) ,
	NETWR_SUM decimal(15, 2 )
);
	


INSERT INTO STREAMING_USER.VBAP_SOURCE VALUES (1, 'A', 'a', 10.1);
INSERT INTO STREAMING_USER.VBAP_SOURCE VALUES (1, 'A', 'a', 12.3);
INSERT INTO STREAMING_USER.VBAP_SOURCE VALUES (1, 'A', 'a', 9.4);
INSERT INTO STREAMING_USER.VBAP_SOURCE VALUES (1, 'A', 'b', 1.01);
INSERT INTO STREAMING_USER.VBAP_SOURCE VALUES (1, 'A', 'b', 1.23);
INSERT INTO STREAMING_USER.VBAP_SOURCE VALUES (1, 'A', 'b', 0.94);

2) From the SAP HANA Streaming Development perspective in HANA Studio or from the streaming plugin in WebIDE create a data service called 'hana_service'. Instructions on how to create a data service can be found in Part 1 of the Get started with SAP HANA Smart Data Streaming tutorial.

3) Create a new streaming project, switch to the CCL text editor and cut and paste in the following CCL:

CREATE INPUT STREAM VBAP_stream1 SCHEMA
(	MATNR  integer ,
	MATKL  string ,
	ARKTX  string ,
	NETWR_SUM  decimal(15, 2 )
 );




ATTACH INPUT ADAPTER Generic_DB_Input1 TYPE db_in
TO VBAP_stream1
PROPERTIES
	   service = 'hana_service' ,
	   query = 'SELECT MATNR, MATKL, ARKTX, SUM(NETWR) NETWR_SUM FROM STREAMING_USER.VBAP_SOURCE GROUP BY MATNR, MATKL, ARKTX' ,
	  table = '\"STREAMING_USER\".\"VBAP_SOURCE\"' , 
	  pollperiod = 10 ,
	permutation = 'MATNR=MATNR:MATKL=MATKL:ARKTX=ARKTX:NETWR_SUM=NETWR_SUM' ;


ATTACH OUTPUT ADAPTER HANA_Output1 TYPE hana_out
TO VBAP_stream1
PROPERTIES
	 service = 'hana_service' ,
	 sourceSchema = 'STREAMING_USER' ,
	 table = 'VBAP_DESTINATION',
	 permutation = 'MATNR=MATNR:MATKL=MATKL:ARKTX=ARKTX:NETWR_SUM=NETWR_SUM' ;

RobertWaywell
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Vinaya,

The block diagram that you included in the question is useful to help us understand the data flow that you are trying to implement. In order to get a better understanding of how you have configured the adapters, the CCL would be more informative.

By default the Generic DB Input adapter will query the source database table once when the project starts up. The adapter can be configured to periodically poll the database for new/changed records but that won't happen by default. As a guess, that would be the first place I would suggest looking.

Are you looking to use this configuration - basically moving data between 2 HANA tables - in a particular application, or is this just a test project that you're using to get familiar with streaming analytics?

Thanks

Rob

vinaya_hg
Participant
0 Kudos

Hi Robert ,

Thanks for responding.

It's just a test project to understand the use of input and output adapter ,similarly I want to try using Sybase as input and HANA as output or vice-versa.

Please find attached CCL and tell us how to write SQL query in adapter.

ccl.txt

Regards,

Vinaya

RobertWaywell
Product and Topic Expert
Product and Topic Expert
0 Kudos

Is the input table really supposed to be in the "schema" schema?

Currently you have it specified as:

table = '\"schema\".\"VBAP_stream1\"'

In contrast, you have the output table specified as being in the schema "VCHANDRA".

vinaya_hg
Participant
0 Kudos

No in general I specified it as schema

RobertWaywell
Product and Topic Expert
Product and Topic Expert
0 Kudos

Ok, so the first think to fix is to specify the correct schema name for the schema that the VBAP_stream1 table is in.

As a 2nd question, do you have 2 different versions/instances of the VBAP_stream1 table?

vinaya_hg
Participant
0 Kudos

I don't have two separate instances of VBAP_stream1 ,but VBAP_stream1 is a input stream name as well as the destination table.

RobertWaywell
Product and Topic Expert
Product and Topic Expert
0 Kudos

What behavior are you expecting using the same table as both input and output? Reading data out of a table and then writing it back to the same table will just duplicate the data - except in your case you are executing a GROUP BY in your SELECT statement in the input adapter, so the values written out will be an aggregate. I'm suspicious that isn't the behavior you are looking for.

Perhaps the more important question, is your current scenario important to the project you want to build or are you more focused on a hands-on introduction to HANA streaming analytics? If you are looking for a getting started tutorial then we have a rich set of tutorials on the SAP HANA streaming analytics Developer Center. If you are working with HANA Express, then the tutorial group to start with is the Get started with SAP HANA streaming analytics for SAP HANA, express edition

vinaya_hg
Participant
0 Kudos

Hi Robert,

I know this is not the right scenario, but as I said in the beginning I am just trying to understand the adapters functionality. As per my understanding once I compile the project input stream should get the data from input adapter, and finally to the destination.

I have a doubt whether the query statement written is wrong or the procedure of compiling the project.

Please help me