topic Re: Insert multiple unique records into MySQL in Talend Studio

Insert multiple unique records into MySQL

Anonymous — Sat, 16 Nov 2024 13:24:47 GMT

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?

Re: Insert multiple unique records into MySQL

Anonymous — Tue, 01 Jun 2010 10:40:35 GMT

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

Re: Insert multiple unique records into MySQL

Anonymous — Wed, 02 Jun 2010 12:57:06 GMT

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

Re: Insert multiple unique records into MySQL

Anonymous — Wed, 02 Jun 2010 13:44:20 GMT

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.

Re: Insert multiple unique records into MySQL

Anonymous — Wed, 02 Jun 2010 14:50:06 GMT

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.

Re: Insert multiple unique records into MySQL

Anonymous — Wed, 02 Jun 2010 17:04:18 GMT

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.

Re: Insert multiple unique records into MySQL

Anonymous — Mon, 07 Jun 2010 17:43:39 GMT

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

Re: Insert multiple unique records into MySQL

Spretorius — Sun, 05 Dec 2010 18:29:01 GMT

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.