Re: [EXT] New to Nifi - Failed to update database due to a failed batch update
Hi Aruna, The XML files in the Gist page are NiFi Templates. You can import those XML from NiFi UI. Please look at this documentation for detail: https://nifi.apache.org/docs/nifi-docs/html/user-guide.html#Import_Template As to PutDatabase doing nothing, the '1' on the right top corner of PutDatabaseRecord indicates that one thread is running for this processor currently. That's strange if you don't see anything happening with it for 30 min, the thread may be blocked unexpectedly. If possible please take a thread dump with following command and share it with us: $NIFI_HOME/bin/nifi.sh dump Then thread dump is logged at $NIFI_HOME/logs/nifi-bootstrap Also, please share PutDatabaseRecord and its record reader configurations for further investigation. Thanks, Koji On Thu, Sep 28, 2017 at 1:48 AM, Aruna Sankaralingam < aruna.sankaralin...@cormac-corp.com> wrote: > Thank you Koji. Could you please let me know how I can import the xml so > that I can see them as nifi processors? > > I updated my flow as shown below. When I started PutDatabaseRecord, it is > not doing anything. It’s been more than 30 mins. I don’t see any errors as > well. How do I find out what is wrong? > > > > > > *From:* Koji Kawamura [mailto:ijokaruma...@gmail.com] > *Sent:* Tuesday, September 26, 2017 10:22 PM > > *To:* users@nifi.apache.org > *Cc:* karthi keyan > *Subject:* Re: [EXT] New to Nifi - Failed to update database due to a > failed batch update > > > > Hi Aruna, > > > > To explain details, I've summarized two different approaches to load a CSV > file into a Table in this Gist page: > > https://gist.github.com/ijokarumawak/b37db141b4d04c2da124c1a6d922f81f > > > > One is using ConvertCSVToAvro and few additional processors. > > I didn't use ReplaceText as I thought altering raw SQL string would be > error prone. > > This approach should work with older version of NiFi (I see you're using > NiFi 1.2.0 in your screenshot). > > > > The another way is to use PutDatabaseRecord. > > This is recommended if you're able to upgrade your NiFi installation. > > > > I hope you find these examples useful. > > > > Thanks, > > Koji > > > > On Tue, Sep 26, 2017 at 11:23 PM, Aruna Sankaralingam < > aruna.sankaralin...@cormac-corp.com> wrote: > > I am not sure I understand. This is how my CSV looks. > > > > > > -Original Message- > From: Koji Kawamura [mailto:ijokaruma...@gmail.com] > Sent: Monday, September 25, 2017 8:19 PM > To: users@nifi.apache.org > Cc: karthi keyan > Subject: Re: [EXT] New to Nifi - Failed to update database due to a failed > batch update > > > > 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 < > aruna.sankaralin...@cormac-corp.com> 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:karthi93.san...@gmail.com > <karthi93.san...@gmail.com>] > > > Sent: Monday, September 25, 2017 7:15 AM > > > To: users@nifi.apache.org; Aruna Sankaralingam > > > > > > > > > Subject: Re: [EXT] New to Nifi - Failed to update database due to a > > > failed batch update > > > > &g
Re: [EXT] New to Nifi - Failed to update database due to a failed batch update
Hi Aruna, To explain details, I've summarized two different approaches to load a CSV file into a Table in this Gist page: https://gist.github.com/ijokarumawak/b37db141b4d04c2da124c1a6d922f81f One is using ConvertCSVToAvro and few additional processors. I didn't use ReplaceText as I thought altering raw SQL string would be error prone. This approach should work with older version of NiFi (I see you're using NiFi 1.2.0 in your screenshot). The another way is to use PutDatabaseRecord. This is recommended if you're able to upgrade your NiFi installation. I hope you find these examples useful. Thanks, Koji On Tue, Sep 26, 2017 at 11:23 PM, Aruna Sankaralingam < aruna.sankaralin...@cormac-corp.com> wrote: > I am not sure I understand. This is how my CSV looks. > > > > > > -Original Message- > From: Koji Kawamura [mailto:ijokaruma...@gmail.com] > Sent: Monday, September 25, 2017 8:19 PM > To: users@nifi.apache.org > Cc: karthi keyan > Subject: Re: [EXT] New to Nifi - Failed to update database due to a failed > batch update > > > > 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 < > aruna.sankaralin...@cormac-corp.com> 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:karthi93.san...@gmail.com > <karthi93.san...@gmail.com>] > > > Sent: Monday, September 25, 2017 7:15 AM > > > To: users@nifi.apache.org; 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 > > > <karthi93.san...@gmail.com> > > > 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 > > > <aruna.sankaralin...@cormac-corp.com> 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) <pwi...@micron.com> > wrote: > > > > > > Use the Download button right next to View, then open it in a text > editor. > > > > > > > > > > > > From: Aruna Sankaralingam [mailto:aruna.sankaralin...@cormac-corp.com > <aruna.sankaralin...@cormac-corp.com>] > > > Sent: Monday, September 25, 2017 9:54 AM > > > To: users@nifi.apach
RE: [EXT] New to Nifi - Failed to update database due to a failed batch update
I am not sure I understand. This is how my CSV looks. [cid:image001.png@01D336B1.6BDCCC60] -Original Message- From: Koji Kawamura [mailto:ijokaruma...@gmail.com] Sent: Monday, September 25, 2017 8:19 PM To: users@nifi.apache.org Cc: karthi keyan Subject: Re: [EXT] New to Nifi - Failed to update database due to a failed batch update 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 <aruna.sankaralin...@cormac-corp.com<mailto:aruna.sankaralin...@cormac-corp.com>> 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:karthi93.san...@gmail.com] > Sent: Monday, September 25, 2017 7:15 AM > To: users@nifi.apache.org<mailto:users@nifi.apache.org>; 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 > <karthi93.san...@gmail.com<mailto:karthi93.san...@gmail.com>> > 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 > <aruna.sankaralin...@cormac-corp.com<mailto:aruna.sankaralin...@cormac-corp.com>> > 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) > <pwi...@micron.com<mailto:pwi...@micron.com>> wrote: > > Use the Download button right next to View, then open it in a text editor. > > > > From: Aruna Sankaralingam [mailto:aruna.sankaralin...@cormac-corp.com] > Sent: Monday, September 25, 2017 9:54 AM > To: users@nifi.apache.org<mailto:users@nifi.apache.org> > 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) > <pwi...@micron.com<mailto:pwi...@micron.com>> 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:aruna.sankaralin...@cormac-corp.com] > Sent: Saturday, September 23, 2017 2:57 AM > To: users@nifi.apache.org<mailto:users@nifi.apache.org> > 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
Re: [EXT] New to Nifi - Failed to update database due to a failed batch update
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 <aruna.sankaralin...@cormac-corp.com> 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:karthi93.san...@gmail.com] > Sent: Monday, September 25, 2017 7:15 AM > To: users@nifi.apache.org; 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 <karthi93.san...@gmail.com> > 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 > <aruna.sankaralin...@cormac-corp.com> 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) <pwi...@micron.com> wrote: > > Use the Download button right next to View, then open it in a text editor. > > > > From: Aruna Sankaralingam [mailto:aruna.sankaralin...@cormac-corp.com] > Sent: Monday, September 25, 2017 9:54 AM > To: users@nifi.apache.org > 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) <pwi...@micron.com> 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:aruna.sankaralin...@cormac-corp.com] > Sent: Saturday, September 23, 2017 2:57 AM > To: users@nifi.apache.org > 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. > > > > > > > > > > > > > > > > > > > >
RE: [EXT] New to Nifi - Failed to update database due to a failed batch update
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:karthi93.san...@gmail.com] Sent: Monday, September 25, 2017 7:15 AM To: users@nifi.apache.org; 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 <karthi93.san...@gmail.com<mailto:karthi93.san...@gmail.com>> 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 <aruna.sankaralin...@cormac-corp.com<mailto:aruna.sankaralin...@cormac-corp.com>> 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) <pwi...@micron.com<mailto:pwi...@micron.com>> wrote: Use the Download button right next to View, then open it in a text editor. From: Aruna Sankaralingam [mailto:aruna.sankaralin...@cormac-corp.com] Sent: Monday, September 25, 2017 9:54 AM To: users@nifi.apache.org<mailto:users@nifi.apache.org> 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) <pwi...@micron.com<mailto:pwi...@micron.com>> 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:aruna.sankaralin...@cormac-corp.com] Sent: Saturday, September 23, 2017 2:57 AM To: users@nifi.apache.org<mailto:users@nifi.apache.org> 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.
Re: [EXT] New to Nifi - Failed to update database due to a failed batch update
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 < aruna.sankaralin...@cormac-corp.com> 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) <pwi...@micron.com> > wrote: > > Use the Download button right next to View, then open it in a text editor. > > > > *From:* Aruna Sankaralingam [mailto:aruna.sankaralin...@cormac-corp.com > <aruna.sankaralin...@cormac-corp.com>] > *Sent:* Monday, September 25, 2017 9:54 AM > *To:* users@nifi.apache.org > *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) <pwi...@micron.com> > 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:aruna.sankaralin...@cormac-corp.com > <aruna.sankaralin...@cormac-corp.com>] > *Sent:* Saturday, September 23, 2017 2:57 AM > *To:* users@nifi.apache.org > *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. > > > > > > > > > > > > > > > > > >
Re: [EXT] New to Nifi - Failed to update database due to a failed batch update
I clicked on that as well but nothing seemed to happen. Thanks Aruna On Sep 25, 2017, at 4:33 AM, Peter Wicks (pwicks) <pwi...@micron.com<mailto:pwi...@micron.com>> wrote: Use the Download button right next to View, then open it in a text editor. From: Aruna Sankaralingam [mailto:aruna.sankaralin...@cormac-corp.com] Sent: Monday, September 25, 2017 9:54 AM To: users@nifi.apache.org<mailto:users@nifi.apache.org> 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) <pwi...@micron.com<mailto:pwi...@micron.com>> 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:aruna.sankaralin...@cormac-corp.com] Sent: Saturday, September 23, 2017 2:57 AM To: users@nifi.apache.org<mailto:users@nifi.apache.org> 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.
RE: [EXT] New to Nifi - Failed to update database due to a failed batch update
Use the Download button right next to View, then open it in a text editor. From: Aruna Sankaralingam [mailto:aruna.sankaralin...@cormac-corp.com] Sent: Monday, September 25, 2017 9:54 AM To: users@nifi.apache.org 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) <pwi...@micron.com<mailto:pwi...@micron.com>> 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:aruna.sankaralin...@cormac-corp.com] Sent: Saturday, September 23, 2017 2:57 AM To: users@nifi.apache.org<mailto:users@nifi.apache.org> 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.
RE: [EXT] New to Nifi - Failed to update database due to a failed batch update
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:aruna.sankaralin...@cormac-corp.com] Sent: Saturday, September 23, 2017 2:57 AM To: users@nifi.apache.org 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. [cid:image001.jpg@01D335DB.94E40DC0] [cid:image002.jpg@01D335DB.94E40DC0] [cid:image003.jpg@01D335DB.94E40DC0]