[jira] [Comment Edited] (HIVE-10924) add support for MERGE statement

["Lefty Leverenz (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/HIVE-10924?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14720334#comment-14720334 ] 

Lefty Leverenz edited comment on HIVE-10924 at 8/30/15 3:37 AM:
----------------------------------------------------------------

h3. Feature design notes
Hive supports [multi-insert statement|https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML#LanguageManualDML-InsertingdataintoHiveTablesfromqueries].  The idea is that you can execute a select statement and split the result stream into several to write to multiple targets.

This matches very closely to what MERGE statement needs to do.
When modeling MERGE as multi-insert, we'd split the stream into 2 stream, 1 for the insert part, 1 for update part but write both results to the same table.  
Section 14.12 of  ISO/IEC 9075-2:2011(E) (SQL 2011) defines MERGE statement.   

Suppose we have tables 
{code:SQL}
CREATE TABLE target(a int, b int, c int);
CREATE TABLE source(x int, y int, z int);
{code}

Then an example that covers most possibilities might look like this
{code:SQL}
MERGE INTO target 
USING source ON b = y
WHEN MATCHED AND c + 1 + z > 0
THEN UPDATE SET a = 1, c = z
WHEN NOT MATCHED AND z IS NULL
THEN INSERT(a,b) VALUES(z, 7)
{code}
\\
\\
And is interpreted as follows
\\
\\
|| Line || Statement Part || Notes ||
| 1 | {code:SQL} MERGE INTO target {code} | Specifies the table being modified |
| 2 | {code:SQL} USING source {code} | specifies the source of the data which may be a table or expression such as SELECT … FROM … |
| 3 | {code:SQL}     ON b = y {code} | is interpreted like exactly like an ON clause of a JOIN between source and target. |
| 4 | {code:SQL} WHEN MATCHED {code} | Applies if expr in ON is true |
| 5 | {code:SQL}     AND c + 1 + z > 0 {code} | Additional predicate to test before performing the action. |
| 6 | {code:SQL}     THEN UPDATE SET a = 1, c = z {code} | May be UPDATE or DELETE.  The later deletes the row from target.  SET clause is exactly like in regular UPDATE stmt. |
| 7 | {code:SQL} WHEN NOT MATCHED {code} | Applies if expr in ON is false |
| 8 | {code:SQL}     AND z IS NULL {code} | Additional predicate to test before performing the action. |
| 9 | {code:SQL} THEN INSERT(a,b) VALUES(z, 7){code} | Insert to perform on target. |
\\
\\

Then the "equivalent" _multi-insert statement_ looks like this:
\\
\\
|| Statement Part ||  Refernce to previous table ||
| {code:SQL} FROM (SELECT * FROM target RIGHT OUTER JOIN SOURCE ON b = y) {code} | Lines 1 - 3 | 
| {code:SQL} INSERT INTO target(a,c) SELECT 1, z {code} | This represents the update part of merge; Line 6 |
| {code:SQL} WHERE c + 1 + z > 0 {code} | Line 5 |
| {code:SQL} AND b = y {code} | Only include ‘matched’ rows; Line 4 |
| {code:SQL} INSERT INTO target(a,b) SELECT z, 7 {code} | This represents the ‘insert’ part of merge; Line 9 |
| {code:SQL} WHERE z IS NULL {code} | Line 8 |
| {code:SQL} AND a = null AND b = null AND c = null; {code} | Only include ‘not matched’ rows; Line 7 |

h4. Some caveats
# Current multi-insert doesn’t support writing to the same table more than once.  Can we fix this?
# This requires the same change as for multi-statement txn, that is to support multiple delta files per transaction. (HIVE-11030)
# Requires annotating each insert (of multi-insert) with whether it’s doing update/delete or insert


Since Hive can already compile an operator pipeline for such a _multi-insert statement_ (almost) support for MERGE doesn't require additional operators.
Also, Update/Delete are actually compiled int Insert statements.


was (Author: ekoifman):
h3. Feature design notes
Hive supports [multi-insert statement|https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML#LanguageManualDML-InsertingdataintoHiveTablesfromqueries].  The idea is that you can execute a select statement and split the result stream into several to write to multiple targets.

This matches very closely to what MERGE statement needs to do.
When modeling MERGE as multi-insert, we'd split the stream into 2 stream, 1 for the insert part, 1 for update part but write both results to the same table.  
Section 14.12 of  ISO/IEC 9075-2:2011(E) (SQL 2011) defines MERGE statement.   

Suppose we have tables 
{code:SQL}
CREATE TABLE target(a int, b int, c int);
CREATE TABLE source(x int, y int, z int);
{code}

Then an example that covers most possibilities might look like this
{code:SQL}
MERGE INTO target 
USING source ON b = y
WHEN MATCHED AND c + 1 + z > 0
THEN THEN UPDATE SET a = 1, c = z
WHEN NOT MATCHED AND z IS NULL
THEN INSERT(a,b) VALUES(z, 7)
{code}
\\
\\
And is interpreted as follows
\\
\\
|| Line || Statement Part || Notes ||
| 1 | {code:SQL} MERGE INTO target {code} | Specifies the table being modified |
| 2 | {code:SQL} USING source {code} | specifies the source of the data which may be a table or expression such as SELECT … FROM … |
| 3 | {code:SQL}     ON b = y {code} | is interpreted like exactly like an ON clause of a JOIN between source and target. |
| 4 | {code:SQL} WHEN MATCHED {code} | Applies if expr in ON is true |
| 5 | {code:SQL}     AND c + 1 + z > 0 {code} | Additional predicate to test before performing the action. |
| 6 | {code:SQL}     THEN UPDATE SET a = 1, c = z {code} | May be UPDATE or DELETE.  The later deletes the row from target.  SET clause is exactly like in regular UPDATE stmt. |
| 7 | {code:SQL} WHEN NOT MATCHED {code} | Applies if expr in ON is false |
| 8 | {code:SQL}     AND z IS NULL {code} | Additional predicate to test before performing the action. |
| 9 | {code:SQL} THEN INSERT(a,b) VALUES(z, 7){code} | Insert to perform on target. |
\\
\\

Then the "equivalent" _multi-insert statement_ looks like this:
\\
\\
|| Statement Part ||  Refernce to previous table ||
| {code:SQL} FROM (SELECT * FROM target RIGHT OUTER JOIN SOURCE ON b = y) {code} | Lines 1 - 3 | 
| {code:SQL} INSERT INTO target(a,c) SELECT 1, z {code} | This represents the update part of merge; Line 6 |
| {code:SQL} WHERE c + 1 + z > 0 {code} | Line 5 |
| {code:SQL} AND b = y {code} | Only include ‘matched’ rows; Line 4 |
| {code:SQL} INSERT INTO target(a,b) SELECT z, 7 {code} | This represents the ‘insert’ part of merge; Line 9 |
| {code:SQL} WHERE z IS NULL {code} | Line 8 |
| {code:SQL} AND a = null AND b = null AND c = null; {code} | Only include ‘not matched’ rows; Line 7 |

h4. Some caveats
# Current multi-insert doesn’t support writing to the same table more than once.  Can we fix this?
# This requires the same change as for multi-statement txn, that is to support multiple delta files per transaction. (HIVE-11030)
# Requires annotating each insert (of multi-insert) with whether it’s doing update/delete or insert


Since Hive can already compile an operator pipeline for such a _multi-insert statement_ (almost) support for MERGE doesn't require additional operators.
Also, Update/Delete are actually compiled int Insert statements.

> add support for MERGE statement
> -------------------------------
>
>                 Key: HIVE-10924
>                 URL: https://issues.apache.org/jira/browse/HIVE-10924
>             Project: Hive
>          Issue Type: New Feature
>          Components: Query Planning, Query Processor, Transactions
>    Affects Versions: 1.2.0
>            Reporter: Eugene Koifman
>            Assignee: Eugene Koifman
>
> add support for 
> MERGE INTO tbl USING src ON … WHEN MATCHED THEN ... WHEN NOT MATCHED THEN ...



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)