Okay, so yeah, thank you, everyone, for coming.
The streaming data, the last one of the day, after that, you're free to use, you know,
go elsewhere.
For the last one, we have a super cool talk with Alex, he is the CEO of the house.
Yes, he's going to tell us how to build real-time application with the house.
Yeah, thank you.
So, the title of my talk is very similar to the previous.
So, let's see what will be the difference.
I will try to build a small, simple analytical application, just about right now.
And how to build an analytical application?
We have to figure out what to do, where to collect our data, how to prepare and clean our data,
how to load it, and how to visualize it.
And I will use the following technologies.
Apache Flink, Apache Boom, Apache Kafka, Apache Pulsar, Apache Spark, Apache Plane,
Stringlet, the Bezier, Apache Iceberg, Apache Superset.
Every time I notice Apache once again, I'm looking more and more stupid.
So, maybe I don't actually have to use all of these technologies, because if I do,
at least I will have to be able to tell apart what is the difference between Apache Kafka
and Apache Pulsar.
If you cannot, don't even try to use these technologies.
And what I want to do?
Actually, I want just analyze data.
What power data? Give me some data.
I want to analyze it.
I have no idea what I will get in the result.
I want some interesting data set with logs, metrics, time series data.
I want clicks, whatever.
So, where to find this data?
If you want some demos, there are plenty of sources of fairly available public
updatable data sets, like Internet Archive or Zmodo.org, whatever does it mean,
or Common Core, GitHub Archive, Wikipedia, blockchain data from public blockchains,
whatever scans.
Sometimes you can do metrics scan by yourself and get away with it, but there are
plenty of downloads.
So, maybe you will be surprised by my choice, but I selected the data from Wikipedia.
Exactly, almost exactly as from previous talk.
The data is available on dumps.wikimedia.org.
It is public demoing.
You can do whatever you want with it.
It contains data dumps, edit history, and page view statistics.
And I will analyze page view statistics.
It is updated every hour and represented by about 70,000 gzip files,
3.5 terabytes.
What to do is 3.5 terabytes, download it.
So, the data looks like this.
It looks kind of low, and I like it.
And how to download it?
With this shell script, it looks kind of raw, and I like it.
So, what it is doing?
It writes by years, it writes by month, collects on the list of links,
and then simply downloading by parallel with WGET and XRX.
It is rate-limited to three concurrent requests, apparently.
Actually, WGET has recursive mode, but it does not have parallelism,
so I decided to simply parallelize with XRX.
And after about three days, data is downloaded.
Let's preview it.
If you decompress just one file, it looks like this.
It is kind of a strange format.
It is not CSV, not TSV, not JSON.
It does not look like ProtoBuff.
It is the white space separated file.
It was just a few fields.
Title, project, subproject, the number of page views,
and also zero, for whatever, always zero field.
How to load this data?
And I like shell scripts, but I don't want to use set, oak, and parallel.
Even despite I am on this open source conference,
I will not use set, oak, and parallel.
Instead, I will use ClickHouse local.
What is ClickHouse local?
It is a small tool for analytical data processing
on local files or remote files without a server.
You don't have to install ClickHouse to use ClickHouse local.
And it can process every data format.
It can process external data from external data sources,
data lakes, object storages, everything.
And actually, ClickHouse local is not a unique tool.
There are many tools for command line data processing.
Here is a list.
I will not pronounce this list because I like ClickHouse local.
I don't like all these tools.
Installing ClickHouse local is easy.
Google.sh.
It is also safe because, keep in mind, it is pipe.sh,
not pipe.sudo.sh.
Writing it is also easy.
And let's preview this data.
It has interactive mode.
Let's run ClickHouse local.
And we can select directly from URL.
What format to use?
CSV does not work.
But there is a format, pretty simple, named line as string.
What is this format?
It interprets a file as a table with a single column,
named line with type string.
So just a single column with all our data.
We can use it for just filtering.
We can also select from multiple files.
As in this example, we can select a file name.
We can filter by something.
OK.
Now we have some idea what our data looks like.
Now we have to clean up, prepare,
structure our data, maybe convert it into another format.
And I will do it with this select query.
What it is doing?
It is selecting from files all our 3 terabyte jzip files
with line as a string.
It will split the string by white space to some values,
represent it as array, select elements of this array
as project, sub-project, and path.
Path can be URL encoded with percent encoding.
I will use a function decode URL component.
I will also extract the date from the file name
with a function path date time best effort.
And it looks like this.
It is not Russian Wikipedia.
It is AB Wikipedia, whatever it means.
And what is AA Wikipedia?
I don't know.
It will be pretty interesting.
Also, what I did with this 3.5 terabyte of files,
I uploaded to my S3 bucket.
And I just made this S3 bucket public.
So until we have money, you will be able to download something.
But please be kind.
And you can select directly from this S3 bucket as well
from all of these files.
Yes, in the same way.
Okay, so we just previewed our data.
Now let's proceed to real data loading.
Let's install a real ClickHouse server
instead of ClickHouse local.
But actually, there is no difference
between ClickHouse local and ClickHouse client
and ClickHouse server.
Well, everything in a single binary.
You just rename it to ClickHouse server
and it automatically becomes a server.
You can create a sim link.
You can take this binary and install it.
And it will install into user bin, user and etc.
You can run it without installation.
So let's start it and let's create a table.
So here is a table structure.
Five fields, date time, because it is time-serious,
project, sub-project, page title,
name it path, the number of page views, name it hits.
I also enabled stronger compression with ZSTD,
the standard and low cardinality data types.
And this standard is just a compression codec.
I will also index it by path and time.
So I will be able to quickly select for specific pages.
And how to load data into this table?
Let's use Kafka or Pulsar
and automate with Airflow and do ETL with Airbite or DBT.
Actually, I don't know why DBT even exists,
because I can do everything without DBT.
I will do it with just insert select.
Insert into Wikistat my select query from S3.
And I will wait while it finishes.
Let's take a look. You don't see anything.
Let's make a font slightly larger.
I will make a font slightly larger.
Okay.
Now it started to load the data.
0%, 57 CPU consumed,
2 gigabytes per second and 50 million rows per second.
50 million.
I did not watch one of the previous talk.
It was named loading more than a million records per second
on a single server.
So we are loading more than a million records per second
on a single server.
Okay. Let's take a look what is happening,
because just loading data is not enough.
It will take a while.
And what to do while it is loading?
I will run Distat.
Distat will show me the system usage,
and I see that it is bounded by IO,
500 megabytes per second, Britain.
It is compressor data.
IO weighed 68%.
CPU weighed almost non-existing.
I can also run top to see what is happening.
CPU 16 cores, and it works, and IO weighed 70%.
But for me, it is not enough.
For me, it is not enough,
because I also run this tool per top,
because I always profile my code.
So what my code is doing?
It is doing compression, sorting, nothing.
Okay.
And after eight hours,
my data is loaded.
The table size on disk is just 700 gigabytes.
Original was 3.5 terabytes,
so it compressed like five times.
It was in Gzip, now it is in Clickhouse,
with all the column-oriented compression.
The speed was 50 million rows per second,
but actually, it was not true,
because after eight hours, it degraded
to just 14 million rows per second.
Still not bad.
It degraded because data has to be merged on disk,
and it takes write amplification,
it takes additional IO.
So what is the size?
380 billion records, 0.3 trillion.
The total page views on Wikipedia
is just 1 trillion, 300 billion page views.
Nothing surprising, Wikipedia is quite popular.
And about my table.
So every record took just 2.0 bytes compressed.
All this title, like Wikipedia main page,
it was like 50 bytes,
now it is compressed to just two bytes.
And if you look at compression ratio,
actually path is compressed to 170 times
because we sorted by path.
Okay, but so what?
What to do with my data?
I have loaded.
It took, it was 3.5 terabytes,
and I can't be proud that I wasted eight hours
loading this data, and it compressed so well.
But what to do with this data?
We need some actionable insights from this data.
Let's make real-time dashboards.
How to do real-time dashboard?
We can use Grafana, SuperSet,
Netbase, Tableau, Observable, or even Streamlit.
I don't want to use Streamlit,
it looks too complex, too complicated in the previous talk.
And actually there is no problem,
I can use Grafana, SuperSet,
Netbase with Clickhouse, it works perfectly,
but I am an engineer.
And why to use Grafana if I can write my own Grafana in a day?
Let's do it just now.
Let's decide what JavaScript framework to use.
I can use React, View, Swelte,
I don't know what is Swelte, but it is popular.
You know, if Rust were JavaScript framework,
I will use Rust.
Maybe I should use not JavaScript, but TypeScript.
But no, I will use modern JavaScript.
What is modern JavaScript?
Modern JavaScript, it is when you simply open HTML file
in Notepad or VI or whatever,
and writing a code without frameworks,
without build systems, without dependencies.
Actually, I need one dependency, some charting library.
And I just picked a random charting library from GitHub.
Name it Uplot from Lyonya.
The description Uplot is a fast memory-efficient library.
Okay, solved. I will use it.
Another question, how to query my database?
Should I write a backend in Python in Go?
No, I will query my database directly from JavaScript,
from modern JavaScript with Rust API.
I will use Async, await, fetch API, and post my query
to the database, and it will return the data in format JSON.
Okay, enough modern JavaScript.
So, Clickhouse has Rust API embedded into the server.
It has authentication, access control, rate limiting,
quotas, query complexity limiting, parameterized queries,
custom handlers, so you don't have to write a select query,
you can just define a handler like
slash my report, or slash insert my data.
And you can actually open Clickhouse to the Internet
and get away with that.
I did that, it still works.
Okay, here is a query for Wikipedia trends
that we will use for a dashboard.
It will simply select this time series
rounded to some time frame, to some page.
And here is a parameterized query.
It looks slightly different, it's not like question mark here.
It is actually a strictly typed substitution.
Okay, and how long this query will take?
Let me ask you, how long this query will take?
What do you think?
Eight days.
Eight days, why eight days?
It should work on a table with 0.3 trillion records.
How long this query will take?
Twenty milliseconds.
Okay, let's experiment nine milliseconds.
So, you are wrong.
You are also wrong.
I was scrolling back and forth.
So, maybe Clickhouse is fast.
What if I do my SQL, 29 milliseconds?
Okay, closer.
MariaDB, 20 milliseconds.
What if I will replace equality comparison to like
and add percent?
The same, because prefix also using index.
But what if I will add percent on the front?
Okay, now it started to do a full scan.
And this full scan was quite fast,
over 1 billion records per second,
but still not fast enough for real time.
But all the queries with exact matching was real time.
Okay, let me show you this dashboard.
It looks like this modern dashboard.
It looks actually gorgeous.
It has dark seam.
And you can see it compares trends on Wikipedia for Clickhouse.
Clickhouse is growing.
Spark is not growing.
Green Plum is not growing.
What was there?
Snowflake is quite okay.
Let's check it.
Let's see what is inside.
Every chart is defined with parameterized query.
You write select.
Actually, it's not even parameterized.
Okay, what about MongoDB?
Here I define a new chart and here is Mongo.
Okay, I did one mistake.
It was filtered by outliers for Snowflake.
Let's move.
Okay, Mongo...
No, Mongo is not doing great.
Clickhouse is doing great.
By the way, what if you will just open a dashboard by default?
It will present you observability dashboard for Clickhouse.
So you can see what the system is doing.
It is actually the same code, the same dashboard,
but different queries.
You can use parameterized queries for these parameters,
change parameters, change the time frame.
It's not like Grafana, it does not have features,
but it is nice.
And you can see, yes, it is a single HTML page
and here is a proof.
Okay.
So what do we have?
We have created real-time dashboard with Clickhouse.
We have loaded 0.3 trillion records of data from a public data set.
It works, it works fast, it looks great.
And if you want to build...
Actually, I don't insist you to use modern JavaScript.
I don't insist you to query Clickhouse directly from a user browser.
You can use Grafana superset meta-base.
Streamlit, maybe, I'm not sure.
But you can also build these small applications.
And I have built quite a few.
There is Clickhouse Playground where you can explore some data sets.
There is a web page for Clickhouse testing infrastructure.
Name it R, test green, yet you can try and check what it is.
And the source code, dashboard HTML, is located in our repository.
And just to note, this service is not original.
I have found multiple similar services, for example, WikiShark,
for the same trends on Wikipedia.
But on WikiShark, there is a description that the author...
I did not remember, maybe he made a PhD implementing a data structure,
custom data structure for this.
But he can simply load the data into Clickhouse.
The experience of working with Clickhouse worth multiple PhDs.
Okay.
Thank you, that's it.
Thank you.
We do have time for multiple questions.
More than JavaScript, for example.
Why is this super fast?
Very easy.
Why this dashboard is fast?
Because it's processing very fast.
Why it is inserting fast?
Why it is selecting fast?
Because I always profile it.
You have seen, I always look at what is happening inside the code.
What can be optimized?
If I see that, like some percent of time,
spent doing nothing for mem copy,
I'm thinking maybe I should optimize mem copy.
Maybe I should remove mem copy.
But actually a very long list about everything.
But still we are talking about one machine.
If one machine can process all the data.
Yeah.
I just created a machine on AWS with GP2 EBS,
just in case.
Data was in S3.
I have uploaded.
By the way, maybe we have time for some demos.
But the resolution, the screen resolution is not.
And Wi-Fi stopped to work.
So probably no demos.
But okay, next questions.
Okay.
Hello, thanks for the talk.
You mentioned compression.
Does that slow down select?
Not quite.
Actually compression can even improve select queries.
It is kind of paradoxical, but let me explain.
First, because less amount of data will be read from disk.
Second, because data read from disk is also cached in memory,
in the page cache.
And the page cache will contain compressed data.
And when you process this data,
you will decompress this data into CPU cache
without using main memory.
So even...
Yeah.
LZ4 from multiple threads can be faster than memory bandwidth.
ZSTD not always.
But on servers like AMD Epic with 128 cores,
if you run ZSTD decompression in every core,
it has a chance to be faster than memory.
Thank you.
So what is your total AWS bill for this project?
I prepared it yesterday and used also S3,
prepared before that.
So let's calculate S3 cost.
I am storing the original data, three and a half terabytes.
And it should be like 23.
But 23 is the least price per month for terabytes.
So it will be like $70 per month
if you don't have AWS discounts.
But I do.
And for the server,
the server was about $4 per hour for a server.
And something for GP2.
So maybe something like $5 per hour.
And it is still running.
I started up it yesterday when I prepared the talk.
And so 24 hours will be how many?
Something like maybe $50.
Okay, I spent $50 for this talk.
Is your S3 back in public?
Yeah, it is public.
So keep in mind, if you will abuse it,
we will simply turn it off.
Maybe another question about S3.
What type of connectors do you have to S3?
Is it just for uploading?
Or can you also use S3 for indexing and storing data?
Yes, you can.
And in multiple different ways.
First, just a bunch of files on S3.
Process them as is.
Parquet, protobufs, Avro.
Avro does not matter.
Everything works.
Second, you can process files in Apache Delta Lake
or Apache Hoodie.
Asperk will be supported maybe in the next release.
So you can prepare data in your data bricks or Spark.
And process with Clickhouse because Clickhouse is better than Spark.
Third option.
You can also plug in S3 as a virtual file system for merge three tables.
And it will be used not only for selects but also for inserts.
And you can have your servers almost stateless.
And the data will be in the object storage.
Yeah, plenty of options.
One more question.
Yeah, for sure.
You can use it in a cluster.
You can set up an insert in a distributed table and it will scale linearly.
And these queries will also scale.
The queries that take already like 9 milliseconds, 10 milliseconds will take not less.
Maybe they will take even more, like 15 milliseconds.
But the queries that took seconds, minutes, they will scale linearly.
Theoretically, no.
But in practice, some companies are using Clickhouse on over 1,000 of nodes.
Many companies are using Clickhouse on several hundreds of nodes.
When you have to deal with clusters with hundreds and thousands of nodes,
especially if it is geographically distributed, you will definitely have troubles.
But with Clickhouse, it is totally possible to have these clusters and it will work.
Another question.
Interesting question because maybe you are asking about what are the data structures inside.
Maybe you are asking, is Clickhouse based on some readily available data structures?
The data format, Clickhouse Merge 3, is original.
You can think that maybe it is somehow similar to Apache Iceberg, maybe.
But actually not.
The column format in memory and the network transfer format is also original,
but it is very similar to Apache Arrow.
That's slightly different.
The algorithms, actually, we took every good algorithm from everywhere.
If someone writes a blog post on the Internet like about,
I have implemented the best hash table.
Instantly, someone from my team will try and test it inside Clickhouse.
Okay, looks like no more questions.
Thank you.