11 ms·

ClickHouse cost-efficiency in action: analyzing 500B rows on an Intel NUC

216 points by hodgesrm 7y ago

jgrahamc 7y ago
We use ClickHouse extensively and it's been great: https://blog.cloudflare.com/http-analytics-for-6m-requests-per-second-using-clickhouse/ https://blog.cloudflare.com/http-analytics-for-6m-requests-p...
- chupasaurus 7y ago
  You've lost a Russian IT meme in translation. We use the verb "to use brakes" to describe that something works slowly. There was a long story of threads about Java performance, until the meme was solidified after the news from 2005 DARPA Grand Challenge (racing competition for autonomous robotic self-driven cars): a car named Tommy by Jefferson Team which was running Java under Linux haven't used breaks before a turn and crashed to the wall at 70 mph, hence Java runs fast and Java doesn't use brakes were both described perfectly by the same sentence. Yandex used the meme to advertise Clickhouse for engineers. edit: formatting edit2: brakes instead of breaks, wtf
  - deleted 7y ago
    [deleted]
  - nkrisc 7y ago
    I think you mean "brakes," as I thought you meant "breaks" like in an iterator. Not that familiar with Java though, just assuming based on the reference to a vehicle failing to brake before a turn. I thought maybe it meant using break statements in your loops slows things down or something.
    - chupasaurus 7y ago
      Yeah, my bad.
  - PeterZaitsev 7y ago
    I think this is poor translation. In Russian "Тормозить" may mean to "use brakes" when applied to a car or just "be slow" when applied to a program (or a person). "MySQL сегодня тормозит" would mean MySQL is acting slow today, not what it is using brakes. So that meme I think is best translated as "Clickhouse is never slow" or "Clickhouse never acts slow"
kresten 7y ago
What’s the tldr on why it is fast?
- eecc 7y ago
  Precomputed materialized views
  - brobinson 7y ago
    Yeah, this post could have been titled "Why Materialized Views are Awesome"
    - bdcravens 7y ago
      Clickhouse is also crazy fast without materialized views - I've only done some PoC's against it, but in loading a largish data set of raw invoice CSVs, I was very impressed with the performance compared to our standard RDBMS.
      - tumanian 7y ago
        that sounds like a non-canonical use of clickhouse. Wouldnt a good RDBMS be a better fit for invoice data? This is on the surface, of course, really interested in what is this invoice data like, and what queries are you trying to run on them.
- polskibus 7y ago
  Heavily optimized column store, incl use of SSE instructions, etc. Moreover, some architectural tradeoffs mentioned at https://clickhouse.yandex/docs/en/introduction/features_considered_disadvantages/ https://clickhouse.yandex/docs/en/introduction/features_cons...
- SloopJon 7y ago
  I'd say the materialized view is the main thing: > Thus, we add the following materialized view ... At the end we should have 1440 times fewer rows in the aggregate than the source table. The cost of populating that view is amortized over the 17.5 hours it took to load the data.
deleted 7y ago
[deleted]
patelh 7y ago
Not exactly a good comparison if you don't generate the data the same way for the test setup. Your generated data is more compressible by clickhouse, that skews the comparison. Would have been better to not change the test data if you wanted to do a comparison.
- sheeshkebab 7y ago
  I bet results would be roughly the same even for the exact same dataset - Scylla and other K/v data stores can’t compete with columnar databases that are purpose built for complex analytics queries. the many orders of magnitude query performance differences (not to count storage, compute overhead) show it enough. It was kind of a crummy use case for Scylla anyway (it’s a transactional write store, not an analytics engine)
- hodgesrm 7y ago
  The important difference is that we used a more realistic temperature profile, which as you say does affect compression for that column. Schema design (including sort order, compression, and codecs) for the remaining columns is just good ClickHouse practice. Much of the storage and I/O savings is in the date, time, and sensor_id and columns. It's also useful to note that the materialized view results would be essentially the same no matter how you generate and store data because the materialized view down-samples temperature max/min to daily aggregates. The data are vastly smaller no matter how you generate them. The article illustrates that if you really had such an IoT app and designed it properly you could run analytics with surprisingly few resources. I think that's a significant point.
  - patelh 7y ago
    Then you should provide results for both test datasets to make the point of using a more realistic approach. Materialized views are not news, nor is properly designed analytics applications. For me the importance is how click house is better and why.
    - manigandham 7y ago
codexon 7y ago
One thing I haven't seen anyone note about clickhouse though which would be really important to many for data durability, is that it does not use fsync anywhere at all.
- caust1c 7y ago
  It's pretty clearly laid out in the docs. Hopefully anyone seriously considering using Clickhouse reads the docs thoroughly and understands what they're implementing.
  - codexon 7y ago
    What do you mean clearly laid out? This is the only mention of fsync I could find through google or their own search function. https://clickhouse.yandex/docs/en/operations/settings/settings/#fsync-metadata https://clickhouse.yandex/docs/en/operations/settings/settin...
    - caust1c 7y ago
      The title of the page might be a little snarky, but it's in the introduction that transactional queries are not supported: https://clickhouse.yandex/docs/en/introduction/features_considered_disadvantages/ https://clickhouse.yandex/docs/en/introduction/features_cons... Sure it's not specifically about `fsync` but presumably this is what the consumer of the database actually wants to know.
      - codexon 7y ago
        Fsync is not synonymous with transactions. Not using fsync anywhere means there's a wide window that can be over 10 minutes long when data can be lost when a server gets an unplanned shutdown.
        valyala 7y ago
        Btw, it should be easy adding fsync to ClickHouse. For instance VictoriaMetrics uses similar file format as ClickHouse, and it issues proper fsyncs at least every second, so it may lose only the last second of data on unclean shutdown such as OOM, hardware reset or `kill -9`. [1], [2]. [1] https://medium.com/@valyala/wal-usage-looks-broken-in-modern-time-series-databases-b62a627ab704 https://medium.com/@valyala/wal-usage-looks-broken-in-modern... [2] https://medium.com/@valyala/how-victoriametrics-makes-instant-snapshots-for-multi-terabyte-time-series-data-e1f3fb0e0282 https://medium.com/@valyala/how-victoriametrics-makes-instan...
tkyjonathan 7y ago
This is going to be your next cloud agnostic data warehouse.
- FridgeSeal 7y ago
  For what it’s worth, I’ve used Clickhouse and Snowflake and I strongly prefer Clickhouse. Performance was superior, client libraries and built-in HTTP interface was a god-send, it supported geospatial queries. I had perpetual issues with getting Snowflake to properly escape strings in CSV, handle JSON in anything approaching a sensible way, there’s claims that it integrates properly with Kafka as a consumer, but it most certainly does not. The UX is horrible to boot.
  - manigandham 7y ago
    What were the issues with JSON? Snowflake is the best cloud data warehouse when it comes to support for unstructured data and far better than Redshift, Bigquery or the others. Snowflake also has geo support. It doesn't support streaming data though so things like Kafka aren't a good fit yet. They have a connector but it's basically a little app that automates consuming from Kafka, writing files to S3, then loading them into your tables.
    - FridgeSeal 7y ago
      Getting it to export JSON was needlessly complicated, those Variant type columns are the worst. Does it have geospatial support now? Because they definitely didn’t have it when I used it. Ultimately though, I don’t see enough benefits of SF over CH: feature set overlaps but with no real winner features. observed performance was worse, the client libraries are few and far between and not great, and no HTTP interface, and no real streaming support. On top of being hideously expensive, and closed source.
      - manigandham 7y ago
        Yes it has geospatial support. Variant columns are better than every other database so far. Redshift and Bigquery just have a text field and require far more verbose commands to operate and cast. It sounds like most of your issues are with importing and exporting data rather than querying it? Snowflake is basically EC2 servers reading files from S3 so you get more bandwidth with a larger warehouse size but it's fundamental limit and will have much higher latency compared to running on a local SSD with clickhouse. Lack of streaming is a known problem. They actually do have an HTTP interface, you just don't see it but that's how all the ODBC drivers are implemented (with HTTP calls and JSON data in the background). If your data fits or you don't mind the operational overhead of running your own data warehouse then it's almost always a cheaper and faster option.
ofek 7y ago
Hey, Ofek from Datadog here! I recently implemented our ClickHouse integration [1], so if any of you would like to try it out we would appreciate feedback. I really enjoyed learning about this database, and it has excellent docs :) Oh fun fact, speaking of docs, this was the first integration of ours that we scrape docs for as part of the test suite. So when a new built-in metric is added it will fail our CI until we support it [2]. We just did this again for Apache Airflow [3]. [1]: https://github.com/DataDog/integrations-core/pull/4957 https://github.com/DataDog/integrations-core/pull/4957 [2]: https://github.com/DataDog/integrations-core/pull/5233 https://github.com/DataDog/integrations-core/pull/5233 [3]: https://github.com/DataDog/integrations-core/pull/5311 https://github.com/DataDog/integrations-core/pull/5311
- tgtweak 7y ago
  So at datadog, after aggregation with spark and storage into parquet, what is used for serving queries of all the datadog aggregated telemetry data (logs, apm and infra telemetry) to the consumers? (interestingly, we have a nearly identical data ingestion/ETL stack running on spot instances and saving to parquet/s3)
  - ofek 7y ago
    Hi there! We actually talked quite a bit about that in a recent podcast https://www.dataengineeringpodcast.com/datadog-timeseries-data-episode-113/ https://www.dataengineeringpodcast.com/datadog-timeseries-da...
johnchristopher 7y ago
Note: not your standard $250 NUC, it's a canyon something with much more oomphs.
- miffy900 7y ago
  It does have more oomph than most NUC's, but it's not a Canyon-something NUC; as stated in the article, the CPU model is Intel i7-6770HQ, which was released in Q1 2016 on Intel's 14nm node, so it's Skylake. https://ark.intel.com/content/www/us/en/ark/products/93341/intel-core-i7-6770hq-processor-6m-cache-up-to-3-50-ghz.html https://ark.intel.com/content/www/us/en/ark/products/93341/i...
  - johnchristopher 7y ago
    No, it is a Skull Canyon NUC from 2016 (with an i7-6770HQ), see https://www.techradar.com/reviews/pc-mac/pc-mac-desktops/intel-skull-canyon-nuc-1322597/review/2 https://www.techradar.com/reviews/pc-mac/pc-mac-desktops/int... for a description (I think this https://www.intel.com/content/www/us/en/products/boards-kits/nuc/kits/nuc6i7kyk.html https://www.intel.com/content/www/us/en/products/boards-kits... is the Intel product page). Intel is using the Canyon word for their range of powerful NUCs (Skull Canyon, Hades Canyon, etc. and the upcoming Ghost Canyon).
atombender 7y ago
Is ClickHouse good for event data when you want to do rollups? For example, say all my events are of the form: {event: "viewedArticle", article_id: 63534, user_id: 42, topic: "news", time: "2020-01-06"} I want to be able to build aggregations which shows number of "viewedArticle" events grouped by hour, grouped by topic, counting unique user_ids within each bucket. Or let's say I want the top K articles viewed each day, filtered by a topic. That's something that's trivial with Elasticsearch, which has a hierarchical aggregation DSL. Is ClickHouse good at this? Whenever I see time-series databases such as InfluxDB mentioned, they look like they're focused on measurements, not discrete rows. You can attach the event data as "labels", but this isn't efficient when the cardinality of each column is very high (e.g. article IDs or user IDs in the above example).
- manigandham 7y ago
  Yes. Clickhouse is a column-oriented relational database among many others like MemSQL, Vertica, Redshift, BigQuery, Snowflake, Greenplum, etc. They're all focused on analytical queries over very large datasets using SQL. An aggregation with several `group by` statements is no challenge and all of these databases also support approximate counting via HyperLogLog for faster results. Clickhouse has some unique features where each table can have a separate 'engine' including some that automatically apply aggregations. Start with a normal table though since it'll be plenty fast enough for most use cases.
  - atombender 7y ago
    Thanks! Looks like the only downside is that, as it returns rows as results, you end up getting a lot of duplicate column data back and need to "nest" the nested buckets yourself. For example, a result like: topic;time;count news;2020-01-01;44 news;2020-01-02;31 Now you have "news" repeated, and to group this into buckets for rendering summary tables and such (with sub totals at each level), you need to iterate through the flattened results and generate nested structures. This is something Elasticsearch gives you out of the box. Last I looked at Clickhouse, it had master/slave replication only, and if you want shards of data distributed across a cluster it's something you need to manually manage?
rossjudson 7y ago
Curious about what Google does in this area? https://blog.acolyer.org/2019/09/11/procella/ https://blog.acolyer.org/2019/09/11/procella/
rossjudson 7y ago
Push-down operator algebra, taken to an extreme... https://www.yellowbrick.com/ https://www.yellowbrick.com/
avisk 7y ago
At Sematext we replaced our HBase based metrics datastore with Clickhouse. We are happy with the performance gain and flexibility. We also added support for Clickhouse monitoring - https://sematext.com/blog/clickhouse-monitoring-sematext/ https://sematext.com/blog/clickhouse-monitoring-sematext/
xs83 7y ago
I love stuff like this but I am massively put off by any database system that I need to "Load" into. There are so many amazing file formats now that can be queried directly as files - a 17H load time simply isnt feasible for much of this work. Does anyone know what format it stores it internally? Is there a way to simply have it query in-place?
- xs83 7y ago
  https://github.com/ClickHouse/ClickHouse/pull/8430 https://github.com/ClickHouse/ClickHouse/pull/8430 Just to answer my own question - this looks good - I might have to try it out!
- jayleeg 7y ago
  I've worked with MPP DBs, Hadoop, Spark, ElasticSearch, Druid, kdb, DolphinDB and now ClickHouse and performance wise it's all true - in our case ClickHouse was 10-20x faster than Spark and used 4x less memory. I've seen it outperform the fastest commercial timeseries stores by 2x. This will make me unpopular but my conclusion is that the file based data lake, splitting data from compute, is not the right approach in many (not all) cases and that Spark was not really that revolutionary. I would go as far to say that the direction data has taken has been a failure and ClickHouse and such come closer to solving the real problem of 'BigData'. So two things here about 'loading'... 1) ClickHouse table/data files are completely portable (like Parquet) and can be moved from one server to another, copied or cloned etc.. there is even a mechanism to allow remote execution or to pull just the files from a remote server or an S3 store etc.. Just because the CH native file format isn't spoken about in the same circles as Parquet and ORC doesn't mean it can't be treated the same way if thats your thing. The CH native format is far more performant/compressible than Parquet or ORC and the specification is Open Source. Someone could implement a CH native file format serdes for Hive for example. 2) In this instance they were generating the data so no different to running Spark and writing to a Parquet file and running analytics on it later. Spark can't write / generate this amount of data in this amount of time on these resources and write out / compress the data to Parquet or whatever other preferred format. I've tried. ClickHouse isn't perfect and I'm not affiliated with the Altinity guys but I can tell you this is the real deal.
subhajeet2107 7y ago
We use Clickhouse extensive at work and boy is it better than anything i have used in column oriented databases so far, documentation is good, http query interface and features such as builtin url parsing are amazing, we also tested Druid and found Clickhouse to be better than Druid, it is easier to setup and maintain as well
pachicodev 7y ago
We have been using ClickHouse in production for some months already and we find it a real game changer. We're running queries over 3B rows for business intelligence purposes. As a side project, a group of friends and I are working on a simple web analytics project powered by ClickHouse (what it's been originally build for). If anyone wants to contribute, just let me know. Cheers
thekozmo 7y ago
ClickHouse achieved good (great) results. However, it's a bad comparison. Clickhouse is an analytics DB while Scylla is a realtime, random access one. /me am Scylla co-founder. We could stack 100k rows in a single partition and be 1000x faster in this use case than the performance we demonstrate but we wanted to keep it real. Actually the use case we wanted to show is a single row per partition which would require more machines but surprisingly we couldn't provision that many on AWS. The presented usecase by ClickHouse is 100x slower on writes (8M row/s) as they report. It doesn't matter since it's just a completely different use case. Use Clickhouse for analytics (I wonder why stop in SSE and not to go all the way to the GPU like SqreamDB) and use Scylla for OLTP
- jayleeg 7y ago
  The ScyllaDB one is a bit funny anyway as it doesn't really target analytical workloads. On SSE/GPUs - the ClickHouse guys don't use GPUs today (GPUs are on the roadmap for next year) as their workloads target volumes greater than GPU memory. If your hot dataset sits totally in GPU memory then it makes sense for some things otherwise they found the cost/performance ratio doesn't add up after you paginate in/out. I don't doubt GPU based DB perf numbers but cost is the main factor. Now just to clarify - you're saying Scylla writes are 100x faster on the same hardware as ClickHouse (so 800M row/s on a NUC). Using the same code that Altinity used I manage around 25M rows/s on my home PC (8 cores/16HT) and elsewhere in this thread the guys from VictoriaMetrics pulled in 53M rows/s on a single node with 28 cores/56 threads (probably doable with ClickHouse on similar hardware I'd suspect). I'm going to test this with Scylla on my home PC to validate your 800M row/s claim and I'll post about it - I should be able to hit around 2.5 billion rows/s with Scylla if what you've said is true. I've had CH write 300M row/s on my 8 core box using memory buffered tables but that was only at burst.
valyala 7y ago
VictoriaMetrics core developer here. The performance numbers from ClickHouse running on Intel NUC are impressive! We are going to publish VictoriaMetrics performance numbers for the original Billy benchmark from ScyllaDB [1] running on the same hardware from packet.com . Initial results are quite promising [2], [3]. [1] https://www.scylladb.com/2019/12/12/how-scylla-scaled-to-one-billion-rows-a-second/ https://www.scylladb.com/2019/12/12/how-scylla-scaled-to-one... [2] https://mobile.twitter.com/MetricsVictoria/status/1209116702606278657 https://mobile.twitter.com/MetricsVictoria/status/1209116702... [3] https://mobile.twitter.com/MetricsVictoria/status/1209186575558565889 https://mobile.twitter.com/MetricsVictoria/status/1209186575...

216 points by hodgesrm 7y ago

We use ClickHouse extensively and it's been great: https://blog.cloudflare.com/http-analytics-for-6m-requests-per-second-using-clickhouse/ https://blog.cloudflare.com/http-analytics-for-6m-requests-p...

chupasaurus 7y ago
You've lost a Russian IT meme in translation. We use the verb "to use brakes" to describe that something works slowly. There was a long story of threads about Java performance, until the meme was solidified after the news from 2005 DARPA Grand Challenge (racing competition for autonomous robotic self-driven cars): a car named Tommy by Jefferson Team which was running Java under Linux haven't used breaks before a turn and crashed to the wall at 70 mph, hence Java runs fast and Java doesn't use brakes were both described perfectly by the same sentence. Yandex used the meme to advertise Clickhouse for engineers. edit: formatting edit2: brakes instead of breaks, wtf
- deleted 7y ago
  [deleted]
- nkrisc 7y ago
  I think you mean "brakes," as I thought you meant "breaks" like in an iterator. Not that familiar with Java though, just assuming based on the reference to a vehicle failing to brake before a turn. I thought maybe it meant using break statements in your loops slows things down or something.
  - chupasaurus 7y ago
    Yeah, my bad.
- PeterZaitsev 7y ago
  I think this is poor translation. In Russian "Тормозить" may mean to "use brakes" when applied to a car or just "be slow" when applied to a program (or a person). "MySQL сегодня тормозит" would mean MySQL is acting slow today, not what it is using brakes. So that meme I think is best translated as "Clickhouse is never slow" or "Clickhouse never acts slow"

kresten 7y ago

What’s the tldr on why it is fast?

eecc 7y ago
Precomputed materialized views
- brobinson 7y ago
  Yeah, this post could have been titled "Why Materialized Views are Awesome"
  - bdcravens 7y ago
    Clickhouse is also crazy fast without materialized views - I've only done some PoC's against it, but in loading a largish data set of raw invoice CSVs, I was very impressed with the performance compared to our standard RDBMS.
    - tumanian 7y ago
      that sounds like a non-canonical use of clickhouse. Wouldnt a good RDBMS be a better fit for invoice data? This is on the surface, of course, really interested in what is this invoice data like, and what queries are you trying to run on them.
polskibus 7y ago
Heavily optimized column store, incl use of SSE instructions, etc. Moreover, some architectural tradeoffs mentioned at https://clickhouse.yandex/docs/en/introduction/features_considered_disadvantages/ https://clickhouse.yandex/docs/en/introduction/features_cons...
SloopJon 7y ago
I'd say the materialized view is the main thing: > Thus, we add the following materialized view ... At the end we should have 1440 times fewer rows in the aggregate than the source table. The cost of populating that view is amortized over the 17.5 hours it took to load the data.

deleted 7y ago

[deleted]

patelh 7y ago

Not exactly a good comparison if you don't generate the data the same way for the test setup. Your generated data is more compressible by clickhouse, that skews the comparison. Would have been better to not change the test data if you wanted to do a comparison.

sheeshkebab 7y ago
I bet results would be roughly the same even for the exact same dataset - Scylla and other K/v data stores can’t compete with columnar databases that are purpose built for complex analytics queries. the many orders of magnitude query performance differences (not to count storage, compute overhead) show it enough. It was kind of a crummy use case for Scylla anyway (it’s a transactional write store, not an analytics engine)
hodgesrm 7y ago
The important difference is that we used a more realistic temperature profile, which as you say does affect compression for that column. Schema design (including sort order, compression, and codecs) for the remaining columns is just good ClickHouse practice. Much of the storage and I/O savings is in the date, time, and sensor_id and columns. It's also useful to note that the materialized view results would be essentially the same no matter how you generate and store data because the materialized view down-samples temperature max/min to daily aggregates. The data are vastly smaller no matter how you generate them. The article illustrates that if you really had such an IoT app and designed it properly you could run analytics with surprisingly few resources. I think that's a significant point.
- patelh 7y ago
  Then you should provide results for both test datasets to make the point of using a more realistic approach. Materialized views are not news, nor is properly designed analytics applications. For me the importance is how click house is better and why.
  - manigandham 7y ago

codexon 7y ago

One thing I haven't seen anyone note about clickhouse though which would be really important to many for data durability, is that it does not use fsync anywhere at all.

caust1c 7y ago
It's pretty clearly laid out in the docs. Hopefully anyone seriously considering using Clickhouse reads the docs thoroughly and understands what they're implementing.
- codexon 7y ago
  What do you mean clearly laid out? This is the only mention of fsync I could find through google or their own search function. https://clickhouse.yandex/docs/en/operations/settings/settings/#fsync-metadata https://clickhouse.yandex/docs/en/operations/settings/settin...
  - caust1c 7y ago
    The title of the page might be a little snarky, but it's in the introduction that transactional queries are not supported: https://clickhouse.yandex/docs/en/introduction/features_considered_disadvantages/ https://clickhouse.yandex/docs/en/introduction/features_cons... Sure it's not specifically about `fsync` but presumably this is what the consumer of the database actually wants to know.
    - codexon 7y ago
      Fsync is not synonymous with transactions. Not using fsync anywhere means there's a wide window that can be over 10 minutes long when data can be lost when a server gets an unplanned shutdown.
      - valyala 7y ago
        Btw, it should be easy adding fsync to ClickHouse. For instance VictoriaMetrics uses similar file format as ClickHouse, and it issues proper fsyncs at least every second, so it may lose only the last second of data on unclean shutdown such as OOM, hardware reset or `kill -9`. [1], [2]. [1] https://medium.com/@valyala/wal-usage-looks-broken-in-modern-time-series-databases-b62a627ab704 https://medium.com/@valyala/wal-usage-looks-broken-in-modern... [2] https://medium.com/@valyala/how-victoriametrics-makes-instant-snapshots-for-multi-terabyte-time-series-data-e1f3fb0e0282 https://medium.com/@valyala/how-victoriametrics-makes-instan...

tkyjonathan 7y ago

This is going to be your next cloud agnostic data warehouse.

FridgeSeal 7y ago
For what it’s worth, I’ve used Clickhouse and Snowflake and I strongly prefer Clickhouse. Performance was superior, client libraries and built-in HTTP interface was a god-send, it supported geospatial queries. I had perpetual issues with getting Snowflake to properly escape strings in CSV, handle JSON in anything approaching a sensible way, there’s claims that it integrates properly with Kafka as a consumer, but it most certainly does not. The UX is horrible to boot.
- manigandham 7y ago
  What were the issues with JSON? Snowflake is the best cloud data warehouse when it comes to support for unstructured data and far better than Redshift, Bigquery or the others. Snowflake also has geo support. It doesn't support streaming data though so things like Kafka aren't a good fit yet. They have a connector but it's basically a little app that automates consuming from Kafka, writing files to S3, then loading them into your tables.
  - FridgeSeal 7y ago
    Getting it to export JSON was needlessly complicated, those Variant type columns are the worst. Does it have geospatial support now? Because they definitely didn’t have it when I used it. Ultimately though, I don’t see enough benefits of SF over CH: feature set overlaps but with no real winner features. observed performance was worse, the client libraries are few and far between and not great, and no HTTP interface, and no real streaming support. On top of being hideously expensive, and closed source.
    - manigandham 7y ago
      Yes it has geospatial support. Variant columns are better than every other database so far. Redshift and Bigquery just have a text field and require far more verbose commands to operate and cast. It sounds like most of your issues are with importing and exporting data rather than querying it? Snowflake is basically EC2 servers reading files from S3 so you get more bandwidth with a larger warehouse size but it's fundamental limit and will have much higher latency compared to running on a local SSD with clickhouse. Lack of streaming is a known problem. They actually do have an HTTP interface, you just don't see it but that's how all the ODBC drivers are implemented (with HTTP calls and JSON data in the background). If your data fits or you don't mind the operational overhead of running your own data warehouse then it's almost always a cheaper and faster option.

ofek 7y ago

Hey, Ofek from Datadog here! I recently implemented our ClickHouse integration [1], so if any of you would like to try it out we would appreciate feedback. I really enjoyed learning about this database, and it has excellent docs :) Oh fun fact, speaking of docs, this was the first integration of ours that we scrape docs for as part of the test suite. So when a new built-in metric is added it will fail our CI until we support it [2]. We just did this again for Apache Airflow [3]. [1]: https://github.com/DataDog/integrations-core/pull/4957 https://github.com/DataDog/integrations-core/pull/4957 [2]: https://github.com/DataDog/integrations-core/pull/5233 https://github.com/DataDog/integrations-core/pull/5233 [3]: https://github.com/DataDog/integrations-core/pull/5311 https://github.com/DataDog/integrations-core/pull/5311

tgtweak 7y ago
So at datadog, after aggregation with spark and storage into parquet, what is used for serving queries of all the datadog aggregated telemetry data (logs, apm and infra telemetry) to the consumers? (interestingly, we have a nearly identical data ingestion/ETL stack running on spot instances and saving to parquet/s3)
- ofek 7y ago
  Hi there! We actually talked quite a bit about that in a recent podcast https://www.dataengineeringpodcast.com/datadog-timeseries-data-episode-113/ https://www.dataengineeringpodcast.com/datadog-timeseries-da...

johnchristopher 7y ago

Note: not your standard $250 NUC, it's a canyon something with much more oomphs.

miffy900 7y ago
It does have more oomph than most NUC's, but it's not a Canyon-something NUC; as stated in the article, the CPU model is Intel i7-6770HQ, which was released in Q1 2016 on Intel's 14nm node, so it's Skylake. https://ark.intel.com/content/www/us/en/ark/products/93341/intel-core-i7-6770hq-processor-6m-cache-up-to-3-50-ghz.html https://ark.intel.com/content/www/us/en/ark/products/93341/i...
- johnchristopher 7y ago
  No, it is a Skull Canyon NUC from 2016 (with an i7-6770HQ), see https://www.techradar.com/reviews/pc-mac/pc-mac-desktops/intel-skull-canyon-nuc-1322597/review/2 https://www.techradar.com/reviews/pc-mac/pc-mac-desktops/int... for a description (I think this https://www.intel.com/content/www/us/en/products/boards-kits/nuc/kits/nuc6i7kyk.html https://www.intel.com/content/www/us/en/products/boards-kits... is the Intel product page). Intel is using the Canyon word for their range of powerful NUCs (Skull Canyon, Hades Canyon, etc. and the upcoming Ghost Canyon).

atombender 7y ago

Is ClickHouse good for event data when you want to do rollups? For example, say all my events are of the form: {event: "viewedArticle", article_id: 63534, user_id: 42, topic: "news", time: "2020-01-06"} I want to be able to build aggregations which shows number of "viewedArticle" events grouped by hour, grouped by topic, counting unique user_ids within each bucket. Or let's say I want the top K articles viewed each day, filtered by a topic. That's something that's trivial with Elasticsearch, which has a hierarchical aggregation DSL. Is ClickHouse good at this? Whenever I see time-series databases such as InfluxDB mentioned, they look like they're focused on measurements, not discrete rows. You can attach the event data as "labels", but this isn't efficient when the cardinality of each column is very high (e.g. article IDs or user IDs in the above example).

manigandham 7y ago
Yes. Clickhouse is a column-oriented relational database among many others like MemSQL, Vertica, Redshift, BigQuery, Snowflake, Greenplum, etc. They're all focused on analytical queries over very large datasets using SQL. An aggregation with several `group by` statements is no challenge and all of these databases also support approximate counting via HyperLogLog for faster results. Clickhouse has some unique features where each table can have a separate 'engine' including some that automatically apply aggregations. Start with a normal table though since it'll be plenty fast enough for most use cases.
- atombender 7y ago
  Thanks! Looks like the only downside is that, as it returns rows as results, you end up getting a lot of duplicate column data back and need to "nest" the nested buckets yourself. For example, a result like: topic;time;count news;2020-01-01;44 news;2020-01-02;31 Now you have "news" repeated, and to group this into buckets for rendering summary tables and such (with sub totals at each level), you need to iterate through the flattened results and generate nested structures. This is something Elasticsearch gives you out of the box. Last I looked at Clickhouse, it had master/slave replication only, and if you want shards of data distributed across a cluster it's something you need to manually manage?

rossjudson 7y ago

Curious about what Google does in this area? https://blog.acolyer.org/2019/09/11/procella/ https://blog.acolyer.org/2019/09/11/procella/

rossjudson 7y ago

Push-down operator algebra, taken to an extreme... https://www.yellowbrick.com/ https://www.yellowbrick.com/

avisk 7y ago

At Sematext we replaced our HBase based metrics datastore with Clickhouse. We are happy with the performance gain and flexibility. We also added support for Clickhouse monitoring - https://sematext.com/blog/clickhouse-monitoring-sematext/ https://sematext.com/blog/clickhouse-monitoring-sematext/

xs83 7y ago

I love stuff like this but I am massively put off by any database system that I need to "Load" into. There are so many amazing file formats now that can be queried directly as files - a 17H load time simply isnt feasible for much of this work. Does anyone know what format it stores it internally? Is there a way to simply have it query in-place?

xs83 7y ago
https://github.com/ClickHouse/ClickHouse/pull/8430 https://github.com/ClickHouse/ClickHouse/pull/8430 Just to answer my own question - this looks good - I might have to try it out!
jayleeg 7y ago
I've worked with MPP DBs, Hadoop, Spark, ElasticSearch, Druid, kdb, DolphinDB and now ClickHouse and performance wise it's all true - in our case ClickHouse was 10-20x faster than Spark and used 4x less memory. I've seen it outperform the fastest commercial timeseries stores by 2x. This will make me unpopular but my conclusion is that the file based data lake, splitting data from compute, is not the right approach in many (not all) cases and that Spark was not really that revolutionary. I would go as far to say that the direction data has taken has been a failure and ClickHouse and such come closer to solving the real problem of 'BigData'. So two things here about 'loading'... 1) ClickHouse table/data files are completely portable (like Parquet) and can be moved from one server to another, copied or cloned etc.. there is even a mechanism to allow remote execution or to pull just the files from a remote server or an S3 store etc.. Just because the CH native file format isn't spoken about in the same circles as Parquet and ORC doesn't mean it can't be treated the same way if thats your thing. The CH native format is far more performant/compressible than Parquet or ORC and the specification is Open Source. Someone could implement a CH native file format serdes for Hive for example. 2) In this instance they were generating the data so no different to running Spark and writing to a Parquet file and running analytics on it later. Spark can't write / generate this amount of data in this amount of time on these resources and write out / compress the data to Parquet or whatever other preferred format. I've tried. ClickHouse isn't perfect and I'm not affiliated with the Altinity guys but I can tell you this is the real deal.

subhajeet2107 7y ago

We use Clickhouse extensive at work and boy is it better than anything i have used in column oriented databases so far, documentation is good, http query interface and features such as builtin url parsing are amazing, we also tested Druid and found Clickhouse to be better than Druid, it is easier to setup and maintain as well

pachicodev 7y ago

We have been using ClickHouse in production for some months already and we find it a real game changer. We're running queries over 3B rows for business intelligence purposes. As a side project, a group of friends and I are working on a simple web analytics project powered by ClickHouse (what it's been originally build for). If anyone wants to contribute, just let me know. Cheers

thekozmo 7y ago

ClickHouse achieved good (great) results. However, it's a bad comparison. Clickhouse is an analytics DB while Scylla is a realtime, random access one. /me am Scylla co-founder. We could stack 100k rows in a single partition and be 1000x faster in this use case than the performance we demonstrate but we wanted to keep it real. Actually the use case we wanted to show is a single row per partition which would require more machines but surprisingly we couldn't provision that many on AWS. The presented usecase by ClickHouse is 100x slower on writes (8M row/s) as they report. It doesn't matter since it's just a completely different use case. Use Clickhouse for analytics (I wonder why stop in SSE and not to go all the way to the GPU like SqreamDB) and use Scylla for OLTP

jayleeg 7y ago
The ScyllaDB one is a bit funny anyway as it doesn't really target analytical workloads. On SSE/GPUs - the ClickHouse guys don't use GPUs today (GPUs are on the roadmap for next year) as their workloads target volumes greater than GPU memory. If your hot dataset sits totally in GPU memory then it makes sense for some things otherwise they found the cost/performance ratio doesn't add up after you paginate in/out. I don't doubt GPU based DB perf numbers but cost is the main factor. Now just to clarify - you're saying Scylla writes are 100x faster on the same hardware as ClickHouse (so 800M row/s on a NUC). Using the same code that Altinity used I manage around 25M rows/s on my home PC (8 cores/16HT) and elsewhere in this thread the guys from VictoriaMetrics pulled in 53M rows/s on a single node with 28 cores/56 threads (probably doable with ClickHouse on similar hardware I'd suspect). I'm going to test this with Scylla on my home PC to validate your 800M row/s claim and I'll post about it - I should be able to hit around 2.5 billion rows/s with Scylla if what you've said is true. I've had CH write 300M row/s on my 8 core box using memory buffered tables but that was only at burst.

valyala 7y ago

VictoriaMetrics core developer here. The performance numbers from ClickHouse running on Intel NUC are impressive! We are going to publish VictoriaMetrics performance numbers for the original Billy benchmark from ScyllaDB [1] running on the same hardware from packet.com . Initial results are quite promising [2], [3]. [1] https://www.scylladb.com/2019/12/12/how-scylla-scaled-to-one-billion-rows-a-second/ https://www.scylladb.com/2019/12/12/how-scylla-scaled-to-one... [2] https://mobile.twitter.com/MetricsVictoria/status/1209116702606278657 https://mobile.twitter.com/MetricsVictoria/status/1209116702... [3] https://mobile.twitter.com/MetricsVictoria/status/1209186575558565889 https://mobile.twitter.com/MetricsVictoria/status/1209186575...