Posted by
BrooksLi on
URL: http://apache-carbondata-dev-mailing-list-archive.168.s1.nabble.com/DISCUSSION-Support-MERGE-INTO-SQL-API-tp103140p103170.html
Hi, Akash. Here are the answers to your questions. And I think we can have a
further investigation about merge into command in Carbondata.
Q1. when there are multiple whenMatched conditions what happens?
A1.
The number of whenMatched and notMatched can be unlimited Since the
Carbondata API for merge matched and unmatched clause are unlimited.
However, the order of WHEN MATCHED and NOTMATCHED matters. The WHEN MATCHED
clause should be placed before the NOTMATCHED.
And once it matched the first matched/not matched clause, it will just begin
to execute and ignore the others.
Q2. validations like when matched can have either update or delete, this
validation
A2.
Yes, each when matched can only have either update/ delete once.
And we need to validate the SQL Text, it the SQL Text do not match the rule
of Merge Into, it will just return an SQL Parser Error back to the users.
Q3. when the condition is not present for when matched, then how to update
or
delete complete data,
example can be given, how the sql will look like
A3.
When there is not when matched clause, this merge into command can only use
to act as INSERT INTO command when it meets the not matched clause.
When all WHENMATCHED Clauses are missed, it begins to check the
WHENNOTMATCHED Clause.
Q4. how to update all columns of the target table from the source table and
how the
SQL will be and validations can be there
A4.
For this problem, I think it may only be available to match all the
corresponding columns manually.
MERGE INTO events
USING updates
ON events.eventId = updates.eventId
WHEN MATCHED THEN
UPDATE SET events.data = updates.data, events.name = updates.name
Q5. validation for whenNot matched to have only insert
A5.
In matched clause: it can only have UPDATE/ DELETE execution
In not matched clause: it can only execute INSERT INTO
Q6. what happens when the columns do not match from source and target
A6.
I think it will do nothing
Q7. how to insert complete data.
A7.
When it do not match any match clause, it will run to the when not matched
clause.
I think there can be two solutions, on the solution is to insert *
The SQL look like this
MERGE INTO logs
USING newDedupedLogs
ON logs.uniqueId = newDedupedLogs.uniqueId
WHEN NOT MATCHED
THEN INSERT *
And the other solution is to match the columns one by one.
We can further discuss these rules and limits of MERGE INTO since the code
is still being implemented.
Thanks
Brooks Li
--
Sent from:
http://apache-carbondata-dev-mailing-list-archive.1130556.n5.nabble.com/