[EPIC] Improve Comet Native writer

[comphead]

What is the problem the feature request solves?

This epic to track Comet native writer issues

• The Comet writer adds unnecessary transition CometColumnarToRow #2958
• Comet fails when local writer enabled #2944
• Comet writer doesnt support INSERT OVERWRITE #2957
• Support HDFS writes with Comet writer via OpenDAL #2890
• Comet writer doesn't respect OVERWRITE mode #2970
• Comet writer executes twice #2971
• Comet writer doesn't create _SUCCESS file #2985

Describe the potential solution

No response

Additional context

No response