Dynamic data transformation

Seven Stars

Dynamic data transformation

so we have requirement to convert bunch of XML's into SQL however we need to transform data in between based on condition set, 

such as:

condition 1 : if clientname = 'a' and processtype = 'n' then columna = 'hello from a & some additional text'

condition 2 : if clientname = 'b' and processtype = 'm' then columnb = 'hello from b & some additional text'

condition 3 : if date between '1/1/2017' and '1/1/2018' then columng = 'hello from c & some additional text'

condition 4 : if flag = 'retrunclient' then columnx = 'hello from x & some additional text'

and so on...

 

we have bunch of condition like this & depending on condition, we need to update value for different column each time.

 

most of the XML's have different schema & depending on the XML, we can load data into appropriate sql table however we need something that we can use as generic transformation.

 

We dont want to write all these condition in tMap as we need some flexible solution that allow us to change above condition without changing the job.

 

We are using enterprise version 6.4. Is there something already available in Talend that we can use? drools? validation rules?

 

Seven Stars

Re: Dynamic data transformation

I would really appreciate any help on this subject, really looking forward to some direction on this in Talend

Seven Stars

Re: Dynamic data transformation

@xdshi I would really appreciate your on this to guide us in right direction.

Moderator

Re: Dynamic data transformation

Hello,

Could you please let us know  if this related topic helps?

https://community.talend.com/t5/Design-and-Development/tMap-with-Dynamic-schema/m-p/43409#M12970

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Seven Stars

Re: Dynamic data transformation

Thanks for your response,

this is for dynamic schema, out schema is predefined however we want transform data based on given condition. and since there will be so many conditions like that, We dont want to write this in as 'if or else' condition. Is there any better way to manage this scenarios?

 

for example:-

condition 1 : if clientname = 'a' and processtype = 'n' then columna = 'hello from a & some additional text'

condition 2 : if clientname = 'b' and processtype = 'm' then columnb = 'any other text'

condition 3 : if date between '1/1/2017' and '1/1/2018' then columng = 'any other text'

and so on...

 

Six Stars

Re: Dynamic data transformation

do you above if else conditions has any pattern or business use case defined?

if you can defined your use case and tranlate into transfomation rules , it would easy to use dynamic or automated logic. try tRules if that can help.
Regards
Abhishek KUMAR
Seven Stars

Re: Dynamic data transformation

There is no common pattern really, it could be any pre-defined condition.

 

I see that tRule only takes 1 or 2 field as input & process those as output. How can I use that with whole dataset?

 

Four Stars

Re: Dynamic data transformation

I have a similar problem where I have an input row with around 250 columns and I need to apply over 1000 validation rules to this data. The output is a record id and an error number. Validation can be a condition of a number of input fields. I can't see any way to apply multiple conditions to an input and get multiple errors for output.

Six Stars

Re: Dynamic data transformation

you could use tMap for this.

Develop each validation rule as Variable , 1)result variable should have error message or "" as a result .
valDef1 = ""
ValDef2 = "error"
valDef3 = ""

2) Create another var like result = valdef1+ valDef2 + Valdef3

4) create your Tmap out only if result result is not null .

It is tricky if you want to keep your validation rule outside talend job.
Regards
Abhishek KUMAR
Four Stars

Re: Dynamic data transformation

We want the rules to be managed by the business. We currently have a rules engine that uses java script rules but it is slow compared to embedded rules.

Six Stars

Re: Dynamic data transformation

Try Drool oprion

https://help.talend.com/reader/uwwCVAHxWDS6l5fZQ~lVYA/Bnm0~QJqvDLhp~AsATmITA

Apart from the Drools BRMS embedded into Talend Administration Center, you can also install and use Red Hat BRMS or JBoss BRMS to manage business rules. For further information, see Red Hat JBoss BRMS documentation.

I never used these but looks like this could solve your problem.
Regards
Abhishek KUMAR
Four Stars

Re: Dynamic data transformation

I was trying to use tRules but it only seems to allow 2 columns in the Schema for the rule so no use if validation condition applies to more than 2 columns

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.