6 ms·

Thoughts on stuff like ClickHouse with JSON column support? Less upfront knowledge of columns needed.

hipadev23 2y ago
Clickhouse's revised JSON type is still quite new (in beta currently), but I'm hopeful for it. Their first attempt fell apart if the schema changed. [1] https://clickhouse.com/blog/a-new-powerful-json-data-type-for-clickhouse https://clickhouse.com/blog/a-new-powerful-json-data-type-fo...
brunoqc 2y ago
ClickHouse is open core too. If you care about that.
valyala 2y ago
JSON column type in ClickHouse [1] looks promising, since it allows storing wide events with arbitrary sets of fields. This feature is still in beta. Let's see how it will evolve. [1] https://clickhouse.com/docs/en/sql-reference/data-types/newjson https://clickhouse.com/docs/en/sql-reference/data-types/newj...
maxmcd 2y ago
It is a great step, but in my testing with the new JSON type if you use beyond 255 unique json locations/types (255 max_dynamic_types in their config) you will fall back to much worse performance for certain queries and aggregations. This is quite easy to hit with some of the suggestions in this blog post, especially if you are designing for multi-tenant use. For this clickhouse wide event lib I'm working on (not worth anyones time atm) I am still using this schema https://www.val.town/v/maxm/wideLib#L34-39 https://www.val.town/v/maxm/wideLib#L34-39 (which is from a Boris Tane talk https://youtu.be/00gW8txIP5g?t=801 https://youtu.be/00gW8txIP5g?t=801) for good multi-tenant performance. I hope clickhouse performance here can still be vastly improved, but I think it is a little awkward to get optimal performance with wide events today.
- bonobocop 2y ago
  A small question on the schema, I noticed that you have only “_now” as the Order By (so should just use that for the primary key). Do you expect any cross tenant queries? Just my feeling would be that I’d add the tenant ID before the timestamp as it should filter the parts more effectively
  - maxmcd 2y ago
    Yes, I think you are correct. In the video Boris/Baselime uses (_tenantId, _traceId, _timestamp). Will update that :)

Clickhouse's revised JSON type is still quite new (in beta currently), but I'm hopeful for it. Their first attempt fell apart if the schema changed. [1] https://clickhouse.com/blog/a-new-powerful-json-data-type-for-clickhouse https://clickhouse.com/blog/a-new-powerful-json-data-type-fo...

brunoqc 2y ago

ClickHouse is open core too. If you care about that.

valyala 2y ago

JSON column type in ClickHouse [1] looks promising, since it allows storing wide events with arbitrary sets of fields. This feature is still in beta. Let's see how it will evolve. [1] https://clickhouse.com/docs/en/sql-reference/data-types/newjson https://clickhouse.com/docs/en/sql-reference/data-types/newj...

maxmcd 2y ago

It is a great step, but in my testing with the new JSON type if you use beyond 255 unique json locations/types (255 max_dynamic_types in their config) you will fall back to much worse performance for certain queries and aggregations. This is quite easy to hit with some of the suggestions in this blog post, especially if you are designing for multi-tenant use. For this clickhouse wide event lib I'm working on (not worth anyones time atm) I am still using this schema https://www.val.town/v/maxm/wideLib#L34-39 https://www.val.town/v/maxm/wideLib#L34-39 (which is from a Boris Tane talk https://youtu.be/00gW8txIP5g?t=801 https://youtu.be/00gW8txIP5g?t=801) for good multi-tenant performance. I hope clickhouse performance here can still be vastly improved, but I think it is a little awkward to get optimal performance with wide events today.

bonobocop 2y ago
A small question on the schema, I noticed that you have only “_now” as the Order By (so should just use that for the primary key). Do you expect any cross tenant queries? Just my feeling would be that I’d add the tenant ID before the timestamp as it should filter the parts more effectively
- maxmcd 2y ago
  Yes, I think you are correct. In the video Boris/Baselime uses (_tenantId, _traceId, _timestamp). Will update that :)