The one we use for the versioning data and "compaction" is attached to the jira I sent as a sample work around we shared, whilst we await true update delete feature.
Sent from my iPhone > On 23 Sep 2016, at 17:17, [email protected] wrote: > > Thanks you for the inputs, anyone has a sample script that I can reuse? > > Regards, > Muji > >> On Sep 23, 2016, at 11:27 AM, Luis Macedo <[email protected]> wrote: >> >> 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 >> 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 >>
