Talend Redshift Create temp table in Job

[sri_21]

Hi All,

I'm trying to insert the updated record from source based on primary Key, as the update performance is very less,

i'm trying to create temp table during starting of the job check the update and new record. Store the update record in temp table and update it using tredshiftrow to target table.

tredshiftrow_1(create temp table)-on sub job ok->tredshiftinput_1, tredshiftinput_2->tmap->tredshiftoutput_1(insert), tredshiftoutput_2(insert to temp table)->on sub job ok->tredshiftrow_2(update to permanent table).

But while running a job it fails as it is not to load the temp table.

java.sql.SQLException: [Amazon](500310) Invalid operation: relation "dim_temp" does not exist;

tredshiftrow_1-

"begin transaction;

create temp table dim_temp (like dim);
"

tredshiftrow_2- Update target_table

set col1=t.col1

from

dim_temp t

where col2=t.col2; end transaction

How to resolve this error. 

Regards,

Sri

[vapukov]

Hi Sri,

does all your components use build-in connection settings?

Temporary table availalble only for single session and if components use independent connection - temp table does not available for the next component

try to add tRedshiftConnection at the begin of the Job (as variant in tPreJob section)

regards, Vlad

[sri_21]

Thanks Vlad for your response. All the components are connected to tredshiftconnection component. The schema of source and scheme of target is different, but all tables are in single database. And temp table is not created on top schema.

Regards,
Sri

[Anonymous]

Hi,

   Could you please select the Die on Error option for tredshiftrow_1 to know more about the error details.

    Another option is to create the table using tCreateTable component instead of tRedshiftRow. While using tCreateTable, double check the schema to make sure that you are adhering to target DB datatypes.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂