Introduction to High-Velocity Analytics Using ClickHouse® Arrays

Recorded: October 7, 2020
Presenter: Robert Hodges, CEO, Altinity

In this webinar, Altinity’s Robert Hodges and Mikhail Filimonov show how to integrate ClickHouse and Apache Kafka to turn fast-moving event streams into low-latency analytics. Robert frames Kafka as a distributed log of topics, partitions, brokers, and consumer groups, and ClickHouse as a fast columnar data warehouse, then walks through the core pattern in a live demo: a Kafka table engine that reads one or more topics, paired with a materialized view that streams rows into a MergeTree table. He also shows writing data back to Kafka and capturing message metadata with virtual columns.

Mikhail then covers the production internals, including librdkafka under the hood, sizing partitions and the background thread pool, why a single consumer per table is the best practice, and how batching produces at-least-once delivery. He closes with a roadmap toward exactly-once support, and the session ends with an extended Q&A on monitoring lag, SSL, schema registry, cluster consumer distribution, chaining materialized views, and replay.

Here are the slides:

Key Moments (Timestamps)

Key moments generated with AI assistance.

• 0:06 – Introduction and presenter bios
• 3:00 – What is ClickHouse?
• 5:45 – Arrays as a SQL extension
• 7:00 – Modeling key-value data with paired arrays
• 9:35 – Array join: syntax and function
• 13:45 – Group array and an airline data problem
• 18:52 – Array sort and lambdas
• 22:16 – Building a funnel with array filter
• 28:06 – Functional programming in ClickHouse
• 36:07 – Worked example: interpolating missing values
• 42:02 – Summary and references
• 46:07 – Q&A

Webinar Transcript

0:06 — Introduction and Presenter Bios

Robert Hodges: Hello everybody, this is Robert Hodges. I’ll be one of your panelists today in our webinar on an introduction to high-velocity analytics using ClickHouse arrays. This is a talk that we’ve wanted to present for a long time. Arrays are a great feature in ClickHouse, and we’ll tell you all about them over the next hour, and I hope to answer lots of questions.

Before we dive into the talk, I’d like to give you a few tips that will help you enjoy the webinar more. First of all, this webinar is being recorded right now, and we will post the link to the recording as well as the slides to everybody who signed up, so you don’t need to take frantic notes. This will come to an email pretty quickly. The second thing is that we have a Q&A box which you can fill out as the webinar is running. So if a question comes up, feel free to pop it into the question and answer box. If it’s convenient, we’ll answer it as part of the talk; otherwise we expect to have time at the end to answer questions. One final thing: we do a poll at the end of every Altinity webinar. It’s three simple questions, takes about 10 seconds to fill out. We just want to get some basic information to make sure the webinar is hitting the right level and to help us pick future topics. So with that, I’m going to dive in.

Some quick introductions. My name is Robert Hodges, I’m the Altinity CEO. I’ve been working on databases for about 37 years this year, and ClickHouse is number 20. I’ve had a few detours off into the fields of virtualization and security, but it’s really been data for the most part over the last three and a half decades. My colleague Alexander Zaitsev is joining me today. He’s the CTO of Altinity, he has decades of experience with petabyte-scale analytics systems, and was actually one of the first people outside of Yandex to use ClickHouse in commercial applications.

A little bit about Altinity, that Alexander helped found. We’re the number one enterprise ClickHouse provider, so we help make ClickHouse work for enterprises, particularly in the United States and the European Union, although we have customers worldwide at this point. As a company, we’re major committers as well as community sponsors for ClickHouse throughout our main regions, and we do lots of activities. These talks are one of the ways we help make it possible for people to learn how to use ClickHouse effectively and be more successful with it. So enough marketing, let’s dive into the talk.

3:00 — What Is ClickHouse?

Robert Hodges: For those of you that are new to ClickHouse, I’d like to do a very quick introduction to what ClickHouse is, so that you have a sense of what you’re getting into. ClickHouse is an open source SQL data warehouse, and it shares some of the best features of products like MySQL, a well-known relational database, and Vertica, which is also a well-known data warehouse. We’re not sharing any code, but we have similar features. Just like MySQL, we have a single binary, so there’s just a single program you start and you’re up and running, we’re open source, we run practically anywhere, and we’re relatively simple to operate. These characteristics have made MySQL incredibly popular, and ClickHouse is very similar in that sense.

On the other side, as we look at the analytic capabilities, we have a number of features similar to things you get in products like Vertica, Snowflake, or Redshift. Of course we speak the SQL language, which you get from the MySQL side of the house, and everybody knows SQL is a great way to process data. But more importantly we have things like a shared-nothing architecture, a very efficient architecture that couples compute and storage and then links the nodes together over the network. Probably the most important analytic feature is column storage, so the data is not stored by rows but instead as columns, long arrays of data which can be broken up into pieces and processed in parallel. We have vectorized query execution, which means applying multiple threads to the arrays using single instruction, multiple data, or SIMD, instructions to process the arrays in chunks as quickly as possible. And finally, very good parallelized execution across multiple nodes, so there’s built-in sharding and replication. This enables you to scale up from data sets that run on a laptop, a few billion rows or even hundreds of billions of rows, up to petabyte-scale data sets that run on 100 servers in a data center. And finally, ClickHouse is just really fast, and arrays are actually a part of that. We won’t talk too much about performance today, but you can refer to some of our other webinars as well as the countless talks people have done that discuss the speed.

5:45 — Arrays as a SQL Extension

Robert Hodges: So let’s turn from ClickHouse in general and have a look at arrays. Arrays are a SQL extension in ClickHouse. Most of the behavior we’ll be describing today, if you’ve not used ClickHouse before, is things that don’t really exist in most other products, and ClickHouse implements this in its own way.

There are a number of facets to arrays, and I’ll mention them here, and as we go through the talk we will dig into them. For example, you can look at arrays simply as a data type. This is an extension to tables that you can use to model certain types of data, basically anything that’s a vector. There are features that allow arrays to be used in joins, we’ll talk about that. There is a huge library of useful functions that operate on arrays. And perhaps most importantly for unlocking the power, arrays implement a functional programming model that makes heavy use of lambdas. We’re going to dig into that, because once you understand it, it opens up the door to performing all kinds of different interesting processing using these array functions.

7:00 — Modeling Key-Value Data with Paired Arrays

Robert Hodges: With that, I’d like to dive in, and as this talk proceeds we’re going to look at things through use cases with worked examples, so you can see the features of arrays in action as opposed to just presenting the syntax and letting you figure it out for yourself.

The first thing we want to do is use arrays in a way that is pretty common in many databases, which is that you have data which essentially is not a scalar value like 2.5 but instead a vector, so 2.5, 3.2, 6.5, and so on. Here’s an example. This comes up when you’re monitoring VMs. Let’s say you have a bunch of them running in Amazon and you’re collecting metric data. When you’re monitoring the VMs you have some fixed header information, like when did you do it, what’s the ID of your VM, what’s its type. But then, as you dig deeper, you’re going to have a lot of information represented as key-value pairs. For example, we have different types of metrics, with values for each in the metrics name and metrics value, and then tags. If you use Amazon tagging, which is standard for VMs, the tags have a wide range of possible names; in fact they’re anything you care to make up. So arrays allow you to model this in a straightforward way.

When we have key-value pairs, it’s very common to set up a table where you’ll have your header values as scalars, and then you’ll use paired arrays to represent first the name of the property you’re looking at, and then its value. For the metrics, which are basically numeric, we have a metrics name array, which gives us an array of the names, and then, in exactly the same order as the names, a bunch of floats that give the actual values. For the tags we do the same thing, the only difference being that in Amazon the tag value is a string. Other than that, we’re using standard ClickHouse syntax: we use the MergeTree engine, partition by date, and order by. These are all standard for setting up tables if you’re familiar with ClickHouse.

9:35 — Array Join: Syntax and Function

Robert Hodges: Let’s have a look at how this actually works. One of the first features of arrays you’ll run into is something called an array join. What the array join does is allow you to take those arrays, which are stored as full vectors in the column, and unroll them, turning them back into key-value pairs again that you can process in a tabular manner.

Here we’re selecting the header names, and we want to look at tags, so we’re going to take that tags array and use array join, so that it will turn into a name column and a value column. What this does is, for every value in the array or arrays, repeat the header information. So the first three columns are header information and they are repeated for each set of values in the array. The interesting thing about this is the arrays are all unrolled together, which is a very powerful feature. It turns the data into a table, and at that point we can do normal SQL things on it. For example, we could select all the machines where there’s a name, or look for machines that aren’t tagged, or join this information with other data and look for untagged machines, or machines belonging to particular groups like marketing. We now have an enormous amount of flexibility, because we can take these arrays and pop them into forms that are easier to deal with in conventional SQL.

This is a powerful feature we’re going to see again and again. Whenever you have a result that contains some scalar values plus some vectors, the array join allows you to unroll them into a nice tabular organization.

There’s a question here: isn’t array join similar to the explode operation from Hive SQL? I actually haven’t used that, but just from the name it sounds very similar, so the answer is probably yes. One thing important to note is that most databases I’ve looked at that have arrays have some feature like this that allows you to do this kind of interpolation.

Now, one really important thing about joining arrays is that ClickHouse actually has two ways to join them. There’s the ARRAY JOIN syntax that I just showed you, and then, somewhat confusingly, there is the arrayJoin function. This essentially does the same interpolation operation, but it has an interesting property you should be aware of. On the left side I show a very simple example where I select 1 and 2, and I’m going to do array join on this vector which has a and b, and it has pretty much the same effect as the ARRAY JOIN syntax. What’s interesting, though, is if you combine them, if you have more than one call to this function in the same select, you’ll actually end up doing a Cartesian join. Sometimes this is what you want, but a lot of times in arrays this is not what you want, so you need to be aware that the behavior is slightly different from the full ARRAY JOIN syntax. Anyway, you can use either one. We use array joins heavily in our processing and so do our customers.

13:45 — Group Array and an Airline Data Problem

Robert Hodges: That gives you an introduction to the basic array features. What I’d now like to do is dig into some of the behavior of arrays where they become quite different from features available in other databases. I’m going to talk about two functions, one called groupArray and another called arraySort. To queue this up, I’d like to offer a problem that will give us a nice opportunity to show how arrays can really work.

One of our favorite data sets is airline on-time data. We have about 175 million rows. This is commonly used as a pet data set to show ClickHouse capabilities. We’re going to take this data set and ask: what’s the highest number of flights per day for a single aircraft? The data set is arranged so that every time a commercial aircraft travels from point A to point B, there’s a record showing where it came from, where it went, time of departure, time of arrival, and so on. So what we want to know is, for a single plane, how many places do they fly in one day? This is a picture of a Southwest 737-700, and if you’re a Southwest customer you know they fly a lot of places. Let’s dig in and see how we find that out.

The first thing we want to do is figure out, for a given airplane, identified by the tail number field, the maximum number of hops we can find in a single day. Here’s a query that gets that. We don’t need arrays for this, because it’s just standard group by and order by behavior, but it tells us there is an aircraft with tail number N963WN that has seven hops, the most we can find on this particular day. So we have a sense that we’re going to see something with seven hops, but what we’d really like to see is the itinerary. For that we’re going to have to do a little more work.

Before we go into that, I’d like to introduce another major function that allows you to use arrays very easily within results, called groupArray. You can think of groupArray as a gateway between groups generated by the group by syntax in SQL and values that are outside the grouping columns. In SQL, if you don’t have window functions, which ClickHouse did not have at the time, it’s difficult to process the values outside the grouping columns or a column that has an aggregate on it. What groupArray does is allow you to pick columns, and the arrays will be populated by the column values found in the group. Moreover, all the arrays you mention will be populated in the same order. So in this case we’re grouping by a couple of columns, and we have a couple of arrays that take additional columns, and the values will end up in arrays. This allows you to take a normal SQL query with a group by and have it generate arrays we can then process further. This is a fundamental function you’ll use all over the place when processing arrays.

At this point we can start building up a list of stops. Here’s an example where I’ve added groupArray. We’re still selecting the carrier, the tail number, and the flight number for a particular day, doing our order by and group by, the same as before, calculating the number of hops, but we’re also using groupArray to pull in departure times. If we show the results, now instead of just seeing the hops we can see a bunch of departure times, with all seven records folded into this array. This is not super useful yet for understanding the itinerary, because the departure times aren’t sorted, and we can’t see where the plane went. So we’re going to have to extend that, basically sort them and add more data, so that we have something meaningful to look at.

18:52 — Array Sort and Lambdas

Robert Hodges: What I’d like to do now is introduce another very useful array function, which is called arraySort. By default you can stick an array in it and it’ll just sort it in ascending order. Here’s an example: we take some departure times, sort them, and now they’re neatly ordered.

Another really interesting thing you can do is add a lambda to this function. A lambda is a functional expression. I show two examples. The first lambda, in arraySort, is just going to reverse the order. It takes each item coming from the array, gets put in as x, and we turn it into minus x, so we’re flipping the value, which causes the arraySort to be in reverse order. Another really cool thing about lambdas is they can process multiple values. Within any function that supports these lambdas, you can have additional parameters coming into the lambda, and then add more arrays. The second example adds a second set of keys, and this says we’ve got the array values here, the departures, but here’s a set of keys to tell us how to sort them. So with this lambda it opens up the possibility to play all kinds of games with what arrays do.

Let’s go ahead and apply that. Now what we’re doing is coordinating the arraySort. We have the groupArray pulling the departure times, and we’re feeding those into a call to arraySort where we’re going to take the origins and sort them in order of departure time. If you’ve used arrays before, you can start to see where this is going. We’ve got the departures neatly lined up and the origins matching them in a separate array. We can build this out a little. Here’s where we build it out fully: we don’t just have the departures and origins, we have the arrival times and the destinations. We complete the query, and then we put it in a subquery and use array join to roll it back out into a nice tabular form. So we get a nice tabular form which shows that first flight we identified, this N963WN, with the exact departure times, arrival times, origins, and destinations, and we can see all the places this plane went, which was quite a few. So this is a great example of how arrays can allow you to solve this problem.

22:16 — Building a Funnel with Array Filter

Robert Hodges: Let’s do something slightly more complicated. Let’s try to build a funnel. A funnel is a very specific type of processing widely used in data warehouses. I’m going to show a simple example from airlines, since I have this data set. I’m going to ask three questions: how many planes are flying each day in a given month, how many of those fly to Chicago Midway, and then how many of those that went to Midway went to Atlanta on the same day. So you’re getting all the flights, then a subset, and then a subset of the subset. This technique was described in a wonderful talk by Mariya Mansurova, also listed in the references. She explains how to do this for a different type of funnel problem that we’ll look at at the end.

We’re going to start with a base query, stripping out some information we had previously, and just look at arrivals and destinations, because we want to know where people are going. We use arraySort and groupArray just as we showed before, and then we can unroll it and get this tabular form. Now we need to add some additional conditions to create the funnel, and to do that we’re going to use a function called arrayFilter. What arrayFilter allows you to do is pass over a set of arrays and apply a condition, and what’s returned is all the values from the array that meet that condition. Here’s a simple example: we’ve got an array with some small values, a, b, c, d, e, f, and g. Let’s filter it to get all the values where the length is greater than one. arrayFilter does that, with a lambda that says, for every value v in the array, return it if the length is greater than one. Another interesting thing is we can combine values, for example an arrayFilter that only returns the values when the tag is equal to name. There’s also a trick where, when you filter something, you can get the first value that passes the filter, that one in square brackets at the end, so that way we find somebody called Joe who is the first person that meets this condition.

With arrayFilter we can now add some additional information. We’re going to use arrayFilter and look through our destination array as well as our arrivals. First we want to find: is one of these destinations MDW? That’s the first thing, did they fly to Midway? The second thing is, if they flew to Midway, do we see something after it with a later arrival where they went to Atlanta? So this is saying, hey, they went to Midway, and then we’re looking for a later arrival for the same plane, same tail number, that arrived in Atlanta. This allows us to analyze whether the plane went to one or both of these locations.

At this point we can take that query, which processes each flight for each plane for each day, and use it as a subquery. Then we select off it, group by day, and look for the count of total flights on that day, then ask whether they went to Midway, that’s our Midway total, and whether they went to Atlanta after that, that’s our Atlanta total. So now you see exactly how many flights there were, how many went to Midway, and how many then went to Atlanta. This is a very powerful form of analysis widely used in analytic applications. We constructed what I would call a flight funnel, but most people on this call are probably familiar with sales or marketing funnels. This is actually an example of the funnel we use at Altinity: we have contacts, leads, marketing qualified leads, sales qualified leads, and customers. We can analyze how they move from one step to the next using exactly the same logic I just showed for the flight data. So this is a really powerful technique, and again something you can do fairly easily with arrays.

28:06 — Functional Programming in ClickHouse

Robert Hodges: At this point we want to go even deeper into array functionality, but what I’d like to do now is take a little break and talk about functional programming in ClickHouse. For that I’d like to invite Alexander Zaitsev to come on board and tell us how it works.

Alexander Zaitsev: Thank you, Robert. Hi everybody. I will speak for the next five or ten minutes about ClickHouse functional programming. ClickHouse is often considered a database for developers, for a variety of reasons, and one of them is the very rich functionality for functional programming, and this is all built on top of arrays. If you’re familiar with functional programming, you know that it typically manipulates lists. Lisp, one of the first functional programming languages, is all built on top of lists, and in ClickHouse we have arrays. There is a classic set of functional programming functions also supported in ClickHouse: arrayFilter is supported, arrayMap is supported, and because ClickHouse has a slightly limited implementation of reduce, which is called arrayReduce. From a functional programming perspective, those are higher-order functions, because they accept another function as an argument. You already saw some examples when we used lambdas for filtering and for some other operations, but this is a generic approach. There are many functions like that. It’s not just filter, map, and reduce. There is also arrayExists, which accepts a lambda, there is arraySort that may accept a custom lambda to provide an order that is not the default, and even arraySum, which can sum your array elements, may also accept a lambda in order to do some modifications before you sum your data. It’s interesting that ClickHouse can accept multiple arrays and use them as a tuple in a lambda function.

Let’s look at a couple more examples. Probably the simplest use case of the map function is to do certain transformations from one data type or one column to another column. In this case, for example, we calculate a substring of an array of strings, something of the length of four. This is very basic. We can also provide the length of the substring as a separate parameter, as you can see in the bottom example, where we have two arrays, one array of strings and one array of string lengths, and we apply this using arrayMap to calculate a variable length for every array element. This is a very powerful function.

ClickHouse has many different functions that help you manipulate arrays, and sometimes they accept lambdas and sometimes not. If you look at the ClickHouse documentation, or even the system.functions system table, all the functions that start with “array,” you’ll find probably 50 or 60 different functions that do quite interesting stuff. I have a couple of examples here. You can reverse an array, you can sort an array, you can use arrayEnumerate to generate your indexes, which may look weird but can be useful for certain calculations. You can do some searches, so indexOf will help us find the first element of an array that matches a condition.

Next, we will look into the weighted sum, how we can calculate the weighted sum of array elements. You can do it with arrayMap and arraySum, as in the top example, where first we multiply two vectors using arrayMap and then sum the vector together. But as I mentioned, arraySum also allows us to use a lambda, and if we use a lambda expression in arraySum, we can calculate it in a single function call. So in one call we already calculate the weighted sum, which is very concise and very effective.

The next example is the reduce function. For a couple of years it was undocumented at all, which was kind of weird. It existed, but it was added into the documentation probably a couple of months ago at the earliest. What it does is allow you to apply an aggregation function to an array. In this example we can calculate the sum of array elements using the arraySum function, but at the same time we can use arrayReduce and apply some function to the array elements. It’s not a precise equivalent of the reduce function in functional programming, because it doesn’t allow you to specify an initial element, for example, and it doesn’t allow you to specify a custom function, which is something we are going to add. But even with this functionality that allows you to provide any aggregation function and apply it to an array, and ClickHouse has several dozen different aggregation functions, even in this form it is very convenient, and also very efficient.

So apparently arrayReduce is vectorized and it can execute in parallel. In this example I can calculate the sum of 10 or 100 billion elements with the arraySum function and it takes a bit less than a second, but if I use arrayReduce for the same exact purpose, the query time is reduced three times. I also played with some other examples that are not shown here, but in some cases arrayReduce may provide even 10 times or 100 times better performance, thanks to better parallelization on all the layers.

36:07 — Worked Example: Interpolating Missing Values

Robert Hodges: This is quite an interesting example, I wonder if you can take it. Thank you so much, Alexander. With what we’ve shown you so far, you know a lot about how arrays work, and in fact if you’ve done functional programming and you’re just new to this, it’s probably starting to dawn on you that you can do a huge amount of stuff here. So, in the spirit of not knowing when to quit when we’re ahead, we’re going to work an example that uses some of these capabilities to do interpolation of data values.

I’ve generated some very simple test data here. Let’s say these are temperature readings being taken every hour, and what we see in this handy bar graph is that we’re missing values. There are about five of them which are zero. That doesn’t mean there was no temperature at that time, it just means the sensor didn’t get it and we don’t have a report. So what we’d like to do is fix this, and the obvious thing is to do linear interpolation from the value before and the value ahead, and assert a value that will at least give us a sense of what the temperature profile looked like.

This is where arrays get interesting. This particular query, I can tell you from personal experience, took me about an hour and a half to set up. As a programmer or coder, you start simple and then just add more and more stuff. Let’s go through what this is doing. We’re using groupArray, as we discussed before, to group by day for this particular sensor, and it turns out there’s only one sensor so I don’t need to add additional things to group by. We’re going to pull the readings for the day into a datetime array, pull the temperatures into an array called temp_a, get the length of that array, and it turns out we’re going to need to reverse the temperature array, because we’re going to have to search it two ways, going up and going down, to find the nearest values to our missing values.

The next thing we’re going to do is it’s helpful to have a list of the indexes to the array, so we’ll use this handy function called arrayEnumerate, which returns a list of the indexes 1, 2, 3, 4, and so on. Now we need to find the higher value and the lower value whenever we have something missing, so we’re going to use arrayMap for that. The first one is fairly easy: we’re going to find the first value above the one we’re looking at that is non-zero. So that’s, for each temp_a, find the first k such that the index value is greater than the i we’re reading in, and temp_a at s is greater than zero. A very simple condition, and lambdas are great for this kind of thing. The next one is harder, because ClickHouse has functions to search arrays forwards but not backwards. So what I had to do was reverse the array, as I did above, then use the same indexes but invert the index values. Believe me, it’s easier to say this than it was to do it, but it gives you a sense that you can put practically any legal SQL expression into the lambda, so you can do very complex processing. Then, once we’ve got the upper and lower values, we create an array that is a map from the original temperatures in temp_a to temp_a_fixed. That walks the temperatures, and if the temperature at each point is greater than zero we just take it, but if it’s zero or less we do an interpolation operation, basically subtracting the difference in the values and dividing by the size of the gap. So it’s a very complex query, but it actually works pretty well, and when we stick it back into the array join, we get the fixed values, nicely interpolated, and we now have a full temperature profile. This is a simple example of the possibilities that open up when you use the full power of arrays, including the functional programming capabilities.

42:02 — Summary and References

Robert Hodges: At this point we’re almost done with the talk, so I’d like to summarize a little, and then we’ll spend some time answering questions. Just some wrap-up of the concepts we applied. Like many databases, ClickHouse can use arrays to model any place where you need to store a vector inside a row, and in fact, because arrays can be key-value pairs, you can effectively model matrices. The array join is really important because it allows you to unroll the arrays into rows, so a lot of times you’ll do operations on arrays and then use array join to put them back in a table. The groupArray is the gateway that takes grouped data and pushes it into arrays so that you can begin to do window-like processing on it. That’s helped by the fact that you have this huge library of array functions, and because they support this functional programming model with lambdas and the ability to have multiple arguments, there’s a huge universe of problems you can solve. One thing I want to be clear about is we’ve just touched on what arrays can do. As Alexander said, there are 50 or 60 array functions, and with lambdas and the full SQL expression capabilities, there’s an enormous amount you can do.

If you want to find out about those, here are three suggestions. First, the ClickHouse documentation in general; arrays are well documented, with a couple of exceptions Alexander noted. There is a wonderful talk I already referenced by Mariya Mansurova at Yandex, where she walks through the example of a marketing funnel on a website, the canonical example of funnel analysis. Her talk is from 2017 but it’s still valid today. And finally, we have a very interesting article on the Altinity blog. As some of you may be aware, we’ve been doing some comparisons with Redshift, with the help of one of our customers who’s also interested in this. One of the ways you can really unleash ClickHouse performance is by using arrays, so we have examples in that article showing cases where ClickHouse, on the same data, just by using arrays, can take shortcuts that allow it to process things 100 times faster than Redshift. So arrays, in addition to being very versatile and allowing you to solve your problems, are also a way to get very high performance in data processing.

So with that, I want to say thank you. We’ll continue to answer questions. I’m going to launch the poll, so if you don’t mind filling that out. While you’re thinking and filling out the poll, we are hiring, so Altinity is building, and here are some references. If you have questions, feel free to email me. The ClickHouse code is located on GitHub at ClickHouse/ClickHouse, and there’s the clickhouse.tech documentation. I just want to put in one more little plug: we have a cloud platform we just officially announced last week, so if you’re in the cloud, particularly Amazon, and want somebody to run ClickHouse for you, we’re at your service.

46:07 — Q&A

Robert Hodges: With that, I’d like to open it up for questions. Alexander, do you have any questions you’d like to answer live?

Alexander Zaitsev: I have answered a couple already. I just want to add that in this talk we mostly covered how you work inside the arrays. We converted data into arrays in these calculations, but it’s worth mentioning that you can also manipulate arrays, and for that ClickHouse has special combinators like ForEach and Array. We didn’t discuss them, but I mention it for the complete picture. Also, ClickHouse has some very interesting functions that work on arrays but provide special behavior. For example, we were talking about funnels, but ClickHouse has a windowFunnel function that does some processing and can calculate funnels as a result. Similarly, for retention, ClickHouse has a retention function that also produces arrays as a result and allows you to do some more complicated analytics. We didn’t discuss those. In this talk we focused solely on how to use arrays and the functionality of arrays themselves, but probably in some of our next webinars we can talk about some advanced ClickHouse functions that also use arrays but bring their own logic and behavior.

Robert Hodges: Alexander, we have a couple of questions coming up. The first one: where is the airline data located? I know we have our own private copies of it, but I think it’s documented on the clickhouse.tech site as well.

Alexander Zaitsev: Yes, if you go to, I think it’s part of the quick start or something, the airline data literally takes probably 10 minutes to set up, including downloading from the internet. It’s a public data set, easily available.

Robert Hodges: That’s a great one. Let me take this next one. Is it possible to present the array element vertically and not horizontally? I want to clarify the question you’re asking. I’ll interpret it the way I think you’re asking. It sounds like what you might like to do is take the values in the array and flip them so that the value becomes a column heading, and you just see them as a bunch of values. This is called pivoting, and ClickHouse does not do full pivoting. The only way I know how to do it right now is you actually pull the data back, do the query, and pull it into something like pandas, which can do a full pivot to turn rows into columns. So if that’s what you’re asking, that’s not something directly supported. As far as presenting things vertically, we did show you the array join, which is essentially where ClickHouse takes the arrays and explodes them out based on the array values, matches them with the headers from whatever your group by columns are, and presents that in a tabular format. ClickHouse does that very well and very quickly.

Robert Hodges: Alexander, here’s a question: suppose I have three arrays x, y, z and I want to return the x values on a condition, and the condition is y greater than five and z greater than nine. How do I combine these two conditions into one?

Alexander Zaitsev: Yeah, I will probably write an example right now. It’s very simple. Here we go.

Robert Hodges: Great. Let’s go back over a few of the existing questions. If you want to type more, feel free and we will. Here’s one I think is an important point: why does groupArray group data in a random manner? Alexander has the answer: the order is non-deterministic, as for all aggregate functions. That’s a really important point. With ClickHouse you can never count on the values coming back in order, even if the part is sorted, because ClickHouse might be reading multiple parts when it returns your array, so you just don’t know what’s happening under there. But one thing that is important to understand is that if you have multiple groupArrays, they will feed the values into the array in the same order for all arrays. So overall the ordering is non-deterministic, but whatever order it picks, it will use the same row order for all of the array values. That’s why we can do these tricks where we use arraySort and use one of the arrays as a key, because all the other arrays are ordered the same way. That’s a really important point.

Robert Hodges: We have a couple more questions. Is ClickHouse array join doing the join then aggregating, or is it aggregating while doing the array join? The latter would have better memory properties. Alexander, do you want to take this one?

Alexander Zaitsev: I think it depends on the query plan. It really depends on the query plan. ClickHouse tries to be memory efficient, but I don’t have a generic answer on that. We can consider that ClickHouse tries to aggregate first and then do any joins, as it does in other cases, unless you explicitly use some columns returned by the array join in your filter conditions. It’s as simple as this.

Robert Hodges: Great. Let’s see if we have any further questions. I think we’re running out, and we’re coming close to the top of the hour. If there are any further questions, please submit them, we’ll stay for another minute. Just looking through the questions you already answered: array of arrays possible as a column type? We support that. And we talked about arrayFilter with multiple conditions. I think if you understand the lambdas, and the way they work and assign the arguments into the lambda, you’re basically ninety percent of the way to understanding what you can do with arrays. Everything else follows from that.

Robert Hodges: I think that is it. So at this point I’d first of all like to thank Alexander Zaitsev for participating in this presentation today. Most of the examples come from things he’s been working on, so thank you very much, Alexander. It’s a pleasure to present with you.

Alexander Zaitsev: Thank you.

Robert Hodges: And I’d like to thank everybody on the call. I hope this was useful. If you have more questions, don’t hesitate to contact us, read our blog, check out the Telegram channels and the community Slack channel. All these sources are places you can go to ask questions about arrays and find out about them, as well as more ClickHouse features. So with that, we’ll call it for today. Thank you very much everybody, have a great day, talk to you soon.

FAQ

What are arrays in ClickHouse and when should you use them? Arrays are a SQL extension in ClickHouse for storing a vector of values inside a single row. They are useful for modeling anything naturally shaped as a vector, including key-value pairs, where one array holds property names and a parallel array holds the values at the same index. Beyond modeling, arrays come with a large function library and a functional programming model, and they can also unlock major performance gains for vector-style computation.

What does ARRAY JOIN do, and how is it different from the arrayJoin function? ARRAY JOIN unrolls an array stored in a column into rows, repeating the row’s scalar (header) values for each array element and unrolling multiple arrays together, which turns array data back into a normal table you can query with standard SQL. The arrayJoin function performs the same interpolation, but with an important difference: if you call the function more than once in the same SELECT, you get a Cartesian join, which is often not what you want.

What is groupArray used for? groupArray is the bridge between a GROUP BY query and the values outside the grouping columns. It collects column values within each group into an array, and when you list several groupArray calls, they all populate their arrays in the same row order. That shared ordering is what lets you sort one array by another and perform window-like processing, which is valuable in SQL dialects or versions lacking native window functions.

How do lambdas work with array functions? Many array functions are higher-order functions that accept a lambda, a functional expression applied to each element. arraySort, arrayMap, arrayFilter, arraySum, and others can take a lambda, and the lambda can receive multiple parameters so you can pass several arrays at once, for example sorting one array by the values of another. Because a lambda can contain almost any valid SQL expression, this opens up a very wide range of processing, from filtering and transformation to complex tasks like interpolation.

How can arrays be used for funnel analysis? You can build a funnel by collecting each entity’s events into sorted arrays with groupArray and arraySort, then using arrayFilter to test ordered conditions, such as whether a flight reached Midway and then later reached Atlanta the same day. Wrapping that in a subquery and counting per day yields the classic funnel counts. The same pattern applies to sales and marketing funnels. ClickHouse also offers a dedicated windowFunnel function as a more efficient alternative for many cases.

Why is the order of values returned by groupArray non-deterministic? Like all aggregate functions in ClickHouse, groupArray does not guarantee a particular output order, partly because ClickHouse may read multiple parts in parallel when building the result. The key guarantee is consistency across arrays: if you populate several arrays in the same query, they all use the same row order. That is why you can reliably sort one array and treat another as its companion, then sort or reorder them together with arraySort and a lambda.

© 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.