Clickhouse Local

https://clickhouse.tech/docs/en/operations/utils/clickhouse-local/

by mooreds • 6 years ago

124 75 6 years ago

clickhouse.tech

19963• 6 years ago

Clickhouse is one of the most underrated databases.

This basically replaces most of my usages of SQLite.

When its SQL "dialect" matures, Clickhouse will eat MySQL lunch, then PostgreSQL.

oreoftw2• 6 years ago

For analytics - sure. Clickhouse was not designed to handle OLTP workload, there's no transaction support.

19963• 6 years ago

I cringe a bit inside at people using say nosql approaches when it makes literally no sense to do so.

Therefore I think the lack of OLTP will not matter much and that clickhouse will be widely used, but also misused when it becomes too fashionable.

deepsun7• 6 years ago

No, Clickhouse doesn't support UPDATEs, and schema changes are even harder than in classic SQL DBs. But we love it anyway :)

polskibus• 6 years ago

I wonder how performant this is in comparison to other quick and dirty methods to work on local files like bash, pythonz etc.

hodgesrm3• 6 years ago

It's much faster in cases that involve significant processing. Where possible query execution is vectorized and runs in parallel on as many cores as you make available.

There are some examples in the article cited by dang: https://news.ycombinator.com/item?id=20163017

dang• 6 years ago

A related article from last year: https://news.ycombinator.com/item?id=20163017. No comments though.

georgewfraser3• 6 years ago

Why do people on HN love Clickhouse so much? As far as I can tell, it’s an ordinary column store, with a bunch of limitations around distributed joins and a heuristic-based query planner. There are several good analytical databases that will give you the same scan performance and a much better query planner and executor.

This is not a rhetorical question, I would really like to know why it gets so much attention here.

oreoftw2• 6 years ago

Could you name those good analytical databases? I'd love to learn more.

georgewfraser3• 6 years ago

Snowflake, Redshift, BigQuery, Databricks, Presto.

phillc732• 6 years ago

DuckDB perhaps[1]: https://www.duckdb.org

[1] I say "perhaps" because I've only just started using it having migrated from MonetDB, but have no experience of alternatives like Presto.

wochiquan3• 6 years ago

Apache Druid: https://druid.apache.org/docs/latest/design/index.html

atombender5• 6 years ago

Because it's one of the few mature, fast, scalable analytics-oriented databases that is also completely open source.

The closest open source thing that matches its feature set is Presto, but that one is quite different.

Apache Druid is supposed to be very mature, but also very difficult to set up and manage. I've not used it myself.

There's also Vespa, but I don't know how well it performs with large numbers of columns.

A lot of people use Elasticsearch for analytics. Being based on Lucene, it's kind of columnar, and it can perform very well indeed on aggregations.

InfluxDB may be good, but it's not fully open source.

lflux• 6 years ago

What about Greenplum or CitusDB?

wochiquan3• 6 years ago

I know a few companies that use Imply Cloud to set up and manage Druid. I linked it if you want to check it out. https://imply.io/product/imply-cloud

peferron• 6 years ago

Apache Druid is supposed to be very mature, but also very difficult to set up and manage

I wouldn't be surprised if this inverted at large scale (say 30+ machines). Druid data servers are rebalanced automatically; if you're on AWS and decide to scale up by adding a new data server, it will automatically load its assigned subset of data from S3. If AWS kills one of your data servers, then other data servers will automatically load from S3 some of the data that server used to carry, in order to reach the desired replication factor again.

Last time I checked ClickHouse had no automatic rebalancing at all, which sounds horrendous unless you're running at very small scale or willing to have people babysit it. I haven't operated ClickHouse at large scale though, so if I'm wrong I'd be happy to hear how people manage ordinary tasks like scaling up and down, replacing dead instances, changing instance types to adjust CPU/mem/disk, etc... with let's say a 100 TB compressed dataset.

Another difference is that Druid can index all dimensions. So if you plan to run queries with filters that only match a small fraction of rows, then Druid can be faster than ClickHouse. Conversely, if your queries have filters that match many rows, then ClickHouse will be faster because it has higher raw scan speed. (At least that was the case about a year ago. Since then, Druid has added vectorized aggregation, which I haven't benchmarked, but I'd bet that ClickHouse is still faster at doing full table scans.)

IMO these 2 things are the main elements to think about when choosing between ClickHouse and Druid.

ddorian43• 6 years ago

Vespa is inverted-index (many extras inside). Actually their aggregations isn't as optimized as elasticsearch (depends on scenario). To do aggs, the fields must reside in-memory.

RhodesianHunter2• 6 years ago

Performance at scale for certain workloads.

amyjess• 6 years ago

It's legitimately fast.

My company migrated our time-series data from InfluxDB to ClickHouse last year (I personally led this, in fact), and the performance difference is night and day.

While I liked a lot of what Influx could do, it was also nonstandard in bizarre ways (Clickhouse behaves more like a subset of SQL), sometimes shockingly immature, and despite appearing fast when we first started using it, so slow that it was a considerable bottleneck.

deepsun7• 6 years ago

Clickhouse is not really time-series database, it's more general analytical DB (e.g. also can handle strings, logs, user IPs).

But if you have a lot of time-series metrics (only numbers), you might be better with specialized time-series databases like Prometheus + VictoriaMetrics with Grafana for visualizing it.

jstrong2• 6 years ago

I think of influx performance as similar to pandas. It's fast enough for many things it was designed for, but it's pretty easy to hit a performance cliff. In particular, "select * from table" type queries are very slow, so don't expect to be pulling out large blocks of data to do analysis somewhere else.

However, influx, being schemaless, can be outstanding for rapidly prototyping ephemeral metrics, as adding a new measurement is zero-cost (just start writing it). It also plays great with grafana for building dashboards. Finally, I much prefer the ergonomics enhancements of the influx query language (v1, the v2 "flux" language looks terrible to me personally), particularly how duration strings are a first-class datatype ("group by time(1h)").

Interested to hear more about clickhouse performance, haven't had a chance to use it for anything where performance would matter significantly, although am aware it can be very fast.

dunkelheit2• 6 years ago

There are several good analytical databases that will give you the same scan performance

The notion that you will get approximately the same query performance with all column stores is false. There can easily be an order of magnitude difference depending on the implementation. Take GROUP BY as a paradigmatic example of what OLAP stores do. Of course the way to implement GROUP BY is with a hash table but little tricks make all the difference and a lot of love went into the clickhouse implementation. Just to give you a taste: there is a custom hash table with specializations for different key types (e.g. it will store a precomputed hash for strings but not for integers and use it to speed up equality test). Variable length data is stored in arenas to reduce allocator pressure. Data will of course be aggregated in several hash tables in different threads and then merged together, but if there is a lot of keys each table will additionally be sharded so that the merge step can be performed in parallel too.

Of course you shouldn't trust random claims on the internet that clickhouse is fast and should do a small case study yourself. Then you'll appreciate how easy is to setup a clickhouse instance or a small cluster. It can easily slurp up most common formats. It is just a single binary with minimal dependencies that will run as-is on any modern linux. There is just a single node type (compare this to druid madness).

You are right that there is a lot of limitations and, how should I put it, quirks. This is resoundingly not a general-purpose database and someone used to the comforts of e.g. postgres will encounter some nasty surprises. Bugs are unfortunately common, especially in the newer functionality. But performance is its main feature and it makes many users of clickhouse put up with its limitations.

jfim2• 6 years ago

Of course the way to implement GROUP BY is with a hash table but little tricks make all the difference and a lot of love went into the clickhouse implementation.

Actually, for low cardinality columns, _not_ using a hash table will speed up things.

For example, a dictionary-encoded column for states might have a few values in its dictionary (1 => CA, 2 => FL, 3 => NY) and the data looks like an array of numbers (eg. [1, 1, 1, 2, 1, 3, 3, 3, 1, 3, 2]). The fastest way to aggregate is to actually use an array, as the dictionary index conveniently maps to an array index.

Then, when it comes to merging several of those arrays, they're turned into hash tables.

Combine enough of those optimizations and proper data layouts, and you end up with several orders of magnitude of performance differences between engines.

hodgesrm3• 6 years ago

In addition to excellent raw scan rates for reasons given above ClickHouse has materialized views, which can re-order and/or preaggregate data. This can speed up query response by 3 orders of magnitude over queries on the source data. See https://www.altinity.com/blog/clickhouse-materialized-views-... + ClickHouse docs for an intro.

As the parent says, try it yourself on your own data. That's all that counts.

Disclaimer: I wrote the blog article and we sell support for ClickHouse.

georgewfraser3• 6 years ago

All of the optimizations you describe are in every columnar analytical database built in the last 10 years, from Vertica to Snowflake.

bsg75• 6 years ago

Its somewhat unique in the open source category.

As a column store engine that supports (hybrid) SQL, manages its own storage and clustering, and has only one external dependency (Zookeeper), I believe its main competitor is Vertica which can be very expensive. I assume Oracle, IBM, and MS have column stores as well and for a cost.

Greenplum is a Postgres fork, and same scale requires much more hardware. Citus is row based, and therefore will lag in scan time for many OLAP query patterns. Presto, Hive, Spark, all of the "post-hadoop" options may scale larger, but will also lag in scan time, and have significant external dependencies - mainly storage.

Clickhouse is easy to install, configure a cluster, load and query. It does have limitations, but currently all horizontally scaled database platforms do.

quantumwoke• 6 years ago

It's the first time I've seen it on HN, and a cursory search shows only a few submissions with significant discussion. Nonetheless, as other commenters have said the SQL-based query language and low external dependencies made it easy for me to integrate it into my workflow.

golover721• 6 years ago

Clickhouse is extremely optimized for performing analytics on time series style data. Things like clickstream data (hence the name). Performing queries to do funnel level analysis for example, are extremely fast compared to other analytics databases I have tried. As others have mentioned it is often compared to Apache Druid in its intent and feature set.

barrkel2• 6 years ago

Low latency query execution and it scales down well to small datasets, are my reasons for really liking it. Top throughout when scanning TB+ of data on multiple nodes isn't my use case; it's answering a query in web request response times over a few hundred million rows of data.

The low latency to query execution is really nice.

tadkar• 6 years ago

To summarise a lot of the responses here, Clickhouse is extremely fast on very modest hardware, very easy to set up, very easy to get started (mostly normal SQL) and free. For our workloads and scale of data, nothing comes close in terms of performance (redshift, BQ, spark) and especially TCO. It is super simple to try out, why not give it a go? Or is the question borne from a previous bad experience?

econcon• 6 years ago

Its main use in analytics and real time dashboard/report generation in areas like adtech.

If a conversion happens, you need to generate the views/cubes again in reporting dashboard, clickhouse makes it cheap and easy to run such operations over commodity hardware.

If you don't have this, you'll be using big query and it might not be as fast.

pyppo• 6 years ago

Perspective from sentry.io (we use it in production as wikibob pointed out below): Besides all the performance considerations discussed in this thread, which are 100% correct, a few other features we really like are:

- the multiple table engines that are heavily optimized for specific data access patterns: ReplacingMergeTrees which essentially make records mutable, SummingMergeTrees that allow us to progressively build pre-aggregated data, AggregateMergeTrees that allow storing the intermediate aggregation state of most aggregation function and compose them at query time over multiple groups (example, store a p95 aggregation state hourly and query the daily p95 by composing them), and more.

- column data types is extensive and includes nested columns

- the architecture is relatively simple making it easy for developers and on prem users to deploy a single node local clickhouse very easily

- it is very efficient in inserting big batches of data which works really well for our use case were we ingest massive amount of errors.

- data skipping indexes, bloom filter indexes

(yes, as vlad@sentry.io mentioned below we are hiring for the team that manages storage and thus clickhouse)

wikibob• 6 years ago

How Sentry.io uses Clickhouse

https://blog.sentry.io/2019/05/16/introducing-snuba-sentrys-...

vladcretu• 6 years ago

We do. We are also hiring. Reach out to vlad@sentry.io for more info.

dzonga2• 6 years ago

funny thing, just learnt about clickhouse today. for experienced people that use columnar stores and pandas for analytics, which tool do you usually prefer for BI stuff ? do ya'll load data into clickhouse then analyse it using pandas. or all analysis is done via the clickhouse sql dialect. As i'm sure things like pivot tables and rolling windows are a PITA in SQL

meritt3• 6 years ago

Why would you bother using a database like clickhouse to store data if you're just going to analyze it in pandas? Just store it in a csv, parquet, or orc.

As i'm sure things like pivot tables and rolling windows are a PITA in SQL

I can't speak for clickhouse, but group-by and window functions are a very standard part of any SQL analysts toolbelt.

FridgeSeal3• 6 years ago

Why would you bother using a database like clickhouse to store data if you're just going to analyze it in pandas?

Because I have more data than what fits locally, there’s a data pipeline that pushes more in, and I only need to work on a subset.

Storing everything in flat csv/parquet etc is useless when there’s more than fits on your local/single machine memory or if you want to search/subset etc some of the data or do anything that’s larger than memory without having to write spill-to-disk stuff in Python/pandas.

RhodesianHunter2• 6 years ago

Why would you bother with load balancers, clusters, and distributed architectures when you could just deploy apache and PHP on a cheap commodity server?

Shelnutt2• 6 years ago

Another option to consider here is storing your data in TileDB[1] which allows you to access it via Python/Pandas, MariaDB for SQL (embeddable or standalone), Spark, R and more. With embedded MariaDB[2] you can query directly into pandas with minimum overhead. TileDB is similar to parquet in that it can capture sparse dataframe usage, however it is more general in that TileDB supports multi-dimensional datasets and dense arrays. Other major features built directly into TileDB include handling updates, time traveling and partitioning at the library level, removing the need for using extra services like Delta Lake to deal with the numerous Parquet files you may create. TileDB also has native support for S3 and in the next release we'll have native Azure Blob Storage support.

[1] https://github.com/TileDB-Inc/TileDB

[2] https://docs.tiledb.com/developer/api-usage/embedded-sql

Disclosure: I am a member of the TileDB, Inc. team

bdcravens2• 6 years ago

Clickhouse Local is great also for importing into your Clickhouse Server, where you can validate and preprocess CSVs into Clickhouse's native table format.

Clickhouse Local

Related Stories