Skip to main content

ClickHouse and Databricks for Real-Time Analytics

Summary

  • Column-oriented performance: ClickHouse delivers sub-second queries on massive datasets for real-time analytics.
  • Delta Lake integration: Databricks embeds the Delta Kernel into ClickHouse for seamless interoperability and governance.
  • Unified ecosystem: Organizations use both platforms together, with ClickHouse handling real-time and Databricks managing governance.

ClickHouse and Databricks for Real-Time Analytics

Watch: ClickHouse and Databricks for Real-Time Analytics
Talk by Melvyn Peignon, Principal Product Manager, ClickHouse
ClickHouse is a C++ based, column-oriented database built for real-time analytics. While it has its own internal storage format, the rise of open lakehouse architectures has created a growing need for seamless interoperability. In response, we have developed integrations with your favorite lakehouse ecosystem to enhance compatibility, performance and governance. From integrating with Unity Catalog to embedding the Delta Kernel into ClickHouse, this video will explore the key design considerations behind these integrations, their benefits to the community, the lessons learned and future opportunities for improved compatibility and seamless integration.
Intelligent analytics for everyone: https://www.databricks.com/product/business-intelligence
BI meets AI: https://www.databricks.com/resources/ebook/business-intelligence-meets-ai
Explore the only unified platform for agent systems: https://www.databricks.com/product/artificial-intelligence
See all the product announcements from Data + AI Summit: https://www.databricks.com/events/dataaisummit-2025-announcements

Chapters

FAQs

What is ClickHouse optimized for?

ClickHouse is a column-oriented database built for real-time analytics on massive datasets. It delivers sub-second query performance by storing and processing data by column rather than row, making aggregations and analytics extremely fast.

Why integrate ClickHouse with Databricks?

ClickHouse excels at real-time query performance while Databricks provides governance, AI, and unified data management. Together, they offer real-time analytics without sacrificing governance and data quality on the Databricks Data and AI platform.

What is real-time analytics?

This video explores real-time analytics through ClickHouse's column-oriented architecture, which processes streaming data at scale. Combined with Databricks Data and AI platform's lakehouse model, organizations can perform sub-second analytics on fresh data, enabling use cases from fraud detection to real-time recommendations.

Real-time analytics platform

This video shows how ClickHouse integrates with Databricks Data and AI platform to deliver real-time analytics at enterprise scale. The integration includes Unity Catalog support and Delta Kernel embedding, providing seamless interoperability between the real-time analytics engine and the managed lakehouse.

What is the future of real-time analytics on operational data?

This video demonstrates the convergence of real-time analytics with lakehouse architectures through ClickHouse and Databricks integration. By embedding Delta Kernel into ClickHouse and leveraging Unity Catalog governance, enterprises can build unified platforms that handle both real-time streaming and batch analytics without data silos.

Full transcript

0:07 Understanding ClickHouse

Okay. Um, hello everybody. Um, so. I'm principal product manager at Click House. And today I'm going to talk to you about uh what Click House is and what data bricks is and how you can use both of them for real time analytics. Um, given we're at the data bricks user conference, I I just would like to be sure that everybody knows what data bricks is. So, if you could all raise your hand if you know what data bricks is.
Okay, can you raise your hand now if you know what click house is? Oh, wow. A bunch of people know what kick house is. So, surprising. There's still a few people that don't know what kick house is, but uh don't worry. I'm going to walk you through exactly what tick house is and how you can actually use both tick house and data bricks um to do real time analytics on top of your data platform. So when I talk to my user and customers uh most of the time you know uh if okay the clicker doesn't seems to work actually okay it worked now sorry um so yeah so when I talk to my user and customer um the first thing that comes to mind when I want to describe click house is that it's an open source project and this is really fundamental about click house is that it has been like uh created a while ago like over a decade ago um really to tackle one problem and this problem was how to do uh web analytics and pas was designed for two things speed and scale and that was it basically that you need to have like a system that can scale to very large data data uh amounts and that could be extremely fast on top of this uh data And in 2016 or around we open source a project uh with Apache 2 license and since then people use click house in variety of scenarios right so from real time analytics to observability to data warehousing some people use it to store vectors for some reason and to do like a lot of weird stuff some some people even use it as a ETL pipeline so when you think about kickass it could be many things and depending to who you talk they're going you probably have like a different use case for kick house.
Um, but yeah, open source. Click house is open source and uh it's a very interesting project developed in C++ if you want to have a look at it. The codebase is awesome and uh it's a very thriving community where we have a lot of contributors and we have very welcoming of any poll request to the project. The second thing that you think about when you think about click house it's often uh the speed, right? So click house was designed for exactly that to do one thing uh but do it very good and very fast.
Um so it's you you don't really put click house in place when you want to do large data processing or batch loading or batch processing. Uh you use click house when you want to execute a query and you want to have a response uh in under seconds most of the time. Right? you want to have a very low insert latency and a very low query latency and click house is a system that can allow both of these thing. So it's really like I'm a PM right?
So I like to put things into category and there is slow things right that are designed for large scale and large data processing and there is fast thing which are designed for uh uh for for running query very quickly and kickass is one of the system. So there is two use case that people are using kickass for the first one is real time analytics. So you know you store data into click house and you want to have a query uh to power a dashboard to uh power your application and you want to be extremely quick and this is one of the use case.
The second use case which is also very popular is around observability and you could argue that observability in a lot of scenario is actually a real time analytics problem right you you collect a bunch of data and you want to be able to know what's happening into your system extremely quickly. Uh so this is why a lot of people are actually using click house for real time analytics and we have uh people like uh entropic tesla Netflix that are deploying clicks at very large scale for this exact purposes.

4:31 Performance architecture

So how did we make click house fast? And this is important to understand because uh it goes back to the fundamental of how we develop click house and a few things that we spend a lot of time working on and the first thing that comes to mind is the native click house formats. So this is how click house actually stored data on disk and how click house as a query engine interact with this stored data and we spend a tremendous amount of time so it's important to highlight that it's column oriented very similar to park and uh the way it works is that click house will by default sort your data on disk automatically.
So you specify a specific key and when click will insert data it's going to sort this data in memory and then it's going to write them on disk using your specified spec specific key and when we write this data on disk we actually create a sparse index uh which we load then memory to have like a very low query latency. We can actually use this sparse index to scan through your data efficiently uh and uh skip a lot of data given the index is sparse and your data is solid.
It allows click house to to be extremely fast if you're quering on your on your soding key. So that's one aspect where we spend a lot of time when developing click house. The other one is around uh making sure that click house could have a good insight throughput. So when you insert data into click house it creates data parts. These data parts over time get merged together into large data parts and larger and larger until it reach like a a very large size and then we stop merging them at some point.
But overall click house has this very similar concept than delta or iceberg has where we we have a bunch of data then we compact them we optimize them over time but click house doing do it extremely aggressively. It has like a variety of algorithm to do this merges process and click really tried to optimize this merge algorithm a lot and then there's a lot of feature around this um this nut format that we've developed.
We've implemented a synchronous and synchronous materializer in order to preprocess your data as you insert them into pow or process your data at a specific time interval and that help a lot when you are doing to reduce your query latency when you are quering uh data. We also use kick house a lot to uh develop many different data types because the more data types you have the more various your workload is the more you will need to build internal data structure to support your workload and when you have like a column storage and you want to optimize on top of this uh uh columnar scenario you need to have the corresponding data structure that help you leveraging all the existing index and data structure that you have in clickouts.
So we develop for example JSON object. How do you develop JSON objects in a in a column format? That's a complicated topic and uh we want to make sure that whenever we introduce a data type in click house we support it for its uh native format very well and it's also important to understand that click house so this read by side right so when kick house is being deployed it's deployed in the cluster so you have like multiple nodes each node can read a subset of your data uh when you execute a query and each node actually has multiple CPU that can pro process chunk of data independently and your CPU actually can leverage CND instruction to uh uh to speed up the processing of this row.

7:41 Ecosystem integration

So we actually build a vectorzed query engine that can use a every component of your infrastructure and on top of that because click house is developed on top of object store you need to make sure that uh you have a very advanced caching layer because the worst thing with object store is the latency right and especially the tail latency. So you need to make sure that your query on is very good to query your object store and then there's a lot of ways of implementing solution for it.
You can have like very aggressi a very aggressive retry mechanism. So you basically have clic always retry reading from specific uh files in your object storage if they don't respond at a certain speed. Uh there's alo the caching. So each compute node in vic come with its own attach SSD where we can actually load parts of the data parts that are stored in your object store. When you insert the data or when you query them so allowing you to instead of going directly to the object store you can actually go through the cache and you have like a very low query latency.
So this is all optimizations that we developed to work on top of our own native format. And at this point in time you are telling me well how does that work on top of my data platform now? And uh this is what I'm going to talk to you about. If you want to learn more about click house we wrote a white paper uh super interesting if you are interested in a computer computer science information retal. It goes into very very detailed explanation about how we build this different data structure that click house is leveraging to uh uh to create its own data format.
But today I'm here to talk to you about how we actually integrate with data platform. So as a OLAP system and click is a system you want to make sure that you integrate with the rest of the ecosystem that your user are using right because we know that is not a single silver bullet that can address any scenario. We know that our customer have Q somewhere right they have like rabbit MQ red pandas cafka though we it's very difficult to work with cfka sometimes we know that our customer and user are using it so we need to make sure that we can integrate with it right we need to make sure that it's easy for user to load data from cfka to click house so we develop query uh we developed you know table engine for that so just a simple table that connects to your cfka topic and can read the data from your cafka topic and insert it automatically into click house just via SQL query.
So that's one thing that we do, right? But then there is people that are using OLTP database on their right. There's a lot of scenarios where you need OLTP database and you must uh store your data in a transactional way and click alo integrate with this OLTP database. We have like CDC pipeline to make sure that we can actually read your data into your OLTP database and load them into click house you know and keep these two table in sync.
Making sure that you can use your MySQL uh posgrsql database for transactional workload but when you need to do analytical workload you can use a proper system that will give you the performance that you want. And of course like one of the biggest uh workload is probably data lakeink but today like every company probably is using S3 to store data. It could be in iceberg format it could be in delta format it could be in S3 it could be in park file it could be in JSON file.
Some people store CSV. So we need to make sure that we can actually also integrate with this ecosystem. And here it becomes a bit more complicated because you need to start supporting many different data formats right. So you support clickoff native format and then you need to support packet file and then you need to support JSON file and CSV file and then you need to start like developing a complex query on J and today I'm going to talk to you about how we did it for uh Delta Lake in particular because I think there is a lot of development that has been happening in that space that made made this integration um very good.
So where to before we developed this integration um I talked with a lot of customers and user and asked them like how do you use click house and data bricks or how do you use click house on your uh delta tables or how do you use click house on your iceberg table and often the architecture that came back was this type of architecture. So depending on the customer like they have different workflow. Some people do dual rights.

12:09 Delta Lake and Unity integration

So they insert both into click house. They insert into their iceberg table or delta table or ud table or pack file and then you know they have like a specific TTL policy for the data in click house. So maybe after two weeks, three weeks the data in kickouts get removed and they keep the data in Delta or iceberg for one year, six months, you know, like depending on the on their requirements. And I think for example, Netflix is doing that at very large scale. and they they did a talk recently where they talk about how they insert like pabytes of data into click house and in iceberg and then when they are using click house and when they using iceberg for the different type of query it's a super interesting talk if you are interested I highly recommend it and then you can actually use kickhouse the good thing is that if you support correctly your data lake you can actually use kick house for your storage but also to send query to your you know data lake if need be right so maybe you know your team is always using click house but sometimes if they have like I don't know uh they need to do uh RCA or they need to access like cold storage then they can still do it from the same system.
So when we u when we come to that conclusion we thought about oh okay so we need to support Delta Lake we need to support iceberg and we need to support uh park. So we started like building all this implementation and funnily enough like us as a company click house we're a company we didn't initially invest it's actually our user that did it right because they're using uh delta legs they're using iceberg and they want to use both of them at the same time um so the first pull request uh was done by the community member and it was initially introducing delta and udi tableine that could you know read delta and udi table on top of s3 that's great Right?
And then people start using it. It's merged. It's released. People use it and they have fun and then it become more popular and as it become popular more popular like things start to break right um well delta change its protocol. So now the table and join doesn't read properly my table and drive right or like I'm using a specific delta feature that is not supported by my table and join because the development of the table and doesn't follow the evolution of the data protocol and things get complicated because now you have the same problem with iceberg you have the same problem with park and you have all the same problem with all these different formats.
So how do you tackle this problem? Well um we actually um tackled this problem via two different road. The first one was how do we make this you know delta table iceberg table discoverable easily from click house and we do that via the integration with the unity catalog. I'm going to walk through how how we did it with click house and some of the gotcha that we um that we had. But the most important and most interesting topic is probably around how you how we start supporting better delta table right how do we avoid things to break randomly once the delta protocol evolve and we tackle this problem using something called the delta kernel I don't know if anybody is familiar with the delta kernel yeah a little bit um so for people that don't know about the delta kernel is that it's an initiative that was open sourced that was led by data bricks and Microsoft.
Um it's a project that basically aim at providing library that you can embed into your uh software that will basically understand the delta protocol for you. So it will read as a delta protocol. It will parse the file correctly and it will provide you with the right rows to read. And the complex thing is how do you do it for any system? because everybody has their own program, everybody has their own optimization. Um, so you need to have an implementation that is uh good enough to be for everybody.
Uh, but that is not too specific, right? Because if you start like, you know, specifying, oh, you need to read a park file, then all these, you know, softwares that are optimized for park reading, you know, cannot use your software because they're like, yeah, but your your park reader is suboptimal. So it comes in two flavor like the delta kernel comes in two flavor. There is one java library and there is a rest library and basically the way it works is that it provide you with a set of APIs uh that can help you plug your own um readers and you can plug your and you can indicate to the delta kernel which table you want to read from.
Right? And you can you can have basically an interface between your client library and the delta tables itself. And we the tricky thing is that we are a C++ database and our library there is no C++ library. There is no C++ library. Generally speaking nobody likes C++. It seems we had the same problem with iceberg. We had to create our own library. And for the delta kernel it comes only Java and R. So what we had to do now is we had to embed like a R library into a CS software and it's it's not trivial especially with when you have like a very complex CI/CD as click house it it it's getting airy.
Uh but in the end we did it and it actually simplify a lot of your story because using the delta kernel we can actually pass to the delta kernel our own park reader our own JSON reader and the delta kernel is going to leverage this uh this reader to process the del the delta protocol and it's just going to give us objects that we just need to iterate on to get the uh uh the correct row that we are quering. So basically the you can see as the delta kennel just providing us blackbox uh with a way for us to iterate on this black box and get the expected result.
So we integrated with the delta ko and it actually helped us a lot develop uh uh the integration with delta lake because historically speaking it was developed by the community but now you have like all this evolution of the delta protocol and then we need to start maintaining it. It was very challenging for us. uh with this delta protocol I can have engineers that just integrate with the library and make sure that we provide the right reader to it and just you know we just need to support a set of APIs rather than reimplementing everything from scratch which help us a lot in term of supporting a large ecosystem.
So I think we integrated with the delta kernel maybe a couple of months ago and from there we were able to quickly start supporting like some some delta specific feature like partitioning printing schema evolution and then we are on on the on the way to start supporting right to delta table using the delta kernel um and then we'll expand to uh statistic pushdown and time travel and it make it much more easier and faster for us to start working on top of this integration.
So that's one integration that we did with uh the delta kernel. The second integration that we did was around the uh unity catalog. So once you can read a delta table and if you take some probably like your old datab bricks user I don't know how many delta tables you have in your environment it's probably a lot right and you if you want to plug an external system to uh your data bricks environment you want to be easily uh you you want to be able to easily use any table in your environment so by integrating with a unity catalog is basically just a layer an API layer that can tell us what table do you have in your environment what what table do you have access to first of all and which table you can read and when you read it it can actually provide you uh with a path to where these data are stored it can provide you with a credential to read these corresponding tables.
So it help us with ex exploring your data directly from click house or directly from any query engine that can integrate with the unity uh rest specification and the good thing is that unity is open source now. So the specification are also open source and it's make it much more easy for any query engine to actually integrate with uh with uh with the delta table or uniform table. So today we are supporting two APIs. We are supporting the uh API for the delta table and we are also supporting the APIs for the uniform table.
So data bricks can create uniform table which add a layer of metadata for iceberg support. So you can actually inter interact with the unity catalog using iceber client directly which allows you to uh to use more query engine on top of your delta tables. And one of the good things that the uh Unity catalog is implementing is around credential vending. You know in a fully managed environments like data bricks if you're using data bricks to read your data it make security much easier right because you are in control of the world chain of stress about who can access what data because you control the query engine you control the storage.
Um so you make it very easy to know who can access your data when you start opening it to external query engine then the chain of trust is broken right because an external core could have like logic you know that you don't trust typically and the way it works in uh data bricks and more most more you know like with unity catalog but it works also with other um I will say rest catalog is that it will have the concept of credential bending.
So when your query engine is connecting to unity catalog basically what's going to happen is that your u unity catalog is going to generate uh specific tokens based on the permissions that uh you gave to the user. So your user is going to say oh I want to read this table. The unity catalog is going to say check the permission of the user. It's going to say okay you can read this table and I'm going to generate a token for you.
So you can actually use this token to read only specific file in your object store and it make it secure and it makes sure that you know an external program only have access to the data that you know it has a right to access. So overall if you with this two integration it allows you to integrate with your data links in many different scenario right. So the first scenario and I will say like this is for the the people that don't like necessarily open table formats is like using park file.
So a bunch of people are using park file or they're using delta tables and they're just doing append only scenario and they're not leveraging the metadata. They're just leveraging the park files directly. And with clickas we have a park reader. You can read your park file directly from your uh uh from your tables. The second scenario and this is when you want to optimize for performance is you actually read the packet file but you don't uh query it directly you actually insert it into a clickant format.
So you are going to insert it and you are going to benefit from all the performance I've highlighted you know previously the click format all the optimization all the data type that comes with um and this is when really you want to have like a very low query latency on top of your object store the second the first scenario is a CDC pipeline so instead of just doing a one time batch loading you actually in kouse you can create a a table called S3Q or Aure Q which is basically a table engine that will connect to your S3 buckets or Azure buckets and it's going to monitor for new file and whenever it's your new file it's going to load it into click house and then like you have like a very no code CDC pipeline on top of your delta or iceberg table or packet file and then you have like more complex scenario where you start going through the iceberg and delta table directly so you can query your delta table directly because it's going to be able to fetch the version ins of your uh of your tables or the latest metadata and it will be able to process your table and the last one which is going through the catalog which is the simplest and I will say the most permissive one.
So I'm going to show you some of the benefits that uh it brings to actually have this integration of click house with your unity catalog. So first of all maybe I'm going to spend I'm going to show you how you can do that in Click House. So Click House I need to stop sharing. I'm going to pull out a terminal. So that's very very interesting. So Click House as a client terminal. So I'm connected to my uh cloud instance right now and yesterday night I created this connection with my catalog.

25:48 Demonstration and summary

So I can actually already start uh quering I don't see my it's going to be interesting. So I can actually show create a show there's no subtitles show database. Okay. Did the query run or Oh, so I actually you can see the um the different database that I have. So I have two database. So more than two database. I have a default database which is a default database in click house where you can actually store your click house data into click format.
Then you have like the system database uh which are system database basically containing like information about uh all the metrics that click generates for the purposes and then you have these two database which are the ones that are interesting for us. So we have unity demo and unity uniform. So that's two different catalog that I've created like integration with my catalog. one which is monitoring the endpoint for my uniform tables and the other one which is monitoring my endpoint for my uh delta table.
So I can actually check the Unity demo one. So I can do a show crates. Yeah, maybe that's what I'm going to do. Okay. Okay, perfect. Thanks. Thanks. So, we can do show databases. Show database and then uh I can select my uh Unity. Let's take Unity demo. So actually this is how I created my database. So you can see here it's very simple you know you create a database Unity demo you see the engine so catalog as you can see this is my databicks environment with a specific uh endpoint that I'm using to uh connect to my environment and then you can specify your uh warehouse uh so you can define a default one or you can use whatever warehouse you have and then you specify your catalog credential.
So here we support like both uh personal access token or you can specify your principal uh principal key and principal secret and then create token and then you can specify catalog type. So we support different type of catalog. We support unity. We support blue. We support polaris rest catalog every catalog you can imagine even I' met store. So you can actually create different type of catalog and integrate the same way.
And then using this database you can actually start querying it uh query your delta table directly. So I'm going to show you very something very quickly which is on my other screen. Um and this is something interesting is that using u so you can see here it's connected to my catalog you can list all my table but something I want to show you is around AI agent as well. So click house has agent AI and we have like agents in our next to each database and you can actually use it to ask him to investigate or analyze your data for you.
So yesterday night I actually asked the agents well I have tables somewhere I didn't specify if they were in data bricks if they were in my ads environment I say I have data about stack overflow and I've collected a bunch of posts from stack overflow and I want to know what's the trend about click house for example and my agent is going to receive this request and it's going to start looking up at all the tables that I have in click house and because we have all this catalog integration you can actually connect to your datab bricks environment and also read the data that you have in your database environment.
So if you have like your catalog defined in click house, your agent will be able to find this table and actually query them to start generating analytics on top of it. So here for example, it's actually uh telling you what it's trying to do. is going to start like trying to display all the tables that you have in your environment and based on the metadata that you have in your table is going to say oh I see that you have uh this table about stack overflow and it's in this database which is actually the unity catalog and then it's going to be able to fetch the information directly in my datab bricks environment and eventually federate them with the data I have in clickouts so I have like a centralized space to actually query multiple system and given clickouts can also integrate with your MySQL or postgraql database it can really open up the ecosystem and the data rich that your agents actually uh have, right?
And the good thing is that you can also control what the agent has access to because you can control the permission at the catalog level. If you don't want the agent to see some data, you can just not giving the agent the right permission to have access to the data you won't want them to access. So you can see them actually running queries and thinking he actually sometimes fails some queries for some reason. Uh but then he finds the right table and he start like doing SQL queries.
He's doing a bunch of SQL queries. I didn't want to do it live because you know like seeing a bots running queries is not the most thrilling thing. So I asked him to generate a report and at the end you can see that directly from a single place he was able to federate all the data they have that he has access to and kind of generate me a small summary of you know uh where click is mentioned in stack overflow. So it checks the body, it checks the tags, it checks the title of the question on Stack Overflow and then it can generate graphs.
So this is the number of comments that mention Stack Overflow. This is a post that mention Stack Overflow. This is the answers that mention clickout sorry and it kind of generate your graph and you can actually start giving you analytics. Um so uh it's it's good. So it say for example there is more questions than answer but most of the time there is at least one uh answer per question which is good. It means that there's a healthy community that actually help our clickoff user even in the open source.
Um so yeah so this is um so you can see at the end it give you like some some key takeaway that you can have what what type of tag you know kickass is using alongside unsurprisingly you know like the most popular one is SQL you know kickass is a SQL database so yeah you can see there is database python docker click client which is the one I showed you just before like from the terminal um so yeah you can see how kickass is kind of connected to the uh ecosystem and then it give you like final key takeaways.
So that's just a way of showing you that you know like by building like good integration with um specific table formats and by building integration with cataloges that kind of display and give you access to more data then it make any workflow better including agentic AI that can actually scan a much larger uh data footprint and giving you the the insight that you might want. Great. So I think that's my last slide. Uh let me go to presentation mode.
Okay, I just did it. And yeah, if you have any question, I would love to take them. There is a question.

Learn more about the Databricks Data and AI platform.

The information provided herein is for general informational purposes only and may not reflect the most current product capabilities or configurations.