Hi Aruna,
The placeholders in your ReplaceText configuration, such as
'${city_name}' are NiFi Expression Language. If the incoming FlowFile
has such FlowFile Attributes, those can be replaced with FlowFile
Attribute values. But I suspect FlowFile doesn't have those attributes
since ReplaceText is connected right after FetchS3Object.
You need to extract values from FlowFile content into FlowFile
attribute somehow, for example, if the data fetched from S3 is a JSON,
use EvaluateJsonPath before ReplaceText.
BTW, I think you don't need to use FetchS3Object because PutS3Object
passes the data object to its 'success' relationship. You can connect
'success' relationship to downstream flow like:
PutS3Object -> EvaluateJsonPath -> ReplaceText -> PutSQL
Also if you can upgrade NiFi to 1.3.0, PutDatabaseRecord can make the
flow simpler and more efficient:
PutS3Objecct -> PutDatabaseRecord (with arbitrary RecordReader)
Thanks,
Koji
On Tue, Sep 26, 2017 at 12:47 AM, Aruna Sankaralingam
<[email protected]> wrote:
> I updated the insert statement to be in a single line. Again it failed. I
> checked the flow file.
>
>
>
> INSERT INTO ADR_SUB_NIFI (enrlmt_id, city_name, zip_cd, state_cd) VALUES
> ('', '', '', '')
>
>
>
> What could be the reason for the values to be blank instead of actual values
> from the CSV file?
>
>
>
> From: karthi keyan [mailto:[email protected]]
> Sent: Monday, September 25, 2017 7:15 AM
> To: [email protected]; Aruna Sankaralingam
>
>
> Subject: Re: [EXT] New to Nifi - Failed to update database due to a failed
> batch update
>
>
>
> Aruna,
>
>
>
> seems failure in your insert statement, don't split the Replacement
> value(query) in the replacetext processor into multiple lines and try to be
> in a single line?
>
>
>
> -Karthik
>
>
>
> On Mon, Sep 25, 2017 at 4:20 PM, karthi keyan <[email protected]>
> wrote:
>
> Aruna,
>
>
>
> You can download the flow file to see whether your query passed correctly
> and try execute the same with you datasoruce.
>
>
>
> -Karthik
>
>
>
> On Mon, Sep 25, 2017 at 4:04 PM, Aruna Sankaralingam
> <[email protected]> wrote:
>
> I clicked on that as well but nothing seemed to happen.
>
> Thanks
>
> Aruna
>
>
> On Sep 25, 2017, at 4:33 AM, Peter Wicks (pwicks) <[email protected]> wrote:
>
> Use the Download button right next to View, then open it in a text editor.
>
>
>
> From: Aruna Sankaralingam [mailto:[email protected]]
> Sent: Monday, September 25, 2017 9:54 AM
> To: [email protected]
> Subject: Re: [EXT] New to Nifi - Failed to update database due to a failed
> batch update
>
>
>
> Hi, thank you for getting back. Could you please let me know how I can see
> the contents of the flow file ? The view option doesn't seem to work for me.
> Please see my last screenshot in my first email.
>
> Thanks
>
> Aruna
>
>
> On Sep 24, 2017, at 8:52 PM, Peter Wicks (pwicks) <[email protected]> wrote:
>
> Hi Aruna,
>
>
>
> Since you are using ReplaceText, you can view the contents of the FlowFile
> and check that you can copy/paste the SQL and execute it by hand in
> Postgres.
>
>
>
> If all that works try setting the batch size on PutSQL to 1 record. This
> will help check if it’s all records that are having trouble, or just a few
> bad records.
>
>
>
> --Peter
>
>
>
> From: Aruna Sankaralingam [mailto:[email protected]]
> Sent: Saturday, September 23, 2017 2:57 AM
> To: [email protected]
> Subject: [EXT] New to Nifi - Failed to update database due to a failed batch
> update
>
>
>
> Hi,
>
>
>
> I am new to Nifi. I am trying to load a CSV file into S3 bucket and then
> load into postgres database. Please see screenshots below. This is what I
> have done. I am successful till “Replace Text”. But I am not sure if the
> replace text is creating the insert query properly. When I start the PutSQL,
> it fails with this error “Failed to update database due to a failed batch
> update. There were a total of 30 FlowFiles that failed, 0 that succeeded,
> and 0 that were not execute and will be routed to retry”
>
>
>
> I tried to see if I can find something in the failure flow file but when I
> click on View or Download, nothing is happening. I would really appreciate
> any kind of guidance to make this work.
>
>
>
> <image001.jpg>
>
>
>
>
>
> <image002.jpg>
>
>
>
> <image003.jpg>
>
>
>
>
>
>
