Hi all, If you have a daily batch that updates the table you can rebuild the table in a mirror table and then swap with "alter table ... exchange partition...".
The problem with this approach is that you will need to get an exclusive lock on the table and any running query will block the operation. One possibility is to hide the underlying table below a view so the we can try to minimize the lock problem. I am not sure if that will relive the problem but its a fair try. Maybe someone can comment. Rgds, *Luis Macedo | Sr Platform Architect | **Pivotal Inc * *Mobile:* +55 11 97616-6438 *Pivotal.io <http://pivotal.io>* *Take care of the customers and the rest takes care of itself* 2016-09-23 8:37 GMT-03:00 Michael André Pearce <[email protected]> : > Ps. Please up vote the jira to help it get priority > > Sent from my iPhone > > On 23 Sep 2016, at 12:30, Michael André Pearce < > [email protected]> wrote: > > Hi > > In my company we faced similar issue. > > I noted on this jira how we're handling this. > > https://issues.apache.org/jira/plugins/servlet/mobile#issue/HAWQ-304 > > Essentially we can have updates as we version the rows and on select use > the data of the latest version this allows us during the day to have > queries and data changes. Then over night or at weekend depending on > frequency of updates to keep performance we "compact" it which at this time > we cannot have any selects but we can manage this. > > Here I guess you can replace our compaction/clean up with simple ctas > recreate strategy with your partition exchange strategy. > > I hope this helps. And would be better if we had native update / delete > support as per jira. > > Cheers > Mike > > Sent from my iPhone > > On 23 Sep 2016, at 09:47, [email protected] wrote: > > Hi Guys, > > I'm curious to know about any experience simulating updates/deletes to > rows using partition exchange strategy. > > Small lookup tables could be rebuilt by creating staging tables, but since > select queries might be running how do we swap out the new table with the > old one without select queries failing. > > Any simple way to do this, if anyone has implemented this workaround > please share insights. > > Regards, > Muji > >
