Thank you Mark. I am trying to insert the file to redshift. The input and output data looks fine in the data provenance. But 0 records got inserted into the table. I do not see any errors in nifi. I have attached the logs below:
Nifi-app.log 2017-12-20 20:00:27,182 INFO [Write-Ahead Local State Provider Maintenance] org.wali.MinimalLockingWriteAheadLog org.wali.MinimalLockingWriteAheadLog@4b3eaf39 checkpointed with 30 Records and 0 Swap Files in 4 milliseconds (Stop-the-world time = 0 milliseconds, Clear Edit Logs time = 0 millis), max Transaction ID 118 2017-12-20 20:00:36,667 INFO [pool-10-thread-1] o.a.n.c.r.WriteAheadFlowFileRepository Initiating checkpoint of FlowFile Repository 2017-12-20 20:00:36,713 INFO [pool-10-thread-1] org.wali.MinimalLockingWriteAheadLog org.wali.MinimalLockingWriteAheadLog@c3df740 checkpointed with 0 Records and 0 Swap Files in 45 milliseconds (Stop-the-world time = 26 milliseconds, Clear Edit Logs time = 12 millis), max Transaction ID 52 2017-12-20 20:00:36,713 INFO [pool-10-thread-1] o.a.n.c.r.WriteAheadFlowFileRepository Successfully checkpointed FlowFile Repository with 0 records in 45 milliseconds 2017-12-20 20:02:27,187 INFO [Write-Ahead Local State Provider Maintenance] org.wali.MinimalLockingWriteAheadLog org.wali.MinimalLockingWriteAheadLog@4b3eaf39 checkpointed with 30 Records and 0 Swap Files in 4 milliseconds (Stop-the-world time = 0 milliseconds, Clear Edit Logs time = 0 millis), max Transaction ID 118 2017-12-20 20:02:36,713 INFO [pool-10-thread-1] o.a.n.c.r.WriteAheadFlowFileRepository Initiating checkpoint of FlowFile Repository 2017-12-20 20:02:36,765 INFO [pool-10-thread-1] org.wali.MinimalLockingWriteAheadLog org.wali.MinimalLockingWriteAheadLog@c3df740 checkpointed with 0 Records and 0 Swap Files in 51 milliseconds (Stop-the-world time = 26 milliseconds, Clear Edit Logs time = 18 millis), max Transaction ID 52 2017-12-20 20:02:36,765 INFO [pool-10-thread-1] o.a.n.c.r.WriteAheadFlowFileRepository Successfully checkpointed FlowFile Repository with 0 records in 51 milliseconds 2017-12-20 20:04:27,191 INFO [Write-Ahead Local State Provider Maintenance] org.wali.MinimalLockingWriteAheadLog org.wali.MinimalLockingWriteAheadLog@4b3eaf39 checkpointed with 30 Records and 0 Swap Files in 4 milliseconds (Stop-the-world time = 0 milliseconds, Clear Edit Logs time = 0 millis), max Transaction ID 118 2017-12-20 20:04:36,765 INFO [pool-10-thread-1] o.a.n.c.r.WriteAheadFlowFileRepository Initiating checkpoint of FlowFile Repository 2017-12-20 20:04:36,811 INFO [pool-10-thread-1] org.wali.MinimalLockingWriteAheadLog org.wali.MinimalLockingWriteAheadLog@c3df740 checkpointed with 0 Records and 0 Swap Files in 45 milliseconds (Stop-the-world time = 26 milliseconds, Clear Edit Logs time = 12 millis), max Transaction ID 52 2017-12-20 20:04:36,811 INFO [pool-10-thread-1] o.a.n.c.r.WriteAheadFlowFileRepository Successfully checkpointed FlowFile Repository with 0 records in 45 milliseconds 2017-12-20 20:06:27,195 INFO [Write-Ahead Local State Provider Maintenance] org.wali.MinimalLockingWriteAheadLog org.wali.MinimalLockingWriteAheadLog@4b3eaf39 checkpointed with 30 Records and 0 Swap Files in 4 milliseconds (Stop-the-world time = 0 milliseconds, Clear Edit Logs time = 0 millis), max Transaction ID 118 2017-12-20 20:06:36,811 INFO [pool-10-thread-1] o.a.n.c.r.WriteAheadFlowFileRepository Initiating checkpoint of FlowFile Repository 2017-12-20 20:06:36,856 INFO [pool-10-thread-1] org.wali.MinimalLockingWriteAheadLog org.wali.MinimalLockingWriteAheadLog@c3df740 checkpointed with 0 Records and 0 Swap Files in 44 milliseconds (Stop-the-world time = 26 milliseconds, Clear Edit Logs time = 11 millis), max Transaction ID 52 2017-12-20 20:06:36,856 INFO [pool-10-thread-1] o.a.n.c.r.WriteAheadFlowFileRepository Successfully checkpointed FlowFile Repository with 0 records in 44 milliseconds 2017-12-20 20:08:27,200 INFO [Write-Ahead Local State Provider Maintenance] org.wali.MinimalLockingWriteAheadLog org.wali.MinimalLockingWriteAheadLog@4b3eaf39 checkpointed with 30 Records and 0 Swap Files in 4 milliseconds (Stop-the-world time = 0 milliseconds, Clear Edit Logs time = 0 millis), max Transaction ID 118 2017-12-20 20:08:36,856 INFO [pool-10-thread-1] o.a.n.c.r.WriteAheadFlowFileRepository Initiating checkpoint of FlowFile Repository 2017-12-20 20:08:36,901 INFO [pool-10-thread-1] org.wali.MinimalLockingWriteAheadLog org.wali.MinimalLockingWriteAheadLog@c3df740 checkpointed with 0 Records and 0 Swap Files in 45 milliseconds (Stop-the-world time = 26 milliseconds, Clear Edit Logs time = 12 millis), max Transaction ID 52 2017-12-20 20:08:36,901 INFO [pool-10-thread-1] o.a.n.c.r.WriteAheadFlowFileRepository Successfully checkpointed FlowFile Repository with 0 records in 45 milliseconds 2017-12-20 20:10:27,205 INFO [Write-Ahead Local State Provider Maintenance] org.wali.MinimalLockingWriteAheadLog org.wali.MinimalLockingWriteAheadLog@4b3eaf39 checkpointed with 30 Records and 0 Swap Files in 4 milliseconds (Stop-the-world time = 1 milliseconds, Clear Edit Logs time = 0 millis), max Transaction ID 118 2017-12-20 20:10:36,902 INFO [pool-10-thread-1] o.a.n.c.r.WriteAheadFlowFileRepository Initiating checkpoint of FlowFile Repository 2017-12-20 20:10:36,948 INFO [pool-10-thread-1] org.wali.MinimalLockingWriteAheadLog org.wali.MinimalLockingWriteAheadLog@c3df740 checkpointed with 0 Records and 0 Swap Files in 45 milliseconds (Stop-the-world time = 26 milliseconds, Clear Edit Logs time = 12 millis), max Transaction ID 52 2017-12-20 20:10:36,948 INFO [pool-10-thread-1] o.a.n.c.r.WriteAheadFlowFileRepository Successfully checkpointed FlowFile Repository with 0 records in 45 milliseconds 2017-12-20 20:12:27,210 INFO [Write-Ahead Local State Provider Maintenance] org.wali.MinimalLockingWriteAheadLog org.wali.MinimalLockingWriteAheadLog@4b3eaf39 checkpointed with 30 Records and 0 Swap Files in 4 milliseconds (Stop-the-world time = 0 milliseconds, Clear Edit Logs time = 0 millis), max Transaction ID 118 2017-12-20 20:12:36,948 INFO [pool-10-thread-1] o.a.n.c.r.WriteAheadFlowFileRepository Initiating checkpoint of FlowFile Repository 2017-12-20 20:12:36,994 INFO [pool-10-thread-1] org.wali.MinimalLockingWriteAheadLog org.wali.MinimalLockingWriteAheadLog@c3df740 checkpointed with 0 Records and 0 Swap Files in 45 milliseconds (Stop-the-world time = 26 milliseconds, Clear Edit Logs time = 12 millis), max Transaction ID 52 2017-12-20 20:12:36,994 INFO [pool-10-thread-1] o.a.n.c.r.WriteAheadFlowFileRepository Successfully checkpointed FlowFile Repository with 0 records in 45 milliseconds 2017-12-20 20:14:04,021 INFO [NiFi Web Server-347] o.a.n.c.s.StandardProcessScheduler Stopping FetchS3Object[id=4bfb6a77-0160-1000-4dd4-8e49e3fae312] 2017-12-20 20:14:04,021 INFO [NiFi Web Server-347] o.a.n.controller.StandardProcessorNode Stopping processor: class org.apache.nifi.processors.aws.s3.FetchS3Object 2017-12-20 20:14:04,021 INFO [StandardProcessScheduler Thread-4] o.a.n.c.s.TimerDrivenSchedulingAgent Stopped scheduling FetchS3Object[id=4bfb6a77-0160-1000-4dd4-8e49e3fae312] to run 2017-12-20 20:14:04,021 INFO [NiFi Web Server-347] o.a.n.c.s.StandardProcessScheduler Stopping UpdateRecord[id=56cf8ad8-0160-1000-97d2-0672b5079b39] 2017-12-20 20:14:04,021 INFO [NiFi Web Server-347] o.a.n.controller.StandardProcessorNode Stopping processor: class org.apache.nifi.processors.standard.UpdateRecord 2017-12-20 20:14:04,021 INFO [NiFi Web Server-347] o.a.n.c.s.StandardProcessScheduler Stopping PutDatabaseRecord[id=4bff93da-0160-1000-af04-fddc88887026] 2017-12-20 20:14:04,021 INFO [NiFi Web Server-347] o.a.n.controller.StandardProcessorNode Stopping processor: class org.apache.nifi.processors.standard.PutDatabaseRecord 2017-12-20 20:14:04,021 INFO [NiFi Web Server-347] o.a.n.c.s.StandardProcessScheduler Stopping ListS3[id=4bf7c7f4-0160-1000-8925-7bc2ae93be65] 2017-12-20 20:14:04,021 INFO [NiFi Web Server-347] o.a.n.controller.StandardProcessorNode Stopping processor: class org.apache.nifi.processors.aws.s3.ListS3 2017-12-20 20:14:04,021 INFO [StandardProcessScheduler Thread-4] o.a.n.c.s.TimerDrivenSchedulingAgent Stopped scheduling UpdateRecord[id=56cf8ad8-0160-1000-97d2-0672b5079b39] to run 2017-12-20 20:14:04,021 INFO [StandardProcessScheduler Thread-4] o.a.n.c.s.TimerDrivenSchedulingAgent Stopped scheduling PutDatabaseRecord[id=4bff93da-0160-1000-af04-fddc88887026] to run 2017-12-20 20:14:04,022 INFO [StandardProcessScheduler Thread-4] o.a.n.c.s.TimerDrivenSchedulingAgent Stopped scheduling ListS3[id=4bf7c7f4-0160-1000-8925-7bc2ae93be65] to run 2017-12-20 20:14:04,506 INFO [Flow Service Tasks Thread-2] o.a.nifi.controller.StandardFlowService Saved flow controller org.apache.nifi.controller.FlowController@2243e144 // Another save pending = false Nifi-user.log 2017-12-20 20:01:46,150 INFO [NiFi Web Server-343] o.a.n.w.s.NiFiAuthenticationFilter Authentication success for admin 2017-12-20 20:01:46,291 INFO [NiFi Web Server-335] o.a.n.w.s.NiFiAuthenticationFilter Attempting request for (<JWT token>) GET https://nifi/nifi-api/flow/process-groups/32071499-0160-1000-332e-1f2b831ca52a (source ip: 127.0.0.1) 2017-12-20 20:01:46,291 INFO [NiFi Web Server-335] o.a.n.w.s.NiFiAuthenticationFilter Authentication success for admin 2017-12-20 20:02:17,130 INFO [NiFi Web Server-335] o.a.n.w.s.NiFiAuthenticationFilter Attempting request for (<JWT token>) GET https://nifi/nifi-api/flow/cluster/summary (source ip: 127.0.0.1) 2017-12-20 20:02:17,130 INFO [NiFi Web Server-335] o.a.n.w.s.NiFiAuthenticationFilter Authentication success for admin 2017-12-20 20:02:17,138 INFO [NiFi Web Server-342] o.a.n.w.s.NiFiAuthenticationFilter Attempting request for (<JWT token>) GET https://nifi/nifi-api/flow/current-user (source ip: 127.0.0.1) 2017-12-20 20:02:17,138 INFO [NiFi Web Server-342] o.a.n.w.s.NiFiAuthenticationFilter Authentication success for admin 2017-12-20 20:02:17,139 INFO [NiFi Web Server-342] o.a.n.w.s.NiFiAuthenticationFilter Attempting request for (<JWT token>) GET https://nifi/nifi-api/flow/controller/bulletins (source ip: 127.0.0.1) 2017-12-20 20:02:17,139 INFO [NiFi Web Server-342] o.a.n.w.s.NiFiAuthenticationFilter Authentication success for admin 2017-12-20 20:02:17,141 INFO [NiFi Web Server-342] o.a.n.w.s.NiFiAuthenticationFilter Attempting request for (<JWT token>) GET https://nifi/nifi-api/flow/status (source ip: 127.0.0.1) 2017-12-20 20:02:17,141 INFO [NiFi Web Server-342] o.a.n.w.s.NiFiAuthenticationFilter Authentication success for admin 2017-12-20 20:02:17,146 INFO [NiFi Web Server-342] o.a.n.w.s.NiFiAuthenticationFilter Attempting request for (<JWT token>) GET https://nifi/nifi-api/flow/process-groups/32071499-0160-1000-332e-1f2b831ca52a (source ip: 127.0.0.1) 2017-12-20 20:02:17,147 INFO [NiFi Web Server-342] o.a.n.w.s.NiFiAuthenticationFilter Authentication success for admin 2017-12-20 20:02:43,554 INFO [NiFi Web Server-337] o.a.n.w.a.c.AccessDeniedExceptionMapper identity[anonymous], groups[none] does not have permission to access the requested resource. Unknown user with identity 'anonymous'. Returning Unauthorized response. From: Mark Payne [mailto:[email protected]] Sent: Wednesday, December 20, 2017 3:05 PM To: [email protected] Subject: Re: Put Database record Aruna, You'll only be able to update a given value once. So you could string together two UpdateRecord processors in a row, or in this specific case you could instead use a replaceRegex function, such as: /NUMBER_OF_PRESCRIBERS = replaceRegex(/NUMBER_OF_PRESCRIBERS, '[,-]', '') Thanks -Mark On Dec 20, 2017, at 2:44 PM, Aruna Sankaralingam <[email protected]<mailto:[email protected]>> wrote: Should I create another variable like the highlighted one below? /NUMBER_OF_PRESCRIBERS = replace(/NUMBER_OF_PRESCRIBERS, ',', '') /NUMBER_OF_PRESCRIBERS1 = replace(/NUMBER_OF_PRESCRIBERS, '-', '') From: Aruna Sankaralingam [mailto:[email protected]] Sent: Wednesday, December 20, 2017 10:41 AM To: [email protected]<mailto:[email protected]> Subject: RE: Put Database record Hi, I did as you suggested and it worked. If I want to remove or replace more than one character, can I do it in the same statement? For ex: I want to replace ‘,’ and ‘-‘ with ‘’ for the same field. From: Mark Payne [mailto:[email protected]] Sent: Thursday, December 14, 2017 12:00 PM To: [email protected]<mailto:[email protected]> Subject: Re: Put Database record Aruna, You would need to remove the commas from the number. The easiest way to do that is probably to use an UpdateRecord processor before the PutDatabaseRecord. In that processor, you would want to use a schema that either declares the field as a "string" or uses a union like: { "name" : "NUMBER_OF_PRESCRIBERS", "type": [ "int", "string" ] } Then, in UpdateRecord you would add properties such as: /NUMBER_OF_PRESCRIBERS = replace(/NUMBER_OF_PRESCRIBERS, ',', '') That would strip out any commas for you. It looks like you would need to do the same for each of the fields here that you are declaring as an "int" Thanks -Mark On Dec 14, 2017, at 11:15 AM, Aruna Sankaralingam <[email protected]<mailto:[email protected]>> wrote: I am getting this error when I am trying to load a csv to redshift. I guess its because I declared that as an INT in AvroSchema and the value has a “,”. If so, how do I make it accept that as an integer? <image001.png> <image002.png> <image003.png>
