+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 <[email protected]> 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/ >
