topic Re: Generation of Surrogate key for Incremental Load in Talend Studio

Generation of Surrogate key for Incremental Load

Anonymous — Mon, 11 Mar 2019 11:08:15 GMT

Hi,

I have a column in a table in table for which am generating a surrogate value by using Numeric.sequence("S1",1000,1). This works fine for the 1st time but for the second time, I want it to take max value form the last generated surrogate value. Here I am taking the max of that column into a context variable and using in the tmap Numeric.sequence("S1",Max(Column_Name),1).

Here I am facing an issue. Context variable is not accepting the int value in it. I am getting error.

Can you please help me solve this issue.

Thanks in advance

ShaX

Re: Generation of Surrogate key for Incremental Load

manodwhb — Mon, 11 Mar 2019 11:11:51 GMT

@ShaX ,in th pre job you need to take the max key form the target table and load to context using tcontextload.you can use that context value this way in downstream.

Numeric.sequence("S1",context.DIm_DW_CUSTOMER_ID,1)

Re: Generation of Surrogate key for Incremental Load

vapukov — Mon, 11 Mar 2019 11:14:32 GMT

Hi,

it absolutely not clear from the description - what you really do?

in any variants, current form:

Numeric.sequence("S1",Max(Column_Name),1)

is wrong

if you store max value (not seen - how) to context variable, it must be:

Numeric.sequence("S1",context.CONTEXT_VARIABLE_NAME,1)

no MAX(), context. - mandatory

add:

as the default value for context variable use 1, it resolve null value warning

Re: Generation of Surrogate key for Incremental Load

Anonymous — Mon, 11 Mar 2019 11:22:23 GMT

Hi Manohar, I am trying to do the same. but my question is : 1. If I declare variable as int am getting "Null value will be used for context parameter DIM_DW_CUSTOMER_ID: For input string: "context.DIM_DW_CUSTOMER_ID"" this error. 2. If I declare variable as String then am getting "string to int conversion error" as the column am populating is Int Regards Sharukh

Re: Generation of Surrogate key for Incremental Load

Anonymous — Mon, 11 Mar 2019 11:24:37 GMT

Hi Vapukov,
My mistake I wrote that
Actually Numeric.sequence("S1",context.DIM_DW_CUSTOMER_ID,1) is the one am using.

Re: Generation of Surrogate key for Incremental Load

manodwhb — Mon, 11 Mar 2019 11:28:23 GMT

@ShaX ,yes As part of initialization,if you have not specified then it will take as Null and when the prejob completed successfully that max value would be stored in memory for that context and would be used in downstream.

Re: Generation of Surrogate key for Incremental Load

vapukov — Mon, 11 Mar 2019 11:30:21 GMT

change the initial context variable value to 1, type INT

in query use CASE for return 1 if it first iteration and max(column) for any other

store to context max(column) + 1

must work

Re: Generation of Surrogate key for Incremental Load

Anonymous — Mon, 11 Mar 2019 12:51:46 GMT

Hi,

I got the weird result, here my max value is 1900, so it has to start from 1901 instead it is starting from 1190.

I got the below error in talend. and I have only 25353 rows instead of 35353 rows

Re: Generation of Surrogate key for Incremental Load

vapukov — Mon, 11 Mar 2019 13:04:02 GMT

@ShaX wrote:

Hi,

I got the weird result, here my max value is 1900, so it has to start from 1901 instead it is starting from 1190.

I got the below error in talend. and I have only 25353 rows instead of 35353 rows

it is not seen - what's wrong (it is work definitely)

diffrerence for 10 000 - because database rollback all batch (10k default batch size in talend) with any error

Re: Generation of Surrogate key for Incremental Load

Anonymous — Tue, 12 Mar 2019 03:56:23 GMT

Hi @vapukov

Can you please explain how to over come this. caz i tried many ways but I am not understanding where am doing it wrong.

I think the issue is with the Variable which I am declaring as Int.

Am getting above error.

Regards

ShaX

Regards

ShaX

Re: Generation of Surrogate key for Incremental Load

Anonymous — Mon, 18 Mar 2019 04:02:18 GMT

Any help on this plzz.

Re: Generation of Surrogate key for Incremental Load

manodwhb — Mon, 18 Mar 2019 04:27:52 GMT

@ShaX ,can you just set a some value like 1 for that context,you will not get the warring.

Re: Generation of Surrogate key for Incremental Load

SREE3 — Tue, 14 May 2019 12:01:32 GMT

Hi All,

I am implementing SCD on my table ,i am trying to generate surrogate id using numeric sequence.

To do this at the start of the job I am taking max of value and storing it in a context variable "context.SOURCE_ID".This context I am using in the numeric sequence. I am getting this error

PFA image.

Regards,

Sreelekha.