cancel
Showing results for 
Search instead for 
Did you mean: 

Send UPDATEs through HANA-Output-Adapter

Former Member
0 Kudos

Hi everyone,

i got a little problem which should be simple to fix. ( at least I hope so).

All I want is to send data updates into HANA using the HANA-Output-adapter. But I only can make inserts. At each row coming through the flex (stream) which should be updated going into the adapter I get a "bad row".

In the flex-adapter I set the opCode to "upsert" in the output command.

The HANA-Table as a key column and the data seams correct in ESP.

What it should look like:

Flex stream -> HANA Output-Adapter -> ---- UPDATE ----> HANA table

Is there another way to mark an event as an update for HANA?

Greetings,

Matthias

Accepted Solutions (1)

Accepted Solutions (1)

JWootton
Advisor
Advisor
0 Kudos

I suspect your problem has to do with setting the Flex output opcode to "upsert".  Also, you say it's a Flex Stream - not sure if you really mean that.  I would recommend a Flex Window and explicitly set the opCode to insert or update as needed.  This will work.

I haven't tried using upsert opcodes into the HANA adapter, but I'm guessing they won't work.  A regular CCL derived window will never produce an upsert - it will always explicitly produce an insert or a delete.

former_member217348
Participant
0 Kudos

Hi Matthias,

To add to Jeff's response, you may want to provide the CCL you are using along with sample input data and the expected output data, so we can get a better sense of where the issue lies.

Thanks,

Alice

Former Member
0 Kudos

My goal is to update a sub set of the HANA table with the upserts.

And yes, I made a flex stream and not a window.

So, I tried it at a more simple project (code attached) with a flex window and set the opcodes to update and the result was that no! rows/events are leaving the flex.


If I set the opcode to upsert the events leaving the flex window and make there way throw the adapter into HANA. In the Studio I'm putting manual events into the in_stream as "Inserts". Events with the same key also get updated in HANA as long as the project has inserted this key. Old key do not get updated (bad rows in the out adapter).


And if the opcode get set to "upsert" in the studio manual insert every thing works according to plan. New rows get inserted und old get updated.


It seams that the problem is in the flex supplying stream.


So, is there a plan to set the opcode direct in a input/output stream?

Maybe I over read something in the docu.


Greetings,



Matthias

Former Member
0 Kudos

CREATE SCHEMA sc_HANA (

  K integer ,

  V integer ) ;

CREATE INPUT STREAM in_stream SCHEMA sc_HANA;

CREATE FLEX flex_window IN in_stream OUT OUTPUT WINDOW flex_window schema sc_HANA PRIMARY KEY (K)

BEGIN

  ON in_stream {

  output setOpcode([K = in_stream.K; V = in_stream.V], update);

} ;

END

;

CREATE OUTPUT STREAM out_stream SCHEMA sc_HANA PRIMARY KEY ( K ) AS SELECT * FROM flex_window ;

ATTACH OUTPUT ADAPTER HANA_Output1 TYPE hana_out TO out_stream PROPERTIES service = 'hana' ,

  sourceSchema = 'MAHA' ,

  table = 'TEST' ,

  bulkBatchSize = 1 ,

  bulkInsertArraySize = 1 ,

  idleBufferWriteDelayMSec = 1 ,

  bufferAgeLimitMSec = 1 ;

remi_astier
Employee
Employee
0 Kudos

Hello Matthias,

Can you attach the hana output adapter directly to the window ?

Remi.

Former Member
0 Kudos

Yes, it works also directly without the out_stream.

But as before only if i set the opcode in in Manual Input to "update" or "upsert".

With the default "insert" only rows get updated which are added by the project. Rows that were in the HANA table before the project started do not get updated.

remi_astier
Employee
Employee
0 Kudos

That's probably not helping, but I recommend to put the pipe to explicitly indicate the PK when outputing the record.

output setOpcode([K = in_stream.K;   |  V = in_stream.V], update);

Former Member
0 Kudos

sadly it changed nothing.

Former Member
0 Kudos

Can you try the following CCL?  Make sure to send an insert first for a key followed by updates.  Upserts will be rejected by the keyed stream "in_stream"

CREATE SCHEMA sc_HANA (

  K integer ,

  V integer ) ;

CREATE INPUT STREAM in_stream SCHEMA sc_HANA PRIMARY KEY (K);

CREATE FLEX flex_window IN in_stream OUT OUTPUT WINDOW flex_window schema sc_HANA PRIMARY KEY (K)

BEGIN

  ON in_stream {

  output setOpcode([K = in_stream.K; V = in_stream.V], upsert);

} ;

END

;

CREATE OUTPUT STREAM out_stream SCHEMA sc_HANA PRIMARY KEY ( K ) AS SELECT * FROM flex_window ;

ATTACH OUTPUT ADAPTER HANA_Output1 TYPE hana_out TO out_stream PROPERTIES service = 'hana' ,

  sourceSchema = 'MAHA' ,

  table = 'TEST' ,

  bulkBatchSize = 1 ,

  bulkInsertArraySize = 1 ,

  idleBufferWriteDelayMSec = 1 ,

  bufferAgeLimitMSec = 1 ;

JWootton
Advisor
Advisor
0 Kudos

The problem is that because the flex is a window, it is going to "enforce" the opCodes.  So it won't let you update a row that's not in the window.  So the above will work using upsert to add or update a row in flex_window,  but here's the deal,  if it's a row that already exists in the HANA table, but doesn't exist in flex_window, it will be processed as an insert, but as an insert to the HANA table it will fail, because a row with that key value exists.

Now if you change the flex to produce a keyed stream instead of a window,  then you can generate update events that will update rows in the HANA table,  but they have to be updates, not upserts, because keyed streams don't do upserts.

So depending on your use case you might need to think about how you want to handle this.  If you need to do apply an upsert to the HANA table, but the HANA table contains rows that didn't come via SDS, and thus rows that the CCL project doesn't know about,  then it's a little tricky. One option would be apply it as both an insert and an update - and just know that one will fail (is that a bad idea?).  Or...?

But I don't know of a way to force the CCL to produce an upsert to HANA (unless there's a trick I don't know - Pauli?)

Former Member
0 Kudos

No way for winodw to produce upserts.  However the Hana adapter has a useUpserts flag that if turned on will try an insert and if it fails will use an update.  It will impact the performance of the adapter though.

JWootton
Advisor
Advisor
0 Kudos

Ahh - Pauli does indeed come through with a trick.  I just tried it,  and setting the HANA adapter property useUpserts=True works.  Now, if the row exists in the HANA table but not the flex window, it will get added to the Flex window and updated in the HANA table:

CREATE SCHEMA MESSAGE (

  ID  INTEGER,

  VALUE integer);

CREATE INPUT STREAM instream

  SCHEMA MESSAGE

  PRIMARY KEY (ID);

CREATE FLEX FlexOut

IN instream

OUT OUTPUT WINDOW FlexOut

SCHEMA MESSAGE

PRIMARY KEY (ID)

BEGIN

  ON instream {

      output setOpcode([ID = instream.ID; | VALUE = instream.VALUE], upsert);

  };

END;

ATTACH OUTPUT ADAPTER HANA_Output1 TYPE hana_out

TO FlexOut

PROPERTIES

  service = 'hdb1' ,

  sourceSchema = 'JEFF' ,

  table = 'STATUS' ,

  useUpserts = TRUE ;

Answers (1)

Answers (1)

Former Member
0 Kudos

Thank you Jeff. Your example sloved the problem!

I adjusted the logic to a Point where I can separate between an update or an insert and moved to a keyed stream with the Adapter directly adapted to it.

CREATE SCHEMA sc_HANA (      K integer ,      V integer ) ;       CREATE INPUT STREAM in_stream SCHEMA sc_HANA; CREATE FLEX flex_stream IN in_stream OUT OUTPUT STREAM flex_stream schema sc_HANA PRIMARY KEY (K) BEGIN      ON in_stream {output setOpcode([K = in_stream.K; | V = in_stream.V], update); }; END ; ATTACH OUTPUT ADAPTER HANA_Output1 TYPE hana_out TO flex_stream PROPERTIES service = 'hana' ,      sourceSchema = 'MAHA' ,      table = 'TEST' ,      bulkBatchSize = 1 ,      bulkInsertArraySize = 1 ,      idleBufferWriteDelayMSec = 1 ,      bufferAgeLimitMSec = 1;