4 ms·

Congrats on the Show HN! It's great to see more tools in this area (querying data from various sources in-place) and the Lambda use case is a really cool idea!

Congrats on the Show HN! It's great to see more tools in this area (querying data from various sources in-place) and the Lambda use case is a really cool idea! I've recently done a bunch of benchmarking including ClickHouse Local, and the usage was straightforward, with everything working as it's supposed to. Just to comment on the performance avenue though, one area I think ClickHouse could still possibly improve on - vs OctoSQL[0] at least - is that it seems like the JSON datasource is slower, especially if only a small part of the JSON objects is used. If only a single field of many is used, OctoSQL lazily parses only that field, and skips the others, which yields non-trivial performance gains on big JSON files with small queries. Basically, for a query like `SELECT COUNT(*), AVG(overall) FROM books.json` with the Amazon Review Dataset (10GB), OctoSQL is twice as fast (3s vs 6s). That's a minor thing though (OctoSQL will slow down for more complicated queries, while for ClickHouse decoding the input is and remains the bottleneck, with the processing itself being ridiculously fast). Godspeed with the future evolution of ClickHouse! [0]: https://github.com/cube2222/octosql https://github.com/cube2222/octosql

justinsaccount 4y ago
There's newer json support in clickhouse where you can load arbitrary json objects into a JSON column type: https://clickhouse.com/blog/getting-data-into-clickhouse-part-2-json https://clickhouse.com/blog/getting-data-into-clickhouse-par... Behind the scenes it flattens things into real columns with auto detected types. I suppose if you planned on running many queries that would be faster, but I bet octosql could also convert it to something like parquet for similar gains. There's also some tricks you can do with json filtering if strings are involved, if you have a query like "select path from http_logs where hostname = 'news.ycom...'" you can read a whole chunk of data, do a `memmem` for the string and if no matches, not even bother decoding ANY of the json.

justinsaccount 4y ago

There's newer json support in clickhouse where you can load arbitrary json objects into a JSON column type: https://clickhouse.com/blog/getting-data-into-clickhouse-part-2-json https://clickhouse.com/blog/getting-data-into-clickhouse-par... Behind the scenes it flattens things into real columns with auto detected types. I suppose if you planned on running many queries that would be faster, but I bet octosql could also convert it to something like parquet for similar gains. There's also some tricks you can do with json filtering if strings are involved, if you have a query like "select path from http_logs where hostname = 'news.ycom...'" you can read a whole chunk of data, do a `memmem` for the string and if no matches, not even bother decoding ANY of the json.