mysql - Flowfile absolute path Nifi

Question

I'm trying to load the flow files into MySQL database using bulk load option. Below is the query I'm using as part of the UpdateAttribute processor and passing that query to PutSQL after updating the parameters to do bulk load.

LOAD DATA INFILE '${absolute.path}${filename}' INTO TABLE ${dest.database}.${db.table.name} FIELDS TERMINATED BY ',' LINES TERMINATED BY '
'

When I ran the flow it's failing saying file not found exception.

. There were a total of 1 FlowFiles that failed, 0 that succeeded, and 0 that were not execute and will be routed to retry; : java.sql.BatchUpdateException: Unable to open file 'data.csv'for 'LOAD DATA INFILE command.Due to underlying IOException:`

** BEGIN NESTED EXCEPTION ** 

java.io.FileNotFoundException
MESSAGE: data.csv (No such file or directory)
java.io.FileNotFoundException: data.csv (No such file or directory).

Here MySQL Server and Nifi are on different nodes so I can't use LOAD DATA LOCAL INFILE query.

I'm not sure why I'm getting file not found exception even though I mentioned the complete absolute path of the flow file in the SQL Query.

When I use query with hard coded file name and providing the absolute path of the file in nifi node, it's working as expected.

Working:

LOAD DATA LOCAL INFILE '/path/in/nifi/node/to/file/data.csv' INTO TABLE ${dest.database}.${db.table.name} FIELDS TERMINATED BY ',' LINES TERMINATED BY '
'}

Question is how to get the absolute path of the flow file and load the same flow file into mysql.

Flow:

Answer 1

• Stop the PutSQL processor and let the flowfiles queue up.
• Once they are queued up, right click on the success relationship
  between UpdateAttribute and PutSQL and select List Queue.
• Select any one flowfile and navigate to the Attributes tab and see if the attributes absolute.path and flowfilename exists and if
  they do exist, verify if they have the expected value set. In your case absolute.path should have the value /path/in/nifi/node/to/file and flowfilename should have the value /data.csv

Question for you: Are you setting these attributes yourself using UpdateAttribute, reason is, NiFi doesn't generate an attribute named flowfilename, it generates one with the name filename.

One more thing, make sure either the value for absolute.path ends with a / in the end or the value of flowfilename begins with a /. If not, they will be appended and the result will be /path/in/nifi/node/to/filedata.csv. You can try the append function that @Mahendra suggested, else you can simply use ${absolute.path}/${flowfilename}.

Update

I just realized that absolute.path is a core attribute like filename, filesize, mime.type, etc. Some processors use all the core attributes while some use very few which are needed. GenerateTableFetch writes absolute.path but doesn't set anything for it. That's why it has ./ which is the default value.

So my suggestion for your approach to work is, you can manually set/overwrite absolute.path attribute using UpdateAttribute (just like you have overwritten filename) and set the desired value which is /path/in/nifi/node/to/file