CSV to DataBase ETL issue when there is composite primary key in table

Five Stars

CSV to DataBase ETL issue when there is composite primary key in table

I have created CSV to database table job where multiple columns in table makes the composite primary key.
There are multiple CSV which are to be inserted in same table. when my job runs when a duplicate entry of record comes in a same file, that record is not inserted and remaing all records are inserted. But when duplicate records comes in 2 different csv files then Job stops at the duplicate record I want Job to transfer all the records In all the csv files ignoring if record is duplicate(should not insert duplicate record and continue with remaining) as per primary key in table.

Eleven Stars

Re: CSV to DataBase ETL issue when there is composite primary key in table

Did you try "Action on Data" as Insert or Update

In this case , if duplicate comes , it will update the record with latest information.

Let us know if you are expecting another behavior



Regards
Abhishek KUMAR
Five Stars

Re: CSV to DataBase ETL issue when there is composite primary key in table

Yes I know that option but in case of duplicate I dont want to update any data. but I want that the duplicate record is ignored and job continues until all the CSV files get processed with all the records in them ignoring if any duplicate
Forteen Stars

Re: CSV to DataBase ETL issue when there is composite primary key in table

@roshan_wani ,i suggest you to do the do the lookup with target table and csv file and do inner join and take the inner rejects and load to the table.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Forteen Stars

Re: CSV to DataBase ETL issue when there is composite primary key in table

@roshan_wani ,if you are using the enterprise one you can log  from tOracleOutput to error flow to a file.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Five Stars

Re: CSV to DataBase ETL issue when there is composite primary key in table

Hi manodwhb,

Thank you for your prompt answer, but it would be great if you show by some example how to get inner rejections

as I want to store csv records that are not present in lookup table(as per primary key).

Employee

Re: CSV to DataBase ETL issue when there is composite primary key in table

Hi,

 

   If you do below method, you can take only new records (which are not present in DB).

image.png

 

image.png

 

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 :-)

Forteen Stars

Re: CSV to DataBase ETL issue when there is composite primary key in table

@roshan_wani , nikhilthampi has provide ,that is what i explained.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Eleven Stars

Re: CSV to DataBase ETL issue when there is composite primary key in table

As in your case 1st occurrence of Key is valid for update or insert ,  I would prefer to merge all CSV file and using tAggregateRow/tuniqrow to get the First entry(for the key) , then use tDBOutput or Bulk components.

 

 

Regards
Abhishek KUMAR

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch