Change DB Column Name in DB Output dynamically using variables

[tnewbie]

Hello All,

I am in the process of building a generic job that could replicate / sync data between two on prem databases. The challenge I am facing is with the key columns during updates, because when I use a tExtractDynamicField component, I am supposed to name the column and mark/checkbox as key column. I can use a dummy name to this key column and dynamically pass the correct DB column name to the component if I am allowed to change the DB Column Name. After lot of strggle / research on this issue, somewhere in the document I read that {context.columnname} is allowed as the DB Column name. But when I tried using it, Talend is not able to substitute context.columnname with the value, so the whole purpose is getting defeated.

Exploring further on the community, I see that some one with a similar issue were using tJavaFlex to over come this issue, being a non Java guy, I dont know how to use tJavaFlex in this context.

Can you please throw some light on the issue and share any suggestions and recommendations

[Banupriya]

What is the generic solution that you have used sync the two database? I am also working on the same same issue.

Different tables have different primary keys . So not able to use the tdboutput component for update insert operation.

How r you fetching the different key values from dynamic schema and loading that in context? ​

[Anonymous]

Hello,

Could you please let us know if this related topic is what you are looking for?

Best regards

Sabrina

[tnewbie]

Sabrina,

I guess you missed including the related topic... can you pls include the url for that related topic

[tnewbie]

Hello All,

Any thoughts on how to substitute a DB Column name with a variable during run time?

[Anonymous]

Hi

You can only change the column properties including column name, DB type etc when using Dynamic schema.

Below is an example showing how to get the column properties on a tJavaRow and update them.

Java code on tJavaRow:

Dynamic columns = input_row.data;

for (int i = 0; i < columns.getColumnCount(); i++)

{

DynamicMetadata columnMetadata = columns.getColumnMetadata(i);

String col_name=columnMetadata.getName();

String col_type=columnMetadata.getType();

if(col_name.equals("name")){

columnMetadata.setName("new_name");

}

if(col_name.equals("age")){

columnMetadata.setType("id_Integer");

}

}

output_row.data=columns;

In this case, I change the column 'name' to a new column name and change data type of column 'age' to 'Integer' type. Copy the code and modify it according to your specific needs. 

Can you try?

Regards

Shong

[Anonymous]

Hello,

Sorry for that.

It was failed to paste the URL in this thread.

Please have a look at this one:

https://community.talend.com/s/question/0D53p00007vCjRACA0/dynamically-change-db-connection

Best regards

Sabrina

[tnewbie]

Thankyou @Shicong Hong​ 

This approach wouldn't work for me, because my tables are pretty huge volumes ranging from 25million to 100million records in each table and as we know parsing of Dynamic schema in itself is relatively slow, so if i try JavaRow for each row, then it is defenetly a pretty big overhead.

On the other hand, I was able to handle the issue with a tDBRow, where PK are assigned by a table driven approach and it seems to be working well. I have tested close to 30 tables so far with max volumes upto 65million and each table is taking around 30mins to 2hours to finish, which I think is not bad for a dynamic schema.

Thanks again for your help

[Anonymous]

Yes, it is not a approach to access the schema properties and change the properties for a huge volumes of data set. Anyway, you have found a way to do it more efficient. 👍

Regards

Shong

[tnewbie]

Thanks Sabrina... my request was different. Shongs post was more to do with switching DB connector from SID to SERVICE Name for Oracle DB. Interestingly shong was replying to my request through that post 4 years back 🙂