Creating messages to Kinesis per data source transaction

[Shingo]

I’m currently investigating Qlik Replicate as a solution to create and send messages to AWS Kinesis, when a transaction is done on a RDB data source (AWS Aurora/PostgreSQL).

Multiple SQL statements will be executed in the transaction, but a message must be sent per transaction, NOT per SQL statement. For example, detecting the final SQL statement execution, and sending messages ONLY on this condition would be a viable solution, if possible.

Can Qlik Replicate implement the above requirement natively, or are other components/custom solutions necessary?

[Ted_Manka]

Hello @Shingo ,

Thank you for submitting this question.  It sounds very interesting.  Would you please flesh out your scenario and question a bit more on the final vs. non-final sql.  I am not sure I understand your example.

Thank you,

Ted Manka

[Shingo]

Hi @Ted_Manka , and thank you for your quick response.

Let’s say for example, for the sake of this discussion, there is a parent table PURCHASE_ORDER and a child table PURCHASE_ITEM that has a one too many relationship.

Example table definition:

PURCHASE_ORDER

id

delivery_date

customer_id

PURCHASE_ITEM

purchase_order_id

quantity

Now let’s say an insert to PURCHASE_ITEM and an update to PURCHASE_ORDER (for example delivery_date) are processed with different SQL statements in the explained order but in one transaction. The "final SQL" I was referring to, would be the update to PURCHASE_ORDER SQL in this example.

Now in a downstream analytics system, we want a message that contains the full description of a purchase which is a join of PURCHASE_ORDER and PURCHASE_ITEM. We also want one message per transaction (a message that contains both the new purchase item AND the modification of the delivery date).

Here is a simple summary of my understanding of what Qlik Replicate can do.

1. Qlik Replicate sends one message per change (per SQL? Per row?).
2. Qlik Replicate can do joins with transformations for each change.
3. Each message will contain a transactionLastEvent attribute in the message header, so the downstream system could identify the message that contains all inserts and updates.

The problem with this is approach is that half baked and wasted messages are sent to AWS Kinesis, which leads to unnecessary costs because AWS Kinesis bills per message, and hence wondering if there was a more ideal way to implement this in Qlik Replicate.

[Ted_Manka · Accepted solution]

Hello @Shingo ,

I checked in with my team and it looks like you could use materialized views on Oracle source or if an equivalent on another database.  This would ensure that your requirements are satisfied before replicate would pick up the data.  I hope this helps.

Thank you,

Ted Manka