Thanks, Matt! This is going to work out well. Dave
-----Original Message----- From: Matt Burgess <mattyb...@apache.org> Sent: Friday, September 20, 2019 5:14 PM To: users@nifi.apache.org Subject: Re: Need to DELETE large recordsets from target SQL table Dave, You can use ConvertRecord or JoltTransformRecord right before PutDatabaseRecord to remove the unindexed / unnecessary fields, then the generated statement should only include the necessary fields and be able to take advantage of any indexes. Regards, Matt On Fri, Sep 20, 2019 at 4:55 PM David Gallagher <dgallag...@cleverdevices.com> wrote: > > Hi – I’m looking to remove records from a table using the results returned > from a QueryDatabaseTable processor. I want to do this in batches because > it’s going to require a lot of deletes, and flowfile per record would be too > slow. I’ve tried using the PutDatabaseRecord processor, but the DELETE option > in StatementType builds a statement that uses every field in the AVRO record. > That would ignores the index on the table and do a full table scan per > delete, which obviously isn’t going to scale well. Is there a way I’m not > thinking of to get the optimized delete statements I’m looking for? The trick > is, my plan is to delete based on the key and then to insert the record, as > I’m just using this as a means of getting around the lack of UPSERT in my > database. > > > > Thanks, > > > > Dave
