Building Beautiful Interactive Dashboards with Grafana and ClickHouse®

Presenters: Robert Hodges (Altinity CEO) and Eugene Klimov (Grafana ClickHouse plugin)

Please note, we have an updated webinar on this topic presented June 2024. You can watch it out here.

In this webinar, Altinity’s Robert Hodges and Eugene Klimov show how to build fast, attractive dashboards by combining Grafana with ClickHouse. After a level-set on what each tool is, ClickHouse as an open-source columnar data warehouse and Grafana as a display tool for time series data, they explain how the two connect over the HTTP interface and how to set up Grafana on Ubuntu, in Docker, or on Kubernetes.

The core of the session is hands-on. The presenters walk through adding ClickHouse as a Grafana data source, building a first table panel, and then creating time series graphs using the plugin’s macros such as time series and time filter. They emphasize practical debugging, using the query inspector to see the expanded SQL and the ClickHouse web client at the /play endpoint to test queries directly.

The talk then covers more advanced techniques, including function macros that generate entire queries, selection variables for interactive dropdowns, and feeding multiple queries into a single panel to build displays like pie charts and geographic maps. The takeaway is that ClickHouse delivers very fast queries on large datasets while the Altinity Grafana plugin makes it quick to construct rich dashboards on top of that data. A wide-ranging Q&A covers Superset, embedding Grafana, query caching proxies, reading CSV data, and querying MySQL directly from ClickHouse.

p.s., Altinity maintains the ClickHouse Grafana plugin and we use it constantly in our own work.

Here are the slides:

Key Moments (Timestamps)

Key moments generated with AI assistance.

• 0:14 – Welcome and webinar housekeeping
• 1:38 – Presenter bios and Altinity overview
• 3:22 – Introducing ClickHouse and Grafana
• 7:18 – How Grafana connects to ClickHouse
• 8:51 – The Altinity.Cloud test endpoint and datasets
• 10:26 – Setting up Grafana on Ubuntu and Docker
• 13:27 – Adding ClickHouse as a data source
• 16:47 – Understanding ClickHouse URLs and debugging
• 18:44 – Building your first dashboard
• 23:58 – Creating time series graphs with macros
• 31:31 – Advanced tricks: function macros and variables
• 37:47 – Multiple queries, pie charts, and maps
• 43:17 – Wrap-up and how to get started
• 47:01 – Q&A: Superset, embedding, proxies, and MySQL

Webinar Transcript

0:14 — Introduction and Housekeeping

Robert Hodges: Hi everybody, welcome. Today we’ll be presenting on building beautiful, fast dashboards with Grafana and ClickHouse. We’ll give you an introduction to how Grafana works with ClickHouse and then give you a bunch of expert tips as well as ideas for things that you can build.

Before we get into the webinar as a whole, I’d like to make you familiar with a few things that will help you enjoy it more. First of all, this webinar is being recorded, and we will publish links to the recording as well as the slides afterwards, so you don’t have to take frantic notes. We’ll send it out to you if you signed up.

The second thing is that we will have time for a Q&A at the end, and you can also ask questions at any point during the webinar simply by typing them into the question and answer box, which you can activate from one of the buttons at the bottom of your screen. If you pose a question that’s relevant to what we’re talking about, I’ll probably stop and answer it right there. Otherwise, we’ll have time at the end.

Finally, at the end we will do a poll. It’s very quick. We’d appreciate it if you would stick with us and fill it out. It will take you literally 15 seconds. It’s just three questions that help us build better webinars in the future. With that, I’m going to proceed.

1:38 — Presenter Bios and Altinity

Robert Hodges: My name is Robert Hodges. I’m CEO of Altinity, but for the purposes of this talk I’m a dev. I’ve worked for over 30 years on databases as well as topics like virtualization and security. ClickHouse is one of many databases I’ve worked with. It is basically number 20.

I also have with me today Eugene Klimov, my colleague. He’s responsible for integrations of ClickHouse as well as management, but most importantly he maintains the Altinity Grafana Plugin for ClickHouse. He will probably not be talking, because he’s in a time zone that is very far away, but he’s tracking the conversation to help answer questions.

Just a little bit about Altinity, which is our company. We’re the number one enterprise ClickHouse provider (i.e., support for ClickHouse). We basically make enterprises successful in building analytic applications on ClickHouse. We offer hosted ClickHouse in Amazon, called Altinity.Cloud. We’re also a major committer as well as a community sponsor for ClickHouse, particularly focused on the U.S. and European markets, and maintaining the Grafana plugin is just one of the many things that we do in the open source environment.

With that, let’s do a level-set to introduce ClickHouse and Grafana, since I assume there are a few people here on the call who may not be using one or the other right now.

3:22 — Introducing ClickHouse and Grafana

Robert Hodges: Let’s start with ClickHouse. The way I like to explain ClickHouse is to imagine that MySQL and Vertica, Vertica being a popular proprietary data warehouse, got married and had a child. That child would be ClickHouse.

From the MySQL side of the house, we get a single binary, it understands SQL, it runs practically anywhere, and it’s open source. Those are all properties of ClickHouse. It turns out ClickHouse even has some resemblances in the language, because many of the people who have committed things to ClickHouse know MySQL very well, so we’ve imitated the language.

From the Vertica side of the house, we get data warehouse capabilities. These include the ability to store data in columns, so you can think of them as big compressed arrays that are very efficiently stored. We have the ability to do both parallel and vectorized execution. Parallel means being able to spread processing across many cores on a single host, as well as many hosts. Vectorized execution means we’re able to take advantage of what are called SIMD instructions, single instruction multiple data, which allow us, for example, to add up multiple things at once in a single clock cycle. And of course, since it’s a data warehouse, it scales to very large amounts of data. There are clusters that run as many as 500 nodes in various locations. Of course, when you’re just starting out, you can run it on a laptop, so there’s tremendous variation in the amount of data and the scale of computing that it supports.

A final thing about ClickHouse that really sticks out for most new users is that it’s incredibly fast. I have a demo I do that shows there are actually cases where ClickHouse can read faster from storage than it can generate the same information in memory. It’s very quick. So that’s ClickHouse. It’s exceedingly popular, it’s very easy to get, and you can start it in 60 seconds and be off and running.

What we want to do is talk about combining it with Grafana. Grafana is a display tool, primarily but not entirely, for time series data. It makes really great dashboards. It has a kind of native understanding of what it means for something to be a time series, so it has a time component, notions of intervals, notions of rates, things like that all built into the model. It’s very simple to install, and we’ll demonstrate that.

It supports lots of data sources. I’d like to say ClickHouse is the biggest one, but it’s not even close. Many people use Grafana on things like Prometheus, MySQL, Postgres, and Druid. It supports a wide variety of databases. There are many display plugins, and we’ll show some examples. It’s also really interactive, so if you’re looking at a graph, it’s very easy to zoom in on a particular section of time series, play around with it, and drill down further into the data. It’s incredibly popular for monitoring dashboards. That’s one of the primary use cases where we see Grafana, and I’d say that probably half of the people we know who are using ClickHouse are using it that way. It’s also open source, which means it fits in well with the ClickHouse model. And finally, it’s just beautiful. It has probably the most interesting-looking graphics of any of the products on the market. So this is what we want to combine with ClickHouse.

7:18 — How Grafana Connects to ClickHouse

Robert Hodges: Here’s a little model that shows the connectivity between these two systems. At the back end you have the ClickHouse server, which is going to serve up your data. I show just one server here, but it could be many. They could be behind a load balancer. It could be a cluster with even hundreds of nodes. It doesn’t really matter. Grafana will be talking to it.

In this talk we show a couple of ports. These are using something called the HTTP interface, which means we speak the HTTP protocol when we’re talking to ClickHouse from Grafana. There are a couple of ports you’ll need to get used to. One is 8123, which is for unencrypted HTTP, and the other is 8443, which by convention is for encrypted HTTP calls. We’ll look more at that as we go along.

Grafana itself typically runs as a server, and it will serve up the dashboards, which contain a bunch of JavaScript that then pops up in your browser, for example Chrome, Firefox, or Edge, and shows the dashboard. So this is the basic flow. You need a browser, which is easy, we’ve got one right here. You’ve got Grafana, which you’ll install. You’ll have to have ClickHouse with some data in it, and then you’re ready to go.

8:51 — The Altinity.Cloud Test Endpoint and Datasets

Robert Hodges: As far as ClickHouse goes, one of the things we’re able to offer people is a test endpoint. We run Altinity.Cloud, which is managed cloud for ClickHouse, and one of the things we do is provide a public endpoint. These are the connection parameters for it. So if you’re testing out Grafana and want to use some interesting data, you can use this public endpoint. It’s read-only. The password and the user are shown here. We’ll actually be using this in the demos. It’s super convenient for building dashboards.

Some of the data sets that are out there are airline on-time data, plus a table you can join with to show your airport names and locations. We also have a full history of GitHub events from the beginning of time, which is over three billion rows, and a trip data table, which is New York City taxi commission ride data. It’s a famous data set, and this version has about 1.3 billion rows. So these are nice data sets you can point your dashboards to in order to illustrate how Grafana works.

With that, we’re ready to talk briefly about how to set up Grafana.

10:26 — Setting Up Grafana on Ubuntu and Docker

Robert Hodges: We’re going to do a couple of things here. For ClickHouse people, we’ll show how to set up Grafana both locally on Ubuntu and using Docker, and then we’ll talk about connecting to ClickHouse as well.

The setup on Grafana is really easy. I do this all the time, because I typically do most of my dev work on Ubuntu. The commands highlighted in black are what you have to run. This can be done in a couple of minutes if you have a good network connection. You’re just going to load the grafana.com repository so that Ubuntu can see the Debian packages, then do an apt install grafana.

A key step is that ClickHouse support is not built into Grafana. There are a bunch of in-tree data sources, but ClickHouse is not one of them, at least not yet. So once Grafana is there, you have to execute this grafana-cli plugins install command, and that installs the Altinity Grafana Plugin for ClickHouse. Whenever you do this, you just want to restart the Grafana service so that it sees it and can work.

One thing I should point out: the name may be a little surprising. It’s called the Vertamedia ClickHouse data source. That’s a historical artifact. It was developed by a company called Vertamedia. Roman, who was the initial developer of it, was working for Vertamedia at the time. He’s since gone on to other things, and we still talk to him, but Altinity is now the maintainer of this. We left it in the same place to avoid confusion. So that’s your setup on Grafana, pretty easy to do if you’re using a Linux laptop the way I typically do, or if you have a VM somewhere.

The other thing you can do, which is very handy if for example you’re running on Mac and don’t necessarily want to run Grafana locally, is just run it in Docker. One of the cool recent improvements in Grafana is that it has gotten better at installing the non-tree plugins. This command we show you right here is going to pull down Grafana, get the latest version, and automatically install the Vertamedia ClickHouse data source, the ClickHouse Grafana plugin. It’ll pop it up and then you can start talking to it.

I do a port mapping here. Grafana normally runs on port 3000. I map it to 3001, and that’s because I was running the previous example, which brought it up on 3000 already, so the port was taken and I remapped it. You can also do this in Kubernetes. Grafana runs super well there, and we do that quite a bit ourselves. We have a blog article that tells how to do that, plus you can find instructions on the net. So that gets you Grafana. Let’s have a look at how we connect to ClickHouse.

13:27 — Adding ClickHouse as a Data Source

Robert Hodges: When you first bring Grafana up, you’re going to have to log in for the first time. This shows the URL on my laptop that I connected to. It’ll ask you to change the password, which is always a good thing to do, and then what you’ll need is to add ClickHouse as a data source.

I’ll show you where you go to get this done. You have to come in as an admin. You go to the little wheel here, which is where the tools are, and there’s a section on data sources where you can add a data source. If everything has gone correctly, you’ll see a long list of data sources, starting with the built-in ones like Prometheus and Graphite. If you scroll all the way down to the bottom, you’ll see your ClickHouse plugin sitting down there, and you can click on that one.

Once you have it up, you fill in the information, test it, and save. You’re going to give the URL, and in this case we’re going to use the Altinity.Cloud endpoint because it’s convenient and we can see it anywhere.

Another important thing for connecting to ClickHouse is that Grafana has a couple of ways of operating dashboards. One is that all the queries to the databases go through Grafana itself, so the browser doesn’t talk directly to the database. This is the recommended way to use ClickHouse. The alternative is to have queries come straight out of your browser. This sometimes doesn’t work because there are some issues with CORS support in ClickHouse. If you’re a front-end or full-stack person, you’ll know what I mean. Basically, ClickHouse does not support what are called preflight requests, which are necessary for most modern browsers to talk to it if ClickHouse is running on a different host from your Grafana server. So we recommend using this default server access.

You’ll want to sign in using basic auth for this. I’ve got that highlighted in the settings. You’ll add your user, the user is demo, and you’ll add your password. It says configured because I’ve already entered it. Another thing we recommend is to use the POST request to send queries. You can use either POST or GET, but POST is better. We’ll show an example further on in the talk, because the query gets sent in the body, so if you have a problem with it, it’s really easy to look at it, see what’s going on, copy it somewhere else, and debug it.

Then you just do save and test. If you’re doing things correctly, you would expect to see data sources working. If you don’t, you have a problem.

16:47 — Understanding ClickHouse URLs and Debugging Connections

Robert Hodges: So what do you do if you have a problem? If you can’t connect to ClickHouse, the next thing you do is look up how ClickHouse URLs work, because that’s probably where you’ve made your mistake.

I’ve got a couple of examples here that show the URLs you can use to connect to ClickHouse. We’re using the curl command because we’re able to actually execute them and see what comes out. This first one is simply connecting to a local server using unencrypted communications. It doesn’t have credentials. It’s got a host name and a port, and this will just return OK if you run the command. So this would tell you yes, the server’s there and it’s live, we’re actually connecting to a ClickHouse server. The fact that it’s unencrypted with no credentials would be terrible in a production system. In this case it’s no big deal because it’s running in my closet, so the only way somebody’s going to get in is if they burgle the house. This is a pretty typical URL you might use for development purposes.

If you’re actually going to a production system, you’ll have a URL that looks more like the one shown below. We’re encrypted, we’re using HTTPS, and we’ve got the credentials baked in. You can also pass them into a header. We’ve got the host name and the port, and then what we’re doing here is a POST, where we’re echoing a ClickHouse command in, and this will bring back the version of the server. So from that previous screen, if it didn’t work, I would go ahead and do something like this to try to figure out what I’d done wrong. There’s more information in ClickHouse about the HTTP interface, and I definitely recommend checking there if you need more information.

At this point, we’re going to build our first dashboard.

18:44 — Building Your First Dashboard

Robert Hodges: The process when you come in to build a dashboard in Grafana is that you start a new dashboard and then add panels to it. As you add each panel, you can go in and edit it, apply the panel, and save the dashboard. You just keep doing that until you have something you’re happy with.

When you get started, it looks kind of blank. This was in fact one of the biggest problems I had with Grafana, just wondering what to do at the beginning. You hit this plus sign in the Grafana console, and then it pops up a new dashboard, and you can start adding panels. You press that button. It’s easy to get started, but the first time you do it, you might look at it and wonder what to do or how to get the dashboard started.

I have some more slides about this, but rather than do that, I’m going to stop right here and we can go look at a real one. Here’s my dashboard. These are just exercises I put together for this talk, so it’s not designed to do anything particularly glorious. It is an example of a dashboard built using the airline on-time data.

Let’s make a new panel. We see the existing panels, they’re not so exciting. We’ll look at them more later, but let’s add a panel and put it somewhere where it doesn’t totally block the screen. Now that pops us into an editor. We’re going to do a very simple panel that’s going to have a table in it. The first thing we’ll want to do to make this work is go to the little query editor and add a query, which is going to tell Grafana to go fetch some stuff. Let me wipe out that sample it supplied and get the query. Here it is.

What we’re going to do is construct a table of popular airline destinations based on the flights that go to them. We’ll put that query in. Oops, it just disappeared. There’s a little pencil here you can hit when you want to move around in Grafana. That’s how you bounce back and forth between different panels. So there’s our query.

What’s cool is there’s a preview screen up here that’s going to show us our data, but right now it’s not showing anything because it doesn’t quite know how to display it. It thinks we want to do a graph. So let’s help it out and tell it that it’s a table. There’s the table. Now it’s showing up as a table, but it’s showing time, which means Grafana thinks this is time series. In fact, this is just a table, so let’s tell it this is tabular data, not a time series. Once we do that, the panel pops up, and here we have the 10 most popular airports by the number of flights that have arrived there.

So that’s all you have to do. That’s a really simple example of creating a panel inside Grafana. You add the queries, select something to display it with, and tell it that it’s tabular information. We can apply that, and now we’re inside Grafana and there’s our table fully populated. This is one of the reasons people like Grafana so much, because it’s really easy to add these panels and then play around with them, move them around, and reshape them. It’s a very nice editor.

Having seen that, let me show you one other thing. We’ll quickly look at the data sources, to recall them. Oh yes, we want to save the dashboard. So here’s our data source, and that just shows you the example data source I set up. Again, you go to this wheel, you hit data sources, and you select it when you’re adding another one. This gives you your list, these are the things I showed you on the slides, and there’s ClickHouse sitting down at the bottom, ready to be used.

So that’s a quick demo. What I like to do now is come back to the slides. By the way, if you have any questions, feel free to post them into the question box. If I see them come up, I’ll answer them right then and there, or we’ll answer them at the end.

23:58 — Creating Time Series Graphs with Macros

Robert Hodges: Let’s look at how to do a time series graph, because this is where things get a lot more interesting. When we go into the editor where we were before, we can see four sections. We have the data preview, which is incredibly useful. You’ll see this little exclamation point in the red corner, which means something’s wrong here, either I don’t have data or your query is messed up. That says I have work to do. There’s the query editor with the sample. In the upper right, we have information about the panel, like its title, and I can give it a description. Then we have a bunch of information about visualization and other options. In this case, it comes in by default saying we’re going to do a graph.

In order to get the time series information set up, we’re going to have to give ClickHouse a little more information than we did last time. Let’s go to the query editor. First of all, we’re going to make sure we’re selecting ClickHouse. There’s only one data source configured, so it will pick this up automatically. The first real work we have is to go to this from field and set the database name, which is called default, and the table name, which is on time. That tells Grafana where the data is coming from, and you’ll see how that gets used in a second.

The next thing is that Grafana has to have a time series column. There’s this column here where you’ll see a column called date time, and there are also two other data types if you scroll down. What you’re doing is telling Grafana this is the data type we’re going to use. To process time series, Grafana basically wants something with millisecond accuracy, so we’ll type in toDateTime on the flight date to convert it to a date time format. That will make Grafana happy, because it’ll know this is the thing it uses to compute intervals on the graph.

There’s also a field here, Column:Date, which is kind of obsolete. I don’t recommend using it. It will probably go away. It’s a vestige based on the older way ClickHouse used to organize data. Finally, there’s a super important button here called the query inspector. This allows you to debug queries. I spend a lot of time there, and we’ll see an example of it in a minute.

So that’s the data source setup. We’ll now define the query. In this case the query is going to look a little different, because we’re going to use something called macros. These are things that Grafana will automatically expand out to become syntactically correct SQL for ClickHouse. For example, we’ll select the time series, and that will fill in the right column name as well as the right expression. We’ll select the table, and that will automatically expand to the database and table name. The time filter will put in a where clause that selects for the time interval we’re looking at. So it looks pretty much like regular SQL, except it has these macros. There are a bunch more of them. I won’t bore you with the list, but if you just type a dollar sign in the editor, it’ll expand out automatically and show it. This one here we want to format as time series, because that lets Grafana know this is really time series.

At this point it’s basically done, except it might be broken. A good thing, something that commonly happens with these queries, is that you won’t get it right the first time. Fortunately, you can just press that query inspector button. I hit that, and it bounces you into this really convenient debugger panel. You hit refresh, and then you’ll see down here the actual query you sent, and if it’s screwing up, you’ll see an error message there as well. This is super convenient, and this is why we like POST, because you can copy and paste this out and stick it somewhere else to debug it. Here you can see the expanded query that resulted from those macros. This is one of the reasons you want to use them, because they generate these complex expressions that you would likely get wrong. Then you have data. It’s basically a bunch of JSON data coming out. This is super useful and enables you to very quickly figure out what’s wrong with a query and fix it.

Sometimes it’s not enough to fix it inside Grafana. So if you really have a query that’s got you stumped, a really helpful trick is that ClickHouse itself has a built-in HTTP interface, like a real JavaScript interface. It’s very simple but allows you to actually run the query. What I did was take that query, copy it, and go to this URL. It’s slash play, and then you give the user, and ClickHouse will pop up this screen where you can debug your query. The demo user will get populated automatically. The place where the dots are, that’s your password, you have to enter that. What I normally do is put a limit in. You also need to be aware that whereas Grafana likes JSON, ClickHouse will only support normal output, so you have to play around a little with the format as well as limits. But it’s pretty easy to use, and this shows that the query is running just fine.

So that’s basically what you need to do for one of the most important features of Grafana, which is to graph time series data. In summary, you have the Column:DateTime, which you’re going to have to set when doing a time series graph. We don’t recommend you use Column:Date. If you put something in, you may need to do a little work to get it back out again. Typically, just putting a space where the name of the data is, or where the column name is, is sufficient to make it disappear. You’ve got these macros, particularly time series and time filters, which are very valuable ones to use. They will automatically ensure that you can support ranges. Then make sure the format is time series and Grafana will be happy. For all visualizations, including time series graphs, you add the query with its macros in the editor, use the query inspector to debug, you can use ClickHouse clients to pull it, and then there’s this super convenient web client, which is available since version 20.11. That’s very helpful for this kind of debugging.

31:31 — Advanced Tricks: Function Macros and Variables

Robert Hodges: So that’s the basics on Grafana. Let’s just take a brief pause and look at that query to cement it. Going back to my dashboard, here’s my time series, the one I was using for that example. I’m going to edit it. I see the nice graph. Here’s my query, showing my different macros.

An interesting thing is you can put alerts in. Grafana now supports those. I don’t have a good example because I don’t have the server configured for it, but you can put alerting in. And of course, if we want to see those parameters like the flight date and the database and table name, that’s available here. We can debug the query as we showed before, just by going to the query inspector and hitting refresh. There it is, and this is where I can copy and paste it out, leaving off the format JSON, and go execute it in another client. All very convenient.

One quick thing, let’s change this very quickly to illustrate. This is a line graph, it’s great. Let’s make it a bar graph, so we’ll take the lines away. That’s actually not showing what we expect. It’s showing way fewer flights because they’re basically overlaying each other. Let’s make that a stacked bar graph. And in fact, we can show some other features. This is just really cool. We can now zero in on the data and show much smaller ranges, and Grafana does this very well and very quickly.

Okay, so that’s the example. Let’s look at some more advanced tricks we can do with Grafana. Let’s look at function macros. Most of the Grafana plugins seem to have macros at different levels. As far as I can tell, there’s no standardization. It’s just whatever the plugin supports. The Altinity Grafana plugin supports what I call function macros, and they will actually generate an entire query. For example, that time series we were just looking at, I can generate it simply by having this macro where I give a couple of columns and say it’s from the on time table. That generates the much larger query shown here. This is sometimes useful because some of these queries are complex to create, and this gives us the same sort of information. So this is a very useful approach.

There’s a bunch of them. For example, there’s a columns one, which takes the carriers and uses them as keys, and then has an array of values per interval. These can then get mapped onto a graph. We also have things like rate, which is actually a very useful one, because many counters we’re measuring in ClickHouse and elsewhere are monotonically increasing, like the number of inserts. This will take something out of the ClickHouse system tables and turn it into a rate, like the number of inserts per unit time. Rate in columns combines that. There’s a way to compute the rate change per interval on counters. I don’t actually use these too much, you can check them out in the documentation. One thing we do recommend is that if you’re using them, it’s often helpful to use them to generate the query, and then sometimes you can take that generated query and tweak it and be happy with that, or you can continue to use the macro. They’re documented on the page for the plugin, so that’s handy.

Another thing that’s pretty cool is selection variables. Selection variables give us the ability to have selectors at the top of the dashboard. For example, we’ve defined one that allows us to pick a particular carrier and zero in on the information associated with that airline. This is the editor. You can click on it when you’re in there and get much more detailed editing. What that does is create a variable you can now use to select in your queries. So dollar carrier becomes a variable that any query can use, and that will show data for the current carrier. You basically have to put it in yourself, but Grafana gives you a simple way of defining the dropdown and populating the variable. We use that feature quite commonly for things like getting information about specific tenants or specific airline carriers.

You can also use these selection variables in titles. Here’s an example. The dropdown that gets created for this one, WN, is actually Southwest Airlines, and you can put that into the title in this graph, which is showing arrival delay over time. So that’s another feature we use pretty heavily.

37:47 — Multiple Queries, Pie Charts, and Maps

Robert Hodges: Finally, one of the really interesting things I find super useful in Grafana is the ability to do multiple queries that feed into a single display. There are many ways this can be used, but one handy one is to construct a pie chart. For example, let’s say we have a pie chart that’s going to show the top five carriers by the number of flights, and then another slice of the pie that shows all the rest.

What you’re going to do is define two queries that you independently add to the same panel. The first one is your top five, and the other one is all the rest. The trick to doing this is that we sort and group by carrier, order by the number of flights in descending order, and take the top five. The other query orders them but takes the sixth one on to whatever is the maximum number. I know I don’t have more than a thousand carriers, so I just use that as a limit. That’s going to give the final chunk of the pie. It’s actually important how you name it. If you name it “others,” it will match this others chunk of the pie, which is something supported by that plugin.

When you actually put it in, it looks something like the following. Here’s the pie. There’s my top five airlines query, and then I added another one called the remaining airlines, which has exactly the text we just saw. So that’s a cool trick, and there are other types of displays that will happily consume multiple queries in the same panel.

There’s lots more cool things you can do in Grafana. This is actually one of the fun parts of my job, to build these displays. Many of you on this call have done far more complex things. Let me show you a couple of interesting things. We have the pie in the new dashboard. Let’s have a quick look at how that was done. We’ll go into edit. Oh, it seems like it’s unhappy here, that’s interesting. So here are the queries, and we have our SQL. The pie chart lined up so we have the pie, we’re showing a legend, having it do percentages, and then that label “others” is actually important, it apparently has to match up with the column name. So that gets you the pie.

What I did want to show you was a very cool thing that’s now quite straightforward to do with ClickHouse in Grafana. This is using Grafana 7.4 and the latest ClickHouse release. This is a fully developed dashboard showing all the things I was playing around with, but nicely formatted and put together. We’re looking at Southwest Airlines. We have 35 total carriers. We can look at flights by carriers and so on. Here’s a really interesting geographic panel. Let’s view it using the ability to zoom in. This is basically telling us the average number of canceled flights per unit of time, which in this case works out to days. What’s cool is you can assign colors to it, and the bubbles show the size for the airport. For example, San Francisco comes up as 3.25 average canceled flights.

What’s happening here is that you see a very large number of canceled flights around places like Charleston on the East Coast. I have a feeling this actually shows us a hurricane. I didn’t go back to look, but there were a bunch of canceled flights during this interval along the East Coast, which usually means some sort of storm problem. If we extend it out to longer intervals, it doesn’t look like we have data there. Let’s try two years. Looks like we have a problem with refresh here. Never mind, you know how demos are, sometimes they don’t work. Anyway, I think you get the idea. It’s a really cool demo, and this kind of geographic display is pretty easy to do now with ClickHouse. So that concludes the specific things I wanted to show about ClickHouse and Grafana itself.

43:17 — Wrap-Up and How to Get Started

Robert Hodges: There’s just a little bit of wrap-up here. How to get started: if you’re new to ClickHouse, feel free to hit up the clickhouse.tech documents. That’s where all the reference documentation is for the open source ClickHouse. The ClickHouse plugin itself is available at this URL. You can also find it if you go to Grafana and look for plugins, and as I say, for historical reasons it’s called the Vertamedia ClickHouse data source. It has a long list of things you can do.

Grafana Labs has this cool feature where there are stored dashboards. A version of that airline dashboard we saw is available there. There are also other fun dashboards that do things like monitor SQL queries inside ClickHouse, and a number of dashboards designed for network flow log management. Finally, to find out more about Altinity.Cloud and using it directly or getting your own copy, check out our site. We’ve got documentation on that.

In summary, ClickHouse gives you very fast query on very large data sets. You’ll notice that as we did those examples, that was a small data set. It had 196 million rows in it and was coming off a small computer sitting in my closet, and the query results were basically instantaneous. The Altinity Grafana plugin enables you to connect Grafana dashboards to ClickHouse and build dashboards very rapidly. We’re the maintainers of that plugin. It has an active roadmap because we use it heavily ourselves, and we’re quite happy to fix things if you run into them. We have some plans, for example we need to do a refresh on the docs, and there are various small features we’d like to do to make this easier to use.

For quick prototypes, if you just want to look at some of the available data, please go out and try the Altinity.Cloud public endpoint. You can’t do much damage out there. If you weigh the system down, we’ll just chop the query. It’s a pretty performant system, so this is a quick way to get started.

With that, I’d like to thank you. It’s a pleasure to present on this, and I’d be delighted to answer questions if you have them. I’m going to start a poll here, and you’ll see it pop up on your screen. If you’d be so kind as to fill it out, three simple questions, they’re designed to help us do better webinars in the future. While you’re doing that, feel free to contact us if you’re using ClickHouse, anywhere, be it on-prem or in the cloud, to talk about how to build systems on ClickHouse and what kind of services we can offer. We do free one-hour consultations, and we do free trials for ClickHouse or Altinity.Cloud, where you can get two weeks for free. Our website is altinity.com, and we have a blog that’s quite popular, where for example you can read more about using Grafana. You can always contact us at info@altinity.com.

47:01 — Q&A: Superset, Embedding, Proxies, and MySQL

Robert Hodges: I see responses coming in on the poll, that’s great. Let me check for questions. Oh, wonderful question, I love this. How does Grafana compare to Apache Superset? Apache Superset also provides a connector to ClickHouse, and it comes with charts built in, including geospatial. Superset is a really good comparable product. We work with both of them. The Grafana support has been around longer, but we’re also working very closely with Max Beauchemin and the rest of the Superset team to ensure the driver support is there. One thing we’re fixing over the next couple of days is that we’re working with the Superset folks to use the right SQLAlchemy driver. I’ve been testing that over the last couple of weeks and it works great. We know of one minor bug right now, but overall it looks really good. I like Superset a lot. Superset and Grafana are basically my favorites, so if you’re comfortable with Superset, absolutely use it, and if you’re comfortable with Grafana, use that. Either way, you’ve got welcoming communities, and we’re around to provide support for both of them.

One thing Eugene pointed out is that Superset right now can’t be combined with Prometheus and Graphite, so it’s harder to combine infrastructure and business metrics in the same dashboard.

Let’s see. Is it possible to integrate Grafana into a page? That’s a really good question. You could use an iframe, but I think you’re thinking of something else, which is to actually embed it in a JavaScript application. I’m going to assume that’s what you’re asking, that you have a full-stack application and you’d like to plug Grafana into it. As far as I know, that is not easy to do. Grafana kind of wants to own the whole frame that you’re executing in. So you could put it in an iframe, but that means it’s a little harder to control the interactivity. What we find is that Grafana is really heavily used for infrastructure monitoring, and as Eugene pointed out to me, because it integrates with a wide range of sources, including very tight integration with Prometheus, which is pretty standard for infrastructure monitoring, what people actually do, if they want something where they control the interactivity, is end up building it themselves.

It looks like Eugene may be sending an answer. There’s a question here, do it vice versa? I’m not sure I understand that. I hope that was helpful, and definitely feel free to contact us, or ask that question out on the Grafana forums, that would probably be your best bet. Eugene is confirming, from his many time zones away, that we can share the dashboard as embedded iframe code. We actually do something like that ourselves, because we use Grafana to monitor our Altinity.Cloud, so we have cases where we need to proxy the Grafana pages.

Let me check the chat again. I hope this covered the questions. I’ll hold it open for another minute or so, and we’re happy to take other questions in the meantime. I’d really like to thank Eugene for all the help in setting this up. Eugene did most of the dashboards.

Oh, thoughts on using chproxy? That is a really good question. I’m not a chproxy expert, but the question is the thoughts on using chproxy. I’ve seen cases of Grafana killing the ClickHouse performance when a query isn’t quite right. That is definitely a possibility. I’m wondering what you’re thinking of when you use chproxy, whether you’re thinking of using query caching, for example. It looks like Eugene may be typing an answer to you as well. In general, if you have a query that’s misbehaving and beating up ClickHouse, my first step would be to grab that query out of the query editor, stick it in my favorite client, and find out why it’s misbehaving. So I would go fix the query. I’m not sure that chproxy is necessarily always going to be the right answer, but you may have different experience on that.

Okay, let’s see. Could I go for CSV-stored finance marketing data containing equity date, open, high, low, close, volume? There are two questions here, and I’m going to answer this the way I think it might have been asked, which is, if you’ve got CSV data, can you read it in ClickHouse? There are a couple of answers. You can do two things. One is you can just leave it in CSV, for example living in S3 files. If you want to read it from ClickHouse, ClickHouse has what’s called an s3 function, which can read these files directly. As long as you give ClickHouse a schema, a description of what the fields are, it will run queries directly off S3. The second thing you can do, instead of running those queries, is just load the CSV. You can insert all the CSV into a proper ClickHouse table and then query it there, again using this same s3 function.

Typically, if I had a CSV I needed to process, I would get it somewhere ClickHouse could see it, and best of all have it actually in a ClickHouse table. The reason you want to do this is that if the CSV is of any size whatsoever, it’s going to be far better to have it in a ClickHouse table, which is highly optimized for speed. For example, it has a column format, so you only do I/O on the columns you use, those columns are compressed, and we have the ability to knock out sections of the columns we don’t need to read. You may see performance differences on the order of a thousand or more depending on what you’re doing. So I would definitely think about loading it into ClickHouse, and of course you can happily handle it there.

I think that’s it for questions, so I would like to thank you all. Oh, okay. Today I use MySQL and process a lot of data by procedures for Grafana, I’ve set up a view layer. Here’s something you could do. You can actually select data directly off MySQL tables from ClickHouse. So if you’re in MySQL, one of the things ClickHouse can do is see MySQL tables. I haven’t tried it on views, but it certainly works great on tables. There’s something called the MySQL table engine and the MySQL database engine. I’d recommend you have a look at those if you have data in MySQL and you’d like to make it visible from ClickHouse.

Well, I think we’re at the end of the presentation. I would like to thank everybody for the questions you’ve offered as well as for attending the webinar. I hope it was useful. Oh, by the way, a final late-coming answer: trickster-proxy.io, if you have concerns about performance, try Trickster. The ClickHouse support there was actually written by one of our friends at Comcast, I believe. trickster-proxy.io, that’s a solution for query caching.

With that, I think we’re going to call it a day. Thank you so much for attending. We hope this was useful. We encourage you to contact us if you have further questions, or go ahead and post out in the Grafana community. We’ll be out there watching for things, and I hope to see you there. Thank you very much, and thanks again, Eugene. I really appreciate the help. Talk to you soon. Bye.

FAQ

How do you connect Grafana to ClickHouse? Install Grafana, then install the ClickHouse plugin (listed in Grafana as the Vertamedia ClickHouse data source, now maintained by Altinity) and restart Grafana. Add ClickHouse as a data source by entering the server URL and credentials. The recommended setup uses server access, so Grafana proxies queries to ClickHouse rather than having the browser connect directly, and POST requests, which make queries easier to debug.

Why does the ClickHouse Grafana plugin show up as “Vertamedia”? It is a historical artifact. The plugin was originally developed by a company called Vertamedia, and its initial developer worked there at the time. Altinity now maintains the plugin but left it registered under the original name to avoid confusion, so you will still find it listed as the Vertamedia ClickHouse data source.

How do you build a time series graph in Grafana on ClickHouse? Set the database and table in the query editor, then give Grafana a millisecond-accurate time column, typically by wrapping your date column with toDateTime. Write your query using macros such as time series and time filter, which Grafana expands into correct ClickHouse SQL, and set the panel format to time series. The avoid the obsolete Column:Date setting where possible.

How do you debug a broken Grafana query against ClickHouse? Use the query inspector inside Grafana and press refresh to see the exact SQL that was sent and any error message. Because POST sends the query in the request body, you can copy that expanded query and run it elsewhere. For tougher cases, paste it into the ClickHouse web client at the /play endpoint, remembering to use a normal output format rather than JSON and to add a row limit.

Can ClickHouse read CSV files or query data from MySQL? Yes to both. ClickHouse has an s3 function that can query CSV files directly from object storage if you supply a schema, and you can also insert that CSV into a ClickHouse table for much faster queries. To read from MySQL, ClickHouse provides a MySQL table engine and a MySQL database engine that connect to MySQL as a client and let you select from its tables directly.

What makes ClickHouse fast enough for interactive dashboards? ClickHouse stores data in columns as compressed arrays, so a query only reads the columns it needs. It runs queries in parallel across cores and hosts and uses vectorized execution with SIMD instructions to process multiple values at once. Combined, these let it return results on datasets of hundreds of millions of rows almost instantly, which keeps Grafana dashboards responsive.

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