feat: support Merge-Into V1 #12350

JackTan25 · 2023-08-03T16:08:58Z

I hereby agree to the terms of the CLA available at: https://databend.rs/dev/policies/cla/

Summary

snowflake merge-into
postgres merge-into
Summary about this PR
merge into semantic:

for one row in target_table, if there is over one row matched with it from the source, we need to throw an exception. (delta_lake semantic)
when there are no matched clauses for the merge command and when there is nothing matched for the merge command even if there are matched clauses, use merge_insert_only (delta_lake implementation)
delta_lake support table, but we need to do more like streaming,streaming_v2,values, this is hard to build plan.In delta lake, The source can be anything that you can turn into a DataFrame (when using the Scala or Python API) or anything that you can turn into a SQL query (when using the SQL API). It could even be the result of joining a 100 tables.

delta-lake implementation survey

Closes #issue
feat: support MERGE #10174

This change is

a logic implementation for V1 . V1 is not ready for users, it's just a testing version. we will do more optimizations based on it. Some optimizations in the future:

tracking

vercel · 2023-08-03T16:09:02Z

The latest updates on your projects. Learn more about Vercel for Git ↗︎

1 Ignored Deployment

Name	Status	Preview	Comments	Updated (UTC)
databend	⬜️ Ignored (Inspect)	Visit Preview		Aug 29, 2023 11:35am

…merge_into_feat

JackTan25 · 2023-08-10T04:17:03Z

I need to transform the merge into statement as a select ,build the insert_source as a TableReference firstly. There is a shortcoming that we can't use optimizer for the query_source. Let's resolve it in V2.

…merge_into_feat

src/query/storages/fuse/src/operations/merge_into/mutator/merge_into_split_mutator.rs

src/common/exception/src/exception_code.rs

src/query/expression/src/kernels/filter.rs

src/query/ast/src/parser/statement.rs

Co-authored-by: Andy Lok <andylokandy@hotmail.com>

… into merge_into_feat

dantengsky · 2023-08-29T01:51:45Z

linux / sqllogic_standalone_base_parquet failed

0: statement failed: mysql client error: Server error: `ERROR HY000 (1105): InvalidRowIdIndex. Code: 1503, Text = row id column should be a column, but it's a scalar.'
[SQL] merge into t1 using (select * from t2 as t2) on t1.a = t2.a  when matched then update set t1.c = t2.c  when not matched then insert (a,b,c) values(t2.a,t2.b,t2.c);
at tests/sqllogictests/suites/base/09_fuse_engine/09_0026_merge_into:73

https://github.com/datafuselabs/databend/actions/runs/6000760718/job/16292350787?pr=12350#step:4:838

JackTan25 · 2023-08-29T03:02:06Z

There are some optimizations advised by @b41sh , I will do it firstly. For now, I use some concat in matched and not matched, there will be many memory copy, and I will use update expr (just use only the one and the same block, no need to split). Thanks

dantengsky

👍 LGTM

* try to add merge grammer * finish parser stage * finish match_clause and unmatch_clause * finish display for merge_into * finish grammer parser, start bind stage * remove useless codes * add MergeIntoPlan * fix distributed http error * support insert values for match * remove useless codes * revert match_insert * refactor merge_into_stmt, build table_reference * cover match pattern * cover match pattern * stash * try to add merge_into_source_scan * add merge_source_scan * bind join * stash * refactor merge_source * bind clauses * add new plan node * add interpreter and refactor bind * try to add processor * add physical plan * fix columns_set * add update/insert expression * remove unused codes and start to build processor and pipeline * add split operator * build source pipeline * finish pipeline build, continue to work on not-matched and matched processor * forbidden different schema for now * refactor expr and finish event schedule * add util split_by_expr * finish not match insert * fix * refactor merge into pipeline * add mutation logentries * add matched mutation * add setting * set not support computed expr * fix bug * fix col_index bug * fix pipeline bug and add basic tests * fix test * fix typos * fix typos * fix clippy * add more tests * add tests * fix bugs * use enable_experimental_merge_into adviced by BohuTang instead * add info * fix typo * fix ut * fix native failure * remove streamingV2Source, need to support streaming in next pr * rename vars adviced by b41sh * Update src/common/exception/src/exception_code.rs Co-authored-by: Andy Lok <andylokandy@hotmail.com> * remove useless comments * fix * unify codes, use bitmap to filter * fix check * unify codes * fix * check duplicate * check duplicate --------- Co-authored-by: Andy Lok <andylokandy@hotmail.com> Co-authored-by: dantengsky <dantengsky@gmail.com>

try to add merge grammer

bc8115e

JackTan25 changed the title ~~try to add merge grammer~~ feat: support Merge Aug 3, 2023

dantengsky changed the title ~~feat: support Merge~~ feat: support Merge-Into Aug 3, 2023

JackTan25 added 8 commits August 4, 2023 13:54

Merge branch 'main' of https://github.com/datafuselabs/databend into …

916604b

…merge_into_feat

finish parser stage

f7ccc95

finish match_clause and unmatch_clause

9a30ace

finish display for merge_into

a0dc670

finish grammer parser, start bind stage

a1fa3b8

remove useless codes

291e813

add MergeIntoPlan

5d30a2d

Merge branch 'main' of https://github.com/datafuselabs/databend into …

c0074b9

…merge_into_feat

vercel bot deployed to Preview August 6, 2023 07:58 View deployment

JackTan25 added 6 commits August 7, 2023 14:45

Merge branch 'main' of https://github.com/datafuselabs/databend into …

2ac1859

…merge_into_feat

Merge branch 'main' of https://github.com/datafuselabs/databend into …

9c71100

…merge_into_feat

fix distributed http error

021724b

support insert values for match

353f828

Merge branch 'main' of https://github.com/datafuselabs/databend into …

85131aa

…merge_into_feat

Merge branch 'main' of https://github.com/datafuselabs/databend into …

ebe2fca

…merge_into_feat

vercel bot deployed to Preview August 9, 2023 03:36 View deployment

JackTan25 and others added 4 commits August 9, 2023 13:49

remove useless codes

53900d5

Merge branch 'main' into merge_into_feat

12e5b65

revert match_insert

33f8818

Merge branch 'main' of https://github.com/datafuselabs/databend into …

e3c1654

…merge_into_feat

vercel bot deployed to Preview August 10, 2023 03:52 View deployment

JackTan25 changed the title ~~feat: support Merge-Into~~ feat: support Merge-Into V1 Aug 10, 2023

JackTan25 and others added 3 commits August 10, 2023 13:01

refactor merge_into_stmt, build table_reference

c6e1de6

Merge branch 'main' into merge_into_feat

615a568

Merge branch 'main' of https://github.com/datafuselabs/databend into …

f4703b2

…merge_into_feat

b41sh reviewed Aug 28, 2023

View reviewed changes

src/query/storages/fuse/src/operations/merge_into/mutator/merge_into_split_mutator.rs Outdated Show resolved Hide resolved

andylokandy reviewed Aug 28, 2023

View reviewed changes

src/common/exception/src/exception_code.rs Outdated Show resolved Hide resolved

src/query/expression/src/kernels/filter.rs Outdated Show resolved Hide resolved

src/query/ast/src/parser/statement.rs Outdated Show resolved Hide resolved

JackTan25 and others added 7 commits August 28, 2023 19:35

Update src/common/exception/src/exception_code.rs

7a173aa

Co-authored-by: Andy Lok <andylokandy@hotmail.com>

remove useless comments

0ef3ede

Merge branch 'merge_into_feat' of https://github.com/JackTan25/databend…

1201913

… into merge_into_feat

fix

17eb8f0

unify codes, use bitmap to filter

6b5c939

fix check

6a87a93

unify codes

15d171f

JackTan25 added 3 commits August 29, 2023 12:50

fix

91dce14

check duplicate

b8cae8a

check duplicate

1dfb53b

JackTan25 requested review from b41sh, BohuTANG and andylokandy August 29, 2023 06:35

dantengsky approved these changes Aug 29, 2023

View reviewed changes

Merge branch 'main' into merge_into_feat

1ade15a

vercel bot deployed to Preview August 29, 2023 07:10 View deployment

Merge branch 'main' into merge_into_feat

3dc9eba

vercel bot deployed to Preview August 29, 2023 07:43 View deployment

Merge branch 'main' into merge_into_feat

c77837c

vercel bot deployed to Preview August 29, 2023 09:26 View deployment

Merge branch 'main' into merge_into_feat

a1980d9

andylokandy approved these changes Aug 29, 2023

View reviewed changes

b41sh approved these changes Aug 29, 2023

View reviewed changes

JackTan25 merged commit cad282e into databendlabs:main Aug 29, 2023
53 checks passed

BohuTANG mentioned this pull request Sep 27, 2023

Release proposal: Nightly v1.3 #11868

Open

8 tasks

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

feat: support Merge-Into V1 #12350

feat: support Merge-Into V1 #12350

JackTan25 commented Aug 3, 2023 •

edited

Loading

vercel bot commented Aug 3, 2023 •

edited

Loading

JackTan25 commented Aug 10, 2023 •

edited

Loading

dantengsky commented Aug 29, 2023

JackTan25 commented Aug 29, 2023

dantengsky left a comment

feat: support Merge-Into V1 #12350

feat: support Merge-Into V1 #12350

Conversation

JackTan25 commented Aug 3, 2023 • edited Loading

Summary

vercel bot commented Aug 3, 2023 • edited Loading

JackTan25 commented Aug 10, 2023 • edited Loading

dantengsky commented Aug 29, 2023

JackTan25 commented Aug 29, 2023

dantengsky left a comment

Choose a reason for hiding this comment

JackTan25 commented Aug 3, 2023 •

edited

Loading

vercel bot commented Aug 3, 2023 •

edited

Loading

JackTan25 commented Aug 10, 2023 •

edited

Loading