Lesser known features of ClickHouse

Re:Lesser known features of ClickHouse by CSDude on Tuesday May 31, 2022 @04:53PM (#31574808)

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?

[ Parent | on HN ]

Re:Lesser known features of ClickHouse by niviksha on Tuesday May 31, 2022 @07:54PM (#31576362)

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) 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

2. Intro blog - https://github.com/SnellerInc/blogs/blob/main/introducing-sn...

[ Parent | on HN ]

Re:Lesser known features of ClickHouse by LunaSea on Tuesday May 31, 2022 @05:04PM (#31574936)

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

[ Parent | on HN ]

Re:Lesser known features of ClickHouse by gauravphoenix on Tuesday May 31, 2022 @06:07PM (#31575513)

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

[2] https://news.ycombinator.com/item?id=31111432

[ Parent | on HN ]

Re:Lesser known features of ClickHouse by datalopers on Tuesday May 31, 2022 @05:34PM (#31575231)

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

[ Parent | on HN ]

Re:Lesser known features of ClickHouse by gauravphoenix on Tuesday May 31, 2022 @06:09PM (#31575531)

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.

[ Parent | on HN ]

Re:Lesser known features of ClickHouse by gauravphoenix on Wednesday June 01, 2022 @10:39AM (#31582435)

> 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.

[ Parent | on HN ]

Re:Lesser known features of ClickHouse by qoega on Wednesday June 01, 2022 @08:28AM (#31580850)

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.

[ Parent | on HN ]

Re:Lesser known features of ClickHouse by nijave on Wednesday June 01, 2022 @06:31PM (#31588981)

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/

[ Parent | on HN ]

Re:Lesser known features of ClickHouse by pqyzwbq on Wednesday June 01, 2022 @05:31AM (#31579733)

you may check: 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.

[ Parent | on HN ]