[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
• Comet writer should support 2PC and staging output #3015
• Comet writer to support nested types #3023
• Comet writer to respect object_store_settings sent from Scala #3032

Describe the potential solution

No response

Additional context

No response