Add support to replicated clickhouse cluster

[jderusse]

When running goose on a clickhouse cluster, it fails to get the list of "previous" migrations (sometime querying a node a get a partial list of migrations)

Possible fix:

1. change the engine

- ENGINE = MergeTree()
+ ENGINE = ReplicatedMergeTree('/clickhouse/{cluster}/tables/{shard}/{database}/{table}', '{replica}')

Not sure how this can be defined in goose via the CreateTable function (or should it be a new driver ? 🤔 )

[hiasr]

It could be possible to have it depend on an environment variable. To be able to support both replicated and non-replicated setups I do the following in my own migrations:

ENGINE = ${CH_ENGINE:-ReplicatedMergeTree}

Something similar could be possible here!

[mfridman]

I'm curious, are folks using goose as a Go library or a standalone CLI tool?

I was thinking of setting up github.com/pressly/goose-dialects with community-supported dialects, which can include the various flavors of ClickHouse requests. Still need to think through whether this is a good idea or not, and whether it'll even work. But it's one option, and would at least satisfy the "goose as a provider in Go code".

[jeevanragula]

@mfridman Golang migrate takes the cluster name as config and creates the table based on the config option.

https://github.com/golang-migrate/migrate/blob/master/database/clickhouse/clickhouse.go

[kostya05983]

Any updates on this issue? it will be great to have a possibility to configure engine here

goose/internal/dialect/dialectquery/clickhouse.go

Line 16 in 46ae662

ENGINE = MergeTree()

[mfridman]

Thanks @jeevanragula, that's an interesting approach. I do wonder if that's a bit limiting, as I've seen requests for other types of ClickHouse configurations. (Need to dive into this a bit more to remember what those were to make sure whatever implementation we come up with ends up being sufficient for most users).

[jeevanragula]

@mfridman I think Supporting ReplicatingMergeTree should be important compared to other Engine types as in production, everyone uses cluster.
Also, The Engine Type can be a configuration using Env Variable or command arguments.

[mfridman]

So would folks prefer it was semi-structured like the example linked above to (#869 (comment)), or should it be more free-form like a raw string (I bet this has some downsides).

[kostya05983]

I think, it should be in free-form, like a raw string, 'cause when you create ReplicatedMergeTree, you specify path in zookeeper and this path can contain different templates variables like '{shard}', '{replica}'. Moreover it would be great to create table for versioning with ON CLUSTER syntax