[jira] [Comment Edited] (HAWQ-304) Support update and delete on non-heap tables
[ https://issues.apache.org/jira/browse/HAWQ-304?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15179320#comment-15179320 ] arron edited comment on HAWQ-304 at 3/4/16 5:17 AM: it means that hawq wil support update or delete operation start with 3.0.0 verion, right? was (Author: arron): it means that hawq wil support update or deleteoperation start with 3.0.0 verion, right? > Support update and delete on non-heap tables > > > Key: HAWQ-304 > URL: https://issues.apache.org/jira/browse/HAWQ-304 > Project: Apache HAWQ > Issue Type: New Feature > Components: Storage >Reporter: Lei Chang > Fix For: 3.0.0 > > -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Comment Edited] (HAWQ-304) Support update and delete on non-heap tables
[ https://issues.apache.org/jira/browse/HAWQ-304?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15179320#comment-15179320 ] arron edited comment on HAWQ-304 at 3/4/16 5:17 AM: it means that hawq wil support update or delete operation starting with 3.0.0 verion, right? was (Author: arron): it means that hawq wil support update or delete operation start with 3.0.0 verion, right? > Support update and delete on non-heap tables > > > Key: HAWQ-304 > URL: https://issues.apache.org/jira/browse/HAWQ-304 > Project: Apache HAWQ > Issue Type: New Feature > Components: Storage >Reporter: Lei Chang > Fix For: 3.0.0 > > -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Comment Edited] (HAWQ-304) Support update and delete on non-heap tables
[ https://issues.apache.org/jira/browse/HAWQ-304?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15184980#comment-15184980 ] Lei Chang edited comment on HAWQ-304 at 3/8/16 2:39 PM: right. it is the current plan. was (Author: lei_chang): right. > Support update and delete on non-heap tables > > > Key: HAWQ-304 > URL: https://issues.apache.org/jira/browse/HAWQ-304 > Project: Apache HAWQ > Issue Type: New Feature > Components: Storage >Reporter: Lei Chang > Fix For: 3.0.0 > > -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Comment Edited] (HAWQ-304) Support update and delete on non-heap tables
[ https://issues.apache.org/jira/browse/HAWQ-304?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15325523#comment-15325523 ] Michael Andre Pearce (IG) edited comment on HAWQ-304 at 6/10/16 11:51 PM: -- Hi Guys, Whilst we wait for this feature, we've made a very rudimentary work around that manages to give the effect/simulate a mutable table (update and delete). Ive attached a sample so others can re-use if they wish. For simulating UPDATE we just append and simply have a bigserial incrementing as update_version for each row key. At query time we select the latest version by key. For simulating DELETE we have a field that marks if the latest update_version for a key is actually a delete. At query time we remove deleted fields from the select. For clean-ness we wrap the above in a view which hides the extra columns and keeps normal SELECT queries clean from the logic a little. We also have a compacting phase we run separately a CTAS which re-creates the table using a SELECT on the view, essentially giving the latest and removing previous iterations and change depth. It seems this works relatively ok, and for updates/deletes works quite fast too. Issues we have is that as no way to lock during the compaction if a query occurs when we run that, we can get undesired effects. We manage this by running at a quiet period. Any idea's how we could make this safer? And or faster/less intensive? Also we wonder if there is a more efficient way of doing the select in the view to get latest row version for a key? Cheers Mike was (Author: michael.andre.pearce): Hi Guys, Whilst we wait for this feature, we've made a very rudimentary work around that manages to give the effect/simulate a mutable table (update and delete). Ive attached a sample so others can re-use if they wish. For simulating UPDATE we just append and simply re have a bigserial incrementing as update_version and for each row key. At query time we select the latest version by key. For simulating DELETE we have a field that marks if the latest version is actually a delete. At query time we remove deleted fields from the select. For clean-ness we wrap the above in a view which hides the extra columns and keeps normal SELECT queries clean from the logic a little. We also have a compacting phase we run separately a CTAS which re-creates the table using a SELECT on the view, essentially giving the latest and removing previous iterations and change depth. It seems this works relatively ok, and for updates/deletes works quite fast too. Issues we have is that as no way to lock during the compaction if a query occurs when we run that, we can get undesired effects. We manage this by running at a quiet period. Any idea's how we could make this safer? And or faster/less intensive? Also we wonder if there is a more efficient way of doing the select in the view to get latest row version for a key? Cheers Mike > Support update and delete on non-heap tables > > > Key: HAWQ-304 > URL: https://issues.apache.org/jira/browse/HAWQ-304 > Project: Apache HAWQ > Issue Type: New Feature > Components: Storage >Reporter: Lei Chang > Fix For: 3.0.0 > > Attachments: mutable_table.sql > > -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Comment Edited] (HAWQ-304) Support update and delete on non-heap tables
[ https://issues.apache.org/jira/browse/HAWQ-304?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15325523#comment-15325523 ] Michael Andre Pearce (IG) edited comment on HAWQ-304 at 6/10/16 11:53 PM: -- Hi Guys, Whilst we wait for this feature, we've made a very rudimentary work around that manages to give the effect/simulate a mutable table (update and delete). Ive attached a sample so others can re-use if they wish. For simulating UPDATE we just append and simply have a bigserial incrementing as update_version for each row key. At query time we select the latest version by key. For simulating DELETE we have a field that marks if the latest update_version for a key is actually a delete. At query time we remove deleted fields from the select. For clean-ness we wrap the above in a view which hides the extra columns and keeps normal SELECT queries clean from the logic a little. We also have a compacting phase we run separately a CTAS which re-creates the table using a SELECT on the view, essentially giving the latest and removing previous iterations and change depth improving/maintaining query performance as time goes on. It seems this works relatively ok, and for updates/deletes works quite fast too. Issues we have is that as no way to lock during the compaction if a query occurs when we run that, we can get undesired effects. We manage this by running at a quiet period. Any idea's how we could make this safer? And or faster/less intensive? Also we wonder if there is a more efficient way of doing the select in the view to get latest row version for a key? Cheers Mike was (Author: michael.andre.pearce): Hi Guys, Whilst we wait for this feature, we've made a very rudimentary work around that manages to give the effect/simulate a mutable table (update and delete). Ive attached a sample so others can re-use if they wish. For simulating UPDATE we just append and simply have a bigserial incrementing as update_version for each row key. At query time we select the latest version by key. For simulating DELETE we have a field that marks if the latest update_version for a key is actually a delete. At query time we remove deleted fields from the select. For clean-ness we wrap the above in a view which hides the extra columns and keeps normal SELECT queries clean from the logic a little. We also have a compacting phase we run separately a CTAS which re-creates the table using a SELECT on the view, essentially giving the latest and removing previous iterations and change depth. It seems this works relatively ok, and for updates/deletes works quite fast too. Issues we have is that as no way to lock during the compaction if a query occurs when we run that, we can get undesired effects. We manage this by running at a quiet period. Any idea's how we could make this safer? And or faster/less intensive? Also we wonder if there is a more efficient way of doing the select in the view to get latest row version for a key? Cheers Mike > Support update and delete on non-heap tables > > > Key: HAWQ-304 > URL: https://issues.apache.org/jira/browse/HAWQ-304 > Project: Apache HAWQ > Issue Type: New Feature > Components: Storage >Reporter: Lei Chang > Fix For: 3.0.0 > > Attachments: mutable_table.sql > > -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Comment Edited] (HAWQ-304) Support update and delete on non-heap tables
[ https://issues.apache.org/jira/browse/HAWQ-304?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15325523#comment-15325523 ] Michael Andre Pearce (IG) edited comment on HAWQ-304 at 6/10/16 11:55 PM: -- Hi Guys, Whilst we wait for this feature, we've made a very rudimentary work around that manages to give the effect/simulate a mutable table (update and delete). Ive attached a sample so others can re-use if they wish. For simulating UPDATE we just append and simply have a bigserial incrementing as update_version for each row key. At query time we select the latest version by key. For simulating DELETE we have a field that marks if the latest update_version for a key is actually a delete. At query time we remove deleted fields from the select. For clean-ness we wrap the above in a view which hides the extra columns and keeps normal SELECT queries clean from the logic a little. We also have a compacting phase we run separately a CTAS which re-creates the table using a SELECT on the view, essentially giving the latest and removing previous iterations and change depth improving/maintaining query performance as time goes on. It seems this works relatively ok, and for updates/deletes works quite fast too. Issues we have is that as no way to lock during the compaction if a query occurs when we run that, we can get undesired effects. We manage/mitigate this by running at a quiet period. Any idea's how we could make this safer? And or faster/less intensive? Also we wonder if there is a more efficient way of doing the select in the view to get latest row version for a key? Cheers Mike was (Author: michael.andre.pearce): Hi Guys, Whilst we wait for this feature, we've made a very rudimentary work around that manages to give the effect/simulate a mutable table (update and delete). Ive attached a sample so others can re-use if they wish. For simulating UPDATE we just append and simply have a bigserial incrementing as update_version for each row key. At query time we select the latest version by key. For simulating DELETE we have a field that marks if the latest update_version for a key is actually a delete. At query time we remove deleted fields from the select. For clean-ness we wrap the above in a view which hides the extra columns and keeps normal SELECT queries clean from the logic a little. We also have a compacting phase we run separately a CTAS which re-creates the table using a SELECT on the view, essentially giving the latest and removing previous iterations and change depth improving/maintaining query performance as time goes on. It seems this works relatively ok, and for updates/deletes works quite fast too. Issues we have is that as no way to lock during the compaction if a query occurs when we run that, we can get undesired effects. We manage this by running at a quiet period. Any idea's how we could make this safer? And or faster/less intensive? Also we wonder if there is a more efficient way of doing the select in the view to get latest row version for a key? Cheers Mike > Support update and delete on non-heap tables > > > Key: HAWQ-304 > URL: https://issues.apache.org/jira/browse/HAWQ-304 > Project: Apache HAWQ > Issue Type: New Feature > Components: Storage >Reporter: Lei Chang > Fix For: 3.0.0 > > Attachments: mutable_table.sql > > -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Comment Edited] (HAWQ-304) Support update and delete on non-heap tables
[ https://issues.apache.org/jira/browse/HAWQ-304?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15929455#comment-15929455 ] Hongxu Ma edited comment on HAWQ-304 at 3/17/17 5:54 AM: - Great job @[~tzolov] I have tried your calcite-sql-rewriter, but failed on HAWQ: https://github.com/tzolov/calcite-sql-rewriter/issues/1 Not support HAWQ now? was (Author: hongxu ma): Great job [~tzolov] I have tried your calcite-sql-rewriter, but failed on HAWQ: https://github.com/tzolov/calcite-sql-rewriter/issues/1 not supported HAWQ now? > Support update and delete on non-heap tables > > > Key: HAWQ-304 > URL: https://issues.apache.org/jira/browse/HAWQ-304 > Project: Apache HAWQ > Issue Type: Wish > Components: Storage >Reporter: Lei Chang >Assignee: Lili Ma > Fix For: 3.0.0.0 > > Attachments: mutable_table.sql > > -- This message was sent by Atlassian JIRA (v6.3.15#6346)
[jira] [Comment Edited] (HAWQ-304) Support update and delete on non-heap tables
[
https://issues.apache.org/jira/browse/HAWQ-304?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15929455#comment-15929455
]
Hongxu Ma edited comment on HAWQ-304 at 3/22/17 3:00 AM:
-
Great job @[~tzolov]
I have tried your calcite-sql-rewriter, but failed on HAWQ:
https://github.com/tzolov/calcite-sql-rewriter/issues/1
Not support HAWQ now?
Update: already support HAWQ, thanks Christian
demo:
{code}
$ java -jar ./target/journalled-sql-rewriter-example-1.7-SNAPSHOT.jar
src/main/resources/myTestModel.json
[main] INFO io.pivotal.calcite.sqlrewriter.Main - INSERT INTO hr.depts (deptno,
department_name) VALUES(696, 'Pivotal')
[main] INFO io.pivotal.calcite.sqlrewriter.Main - updated rows: 1
[main] INFO io.pivotal.calcite.sqlrewriter.Main - SELECT * FROM hr.depts
[main] INFO io.pivotal.calcite.sqlrewriter.Main - result: 696 , Pivotal ,
[main] INFO io.pivotal.calcite.sqlrewriter.Main - UPDATE hr.depts SET
department_name='interma' WHERE deptno = 696
[main] INFO io.pivotal.calcite.sqlrewriter.Main - updated rows: 1
[main] INFO io.pivotal.calcite.sqlrewriter.Main - SELECT * FROM hr.depts
[main] INFO io.pivotal.calcite.sqlrewriter.Main - result: 696 , interma ,
[main] INFO io.pivotal.calcite.sqlrewriter.Main - DELETE FROM hr.depts WHERE
deptno = 696
[main] INFO io.pivotal.calcite.sqlrewriter.Main - updated rows: 1
[main] INFO io.pivotal.calcite.sqlrewriter.Main - SELECT * FROM hr.depts
[main] INFO io.pivotal.calcite.sqlrewriter.Main - result:
[main] INFO io.pivotal.calcite.sqlrewriter.Main - Done
{code}
was (Author: hongxu ma):
Great job @[~tzolov]
I have tried your calcite-sql-rewriter, but failed on HAWQ:
https://github.com/tzolov/calcite-sql-rewriter/issues/1
Not support HAWQ now?
> Support update and delete on non-heap tables
>
>
> Key: HAWQ-304
> URL: https://issues.apache.org/jira/browse/HAWQ-304
> Project: Apache HAWQ
> Issue Type: Wish
> Components: Storage
>Reporter: Lei Chang
>Assignee: Lili Ma
> Fix For: 3.0.0.0
>
> Attachments: mutable_table.sql
>
>
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
