0

I store statistics data in MySQL and want to migrate it to the Clickhouse.

For one entity I have multiple statistics types. For examples: clicks, views, upvotes

And now I store it in the table which looks like: entity_id | datetime | type | value, where type is a constant integer which corresponds to one of statistics types described above.

Is it better to keep the same structure in Clickhouse or to create the next schema: entity_id | datetime | clicks | views | upvotes

As I understood, the Clickhouse is column-oriented DB. Does it mean that the Clickhouse has zero-cost altering when we want to add new columns, when it will be necessary?

Mikhail Stroev
  • 105
  • 1
  • 7

1 Answers1

3

Is it better to keep the same structure in Clickhouse or to create the next schema

if clicks | views | upvotes are related to same entity_id, then it makes sense to create a column for each value. By doing this, you will run one query and get a row containing all the data you need.

On the other hand, if you create table as entity_id | datetime | type | value, then you will have flexibility to have more statistic types(maybe mouse_enter as a 4th type). In the same time, you will have repeated entity_id and datetime values which will cost you disk usage and your table will have 3x rows. As you can see, there is a trade-off between each decision. It's up to you to decide which one to choose. If you think that only clicks | views | upvotes will be enough then it makes much more sense to use this schema.

Clickhouse is column-oriented DB. Does it mean that the Clickhouse has zero-cost altering when we want to add new columns, when it will be necessary

Yes, Clickhouse is column-oriented and each column is a separate file(s) on the disk. So there will be little to none overhead if you create a new column. But altering a column will take time if you use table mutations or alter the type, depending on the operation.

ramazan polat
  • 7,111
  • 1
  • 48
  • 76
  • Hm, it means that second case with the `type` field has only cons. Bcoz` ClickHouse has ~ none overhead if I create a new column. So when I want to add a new statistics type, I can do it instantly. Also there is another pros for the first case, if statistics columns has different data types. Did I understand correctly? – Mikhail Stroev Jul 18 '19 at 08:18
  • 1
    @MikhailStroev that is correct and I think you should have a column for each metric. Also, check this answer which might help: https://stackoverflow.com/questions/42391255/clickhouse-as-time-series-storage/46995670#46995670 – ramazan polat Jul 18 '19 at 08:25