on 04-13-2016 10:14 AM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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 ;
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.
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 ;
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?)
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 ;
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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
81 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.