問(wèn)題描述

我正在嘗試使用批量加載選項(xiàng)將流文件加載到 MySQL 數(shù)據(jù)庫(kù)中.下面是我在 UpdateAttribute 處理器中使用的查詢，并在更新參數(shù)以執(zhí)行批量加載后將該查詢傳遞給 PutSQL.

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

當(dāng)我運(yùn)行流程時(shí)，它沒(méi)有說(shuō)文件未找到異常.

這里 MySQL 服務(wù)器和 Nifi 在不同的節(jié)點(diǎn)上，所以我不能使用 LOAD DATA LOCAL INFILE 查詢.

即使我在 SQL 查詢中提到了流文件的完整絕對(duì)路徑，我也不知道為什么會(huì)出現(xiàn)文件未找到異常.

當(dāng)我使用帶有硬編碼文件名的查詢并在 nifi 節(jié)點(diǎn)中提供文件的絕對(duì)路徑時(shí)，它按預(yù)期工作.

工作:

LOAD DATA LOCAL INFILE '/path/in/nifi/node/to/file/data.csv' INTO TABLE ${dest.database}.${db.table.name} FIELDS TERMINATED BY ',' 以 '\n'} 結(jié)尾的行

問(wèn)題是如何獲取流文件的絕對(duì)路徑并將相同的流文件加載到mysql中.

流程:

• 停止 PutSQL 處理器并讓流文件排隊(duì).
• 一旦他們排隊(duì)，右鍵單擊success關(guān)系
  UpdateAttribute 和 PutSQL 之間并選擇 List Queue.
• 選擇任意一個(gè)流文件并導(dǎo)航到 Attributes 選項(xiàng)卡并查看如果屬性absolute.path 和flowfilename 存在并且如果
  它們確實(shí)存在，請(qǐng)驗(yàn)證它們是否具有預(yù)期值集.在你的情況下 absolute.path 應(yīng)該有值 /path/in/nifi/node/to/file 和 flowfilename 應(yīng)該有值 <代碼>/data.csv

問(wèn)題:您是否使用 UpdateAttribute 自己設(shè)置這些屬性，原因是，NiFi 不會(huì)生成名為 flowfilename 的屬性，而是生成名為 <代碼>文件名.

還有一點(diǎn)，請(qǐng)確保 absolute.path 的值以 / 結(jié)尾或 flowfilename 的值開(kāi)始帶有 /.如果沒(méi)有，它們將被附加，結(jié)果將是 /path/in/nifi/node/to/filedata.csv.您可以嘗試@Mahendra 建議的 append 函數(shù)，否則您可以簡(jiǎn)單地使用 ${absolute.path}/${flowfilename}.

更新

我剛剛意識(shí)到absolute.path 是一個(gè)核心屬性，如filename、filesize、mime.type等.有些處理器使用所有核心屬性，而有些處理器使用很少的必要屬性.GenerateTableFetch 寫入 absolute.path 但沒(méi)有為它設(shè)置任何東西.這就是為什么它有 ./ 這是默認(rèn)值.

所以我對(duì)您的工作方法的建議是，您可以使用 UpdateAttribute 手動(dòng)設(shè)置/覆蓋 absolute.path 屬性(就像您覆蓋了 filename) 并設(shè)置所需的值，即 /path/in/nifi/node/to/file

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 '\n'

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 '\n'}

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

Flow:

• 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

這篇關(guān)于Flowfile 絕對(duì)路徑 Nifi的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！