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,what was the DB type in tDBoutput?
@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?
Talend named a Leader.
Kickstart your first data integration and ETL projects.
Watch the recorded webinar!
Pick up some tips and tricks with Context Variables
Take a look at this technical overview video of Talend API Designer
Take a look at this video about Talend Integration with Databricks