Building an Analytics Pipeline with ClickHouse
Our team highly values scientific user data analysis and experiments. When we started thinking about data analytics about three years ago, we realized that finding an analytics platform that was easy, fast, powerful, and affordable all at once was much harder than we thought. We didn't have any teammates specializing in data analytics or building related systems. Using a commercial solution was a huge burden, as it was expected to cost tens of thousands of dollars a year given Alarmy's active user count.
Choosing Firebase + BigQuery as our main analytics platform was an obvious choice for us at the time. After being acquired by Google, Firebase was reborn as a comprehensive mobile/web backend. Best of all, providing an analytics feature for free was very appealing. Moreover, Firebase supported a feature (which is paid) that allowed us to analyze raw event data with BigQuery. This nicely complemented Firebase's own lack of robust analytics capabilities. For a while, up until mid-2017, we used BigQuery as our primary analytics tool.
Why did we decide to drop BigQuery and look for alternatives?
BigQuery is powerful enough for querying data. However, we felt that BigQuery couldn't quite meet our needs for the following reasons.
1. Writing queries wasn't easy
This is due to the DB structure where user properties, events, and parameter info are nested. Our team doesn't have a dedicated data analyst. All teammates spend their own time conducting analyses in their own ways. Therefore, writing queries and interpreting the results needs to be as easy as possible. In the case of a teammate whose main position is a data analyst, setting aside basic expertise, they can handle a certain level of learning curve. But for teammates in other positions, if the analytics process becomes even slightly difficult, it becomes very hard to approach. The high difficulty of writing BigQuery queries was a major stumbling block for our team to freely try out analytics.
2. Lack of real-time performance
In terms of processing speed, BigQuery isn't exactly lacking. But our main use case wasn't running complex and heavy analyses in the background; it was OLAP (Online Analytical Processing) to do simple analyses in real-time. BigQuery simply wasn't real-time enough.
3. Cost
BigQuery bills you proportionally to the amount of data referenced during a query. For our service, the data volume is not small, so we expected it to cost quite a bit to query freely without worrying. In this aspect, we wanted to be able to conduct analyses on our own server without the burden of usage-based billing if possible.
As the service matured, these downsides became a fatal bottleneck to our rapid growth. All teammates deeply felt the need to build a smooth analytics pipeline. From that point on, we set out to build an analytics pipeline tailored to the characteristics of our service and the analytics needs of our team.
A New Data Analytics Pipeline
Several alternatives were considered. Just in time, we had the opportunity to use Azure to our heart's content thanks to sponsorship support from Microsoft. This added more momentum to building our own server rather than using a commercial service. Ultimately, we built a pipeline where we collect data with Firebase, migrate it to ClickHouse, and analyze it with Zeppelin.
Let's look at the four key components.
1. Data collection using Firebase
Instead of building and operating a data collection tool ourselves, we decided to use Firebase. This was because we could export raw data via BigQuery and move it to our DB. Investing time and manpower into building a separate system that collects user data as stably as Firebase didn't seem right for our team, when we barely had enough time to focus on the product itself. Of course, moving data out of BigQuery incurs an extra cost, but it was at a level we could handle.
2. ETL: Data flattening
Our team built and uses a sort of ETL (Extract-Transform-Load) program that moves data from Firebase (BigQuery) to ClickHouse. This process is very important in that it doesn't just simply move data, but processes and stores it in the way we want.
In the case of BigQuery data, as mentioned earlier, various parameters and user data are stored nested even within a single event record. Currently, BigQuery's Firebase data storage schema has improved a little bit (since June 2018), but when we first built this pipeline, the schema was far more complex.
To resolve this complexity, we flatten all nested data during the ETL process. Data made of 2 to 3 layers unconditionally becomes 1 layer. Take event parameter data as an example. A nested parameter with the key alarm_id and an integer value of 35 is converted into a single field called alarm_id_i. In the name alarm_id_i, the preceding alarm_id is the parameter key, and _i indicates the type of the parameter value. Now when querying related to parameters, there's no need to use functions like UNNEST in BigQuery or explode in Spark. As long as you know the parameter name and type, you can query it at the same level as any other field. Instead, hundreds of new fields are added. In the case of traditional row-oriented databases, this would have been a problem, but for a column-oriented database like ClickHouse, it's no problem at all.
3. ClickHouse
ClickHouse is an open-source column-oriented DB made by Yandex for OLAP. It is not that well known. While trying out various column-oriented DBs, its incredibly fast query processing speed caught our eye, so we decided to use it.
Pros
Easy setup (including cluster and replication): We were able to insert our data and run it in a very short amount of time
Very fast processing speed: It handles everyday aggregation tasks in near real-time
Cons
Instability: It's a project with a short history and ongoing changes. It isn't widely used yet, so it hasn't been fully proven.
Lack of third-party support: Support from DB management tools or analytics tools is lacking. For example, it's not easy to use ClickHouse as a data source in Tableau.
Here is an example of querying the number of daily new users starting from August 22nd in ClickHouse. ClickHouse figured it out in 0.071 seconds, whereas running the same query in BigQuery takes over 3 seconds.
What about Spark? or XXX?
While looking for alternatives to BigQuery, several other solutions besides ClickHouse came up for consideration. Notable ones included Druid, Spark, etc. How ClickHouse managed to beat all the candidates is something I'll cover in a separate post later. To put it briefly, what we liked the most was that it ran the queries we mostly work with very fast with minimal setup and tuning. As mentioned earlier, our team doesn't have a data analyst or a teammate specializing in building data analytics infrastructure. So we couldn't spend a lot of time on setup and tuning just to try out a system without even knowing how the results would turn out. For example, if we were to use Spark, perhaps if we tuned it well to fit our data characteristics and available computing resources, it might have shown better performance than ClickHouse. (I don't know if it actually would. Just take it as a simple example.) However, if ClickHouse already met our requirements to some extent, we couldn't just pour a ton of time into tuning Spark merely for the possibility that it might be slightly better, right?
4. Zeppelin
Zeppelin is an open-source tool that lets you interactively execute queries and visualize the results in a notebook format on the web. It's known for supporting Spark well. It supports not only Spark but also various languages/execution environments through an Interpreter plugin system. Even within a single notebook, you can switch interpreters for each Cell, which is the unit of execution. You can execute and view various code snippets right on the spot, including Python, Spark, MySQL, and Bash shell. ClickHouse can also be used as an interpreter.
Pros
Easy usage: The notebook interface allows even beginners to easily execute queries and record results
Web-based, making online collaboration and result sharing easy
Supports a variety of Interpreters
Free
Cons
Relatively weak visualization tools
Since it's not a commercial service, you have to install and operate it on a server yourself, and there is no special user support
Setup
We collect over 100 million events from more than a million users every day. Currently, over 300 days' worth of events—which roughly calculates to tens of billions of events—are stored on our ClickHouse DB server. We distribute the storage and run queries by forming a cluster made up of 3 nodes. Each node is an Azure VM with Ubuntu 16.04 installed, packing a performance of 64GB~128GB RAM and a 16~32 core CPU.
The Zeppelin site is open to all teammates, so anyone can easily log in and run queries in real-time. While not everyone is running queries intensively every single moment, the setup is more than capable of handling it when people fire off queries concurrently to some degree. If it ever feels like the ClickHouse server is struggling, we'd have to solve it by adding nodes or upgrading the performance.
Cost
To be fair, maintaining the cluster setup above does incur some cost. In our team's case, thanks to the help of the Azure Sponsorship, the actual cost was drastically reduced. If you want to build your own ClickHouse cluster, you should carefully calculate the cost differences compared to hosted data warehouses like BigQuery or Redshift, or other commercial analytics platforms.
Wrapping up
Once the ClickHouse-based analytics pipeline was up and running, our analytics speed became 10 times, no, over 100 times faster than before. This is not just about raw query processing speed, but also the reduced time it takes to write a query, and the fact that more people can write queries concurrently. We were also able to perform A/B testing, cohort analysis, and more without a hitch.
We even started having a session called DDA (Daily Data Analysis), where all teammates discover new insights through data. The results gathered from DDA actually have a profound impact on our decision making. The new pipeline has established itself as an essential tool for our team's data-driven growth.
If you need an OLAP DB that shows decent performance and can be up and running quickly for a small-to-medium-sized service, I highly recommend giving ClickHouse a try. Even though ClickHouse is used by large companies like Yandex and Cloudflare, its validation in an enterprise environment is still somewhat lacking compared to other widely used solutions. Either way, a cautious approach to prepare against data loss is necessary.
Lastly, I want to express my gratitude to the ClickHouse team, the Zeppelin team, and all the other contributors who built such amazing open-source software.
If you are interested in DelightRoom, where we make data-driven decisions and value the technical challenges the team needs, click the link below regardless of your role!
References / Further Reading
HTTP Analytics for 6M requests per second using ClickHouse (Cloudflare)
Comparison of the Open Source OLAP Systems for Big Data: ClickHouse, Druid and Pinot
ClickHouse in Real Life. Case Studies and Best Practices, by Alexander Zaitsev