How do I create a custom version table for clickhouse?

[gumi-bd]

I am running clickhouse on a kubernetes set up and I want to maintain a separate version file for every table that I create. In order to maintain consistency I created a version table that uses a distributed engine over a sharded table as follows:

ddl = f"""
    CREATE TABLE IF NOT EXISTS {db}.sharded_{table} ON CLUSTER 'all-sharded'
(
    id UInt64,
    version_id UInt64,
    is_applied UInt8,
    tstamp DateTime DEFAULT now()
)
ENGINE = ReplicatedMergeTree('/version_tables/{{shard}}/sharded_{table}', '{{replica}}')
ORDER BY id"""

    ddl2 = f"""
CREATE TABLE IF NOT EXISTS {db}.{table} ON CLUSTER 'all-sharded'
AS {db}.sharded_{table}
ENGINE = Distributed('all-sharded', 'default', 'sharded_{table}', rand());"""

However when I run goose up with the table as an option, I get no next version found as an error. However when I run it without the table option it runs smoothly but does not store the migrations anywhere. How can I make the version table so that it runs without failure and is consistent no matter which pod I establish a connection with through goose

[mfridman]

Goose owns creating the version table, it is created implicitly when running goose commands.

https://github.com/pressly/goose/blob/main/internal/dialects/clickhouse.go#L18-L28

If you need different tables, I'd suggest looking into the goose provider (goose.WithTableName) or the CLI with the --table command to give different names to differenent migration sets.