Update tables from CSV to MySQL when imported with dynamic schema

[Anonymous]

Hi there,

I am trying to update rows/values of a table where changes (modified/added/deleted) has been been made without rewriting data for the whole table.

If possible, I want the update process to work in the following way. 

I think a key is needed here, but is it possible to do it without making changes to the existing table?

 Origin tableChanges (CSV Upload)Expected Result

With the job design below, I can create tables and import it to database, though it doesn't work with update.

** I used dynamic schema when mapping input and output. **I added one dynamic column in the schema.

input

It works well with insert, but not for update as a key is needed.

Exception in component tDBOutput_2 (test02)
java.lang.RuntimeException: For update, Schema must have a key

However, not all tables have keys and I can't make changes to the source tables, as it's a company data.

Is there a way to make this work?

tMapoutput

Please let me know if you have any question.

Thanks alot!!

[manodwhb]

@crotmn , to update you should have key I believe you can define key on the last_name. for the the update you should use last_name and dynamic column.

[Anonymous]

@manodwhb 

The thing here is that there is a gonna be hundreds of CSV files I'm going to be dealing with all have different schemas and column names (as well as different number of columns and data types).

So I'm not sure how can I can define a key here.

[manodwhb]

@crotmn ,you can define the key that column if has in all the files. otherwise the way you want will not support with Dynamic schema.

[Anonymous]

@manodwhb
i see so there's no way to update without defining a key, right?

I tried adding an id column and defined it as a key, but I think I did missed something here...

Because it's not working...

Here's how I mapped the columns

Please let me know how I can improve this.

For data type, I initially set the data type as integer but it didn't work

Couldn't parse value for column 'id' in 'row1', value is '62.82.116.210'.

Details: java.lang.NumberFormatException: For input string: "62.82.116.210"
[ERROR]: local_test.test02_0_1.test02 - tFileInputDelimited_1 - Couldn't parse value for column 'id' in 'row1', value is '62.82.116.210'. Details: java.lang.NumberFormatException: For input string: "62.82.116.210"

So I tried change from integer to string, but still it didn't work.

[FATAL]: local_test.test02_0_1.test02 - tDBOutput_2 Duplicate column name 'id'
java.sql.SQLSyntaxErrorException: Duplicate column name 'id'
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)

------------------------------------

Actually when I went back to check on my original database, I found that there were multiple primary keys (in MSSQL), however, some keys weren't properly migrated to MySQL when performing data migration in Talend,

so I initially thought there some tables doesn't have a key lol...

Here's a question.

1) Is there a way to make sure that primary keys would are migrated correctly? 

MSSQL => MySQL

2) In case there are multiple keys, how can I map the columns correctly, given that I wanted do it dynamically.

 The actual database contains over hundreds tables with different schemas (no. of columns and data types as well)

multi-key examples

[manodwhb]

@crotmn , select the multiple columns as key,since you are using dynamic ,you need to check is that feasible for your use case.

[Anonymous]

@manodwhb
can you elaborate more about this?
>> select the multiple columns as key, since you are using dynamic

at first I thought you mentioned about define a key and using dynamic column
>> to update you should have key I believe you can define key on the last_name. for the the update you should use last_name and dynamic column.

so here I create a new column called 'id' in all csv files, and I have them in my schemas along with dynamic column
and I also mapping the columns from input to output
(please check my previous post, I have a screenshot there)

[manodwhb]

@crotmn , Key columns need to specify in the schema.

[Anonymous]

@manodwhb 

i did

please check here

Here I have id as a key column so I added it in my schema here.

Please let me know else I need here.

[manodwhb]

@crotmn , you need to add below columns as this table is target .