topic Re: Numeric Sequence Generation function giving duplicate numbers in Talend Studio

Numeric Sequence Generation function giving duplicate numbers

TomG1 — Sat, 16 Nov 2024 09:40:01 GMT

Hi,

I have created a bigdata spark job.

I am reading rows from a file.

Basically I am generating an id for each record in the file in tmap using the following code.

Numeric.sequence("IDGen", 1000000, 1)

I checked the file and found duplicate IDs generated.

Why is this happening ?

Please note that this is a bigdata spark job and i am running this job in a spark cluster.

Is there a workaround for this issue?.

Thanks

Re: Numeric Sequence Generation function giving duplicate numbers

TRF — Wed, 07 Jun 2017 15:35:41 GMT

Can you share your job?

How the duplicates are distributed over the records?

Why do you initialize the sequence with 1,000,000?

Re: Numeric Sequence Generation function giving duplicate numbers

TomG1 — Wed, 07 Jun 2017 18:07:07 GMT

out of 10000 numbers generated by the talend sequence function , 5 numbers are duplicate.

what i meant is , I can see count of 5 different numbers as two

ex:

ID/number duplication_count

123456 2

324532 2

There is no special reason for initializing with 1,000,000

Re: Numeric Sequence Generation function giving duplicate numbers

cterenzi — Wed, 07 Jun 2017 20:23:56 GMT

If you initialize to 1,000,000 how are you getting values less than 1,000,000?

Re: Numeric Sequence Generation function giving duplicate numbers

TomG1 — Thu, 08 Jun 2017 05:35:05 GMT

it is just an example to make people understand...

The real duplicates are different...

Any way the point is , I am getting duplicates

Re: Numeric Sequence Generation function giving duplicate numbers

TomG1 — Thu, 08 Jun 2017 05:36:13 GMT

ok... i will share how duplicates are distributed in a short while

Re: Numeric Sequence Generation function giving duplicate numbers

TomG1 — Thu, 08 Jun 2017 08:00:47 GMT

this is how duplicates are distributed.

ID Count of ID generated in the output file

1000815 2
1006072 2
1005490 2
1005905 2
1000889 2
1007748 2
1000246 2

Re: Numeric Sequence Generation function giving duplicate numbers

TRF — Thu, 08 Jun 2017 08:43:10 GMT

Hi,

It looks very strange.

Can you share your job design + configuration for any component where the sequence is calculated.

Also, how are the duplicates identified? (with details)

Is there any value for which there is more than 2 duplicates?

Re: Numeric Sequence Generation function giving duplicate numbers

TomG1 — Thu, 08 Jun 2017 09:12:07 GMT

here is the job design

Basically the job is reading from a source file . it attaches every record in the source file with an ID in tMap. Finally new records from tMap are stored into another file.

The subjob is checking for duplicates. the subjob groups all records in the output file of previous subjob on the basis of ID. ID along with count is stored into an output file .

There is no ID having more than 2 duplicates.

The following are the duplicates as i share earlier

ID Count

1000815 2
1006072 2
1005490 2
1005905 2
1000889 2
1007748 2
1000246 2

Re: Numeric Sequence Generation function giving duplicate numbers

TRF — Thu, 08 Jun 2017 09:24:31 GMT

Nothing strange in the job design which is very simple.

Last question: for duplicates records, except the UniqueID, are other fields duplicated or not?

Re: Numeric Sequence Generation function giving duplicate numbers

TomG1 — Thu, 08 Jun 2017 09:56:02 GMT

No . the other fields are not duplicated. I just compared two rows whose IDs are same.

Is this a drawback of big data spark Job?

Re: Numeric Sequence Generation function giving duplicate numbers

TRF — Thu, 08 Jun 2017 10:57:04 GMT

I've just tried the same job with 50000 records and get no duplicate records.

I use TDI 6.3.1

Re: Numeric Sequence Generation function giving duplicate numbers

TomG1 — Thu, 08 Jun 2017 11:35:04 GMT

do you create a standard job or big data job?

we have to create a bigdata spark job and then run this job in spark cluster.

I am using talend big data platform 6.2

Re: Numeric Sequence Generation function giving duplicate numbers

TRF — Thu, 08 Jun 2017 11:41:13 GMT

No, I just use standard job.
Maybe the problem is due to cluster mode.
I don't know very well how it works but if the job execution is distributed over many nodes, I suppose it should be a problem for sequence calculation which is an in memory operation (probably not shared between nodes).

Re: Numeric Sequence Generation function giving duplicate numbers

cterenzi — Thu, 08 Jun 2017 14:00:55 GMT

Agreed, this is probably a threading issue, and the Numeric routines likely aren't threadsafe. You may have to create your own sequence.

Re: Numeric Sequence Generation function giving duplicate numbers

Anonymous — Thu, 24 Aug 2017 12:25:39 GMT

Hi Tom,

first of all sorry for my English ...

happened also in a Talend Data Integration job, during a TMap row number assigner ...

it is very strange ... the job has running for many days ed worked many times ...

when counting 500 rows it produced 20 duplicated row numbers ...

What workaround you founded ?

Cattura.PNG

Re: Numeric Sequence Generation function giving duplicate numbers

TomG1 — Thu, 24 Aug 2017 17:24:05 GMT

For me the error occurred in talend big data job.

I found a workaround using tsql row.

tsql row will be connected with two rows. First row will be the incoming records.

The other row will be containing the last generated max sequence.

in tSQLrow i used a row_number() to generate a unique number for each incoming row.

ex:query

select (row_number() over()) + row2.lastseq from row1,row2

Re: Numeric Sequence Generation function giving duplicate numbers

TomG1 — Thu, 24 Aug 2017 17:25:33 GMT

if your talend is licensed and it is happening in data integration job, then raise a bug with talend

Re: Numeric Sequence Generation function giving duplicate numbers

vimal_kumar — Tue, 23 Jan 2018 16:03:21 GMT

Hi Tom/All,

is there a way to generate a fixed length/Cyclic sequence in Talend like the way we can do in Oracle sequencer.

Example: Say, i use something like this to generate sequence and i want it to be always of size 8 in length.

Numeric.sequence("s1",1,1)

I want output as :

00000001

00000002

00000003

Re: Numeric Sequence Generation function giving duplicate numbers

TomG1 — Mon, 05 Feb 2018 10:02:03 GMT

Hi,

You can generate sequence numbers using tsqlrow like I mentioned in the above reply.

then you can connect output of tsqlrow to tmap and append zeros to the left of the number using lpad() in java.