topic Re: Dynamic schema table updates with computed columns in Talend Studio

Dynamic schema table updates with computed columns

Papademuchos — Sat, 16 Nov 2024 00:08:23 GMT

I'm trying to move data from one database to another. I have over 180 tables that are mostly similar, however 26 of them have different columns from the rest.

I've played around with dynamic schema and have that working, however I ran into a problem with SQL computed columns. As expected, a computed column can't be directly inserted to or updated.

Is there any way to exclude these columns from the dynamic column list? Ideally I'd like to exclude them based on the computed property from SQL. Or, I can hard-code a list of the column names if necessary... but I'm not sure how to "extract" a hard-coded list of column names from the dynamic list and exclude them from the update statement.

Any ideas would be appreciated!

Re: Dynamic schema table updates with computed columns

Anonymous — Thu, 20 May 2021 01:01:07 GMT

This blog might be helpful, if you know some Java knowledge, you can hard-code to get properties of dynamic schema and update them.

Regards

Shong

Re: Dynamic schema table updates with computed columns

Papademuchos — Thu, 20 May 2021 18:59:44 GMT

Thanks shong for your response - that's helpful. After playing around a bit more I was able to figure out how to do what I want using tExtractDynamicFields and adding in a hard coded list of columns that I want to ignore in a tMap that follows. But the blog shows an option that would let me play with the columns based on column properties. I'll circle back around and try that out!

For anyone else that finds this thread and may be wondering what I did, here are my steps:

tDBInput to read all rows from my source table
tExtractDynamicFields lets me list out the columns I want and keep all other columns in a dynamic field
tMap let's me indicate which fields to pass through
tDBOutput to Insert or Update to my target table

1 tDBInput: - use a "Select *" to retrieve all columns

My schema looks like this:

In my example, all 140 tables have id, code & description. The tDBInput component is smart enough to load those columns into my defined columns, and put "everything else" into the "other_columns" dynamic field.

2 In the tExtractDynamicFields component I've included my common fields, all of the potential "computed" type fields that might be present in my tables, and the "other_columns at the end. Again, Talend is smart enough to extract those columns present in my source "other_columns" dynamic field, map them to the new column name that matches, and leave everything else in the "other_columns" field at the end.

3 In tMap I'm simply leaving the computed columns behind and only passing through the ones that I want. "other_columns" has everything else for that particular table.

4 Finally tDBOutput does an Insert of Update with known columns, and the unknown columns ("other" -> dynamic type)

I hope this helps someone! It took me a bit of research to put it together.

Re: Dynamic schema table updates with computed columns

Anonymous — Fri, 21 May 2021 06:46:24 GMT

Great, thanks for sharing detailed steps to community!

Regards

Shong