topic Re: Unable to update my target table which is used a source table in Talend Studio

Unable to update my target table which is used a source table

sushantk19 — Sat, 16 Nov 2024 02:26:38 GMT

Hi,

I am trying to update 2 fields in my target table (Say table A) with the same Target table used as a source. Now i have just included only 3 fields from Source table (id, field 1, field2) and need to update field 1, field2 in my target table which actually has around 20 fields. basically, i want to patch the data for 2 fields later on after my table has been loaded.

But when i run the job, it keeps running and doesnt even extract the data from source when source and target tables are the same. Action on Data is "Update" and i have defined id as key in my Talend job. The design is as below

Source Table(A)--->tmap---tlog--->Target Table(A). What could be possible issue?

Re: Unable to update my target table which is used a source table

manodwhb — Thu, 14 May 2020 12:20:50 GMT

@sushantk19 , your job design should be like below when your source and target tables are same to update.

Re: Unable to update my target table which is used a source table

sushantk19 — Thu, 14 May 2020 13:08:52 GMT

I am unable to locate tHash component. where do i find this?

Re: Unable to update my target table which is used a source table

manodwhb — Thu, 14 May 2020 13:27:24 GMT

@sushantk19, you need go to File>>Edite to project settings>>Designer>>palette settings>> Technical>>move those two component from left to right side and click OK and you will find,

Re: Unable to update my target table which is used a source table

sushantk19 — Thu, 14 May 2020 13:59:21 GMT

ok.i can see them now. I will test my job and let you know the result.

Re: Unable to update my target table which is used a source table

manodwhb — Thu, 14 May 2020 14:19:11 GMT

@sushantk19, Okay.

Re: Unable to update my target table which is used a source table

sushantk19 — Fri, 15 May 2020 08:02:32 GMT

@manodwhb : in my target table i.e tDBoutput1 shall i include all the fields from target table or just 3 fields i.e primary_key and 2 fields which i want to update?

Re: Unable to update my target table which is used a source table

manodwhb — Fri, 15 May 2020 08:04:17 GMT

@sushantk19 , Keep the filed which you want to update along with the primary key.

Re: Unable to update my target table which is used a source table

sushantk19 — Fri, 15 May 2020 08:39:20 GMT

The logic works perfectly fine when i try to update a flat file, but with same Redshift table as my target the query keeps running and doesn't commit.

I have defined one of the ID field as primary key at DB and ETL level and action on data as "update". I am testing initially with such a couple of records, but it is not working. What could be issue?

Re: Unable to update my target table which is used a source table

manodwhb — Fri, 15 May 2020 09:07:42 GMT

@sushantk19 , maybe you need to call commit separately after the tredshiftoutput.

Re: Unable to update my target table which is used a source table

sushantk19 — Fri, 15 May 2020 09:59:15 GMT

I tested for the same logic by creating another redshift table and it is working fine on that Table(without adding any commit component). I guess the issue is somewhere on DB side. Should I add a index to the table ? Or anything else i can try?

Re: Unable to update my target table which is used a source table

sushantk19 — Fri, 15 May 2020 10:01:16 GMT

because my target table has huge volume of data like 800,000 records.

Re: Unable to update my target table which is used a source table

manodwhb — Fri, 15 May 2020 10:01:38 GMT

@sushantk19 , do you have index on the table which was working without commit? probably you can check the definition of the tables.

Re: Unable to update my target table which is used a source table

sushantk19 — Fri, 15 May 2020 10:03:52 GMT

I created a temp table as below; Didn't define any index

create table core_dw.temp_order_upd
(
Order_ID INT NOT NULL,
Ordernumber Varchar(255),
ShippingAgent_ID INT,
Brand_ID INT
);

ALTER TABLE CORE_DW.temp_order_upd ADD CONSTRAINT PKTEMPUPDR PRIMARY KEY (Order_ID)

Re: Unable to update my target table which is used a source table

manodwhb — Fri, 15 May 2020 10:42:29 GMT

@sushantk19 , are you able to load data into temp table without a commit component?

Re: Unable to update my target table which is used a source table

sushantk19 — Fri, 15 May 2020 11:13:08 GMT

@manodwhb : Yes, it is working fine with constraint defined.

Re: Unable to update my target table which is used a source table

manodwhb — Fri, 15 May 2020 11:15:08 GMT

@sushantk19 , now check the original table structure against temp and you will find the problem

Re: Unable to update my target table which is used a source table

sushantk19 — Fri, 15 May 2020 11:24:30 GMT

temp table is a subset of the Original table defined with 4 fields and same primary key constraint. only difference i could see is the amount of data in my temp table(just 1 record here) and Original table(which has approx 800,000 records)

Re: Unable to update my target table which is used a source table

sushantk19 — Tue, 19 May 2020 16:01:54 GMT

I truncated the data and reloaded the data. Now the Logic is working fine.