Hi @rhall_2_0 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?
@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,ok..for merging you can use tRedshiftRow to insert from temp table to main table by using query.
@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.
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);
@Parikhharshal,when you use on component ok to tDBRow from tDBOutput,tDBrow also execute with different table names to insert data into main table from temp/stage tables.
@manodwhb: I tried changing my job flow little bit and now everything comes straight into table and then I am doing other things.
My tDBoutput component config looks like below:
It keeps giving me an error here on this component and says:
java.sql.SQLException: [Amazon][JDBC](11220) Parameters cannot be used with normal Statement objects, use PreparedStatements instead.
at com.amazon.exceptions.ExceptionConverter.toSQLException(Unknown Source)
Is it because I am using table name based on context?
@Parikhharshal,it looks like table name context,as part of testing just use the one file and hard code the table name instead of context?
@manodwhb: The context value will create multiple tables for me in a loop.
and so on.... The table will be 820-14345, 820-43555, 623-34664, 445-35656. There is no issue with context.
@Parikhharshal,instead of tDBOutput,use tDBRow and write a query to insert data.
@manodwhb: If you see my job flow, tMap is connected to tDBoutput. So what query I can write using tDBrow? I do not think that's really possible. What are your thoughts?
Introduction to Talend Open Studio for Data Integration.
Practical steps to developing your data integration strategy.
Create systems and workflow to manage clean data ingestion and data transformation.