HN user
Very cool project indeed!
I tried to check the kind of flights they flew in the world's dangerous airport (Lukla, Nepal)[0] and found they use ATR-72 series.
[0] https://adsb.exposed/?dataset=Planes&zoom=12&lat=27.7136&lng...
Debian package is of poor quality
Can you elaborate more please? I would love if you can say what all can be improved to make debian package up to standards.
clickhouse-local is similar to duckdb, you don't need a clickhouse-server running in order to use clickhouse-local. You just need to download the clickhouse binary and start using it.
clickhouse local
ClickHouse local version 25.4.1.1143 (official build).
:)
clickhouse local --query "SELECT count() FROM file('top-1m-2018-01-10.csv.zip :: *.csv')"
1000000
I prefer to use clickhouse-local for all my CSV needs as I don't need to learn a new language (or cli flags) and can just leverage SQL.
clickhouse local --file medias.csv --query "SELECT edito, count() AS count from table group by all order by count FORMAT PrettyCompact"
┌─edito──────┬─count─┐
│ agence │ 1 │
│ agrégateur │ 10 │
│ plateforme │ 14 │
│ individu │ 30 │
│ media │ 423 │
└────────────┴───────┘
glad you are using CH :)
I am looking forward to learn about such upcoming features in the community call https://clickhouse.com/company/events/v25-1-community-releas...
ClickHouse has a solid Iceberg integration. It has an Iceberg table function[0] and Iceberg table engine[1] for interacting with Iceberg data stored in s3, gcs, azure, hadoop etc.
[0] https://clickhouse.com/docs/en/sql-reference/table-functions...
[1] https://clickhouse.com/docs/en/engines/table-engines/integra...
To my knowledge, both Postgres and MySQL has their own strengths and weaknesses. Example: mvcc implementation, data replication, connection pooling and difficulty of upgrades were the major weaknesses of Postgres which are much improved over time. Similarly mysql query optimizer is consider lesser developed than that of Postgres's.
Overall I think Postgres adoption and integrations and thus community is much more wider than MySQL which gives it major advantage over MySQL. Also looking at the number of database-as-a-service companies of Postgres vs those of MySQL we can immediately acknowledges that Postgres is much widely adopted.
If I had to only pick two databases to deal with, I’d be quite happy with just Postgres and ClickHouse - the former for OLTP, the latter for OLAP.
As the author mentioned, I completely agree with this statement. In fact, many companies like Cloudflare are built with exactly this approach and it has scaled them pretty well without the need of any third database.
Another reason I suggest checking out ClickHouse is that it is a joy to operate - deployment, scaling, backups and so on are well documented - even down to setting the right CPU governor is covered.
Another point mentioned by author which is worth highlighting is the ease of deployment. Most distributed databases aren't so easy to run at scale, ClickHouse is much much easier and it has become even more easier with efficient storage-compute separation.
Streaming queries is coming in ClickHouse too: https://github.com/ClickHouse/ClickHouse/pull/63312
There was a great talk[0] recently from Laravel core team member about ClickHouse, Probably you will enjoy watching it.
You should look at ClickHouse which has good PHP client
Since analytics data is generally write-heavy, I would recommend to use ClickHouse. You can use async-insert[0] feature of ClickHouse, thus you don't need to worry about batching events on your side. If you are looking for an embedded solution, you can use chDB which is built on top of ClickHouse.
[0] https://clickhouse.com/blog/asynchronous-data-inserts-in-cli...
You can leverage ClickHouse to process your music data. ClickHouse supports both TSV[0] and XML[1] data formats.
[0] https://clickhouse.com/docs/en/interfaces/formats#tabseparat...
This syntax looks a lot like PRQL. ClickHouse supports writing queries in PRQL dialect. Moreover, ClickHouse also supports Kusto dialect too.
https://clickhouse.com/docs/en/guides/developer/alternative-...
I would recommend using ClickHouse which provides very efficient compression and thus reduces the size of data drastically.
Apart from that, it provides various other feature:
- Dynamic datatype [0] which are very useful for semi-structured fields which generally logs contains very often.
- You can configure column's & table's TTL [1] which provides efficient way to configure retention.
At my previous job (Cloudflare), we migrated from Elasticsearch to ClickHouse and saved nearly 10x reduction in data size and got 5x perf improvement. You can read more about it [2] and watch the recording here [3]
Recently, ClickHouse engineers published a wondering detailed blog about their logging pipeline [4]
[0] https://clickhouse.com/docs/en/sql-reference/data-types/dyna...
[1] https://clickhouse.com/docs/en/engines/table-engines/mergetr...
[2] https://blog.cloudflare.com/log-analytics-using-clickhouse
[3] https://vimeo.com/730379928
[4] https://clickhouse.com/blog/building-a-logging-platform-with...
I would recommend to look at ClickHouse[0] which is Apache-License and is completely open-source. You can self-host it and it is one of the fastest growing OLAP database. It has good integrations with BI tools and it can be used for wide varieties of use cases. It is widely adopted[1]. I would recommend you to give it a try and validate it for your use-case.
I work for ClickHouse and available for any queries or help you need.
[0] https://clickhouse.com/ [1]: https://clickhouse.com/docs/en/about-us/adopters
If you are interested in reading internals of using Parquet with ClickHouse, do read following articles:
- https://clickhouse.com/blog/apache-parquet-clickhouse-local-...
- https://clickhouse.com/blog/apache-parquet-clickhouse-local-...
Reading through this blog, to me it seems Parquet is lot like ClickHouse native data format.
Best part of ClickHouse native data format is I can use the same ClickHouse queries and can run in local or remote server/cluster and let ClickHouse to decide the available resources in the most performant way.
ClickHouse has a native and the fastest integration with Parquet so i can:
- Query local/s3 parquet data from command line using clickhouse-local.
- Query large amount of local/s3 data programmatically by offloading it to clickhouse server/cluster which can do processing in distributed fashion.
Postgres is still way ahead of ClickHouse in terms of operational simplicity
Having served as both ClickHouse and Postgres SRE, I don't agree with this statement.
- Minimal downtime major version upgrades in PostgreSQL is very challenging.
- glibc version upgrade breaks postgres indices. This basically prevents from upgrading linux OS.
And there are other things which makes postgres operationally difficult.
Any database with primary-replica architecture is operationally difficult IMO.
If you are interested in reading about JOINS in detail, ClickHouse has good blogs around it:
- https://clickhouse.com/blog/clickhouse-fully-supports-joins-...
- https://clickhouse.com/blog/clickhouse-fully-supports-joins-...
- https://clickhouse.com/blog/clickhouse-fully-supports-joins-...
- https://clickhouse.com/blog/clickhouse-fully-supports-joins-...
- https://clickhouse.com/blog/clickhouse-fully-supports-joins-...
Definitely clickhouse ecosystem.
- For querying csv data from command line, I use clickhouse-local.
- For querying csv data programmatically using a library, I use chdb (embedded version of clickhouse)
- For querying large amount of csv data programmatically, I offload it to clickhouse cluster which can do processing in distributed fashion.
If you are looking from query performance perspective, this blog is useful: https://www.vantage.sh/blog/clickhouse-local-vs-duckdb
I enjoy using clickhouse-local for parsing csv files. I generally hit situations where I need custom delimiter and custom parsing rules, I find it handles all of these edge cases very well. Recently I found that if my csv files are compressed, i don't even need to uncompress them, it auto-magically figures out the compression format and process it for me.
I have read your blogs and The Little Go Book. I must say that they are great. Thank you for writing those and keep writing.
Wow I didn't know that snowflake charges on query level. Very good business idea to first create dependency and rip them off once their business grows.
Sometime back, I tried using clickhouse-keeper as zookeeper alternative with few other systems like kafka, mesos, solr, Wrote some notes here: https://pradeepchhetri.xyz/clickhousekeeper/
If you are interested in performance aspect of databases, I would recommend watching this great talk [0] from Alexey, ClickHouse Developer, where he talks about various aspects like designing systems by first realising the hardware capabilities and understanding the problem landscape.
I would consider following things before selecting a database offering:
- Whether the database vendor is a lock-in. It would be a straight "NO" for me if the database isn't open-source with proper license, since I can't self-host it in case, I need to move away from their SaaS offering due to various unexpected foreseen reasons: vendor decided to increase pricing, vendor has hidden pricing, vendor has reliability issues etc etc.
- How big is the community behind the database. Check their public forums to understand how community feels about the database and how their requirements are considered.
- Don't believe any random benchmarking post online but by doing my own benchmarking for my use-case.
- Check which other companies have adopted that database and read their experiences.
As an SRE, I used to install one tool for every data format until I came across clickhouse-local. Now I use it for everything.
- It works with every data format which I come across in my daily job. It support data formats like Protobuf, Avro, Cap'n Proto which regular tools don't support. Funny thing, it can even read mysql dumps.
- I can read the data stored in local or remote location like http, s3, gcs, azure and whatever location I can think of.
- I can SQL queries on the raw data and improve my SQL skills on daily basis.