Feature: Merge Into Optimizations

[JackTan25]

Merge Into is supported by #12350, but it's not completed. We have many things to do.

A basic implemention :

Optimizations

• merge into in distributed mode improvement bug: merge into in distributed mode improvement. #13970
• support auto spill for merge into. bug: Spill is not open for merge into. #13930 assigned to @JackTan25
• full optimizer import for merge into feat: import more join strageties for merge into (new distributed and standalone stargety ) #13950 in merge into runtime filter branch.
• merge into should output the performed affected for insert/delete/update rows, details in feat: merge into returns how many rows update/delete/inserted  #13855 assigned to @JackTan25 feat: add merge status #13903
• reduce segments locations memory for distributed merge into (in fact, we will transform all segment locations
  hashmap(segmentId -> segment location), in src/query/sql/src/executor/physical_plans/physical_merge_into.rs' struct MergeInto, the segments field will hold all segments in every node, in fact every node just need to hold the info it need). (this optimization can be assigned to @JackTan25 or @SkyFan2002 )
• meta table (reference to BigQuery's big meta paper) (this task is taken by @SkyFan2002 )
• a more powerful hashTable which supports split data block like above.(assign to @JackTan25 ) feat: target build optimization for merge into #14066
• use bitmap for duplicate check (assign to @JackTan25 or @SkyFan2002)
• optimizer for query source. fix: Fix merge into bug #13744
• support not match by source (full outer join).
• insert-only optimization. feat: matched only and insert only pipeline separate #13680
• pre-compute cols for macth_expr/not match expr.
• parallel merge into feat: improve parallel run for merge into  #13045
• distributed merge into feat: support distributed merge_into #13151
• support streaming source, table, values feat: support alias for source to support stage #12968 (12968 is enough)
• support computed expr feat: add computed expr for merge into #12905
• support input schema which is not the same with table schema (filling default columns) feat: fill default columns for not match #12747
• support star "*" function to enhance merge into. feat: support star "*" for merge into #12906
• fetch complete fields for update clause to simplify block split. refactor: remove concat #12622 (fetch all fields when there is update)
• matched and unmatched clauses exprs (only target table filed) push down (assign to @JackTan25) feat: target only expr push down #13082
• use if func and merge update and delete expos to optimize when the updates and deletes are separate (advised by @b41sh ) refactor: remove concat #12622 （a better way）
• use origin block to do insert and update (advised by @b41sh ) refactor: remove concat #12622
• push down check duplicate and idempotent delete feat: push down check duplicate and idempotent delete #12780

[ct20000901]

Hello, may I help with completing this part of the development? I'm quite interested in this aspect.

[BohuTANG]

Hello, may I help with completing this part of the development? I'm quite interested in this aspect.

Sure, thanks.

But we are in the early stage of the merge into design, it's better to wait us some time when the fundamental working is done, we may spill out some tasks, I think then is good.

[ct20000901]

Hello, may I help with completing this part of the development? I'm quite interested in this aspect.

Sure, thanks.

But we are in the early stage of the merge into design, it's better to wait us some time when the fundamental working is done, we may spill out some tasks, I think then is good.

Perhaps this question may seem a bit forward, but can I also participate in the process of basic design and development for 'merge_into'? I plan to attempt an internship with Databend after completing one or two issues, so I would like to have a deeper understanding of Databend's overall design.

[BohuTANG]

@ct20000901 Feel free to take any task/issue you are interested :)

[JackTan25]

@ct20000901 you can try this one #12901 firstly. please comment "/assign me" at #12901.