topic Re: I need to find a component that ... in Talend Studio

I need to find a component that ...

MarkM1 — Sat, 16 Nov 2024 00:57:56 GMT

I'm looking for a component that will set defaults and check data lengths for me and output a warning or error schema if any field fails the checks. This should be easy to make generic for any database, but the specific one I am working with is AS400 DB2.

The component should:

Apply schema defaults if a value is null and the column does not allow nulls
String processing
- Convert from String to CHAR or VARCHAR and optionally trim on the right if the target is a varchar.
- Test the length of the string and truncate automatically if necessary, optionally duplicating the record to the error schema and indicating which field was in error.
Numeric processing
- Convert automatically between all numeric formats including BigDecimal and BigInt.
- Test the number against the target precision and scale, if overflow occurs provide for a literal replacement value, and optionally duplicating the record to the error schema and indicating which field was in error.
- On truncation of significant decimal digits, optionally duplicate the record to the error schema and indicate which field was in error.

Is there a component that does this, or would it have to be a custom component? It seems that it would be a useful component, and easy to make generic.

Re: I need to find a component that ...

manodwhb — Sun, 13 Dec 2020 07:24:35 GMT

@Mark Murphy , we do not have direct component, but you can design a custom component your own to do these functionalities.

Thanks,

Manohar

Re: I need to find a component that ...

Anonymous — Mon, 14 Dec 2020 05:37:57 GMT

Hello,

Is it possible for your to achieve your goal via talend job instead of a generic component?

Best regards

Sabrina

Re: I need to find a component that ...

tnewbie — Mon, 14 Dec 2020 17:37:21 GMT

@mmurphy, In my view, a quick workaround for your ask is to go the XML/XSD route if possible. Convert your incoming data to an xml and run that against a pre-defined xsd that can enforce all kinds of validations that you are asking for. In addition, if you go the xsd route, capturing the error messages and exact discrepancies between expected and actual data will be seamless and your main stream of process would still move forward and push the errored data to go the rejected route.

Hope this helps a bit

Re: I need to find a component that ...

MarkM1 — Wed, 16 Dec 2020 15:33:18 GMT

I'm doing this for database migration. Some of the databases we migrate are massive, converting to XML seems like it would take way to much memory and processing time as XML is extremely inefficient when it comes to content vs. metadata.

Re: I need to find a component that ...

MarkM1 — Wed, 16 Dec 2020 15:36:35 GMT

Yes, I do all this in tMap components. Over and over again. Once I have done a thing enough times, I look for a way to automate it. All of the needed data is available in the schemas. I just want a nice component to drop in to do this.

Re: I need to find a component that ...

tnewbie — Thu, 17 Dec 2020 14:31:32 GMT

My 2 cents:

If you think through and estimate the processing overhead, rather than trying to validate every column value at each record level, XML validation happens at a record level and would relatively put lesser processing overhead when compared to custom coding where you converse every incoming row to multiple rows before you can qualify a row as valid and take it to next level of processing.

Even if your data/databases are massive, your requirement of data/schema/metadata validation should happen at column level.