Clickhouse Cluster Support about scratchdata

[poundifdef]

Sure.

[amrullahfarook]

Hey there @poundifdef! I came across Scratch DB and was interested in contributing, can I take up this issue?

[poundifdef]

I think there need to be 2 data structures: one users and servers.

The users data structure looks like this:

{
  "users": [
    {"api_key": "A","cluster": "A"},
    {"api_key": "B","cluster": "B"}
   ]
}

The servers structure would look like this:

{
  "servers": {
    "1.1.1.1": [
      {"cluster": "A", "shard": "A1"},
      {"cluster": "B", "shard": "B1"}
    ],
    "2.2.2.2": [
      {"cluster": "A", "shard": "A2"},
      {"cluster": "B", "shard": "B1"}
    ]
  }
}

Now we have a correlation between a user and their cluster with the users.cluster.

The servers structure should be able to directly translate to a clickhouse cluster XML config. For example, the above would translate to:

<clickhouse>
    <remote_servers>
        <A>
            <shard>
            <!-- shard A1 -->
                <replica>
                    <host>1.1.1.1</host>
                </replica>
            </shard>
            <shard>
            <!-- shard A2 -->
                <replica>
                    <host>2.2.2.2</host>
                </replica>
            </shard>
        </A>
        <B>
            <shard>
                <!-- shard B1, 2 replicas -->
                <replica>
                    <host>1.1.1.1</host>
                </replica>
                <replica>
                    <host>2.2.2.2</host>
                </replica>
            </shard>
        </B>
    </remote_servers>
</clickhouse>

I would also be open to the inverse - having the config store data which mirrors the Clickhouse required config, and using that to translate to figure out which clusters are hosted on which servers.

At the end of this project, we should be able to do the following:

1. Ensure that we are performing database options on the correct cluster depending on which API key was used
2. Write algorithms to choose which individual shard or replica to write data to