[DISCUSSION] Support MERGE INTO SQL API

Posted by BrooksLi on
URL: http://apache-carbondata-dev-mailing-list-archive.168.s1.nabble.com/DISCUSSION-Support-MERGE-INTO-SQL-API-tp103140.html

[Background]
Currently, Carbondata do not have SQL command to support upsert

[Movitation]
Since we already have merge into dataset API, we can develop a MERGE INTO
SQL API. Since the merge into command is a litter bit complex, it may need
to develop a SQL parser with ANTLR to parse the SQL.

MERGEINTO SQL COMMAND

MERGE INTO [db_name.]target_table [AS target_alias]
USING [db_name.]source_table [AS source_alias]
ON <merge_condition>
[ WHEN MATCHED [ AND <condition> ] THEN <matched_action> ]
[ WHEN MATCHED [ AND <condition> ] THEN <matched_action> ]
[ WHEN NOT MATCHED [ AND <condition> ]  THEN <not_matched_action> ]

MERGE INTO TAREGT
USING SOURCE
ON SOURCE.ID=TARGET.ID
WHEN MATCHED THEN
UPDATE SET TARGET.NAME = SOURCE.NAME
WHEN NOT MATCHED THEN
INSERT (TARGER.ID, TARGET.NAME, TARGET,AGE) VALUES ( SOURCE.ID, SOURCE.NAME,
SOURCE.AGE)

TARGET TABLE
ID Name     Age
1 Jan      23
2 KK      22
3 Joe      27

SOURCE TABLE
ID Name Age
2 Steve 22
4 Mike        24


AFTER MERGE INTO COMMAND
TARGET TABLE
ID Name Age
1 Jan        23
2 Steve 22
3 Joe        27
4 Mike        24

In the first version of implement, MERGE INTO SQL command in Carbondata will
support basic table merge condition. And it will support up to 2 MATCHED
clause, and 1 NOT MATCHED clause.

In the send version, it can support the Carbondata feature, such as segment,
time travel version etc.


CarbonDataMergeIntoSQl.docx
<http://apache-carbondata-dev-mailing-list-archive.1130556.n5.nabble.com/file/t492/CarbonDataMergeIntoSQl.docx>  



--
Sent from: http://apache-carbondata-dev-mailing-list-archive.1130556.n5.nabble.com/