Visualizing Real-Time Data Using ClickHouse® and Superset

Recorded: June 8, 2021 | 10 am PT
Presenters: Srini Kadamati – Developer Advocate, Superset, and Robert Hodges – CEO, Altinity

This co-presented webinar pairs Altinity’s Robert Hodges with Preset’s Srini Kadamati to show how to visualize real-time data using ClickHouse and Apache Superset. Srini introduces Superset as a modern open-source BI platform that works with almost any SQL engine thanks to its bet on SQLAlchemy, offering a browser-based SQL IDE, a semantic layer, and a no-code chart builder. Robert introduces ClickHouse as an open-source SQL data warehouse that is accessible to developers, runs almost anywhere, and answers queries on billions of rows in milliseconds.

The heart of the talk is a live walkthrough of connecting the two. Robert explains the SQLAlchemy driver options, recommends the actively maintained clickhouse-sqlalchemy driver with TLS support, and demonstrates connecting Superset to ClickHouse. He then builds a time series chart from a physical dataset with no SQL and a richer geographic map and word cloud from a virtual dataset that joins flight data with airport coordinates, showing the well-formed ClickHouse SQL that Superset generates underneath.

The session closes on collaboration and roadmap, noting that everything shown works in open source and that both teams are improving the driver and connecting their managed services. A detailed Q&A covers Superset CSV export limits, how ClickHouse compares to Druid, how ClickHouse stores data in MergeTree parts with a sparse primary key index, instant queryability through materialized views, and how Superset compares to Grafana.

Here are the slides:

Key Moments (Timestamps)

Key moments generated with AI assistance.

• 0:02 – Introduction and presenter bios
• 1:43 – Why data visualization matters
• 3:46 – What Apache Superset is
• 7:09 – Superset’s no-code chart builder
• 8:19 – ClickHouse as a new option for analytics
• 14:20 – Connecting Superset to ClickHouse with SQLAlchemy
• 17:04 – Setting up Superset and connection strings
• 20:03 – How Superset organizes data
• 21:28 – Creating a time series chart from a physical dataset
• 26:24 – Virtual datasets, joins, and a map chart
• 33:44 – Word clouds, dashboards, caching, and roadmap
• 40:45 – Q&A: CSV limits, Druid, MergeTree, and Grafana

Webinar Transcript

0:02 — Introduction and Presenter Bios

Srini Kadamati: There we go. All right, thanks everyone for coming. Today we’ll be talking about ClickHouse and Superset, and there’s a fun collaboration story there as well around getting the driver to play nicely. Just a month ago we gave a talk together at Percona Live on a similar, overlapping set of topics, so thanks Robert for being here, and I’ll let you take it away.

Robert Hodges: Great, thanks Srini. It’s great to be back and doing another talk together. As Srini said, we’ll be talking about visualizing real-time data using ClickHouse and Superset. Let’s do a little bit of intro here. I am CEO at Altinity. We are a company that supports ClickHouse for enterprises. We do everything from running it in the cloud to training to engineering features, actually a lot of similarities to the work that Preset does. What’s more important for this talk is that I’m a database geek. I’ve been working on databases since 1983. I had some detours into things like virtualization and security, and ClickHouse is database number 20.

Srini Kadamati: Quick intro here. I’m Srini Kadamati. I live in Cambridge, Massachusetts. I have backgrounds in data science and statistics, and I have a big love for visualization. My background is also in data science education, so I’m here to help people learn how to be effective with data.

Robert Hodges: On my side, introducing ClickHouse: it’s an open source SQL data warehouse. I like to describe it as the first one that can play with the big kids. We’ll talk a little more about it further on in the talk.

1:43 — Why Data Visualization Matters

Srini Kadamati: I’ll provide a quick overview of why we’re here, talk about Superset for people who are new to it, and then Robert will talk about ClickHouse. The main thing I want people to take away is that the main purpose of data science really is insight. Nowadays you hear about large amounts of data or doing cool things with machine learning, and those things are valid, but ultimately most businesses do need insights from what’s going on in their business as well as the rest of the world. So I always come back to insight as being the main driver of data efforts, and I think that’s how organizations should be thinking of things. Superset is a natural tool to help there.

Superset is a BI tool, and effective data visualization is one of the best ways to get insight from a large, complex amount of data. Here are some fun examples I put together, spanning everything from Star Wars to video game sales. I’m in Massachusetts myself, so I’m always looking at my own internal dashboard of our total vaccinations here, since COVID is thankfully winding down in the United States and the rest of the world.

3:46 — What Is Apache Superset?

Srini Kadamati: Superset is a modern open source BI platform. It was originally created by Maxime Beauchemin, who was also the original creator of Apache Airflow, and he also created Apache Superset at Airbnb. He just can’t stop open sourcing cool data tools. The really cool thing about Superset is that it pretty much works with any SQL-speaking data engine, and that’s thankfully due to the bet the community made early on on SQLAlchemy as an important platform, plus a huge diversity of charts that are also extendable.

The biggest thing Superset offers in the open source BI space is just how extensible it is, in terms of both the simpler things like supporting databases and supporting a large variety of chart types, but also how it can be used and embedded in many different contexts. We’ve even spoken to companies and projects building entire businesses on this BI platform, which is super exciting.

As I mentioned, SQLAlchemy and the Python DB API 2.0 are two important bets the community has made. This lets Superset work with a wide variety of databases, and it’s one of the reasons why, when the ClickHouse community and the Superset community were trying to figure out how to work together and make sure the integration was working well, having a shared set of standards really made sure that process was smooth. A lot of tools end up building native connectors, and there are pros and cons to that approach, but in terms of speed and letting Superset work with every new database that’s coming out, shared standards are super critical.

There are two main workflows in Superset. You have SQL Lab, which is a state-of-the-art browser-based SQL IDE. This is really focused on writing analytical queries, so more read-only focused, as is common in a lot of BI tools, exploring data, sculpting it, and preparing it for visualization, then saving that data as virtual datasets, database views, or database tables. This is really where people who are savvy with SQL and want to take things to the next level are empowered to reshape their data, and this IDE for SQL keeps getting better.

After you reshape and re-sculpt your data for visualization, Superset has a semantic layer where you can publish virtual datasets. So if you need to save it at the semantic layer instead of pushing it down to your database and saving it as a view, you can do that, which is a common thing a lot of BI tools have. In the coming months there are a lot of exciting developments to make the semantic layer even more powerful. This is something that’s really unique to Superset in the open source BI landscape.

7:09 — Superset’s No-Code Chart Builder

Srini Kadamati: Once you have your data prepped, you can use Superset’s Explore view. This is a no-code chart builder, and again, betting on open standards, SQLAlchemy was a really important enabler of this functionality. There are entire companies and projects just focused on no-code chart building and no-code query building, and leveraging what’s already been done in the SQLAlchemy community and all the dialects means Superset is able to offer a really powerful front end in this Explore view, to let everyone in an organization create charts without even needing to code.

To end the Superset part of this, here’s a fun example of a visualization. This is a slightly out-of-date screenshot, but it’s the Slack community dashboard that we use at Preset to understand the Superset Slack community. This dashboard is something I built in about 10 minutes, and that showcases the power and how attractive the charts are in Superset.

Robert Hodges: I’ll jump in. Thank you so much, Srini. We’re assuming that most people on this call have some acquaintance with Superset, so we’ve gone over that pretty quickly. What we’ll do now is spend most of the rest of the talk on ClickHouse and how to integrate it.

8:19 — ClickHouse: A New Option for Analytics

Robert Hodges: First I’d like to talk a little about ClickHouse, because I assume there are a few people on this call who have never heard of it, and perhaps even more who have not used it. ClickHouse is a new option for building analytic services. By an analytic service, I just mean something you can present to your users to get answers from large pools of data. One way to look at it is that it’s an industrial-strength data warehouse. It’s also open sourced under the Apache 2.0 license, just like Superset.

There are a number of important characteristics, but if you had to pick three, one is that it’s incredibly accessible for developers. Like Superset, it’s something you can spin up by yourself. There are cloud versions of course, but it’ll install on a laptop in 60 seconds, so you can pull it down, either install it on Ubuntu on a Linux VM or run it through Docker. The other thing is that it runs pretty much anywhere Linux does. We have examples of it running on everything from an Android phone to clusters that contain hundreds of nodes. It runs great in containers, on bare metal, and in the cloud, so you have a lot of choices about where you can deploy it, again very similar to Superset.

Finally, and this is the thing that really grabs people by the collar when they first use it, it’s incredibly fast. You can get answers to queries literally in milliseconds on data sets that contain billions or trillions of rows. In some cases this enables entirely new applications to be built, because you can answer questions well within the time it takes to display a web page. That means you can be looking at these pools of data and making decisions about what you show people on the fly.

Digging into it a little more, some of the specific features that make it powerful: first, it’s very simple. The basic distribution is just a C++ binary. I sometimes like to say it’s as if MySQL and Vertica, another popular data warehouse, pretend they had a kid. From the MySQL side, it has the same ease of installation and a single statically linked binary that will run anywhere Linux does. Second, it has an advanced SQL implementation. Like most analytic databases, it’s very focused on select. Things like updates and deletes are not so prominent.

It uses column storage, which is tremendously important for large data sets because it enables high compression. Not only do we have multiple ways to compress data, but we also have things called codecs, which allow you to apply transformations that basically take the air out of your data and make a much smaller thing to compress, so you can sometimes get compression levels on the order of hundreds of times the original data size. It’s very good at distributing processing both across nodes and using all the cores locally. It also uses vectorization, which is the ability to split things into pieces and process them as arrays using SIMD instructions, single instruction multiple data. That means if you’re summing numbers, you can do four or eight at a time in a single clock cycle, as opposed to pulling everything into registers one by one. For sharding, replication, and scaling, you run it on a laptop, as I said, starting in 60 seconds, but then you can take that system and expand it out with the same code and essentially the same operations out to hundreds of nodes. And it’s Apache 2.0, and given the way the community is structured, it’s unlikely that will ever change.

It’s become very popular. I show a few logos of just some of the thousands of companies currently using it. One that’s particularly prominent, and will come up again in this talk, is Cloudflare. They were one of the early adopters in the United States, and this quote is something that John Graham-Cumming, their CTO, posted about a year ago on Hacker News. Basically all of the analytics they serve up to their customers are driven off ClickHouse.

One more thing, a little about the community. Like Srini and the Preset folks, we’re really big into open source. We’re riding on top of this huge community that evolved from what was originally an in-house project at Yandex. That’s how ClickHouse was developed. They had a web analytics project, were looking for a database or data warehouse that could serve data quickly for it, couldn’t find one, so they wrote one. They open sourced it in 2016 and it has grown very rapidly since then. These are some common metrics. One that’s particularly interesting is the unique GitHub project contributors each year, in other words the people who have filed a PR against the ClickHouse code base. We’ve grown really rapidly, and my numbers run up to April 20th of this year, where we were exactly equal to the number of contributors on Elasticsearch. So this community has grown very rapidly and is now, by some measures, the same size as Elasticsearch.

14:20 — Connecting Superset to ClickHouse with SQLAlchemy

Robert Hodges: That’s a little about ClickHouse and why you should care. What I’d like to do now is talk about how we actually connect it up. As Srini mentioned, ClickHouse has made this commitment to SQLAlchemy, so what you’re going to need to do is install a SQLAlchemy driver. The basic architecture is shown here. We’re going to be working with the clickhouse-sqlalchemy driver, which is also written in Python and uses something underneath called clickhouse-driver. That driver is the thing that actually takes care of the network connectivity and speaking the wire protocol. It will then talk to ClickHouse.

ClickHouse, just so you know, has a couple of ports it uses for this particular protocol. This is called the TCP native protocol. Port 9000 is for communications in the clear, and 9440 is for TLS encrypted communications.

This is a good time to dig in a little to the drivers, because there are actually two of them. The driver originally used with Superset most commonly was developed at Cloudflare by Marek Vavruša, and he contributed this probably back in 2017 or so. It’s called sqlalchemy-clickhouse, and this is the one documented on the site. I think that’s going to change, or the other driver will be documented soon. Things to note about it: it uses the HTTP interface, which is a different interface you can use to talk to ClickHouse. It doesn’t matter a lot, but a couple of key things are that it doesn’t have TLS support, which is important, and it also has a lot less maintenance. The last PyPI release was in 2018, so you basically have to go get the code yourself if you want anything newer.

What we recommend at this point is to use a newer driver called clickhouse-sqlalchemy. We chose these names to confuse our enemies. This one is also Apache 2.0, developed by a fellow called Konstantin Lebedev. It has TLS support, and it has a bunch of bug fixes that we put in with Srini and Max and the help of other people in the Superset community, and it’s very actively maintained. The last release, when we put these slides together, was March 15th, and there may even have been one since then. So I give that one two thumbs up. This is the one where, if you’re starting out on Superset, I would highly recommend using this, because it does address a number of problems people have had in the past.

17:04 — Setting Up Superset and Connection Strings

Robert Hodges: Setting it up is totally easy. This is an example of the commands I run when I’m on my Ubuntu laptop, when I’m doing real work as opposed to manager work, which I do on Windows. You just follow the install instructions, which are documented on the Superset site. Like every sane person, I do this in a virtual environment. I don’t show those commands, I figure you know that if you’re familiar with Python. So this sets up Superset, and then before we get too far in and kick it off, we want to do a pip install to get clickhouse-sqlalchemy. You want the latest version, which is greater than or equal to 0.1.6, that has the bug fixes I mentioned. Then you start Superset and you’re off and running. At that point you can connect from Superset through the UI to ClickHouse.

First I’m going to talk about connection strings. SQLAlchemy, like all self-respecting connectivity APIs, has a URL format you’re probably familiar with if you’ve connected with other databases. This is the format for the clickhouse-sqlalchemy driver. It’s clickhouse+native, that’s the scheme to make sure you get the right driver. Here are a couple of examples you can use. If you’re just running ClickHouse on your laptop, where it runs great as I’ve mentioned multiple times, you can use a very simple form where the host is localhost and you connect to the default database. That’s about as simple as these get.

What you can also do, if you’re just playing around with this and don’t want to bother installing ClickHouse, is use this second URL, which connects to a public endpoint that we maintain. It has some interesting data sets, taxi data, airline data, and the GitHub data I was referring to previously. You can connect to this anywhere you have an internet connection. Of course, if you have your own ClickHouse servers, you can form the URL appropriately and connect to them.

The database connection page is very straightforward. This is one of the things where the Superset community has done an amazing job of making it super easy. Connecting to ClickHouse is just going into the databases page, pressing add database, and a form pops up. You give it a name and type your URL in. I always, as a matter of practice, do a test connection just to make sure I got it right. That ensures the thing actually connects, and then you press add. At that point you’re done and basically ready to talk to ClickHouse, and you can have some fun with ClickHouse data. That’s what we’ll do for the rest of this talk, discuss exactly how you can do that, showing some simple examples and then what’s going on under the covers, because if you’re a ClickHouse person, it’s nice to know what the driver is doing for you and why it works.

20:03 — How Superset Organizes Data

Robert Hodges: Before I go into the two examples, I’d like to give a conceptual overview of the pieces in Superset that we’ll be dealing with. This is not from the Superset documentation, it’s just something I cooked up to explain it to myself as well as other ClickHouse users. You can think of the base as being the database object, the thing that connects you. Then there are two ways you can get data, as Srini mentioned: the virtual dataset, but also physical datasets, and we’ll talk about those in a minute. These datasets are like the semantic layer that actually goes and fetches the data for you and can cache it. They basically give you the cube structure. Then the charts attach to those, and those are your individual displays. Then you can link them all together in dashboards, which is your final thing that you show to your users.

So this is the structure, and what we’re going to do is create each of these pieces, link them together, and then we’ll have a nice dashboard. We don’t have to do the database because we just did that a minute ago.

21:28 — Creating a Time Series Chart from a Physical Dataset

Robert Hodges: I’m going to start with a basic time series chart for ClickHouse. This is one of the first things you’re probably going to want to do, because most data in ClickHouse is time-ordered in some way. There are three steps and it’s really quick. We’re going to create a database, which we already did, so we won’t show that again. We’re going to create what’s called a physical dataset, which points to a ClickHouse table. Unlike what Srini showed a few slides back, we don’t even have to go in and look at SQL. This connects straight to the table, and then we’ll be able to build charts directly from that table. Finally, we’ll create the chart. The cool thing about these steps is you don’t have to write any code. One of the reasons I like this is I like finding options where people who are not necessarily SQL gurus can still come to ClickHouse and get valuable information out of it without having to write a bunch of SQL.

The second step in that process was to create the physical dataset. This is very straightforward. I’m using this public endpoint. You go into the datasets section in Superset, and there’s a plus dataset that you can press. You select the database connection, here’s our ClickHouse public endpoint, which we already made. You select the schema, which in ClickHouse parlance would be the database, so we’ll select the default database, and then pick a table. That’s it. You just press add and in it goes. Once you do that, you get pushed back to the datasets view, and the last dataset you created is right at the top, so there it is, on time, just created a minute ago.

To make a chart, all we have to do is click on that, it’s really easy, and you’ll be bounced into this chart view where you can now create a time series chart. You have to play around a little. The first thing you do is select the chart type, which is time series. Since it’s time, you’ll tell it a little about your time dimensions: what’s the column? Superset is very good at recognizing time series columns automatically, so they’ll have a little clock beside them. What’s your time grain, what level of granularity do you want to view it at, and what’s the time range? These are all really easy to select. You’ll then select a metric, some measurement you want to see, and finally one or more things to group by, which break up the data into different series.

If you fill this out, you can follow exactly these steps right this instant if you have Superset, and you’ll see a nice chart, which in this case I’ve called flights per month. Normally a line chart will come up, but I’m a stacked bar chart kind of guy, so I quickly went and customized it into a nice bar chart. But there’s your data, and the cool thing, as I say, is I didn’t write any SQL to get this. As Srini says, this is a no-code operation.

As a database person, though, I love to know what’s going on under the cover, just to make sure it’s doing the right thing. I’m not naturally paranoid, but I guess I am a little bit, and I’m very curious. One of the things that’s really cool about Superset is that when you’re in this chart, you can find out what query got generated just by going over to these little lines and pressing that button. You’ll see a dropdown that says view query, and if you punch that, you’ll see the exact query that got generated. What I like is that I look at this and say, this is well-formed SQL. This feels like the same SQL I would have written if I were doing this myself, so that makes me feel pretty confident that under the covers it’s doing the right thing.

So that’s our time series chart. If you have a big fact table and it’s already got everything denormalized into it, this might be all you need to do to generate time series charts. You don’t have to go into SQL Lab or anything like that, you can already get useful information out of it.

26:24 — Virtual Datasets, Joins, and a Map Chart

Robert Hodges: What’s interesting, though, is to look at something more in depth. There are cases where, as Srini said, we want to sculpt the data and add additional information to it. In that case, since it’s going to be based on a query, we’re going to need to create what’s called a virtual dataset that points to a ClickHouse query, and that query in turn can reference as many tables as it needs to fetch the information we’re interested in. Then we’ll create the chart on top of that. In that case, what it’s going to do is create a ClickHouse query which references a sub-query that represents the virtual dataset. When I typed these slides up, I got them slightly backwards, but you’ll see what I mean in a second.

Let me show a more concrete example of why you would want to do this. I’m going to build a query that answers multiple questions. I’m sure everybody can see instantly by reading this that what it’s doing is fetching cancelled and delayed flights, taking the arrival points and the destination points, and adding latitude and longitude to them. This is useful because in addition to giving us nice metrics, like how often flights get cancelled, we can see the exact locations of the airports and maybe put them on a map.

I was just kidding about this being totally easy to read, let me show you a picture. This is what we’re doing. We have this on time dataset, which contains flight data, and we’re joining it with another table called airports. There’s a field called IATA, that’s the code like SFO or DFW that we use to designate the airport. We can join on that and gather in the latitude and longitude for both the originating airport and the destination airport. This is an example of something where in Superset you do have to write SQL code to get it, because Superset currently doesn’t know how to do joins. Sometimes you need to transform or fix up things, so you’ll want to come down to the code level and get it right.

In this case, we’re going to jump into SQL Lab with that query. You’ll come in, enter the query, and play around with it, because what SQL Lab allows you to do when you hit the run button is then show you the data, so you can get a sense of whether it’s erroring out and whether it’s returning data that looks roughly like what you expected. There’s another nice thing: the query editor is pretty smart about columns. You can select the table or tables you’re operating on, you can see here we have both the airports and the on time table, so when you’re filling things in, the query editor will help you fill in column names. You play around with this, get happy with the data, and then you save the query. You give it a name and press save and explore. What that does, once you press it, is save this as a virtual dataset. This is the simplest and in fact the standard way to create virtual datasets. There’s more we could do with virtual datasets, because you can go in and tweak them to add additional metrics and other configuration, but I’ll skip that since those are things you can go do for yourself.

What we’re going to do now is create a chart on it, which turns out to be really straightforward. From the datasets view, we just click on that virtual dataset, and that dumps us into a new chart. In this case I’m going to create what’s called a deck.gl Arc chart, which is basically a map that shows the arcs between geographic locations. I picked this for a couple of reasons, but the biggest is that it consumes latitude and longitude, so it matches the data we’re fetching from that virtual dataset. One thing I should mention: if you’re a ClickHouse user doing this in open source, you’ll need to go get a Mapbox token, which you then pass into Superset when you start it. This is documented really nicely in the Superset and Preset docs. It’s very easy, you just get the token and put it in your environment and it all works. As far as the chart type, you select deck.gl Arc, give it time dimension information so we can set the range, and it’s going to ask you for the fields that represent your latitude and longitude. In this case I’m setting a filter because I just want to show flights from SFO. As soon as you do all that and press run, you’ll see a nice chart like the one on the right side. That’s all you have to do to create a geographic chart, it’s very straightforward.

One thing that is interesting to look at is, again, what’s going on under the covers. We can do the same thing where we go look at the generated query. In this case, if you understand how ClickHouse queries work, what’s basically going to happen is the chart selects all the fields it’s showing, puts all the filter conditions in, and adds things like the limit. But then what you’ll see in the middle, and I have it in orange because it’s sort of long, is that whole query we just developed, which is then selected off using a sub-query. This generates a lot of code, but it turns out it runs pretty fast. ClickHouse is quite intelligent, so things like the where clause and the various filter conditions will just get pushed down into that sub-query, so we won’t bother to select all the data and then filter it. We’ll initially only select the data we actually want. This is super efficient and works very well.

33:44 — Word Clouds, Dashboards, Caching, and Roadmap

Robert Hodges: Here’s another of my favorite Superset charts, because it’s so easy to do: a word cloud. I love this one. This is another chart I’ve created off that same virtual dataset. You select word cloud, give it the time dimension, the variable you’re looking at, in this case the origin, that’s the dimension, and then your metric, which is the number of flights. Because of the way it’s sorted, this is essentially the busiest airports by departures. This is totally cool. It’s something you can literally do in about two minutes, and you have this chart that shows a really interesting visualization of the data.

That begins to trend into the whole panoply of things you can do in Superset. I think you guys have at least 40 different display types, something like that. It’s an absurd number, just a huge number of ways you can display data imaginatively.

Finally, putting them in a dashboard is really simple. Anybody who’s used Superset knows you go in and there’s a dashboard editor. You can edit the charts, add them in, and then publish them, and this is what you show to your users. This is really powerful. It was a real pleasure to get involved with using Superset. There are also some interesting features in Superset that I think we may get into in future webinars. One I find really fascinating is that Superset can do caching and clustering, and this addresses one of the impedance mismatches we get in multi-tenant systems, where underneath your engine is ClickHouse, which is designed to answer queries as quickly as possible. The way it does this is that when a query comes in, it seizes as many resources as it’s allowed to and answers that question as quickly as possible. Now, if you have a system with 10,000 people looking at the data, that can create a problem, because if they all come in and do that, they’re all grabbing these resources, and this is a concurrency or contention issue. What Superset can do to help is cache this in the semantic layer, and then people refer to that. The queries get run once, or run at intervals, and it begins to give you a way of solving the multi-tenancy problem while still having a highly responsive, essentially real-time analytic service for your users.

Let me stop for a second. Here’s the dashboard we were talking about. Let’s expand this up. As I said, these things are really delightful to edit. We can edit the dashboard as easily as this. If we want to add more charts, I have my busiest destinations. I can bring that over and stick it down here. Let’s see if it’ll go in there. Not enough space. You can just play around. It’ll go below if you want it, you can add it to a second row.

Srini Kadamati: Yeah, that’s what it is, like components, so I can add a row. There we go. It has to highlight blue, and when you drag it you’ll see that blue highlight.

Robert Hodges: There we go. Okay, now we’ve got a row. I’ve been more interested in creating the charts themselves than the dashboards, but this is totally easy. We can expand it out and make it nice. Really easy to use chart editor. And the cool thing again, if I can editorialize, is what this really opens up for ClickHouse users: the ability to expose this data to people who are not necessarily ClickHouse experts. I think that’s where the visualization tools both display things imaginatively and open up the audience of users. This is a really important collaboration between these two types of software. I could work on this all day, I really like working with it. We’ll just save it. There it is, looks very nice, and we’ll go back to the presentation.

So we’ve been working together with Srini and the Preset team on this, and more broadly the Superset community, because we want this to work for everybody. One thing I want to emphasize is that everything we’ve shown you works great in open source, and that’s our goal on both sides. The first thing on the roadmap is that there are a few bugs. For example, in the clickhouse-sqlalchemy driver there are certain ClickHouse tables, to give a concrete example, where if they use enums and you’re using Postgres as your backend, it will bomb out when it tries to read it and convert it to a dataset. So we need to address things like that. We also need to build out and create more documentation. This webinar is part of it. We’ve written a couple of blog articles, which Srini kindly published on the Preset side, and those are on the Altinity blog. We’ve also posted docs on our side that help people get this set up and connected as quickly as possible. More broadly, in addition to open source, a lot of people like managed services, so we both have managed services and we’re working on connecting those seamlessly. For example, if you’re a Preset Cloud user you can connect to Altinity.Cloud, but of course you can do it all in open source as well. For both of us, it’s run it where it’s best for you. We offer this as a service, but you don’t have to use it, you can get everything we’ve described here in open source.

That’s it. Srini, thank you so much for having me as a guest. It’s a pleasure to do this talk, and we’ll take questions.

40:45 — Q&A: CSV Limits, Druid, MergeTree, and Grafana

Srini Kadamati: Absolutely. Thanks again, Robert, for coming on. If you’re new to Zoom webinars, you can use the Q&A button at the bottom to ask questions. Now’s the chance.

Robert Hodges: For those of you who want to use the link to the public dataset, Srini shared it. I’ll show you how that link works. This is called the play interface. I’m hitting that, and if you want to use it, this is a simple web UI native to ClickHouse itself. You press your account name, this is demo, and the password is demo, and at that point you’re talking to ClickHouse. We can prove that by asking it to tell us something, like select the version, or show the tables. So feel free to use that URL for testing out Superset. It’s our plan to keep that available for as long as people are interested in it.

Srini Kadamati: We’re getting some questions. Andre asked, if I’d like to export data from virtual datasets as CSV, Superset limits the result to 5000 rows. Is there any chance to export all that data from ClickHouse?

Robert Hodges: On the Superset side, I don’t know the exact answer, whether you can increase that or not. I’ll have to follow up. But I made Max a panelist, so maybe he’ll know. Max, do you know anything about removing the limit for CSV row limits out of SQL Lab?

Maxime Beauchemin: You can crank up the limit in your environment. There’s a confusing number of configuration keys you can change to control limits in Superset. Some limits are related to Explore, some to SQL Lab, some related to how much you send to the client or web browser, and some on the CSV side. All of that is to try to prevent ourselves from crashing people’s web browsers or infrastructure in general. I thought the CSV export default was a million, and you can bump it, and there are ways around it. Hopefully there’s some documentation around this, maybe we can share some links. Otherwise, you can always read the code. Under superset_config.py is where all the config elements are, and I would grep or look for CSV and limit as keywords and see what you find there.

Robert Hodges: I should say, on this public endpoint we do limit the number of rows we permit you to come back. It looks like this one said it came back with two million rows. You’ll definitely hit a limit, but it’s pretty large. I have found personally that in making toy graphs, typically the things I was looking at, I could limit the data in such a way that the limits didn’t cause me much of a problem, but that’s probably not true for everybody.

Maxime Beauchemin: I just put a link as well to at least one parameter that limits SQL max row. It limits it to a million. By the way, I totally get how people want to extract gigantic CSV exports and raw data into Excel and everything, but it’s kind of a flawed thing in general. You want your super fast database engine, whether it’s ClickHouse or BigQuery or something else, to be doing the aggregation for you. There’s no way as a human that you can consume eight million rows. So think about the approach here, what are you trying to achieve and understand, and look at maybe a collection of queries instead of extracting everything.

Robert Hodges: I totally agree with that. That’s why this limit has not been a problem for me either.

Srini Kadamati: Let’s do the next question. I’ll do these a little out of order. Mark Sherman asks, what are the key differentiators between ClickHouse and Druid?

Robert Hodges: Great question. Let’s talk first about what’s the same about them, because that frames it a little. Both Druid and ClickHouse are trying to solve a very similar problem: to have a very large data set with people asking questions against it and getting answers within a well-bounded period of time, say a second for a human asking a question like slice and dice web analytics the way you do in Google Analytics, or a machine asking a question like, I see somebody browsing my website, should I throw up a pop-up to ask them to subscribe? In that case you might want an answer back in 10 to 20 milliseconds. So both systems are designed to solve that problem.

The differences start from there, because ClickHouse is a SQL data warehouse and it used SQL right from the start. It also has a very simple architecture. There’s really just one process that does the work, the ClickHouse server. When we’re doing clustering, for example replication and sharding, we use ZooKeeper to keep consensus between replicas, but we’re actually eliminating that, so it’s really just one process. By contrast, Druid has a bunch of different types of services that you then have to configure to perform different operations within the cluster. Another thing is that Druid is written in Java, so it tends to be less efficient on resources. It can achieve high performance, but you tend to have to put more resources out there and tune them more carefully. This is generally true of systems written in Java. Another thing people tend to notice is that Druid, although very powerful on time series and able to scale very high, is not as good on some of the SQL features like joins and various types of operations. Everything in ClickHouse is SQL. There are still places where people tend to have to descend into JSON to get stuff working, and those JSON interfaces have been somewhat inconsistent. That said, I’ll circle back and give these folks props. They were the first people to recognize this problem, and there’s no question that Druid scales well. In fact, both systems scale well.

Srini Kadamati: Adding to that, Max had a database geek question: how do data segments look in ClickHouse, similar to Druid, Vertica, Parquet, et cetera?

Robert Hodges: Great question. I normally have a picture of this, but I kept it out to keep things short. The workhorse table format is called MergeTree. We store the tables in column format, but what we actually do is break up the tables into pieces we call parts, based on a partition key, like you might partition by month. Every time you send a bunch of data up to ClickHouse and add it to one of these MergeTree tables, let’s say it comes from the month of June, it’ll create a part. A couple of days, or maybe an hour, from now, you’ll add more data, so you’ll get another part. These parts are then just present within the structure that holds the table. ClickHouse knows where they are, and when it needs to do a query, it queries all the parts and parallelizes very well across them.

Over time, one of the things ClickHouse does is a background merge, where it takes these initial parts, which are kind of small, and merges them. They coalesce over time into larger and larger ones until ClickHouse says these are just about right, I’m going to leave them alone. So it’s basically columnar format parts, the parts are divided by a partition key, and within the parts they are sorted using an ordering. That ordering, by default, is also used to maintai sparse primary key index, which allows us, if you’re using one of those order by keys, to find data more quickly. But we don’t need it, we can also just do brute force scans.

Maxime Beauchemin: When you say columnar, it’s one of these pivoted formats, right, where you set a number of rows in a segment and twist it 90 degrees?

Robert Hodges: You have a default, you can have a maximum part size. In general, nobody sets that because ClickHouse just figures it out. But generally speaking, if you go and look, you’ll see by default two files for every column. One is the actual blocks of data, and the other is what we call a mark file, which is a bunch of offsets that basically tell us how to get into these groups, given the ordering. By default it tells us the beginning of the block for every eight thousand rows, a little bit like the Parquet footer with offsets.

Maxime Beauchemin: Druid systematically, originally at least, had reverse bitmaps for dimensions, and then everything is memory mapped, things like that.

Robert Hodges: The idea in all of these systems, and I think Druid shares this, there are two important things about indexes in ClickHouse. One, they’re sparse, so what I said about having an entry for every eight thousand rows or so is to ensure that the index can fit entirely in memory. The second thing is we do have secondary indexes, but they’re what are called skip indexes, so they’re designed not to find things but to tell you the things you’re not going to find. In other words, using bloom filters, for example, so that we can say, as we’re scanning this column, we do not need to look at 30 percent of the blocks because we’re just not going to find the value there. So we can reduce I/O that way.

Srini Kadamati: Another question. Good presentation, thank you. How is an entry of a new record triggered to the graph? Is it polling, is there a trigger in the database? They had a follow-up around looking at temperature readings from a car, curious how the data is queried to update the graph from the Superset side.

Srini Kadamati: As far as I know, depending on how aggressively you set your refresh interval, your query is basically rerun. So if you have new data, you’re getting all that data. If it’s the last 24 hours and it’s been five minutes, then that data is being re-queried. I don’t know if there’s anything on the ClickHouse side, Robert.

Robert Hodges: What’s really relevant on ClickHouse is that when you insert something into ClickHouse, it’s instantly queryable. There are systems where you insert and then it takes a while for it to materialize and actually be viewable, but in ClickHouse, when you do the insert, it’s instantly queryable in the source table. What’s almost more important is that if you’re computing aggregates off that source table, and we do this through something called materialized views, those views are also instantly updated. So generally speaking, as soon as you get the high sign back from ClickHouse, yes, we got your block, you can query it. That means that between the refresh rate and the fact that it’s instantly queryable, you can see data very quickly.

Srini Kadamati: There’s one here on Superset versus Grafana. Do you mind if I take that, Robert?

Robert Hodges: First of all, I want to say they both work great with ClickHouse. We also maintain the Altinity Grafana Plugin for ClickHouse. I’ll mention two things I think are significant. One is that Grafana is very focused on time series, and it actually has a relatively small number of visualizations compared to Superset. The word cloud, for example, I don’t believe that exists in Grafana, I’ve never seen anything like it. Grafana is very good at being able to have a bunch of data and a time series come down, and then you can manipulate it interactively inside the browser, extend the intervals and things like that, without having to go back. By contrast, what I love about Superset is that there are so many more ways to show data in interesting and imaginative ways, that allow people to use the full bandwidth of their brains to appreciate the meaning of the data. There’s also a second thing which is really important: Grafana has no caching layer, so if you have a thousand people and they all have Grafana dashboards, every time they press refresh they’re all going back to the server. Superset has the semantic layer and is able to avoid that kind of problem. So that’s something which would be quite significant in some use cases.

Srini Kadamati: I wanted to add a few things. One thing that’s really clear is there’s a chasm in the analytics world between operational analytics and business intelligence or business analytics. Superset really comes from that area of business analytics, and it is pretty good at operational analytics too. Grafana is really built with operational analytics in mind, typically querying a time series database a lot, like what happened in the last hour, the last 90 minutes, the last day or two. Everything is timed down, not as much dimensionality or combination of dimensions usually. That being said, Grafana is getting better at business analytics, and Superset is getting better at operational analytics, and we start to see that in the database world too, with databases like ClickHouse and Druid and Pinot and others that are good and useful on both sides of the chasm.

Robert Hodges: That’s a great point. One of the things that’s great about Superset is this no-code thing, and as I mentioned, expanding the audience. Right now you really have to be pretty good at programming to use ClickHouse or to use Grafana effectively, and for example you don’t get much reuse of queries. But with Superset, you put it in the virtual dataset, you’ve got your base layer, and then you build on top of that. So that’s something I really like about Superset. That said, I like both of them, and I think what Max said about operational versus business is a really good characterization.

Srini Kadamati: Awesome. Well, we just cleared out our Q&A queue, and we’re at time. Thanks everyone for sticking around, and thanks Robert for the excellent live demo.

Robert Hodges: Thank you, guys. It’s been a huge pleasure working with the Superset community. Go Superset, we’re looking forward to doing a lot more stuff with you.

Srini Kadamati: Great. Enjoy the rest of your day, folks. Thanks.

Robert Hodges: Thank you. Bye bye.

FAQ

Which SQLAlchemy driver should you use to connect Superset to ClickHouse? There are two drivers with deliberately similar names. The older sqlalchemy-clickhouse driver uses the HTTP interface, lacks TLS support, and has not been actively maintained since 2018. The recommended option is the newer clickhouse-sqlalchemy driver, which is actively maintained, Apache 2.0 licensed, and adds TLS support and many bug fixes. Install a version of 0.1.6 or later to get those fixes.

What is the difference between a physical dataset and a virtual dataset in Superset? A physical dataset points directly at a single ClickHouse table, so you can build charts from it without writing any SQL. A virtual dataset is based on a saved query, which can join multiple tables or reshape data. You create a virtual dataset by writing and saving a query in SQL Lab, which is necessary for cases like joins, since Superset’s no-code builder does not generate joins on its own.

How does ClickHouse store data, and what is a MergeTree part? The workhorse table format is MergeTree. Data is stored in columns and split into pieces called parts, organized by a partition key such as month. Each insert creates a part, and ClickHouse continuously runs background merges that combine small parts into larger ones. Within a part, data is sorted by the table’s ordering key, which also maintains a sparse primary key index that fits in memory and speeds up lookups.

How quickly can you query data after inserting it into ClickHouse? Almost immediately. When you insert data into ClickHouse, it is instantly queryable in the source table, unlike systems where new data takes time to materialize. Aggregates computed through materialized views are also updated instantly on insert, so a dashboard’s responsiveness is governed mainly by its refresh interval rather than any delay in the database.

How does ClickHouse compare to Druid for real-time analytics? Both target fast queries on very large datasets with well-bounded response times. ClickHouse is a SQL-first data warehouse with a simple single-process architecture (using ZooKeeper only for replication coordination) and is written in C++, so it tends to be resource-efficient. Druid uses multiple service types and is written in Java, scales well on time series, but historically has weaker SQL support, sometimes requiring JSON interfaces. Druid was an early pioneer of this problem space.

How does Superset compare to Grafana for ClickHouse dashboards? Both work well with ClickHouse, and Altinity maintains plugins for each. Grafana is focused on operational analytics and time series, with a smaller set of visualizations and no caching layer, so every dashboard refresh queries the server again. Superset comes from the business analytics side, offers many more chart types such as word clouds, and includes a semantic layer that can cache results, which helps with high-concurrency, multi-tenant workloads.

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