Load multiple delimited files into temp table and then merging into main redshift output table

[Parikhharshal]

Hi @rhall and other Experts

I have got delimited .tsv files generated from one of the job. I need a way where I can read files one by one and output into Redshift DB table.

My job initial looks like this.

Is there a way I can create multiple temp tables depending on my iteration and then later merge all these temp tables to output into main redshift table?

Thanks

Harshal.

[manodwhb]

@Parikhharshal,if you were using the enterprise edition.

1) take the current file name from tFileList and remove the .csv or .tsv and use that name as temp take and action on table is create. the first flow is your design as shown.

2) in the second flow take tDBlist and list the table and iterate and use tOracleInput and toracleoutput .

[Parikhharshal]

@manodwhb: Thanks for your reply. Sorry I didn’t understand your second step. What do you mean by using oracle input and oracle output with dblist? Have you got any design which you can share?

[manodwhb]

@Parikhharshal,what was the DB type in tDBoutput?

[Parikhharshal]

@manodwhb: it’s redshift.

[manodwhb]

@Parikhharshal,ok..for merging you can use tRedshiftRow to insert from temp table to main table by using query.

[Parikhharshal]

@manodwhb: Are you considering only one file? I have many files which I want to insert into temp tables and then merge all temp table into main table.

The job you showed will only do for one file and create one temp table?

[manodwhb]

@Parikhharshal,with multiple files also that will work in tDBOuput the action on Table should be create and take the filename and remove .csv and then use that as a table name to create.

tDBrow ..you need to use the above table name as temp table to insert data from temp table to main table by writing query. 

[Parikhharshal]

@manodwhb: Thanks a lot for your reply. I understand now.

According to your response, I’m assuming tdboutput will create multiple tables for multiple files.

How would I have to write the query in order to get all dynamic table names to read from it?

Is there anyway I can parallelise this whole thing so that simultaneously it writes to multiple tables?

[manodwhb]

@Parikhharshal,

we the below type of query in tDBrow,for the stage table you need to take current filename .

insert into category (select * from category_stage);