Altinity Quickstart for ClickHouse®! Build Your First App

Recorded: Tuesday, Jan 25th
Presenters: Robert Hodges, CEO, Altinity

This webinar, the Altinity Quickstart presented by Robert Hodges, CEO of Altinity, walks application developers through everything needed to build their first application on ClickHouse, the open-source SQL data warehouse built for real-time analytics. Robert frames what ClickHouse is good at, reading very large data sets and answering complex questions fast, and what it is not, contrasting it with MySQL, Snowflake, and Redis, before explaining the three features behind its speed: columnar storage, compression, and parallel, vectorized query execution.

The hands-on portion covers getting ClickHouse running, whether from community or Altinity Stable builds, Docker, or the cloud, connecting with clickhouse-client and the built-in Play web UI, and navigating databases, tables, and the invaluable system tables. It then builds a table with the MergeTree engine, explains partitioning, ordering, and the sparse index, and shows several ways to load data: SQL inserts, raw input formats like CSV, JSON, and Parquet, HTTP POST, and parallel loading from S3. Updates and deletes are handled asynchronously as mutations.

On the read side, Robert covers SQL selects with dimensions and aggregates, the strong time-series and date functions, top-N queries, and how joins work, loading the right-side table into memory and scanning the big left-side table in parallel, so the big table belongs on the left. The talk closes with performance self-defense: raising max threads, reading fewer rows, sound table design, and shrinking data with codecs and LowCardinality encoding, plus where to find documentation, community, and help.

If you’d like to receive the PowerPoint presentation, please contact us at marketing@altinity.com. 

Key Moments (Timestamps)

Key moments generated with AI assistance.

• 0:02 – Welcome and introductions
• 1:53 – What ClickHouse is and is not
• 5:04 – Why columnar storage is efficient
• 8:04 – Installing ClickHouse, Docker, and cloud
• 12:00 – Connecting via CLI and web UI
• 14:22 – Databases, tables, and system tables
• 18:04 – Creating a table and MergeTree
• 20:47 – Inserting data and input formats
• 24:10 – Loading data from S3
• 26:12 – Updating and deleting with mutations
• 27:48 – Parts and background merges
• 31:44 – Selecting data and time series
• 37:15 – Aggregate functions and joins
• 45:15 – Performance, codecs, and table design
• 55:22 – Resources and wrap-up

Webinar Transcript

0:02 – Welcome and Introductions

Robert: Okay, we’ve got the recording going. I’d like to welcome everyone to the Altinity Quickstart for ClickHouse, creating your first application. What we’re going to do in the next 60 minutes or so is give you all the information you need to build your first application using the ClickHouse database. My name is Robert Hodges, and I’m the CEO of Altinity. I’ve been working with databases for many years, and it’s a pleasure to be able to give this presentation today.

Let’s do a few more introductions. First of all, us. We come from Altinity. We offer ClickHouse support and services, including Altinity.Cloud. We wrote the Altinity Kubernetes Operator for ClickHouse and a number of other open-source projects, and we’ve been involved in ClickHouse for over five years at this point.

As far as our team, we’re database geeks. Together, we have centuries of experience in databases and applications. For myself, I’ve been working with databases since 1983, so it’s getting on to four decades, a pretty long time to be using them.

You, the audience: we’re focused on application developers who are looking to learn about ClickHouse. We assume you have some background with databases and are somewhat familiar with them, and of course, we assume you have some development chops and know how to put applications together. What we’re going to do is supply the extra information to apply your existing knowledge to ClickHouse.

As this presentation goes on, if you have questions, feel free to throw them into the chat or into the question-and-answer box. We should have a little time at the end to cover them, and if they’re directly relevant to the conversation, I’ll cover them as I go along.

1:53 – What ClickHouse Is, and What It Is Not

Robert: Let’s start with what ClickHouse is. ClickHouse is a SQL data warehouse, which means it is a database designed to read data and give answers very, very quickly. It has a number of properties it shares with some, but not all, other data warehouses. One is that it understands SQL, which is sort of the winning language for queries.

ClickHouse is interesting in that it’s very portable. It runs on everything from bare metal to the cloud. It has what’s called a shared-nothing architecture, which means it’s a bunch of nodes with attached storage connected by a network. It is moving toward a decoupled architecture where compute and storage are separate, but that’s an ongoing process, like almost all data warehouses. It stores data in columns as opposed to rows; we’ll talk about that in a minute. It has extremely efficient execution, both parallelized across many nodes and vectorized, which means it can exploit the parallelism available on CPUs. It will work on your laptop, but it can scale to tens of petabytes in huge clusters; the largest clusters have hundreds of nodes. And it’s open source, permissively licensed under Apache 2.0, which means you can use it anywhere for any business purpose.

What’s happened is that ClickHouse has been so successful it’s evolved into what we might call the core engine for real-time analytics. Real-time analytics means you’re building analytics where you need to either react to information very quickly as it occurs in the outside world or provide instant answers to potentially complex questions. Either way, it’s real-time, and many applications combine both of these things. So we have data feeding in from event streams, ELT, and object storage, coming out to be consumed in dashboards, interactive APIs, and other ways the data can be used. That’s the ClickHouse SQL data warehouse.

It’s useful to take a minute and talk about what ClickHouse is not. It’s not MySQL. MySQL is great for running e-commerce sites, of course, and it underlies countless applications worldwide. ClickHouse doesn’t have a full ACID transaction model, and it’s also very slow on updates, so you can’t use it to run an e-commerce site. You would lose money, and it wouldn’t have the ability to make the changes fast enough. We’ll talk about that in a little bit.

It’s not Snowflake. Snowflake is a cloud data warehouse with an extremely complete SQL implementation. ClickHouse is a product that values speed over SQL standards, and it runs anywhere. So it’s not that we lack SQL, but we may not always do it in exactly the standard way yet.

Finally, it’s not Redis. Redis is used as a front-end cache and also for things like shopping carts, where you have potentially hundreds of thousands of concurrent users who need answers very quickly. ClickHouse is designed to read very large blocks of data, not small things, and to do it for a relatively small number of users, which might be in the hundreds but not tens of thousands.

5:04 – Columnar Storage, Compression, and Parallel Query

Robert: So how does ClickHouse do what it does well, which is to read data? Three things. First, it stores data in columns. This is really important because it means that if you have a very large table, and in fact ClickHouse is designed to permit tables to have hundreds or even thousands of columns, you only need to touch the columns you’re actually asking questions about. This is unlike a row database like MySQL, where if you reference three columns, you’re going to read the data for all of the columns in order to answer that question. So columnar storage is extremely efficient.

It’s also coupled with a couple of other features: first, compression, and second, parallel query. I have an example here of the effect of columnar storage, going all the way to parallelism, and what kind of difference it makes in the amount of data we need to scan.

This big ball on the left represents the volume of reading all columns in a row database that’s storing 59 gigs of uncompressed data. This was based on our on-time data set. Now, if I run a query that just references three columns, all of a sudden, instead of 109 columns in that data set, I’m only reading three columns, three percent of the data. That just happens to be exactly what it turns out to be. Moreover, that data is incredibly compressed. Those three compressed columns, the actual data stored on disk, was 21 megabytes, so we’re already down way below, to three one-hundredths of a percent. Moreover, because we can parallelize on the CPU, we can get eight hardware threads to read that data, and each of those only has to read 2.6 megabytes.

The effect is that the amount of data each thread may read, when we’re scanning a table to read the contents of all three of these columns, is reduced by a factor of tens of thousands. This kind of efficiency means that ClickHouse can answer questions that require us to scan sometimes trillions of rows, but it can answer them in many cases in less than a second. It’s because of the parallelism, the fact that we minimize the data we touch, and the fact that the data we do touch is very, very small.

That’s ClickHouse. I won’t give you any more marketing information. What we’re going to do now is dive in and figure out how to install ClickHouse and connect to it. This is our first step.

8:04 – Installing ClickHouse and Connecting

Robert: There are a couple of different sources for ClickHouse builds. The traditional source is the ClickHouse community builds. Go to the clickhouse.com site, where they’re hosted, and there are a couple of different kinds. There are monthly builds; every month, ClickHouse has a new branch. For example, this is January, so this month’s build is 22.1, the first month of 2022. Every six months, the ClickHouse team has what’s called an LTS release, a long-term support release, and the community supports it for one year. The next LTS build is scheduled for March, so it will be 22.3, and you can count on it having about a year of support.

The other source of builds is Altinity Stable® Builds for ClickHouse, which we produce. Unlike the community builds, we don’t try to do a build every month. Instead, we focus on building the LTS builds, and only when they’re production-ready. So if you pull an Altinity Stable build, you have a build we’ve certified is ready for use in production. We also support a recent one for up to three years. You can find out about those at docs.altinity.com.

Either way, once you point to the repo, you can just use something like apt if you’re on Ubuntu, or yum on Red Hat, and so on, and in a couple of commands, you have ClickHouse up and running. It’s important to note that it’s always two commands to install ClickHouse: one to get the software and a second to start it. This is because ClickHouse lets you decide when you actually want to start the server, which makes upgrades easier. That’s how to get the raw software. If you’re running on Linux, this works great on your laptop, on VMs, and so on.

Another popular way to install ClickHouse is to use Docker. For example, if you’re on a Mac and don’t feel like taking the trouble to install it locally, which you actually can, you can run Docker. Again, there are two sources for the Docker containers. The traditional source is yandex/clickhouse-server; that tag is left over from the days when Yandex was building this. You can pull that, or you can pull altinity/clickhouse-server. Either way, with a single Docker run command, you can bring it up. What I show here is making a directory for my ClickHouse data, then doing docker run with a –volume option to map it to my disk, so I can actually see the data and I won’t lose it if the container restarts. I also do port mapping, set the number of open files, and things like that. This single command will bring it up and running. In both cases, Docker as well as installing, if you have a good network connection, you’re up and running in about 60 seconds.

A final option, if you don’t feel like running it yourself, is to just run it in the cloud. There are a couple of obvious places you can go right now. Altinity.Cloud, which we run, has trials; you can get one spun up in a couple of hours and be out and working with ClickHouse. A second option, and our second service in Eastern Europe, is that a number of people use Yandex, which has a managed service for ClickHouse. There are others on the way, and in later versions of this class we will update them. So there are lots of options to get ClickHouse into a place where you can run it.

Let’s look at connecting to it. There are many ways you can connect to ClickHouse. I’m not going to go into all the tools, just two really simple ways that work out of the box. The first is clickhouse-client, a command-line, or CLI, that talks to ClickHouse. If you install ClickHouse on your laptop, chances are you can just type clickhouse-client, and you’ll be connected. What was it really doing? It was actually running the second command but filling in the options for you. The host was localhost, whatever you’re running on; the port is 9000, the standard port for clickhouse-client, used for the native TCP protocol; and it has a user called default, which always exists in new ClickHouse servers and doesn’t have a password. So you’re connected.

If you’re connecting to something running in the cloud, the command looks more like what we have down below. We give a hostname, in this case, github-demo.altinity.cloud, which is a real host you can go try, a port of 9440, which is TLS encrypted and secure, and then demo, where demo is the password. If you type this command, you’ll be connected to a ClickHouse server immediately.

Finally, there’s a really nice web UI for quickly logging in. You don’t have to install any software; you can run this if you have a browser and can see the server. It’s called the Play interface, and it works by giving the hostname and the port, which is 8123, an HTTP port open by default on new ClickHouse servers. This one we see right here is just connecting to the laptop, and here’s an example with HTTPS down below, again connecting to that same GitHub demo data hosted out on altinity.cloud. So these are two ways to get into ClickHouse, and at this point, you’re connected and can begin to look around.

14:22 – Finding Your Way Around: Databases, Tables, and System Tables

Robert: ClickHouse, just like virtually every other self-respecting database, has databases and tables. The organization is pretty much like MySQL. In MySQL, you have databases, which are basically folders, and then you have tables that live within them. If you go out onto the file system, that’s literally how they’re organized: you see a directory corresponding to your database, and then each table is a directory underneath that.

If you’re a Postgres person, they’re like schemas, they aren’t like Postgres databases. There’s a big distinction there. In Postgres, databases are independent things you connect to one at a time, and when you’re connected to one database on your Postgres server, you can’t see any of the others. ClickHouse databases are just like folders, and every connection to a ClickHouse database has a current database. If you’re connected to that current database, you don’t need a database name, because your tables will all be referenced automatically. We’ll show an example of that.

Commands for navigation: if you’re a MySQL person, you’re going to fall in love, because it’s really simple. SHOW DATABASES lists your databases. SELECT currentDatabase() tells you where you are, what database you’re actually connected to, and your default database. SHOW TABLES shows the local ones, and you can add FROM and give another database. DESCRIBE a table that shows its organization. And a couple of examples of SQL commands: if I say SELECT count() FROM on_time and don’t give a database name, it looks for the table on_time in the local database. If I do give a database name, as in the last example, it selects from that database.

There’s also a USE command. This is very similar to what Sybase used to support, so it goes back a long way. If you say USE system when connected with clickhouse-client, that changes your default, or current, database. When you’re using APIs to connect, they generally require you to say what your current database is, and they set that for you, and it won’t switch around. So this USE system command is not a SQL command; it’s actually something the client has to know about.

Finally, one thing that’s really important to understand about ClickHouse: when you log into your new ClickHouse server, there are always going to be two databases there and ready for you. The default is your current database whenever you log in; when you’re starting up, that’s probably where you’ll end up putting tables. You can make more databases if you want, but that one is always there and available. Then there’s another really important database called the system. As it sounds, these are the system tables, all your metadata about what’s going on inside ClickHouse. These are incredibly useful. We’ll only touch them lightly, but they tell you everything from your level of compression to what’s going on in the log. You can configure them so that, for example, you can get logging information and see records of queries. They are incredibly powerful, and in my experience, they are the best system tables I have ever worked with, really easy to understand and very accessible. We’ll show a couple of examples as we go along.

18:04 – Creating a Table and the MergeTree Engine

Robert: What I’d like to do now is get into the guts of using ClickHouse. We’re going to create a table and show how to load data into it in different ways.

Here’s a CREATE TABLE command. If you haven’t seen SQL before, this is a standard command to create a table. ClickHouse is a little bit different because, traditionally, and in many of the versions you’ll likely be using, it supports these special column types. Where we’d normally just say INT in other SQL databases, here we’d say Int32. The ANSI types have actually been implemented in ClickHouse, so they should work in more recent versions, but these are your table columns. You give a name and a type. It’s all pretty straightforward. Yes, the types are different, but you get used to them in about three minutes, and you’re ready to go.

One thing I should mention is that ClickHouse has very good support for types like arrays and maps, structured data types. We’re not going to talk about those in this talk, but they’re there, and they’re very powerful.

What’s very important to notice right from the start is that ClickHouse has some table properties that are not the same as those of other databases. For example, there’s this engine command. It looks a little bit like MySQL, if you remember ENGINE=InnoDB from those days. We have an engine called MergeTree, which is basically the workhorse engine for analytic data in ClickHouse, and we’ll describe its properties in a few slides.

MergeTree tables are broken up into what are called partitions. This separates the data out into groups and makes it easier for ClickHouse to decide how to divide the data up, because our data sets are very large. What this does is create parts. Parts can be pretty big, into hundreds of gigabytes, but they can’t be infinitely large; this tells ClickHouse where to make breaks. And then, finally, how to order the data. This ORDER BY is very important in columnar databases, because we choose the ordering of all the columns up front, and we also use it to build what’s called a sparse index, which enables us to find anything that is referenced by these columns, dev id and m datetime. We’ll talk more about that in later slides so it becomes clear.

20:47 – Loading Data: Input Formats, HTTP, and S3

Robert: Let’s look at inserting data. The traditional way is to use a SQL INSERT command. Here’s an example: INSERT INTO sdata VALUES, and then you give some tuples. If you’re used to running that in MySQL or Microsoft SQL Server, it works here pretty much the way you’d expect.

The thing is, in real ClickHouse, not many people actually do it this way, because we’re dealing with very large amounts of data and we really don’t want to spend a lot of time turning everything into this SQL statement. So what we in fact do is use input formats that allow us to load data in their raw forms. For example, VALUES is an input format; it says, look past the end of this, and you’ll see tuples on the command. But we can load CSV directly, just raw CSV or CSV with column names. We can load JSON; there are a bunch of ways, and the most popular is to have a single JSON document on each row. We can do Protobuf, Avro, Parquet, ORC. There are literally dozens of these. You can go look them up in the docs and see which one suits you. There’s a really good chance that if you’re loading Parquet, for example, you can load it straight out of the Parquet file. All you need to do is pump it in.

The question is how you do that, and it turns out that ClickHouse has a number of ways of grabbing these files. The simplest way, when you’re starting out and just want to get some data loaded quickly from files, is to load it using clickhouse-client. Here’s my file, sdata.csv, with my column names in the first row. I’m just going to pipe it into clickhouse-client on the command line, send it to a database, and give the INSERT command to tell it what to do. What’s nice is I don’t even have to tell it what columns I have; it reads them and correctly assigns them from the column names on the first line.

If you don’t want to do that, or clickhouse-client is not convenient, you can also load it with an HTTP POST command. This is another really cool feature of ClickHouse: it has a web service API based simply on HTTP POST and GET. Here, doing it in bash, we create our query with nice URL encoding, stick it on the end of the URL after query=, put that value in, and then, using curl, include the body of our data inside the POST request that gets sent off to ClickHouse. This works perfectly well, and you can use it to load data in any of the formats. Of course, you can drive this through JavaScript or Python or whatever, but it’s really handy for loading data.

Another way to get data in quickly when you’re starting out is to get it from S3. We use a form of the INSERT where we INSERT INTO sdata but, instead of VALUES, we say SELECT, and we SELECT star FROM something. That something is not a table. We could select from a table, which is a perfectly good way to load data, but if we don’t have anything there, we use a table function, s3, which is a function that returns tabular output. In this case, it goes and gets the data from S3. You can see we have a nice long URL, we include our access key and secret key for Amazon, and we give the input format. In current releases up through the end of last year, you also had to give the schema, which is a bit painful because, as I showed, clickhouse-client can already figure this out. But you had to give the schema even if you were reading something like Parquet, which already knows it, or CSV with names. As of 22.1, which is this month’s release, that’s no longer necessary.

One important thing to note about loading from S3: you can read multiple files at once. Here you can pull in sdata using a wildcard. And there’s this max_insert_threads=32, which says allocate 32 threads and load this data. One thing to look out for with this kind of parallelization: it makes loading incredibly fast, you can literally load millions of rows per second from S3, but it will use more memory, so you may occasionally run out, in which case you either bump up the memory available to queries or reduce the parallelism.

26:12 – Updates, Deletes, and How Parts Merge

Robert: That was inserting rows. You may wish to update or delete them. This is a place where ClickHouse, again, is very different. It doesn’t yet have a SQL DELETE command or a SQL UPDATE command. Instead, we use a form of ALTER TABLE, and we do it asynchronously. These examples look similar to UPDATE and DELETE commands, but what’s happening is that when you issue these commands, they return instantly and generate what’s called a mutation, which is a command that is going to run in the background. ClickHouse just schedules it, and over time it will get done. If you have a powerful database and you’re not changing very much, it will get done almost instantly. In extreme cases, it could take hours to complete, and the reason it takes a long time is that it actually has to rewrite data. We’ll show an example in a second.

It is possible to find out when these are finished. A very common command, when you’re doing a change, is to use the system.mutations table. Here’s our first example of one of these great system tables, and you can use it to find out the status. Once they say done, they’re finished. Every now and then, one goes rogue or just does not finish, and you can kill it. This will usually knock it off; it may take a little while to stop, but it will stop it if you need that.

It’s worth talking a little about what’s going on inside ClickHouse, because these things are kind of different from other databases. I mentioned that we had parts. Here’s what actually happens. If you look at the database, the table is divided up into parts, each one in a separate folder, and within the parts, they all belong to whatever partition value you set in PARTITION BY. So, for example, all your January data would be in a single part or a set of parts, but they would be only January data. They have columns sorted in the order you gave in your CREATE TABLE, and they also have this sparse index, which has an entry, by default, for every 8,192 rows and allows you to reference blocks of data quickly, as long as there’s something in the index key.

Every time you insert, it generates a part. Now, those parts can be kind of small: ten thousand rows, a hundred thousand rows, maybe a million rows, which is still small by ClickHouse standards. So what ClickHouse does in the background is, because it really wants those parts to get pretty large, into many millions of rows, extending up to, say, two or three hundred gigabytes in some cases, it merges them. You write your parts, you insert them, and in the background, ClickHouse looks them over and, from time to time, runs a merge, taking smaller parts that have the same partition key and merging them into a bigger part. Why is this important? It reduces the number of files we have to read, it gives you longer runs for compression, and it has a number of benefits that mean ClickHouse can then do reads faster off these parts.

An important point: when you do updates and deletes, they have to actually rewrite the parts. Updates will only rewrite the columns they affect, but a delete, for example, when you’re removing rows, has to rewrite the whole part. That requires the same process, and that’s one of the reasons they’re asynchronous.

Here are some tips if you’re new to ClickHouse. You want to pick that PARTITION BY; it’s important. Pick something that gives you nice fat partitions. Here’s some guidance: make sure you end up with less than a thousand parts. It’s easy to find out; there’s a table called system.parts. If you can’t decide, partition by month. That’s the most common one; in fact, ClickHouse used to default to that very early on. It’s a good thing to get started with, and if you have time-based data, it won’t be that far off. Second, use input formats to load that raw data directly; don’t generate a bunch of INSERT statements. And finally, insert large blocks, and when we say large, we mean really large. You can insert tens of millions of rows simultaneously. ClickHouse has no problem with it, and in fact, it’s more efficient because it requires less merging afterward. If you’re inserting single rows, you’re going to die. ClickHouse will just collapse, because every one of those rows generates a block that has to get merged as more rows arrive, and you have to re-merge. That’s not what you want. So make those blocks large, which usually means you have to buffer data a little. If you’re reading out of Kafka, for example, wait for a couple of seconds, let a few million rows come in, and then blast them into ClickHouse. There are many other things about table design and data loading, but these three will get you a long way.

31:44 – Selecting Data: SQL, Time Series, and Aggregates

Robert: Let’s talk about selecting data, because now that we’ve got the data in, it’s time to get it back out again. SELECT is where we start to harvest the benefit of having loaded all this data. Here are some cool diagrams. These were done in Preset, which is hosted on Apache Superset, a really cool dashboarding tool. This shows the air traffic flows between different large airports and the busiest airports. This is the visualization we want to achieve, but to do that, we have to select data out, so let’s look at how.

Here’s a SELECT. I’ll assume everybody on this call has seen SQL once before. All the basic stuff you expect in SQL selects is there. Because we’re using an analytic database, we refer to columns in a different way. We talk about what are called dimensions. A dimension is data that characterizes the thing we’re querying. For example, carrier and year. We’re going to group the data, and when we count things up, we group them by these dimensions. We also have the things we count, which are called aggregates, and I’ll use that terminology consistently. Here we have sum, where we’re adding up the values of a column, and we have count; those are just two examples. We can filter data with a WHERE clause, do a GROUP BY, and use HAVING, which lets you filter on the values of aggregates, so once the aggregates are computed, if they don’t match the HAVING, they get knocked out. You can ORDER the data, which is the order it comes back to you after processing. And, really important in analytic queries, you’ll use LIMIT, because many queries can potentially return millions of rows, but in many cases you’re just interested in a few of them, and LIMIT helps with that.

If you’ve seen SQL before, this is pretty standard stuff, and in fact, this exact query would probably run just fine in most databases; it runs in MySQL. So if you’re familiar with SQL, you can basically just try it out in ClickHouse, and chances are it’ll just work. The question is what’s different.

First, if you’re using ClickHouse and have large data sets, there’s a really good chance you care about time. We have what we call time series: data with built-in time ordering. This is really important in large data sets, because they’re almost always based on facts of some kind, those facts are associated with time, and there’s a really good chance that you care more about more recent things, or that you’re asking questions like what happened over time. So we very commonly run queries that have time dimensions in them. For example, this query takes the date of an airline flight and normalizes the flight date to the start of the week, so everybody in the week has the same value. We call that the week, group by that, and that’s how we get the kind of information you saw visualized on the previous slide. This kind of query is exceedingly common in ClickHouse, and for that reason, ClickHouse has really great support for managing and querying time.

We have three data types. The date type gives you precision to the day; that particular data set uses that. A very common one is the standard Linux DateTime, with precision to the second. And a recent addition in the last couple of years, which I’m proud to say was put in by our team, is DateTime64, which gives precision down to nanoseconds. Those are the column types you can choose from. On top of that, you have a raft of support in the form of functions that can do various transformations on date, datetime, and datetime64. For example, you can normalize to the start of the year, or turn things into a date, or go down to seconds, all kinds of great transformations. Just look them up; they’ll help you construct whatever query you want.

The other thing ClickHouse has is really great aggregate functions. In a standard SQL database, you have five, the ones defined in the SQL standard. You’ll also have many, many more. Let’s pick one: uniq, the number of unique values. You can use that to help compute the number of unique visitors to your site per unit of time, a very common one. You can do quantiles and linear regression, and there’s also this any function, which is kind of interesting; some databases support it, and I’ll show an example in a minute.

Here’s drilling into the GROUP BY syntax using these aggregates. Again, you have the dimensions, the things you’re grouping by, the characteristics of your data that are important to you, and for which you want to count or otherwise aggregate. We’re using carrier and year, and then we count the total flights and the sum of the canceled flights. Anything that is not a GROUP BY has to be an aggregate; it needs to be one of these aggregate functions, otherwise you’ll get a syntax error.

Another example that shows the effect of these aggregate functions in a more interesting way is what’s called a top-N query. This is a really important query that you’ll use constantly, particularly for dashboards where you’re showing who your top customers are, your slowest queries, or which pieces of software have had the most failures of a certain kind. This kind of top-N query uses LIMIT and ORDER in descending order. And here’s an interesting use of aggregates; this one’s called any. It allows you to grab correlated values that correlate to a dimension value. This is helpful because in ClickHouse tables, it’s very common, since there’s no cost to having extra columns, to denormalize. So we include something like the origin city name for flights, and we can just grab that and include the extra data, but it has to be used in an aggregate, and that’s what any does: it just grabs one.

39:48 – Joins and How ClickHouse Processes Them

Robert: Let’s talk about joins. In many ways, joins look identical to joins in any other database. This syntax would work just fine, well, I don’t know about the any, but this would probably work just fine in MySQL. What we have is collecting data from on time, our list of flights, and joining it with the airports to get the airport name. We have a LEFT JOIN, a common type that says: the left side table is on time, so include the on-time data even if we can’t find the airport name, because we might be missing something. Incidentally, in ClickHouse, like many analytic databases, there is no referential integrity between tables, so this kind of left or right join becomes quite important.

Let’s have a look. It looks like we’ve got a little rendering problem here. We’ll look at this in detail because it’s important to understand, particularly because of the way ClickHouse processes joins. In detail, you see we have the left side table, which is on time, joined to the right side table, and then your different left and right joins. These are sometimes called a left outer join or right outer join. The inner join only includes rows that match; a full outer join includes everything, including non-matching rows; and there’s the cross join. These are standard join types present in just about every database, and ClickHouse does support them. If you’re curious about how they work, it’s really easy in ClickHouse to set up some little tables and check them out yourself. Here’s an example I use to make sure I’m not getting left and right mixed up.

ClickHouse does this fine. What’s different about ClickHouse is the following. ClickHouse doesn’t really know how big the tables are. Well, that’s not totally true; it does know, but it doesn’t use that information. So when ClickHouse processes that join, leaving aside parsing and such, it goes through three steps. First, it finds the right side table in the join and loads it into memory. It will filter it if it can, applying some predicates, but basically it constructs a hash table in memory. Then you have the left-side table, and this only allows you to do what’s called an equijoin, where the values equal each other. Once it’s got that right side table in memory, it does a parallel scan of the left side table. What we hope is that the left-side table is big, because that’s where you get your parallelization. As it runs down that join, as each hardware thread is reading data and computing aggregates, it also looks across and grabs data out of that in-RAM hash table and adds it to the results as it goes along. Eventually, all the threads bring their results to a single point where they get merged; for example, each thread computes part of the aggregate, and then they all get combined into your final answer, sorted, and handed back to you.

So the effect is that you want the left side to be your big table. That’s where you get the power of ClickHouse, with all the parallelization and the effects of reading compressed data. The right side table, and you can have more than one, ClickHouse is perfectly happy to join against multiple tables, those need to be small and fit in memory. This is a place where ClickHouse differs from other databases; you need to make some choices. The nice thing is it’s not very hard to understand.

Here are some tips for querying data with ClickHouse. Use the time functions. Every now and then, I see people just selecting raw data out of ClickHouse. It’s pretty rare to do that; sometimes you may, perhaps because you’re looking at the source that generated an aggregate, but what you want to do is typically ask a question where you’re counting something, often by some unit of time, so those time functions are there. Similarly, you don’t want to pull data out and aggregate it yourself. Use the aggregates to summarize the data inside ClickHouse, which can do this vastly faster than almost any application. And finally, if you’re doing joins, the big table goes on the left side of the join. That’s really important, otherwise, you’ll run out of memory or have other unfortunate side effects. Eventually, we hope this year, ClickHouse will be able to start applying some of the information it possesses and set the join order automatically, and this will become less important.

45:15 – Performance Self-Defense and MergeTree Internals

Robert: Speaking of performance, the final section is about some self-defense on performance. ClickHouse is out of the box really fast, but it doesn’t hurt to do a few things that will help it go even faster and begin to get you some of that legendary performance.

Four easy things you can do: add more CPU; be careful what you ask for, so limit your columns and rows; do some simple fixes on table design; and then, more complex, store data more efficiently on disk.

Adding CPU is really simple. ClickHouse has a property called max threads. In a new server, by default it’s half of your available hardware threads. If you see a query that’s not going fast enough, one simple way to goose it up is to bump up your max threads. This shows the SET command, doing it in a session with clickhouse-client. This graph shows the effect of different numbers of threads applied to the query. Bumping it up sometimes gives you a big jump, and then at some point you start to see things like merging and sorting, which can’t be parallelized as well, so the payoff for adding more threads tends to decline as you get to larger numbers. One other thing about max threads: as you increase the number of threads, it increases the amount of memory the query uses, so the trade-off is you might run out of memory. But it’s a simple way to goose things up, and sometimes it’s exactly the right thing to do.

Another really simple thing is to read fewer rows. Here’s an example using a WHERE clause to restrict things down. For reading a single table like this, or a table where you’re joining to some small things, the query performance is going to be dominated by the amount of data you actually read. We call it marks; I’ll show you what they are in a second. Here you can see the amount of marks, blocks of data we had to read, correlates directly with the time the query requires. It’s as simple as that: we’re scanning the table, so the less data you scan, the faster you go.

It’s useful at this point to understand a little more about what’s going on inside MergeTree. This is the final bit of plumbing that’s helpful to understand. I mentioned that ClickHouse tables have this sparse index. It’s a file called primary.idx. The reason it’s sparse is that it just has an entry for every 8,192 rows by default. ClickHouse will use this to locate rows if it can. But within the columns, we then have what are called marks, which correspond to granules of data. They roughly correspond to compressed blocks, but the compressed blocks actually have more than one granule in them typically. The point is that when you select something in ClickHouse, you go out and find the compressed blocks and then read them. One reason ClickHouse can’t read small amounts of data easily is that these granules include about 8,000 rows, so if you want one row, you’re going to get eight thousand. That’s also why we don’t use ClickHouse to run e-commerce sites. So one of the things that’s important is to try to make these compressed blocks as small as possible. That’s definitely something we can do, and there are a variety of ways to help make this more efficient. We’d also like to have fewer of these files lying around, so we want the parts to be as large as possible.

49:35 – Table Design, Codecs, and Where to Learn More

Robert: Let’s look at those two things. Table design is where I said we’d come back to it. We’ll look at partition values, primary key, and order by. PARTITION BY breaks the data up into parts. In this particular data set, if I partition by year, it turns out to be a number of years of data, and we end up with about 125 parts. This actually is less efficient than what I’d expect; these parts are kind of small, and we could probably make them larger. The point is that you have parts with 107 megabytes, and for a large data set you’d actually want parts far larger than these, so you want to partition by some value that’s going to make parts pretty beefy.

Second is the primary key. If you have columns you particularly filter on, it’s really good to have them in this primary key; that’s the carrier and the flight date. And finally, you order the columns, because this is your sorting order, and the first part of the order has to be a prefix of, or has to be, the primary key order. What you do is, the columns need to be ordered by the increasing cardinality, in other words the number of values. For example, we could go from a carrier, which has a small number of values, to flight date, which has a larger number, to departure time, which has an even larger number. So you order them from left to right. This is just a standard way of setting this up. Cardinality, if you’re not familiar with the term, just means how many unique values there are. That kind of table design will help make things more efficient.

Equally important, you can set the column compression level. This will make those granules we just talked about smaller. And there’s another great system table, system.columns, which shows the exact compression levels, so it’s really easy to find out how efficiently you’re storing the data just by running this query. You’ll be able to get this off the slides that get sent to you afterward.

There are two things we can do. We can change the compression. By default, ClickHouse comes with LZ4 compression, which is really quick to compress and decompress. You can also use ZSTD, which is a little more expensive to compress or decompress but generally gets smaller results than LZ4. You have to specifically ask for it. Here’s a value, tail number; this would take these bits, without knowing anything about what they mean, and reduce them to a smaller size. So changing compression is one thing we can do.

The other thing is adding a codec, or an encoding. An example of this is low cardinality. This appears to be a data type, or a function you call on the column value or column type. It turns a string into what’s called a dictionary encoding. Say you have a string like an airport code or tail number, where there are a few thousand of them but not a huge number. Instead of storing N862A, it sticks it in a dictionary with an integer value, which is smaller, and then we can apply something like ZSTD compression on it and get something pretty small. This is a very powerful technique. There are several encodings; low cardinality is for strings, and then there are several types of encodings for numeric data types, which are listed here. One thing to note is that with codec compression, when these codecs are applied, you may need to switch to ZSTD, because LZ4 sometimes doesn’t compress them well.

Here’s an example of how to do it. You just do an ALTER TABLE; it’s really easy and can be done instantly, these commands complete instantly. This one changes tail number to use low cardinality as a transformation on strings. This one has slightly different syntax, taking year and applying a double delta codec, just store the slope of the slope, which should be pretty efficient, and then compress it with ZSTD1. You have to tell ClickHouse that you’ve made these changes and you want them forced to be materialized; that’s the standard way of doing it in most ClickHouse versions.

The difference can be pretty significant, in fact, phenomenal in some cases. This is the effect of something I was running last night on the tail number. Uncompressed data in the table was about a gig. Compressed with LZ4, it’s about half a gig, 450 megs. If I apply low cardinality, the data that goes in is much smaller to begin with, and then I use LZ4 compression, which squeezes out even more, so I’ve reduced the data to about 40 percent of its original size. That’s pretty good. We’re basically talking a compression rate of about 80 percent on these strings. ClickHouse is not bad. There are other things we can do, but this gives you an idea of how we can do this effectively.

That’s all the tips and tricks for getting started on applications or for ClickHouse itself. Let’s talk about some information that may help you get started on applications. First, if you’re building analytic applications, there’s definitely some software you need to know about. I’ve listed standard event streaming, ELT, rendering and display, client libraries, and Kubernetes software. This is all open source, standard components we see again and again in applications, and if you’re not using them now, you may want to think about them. For example, on event streaming, I’ll pick one: Vectorized produces Redpanda. It is a Kafka-compatible event streaming system that’s very efficient; the name Vectorized comes from the fact that it uses vector processing on the data, and it really screams. For Kubernetes, Altinity provides the Altinity Operator for ClickHouse. If you’re using JavaScript inside Node, you probably want to look at one of the Node client libraries, and so on. The slides will have the links. One thing that’s important about ClickHouse is that, because it’s open source and a lot of people have worked on it, there are many choices for client libraries, and they are not equal. Some are things people did as a hobby. This list includes the most popular and most stable versions of all of them.

Second, where’s the documentation? The best source to start with is the ClickHouse official docs. They’re great, and as people always say, you can’t believe an open source project has this much documentation. It’s very complete. There are things not covered well, but by and large, you’ll find references to everything. To dig deeper, there’s the Altinity blog, our YouTube channel, and the knowledge base. For example, this class is going to go up on the YouTube channel, and the knowledge base is invaluable. That’s our support folks; we have a growing team, and as we run into problems or interesting things, we just pop them into knowledge base articles. It’s all out there, open source, you can grab it and try it out, and you can also submit your own. Then there’s a pile of meetups, other blogs, and external resources; just search for them, and you’ll find them.

Finally, where can I get help? Telegram is probably the number one place a lot of people still use. There are two huge Telegram channels, one in Russian with five or six thousand members and another in English with at least 2,000 members. The pop-ups we were seeing were actually coming from Telegram because I failed to turn the notifications off. Another great place is Slack: there’s the ClickHouse public workspace, where a lot of questions come up, and Altinity has a public workspace too, so come visit us, we’re happy to help and spend a lot of time on both. For education, there’s Altinity training for ClickHouse; we also have a ClickHouse 101 class that I highly recommend, which goes deep on everything you’d want to know to build from a small embedded application up to clusters with dozens or hundreds of nodes. And finally, we offer support for ClickHouse in all environments.

With that, we’ve reached the end. I want to thank you and wish you good luck. Hopefully, you’ll look like the guy shown on the right, only instead of surfing a big wave, you’ll be surfing big data. We hope this class has been helpful. I’ll stick around if you have questions, and I’m glad to answer them, but best of luck. This has been recorded, and we’ll send a link to the recording as well as the slides, so I hope you haven’t been frantically scribbling notes; you’ll have the slides to go along with them, and we’ll get them out within 24 hours. So with that, if you have any questions, feel free to pop them into the chat or the Q&A, and I’ll answer them.

FAQ

What is ClickHouse good for, and what should I not use it for? ClickHouse is a SQL data warehouse built to read large data sets and answer analytical questions very quickly, often in under a second, for a moderate number of users. It is not a replacement for MySQL on an e-commerce site because it lacks a full ACID transaction model and is slow on updates. It is also not Snowflake or Redis; it favors speed and portability over full SQL-standard coverage, and it reads large blocks rather than serving many tiny lookups.

How do I install ClickHouse and connect to it? You can install from a build repository, either community monthly or LTS builds or Altinity Stable builds, using apt or yum, run it in Docker, or use a managed cloud service. Installation is always two steps: get the software, then start the server. To connect, use clickhouse-client on the command line or the built-in Play web UI on the HTTP port 8123 with just a browser.

What is the MergeTree engine, and why does it merge parts? MergeTree is the workhorse engine for analytic data in ClickHouse. Each insert writes one or more parts, which can be small. In the background, ClickHouse merges smaller parts that share a partition key into larger ones, which reduces the number of files to read, gives longer runs for compression, and speeds up reads. Updates and deletes run as mutations that rewrite parts, which is why they are asynchronous.

What is the fastest way to load a lot of data? Use input formats to load raw data directly, such as CSV, JSON, and Parquet, rather than generating large numbers of INSERT statements, and insert large blocks rather than single rows. You can pipe a file through clickhouse-client, send it via HTTP POST, or select from the s3 table function, including parallel loads with many threads, which is very fast but uses more memory.

Why does the big table go on the left side of a Join? ClickHouse uses a hash join. It loads the right-side table into an in-memory hash table, then performs a parallel scan of the left-side table and looks up matches as it goes. The parallelism and compressed-read speed come from the left side, so the left side should be the big table, and the right side should be small enough to fit in memory. You can join multiple right-side tables as long as they fit.

How can I make ClickHouse queries faster? Start with simple self-defense: raise max threads to use more CPU, which also increases memory use, and read fewer rows by filtering, since query time tracks the amount of data scanned. Then improve table design by choosing a partition key that yields large parts, putting frequently filtered columns in the primary key, and ordering columns by increasing cardinality. Finally, shrink data on disk with compression such as LZ4 or ZSTD and codecs like LowCardinality and delta encodings.

© 2026 Altinity, Inc. All rights reserved. Altinity®, Altinity.Cloud®, and Altinity Stable® are registered trademarks of Altinity, Inc. ClickHouse® is a registered trademark of ClickHouse, Inc. Altinity is not affiliated with or associated with ClickHouse, Inc. Kubernetes, MySQL, and PostgreSQL are trademarks and property of their respective owners.