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.