topic Re: newbie question on writing to a sql db in Talend Studio

newbie question on writing to a sql db

Anonymous — Tue, 12 Feb 2019 21:47:18 GMT

How do I make sure that I do not duplicate data being copied from a flat file to a sql table

I am reading a txt file going thru logrow and then writing to a sql table. The process works but every time I run it the same data gets inserted into my sql table

I know when writing to a txt file you have the option of append or rewrite .. is there such an option for writing to sql

Tom

Re: newbie question on writing to a sql db

vapukov — Wed, 13 Feb 2019 00:32:26 GMT

Hi Tom

with files the same - if you will append same information, it will be duplicated and in files as well

but you could save in database row number - add column and populate it by talend sequence

on next time - you will need request max row from table and start read the file from NNN+1 (header section of tFileInputDelimited)

you could also add more logic:

count number of rows in file
read from NNN+1 if NNN bigger than number of rows
read from begin if number of rows less than NNN - it will help in case if file was truncated, but also will require reset max NNN in database

Re: newbie question on writing to a sql db

Anonymous — Wed, 13 Feb 2019 00:52:35 GMT

Thank you for the quick response .. I understand the thought that the data could be duplicated if the input file was used multiple times. Which is what I see when I run the job more then once. Is there a way to parse the data itself either via Talend or SQL to stop duplicate rows from being added. I am trying to learn and become more proficient so any support is very welcome

Tom

Re: newbie question on writing to a sql db

vapukov — Wed, 13 Feb 2019 01:33:12 GMT

there are many ways to achieve the same goals:

who and how generate these logs?
is it possible truncate logs after reading?
you could use insert ignore if output database support it
you could add database trigger to ignore duplicates if a database does not support insert ignore, or use tSQLRow to do the same (INSERT xxx WHERE NOT EXISTS ...)
you could read data from db and reject duplicates in tMap if data not too big

Re: newbie question on writing to a sql db

Anonymous — Wed, 13 Feb 2019 01:41:12 GMT

This is not a production environment of any sort just me trying to learn how to write to a sql db. One idea that came to me was the use of Line id and adding logic to verify the new data line id versus the largest one in the db itself. What I am finding in developing in Talend is looking at all the ways that the data needs to be handled prior to ingestion

Tom