16 ms·

A new JSON data type for ClickHouse

382 points by markhneedham 2y ago

abe94 2y ago
We've been waiting for more JSON support for Clickhouse - the new type looks promising - and the dynamic column, and no need to specifcy subtypes is particularly helpful for us.
officex 2y ago
Great to see! I remember checking you guys out in Q1, great team
baq 2y ago
Clickhouse is criminally underused. It's common knowledge that 'postgres is all you need' - but if you somehow reach the stage of 'postgres isn't all I need and I have hard proof' this should be the next tech you look at. Also, clickhouse-local is rather amazing at csv processing using sql. Highly recommended for when you are fed up with google sheets or even excel.
- oulipo 2y ago
  would you recommend clickhouse over duckdb? and why?
  - PeterCorless 2y ago
    Note that every use case is different and YMMV. https://www.vantage.sh/blog/clickhouse-local-vs-duckdb https://www.vantage.sh/blog/clickhouse-local-vs-duckdb
    - deleted 2y ago
      [deleted]
    - hn1986 2y ago
      Great link . Curious how it compares now that Duckdb is 1.0+
  - theLiminator 2y ago
    Not to mention polars, datafusion, etc. Single node OLAP space is really heating up.
    - fiddlerwoaroof 2y ago
      Clickhouse scales from a local tool like Duckdb to a database cluster that can back your reporting applications and other OLAP applications.
  - nasretdinov 2y ago
    IMO the only reason to not use ClickHouse is when you either have "small" amount of data or "small" servers (<100 Gb of data, servers with <64 Gb of RAM). Otherwise ClickHouse is a better solution since it's a standalone DB that supports replication and in general has very very robust cluster support, easily scaling to hundreds of nodes. Typically when you discover the need for OLAP DB is when you reach that scale, so I'm personally not sure what the real use case for DuckDB is to be completely honest.
    - geysersam 2y ago
      DuckDB probably performs better per core than clickhouse does for most queries. So as long as your workload fits on a single machine (it's likely that it does) it's often the most performant option. Besides, it's so simple, just a single executable. Of course if you're at a scale where you need a cluster it's not an option anymore.
      - zX41ZdbW 2y ago
        The good parts of DuckDB that you've mentioned, including the fact that it is a single-executable, are modeled after ClickHouse.
        RyanHamilton 2y ago
        Can you provide a reference for that belief? To me that's not true. They started from solving very different problems.
        geysersam 2y ago
        I didn't express myself well. What I meant to say was that Duckdb runs a single process. That simplifies things. Clickhouse typically runs several processes (server, clients) interacting and that already makes things more complicated (and more powerful!). That's not to say one is good and the other bad, they're just quite different tools.
    - justCHurious 2y ago
      There is another place where you should not use CH, and it's in a system with shared resources. CH loves, and earned the right, to have spikes of hogging resources. They even allude to this on the Keeper setup - if you put the nodes for the two systems in the same machine, CH will inevitably push Keeper off the bed and the two will come to a disagreement. You should not have it on a k8s Pod for that reason, for example. But then again, you shouldn't have ANY storage of that capacity in a k8s pod anyways.
- deleted 2y ago
  [deleted]
- mrsilencedogood 2y ago
  This is my take too. At one of my old jobs, we were early (very early) to the Hadoop and then Spark games. Maybe too early, because by the time Spark 2 made it all easy, we had already written a lot of mapreduce-streaming and then some RDD-based code. Towards the end of my tenure there, I was experimenting with alternate datastores, and clickhouse was one I evaluated. It worked really, really well in my demos. But I couldn't get buy-in because management was a little wary of the russian side of it (which they have now distanced/divorced from, I think?) and also they didn't really have the appetite for such a large undertaking anymore. (The org was going through some things.) (So instead a different team blessed by the company owner basically DIYd a system to store .feather files on NVME SSDs... anyway). If I were still there, I'd be pushing a lot harder to finally throw away the legacy system (which has lost so many people it's basically ossified, anyway) and just "rebase" it all onto clickhouse and pyspark sparksql. We would throw away so much shitty cruft, and a lot of the newer mapreduce and RDD code is pretty portable to the point that it could be plugged into RDD's pipe() method. Anyway. My current job, we just stood up a new product that, from day 1, was ingesting billions of rows (event data) (~nothing for clickhouse, to be clear. but obviously way too much for pg). And it's just chugging along. Clickhouse is definitely in my toolbox right after postgres, as you state.
- osigurdson 2y ago
  Agree. CH is a great technology to have some awareness of. I use it for "real things" (100B+ data points) but honestly it can really simplify little things as well. I'd throw in one more to round it out however. The three rings of power are Postgres, ClickHouse and NATS. Postgres is the most powerful ring however and lots of times all you need.
- CalRobert 2y ago
  Clickhouse and Postgres are just different tools though - OLTP vs OLAP.
  - fiddlerwoaroof 2y ago
    It’s fairly common in my experience for reports to initially be driven by a Postgres database until you hit data volumes Postgres cannot handle.
anonygler 2y ago
I keep misreading this company as ClickHole and expecting some sort of satirical content.
ramraj07 2y ago
Great to see it in ClickHouse. Snowflake released a white paper before its IPO days and mentioned this same feature (secretly exploding JSON into columns). Explains how snowflake feels faster than it should, they’ve secretly done a lot of amazing things and just offered it as a polished product like Apple.
- statictype 2y ago
  Do you have a link to the Snowflake whitepaper?
  - JosephRedfern 2y ago
    Perhaps this: https://event.cwi.nl/lsde/papers/p215-dageville-snowflake.pdf https://event.cwi.nl/lsde/papers/p215-dageville-snowflake.pd...
- leetrout 2y ago
  Scratch data does this as well with duckdb https://github.com/scratchdata/scratchdata https://github.com/scratchdata/scratchdata
- nojvek 2y ago
  Singlestore has been doing json -> column expansion for a while as well. https://www.singlestore.com/blog/json-builtins-over-columnstore/ https://www.singlestore.com/blog/json-builtins-over-columnst... For a colstore database, dealing with json as strings is a big perf hit.
notamy 2y ago
Clickhouse is great stuff. I use it for OLAP with a modest database (~600mil rows, ~300GB before compression) and it handles everything I throw at it without issues. I'm hopeful this new JSON data type will be better at a use-case that I currently solve with nested tuples.
- philosopher1234 2y ago
  Postgres should be good enough for 300GB, no?
  - notamy 2y ago
    Probably, but Clickhouse has been zero-maintenance for me + my dataset is growing at 100~200GB/month. Having the Clickhouse automatic compression makes me worry a lot less about disk space.
  - tempest_ 2y ago
    It depends, if you want to do any kind of aggregation, counts, or count distinct pg falls over pretty quickly.
  - whalesalad 2y ago
    For write heavy workloads I find psql to be a dog tbh. I use it everywhere but am anxious to try new tools. For truly big data (terabytes per month) we rely on BigQuery. For smaller data that is more OLTP write heavy we are using psql… but I think there is room in the middle.
  - marginalia_nu 2y ago
    At least in my experience, that's about when regular DBMS:es kinda start to suck for ad-hoc queries. You can push them a bit farther for non-analytical usecases if you're really careful and have prepared indexes that assist every query you make, but that's rarely a luxury you have in OLAP-land.
  - wiredfool 2y ago
    I had a postgres database where the main index (160gb) was larger than the entire equivalent clickhouse database (60gb). And between the partitioning and the natural keys, the primary key index in clickhouse was about 20k per partition * ~ 1k partitions. Now, it wasn't a good schema to start with, and there was about a factor of 3 or 4 size that could be pulled out, but clickhouse was a factor of 20 better for on disk size for what we were doing.
fuziontech 2y ago
Using ClickHouse is one of the best decisions we've made here at PostHog. It has allowed us to scale performance all while allowing us to build more products on the same set of data. Since we've been using ClickHouse long before this JSON functionality was available (or even before the earlier version of this called `Object('json')` was avaiable) we ended up setting up a job that would materialize json fields out of a json blob and into materialized columns based on query patterns against the keys in the JSON blob. Then, once those materialized columns were created we would just route the queries to those columns at runtime if they were available. This saved us a _ton_ on CPU and IO utilization. Even though ClickHouse uses some really fast SIMD JSON functions, the best way to make a computer go faster is to make the computer do less and this new JSON type does exactly that and it's so turn key! https://posthog.com/handbook/engineering/databases/materialized-columns https://posthog.com/handbook/engineering/databases/materiali... The team over at ClickHouse Inc. as well as the community behind it moves surprisingly fast. I can't recommend it enough and excited for everything else that is on the roadmap here. I'm really excited for what is on the horizon with Parquet and Iceberg support.
- deleted 2y ago
  [deleted]
everfrustrated 2y ago
>Dynamically changing data: allow values with different data types (possibly incompatible and not known beforehand) for the same JSON paths without unification into a least common type, preserving the integrity of mixed-type data. I'm so excited for this! One of my major bug-bears with storing logs in Elasticsearch is the set-type-on-first-seen-occurrence headache. Hope to see this leave experimental support soon!
- atombender 2y ago
  I never understood why ELK/Kinana chose this method, when there's a much simpler solution: Augment each field name with the data type. For example, consider the documents {"value": 42} and {"value": "foo"}. To index this, index {"value::int": 42} and {"value::str": "foo"} instead. Now you have two distinct fields that don't conflict with each other. To search this, the logical choice would be to first make sure that the query language is typed. So a query like value=42 would know to search the int field, while a query like value="42" would look in the string field. There's never any situation where there's any ambiguity about which data type is to be searched. KQL doesn't have this, but that's one of their many design mistakes. You can do the same for any data type, including arrays and objects. There is absolutely no downside; I've successfully implemented it for a specific project. (OK, one downside: More fields. But the nature of the beast. These are, after all, distinct sets of data.)
  - mr_toad 2y ago
    > For example, consider the documents {"value": 42} and {"value": "foo"}. To index this, index {"value::int": 42} and {"value::str": "foo"} instead. Now you have two distinct fields that don't conflict with each other. But now all my queries that look for “value” don’t work. And I’ve got two columns in my report where I only want one.
    - atombender 2y ago
      The query layer would of course handle this. ELK has KQL, which could do it for you, but it doesn't. That's why I'm saying it's a design mistake. If your data mixes data types, I would argue that your report (whatever that is) _should_ get two columns.
trollied 2y ago
[flagged]
- rockostrich 2y ago
  Analytical databases have rows and columns? What do you do when you're ingesting TBs, if not PBs, of unstructured data and need to make it actually useable. A couple of MBs (or even GBs) for storage for metadata is peanuts compared to the actual data as well as the material savings when storing it in a column-oriented engine.
breadwinner 2y ago
If you're evaluating ClickHouse take a look at Apache Pinot as well. ClickHouse was designed for single-machine installations, although it has been enhanced to support clusters. But this support is lacking, for example if you add additional nodes it is not easy to redistribute data. Pinot is much easier to scale horizontally. Also take a look at star-tree indexes of Pinot [1]. If you're doing multi-dimensional analysis (Pivot table etc.) there is a huge difference in performance if you take advantage of star-tree. [1] https://docs.pinot.apache.org/basics/indexing/star-tree-index https://docs.pinot.apache.org/basics/indexing/star-tree-inde...
- haolez 2y ago
  What's the use case? Analytics on humongous quantities of data? Something besides that?
  - breadwinner 2y ago
    Use case is "user-facing analytics", for example consider ordering food from Uber Eats. You have thousands of concurrent users, latency should be in milliseconds, and things like delivery time estimate must updated in real-time. Spark can do analysis on huge quantities of data, and so can Microsoft Fabric. What Pinot can do that those tools can't is extremely low latency (milliseconds vs. seconds), concurrency (1000s of queries per second), and ability to update data in real-time. Excellent intro video on Pinot: https://www.youtube.com/watch?v=_lqdfq2c9cQ https://www.youtube.com/watch?v=_lqdfq2c9cQ
    - listenallyall 2y ago
      I don't think Uber's estimated time-to-arrival is a statistic on which a database vendor, or development team, should brag about. It's horribly imprecise.
      - akavi 2y ago
        Also isn't something that a (geo)sharded postgres DB with the appropriate indexes couldn't handle with aplomb. Number of orders to a given restaurant can't be more than a dozen a minute or so.
dangsux 2y ago
[dead]
CSDude 2y ago
When I tried it a few weeks ago, because ClickHouse names the files based on column names, weird JSON keys resulted in very long filenames and slashes and it did not play well with it the file system and gave errors, I wonder that is fixed?
- setr 2y ago
  Isn’t that the issue challenge #3 addresses? https://clickhouse.com/blog/a-new-powerful-json-data-type-for-clickhouse#preventing-an-avalanche-of-column-files https://clickhouse.com/blog/a-new-powerful-json-data-type-fo...
  - CSDude 2y ago
    Tried with the latest version, but it doesn't solve. CREATE TABLE mk3 ENGINE = MergeTree ORDER BY (account_id, resource_type) SETTINGS allow_nullable_key = 1 AS SELECT *, CAST(content, 'JSON') AS content_json FROM file('Downloads/data_snapshot.parquet') Query id: 8ddf1377-7440-4b4d-bb8d-955cd0f2b723 ↑ Progress: 239.57 thousand rows, 110.38 MB (172.49 thousand rows/s., 79.48 MB/s.) 22% Elapsed: 4.104 sec. Processed 239.57 thousand rows, 110.38 MB (58.37 thousand rows/s., 26.89 MB/s.) Received exception: Code: 107. DB::ErrnoException: Cannot open file /var/folders/mc/gndsp71j6zz64pm7j2wz_6lh0000gn/T/clickhouse-local-503e1494-c3fb-4a5e-9514-be5ba7940fec/data/default/mk3/tmp_insert_all_1_1_0/content_json.plan.features.available.core/audio.dynamic_structure.bin: , errno: 2, strerror: No such file or directory. (FILE_DOESNT_EXIST)
jmspring 2y ago
[flagged]
peteforde 2y ago
I admit that I didn't read the entire article in depth, but I did my best to meaningfully skim-parse it. Can someone briefly explain how or if adding data types to JSON - a standardized grammar - leaves something that still qualifies as JSON? I have no problem with people creating supersets of JSON, but if my standard lib JSON parser can't read your "JSON" then wouldn't it be better to call it something like "CH-JSON"? If I am wildly missing something, I'm happy to be schooled. The end result certainly sounds cool, even though I haven't needed ClickHouse yet.
- lemax 2y ago
  As far as I understand they're talking about the internal storage mechanics of ClickHouse, these aren't user exposed JSON data types, they just power the underlying optimizations they're introducing.
  - selcuka 2y ago
    Which is the same as PostgreSQL [1] or SQLite [2] that can store JSON values in binary formats (both called JSONB) but when you "SELECT" it you get standard JSON. [1] https://www.postgresql.org/docs/current/datatype-json.html https://www.postgresql.org/docs/current/datatype-json.html [2] https://www.sqlite.org/json1.html https://www.sqlite.org/json1.html
    - lucianbr 2y ago
      They both store JSON, each in some particular way, but they don't both store it in the same way. Just like they both store tabular data, but not in the same way, and therefore get different performance characteristics. Are you arguing that since Clickhouse is a database like Postgres, there's no point for CH to exist as we already have Postgres? Column-oriented databases have their uses.
      - selcuka 2y ago
        > Are you arguing that [...] there's no point for CH to exist Wow, that escalated quickly. You are reading too much into my comment. You should read the comment thread from the beginning to understand which question I'm replying to.
maccard 2y ago
I've heard wonderful things about ClickHouse, but every time I try to use it, I get stuck on "how do I get data into it reliably". I search around, and inevitably end up with "by combining clickhouse and Kafka", at which point my desire to keep going drops to zero. Are there any setups for reliable data ingestion into Clickhouse that don't involve spinning up Kafka & Zookeeper?
- ramraj07 2y ago
  Where are you loading the data from! I had no trouble loading data from s3 parquet.
  - maccard 2y ago
    I'm streaming data from a desktop application written in C++. It's the step to get it into parquet in the first place.
    - mplanchard 2y ago
      We use this Rust library to do individual and batch inserts: https://docs.rs/clickhouse/latest/clickhouse/ https://docs.rs/clickhouse/latest/clickhouse/ The error messages for batch inserts are TERRIBLE, but once it’s working it just hums along beautifully. I’d be surprised if there isn’t a similar library for C++, as I believe clickhouse itself is written in C++
    - andag 2y ago
      There is an http API and it can eat json and csv too (as well as tons of others)
- sdairs 2y ago
  Interesting, that's not a problem I've come across before particularly - could you share more? Are you looking for setups for OSS ClickHouse or managed ClickHouse services that solve it? Both Tinybird & ClickHouse Cloud are managed ClickHouse services that include ingest connectors without needing Kafka Estuary (an ETL tool) just released Dekaf which lets them appear as a Kafka broker by exposing a Kafka-compatible API, so you can connect it with ClickHouse as if it was Kafka, without actually having Kafka (though I'm not sure if this is in the open source Estuary Flow project or not, I have a feeling not) If you just want to play with CH, you can always use clickhouse-local or chDB which are more like DuckDB, running without a server, and work great for just talking to local files. If you don't need streams and are just working with files, you can also use them as an in-process/serverless transform engine - file arrives, read with chDB, process it however you need, export it as CH binary format, insert directly into your main CH. Nice little pattern than can run on a VM or in Lambda's.
kreetx 2y ago
This seems similar to instead of storing any specific part (int, string, array) of JSON, just store any JSON type in the column, much like "enum with fields" in Swift, Kotlin or Rust, or algebraic data types in Haskell - a feature not present in many other languages.
Thorrez 2y ago
>For example, if we have two integers and a float as values for the same JSON path a, we don’t want to store all three as float values on disk Well, if you want to do things exactly how JS does it, then storing them all as float is correct. However, The JSON standard doesn't say it needs to be done the same way as JS.
- barumrho 2y ago
  The new Variant type exists independently of JSON support, so it seems good that they handle it properly.
karsinkk 2y ago
Oracle 23ai also has a similar feature that "explodes" JSON into relational tables/columns for storage while still providing JSON based access API's : https://www.oracle.com/database/json-relational-duality/ https://www.oracle.com/database/json-relational-duality/
jakozaur 2y ago
Looks like Snowflake was the first popular warehouse to have variant type which could put JSON values into separate columns. It turned out great idea which inspired other databases.
jojohohanon 2y ago
I’m a few years removed, but isn’t this how google capacitor stores protobufs (which are ~ equivalent to json in what they can express)?

382 points by markhneedham 2y ago

We've been waiting for more JSON support for Clickhouse - the new type looks promising - and the dynamic column, and no need to specifcy subtypes is particularly helpful for us.

officex 2y ago

Great to see! I remember checking you guys out in Q1, great team

baq 2y ago

Clickhouse is criminally underused. It's common knowledge that 'postgres is all you need' - but if you somehow reach the stage of 'postgres isn't all I need and I have hard proof' this should be the next tech you look at. Also, clickhouse-local is rather amazing at csv processing using sql. Highly recommended for when you are fed up with google sheets or even excel.

oulipo 2y ago
would you recommend clickhouse over duckdb? and why?
- PeterCorless 2y ago
  Note that every use case is different and YMMV. https://www.vantage.sh/blog/clickhouse-local-vs-duckdb https://www.vantage.sh/blog/clickhouse-local-vs-duckdb
  - deleted 2y ago
    [deleted]
  - hn1986 2y ago
    Great link . Curious how it compares now that Duckdb is 1.0+
- theLiminator 2y ago
  Not to mention polars, datafusion, etc. Single node OLAP space is really heating up.
  - fiddlerwoaroof 2y ago
    Clickhouse scales from a local tool like Duckdb to a database cluster that can back your reporting applications and other OLAP applications.
- nasretdinov 2y ago
  IMO the only reason to not use ClickHouse is when you either have "small" amount of data or "small" servers (<100 Gb of data, servers with <64 Gb of RAM). Otherwise ClickHouse is a better solution since it's a standalone DB that supports replication and in general has very very robust cluster support, easily scaling to hundreds of nodes. Typically when you discover the need for OLAP DB is when you reach that scale, so I'm personally not sure what the real use case for DuckDB is to be completely honest.
  - geysersam 2y ago
    DuckDB probably performs better per core than clickhouse does for most queries. So as long as your workload fits on a single machine (it's likely that it does) it's often the most performant option. Besides, it's so simple, just a single executable. Of course if you're at a scale where you need a cluster it's not an option anymore.
    - zX41ZdbW 2y ago
      The good parts of DuckDB that you've mentioned, including the fact that it is a single-executable, are modeled after ClickHouse.
      - RyanHamilton 2y ago
        Can you provide a reference for that belief? To me that's not true. They started from solving very different problems.
      - geysersam 2y ago
        I didn't express myself well. What I meant to say was that Duckdb runs a single process. That simplifies things. Clickhouse typically runs several processes (server, clients) interacting and that already makes things more complicated (and more powerful!). That's not to say one is good and the other bad, they're just quite different tools.
  - justCHurious 2y ago
    There is another place where you should not use CH, and it's in a system with shared resources. CH loves, and earned the right, to have spikes of hogging resources. They even allude to this on the Keeper setup - if you put the nodes for the two systems in the same machine, CH will inevitably push Keeper off the bed and the two will come to a disagreement. You should not have it on a k8s Pod for that reason, for example. But then again, you shouldn't have ANY storage of that capacity in a k8s pod anyways.
deleted 2y ago
[deleted]
mrsilencedogood 2y ago
This is my take too. At one of my old jobs, we were early (very early) to the Hadoop and then Spark games. Maybe too early, because by the time Spark 2 made it all easy, we had already written a lot of mapreduce-streaming and then some RDD-based code. Towards the end of my tenure there, I was experimenting with alternate datastores, and clickhouse was one I evaluated. It worked really, really well in my demos. But I couldn't get buy-in because management was a little wary of the russian side of it (which they have now distanced/divorced from, I think?) and also they didn't really have the appetite for such a large undertaking anymore. (The org was going through some things.) (So instead a different team blessed by the company owner basically DIYd a system to store .feather files on NVME SSDs... anyway). If I were still there, I'd be pushing a lot harder to finally throw away the legacy system (which has lost so many people it's basically ossified, anyway) and just "rebase" it all onto clickhouse and pyspark sparksql. We would throw away so much shitty cruft, and a lot of the newer mapreduce and RDD code is pretty portable to the point that it could be plugged into RDD's pipe() method. Anyway. My current job, we just stood up a new product that, from day 1, was ingesting billions of rows (event data) (~nothing for clickhouse, to be clear. but obviously way too much for pg). And it's just chugging along. Clickhouse is definitely in my toolbox right after postgres, as you state.
osigurdson 2y ago
Agree. CH is a great technology to have some awareness of. I use it for "real things" (100B+ data points) but honestly it can really simplify little things as well. I'd throw in one more to round it out however. The three rings of power are Postgres, ClickHouse and NATS. Postgres is the most powerful ring however and lots of times all you need.
CalRobert 2y ago
Clickhouse and Postgres are just different tools though - OLTP vs OLAP.
- fiddlerwoaroof 2y ago
  It’s fairly common in my experience for reports to initially be driven by a Postgres database until you hit data volumes Postgres cannot handle.

anonygler 2y ago

I keep misreading this company as ClickHole and expecting some sort of satirical content.

ramraj07 2y ago

Great to see it in ClickHouse. Snowflake released a white paper before its IPO days and mentioned this same feature (secretly exploding JSON into columns). Explains how snowflake feels faster than it should, they’ve secretly done a lot of amazing things and just offered it as a polished product like Apple.

statictype 2y ago
Do you have a link to the Snowflake whitepaper?
- JosephRedfern 2y ago
  Perhaps this: https://event.cwi.nl/lsde/papers/p215-dageville-snowflake.pdf https://event.cwi.nl/lsde/papers/p215-dageville-snowflake.pd...
leetrout 2y ago
Scratch data does this as well with duckdb https://github.com/scratchdata/scratchdata https://github.com/scratchdata/scratchdata
nojvek 2y ago
Singlestore has been doing json -> column expansion for a while as well. https://www.singlestore.com/blog/json-builtins-over-columnstore/ https://www.singlestore.com/blog/json-builtins-over-columnst... For a colstore database, dealing with json as strings is a big perf hit.

notamy 2y ago

Clickhouse is great stuff. I use it for OLAP with a modest database (~600mil rows, ~300GB before compression) and it handles everything I throw at it without issues. I'm hopeful this new JSON data type will be better at a use-case that I currently solve with nested tuples.

philosopher1234 2y ago
Postgres should be good enough for 300GB, no?
- notamy 2y ago
  Probably, but Clickhouse has been zero-maintenance for me + my dataset is growing at 100~200GB/month. Having the Clickhouse automatic compression makes me worry a lot less about disk space.
- tempest_ 2y ago
  It depends, if you want to do any kind of aggregation, counts, or count distinct pg falls over pretty quickly.
- whalesalad 2y ago
  For write heavy workloads I find psql to be a dog tbh. I use it everywhere but am anxious to try new tools. For truly big data (terabytes per month) we rely on BigQuery. For smaller data that is more OLTP write heavy we are using psql… but I think there is room in the middle.
- marginalia_nu 2y ago
  At least in my experience, that's about when regular DBMS:es kinda start to suck for ad-hoc queries. You can push them a bit farther for non-analytical usecases if you're really careful and have prepared indexes that assist every query you make, but that's rarely a luxury you have in OLAP-land.
- wiredfool 2y ago
  I had a postgres database where the main index (160gb) was larger than the entire equivalent clickhouse database (60gb). And between the partitioning and the natural keys, the primary key index in clickhouse was about 20k per partition * ~ 1k partitions. Now, it wasn't a good schema to start with, and there was about a factor of 3 or 4 size that could be pulled out, but clickhouse was a factor of 20 better for on disk size for what we were doing.

fuziontech 2y ago

Using ClickHouse is one of the best decisions we've made here at PostHog. It has allowed us to scale performance all while allowing us to build more products on the same set of data. Since we've been using ClickHouse long before this JSON functionality was available (or even before the earlier version of this called `Object('json')` was avaiable) we ended up setting up a job that would materialize json fields out of a json blob and into materialized columns based on query patterns against the keys in the JSON blob. Then, once those materialized columns were created we would just route the queries to those columns at runtime if they were available. This saved us a _ton_ on CPU and IO utilization. Even though ClickHouse uses some really fast SIMD JSON functions, the best way to make a computer go faster is to make the computer do less and this new JSON type does exactly that and it's so turn key! https://posthog.com/handbook/engineering/databases/materialized-columns https://posthog.com/handbook/engineering/databases/materiali... The team over at ClickHouse Inc. as well as the community behind it moves surprisingly fast. I can't recommend it enough and excited for everything else that is on the roadmap here. I'm really excited for what is on the horizon with Parquet and Iceberg support.

deleted 2y ago
[deleted]

everfrustrated 2y ago

>Dynamically changing data: allow values with different data types (possibly incompatible and not known beforehand) for the same JSON paths without unification into a least common type, preserving the integrity of mixed-type data. I'm so excited for this! One of my major bug-bears with storing logs in Elasticsearch is the set-type-on-first-seen-occurrence headache. Hope to see this leave experimental support soon!

atombender 2y ago
I never understood why ELK/Kinana chose this method, when there's a much simpler solution: Augment each field name with the data type. For example, consider the documents {"value": 42} and {"value": "foo"}. To index this, index {"value::int": 42} and {"value::str": "foo"} instead. Now you have two distinct fields that don't conflict with each other. To search this, the logical choice would be to first make sure that the query language is typed. So a query like value=42 would know to search the int field, while a query like value="42" would look in the string field. There's never any situation where there's any ambiguity about which data type is to be searched. KQL doesn't have this, but that's one of their many design mistakes. You can do the same for any data type, including arrays and objects. There is absolutely no downside; I've successfully implemented it for a specific project. (OK, one downside: More fields. But the nature of the beast. These are, after all, distinct sets of data.)
- mr_toad 2y ago
  > For example, consider the documents {"value": 42} and {"value": "foo"}. To index this, index {"value::int": 42} and {"value::str": "foo"} instead. Now you have two distinct fields that don't conflict with each other. But now all my queries that look for “value” don’t work. And I’ve got two columns in my report where I only want one.
  - atombender 2y ago
    The query layer would of course handle this. ELK has KQL, which could do it for you, but it doesn't. That's why I'm saying it's a design mistake. If your data mixes data types, I would argue that your report (whatever that is) _should_ get two columns.

trollied 2y ago

[flagged]

rockostrich 2y ago
Analytical databases have rows and columns? What do you do when you're ingesting TBs, if not PBs, of unstructured data and need to make it actually useable. A couple of MBs (or even GBs) for storage for metadata is peanuts compared to the actual data as well as the material savings when storing it in a column-oriented engine.

breadwinner 2y ago

If you're evaluating ClickHouse take a look at Apache Pinot as well. ClickHouse was designed for single-machine installations, although it has been enhanced to support clusters. But this support is lacking, for example if you add additional nodes it is not easy to redistribute data. Pinot is much easier to scale horizontally. Also take a look at star-tree indexes of Pinot [1]. If you're doing multi-dimensional analysis (Pivot table etc.) there is a huge difference in performance if you take advantage of star-tree. [1] https://docs.pinot.apache.org/basics/indexing/star-tree-index https://docs.pinot.apache.org/basics/indexing/star-tree-inde...

haolez 2y ago
What's the use case? Analytics on humongous quantities of data? Something besides that?
- breadwinner 2y ago
  Use case is "user-facing analytics", for example consider ordering food from Uber Eats. You have thousands of concurrent users, latency should be in milliseconds, and things like delivery time estimate must updated in real-time. Spark can do analysis on huge quantities of data, and so can Microsoft Fabric. What Pinot can do that those tools can't is extremely low latency (milliseconds vs. seconds), concurrency (1000s of queries per second), and ability to update data in real-time. Excellent intro video on Pinot: https://www.youtube.com/watch?v=_lqdfq2c9cQ https://www.youtube.com/watch?v=_lqdfq2c9cQ
  - listenallyall 2y ago
    I don't think Uber's estimated time-to-arrival is a statistic on which a database vendor, or development team, should brag about. It's horribly imprecise.
    - akavi 2y ago
      Also isn't something that a (geo)sharded postgres DB with the appropriate indexes couldn't handle with aplomb. Number of orders to a given restaurant can't be more than a dozen a minute or so.

dangsux 2y ago

[dead]

CSDude 2y ago

When I tried it a few weeks ago, because ClickHouse names the files based on column names, weird JSON keys resulted in very long filenames and slashes and it did not play well with it the file system and gave errors, I wonder that is fixed?

setr 2y ago
Isn’t that the issue challenge #3 addresses? https://clickhouse.com/blog/a-new-powerful-json-data-type-for-clickhouse#preventing-an-avalanche-of-column-files https://clickhouse.com/blog/a-new-powerful-json-data-type-fo...
- CSDude 2y ago
  Tried with the latest version, but it doesn't solve. CREATE TABLE mk3 ENGINE = MergeTree ORDER BY (account_id, resource_type) SETTINGS allow_nullable_key = 1 AS SELECT *, CAST(content, 'JSON') AS content_json FROM file('Downloads/data_snapshot.parquet') Query id: 8ddf1377-7440-4b4d-bb8d-955cd0f2b723 ↑ Progress: 239.57 thousand rows, 110.38 MB (172.49 thousand rows/s., 79.48 MB/s.) 22% Elapsed: 4.104 sec. Processed 239.57 thousand rows, 110.38 MB (58.37 thousand rows/s., 26.89 MB/s.) Received exception: Code: 107. DB::ErrnoException: Cannot open file /var/folders/mc/gndsp71j6zz64pm7j2wz_6lh0000gn/T/clickhouse-local-503e1494-c3fb-4a5e-9514-be5ba7940fec/data/default/mk3/tmp_insert_all_1_1_0/content_json.plan.features.available.core/audio.dynamic_structure.bin: , errno: 2, strerror: No such file or directory. (FILE_DOESNT_EXIST)

jmspring 2y ago

[flagged]

peteforde 2y ago

I admit that I didn't read the entire article in depth, but I did my best to meaningfully skim-parse it. Can someone briefly explain how or if adding data types to JSON - a standardized grammar - leaves something that still qualifies as JSON? I have no problem with people creating supersets of JSON, but if my standard lib JSON parser can't read your "JSON" then wouldn't it be better to call it something like "CH-JSON"? If I am wildly missing something, I'm happy to be schooled. The end result certainly sounds cool, even though I haven't needed ClickHouse yet.

lemax 2y ago
As far as I understand they're talking about the internal storage mechanics of ClickHouse, these aren't user exposed JSON data types, they just power the underlying optimizations they're introducing.
- selcuka 2y ago
  Which is the same as PostgreSQL [1] or SQLite [2] that can store JSON values in binary formats (both called JSONB) but when you "SELECT" it you get standard JSON. [1] https://www.postgresql.org/docs/current/datatype-json.html https://www.postgresql.org/docs/current/datatype-json.html [2] https://www.sqlite.org/json1.html https://www.sqlite.org/json1.html
  - lucianbr 2y ago
    They both store JSON, each in some particular way, but they don't both store it in the same way. Just like they both store tabular data, but not in the same way, and therefore get different performance characteristics. Are you arguing that since Clickhouse is a database like Postgres, there's no point for CH to exist as we already have Postgres? Column-oriented databases have their uses.
    - selcuka 2y ago
      > Are you arguing that [...] there's no point for CH to exist Wow, that escalated quickly. You are reading too much into my comment. You should read the comment thread from the beginning to understand which question I'm replying to.

maccard 2y ago

I've heard wonderful things about ClickHouse, but every time I try to use it, I get stuck on "how do I get data into it reliably". I search around, and inevitably end up with "by combining clickhouse and Kafka", at which point my desire to keep going drops to zero. Are there any setups for reliable data ingestion into Clickhouse that don't involve spinning up Kafka & Zookeeper?

ramraj07 2y ago
Where are you loading the data from! I had no trouble loading data from s3 parquet.
- maccard 2y ago
  I'm streaming data from a desktop application written in C++. It's the step to get it into parquet in the first place.
  - mplanchard 2y ago
    We use this Rust library to do individual and batch inserts: https://docs.rs/clickhouse/latest/clickhouse/ https://docs.rs/clickhouse/latest/clickhouse/ The error messages for batch inserts are TERRIBLE, but once it’s working it just hums along beautifully. I’d be surprised if there isn’t a similar library for C++, as I believe clickhouse itself is written in C++
  - andag 2y ago
    There is an http API and it can eat json and csv too (as well as tons of others)
sdairs 2y ago
Interesting, that's not a problem I've come across before particularly - could you share more? Are you looking for setups for OSS ClickHouse or managed ClickHouse services that solve it? Both Tinybird & ClickHouse Cloud are managed ClickHouse services that include ingest connectors without needing Kafka Estuary (an ETL tool) just released Dekaf which lets them appear as a Kafka broker by exposing a Kafka-compatible API, so you can connect it with ClickHouse as if it was Kafka, without actually having Kafka (though I'm not sure if this is in the open source Estuary Flow project or not, I have a feeling not) If you just want to play with CH, you can always use clickhouse-local or chDB which are more like DuckDB, running without a server, and work great for just talking to local files. If you don't need streams and are just working with files, you can also use them as an in-process/serverless transform engine - file arrives, read with chDB, process it however you need, export it as CH binary format, insert directly into your main CH. Nice little pattern than can run on a VM or in Lambda's.

kreetx 2y ago

This seems similar to instead of storing any specific part (int, string, array) of JSON, just store any JSON type in the column, much like "enum with fields" in Swift, Kotlin or Rust, or algebraic data types in Haskell - a feature not present in many other languages.

Thorrez 2y ago

>For example, if we have two integers and a float as values for the same JSON path a, we don’t want to store all three as float values on disk Well, if you want to do things exactly how JS does it, then storing them all as float is correct. However, The JSON standard doesn't say it needs to be done the same way as JS.

barumrho 2y ago
The new Variant type exists independently of JSON support, so it seems good that they handle it properly.

karsinkk 2y ago

Oracle 23ai also has a similar feature that "explodes" JSON into relational tables/columns for storage while still providing JSON based access API's : https://www.oracle.com/database/json-relational-duality/ https://www.oracle.com/database/json-relational-duality/

jakozaur 2y ago

Looks like Snowflake was the first popular warehouse to have variant type which could put JSON values into separate columns. It turned out great idea which inspired other databases.

jojohohanon 2y ago

I’m a few years removed, but isn’t this how google capacitor stores protobufs (which are ~ equivalent to json in what they can express)?