Insert multiple unique records into MySQL

One Star

Insert multiple unique records into MySQL

I have a MySQL input (Table 1) with a field which I am mapping to a unique field.
The input field contains duplicate values for the input, which can not be saved to the table (Table 2) due to MySQL duplicate error message.
What I am trying to do is when an insert fails alter the input value and try the insert again.
For example the output for an input is:
Table 1 input:
id name
1 abc
2 xyz
3 abc
4 efg
5 abc
6 abc
Table 2 output:
id name
1 abc
2 xyz
3 abc#1
4 efg
5 abc#2
6 abc#3

I have looked at tLoop as a while loop but not sure how I use a condition of not inserted based on a MySQL query.
I am using the following query to find the next # value, where <current name> is the value in 'Table 1 name', e.g. abc
select count(*) from table 2 where name regex '<current name>#+$'

Which tells me what the next # value should be, but putting this all together into a job using a tMap has me scratching my head.
Am I trying to do the impossible?
One Star Lie
One Star

Re: Insert multiple unique records into MySQL

Hi
Can't you remove the key on the name column ?
You should be able to insert duplicate values.

In case that doesn't solve your problem :
Create a routine that checks if the value exists then returns the formatted value (MyRoutine.check(myString) = "myString#X"). Use this routine on the "name" column.
And the "next" value can be stored in a file/db, this should make it easier to get.

Regards,
Lie
One Star

Re: Insert multiple unique records into MySQL

Removing the unique key would allow for the insert but I would like to know how to go about solving the problem by using a rountine to update the name column.
How do I go about getting the rountine to run the query above and pass through the <current name>? The <current name> will be based on the current row being mapped, so I can't create a SQLInput for it.
Thanks
One Star Lie
One Star

Re: Insert multiple unique records into MySQL

package routines;
import java.util.HashMap;
import java.util.Map;
public class SNumeric {
private static final Map<String, Integer> seq_Hash = new HashMap<String, Integer>();
private static final Map<String, Map<String,Integer>> key_Hash = new HashMap<String, Map<String,Integer>>();
/**
* return the value corresponding to the key or an incremented numeric id
*
* {talendTypes} int | Integer
*
* {Category} Numeric
*
* {param} string("s1") sequence identifier
*
* {param} string("key") key
*
* {param} int(1) step
*
* {example} sequenceKey("s1", "abc", 1) # "abc", "abc#1", "abc#2", ...
*
* {example} sequenceKey("s1", "abc", 3) # "abc", "abc#3", "abc#6", ...
*
*/
public static String setUniqueId(String seqName, String key, int step) {
if (seq_Hash.containsKey(seqName)) {
if (key_Hash.get(seqName).containsKey(key)) {
return key + "#" + key_Hash.get(seqName).get(key);
} else {
Integer incValue = seq_Hash.get(seqName) + step;
seq_Hash.put(seqName, incValue);
key_Hash.get(seqName).put(key, incValue);
return key + "#" + incValue;
}
} else {
seq_Hash.put(seqName, startValue);
key_Hash.put(seqName, new HashMap<Object, Integer>());
return key;
}
}
}

Here is a routine that should fit your needs.

Instead of mapping table1.name to table2.name,
map SNumeric.getUniqueId("seq", table1.name, 1) to table2.name.
To get the current value of current name, you should have a table :
sequence    label    id
seq abc 4
seq xyz 3
seq2 abc 1

Then get all those informations and create an "init" routine that initializes all the sequences.

=== Second idea (think it's a better (easier) one than the first) ===
You also may be able to "group" datas with a tAggregateRow and count rows.
You should have something like :
id name count
1 abc 3
2 xyz 1
3 efg 1

Then you create a schema as below :
                                                                                
|
v
----Iteration--->----Iteration--->----row--->----row--->

The tLoop use the "count" column of the tAggregateRow. Then you should be able to treat rows individually and use a tMySQLInput for each.

Sorry if my explanations aren't clear.
I'll try to use printscreens if you don't understand.
One Star

Re: Insert multiple unique records into MySQL

Thanks I will give those methods a go, I'm not totally sure but it might become clearer as I try and set it up. I will report back how it goes.
One Star

Re: Insert multiple unique records into MySQL

I've managed to get it to work a little bit, here's my screenshot.
I wasn't sure how to setup the tLoop though.
What it does at the moment is:
Table 1 input:
id name
1 abc
2 xyz
3 abc
4 efg
5 abc
6 abc
Table 2 output:
id name
1 abc
2 xyz
3 abc#1
4 efg
5 unique key error tried to use the name: abc#1
6 unique key error tried to use the name: abc#1

This seems to be because the group by is on the name, which doesn't allow for abc and abc#1 to be grouped together for the count so that the next value would be abc#2.
The value after the # is the count from the group by.
Thanks for your help so far but think I need some more guidance.
One Star

Re: Insert multiple unique records into MySQL

I've had another look at this and I am still struggling any help would be greatly appreciated, thanks.
One Star

Re: Insert multiple unique records into MySQL

Hi I have to create a job where i take
a Fist_Name and Last_Name from a mssql db
and combine them so that it creates a username
Ex If the First_Name = John and the Last_Name = Perry
it should output it as JPerry. But my problem lies that when I created
that user name it should then check the LDAP AD if such a user name already exists
and if it does my job should then add a 1 after the username to = JPerry1 then it must search
the LDAP AD agian and if theres already a JPerry1 ist should then change to JPerry2 and so on
until theres a unique user name and then it should update the new username in LDAP.
I have already got my job to convert the to username and check if such a name already exists,
my output will then show the usernames that already exists in LDAP AD, now how can I use Talend to add a 1
after the username and loop the check again until a unique username is created.
If someone can help me with that I will appreciate it very much.

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch