topic Re: Schema Data Type Conversion in Talend Studio

Schema Data Type Conversion

mborlo15 — Fri, 15 Nov 2024 23:22:38 GMT

Situation

I am using Talend Data Integration. We often like to make our Talend jobs versatile for many situations.

We have a child job that is passed a query from a parent job via a context variable. Although the fields in the SELECT clause of the query remain the same, the tables can differ. Sometimes the tables have different data types for the same fields.

Example

For instance, some tables will store a value with the DATE datatype and others will store the date as a VARCHAR. Some might hold some values as INT and others as VARCHAR, etc. This is for the tDBInput component.

Similarly, what would happen if Talend's tDBOutput component uses Integer data type, but the database table in reality stores values as NUMBER? Will that cause an unexpected value to be written to the database?

Question

Essentially, the question is: will Talend automatically cast values to match the component schema, and will there be complications when reading from and writing to databases if the component schemas (whether tMap, tDBOutput, tDBInput, tFileInputExcel, etc.) don't match what's in the database?

Re: Schema Data Type Conversion

Anonymous — Mon, 27 Dec 2021 03:52:22 GMT

Hello,

From your description, it seems you are looking for dynamic schema mapping.

You could use dynamic schema to move the data from source to target where target columns names are different from source.

For more information, please have a look at this KB article to see if it is what you are looking for.

https://community.talend.com/s/article/How-to-process-changing-data-structure-ExK7j

Best regards

Sabrina

Re: Schema Data Type Conversion

mborlo15 — Tue, 28 Dec 2021 21:16:12 GMT

Hi Sabrina, thank you for your reply. Unfortunately, a dynamic schema will not work for us in this scenario. Since this is the case, will a fixed schema in a tMySQLInput component that does not necessarily match the MySQL database cast everything properly? No errors have been thrown thus far.

The flow is like this

tMySQLInput --- tMap --- tOracleOutput

where tMySQLInput schema data types may not match the underlying MySQL table

Re: Schema Data Type Conversion

Anonymous — Wed, 29 Dec 2021 03:34:06 GMT

Hello,

Would you mind posting some trouble screenshots here which will be helpful for us to address your issue?

And could you please also clarify in which Talend version/edition you are?

Best regards

Sabrina

Re: Schema Data Type Conversion

mborlo15 — Fri, 07 Jan 2022 06:25:02 GMT

Hi Sabrina, I was able to test our solution and Talend seems to be automatically casting everything as expected. Thank you for your help. The dynamic schema was an interesting idea, though. Can you explain the warning about it in Talend documentation? It makes the feature sound unreliable.

I am using Talend Data Integration 7.3

https://help.talend.com/r/9bBURCEt_t~lUHE3DeE2LA/N6SaNijL0j9L~Es~kvlqZA

Re: Schema Data Type Conversion

gjeremy1617088143 — Fri, 07 Jan 2022 07:52:22 GMT

Hi,I think the warning is about the fact that cause dynamic shema is dynamic it will always propagate the data you send, so if your input data structure change, the changing data strucutre will be send to the output and if you haven't change the data structure of the target it will not fit.