Transaction management

CloverDX Platform
1

Hi,

I have a question about the possibility to create a graph for the following context:
We have the following elements:
- Database table with the input data (denormalized, source data) – we can call it SOURCE_TBL.
- Database table for the rejected data – REJECTED_DATA_TBL
- Database table for the reasons of rejection – REJECTION_REASONS_TBL (can have more than one record per rejected record).

The graph we want to create is like this:
1.DBInputTable reads everything from SOURCE_TBL
2.DBInputTable pass one record at a time to a custom component that validates each record (ValidationComponent).
3.ValidationComponent validates a record at a time and, if not valid, we must do the following:
3.1 Write the slightly transformed record to REJECTED_DATA_TBL

3.2 Write the reasons of rejection to REJECTION_REASONS_TBL (can have more than one record per rejection)

3.3 Delete the record from SOURCE_TBL

The tasks 3.1, 3.2 and 3.3 must be done in a transaction (a transaction per record rejected). We would like to delegate these tasks to a custom component other than ValidationComponent for the sake of separating the responsibilities of components.
It is fairly simple to do that kind of work by a simple java class but we would prefer to stay in the Clover World.
Is there a way to do that?

Thanks

2

Hi,
if you send all data from Validation Component in one record you can do it with DBOutputTable component:

<Node dbConnection="Connection0"  id="DB_OUTPUT_TABLE0" sqlQuery="insert into REJECTED_DATA_TBL values ($transformed_field1, $transformed_field2, $transformed_field3);
insert into REJECTION_REASONS_TBL values($rejectionReason);
delete from SOURCE_TBL where field1=$original_field1,field1=$original_field2, field3=$original_field3;" type="DB_OUTPUT_TABLE"/>

It does all 3 statements in one transaction.

3

Hi,
I cannot send all data from Validation Component in one record because I can have more than one record to insert into REJECTION_REASONS_TBL for each record transferred to REJECTED_DATA_TBL.

In fact, my question could be asked in another way:
Can I send a data structure representing one to many relationships (ie. one record for REJECTED_DATA_TBL and several records (1…n) for REJECTION_REASONS_TBL) from one component (ie. Validation component) to another component (ie. a database write custom component) and still treat them as a unity. I know that I can do that by using something as a Merge component but in that case the Merge component will have to wait for all records to be processed before starting to treat the data. I don’t want to do that. I want to treat one input record (becoming one to many relationships) at a time.

My scenario would be as following:
1.DBInputTable reads everything from SOURCE_TBL
2.DBInputTable pass one record at a time to a custom component that validates each record (ValidationComponent).
3.ValidationComponent validates a record at a time and, if not valid, create a rejected record and several rejection reason records (1…n) and sends them to a Database Write custom component.
4. Database Write custom component takes the input corresponding to one record from SOURCE_TBL (one rejected record and several rejection reason records) and inserts them in the database in a transaction
5. And so on, for each record from DBInputTable

Thanks

4

Hi,
I concern that is not possible in current Clover.ETL development level. You can write sets of records to different flat files (using partitionKey and partitionFileTag attributes on DataWriter), but not to database in one transaction. We plan to extend metadata to be more level structure in the future; in such case you could have one record on one field, but in current version it’s impossible :frowning: