Supercharge Your Application Logging with ClickHouse: Part — 1

When I first started learning programming, I created various applications — from simple arithmetic tools to complex desktop applications for image processing. Initially, I used print statements to trace and debug errors. However, I faced a turning point when a couple of friends tried to use my application, and it failed on their devices. The issue was that the application was bundled, so print statement outputs were not visible. This experience taught me the importance of proper logging systems and introduced me to the world of log files.

What is log(s)?

A log is a formatted or unformatted record containing information about the activities being carried out in an IT system. A log can exist in a file or as a database entry. This can include information that covers H4W. How, Who, When, What & Where changes were made? Thus, logs are important for troubleshooting IT systems and keeping track of user activity.

Note:- To know more about types of logs and how they are generated, check this post by Arfan Sharif on CrowdStrike.

A brief into Application Logging

Application logging ensures the IT teams can use it typically for investigating outages, troubleshooting bugs, or even analyzing security incidents. Thus application log(s) can be helpful for IT team(s) to determine the root cause of incident(s).

Combination of following articles will help you attain better understanding of application logs:-

1. Application Logging: Definition, Examples, and Best Practices by Coralogix
2. Writing Best Practices For Application Logs by Heroku Dev Center
3. Logging Cheat Sheet by OWASP
4. Logging 101 by Dmitriy Ryaboy

What is Clickhouse?

Clickhouse is proven to be faster than most traditional data warehouses and databases. It is most often used to establish a set-up that allows real-time queries on large datasets at an affordable cost.

But wait, what type of data processing system does Clickhouse use?

Clickhouse is an OLAP database. OLAP stands for “online analytical processing” which contrasts with OLTP aka “online transactional processing”. If you haven't figured it out now, let me tell you that OLAP is supposed to perform significantly better compared to OLTP for analytical workloads, which is common with large datasets.

What is a Columnar Database?

A columnar database (aka column-oriented database management system) stores the data of each column independently, which allows reading data from disk only for the column(s) associated with any given query. The only cost is the operations that affect whole rows become proportionally more expensive.

Key advantages of columnar databases:-

1. Queries that use only a few columns out of many.
2. Aggregating queries against large volumes of data.
3. Column-wise data compression.

Query execution in Traditional row-oriented DB

Query execution in Columnar DB

A columnar database is ideal for analytical applications because it can accommodate many columns in a table without incurring costs for unused columns during read queries. In contrast, traditional OLTP databases read all data during queries since it is stored in rows. Column-oriented databases are specifically designed for big data processing and data warehousing, often scaling natively with distributed clusters of low-cost hardware to enhance throughput.

Clickhouse v/s Others

When it comes to application and audit logs, the general perspective is to opt for tools like

1. Coralogix
2. Datadog
3. Splunk
4. Newrelic
5. AWS Cloudwatch and many more

Although these tools are hassle-free their cost increases exponentially with an increase in scale. Thus, self-hosted and self-managed solutions can be used like

1. ELK stack — Elasticsearch, Logstash & Kibana
2. Storing logs in the form of files in S3 or other centralized storage

But the issue here is that the ELK stack is compute-hungry with scale, impacting the cost. And with log files in storage, it is not very accessible to query logs frequently and visualize data.

In the upcoming article, I’ll show you how to set up ClickHouse as a logging warehouse, integrate it with tools like Fluent Bit, and visualize data to unlock its full potential.

Have questions or insights on using ClickHouse for logs? Share your thoughts below, and stay tuned for Part 2!