How to route the dateflow after executeSQL processor

[彭光裕 <ro...@cht.com.tw>]

Hi,
        I have a flow   like this: ExecuteSQL-->ConvertAvroSchema

       The value of SQL select query property is:
       “select hour,available,wait from TCMSV_STAT where id='${parking.id}'”

       When ExecuteSQL doesn’t find any result(${parking.id} not match anything), the ConvertAvroSchema will pop out an Error.

       [Error]
2016-01-14 22:41:48,697 ERROR [Timer-Driven Process Thread-2] o.a.nifi.processors.standard.ExecuteSQL ExecuteSQL[id=b9baec46-4aa5-41c2-bb08-a6c49bf1b848] Failed to process session due to org.apache.nifi.processor.exception.FlowFileHandlingException: StandardFlowFileRecord[uuid=871d1acd-3232-489b-9edb-0ded3f63f7bb,claim=,offset=0,name=54505769166005,size=0] transfer relationship not specified: org.apache.nifi.processor.exception.FlowFileHandlingException: StandardFlowFileRecord[uuid=871d1acd-3232-489b-9edb-0ded3f63f7bb,claim=,offset=0,name=54505769166005,size=0] transfer relationship not specified

     Then the rest of ExecuteSQL will be held in queue.

       My question is: how to route the dateflow after executeSQL processor while query doesn’t find any result? What kind of processor I should use between these two processors? Thank you.


Roland.

Please be advised that this email message (including any attachments) contains confidential information and may be legally privileged. If you are not the intended recipient, please destroy this message and all attachments from your system and do not further collect, process, or use them. Chunghwa Telecom and all its subsidiaries and associated companies shall not be liable for the improper or incomplete transmission of the information contained in this email nor for any delay in its receipt or damage to your system. If you are the intended recipient, please protect the confidential and/or personal information contained in this email with due care. Any unauthorized use, disclosure or distribution of this message in whole or in part is strictly prohibited.  Also, please self-inspect attachments and hyperlinks contained in this email to ensure the information security and to protect personal information.

Re: How to route the dateflow after executeSQL processor

[Matthew Burgess <ma...@gmail.com>]

Looks like you found a bug with ExecuteSQL handling empty result sets. I have captured this in Jira:

https://issues.apache.org/jira/browse/NIFI-1409

Note that there is a workaround to get the processor to route to failure (instead of giving the “transfer relationship not specified” error), it’s in the Jira but I’ll paste here for convenience:

If it desired that an empty result set causes a route to failure, then a workaround is to add a GenerateFlowFile processor (with a 0-byte size) in front of the ExecuteSQL processor. This bypasses the logic error because the incoming flowfile is reused and eventually transferred to failure because of the exception thrown by JdbcCommon.

Please feel free to comment on the Jira case with your thoughts, suggestions, etc.

Regards,
Matt

From:  彭光裕 <ro...@cht.com.tw>
Reply-To:  <us...@nifi.apache.org>
Date:  Thursday, January 14, 2016 at 10:55 PM
To:  "users@nifi.apache.org" <us...@nifi.apache.org>
Subject:  How to route the dateflow after executeSQL processor

Hi,

        I have a flow   like this: ExecuteSQLàConvertAvroSchema

 

       The value of SQL select query property is:

       “select hour,available,wait from TCMSV_STAT where id='${parking.id}'”

 

       When ExecuteSQL doesn’t find any result(${parking.id} not match anything), the ConvertAvroSchema will pop out an Error.

 

       [Error]

2016-01-14 22:41:48,697 ERROR [Timer-Driven Process Thread-2] o.a.nifi.processors.standard.ExecuteSQL ExecuteSQL[id=b9baec46-4aa5-41c2-bb08-a6c49bf1b848] Failed to process session due to org.apache.nifi.processor.exception.FlowFileHandlingException: StandardFlowFileRecord[uuid=871d1acd-3232-489b-9edb-0ded3f63f7bb,claim=,offset=0,name=54505769166005,size=0] transfer relationship not specified: org.apache.nifi.processor.exception.FlowFileHandlingException: StandardFlowFileRecord[uuid=871d1acd-3232-489b-9edb-0ded3f63f7bb,claim=,offset=0,name=54505769166005,size=0] transfer relationship not specified

 

     Then the rest of ExecuteSQL will be held in queue.

 

       My question is: how to route the dateflow after executeSQL processor while query doesn’t find any result? What kind of processor I should use between these two processors? Thank you.

 

 

Roland.


本信件可能包含中華電信股份有限公司機密資訊,非指定之收件者,請勿蒐集、處理或利用本信件內容,並請銷毀此信件. 如為指定收件者,應確實保護郵件中本公司之營業機密及個人資料,不得任意傳佈或揭露,並應自行確認本郵件之附檔與超連結之安全性,以共同善盡資訊安全與個資保護責任. 
Please be advised that this email message (including any attachments) contains confidential information and may be legally privileged. If you are not the intended recipient, please destroy this message and all attachments from your system and do not further collect, process, or use them. Chunghwa Telecom and all its subsidiaries and associated companies shall not be liable for the improper or incomplete transmission of the information contained in this email nor for any delay in its receipt or damage to your system. If you are the intended recipient, please protect the confidential and/or personal information contained in this email with due care. Any unauthorized use, disclosure or distribution of this message in whole or in part is strictly prohibited.  Also, please self-inspect attachments and hyperlinks contained in this email to ensure the information security and to protect personal information.