Re: [DISCUSSION] Support MERGE INTO SQL API

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

+1,

Thanks for planning to implement this.

Please define the limitations or scope in more detail for WHEN MATCHED  and
WHEN NOT MATCHED.
For example, when  NOT MATCHED, can UPDATE also supported? (I guess only
insert is supported)

Thanks,
Ajantha

On Thu, Nov 5, 2020 at 8:10 AM BrooksLi <[hidden email]> wrote:

> [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/
>