You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@nifi.apache.org by mfzeidan <ma...@cleverspeck.com> on 2016/04/22 18:21:08 UTC

Monitoring duplicate file entries with ExecuteSQL

I am trying to use a ExecutesSQL process to check if a file has already been
processed.

Files are flowing into our nifi and we have it set up to move files to
another folder. Once the file is moved, we just log the history into MYSQL. 

I want to have it check if the filename is already in the db, thus checking
if we have already processed this file. 

SELECT * FROM tbl WHERE ${filename} = File_Name(this is the column header in
our MYSQL.)

I have yet to get anything besides an error being generated saying my sql
statement is incorrect. Am I going about this the wrong way? Does anyone
have a better way of checking this sort of thing? I have added in future
file names(as the file names are predictable) but it still will give me an
error.

Thanks



--
View this message in context: http://apache-nifi-developer-list.39713.n7.nabble.com/Monitoring-duplicate-file-entries-with-ExecuteSQL-tp9490.html
Sent from the Apache NiFi Developer List mailing list archive at Nabble.com.

Re: Monitoring duplicate file entries with ExecuteSQL

Posted by Simon Ball <sb...@hortonworks.com>.
ListFiles will maintain state on files processed. However, if any of the meta data for those files changes, they will be output from ListFiles again (modification time change for example).

Simon

> On Apr 22, 2016, at 6:49 PM, mfzeidan <ma...@cleverspeck.com> wrote:
> 
> Thank you Simon. I am using the ListSFTP -> FetchSFTP combination as well.
> Can I assume this maintains which files have already been processed? I want
> to make sure we don't process any files twice and I need to be as close
> possible to 100%. 
> 
> 
> 
> --
> View this message in context: http://apache-nifi-developer-list.39713.n7.nabble.com/Monitoring-duplicate-file-entries-with-ExecuteSQL-tp9490p9508.html
> Sent from the Apache NiFi Developer List mailing list archive at Nabble.com.
> 


Re: Monitoring duplicate file entries with ExecuteSQL

Posted by mfzeidan <ma...@cleverspeck.com>.
Thank you Simon. I am using the ListSFTP -> FetchSFTP combination as well.
Can I assume this maintains which files have already been processed? I want
to make sure we don't process any files twice and I need to be as close
possible to 100%. 



--
View this message in context: http://apache-nifi-developer-list.39713.n7.nabble.com/Monitoring-duplicate-file-entries-with-ExecuteSQL-tp9490p9508.html
Sent from the Apache NiFi Developer List mailing list archive at Nabble.com.

Re: Monitoring duplicate file entries with ExecuteSQL

Posted by Simon Ball <sb...@hortonworks.com>.
You need to quote the attribute in your SQL: '${filename}' to produce valid SQL. 

One alternative to this would be the use ListFile -> FetchFile together. With that pattern, ListFile maintains the state of which files have been processed for you within nifi, meaning you wouldn't need the separate MySQL log, so could reduce your complexity of you don't need the SQL log for anything else.

Simon 


> On 22 Apr 2016, at 18:22, mfzeidan <ma...@cleverspeck.com> wrote:
> 
> I am trying to use a ExecutesSQL process to check if a file has already been
> processed.
> 
> Files are flowing into our nifi and we have it set up to move files to
> another folder. Once the file is moved, we just log the history into MYSQL. 
> 
> I want to have it check if the filename is already in the db, thus checking
> if we have already processed this file. 
> 
> SELECT * FROM tbl WHERE ${filename} = File_Name(this is the column header in
> our MYSQL.)
> 
> I have yet to get anything besides an error being generated saying my sql
> statement is incorrect. Am I going about this the wrong way? Does anyone
> have a better way of checking this sort of thing? I have added in future
> file names(as the file names are predictable) but it still will give me an
> error.
> 
> Thanks
> 
> 
> 
> --
> View this message in context: http://apache-nifi-developer-list.39713.n7.nabble.com/Monitoring-duplicate-file-entries-with-ExecuteSQL-tp9490.html
> Sent from the Apache NiFi Developer List mailing list archive at Nabble.com.
>