It's a super fast filtering and aggregation operators to JSON pass.
So that's very tempting to do, but it comes with three challenges, which I like to explain.
Challenge one is when documents share no common pass.
So let's look at the orange, the red, and the green box.
These are three JSON documents, but they shall not come in pass, right?
So we don't have two or three pass, we have seven pass in total.
There's a general risk that the number of pass explodes and we'll get an avalanche of columns.
Challenge two is when we actually have shared pass, but they do have different types.
So if you look at the green box and the blue box, these two documents have the same structure.
But the pass heads go, and one document is an integer, and the other document is a string.
And that's a problem, because in relational databases, every column needs to be of exactly one type.
And finally, challenge three is that the pass can have very different frequencies across JSON documents.
So imagine a pass that occurs in just one document.
Its column will be virtually empty except for that single one value, right?
We call this pass column, and that's not really great for storage, because it contains literally your hot air.
Okay, now to solve these three challenges, we designed two new data types in Clickhouse as building blocks.
And eventually we added a dedicated JSON type on top of these two building blocks.
Both building blocks can be used standalone and outside of a JSON context, and that would be a totally valid use case.
First building block here is the variant data type.
You can imagine variant as a union of multiple sub types.
So basically, every value has one of the sub types, or it is null.
How does it work?
Let's start with the yellow example column on the left side.
You can see it contains various values of different types.
For example, integer strings, areas, there's also a null there.
And the type of the column is variant of in 64 string and area of in 64.
That's a little bit hard to read. It's in light gray on top of the yellow box in the middle.
Okay, now the big black box on the right shows how their variant column is internally represented.
And there are really two important data structures here.
The first one is the one in light blue, and we call it discriminator column.
It encodes for every value the value is type.
So in the example of one in the first row of the discriminator column, means that the value is in integer.
Two in the second row means it's a string, and zero in the third row means it's an area.
Now you may ask, how do the sub types map to these type integers?
And the mapping is actually implicit.
So what happens is that we sort all the names to use a specified in the variant type.
We sort them, and then the relative position, that's the type integer.
The other structure here is an offset column, and that's the light gray box right next to the blue box.
So this one represents for every value it's position in a sub type column.
So if you look at the bottom right, you see three yellow boxes, and there's a box for every sub type of the variant,
and every box stores the values of a sub type consecutively.
So for example, the leftmost yellow box on the bottom right stores all the integers.
And that's 42, 43, and 44.
And it stores them one after another.
Now the offset column is used to reconstruct the original positions of the sub type values.
And it stores for every value in the original column, the position of the value in its sub type column.
It's not a little complicated, but long story short, the variant data type in click house.
Sort the second and the third challenge that we had on the previous slide.
The second challenge, if you remember, was that JSON pass may contain different types.
And the variant columns in the variant columns you can have values of different types in the same column.
And finally the third challenge was that was about sparse columns.
The variant column, the data is always stored in a dense fashion.
Okay, now the second building block is the dynamic data type, and again it can be used standalone outside JSON context.
The dynamic data type is exactly the same as the variant data type.
The difference is that it does not require you to specify all the possible sub types upfront.
The dynamic type can store whatever data type you throw at it at runtime.
And if you look at the yellow box again, that's the original column.
You see that it has now a data type dynamic, but like the sub types are gone.
They are not specified explicitly.
Because we no longer specify the possible sub types in the type itself, we still need to remember them somehow.
And that is done by an additional file, which is called dynamic structure bin.
And that's highlighted in red on the right.
It contains the contained sub types, and it stores some additional statistics.
So in this example, we store an area of N64, N64, and string a sub types.
And besides that, as I mentioned, there's some more statistics which are needed to optimize the access later on.
Now, remember there was this first challenge, right, where which was about exploding paths and column counts.
And we will now address this.
And the way that it's done is that we allow to specify an optional upper limit on the number of sub types in a dynamic column.
If more types are, if more types than the limit are added, they are not rejected,
but they are stored in a fallback column, which is going to be a little bit slower to access.
So to be more precise, all values of the types which exceed the limit are stored together in a single column.
And we use a very simple pair of data type and value to represent them.
And this is how it looks like. Let's make an example in the yellow box on the right. You see that we used a dynamic data type.
But this time, we also specify the parameter max types S3, which means that at most three types should be stored in dedicated columns.
And the values of the force, the fifth, the sixth, and so on, types should go into a fallback column.
Unfortunately, this column contains more than three types. So all the excess types are stored in the green box on the bottom right.
Each value in the green box is a pair of data type and value.
So in the example, the first value there is a pair of data and 2020-11 followed by a pair of pool and falls and so on.
Now that I introduced the dynamic type and the variant type, I can finally present the JSON data type.
Generally speaking, each pass across all the stored JSON documents go into a dynamic column.
And this is how you formally declare the JSON type in Clickhouse for your data.
There are five parameters in green, max dynamic pass. That specifies the maximum number of pass, which are stored in sub columns.
In the interest of time, I will not explain this mechanism detail.
And then there is in brown, max dynamic types, which specifies the number of types for a single pass, which are stored as sub columns.
If the limit is exceeded, new types are stored together in a fallback column, and that's what you saw on the previous slide.
And then in blue, there is some pass type name and that states which pass should never be stored or should not be stored in a dynamic column.
And there is instead stored as regular columns.
That's quite nice. That's, again, like a special case, and it gives you performance guarantees for certain paths that you want to access very quickly.
Like that, that's without compression, more or less.
And then in purple, skip and skip records, these refer to JSON paths, which should be completely ignored on other words, they will never be stored at all.
Okay, now there's this intent example for the JSON data type.
I will probably not explain it in detail, because the font is quite small, and you will throw things at me if I go too much into the data.
The just of this figure is that there are six different JSON documents, and five pass, and we store every pass by default in a dynamic column, or in a dedicated column, if the user requested that via a parameter in the JSON type declaration.
If you'd like to know more, you can see the nice bookpost about JSON support and kickhouse that we published recently, it's shown at the bottom of the slide.
The last thing I like to show is a benchmark for JSON documents, which we did recently.
This benchmark compares native JSON support in analytics databases, and it runs queries over a data set with one billion blue sky events modeled as JSON documents.
It's total compressed size is 125 gigabyte. Everyone's free to grab the data from our public as free bucket and reproduce the measurements.
So we compared the query performance of the new JSON type in clickhouse versus elastic search, ductDB, MongoDB and Postgres, and you can see clickhouse runs the queries by far as the fastest database ahead of all other systems.
Okay, to sum up, clickhouse now speaks JSON, the implementation is easy to use, it's flexible, it's extremely fast.
Chasing supports currently better and we plan to make it GA during 2025.
And now really the last thing we're going to have a dinner, so a clickhouse dinner tonight with free drinks and free food.
And you're invited to head over and we can talk about any topics about clickhouse database in general.
I would be very happy to meet you there. Thank you.
Any questions?
Please.
Yeah, so the question was about data compression and elastic search.
To be honest, I don't know exactly like from the top of my mind.
Like the exact steps are available in the GitHub repository, you can check it out and see what settings specifically used.
Please.
That's a good question.
I guess I guess there is not as far as I know there are not too many benchmarks about JSON,
it's important data basis for now and it's like still like an emerging thing.
Yeah, but of course every benchmark has weaknesses.
It's always informed by the launch of the benchmark in a balance, right?
Okay, thank you.
Still 30 of you, thank you.
I'm on this.
Yeah, I guess here.
Thank you.
Lovely. Thank you.
Yeah.
I could be.
If any of you have a lot of columns of files, they've been at a lot of the properties.
We've done a few things.
Oh, yeah.
Where are you?
You need to pass them.
They only have a single one.
So it's possible to implement.
Right?
And with this dense solution, you mentioned that now is possible from the site.
Would you just reach out and quiet?
Yeah.
You don't have to solve this at your Nicole.
It's very interesting.
And I didn't touch on that because I was telling you all the time.
Okay.
That was the first parameter in the chase in the declaration that I said,
No, I marked it.
Yeah, yes.
Yes, another for that mechanism.
Okay.
Which, like, compresses, type of files which go beyond this limit.
And they are stored consecutively in the same place.
They're empty.
But it's, but it's all described in the blog.
They have to somehow, in the sense that if I have, I don't know,
I don't know, one hundred parts, like a file and a column.
And then a lot of parts, but then you need.
Do you have any guarantees, though?
At least they have spot you in the course storage.
Yeah, yes.
Yeah.
So yes, it's, like, an upper limit on the number of files.
And then an upper limit on the number of files.
And it's the, but it's the same.
Yeah.
Okay.
So I might have been like it, but I have a, yeah.
Excuse me.
Yes.
Okay.
Thank you.
And this event after the post office is going to be just a form of thing.
Very important.
Gonna have to be here.
Okay.
Thank you.
Yeah.
And yes.
I put that on the backstage room.
Yeah.
He's.
Yeah.
He's a big team there too.
No, yeah.
Yeah.
We're going to have a fight.
So we should be our team here.
Wow.
We know.
We do.
I don't know, I don't know, whatever is working here, I don't know, I don't know, whatever is working here, I don't know, I don't know, I don't know, whatever is working here, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't
Well it's for audio, right?
or if audio, right?
Or if you've got audio, right? You can have this on if you want or you can use that.
But this is only for the string. It doesn't work because you have to speak loud.
If it's too loud, maybe the string will be...
So if you think you're going to speak loud, put it on your shirt on your shirt.
So then that will compensate for it.
I can check the notes anyway.
So when you start talking on, you'll make sure the levels are okay.
Not sure if it's fine, but...
Yes, confusing it.
I always find the right.
Is it perfect? Perfect.
Yeah, I think I'm...
Good, good to wear a shirt.
Yeah, that's that's that.
I mean, actually the hoodie is...
This is...
Yeah, that's that.
Yeah.
I just want to go to the market.
If you're looking for something to do,
there's something to do with the price.
Yeah, that's what I do.
I just want to go first.
Yeah, that's what I do.
I don't want to think about this at the end.
Yeah, that's what I do.
Oh, that's what I do.
Yeah, that's what I do.
And it's a good set for me.
I don't know what it kind of...
I think country's more traditional...
I think country's more traditional, I think...
Yeah, yeah, yeah.
I'm sure we can go.
Yeah, that's what I do.
Yeah, I'm sure that.
Thank you for that.
Yes, thank you for that.
Thank you.
I think, I don't know about the price.
I think that's what I do.
I want to ask.
Thank you.
It's all right.
Thank you.
And I'm not mistaken.
Can I ask you what...
I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't know, I don't
Jerry glitter around the Jeremy will give you that, he will give you your five minutes at the beginning,
You see me, you don't trouble because I come in right at the end so I'll be outside making balls
The moment you appear that's your time. You should be literally wrap up
Okay, okay, so if you see me because I will I will switch to
That's okay, but so you'll fit him he'll give you the five minute mark if you go to late and you see me
You know you've got a finish
Okay, okay, yeah, yeah, because I have more content than that
Oh, yeah, absolutely, but I will tell you I will tell you
Yeah, I will start waving. Oh, sorry. Oh, you know what? We're going to start a fling the earlier
Yeah, we can do that if you want to start five minutes early. We can do that as well
Do you give you five minutes more
Okay, yeah
So just just to not
So we'll start at 20 past so that's the six minutes time. We'll start at six minutes
Okay, yeah, I think
Yeah, so if there is no problem with a synchronization
No, because it was the newscales like a lot of people
People yeah people either I'm going to be here already or if I'm going to be female it's like because I'm not at all
I'm going to be female it's like anyway
So this window talk with no other the basis
It's the kind of the time they still see you
Thanks
Senior