[Feature] Support compaction for CDC Sink for bucket-unaware table

### Search before asking

- [x] I searched in the [issues](https://github.com/apache/paimon/issues) and found nothing similar.

### Motivation

I noticed compaction is disabled for CDC Sink for unaware bucket tables, as noted in:

https://github.com/apache/paimon/blob/2d825d5e4eafd2e34b5a7300503a1ae79a60dac0/paimon-flink/paimon-flink-cdc/src/main/java/org/apache/paimon/flink/sink/cdc/CdcAppendTableSink.java#L33-L37

We have an unaware bucket append-only table (Kafka to Paimon sink, by extending [KafkaSyncTableAction](https://github.com/apache/paimon/blob/866dd0bd039e4ad3a8da3725f5b01e86f9b64078/paimon-flink/paimon-flink-cdc/src/main/java/org/apache/paimon/flink/action/cdc/kafka/KafkaSyncTableAction.java#L27)), and I'd like to enable compaction to avoid having too many small files. I was wondering if there are any plans to support compaction for this use-case.

### Solution

_No response_

### Anything else?

_No response_

### Are you willing to submit a PR?

- [x] I'm willing to submit a PR!

Start by reading CdcAppendTableSink.java at the referenced compaction-disabled code and then KafkaSyncTableAction.java to understand the bucket-unaware CDC sink path. Trace how the append-only table is configured and determine what would be required for compaction without buckets. Done means the described CDC Sink use case supports compaction and avoids an excess of small files.

Written by the indexing model from the issue text.