4 ms·

We have a use case for wide columnar data, used for mostly performance analytics. There are many types of events that share same columns, but mostly total uniqu

by CSDude 4y ago

We have a use case for wide columnar data, used for mostly performance analytics. There are many types of events that share same columns, but mostly total unique columns are 20x of a typical events columns. My use case is filtering by some boolean logic and aggregating them. I use Elasticsearch with a mapping that does not tokenize text fields, (all strings are keyword type) and it works very well. I can add nodes as I want and adjust shards with ease with spot instances and make it cost effective. I have not found a way/tool to replace this. Many of the tools fail at dynamic data with cardinality. Wanted to use Clickhouse like this, adding columns as they are discovered but it did not go well, but it has been a while. Also adding replicas is not as easy as Elasticsearch. Does anyone have a similar use case implemented with Clickhouse when data is not known before hand and unfolds as time goes by?

LunaSea 4y ago
I think you should give PostgreSQL's intarray extension a try, especially using the "@@" QUERY_INT boolean logic queries over GIN indexed columns.
datalopers 4y ago
CH approach: Load the dynamic data into a JSON field and use materialized columns when needed for performance beyond calling JSONExtract().
- gauravphoenix 4y ago
  the "Object" data type works very well and is quite performant. But do note that if you have lots of "schemas" you will need to create multiple such columns that represent such columns e.g. one column each for GitHub,GitLab etc.
  - qoega 4y ago
    Is it really the case? Assume you have column with schema name and one with json object. And your materialised view/JsonExtract can be dispatched by schema name for a row. I see the only suboptimal part if different schemas have different types for the same field and it has to convert it to String.
  - gauravphoenix 4y ago
    > And your materialised view/JsonExtract can be dispatched by schema name for a row. How would that look like? MatViews in CH are like insert triggers so does it mean that one MatView per schema? >I see the only suboptimal part if different schemas have different types for the same field and it has to convert it to String. Agree, this is what I was actually after and it actually happens with system data sometimes- you can have "user" field as an int and string in different schemas.
gauravphoenix 4y ago
We at Dassana[1] use clickhouse underneath and solve the exact same problem. Have a look and feel free to reach out to me if you have any questions. You can find more details on my ShowHN post[2]. [1] https://lake.dassana.io https://lake.dassana.io [2] https://news.ycombinator.com/item?id=31111432 https://news.ycombinator.com/item?id=31111432
niviksha 4y ago
Thanks for sharing this. It is a very interesting problem that highlights some of the technical challenges of working with modern event data, which happens to 'prefer' being semi-structured (i.e JSON is the most natural serialization format while creating events). It's also something we're working on! Shameless plug - I happen to work at Sneller (sneller.io, open source at https://github.com/SnellerInc/sneller https://github.com/SnellerInc/sneller) that might be interesting to you. A couple of key ideas - first, we bypass the need for any sort of 'semi-structured to relational' ETL/ELT overhead by running vectorized SQL on a (compressed) binary form of the JSON data which preserves its original structure. So we're schema-on-read first and foremost - you don't need to worry about adding new fields in the source JSON as long as your queries know of these new fields. Second, we completely separate storage from compute. Unlike CH we don't use local disk as any sort of storage tier, and use cloud object stores as our _primary_ storage tier. So all your data (including the compressed binary version of your source JSON) lives in s3 buckets in your control. Feel free to check us out and let us know what you think! 1. Github - https://github.com/SnellerInc/sneller https://github.com/SnellerInc/sneller 2. Intro blog - https://github.com/SnellerInc/blogs/blob/main/introducing-sneller.md https://github.com/SnellerInc/blogs/blob/main/introducing-sn...
pqyzwbq 4y ago
you may check: https://github.com/jitsucom/jitsu https://github.com/jitsucom/jitsu. "Jitsu is an open-source Segment alternative. Fully-scriptable data ingestion engine for modern data teams. Set-up a real-time data pipeline in minutes, not days" You can create an API endpoint, and send those JSON to it. In the "destination" part, it can sync to clickhouse (one of many choices, like redshift, snowflake,besides clickhouse) very quickly, and flatten the JSON into columns. If there is new key found in JSON, it will create a new column in clickhouse.
nijave 4y ago
Uber has a blog post about using a hybrid static column/array approach for log files that might be similar to your use case (they replaced Elasticsearch installations) https://eng.uber.com/logging/ https://eng.uber.com/logging/

by CSDude 4y ago

LunaSea 4y ago

I think you should give PostgreSQL's intarray extension a try, especially using the "@@" QUERY_INT boolean logic queries over GIN indexed columns.

datalopers 4y ago

CH approach: Load the dynamic data into a JSON field and use materialized columns when needed for performance beyond calling JSONExtract().

gauravphoenix 4y ago
the "Object" data type works very well and is quite performant. But do note that if you have lots of "schemas" you will need to create multiple such columns that represent such columns e.g. one column each for GitHub,GitLab etc.
- qoega 4y ago
  Is it really the case? Assume you have column with schema name and one with json object. And your materialised view/JsonExtract can be dispatched by schema name for a row. I see the only suboptimal part if different schemas have different types for the same field and it has to convert it to String.
- gauravphoenix 4y ago
  > And your materialised view/JsonExtract can be dispatched by schema name for a row. How would that look like? MatViews in CH are like insert triggers so does it mean that one MatView per schema? >I see the only suboptimal part if different schemas have different types for the same field and it has to convert it to String. Agree, this is what I was actually after and it actually happens with system data sometimes- you can have "user" field as an int and string in different schemas.

gauravphoenix 4y ago

We at Dassana[1] use clickhouse underneath and solve the exact same problem. Have a look and feel free to reach out to me if you have any questions. You can find more details on my ShowHN post[2]. [1] https://lake.dassana.io https://lake.dassana.io [2] https://news.ycombinator.com/item?id=31111432 https://news.ycombinator.com/item?id=31111432

niviksha 4y ago

Thanks for sharing this. It is a very interesting problem that highlights some of the technical challenges of working with modern event data, which happens to 'prefer' being semi-structured (i.e JSON is the most natural serialization format while creating events). It's also something we're working on! Shameless plug - I happen to work at Sneller (sneller.io, open source at https://github.com/SnellerInc/sneller https://github.com/SnellerInc/sneller) that might be interesting to you. A couple of key ideas - first, we bypass the need for any sort of 'semi-structured to relational' ETL/ELT overhead by running vectorized SQL on a (compressed) binary form of the JSON data which preserves its original structure. So we're schema-on-read first and foremost - you don't need to worry about adding new fields in the source JSON as long as your queries know of these new fields. Second, we completely separate storage from compute. Unlike CH we don't use local disk as any sort of storage tier, and use cloud object stores as our _primary_ storage tier. So all your data (including the compressed binary version of your source JSON) lives in s3 buckets in your control. Feel free to check us out and let us know what you think! 1. Github - https://github.com/SnellerInc/sneller https://github.com/SnellerInc/sneller 2. Intro blog - https://github.com/SnellerInc/blogs/blob/main/introducing-sneller.md https://github.com/SnellerInc/blogs/blob/main/introducing-sn...

pqyzwbq 4y ago

you may check: https://github.com/jitsucom/jitsu https://github.com/jitsucom/jitsu. "Jitsu is an open-source Segment alternative. Fully-scriptable data ingestion engine for modern data teams. Set-up a real-time data pipeline in minutes, not days" You can create an API endpoint, and send those JSON to it. In the "destination" part, it can sync to clickhouse (one of many choices, like redshift, snowflake,besides clickhouse) very quickly, and flatten the JSON into columns. If there is new key found in JSON, it will create a new column in clickhouse.

nijave 4y ago

Uber has a blog post about using a hybrid static column/array approach for log files that might be similar to your use case (they replaced Elasticsearch installations) https://eng.uber.com/logging/ https://eng.uber.com/logging/