Need to Make ClickHouse Cloud-Mode an Option

[cams-data]

With the release of the official ClickHouse Kubernetes operator, the new standard for production self-hosted ClickHouse clusters is to make use of the replicated database engine. This produces similar functionality to ClickHouse Cloud, in that it does not work with CREATE TABLE ... AS SELECT. SQLMesh already has a workaround for this with cloud mode. This mode can only be enabled by having "clickhouse.cloud" in the host name. I have added a CNAME to my dns to have my clickhouse host be resolved by clickhouse.cloud.my.domain.net to get around this. In the long run it would be good to have this property be a setting in ClickHouse connection config. Read more on the new paradigm here: https://clickhouse.com/docs/clickhouse-operator/overview

[cookieark7]

I'd like to pick this one up.

Plan: add cloud_mode to ClickhouseConnectionConfig as an optional bool. Default None keeps the current "clickhouse.cloud" in host detection so existing configs are untouched; true forces it on for self-hosted replicated clusters; false forces it off, including on a real *.clickhouse.cloud host, on the basis that an explicit setting should win.

Keeping the name cloud_mode to match the existing property and EngineRunMode.CLOUD — happy to rename if you'd prefer something that reads less like "the managed product" now that replicated self-hosted is a case.

cloud_mode also isn't documented today, so I'd add it to the connection options table in clickhouse.md and cover the replicated-database case in the modes-of-operation section.

@cams-data if you're still running the CNAME workaround, it'd be useful to have this verified against a real replicated cluster once it's up — I can only test the generated SQL locally.