OLTP vs OLAP


In this guide, we’ll explore columnar databases, column stores, column-oriented databases, column-wise databases, or “insert your favorite acronym” 
How do they differ from row-based databases? What are they good at?  What are the advantages of using a column store?

We’ll answer these questions and more, but first, a brief history lesson.

## A brief history of columnar databases

The idea of storing data in columns is not new. In 1985, GP Copeland and SN Khoshafian introduced the concept in a paper titled [A decomposition storage model](https://scholar.google.com/scholar?hl=en&as_sdt=0,5&q=G.+P.+Copeland+and+S.+Khoshafian.+A+Decomposition+Storage+Model.&btnG=) (DSM).

The DSM proposed storing data in binary relations, pairing each attribute value with the record's surrogate, effectively organizing data by columns rather than rows. The authors argued that this approach, while requiring more storage space overall, offered potential advantages in simplicity, generality, and retrieval performance, especially for queries involving a subset of attributes.

By 1999, MonetDB had implemented the ideas from the DSM paper and showcased their efficacy. The paper "[Database architecture optimized for the new bottleneck: Memory access](https://scholar.google.com/citations?view_op=view_citation&hl=en&user=DCIZE1kAAAAJ&cst[…]&sortby=pubdate&citation_for_view=DCIZE1kAAAAJ:u5HHmVD_uO8C)" by Peter A. Boncz, Stefan Manegold, and Martin L. Kersten details their experience building MonetDB and offers insights into their design choices.

The authors emphasized that their early intuitions about the importance of vertical fragmentation in improving memory access were later validated through experimentation and detailed performance modeling. Their work demonstrated that a database architecture meticulously designed around memory access considerations can yield substantial performance improvements, even in the face of a widening gap between CPU and memory speeds.

In the years since then, further innovations, such as [vectorized processing](https://www.cidrdb.org/cidr2005/papers/P19.pdf) and data compression techniques, have made column-based stores even more useful for large-scale analytical processing.

## Row-based vs. column-based

As column stores evolved from theoretical concepts to practical implementations, their fundamental difference from traditional row-based databases became increasingly apparent. 

This distinction in data organization is at the core of why column stores excel at specific queries while row-based systems remain preferable for others. Comparing their data layout with traditional row-based databases is essential to understanding the strengths and weaknesses of column stores.

In a row-oriented database, consecutive table rows are stored sequentially, one after the other. This layout allows for quick retrieval of rows, as the column values of each row are stored together.

In a column-oriented database, tables are stored as a collection of columns, i.e., the values of each column are stored sequentially, one after the other. This layout makes it harder to restore single rows (as there are now gaps between the row values), but column operations such as filters or aggregation become much faster than in a row-oriented database.  

> In a column store, column operations such as filters or aggregation are faster than in a row-oriented database.

The diagram below shows how some weather data would be stored in a row-based and column-based database:

![](/images/engineering-resources/0_columnstore.png)

In the row-based approach, all the values for a given row are adjacent, whereas in the column-based approach, the values for a given column are adjacent.

The row-based approach works better for single-row lookups. The column-based approach is preferable for executing analytics queries that aggregate or filter a few columns, especially when working with large datasets.

## When should I use a column store?

Use a column store when you want to run queries that aggregate and filter data across a few columns but lots of rows. 
These datasets will typically contain a lot of columns, but we’ll only be touching a subset of those columns in each query.

For example, take the [real-world anonymized web analytics dataset](https://clickhouse.ac.cn/docs/en/getting-started/example-datasets/metrica) that contains 100 million rows and has the following columns:

```sql
SELECT groupArray(name) AS columns
FROM system.columns
WHERE (database = 'metrica') AND (`table` = 'hits')
FORMAT Vertical;
```

```sql
columns: ['WatchID','JavaEnable','Title','GoodEvent','EventTime','EventDate','CounterID','ClientIP','RegionID','UserID','CounterClass','OS','UserAgent','URL','Referer','Refresh','RefererCategoryID','RefererRegionID','URLCategoryID','URLRegionID','ResolutionWidth','ResolutionHeight','ResolutionDepth','FlashMajor','FlashMinor','FlashMinor2','NetMajor','NetMinor','UserAgentMajor','UserAgentMinor','CookieEnable','JavascriptEnable','IsMobile','MobilePhone','MobilePhoneModel','Params','IPNetworkID','TraficSourceID','SearchEngineID','SearchPhrase','AdvEngineID','IsArtifical','WindowClientWidth','WindowClientHeight','ClientTimeZone','ClientEventTime','SilverlightVersion1','SilverlightVersion2','SilverlightVersion3','SilverlightVersion4','PageCharset','CodeVersion','IsLink','IsDownload','IsNotBounce','FUniqID','OriginalURL','HID','IsOldCounter','IsEvent','IsParameter','DontCountHits','WithHash','HitColor','LocalEventTime','Age','Sex','Income','Interests','Robotness','RemoteIP','WindowName','OpenerName','HistoryLength','BrowserLanguage','BrowserCountry','SocialNetwork','SocialAction','HTTPError','SendTiming','DNSTiming','ConnectTiming','ResponseStartTiming','ResponseEndTiming','FetchTiming','SocialSourceNetworkID','SocialSourcePage','ParamPrice','ParamOrderID','ParamCurrency','ParamCurrencyID','OpenstatServiceName','OpenstatCampaignID','OpenstatAdID','OpenstatSourceID','UTMSource','UTMMedium','UTMCampaign','UTMContent','UTMTerm','FromTag','HasGCLID','RefererHash','URLHash','CLID']
```

This table contains over 100 columns, but we’d usually consider only a few in each query. For example, we could write the following query to find the most popular mobile phone models in July 2013:

```sql
SELECT MobilePhoneModel, COUNT() AS c
FROM metrica.hits
WHERE
      RegionID = 229
  AND EventDate >= '2013-07-01'
  AND EventDate <= '2013-07-31'
  AND MobilePhone != 0
  AND MobilePhoneModel not in ['', 'iPad']
GROUP BY MobilePhoneModel
ORDER BY c DESC
LIMIT 8;
```

This query demonstrates several characteristics that make it ideal for a column store:

1. Selective column access: Despite the table having over 100 columns, the query only needs to read data from `MobilePhoneModel`, `RegionID`, `EventDate`, and `MobilePhone`.  
2. Filtering: The `WHERE` clause allows the database to eliminate irrelevant rows quickly.  
3. Aggregation: The `COUNT()` function aggregates data across millions of rows.  
4. Large-scale processing: The query operates on a dataset of 100 million records, which is the lower bound of where column stores start showing their value for analytical queries.

You can [try this query on the ClickHouse SQL Playground](https://sql.clickhouse.com/?query=U0VMRUNUIE1vYmlsZVBob25lTW9kZWwsIENPVU5UKCkgQVMgYyAKRlJPTSBtZXRyaWNhLmhpdHMgCldIRVJFIAogICAgICBSZWdpb25JRCA9IDIyOSAKICBBTkQgRXZlbnREYXRlID49ICcyMDEzLTA3LTAxJyAKICBBTkQgRXZlbnREYXRlIDw9ICcyMDEzLTA3LTMxJyAKICBBTkQgTW9iaWxlUGhvbmUgIT0gMCAKICBBTkQgTW9iaWxlUGhvbmVNb2RlbCBub3QgaW4gWycnLCAnaVBhZCddIApHUk9VUCBCWSBNb2JpbGVQaG9uZU1vZGVsCk9SREVSIEJZIGMgREVTQyAKTElNSVQgODs&chart=eyJ0eXBlIjoicGllIiwiY29uZmlnIjp7InhheGlzIjoiTW9iaWxlUGhvbmVNb2RlbCIsInlheGlzIjoiYyJ9fQ&run_query=true), hosted at [sql.clickhouse.com](http://sql.clickhouse.com). 
The query processes 100 million rows in under 100 milliseconds, and you can explore the results as a table:

![](/images/engineering-resources/1_columnstore.png)

Or as a chart:

![](/images/engineering-resources/2_columnstore.png)


## When should I not use a column store?

While column stores excel in certain scenarios, particularly in analytical workloads involving large datasets, they are not a one-size-fits-all solution. Understanding their limitations is crucial for making informed decisions about database architecture. 

Let's explore situations where there might be better choices than column stores.

### Row-based lookups and OLTP Workloads

Column stores are designed for analytics queries that typically aggregate or scan a few columns across many rows. However, they can be suboptimal for row-based lookups, common in [Online Transaction Processing (OLTP) systems](https://clickhouse.ac.cn/engineering-resources/oltp-vs-olap).

Consider our web analytics dataset example. If most of your queries resemble this:

```sql
SELECT * 
FROM metrica.hits 
WHERE WatchID = 8120543446287442873;
```

A column store would need to:

1. Read the `WatchID` column to find the matching row  
2. Fetch data from every other column for the matching row  
3. Reconstruct the full row(s)

This process can be inefficient, especially if you're fetching many columns. In contrast, a row-based store would have all the data for a single row stored contiguously, making such lookups much faster.

Real-world example:  E-commerce platforms often need to retrieve all details of a specific order quickly. This operation is much more efficient in a row-based store.

### Small Datasets

The benefits of column stores often become apparent only at scale. When dealing with smaller datasets (e.g., millions of rows or less), the performance difference between column and row stores for analytical queries might need to be more significant to justify adding another database.

For instance, if you're analyzing sales data for a small business with a few thousand monthly transactions, a well-indexed row-based database might perform satisfactorily without needing a column store.

### Transactions

Most column stores don't support ACID (Atomicity, Consistency, Isolation, Durability) transactions, which are crucial for many business applications. If your use case requires strong transactional guarantees, a traditional row-based RDBMS would be a more suitable choice.

For instance, a banking system processing account transfers must ensure that debits and credits are applied atomically across accounts. This is typically easier to achieve with row-based, transactional databases.

## Advantages of using a column store

Using column stores has two main advantages: query performance on large datasets and efficient data storage. 
These benefits are particularly valuable in data warehousing, business intelligence, and large-scale analytics scenarios.
Let’s learn about the techniques used to achieve this.

### Efficient storage

At the start of this article, we learned that column stores store data from the same column next to each other. This means that identical values are often adjacent, which is [perfect for data compression](https://clickhouse.ac.cn/docs/en/data-compression/compression-in-clickhouse). 

> Column stores are perfect for data compression.

Column stores leverage various encoding and compression techniques:

1. Dictionary encoding: Replaces repeated string values with integer IDs, dramatically reducing storage for columns with low cardinality.  
2. Run length encoding (RLE): Compresses sequences of repeated values by storing the value and its count. For example, "AAAABBBCC" becomes "(A,4)(B,3)(C,2)".  
3. Bit packing uses the minimum number of bits required to represent integers in a given range, which is particularly effective for columns with a limited range of values.  
4. General-purpose compression: For further compression, algorithms like ZSTD, LZ4, and GZIP are applied to these specialized encodings.

For instance, consider a column storing country codes. Instead of repeatedly storing "USA" or "CAN," dictionary encoding might replace these with 1 and 2, respectively. If there are many consecutive "USA" entries, RLE could further compress this to (1, 1000), representing "USA" repeated 1000 times.

The benefits extend beyond mere storage savings. Less data on disk translates to reduced I/O, accelerating queries and data insertions. While decompression does introduce some CPU overhead, the I/O reduction typically far outweighs this cost, especially in I/O-bound analytical workloads.

## Query performance

Column stores truly shine in analytics queries, particularly those involving large datasets. Their performance advantage stems from a couple of factors:

1. Efficient I/O utilization: Column stores can skip vast amounts of irrelevant data by reading only the columns relevant to a query. For instance, in a query like `SELECT AVG(salary) FROM employees WHERE department = 'Sales` , a column store only needs to read the `salary` and `department` columns, potentially ignoring dozens or hundreds of other columns.  
2. Vectorized query execution: The columnar data layout aligns perfectly with modern CPU architectures, enabling efficient vectorized processing. Instead of processing data row-by-row, column stores can simultaneously operate on large chunks (vectors) of a single column. This approach maximizes CPU cache usage and allows for SIMD (Single Instruction, Multiple Data) operations, dramatically speeding up calculations.

### Query performance

Column stores excel at analytics queries. They make efficient use of I/O, as they can quickly skip irrelevant data since they only need to read the columns involved in a query, not entire rows.

The data layout in column stores is also ideal for vectorized query execution, an essential characteristic of how column stores achieve query performance by efficiently using modern CPU architectures.

## Challenges of using a column store

While column stores offer significant advantages for analytical workloads, they also present unique challenges, especially for users accustomed to traditional row-based systems. Understanding these challenges is crucial for effectively implementing and managing a column store database.

### Updates

In row-based stores, updates are straightforward: data is modified in place, and the transaction is complete. However, column stores operate on a fundamentally different paradigm.

Column stores typically organize data into immutable column chunks. This immutability is a double-edged sword: it enables efficient compression and query performance but complicates the update process. The entire process becomes more intricate when a row needs to be updated.

We can’t update a row in place instead, we’ll need to write a new column chunk.

The column store's implementation will determine how the new value is made available to the query engine. The whole column chunk could be replaced, the new chunk could be merged with the existing one, or a lookup table may indicate the appropriate chunk to read for a given row.

This adds complexity to the database internals and means that column stores are generally optimized for inserting or updating records in larger batches rather than making many small updates.

### Denormalization

Historically, column stores were optimized for read-heavy workloads and often lacked efficient join capabilities. This limitation led to a common practice: denormalizing data during ingestion.

Denormalization involves combining data from multiple tables into a single, wide table. While this approach can significantly boost query performance, it comes with trade-offs:

1. Data redundancy: Denormalized data often contains duplicated information, increasing storage requirements.  
2. Update complexity: Changes to denormalized data may require updates across multiple rows or columns.  
3. Data consistency: Maintaining consistency across denormalized data can be challenging, especially in systems with frequent updates.

Modern column stores have improved their join performance, making extreme denormalization unnecessary. However, for maximum query performance, some level of denormalization is often still beneficial. The challenge lies in finding the right balance between normalization (for data integrity and ease of updates) and denormalization (for query performance).

### Knowing your query patterns

In column stores, the physical organization of data can dramatically impact query performance. Understanding your query patterns before data ingestion is crucial.

Key considerations include:

1. Sort keys: Choosing the right columns to sort by can significantly speed up range queries and joins.  
2. Partitioning: Effective data partitioning can enable query engines to skip large chunks of irrelevant data.

For example, sorting data primarily by date could yield substantial performance benefits if most queries filter on date ranges. However, if this isn't considered during initial data loading, achieving optimal performance may require a costly data reorganization process.

## Examples of columnar databases

There are a large number of databases that have column-oriented storage, so we’ll cover just the most popular ones at the time of writing.

As mentioned earlier, MonetDB is the original column store, and it’s still around today. Since then, other column stores have emerged, including SAP IQ, Greenplum DB, Vertica, and more.

Amazon Redshift, Google BigQuery, and Snowflake were released in the early 2010s as cloud data warehouses. They all store data in columns and are predominantly used for internal-facing analytics on large volumes of data.

Apache Pinot, Apache Imply, and ClickHouse emerged in the late 2010s. They can be used for internal-facing analytics but also support [real-time analytics](https://clickhouse.ac.cn/engineering-resources/what-is-real-time-analytics), a prerequisite for databases to serve insights to external users and customers.

In addition, row-based stores like Postgres have columnar add-ons via Citus or Timescale.

## Is ClickHouse a column database?

Yes, [ClickHouse is a column database](https://clickhouse.ac.cn/docs/en/intro). It is available as [open-source software](https://github.com/ClickHouse/ClickHouse) and a [cloud offering](https://clickhouse.ac.cn/cloud) and is the [fastest](https://clickhouse.ac.cn/docs/en/concepts/why-clickhouse-is-so-fast) and most resource-efficient real-time data warehouse and open-source database.

ClickHouse Cloud is used by Sony, Lyft, Cisco, GitLab, and many others. 

You can learn more about the problems that ClickHouse solves in the [user stories](https://clickhouse.ac.cn/user-stories) section.

## Can I use row-based and column-based stores together?

Yes, and this is quite common. A hybrid architecture that used both types of stores might look like this:

### Row-based store for OLTP (Online Transaction Processing)

* Handles day-to-day transactional operations  
* Manages real-time data updates and insertions  
* Ensures ACID (Atomicity, Consistency, Isolation, Durability) compliance

### Column-based store for OLAP (Online Analytical Processing)

* Manages large-scale data analytics  
* Handles analytical queries on historical and real-time data  
* Optimizes for read-heavy workloads and aggregations

Many organizations employ change data capture (CDC) techniques to keep these systems in sync. CDC is a set of software design patterns used to determine and track data changes so that action can be taken using the changed data.

In the [OLAP vs OLTP guide](https://clickhouse.ac.cn/engineering-resources/oltp-vs-olap#oltp-to--olap-with-change-data-capture), you can read more about using CDC to move data between OLTP and OLAP.

Columnar databases explained


Real-time analytics refers to data processing that delivers insights to end users and customers as soon as the data is generated. It differs from traditional or batch analytics, where data is collected in batches and processed, often a long time after it was generated.

Real-time analytics systems are built on top of event streams, which consist of a series of events ordered in time. An event is something that’s already happened. It could be the addition of an item to the shopping cart on an e-commerce website, the emission of a reading from an Internet of Things (IoT) sensor, or a shot on goal in a football (soccer) match.

An example of an event (from an imaginary IoT sensor) is the following:

```json
{
  "deviceId": "sensor-001",
  "timestamp": "2023-10-05T14:30:00Z",
  "eventType": "temperatureAlert",
  "data": {
    "temperature": 28.5,
    "unit": "Celsius",
    "thresholdExceeded": true
  }
}
```

Organizations can discover insights about their customers by aggregating and analyzing events like this. This has traditionally been done using batch analytics, and in the next section, we’ll compare batch and real-time analytics.

## Real-Time Analytics vs Batch Analytics

The diagram below shows what a typical batch analytics system would look like from the perspective of an individual event:

![](/images/engineering-resources/0_rta.png)

You can see that there’s quite a big gap from when the event happens until we process and gain some insight from it. Traditionally, this was the only means of data analysis, and we’d need to create artificial time boundaries to process the data in batches. For example, we might process all the data collected at the end of a day. This worked for many use cases, but for others, it’s sub-optimal because we’re working with stale data, and it doesn’t allow us to react to the data quickly enough.

By contrast, in real-time analytics systems, we react to an event as soon as it happens, as shown in the following diagram:

![](/images/engineering-resources/1_rta.png)

We can now derive insights from events almost as soon as they’re generated. But why is this useful?

## Benefits of real-time analytics

In today's fast-paced world, organizations rely on real-time analytics to stay agile and responsive to ever-changing conditions. A real-time analytics system can benefit a business in many ways. 

### Better decision-making 

Decision-making can be improved by having access to actionable insights via real-time analytics. When business operators can see events as they’re happening, it makes it much easier to make timely interventions. 

For example, if we make changes to an application and want to know whether it’s having a detrimental effect on the user experience, we want to know this as quickly as possible so that we can revert the changes if necessary. With a less real-time approach, we might have to wait until the next day to do this analysis, by which type we’ll have a lot of unhappy users.

### New products and revenue streams

Real-time analytics can help businesses generate new revenue streams. Organizations can develop new data-centered products and services that give users access to analytical querying capabilities. These products are often compelling enough for users to pay for access.

In addition, existing applications can be made stickier, increasing user engagement and retention. This will result in more application use, creating more revenue for the organization. 

### Improved customer experience

With real-time analytics, businesses can gain instant insights into customer behavior, preferences, and needs. This lets businesses offer timely assistance, personalize interactions, and create more engaging experiences that keep customers returning.

## Real-time analytics use cases

The actual value of real-time analytics becomes evident when we consider its practical applications. Let’s examine some of them.

### Fraud detection

Fraud detection is about detecting fraudulent patterns, ranging from fake accounts to payment fraud. We want to detect this fraud as quickly as possible, flagging suspicious activities, blocking transactions, and disabling accounts when necessary.

This use case stretches across industries: healthcare, digital banking, financial services, retail, and more.

[Instacart](https://www.instacart.com/) is North America's leading online grocery company, with millions of active customers and shoppers. It uses ClickHouse as part of [Yoda, its fraud detection platform](https://tech.instacart.com/real-time-fraud-detection-with-yoda-and-clickhouse-bd08e9dbe3f4). In addition to the general types of fraud described above, it also tries to detect collusion between customers and shoppers.

![](/images/engineering-resources/2_rta.png)

They identified the following characteristics of ClickHouse that enable real-time fraud detection:

> ClickHouse supports LSM-tree based MergeTree family engines. These are optimized for writing which is suitable for ingesting large amounts of data in real-time.

> ClickHouse is designed and optimized explicitly for analytical queries. This fits perfectly with the needs of applications where data is continuously analyzed for patterns that might indicate fraud.

➡️ Learn more about how Instacart use ClickHouse for the [fraud detection use case](https://clickhouse.ac.cn/videos/real-time-fraud-detection-with-yoda-and-clickhouse) in the video recording and [blog post](https://tech.instacart.com/real-time-fraud-detection-with-yoda-and-clickhouse-bd08e9dbe3f4).

### Time-sensitive decision making

Time-sensitive decision-making refers to situations where users or organizations need to make informed choices quickly based on the most current information available. Real-time analytics empowers users to make informed choices in dynamic environments, whether they're traders reacting to market fluctuations, consumers making purchasing decisions, or professionals adapting to real-time operational changes.

Coinhall provides its users with real-time insights into price movements over time via a candlestick chart, which shows the open, high, low, and close prices for each trading period. They needed to be able to run these types of queries quickly and with a large number of concurrent users.

![](/images/engineering-resources/3_rta.png)

> In terms of performance, ClickHouse was the clear winner, executing candlestick queries in 20 milliseconds, compared to 400 milliseconds or more for the other databases. It ran latest-price queries in 8 milliseconds, outpacing the next-best performance (SingleStore) which came in at 45 milliseconds. Finally, it handled ASOF JOIN queries in 50 milliseconds, while Snowflake took 20 minutes and Rockset timed out.

➡️ Learn more about how Coinhall uses ClickHouse for the [time-sensitive decision making use case](https://clickhouse.ac.cn/blog/trade-secrets-how-coinhall-uses-clickhouse-to-power-its-blockchain-data-platform).

### Observability

Many organizations run online services, and if something goes wrong, they need to be able to solve the problem as quickly as possible. Real-time analytics enables them to debug issues as they happen, using logs, events, and traces. This use case has high insert workloads and the need for low-latency analytical queries.

LangChain is a popular software framework that helps users build applications that use large language models. They have built a commercial product called LangSmith, a unified developer platform for LLM application observability and evaluation that uses ClickHouse under the hood.  

LangSmith lets users understand what’s going on in their LLM applications and allows them to debug agentic workflows. It also helps developers detect excessive token use, a costly problem they want to detect as soon as possible.

When working with LLM applications, there are invariably many moving pieces with chained API calls and decision flows. This makes it challenging to understand what's going on under the hood, with users needing to debug infinite agent loops or cases with excessive token use. Seeing an obvious need here, LangSmith started as an observability tool to help developers diagnose and resolve these issues by giving clear visibility and debugging information at each step of an LLM sequence.

> We wanted something that was architecturally simple to deploy and didn’t make our infrastructure more complicated. We looked at Druid and Pinot, but these required dedicated services for ingestion, connected to queuing services such as Kafka, rather than simply accepting INSERT statements. We were keen to avoid this architectural complexity,

➡️ Learn more about how LangChain uses ClickHouse for the [observability user case](https://clickhouse.ac.cn/blog/langchain-why-we-choose-clickhouse-to-power-langchain).

There are also many other use cases, including:

* Real-time ad analytics: Give buyers and sellers the ability to slice and dice ad performance data.  
* Content recommendation: Show users fresh and interesting content each time they log in, considering the content they typically consume and interact with.  
* Anomaly detection: In real-time, identify unusual patterns in data that might indicate a problem that needs addressing.  
* [Usage-based pricing](https://clickhouse.ac.cn/blog/openmeter-real-time-usage-based-billing-powered-by-clickhouse-cloud): Precise metering through real-time analytics prevents overages and ensures fair charging in dynamic cloud environments.  
* [Short-term forecasting](https://clickhouse.ac.cn/videos/lyft-user-story): Computing predicted demand using the latest usage data, sliced and diced across multiple dimensions.

## Characteristics of a real-time analytics system

The system we build must have specific characteristics to achieve the benefits and use cases described in the previous two sections.

### Ingestion speed

We’re predominantly working with streaming data, the value of which decreases over time. Therefore, we need a fast data ingestion process to make the data available for querying. We should see a change in the real-time analytics system as soon as it happens. 

There will always be some lag between when the data is generated and when it’s available for querying. We need that lag to be as small as possible.

### Query speed

Once the data is ingested, we must ensure it can be queried quickly. We should aim for web page response times, i.e., a query's time to run and return a result should be sub-second and ideally 100 milliseconds or less.

The system must maintain quick query responses at all times. This should hold even during periods of high-volume data ingestion.

### Concurrency

Real-time analytics systems are often user-facing and must handle many concurrent queries. It’s common for these systems to support tens or hundreds of thousands of queries per second.

These characteristics are typically seen in [column databases](https://clickhouse.ac.cn/engineering-resources/what-is-columnar-database) like ClickHouse.

## Data sources for real-time analytics

Real-time analytics systems are based on events, typically managed by a streaming data platform. These systems act as the source of truth for event data and can handle a high volume and concurrency of data being produced and consumed. The events are stored durably and consumed by downstream systems.

There are many different providers of streaming data platforms, including Redpanda, Apache Pulsar, and Amazon Kinesis, but the [most popular at the time of writing is Apache Kafka](https://www.ibm.com/blog/apache-kafka-use-cases/).

In addition to working with real-time data, we must support slower-changing data, such as product catalogs, customer profiles, or geographic data. This data might reside in another database, on the file system, or in cloud bucket storage, so we’ll need to ingest it from there.

## What is a real-time analytics database?

At the heart of any real-time analytics system is a database that ingests data quickly and serves low-latency queries at scale. These databases are a subset of [Online Analytics Processing (OLAP) databases](https://clickhouse.ac.cn/engineering-resources/oltp-vs-olap) but have some notable differences.

Traditional OLAP databases were designed for batch analytics, which we learned about earlier. They were used for internal-facing use cases where having data refreshed every day or, at best, every hour was sufficient. This approach doesn’t work for user-facing applications with expected sub-second query response time.

Real-time databases must ingest hundreds of thousands or millions of records per second and immediately make this data available to queries, providing users with access to fresh data.

## Why ClickHouse Cloud for real-time analytics?

[ClickHouse Cloud](https://clickhouse.ac.cn/cloud) is a real-time data analytics platform. It offers a fully managed, cloud-based version of ClickHouse and also comes with ClickPipes, an integration engine that simplifies continuous data ingestion from various sources, including Amazon S3 and Apache Kafka. 

You can see the architecture of ClickHouse Cloud in the following diagram:

![](/images/engineering-resources/4_rta.png)

ClickHouse has the characteristics needed in a real-time analytics system, described earlier in this article.

### Ingestion speed

It can comfortably ingest hundreds of thousands and even millions of records per second. Cloudflare was [ingesting 6 million records per second](https://blog.cloudflare.com/http-analytics-for-6m-requests-per-second-using-clickhouse/) as far back as 2018\. It can also ingest data from an extensive range of sources. ClickPipes provides enterprise-grade support for ingesting data from Confluent Cloud, Amazo S3, Azure EventHubs, and more. In addition, ClickHouse also has [integrations](https://clickhouse.ac.cn/docs/en/integrations) with Apache Iceberg, MongoDB, MySQL, AirByte, and more.

### Query latency

[Query performance is a top priority](https://clickhouse.ac.cn/docs/en/concepts/why-clickhouse-is-so-fast) during the development of ClickHouse. ClickHouse Cloud customers typically see [second](https://clickhouse.ac.cn/blog/data-hive-the-story-of-beehiivs-journey-from-postgres-to-clickhouse) or [sub-second](https://clickhouse.ac.cn/blog/lago-using-clickhouse-to-scale-an-events-engine) latency for their queries, even with high data volumes. This is achieved through the way data is stored, how it’s compressed, and the use of data structures to ensure only relevant data is processed. 

ClickHouse also uses vectorized query execution and parallelizes query execution across cores and servers. Users can also use materialized views to reduce the number of rows that need to be scanned when query latency is at a premium.

### Concurrency  

ClickHouse is designed to handle high concurrency workloads. It is frequently used in applications where organizations want to [provide real-time analytics functionality to their users](https://clickhouse.ac.cn/blog/behind-the-scenes-how-clickhouse-helps-vimeo-power-video-analytics-at-scale).  It's also used in [real-time dashboards](https://clickhouse.ac.cn/blog/how-quickcheck-uses-clickhouse-to-bring-banking-to-the-unbanked), where there are fewer users, but each dashboard refresh spawns multiple queries.   

## Getting started with ClickHouse Cloud for real-time analytics

Hopefully, you’re excited to [add real-time analytics functionality to your application](https://clickhouse.ac.cn/blog/adding-analytics-to-an-application-with-clickhouse-query-endpoints). Let’s have a look at how to do that.

The first step is to navigate to [clickhouse.cloud](https://console.clickhouse.cloud/) and create an account for your 30-day free trial. Once you’ve done that, create a service in the cloud and region of your choice. Initializing the service will take a few minutes, but then we’re ready to import some data. 

You can also watch the following video to see the steps described above:

<iframe width="768" height="432" src="https://www.youtube.com/embed/uWNY0GLUkqc?si=0znNr0i1To7N2ZGb" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen\></iframe>

<p></p>
Once your service is up and running, we need to follow these steps:
<p></p>

1. Ingest data with ClickPipes.  
2. Write queries using the ClickHouse Cloud SQL console.  
3. Create query endpoints based on those queries.

Let’s go through each of these steps in a bit more detail.

### Ingest data with ClickPipes

ClickPipes is an integration engine that makes ingesting massive volumes of data from diverse sources as simple as clicking a few buttons. It supports a variety of sources, both for streaming and static data.

![](/images/engineering-resources/5_rta.png)

You’ll likely want to use it to ingest data from Confluent Cloud or one of the other streaming data platforms. You’ll have to populate your credentials and select the topic you want to ingest. ClickPipes will then infer a table schema by sampling some of the messages in the chosen topic before creating a table and ingesting the data into it.

### Write queries in the SQL console

Once the data is ingested, you can explore it using the [SQL console](https://clickhouse.ac.cn/docs/en/get-started/sql-console). Clicking on a table will show its contents, which you can view and filter.

You can also write queries manually, and the SQL console provides a rich UI for editing queries and even has AI assistance if your SQL is a bit rusty. You can also create visualizations to understand query results better. 

### Create query endpoints

Once you’re happy with your query, you can save it and share it with other users in your organization. You can also [create an API endpoint](https://clickhouse.ac.cn/docs/en/get-started/query-endpoints) for the query. Query API endpoints create an API endpoint directly from any saved SQL query in the ClickHouse Cloud console. You can then access API endpoints via HTTP to execute your saved queries without connecting to your ClickHouse Cloud service via a native driver.

You can read more about query endpoints in the blog post [Adding Analytics to an Application in under 10 minutes with ClickHouse Cloud Query Endpoints](https://clickhouse.ac.cn/blog/adding-analytics-to-an-application-with-clickhouse-query-endpoints)  



What is real-time analytics?


> They say a picture is worth a thousand words, so does that mean one data visualization is worth a thousand rows? 

Data visualizations are to raw data as images are to prose \- they can often tell the story of a query result much better than any table of data or stream of JSON ever could. Real-time data visualization adds a real-time element to these visualizations. We want these visualizations to use fresh data and be updated regularly as new data appears.

This guide will explore real-time data visualization, learn why it’s essential, and see some examples in action.

## Real-time data visualization needs real-time analytics

We want to get insights as soon as the data is available. If I upload a video to YouTube, I want to know if anyone’s watching it and for how long they’re watching as soon as possible. I don’t want to wait until tomorrow when the batch job runs and populates the analytics database.

Real-time data visualization goes hand in hand with [real-time analytics](/engineering-resources/what-is-real-time-analytics). We can’t generate real-time visualizations if we don’t have the infrastructure to ingest streaming data quickly, serve queries on it with low latency, and do so at scale.

Without **speedy ingestion**, our visualizations are based on stale data. Without **low-latency queries**, dashboards take too long to load and update, and without **scale**, we can’t have as many visualizations as we’d like on our dashboards.

None of these scenarios are helpful for timely decision-making.

## Benefits of real-time data visualization

The benefits of real-time data visualization are similar to those of real-time analytics. Let’s go through some of them and some examples of how these benefits might play out.

### Faster decision-making

We can now react quickly to changing situations and make decisions based on up-to-date information. 

For example, imagine that we’re running an online clothing store with an unexpected demand for a summer dress. Seeing this in real-time allows the operations manager to check stock numbers and place an order with the supplier to ensure that demand is satisfied. Or perhaps they could introduce a flash promotion for accessories that go well with the dress to take advantage of the sudden interest.

Using real-time data to adapt the user experience quickly makes you more likely to engage customers, increase sales, and improve overall satisfaction with your e-commerce platform.

### Improved customer experience

Organizations can use real-time data to instantly personalize user experiences and react to trends. By leveraging real-time dashboards, organizations can spot and act on trends as they happen, creating timely and relevant user experiences.

For example, imagine we’re the concierge at an upscale hotel in a popular tourist area. Our dashboard shows a spike in check-ins on one chart but unusually low wait times for the city’s most famous museum. Based on this insight, we could greet guests as they check-in, inform them about the short wait times, and offer to reserve museum tickets. We could even arrange for the hotel’s shuttle service to make an extra trip to the museum. 

By using real-time data to inform your interactions with guests, we can provide a service that feels personal, timely, and valuable. This can lead to higher guest satisfaction, positive reviews, and increased loyalty to your hotel.

### Early problem detection

Real-time data visualization serves as an early warning system, allowing us to identify issues or anomalies as soon as they emerge. This proactive approach enables us to address potential problems before they escalate into significant concerns.

For example, imagine our e-commerce platform's real-time dashboard suddenly shows a spike in cart abandonment rates. Upon investigation, we discover that a recent website update has caused the checkout process to slow down significantly on Android phones. By catching this issue early through our real-time visualizations, we can quickly roll back the problematic update or implement a fix, minimizing lost sales and maintaining a smooth customer shopping experience.

Using real-time data visualization can help detect and resolve issues quickly, preventing potential revenue loss and maintaining customer satisfaction

## Examples of real-time data visualization

Let’s look at some uses of real-time data visualizations. 

### CommonRoom

[CommonRoom](https://www.commonroom.io/) is a software platform or tool designed to help companies, particularly tech companies, manage and optimize their developer relations and community engagement efforts.

The CommonRoom app contains various real-time dashboards and visualizations that allow organizations to manage their communities. They [recently added ClickHouse to their architecture](https://clickhouse.ac.cn/blog/clickhouse-replaces-postgres-to-power-real-time-analytics-in-common-room-customer-portal) to act as a fast search engine on top of data ingested from multiple sources, such as Marketo, Salesforce, LinkedIn, Slack, and more. 

An example of a page in CommonRoom is shown below:

![](/images/engineering-resources/0_dataviz.jpg)

### adsb.exposed 

ADS-B (Automatic Dependent Surveillance-Broadcast) is a radio protocol that broadcasts various flight data. ClickHouse co-founder and CTO Alexey Milovidov has built an interactive visualization and analytics tool based on this data. 

[adsb.exposed](https://adsb.exposed/) aggregates and visualizes massive amounts of air traffic data. The data is hosted in a ClickHouse database and queried on the fly. You can tune the visualizations with custom SQL queries and drill down from 50 billion records to individual data records.   
   
Below is an example visualization from the application showing [helicopters flying over the River Thames in London:](https://adsb.exposed/?zoom=12&lat=51.5079&lng=359.8960&query=e18e8c8d6a1db73c63953798ad8919a9)  

![](/images/engineering-resources/1_dataviz.png)

### CryptoHouse

[CryptoHouse](http://crypto.clickhouse.com) is a [platform powered by ClickHouse](https://clickhouse.ac.cn/blog/announcing-cryptohouse-free-blockchain-analytics) that offers free blockchain analytics. CryptoHouse provides real-time analytics, making instant query responses accessible to everyone. 

Users can utilize SQL to query continuously updated data, courtesy of [Goldsky](https://goldsky.com/). In addition to viewing results in table format, users can also generate charts. Below is an example of a chart that shows the number of Solana transactions, grouped by day and status:  

![](/images/engineering-resources/2_dataviz.png)

### Juspay

Juspay is an Indian fintech company that [uses ClickHouse to power A/B testing and monitoring](https://clickhouse.ac.cn/blog/juspay-analyzes-payment-transactions-in-real-time-with-clickhouse) for its end-to-end payment solutions and real-time merchant dashboards.

For monitoring and A/B testing, they use a combination of Kafka, ClickHouse, and Grafana. They monitor active logs and perform transformations in Kafka, before transferring data to ClickHouse. You can see an example of one of their Grafana dashboards below:

![](/images/engineering-resources/3_dataviz.png)

They also provide real-time dashboards to their merchants so that they can track their transactions and monitor their success or failure in real time. An example of one such dashboard is shown below:

![](/images/engineering-resources/4_dataviz.png)

## Real-time data visualization architecture

The data used in real-time data visualizations will start their life on a streaming data platform like Apache Kafka or Redpanda. That data might be transformed or augmented by a stream processor before being transported to a real-time database.

![](/images/engineering-resources/5_dataviz.png)

Now that the data’s in the database, how does it get to the visualization? That’s what we’ll learn about in the next section

## Connecting databases to visualization tools

At a high level, there are two approaches to getting data from a database to a dashboard or visualization tool: push-based and pull-based.

### Push-based data transfer

Push-based means that as data arrives in our real-time analytics system, it’s sent to the visualization tool \- it doesn’t need to do anything to get the data. [Web sockets](https://mdn.org.cn/en-US/docs/Web/API/WebSockets_API) and [server-side events](https://mdn.org.cn/en-US/docs/Web/API/Server-sent_events/Using_server-sent_events) are the most popular technologies for this technique.

With both of these technologies, you create a subscription from the client and receive new events as they become available. These events can then be included in the visualization.

![](/images/engineering-resources/6_dataviz.png)

ClickHouse doesn’t currently support this approach.

### Pull-based data transfer

Conversely, pull-based means polling a server for data, usually on a fixed interval. For example, we might ask for new data every second, so we’d call an API or send a query to execute every tick. We’d use an HTTP client or programming language client for this technique.

![](/images/engineering-resources/7_dataviz.png)

This was traditionally done using APIs like JDBC if you used Java and ODBC for other programming languages. Indeed, many analytics platforms still require you to use one of these APIs to connect to your database.

As Python has grown in popularity, [SQLAlchemy](https://sqlalchemy.org.cn/) emerged as an alternative API. SQLAlchemy provides a high-level ORM (object-relational mapper) for mapping Python classes to database tables and the ability to execute SQL queries.

## Tools and technologies

Let’s now look at some tools we can use to build real-time data visualizations and dashboards. We’ll assume that ClickHouse is being used to serve the data to these tools\!

This is a very fast-moving space, so while we’ll cover the latest tools at the time of writing, new ones may be created by the time you read this\!

### Analytics platforms

The first category is what we’ll call analytics platforms. These tools are all-in-one solutions that let you create multiple dashboards with a wide selection of supported visualizations. They will generally have connectors to popular analytical databases.

* [**Omni Analytics**](https://omni.co/) lets companies quickly embed analytics into their products. It offers a modern architecture that combines in-database and in-memory processing with intelligent caching for fast results. They provide customizable visualizations and extensible components so that companies can create branded data experiences. Omni has connectors to many popular databases, [including ClickHouse](https://docs.omni.co/docs/connections/database/clickhouse).  
* [**Apache Superset**](https://superset.org.cn/) is an open-source data exploration and visualization platform. It is designed to be fast, intuitive, and loaded with options, making it user-friendly for individuals with varying levels of expertise to analyze and visualize their data. It can connect to any SQL-based database, including ClickHouse and ClickHouse Cloud. We use Superset internally at ClickHouse to [visualize data in our data warehouse](https://clickhouse.ac.cn/blog/building-a-data-warehouse-with-clickhouse).  
* [**Looker**](https://cloud.google.com/looker), developed by Google, is a business intelligence (BI) platform and embedded analytics tool that lets users build custom data applications. Looker has a universal semantic modeling layer to make data more accessible. It also makes it easy to embed dashboards in your applications. You can [use Looker with ClickHouse](https://clickhouse.ac.cn/docs/en/integrations/looker), but it has some limitations on how it handles ClickHouse’s data types.  
* [**Metabase**](https://metabase.net.cn/) is an open-source business intelligence platform whose big selling point is its ease of use. It provides a user-friendly visual query builder and supports connectivity to over 20 data sources, including those from Amazon and Google. Metabase is lauded for its intuitive interface, which allows even non-technical users to explore data and create reports independently. [ClickHouse is a supported data source](https://metabase.net.cn/data_sources/click-house) in Metabase.  
* [**Salesforce-owned Tableau**](https://www.tableau.com/en-gb) is a visual analytics platform that allows users to explore and analyze data from various sources. It offers a suite of products, including Tableau Desktop, Tableau Server, Tableau Public, and Tableau Reader, with capabilities ranging from data preparation to visual storytelling and collaboration. Tableau has a [connector for ClickHouse](https://clickhouse.ac.cn/docs/en/integrations/tableau).

### Low-code tools

This category emerged in the late 2010s, making it easy for data scientists to share their work. These tools let you write Python code, which is then turned into an interactive web application.

* [**Streamlit**](https://streamlit.io/) makes creating and sharing web apps for machine learning and data science projects easy. Users can create interactive dashboards and data visualizations with just a few lines of Python code and publish them to Streamlit Cloud.  
* [**FastHTML**](https://github.com/AnswerDotAI/fasthtml) is a web framework designed to develop scalable interactive web applications while maintaining a direct correlation to HTML and HTTP concepts.   
* [**Mesop**](https://google.github.io/mesop/) originated from Google and was designed for users building AI web applications. It offers a declarative approach to UI design, a range of pre-built components, and streamlined deployment options, enabling developers to focus on AI logic rather than frontend complexities.  
* [**Gradio**](https://www.gradio.app/) enables users to quickly create and share interactive machine-learning demos through user-friendly web interfaces. Gradio apps can be served as web pages or embedded into Python notebooks. Gradio also offers permanent hosting options through Hugging Face Spaces.

### Python Visualization libraries

Low-code tools provide the container for real-time dashboards, but we’ll need to use a visualization library to actually visualize the data. The Python ecosystem has many such libraries, so we’ll focus on a few of our favorites.

* [**plotly**](https://github.com/plotly/plotly.py) is an interactive, open-source, and browser-based graphing library. It ships with over 30 chart types, including scientific charts, 3D graphs, statistical charts, SVG maps, financial charts, and more.  
* [**Vega-Altair**](https://altair-viz.github.io/) is a declarative visualization library built on the Vega-lite grammar and has a reasonably simple API.  
* [**Bokeh**](https://docs.bokeh.org.cn/en/latest/) lets users create interactive visualizations for modern web browsers. It helps you create anything from simple plots to complex dashboards with streaming datasets. 

We also want to give a special shout-out to [**Perspective**](https://perspective.finos.org/), a library we recently discovered. It’s designed for large streaming datasets, making it a [perfect choice for real-time data visualization](https://clickhouse.ac.cn/blog/streaming-real-time-visualizations-clickhouse-apache-arrow-perpsective).

### JavaScript Visualization libraries

There are also a variety of visualization libraries available if you’re creating front-end applications using JavaScript. Below are some of our favorites:

* [**Apache Echarts**](https://echarts.org.cn/en/feature.html) is an open-source JavaScript visualization library optimized for both web and mobile. It’s compatible with most modern web browsers, including IE9/10/11, Chrome, Firefox, and Safari.  
* [**Tremor**](https://tremor.so/) provides a set of open-source, accessible components for dashboards and charts. It’s designed for React v18.2.0+ and requires Tailwind CSS v3.4+.  
* [**plotly**](https://plotly.com/javascript/) is an interactive, open-source, and browser-based graphing library. It ships with over 30 chart types, including scientific charts, 3D graphs, statistical charts, SVG maps, financial charts, and more. It’s also available for Python users.


Real-time data visualization


Time-series data is everywhere in modern systems - from IoT sensors and financial markets to application monitoring and user analytics. As organizations collect more temporal data, they need efficient ways to store, process, and analyze it. 

This article explores time-series databases, their use cases, and how different database solutions handle time-based data. Whether you're dealing with millions of sensor readings, tracking user behavior, or monitoring system performance, understanding your options for time-series data storage is crucial for building effective data systems.

## What is time-series data?

Let’s start by defining time-series data. It describes datasets where observations are captured along a timeline, and a key feature is a **timestamp**. These data points are collected at **regular intervals**—like every second, minute, or day—or **irregular intervals** when events occur unpredictably.

For example, while a weather sensor might collect readings at fixed intervals (e.g., every 10 seconds), an error log in a server system records data only when an error event happens, which can be at any time. Both are forms of time-series data because they represent data that changes over time, whether those changes are **periodic** or **sporadic**.

Let’s look at the methods of data collection in a little more detail:

* **Fixed interval sampling:** This method captures data at consistent time points, providing a predictable and continuous stream of information. Examples include weather sensors, heart rate monitors, and energy meters. Since the points are evenly distributed, this data is beneficial for seeing trends over specific periods.  
* **Event-driven data:** Time-series data can also be captured irregularly, triggered by specific occurrences. For instance, a server logs data each time a particular error occurs, which could happen multiple times in one hour or not for several hours. Other examples include clickstream data from websites (where clicks happen unpredictably) or social media posts, which depend entirely on user activity.

When analyzing time series data, we often **slice or** **group** it by different time periods to understand how it changes over time. This ability to analyze changes across **time** defines time series data—any data that changes in any way over time belongs to this category.

Below is an example of a message that a weather sensor might generate:

```javascript
{
  "device_id": "sensor-12345",
  "timestamp": "2024-12-04T10:15:00Z",
  "location": {
    "latitude": 40.7128,
    "longitude": -74.0060,
    "altitude": 15.5
  },
  "metrics": {
    "temperature": 23.5,
    "humidity": 60,
    "pressure": 1013.25,
    "battery_level": 85,
    "signal_strength": -70
  },
  "status": {
    "operational": true,
    "last_maintenance": "2024-11-20T08:00:00Z"
  }
}
```

In this example, several **metrics,** such as `temperature`, `humidity`, and `pressure` are captured. These metrics continuously change, often at high frequency. Additionally, we have **metadata** like the `device_id` and `location`, which provide context for where and what is being measured but change much more frequently than metrics and perhaps not at all. There is also **status information**, which includes details like `operational` status and `last_maintenance` date.

Finally, each data point is associated with a **timestamp**, representing when the observation was taken. This timestamp is crucial for understanding how the metrics evolve, allowing us to detect patterns or trends.

## Use cases for time-series data

Time-series databases can support a wide range of applications, each benefiting from the ability to store and analyze data as it changes over time. Let's explore some common use cases and the types of questions they help organizations answer.

### Product analytics

Product analytics generates rich time-series data through user interactions, system events, and transactions. Every click, page view, feature interaction, and purchase is timestamped, creating a detailed record of user behavior over time. This temporal data enables teams to answer crucial questions about their product: How do users navigate it? What paths lead to successful conversion? Which behaviors indicate potential churn? When do users typically discover and adopt new features?

The power of time-series analysis in product analytics lies in understanding not just what users do but when and in what sequence they do it. Teams can track user journeys through onboarding, measure time-to-conversion, analyze retention patterns, and identify features that drive engagement. By correlating these behaviors with other metrics like performance data, organizations can build a complete picture of their product's effectiveness and make data-driven decisions about product development.

➡️ Read more about [building product analytics with ClickHouse](https://clickhouse.ac.cn/blog/building-product-analytics-with-clickhouse)

### Financial Markets and Trading

Financial markets, from traditional stock exchanges to cryptocurrency trading platforms, generate massive volumes of time-series data. Every price change, trade, and order book update must be captured and analyzed in real-time. This data is crucial for generating trading signals, performing technical analysis, and identifying market opportunities.

Time-series analysis is particularly important for creating standard trading tools like candlestick charts showing price movements over specific time intervals. These charts require rapid price data aggregation (open, high, low, close) over various time windows, from minutes to months. Traders also need to analyze market liquidity, calculate technical indicators, and simultaneously detect patterns across multiple assets or trading venues. Processing this data quickly is crucial \- even small delays can mean missed trading opportunities or increased risk.

For example, cryptocurrency trading platforms must aggregate data from multiple blockchain networks and decentralized exchanges, processing millions of price updates daily while maintaining sub-second query response times. This enables traders to spot arbitrage opportunities, track market trends, and make real-time trading decisions.

➡️ Read more about how [Coinhall uses ClickHouse to power its blockchain data platform](https://clickhouse.ac.cn/blog/trade-secrets-how-coinhall-uses-clickhouse-to-power-its-blockchain-data-platform)

### System and Application Observability

Modern applications generate vast amounts of operational data that must be monitored in real time. From server metrics to user behavior, organizations need to track everything from system health to user experience. This observability data typically includes system metrics (CPU, memory, network), application telemetry (response times, error rates), and user interaction data.

Time-series analysis enables teams to visualize this data through real-time dashboards, track performance trends, and quickly identify issues. For example, teams can monitor application performance across different regions, track user engagement metrics, and analyze experiment results from A/B tests. The ability to correlate various metrics \- from infrastructure health to user behavior \- helps organizations understand how system performance impacts user experience and business outcomes.

By storing this data in a time-series database, teams can not only monitor the current system state but also analyze historical patterns, establish baselines, and detect anomalies that might indicate potential problems. This comprehensive view of system behavior is essential for maintaining reliable services and optimizing user experience.

➡️ Learn how [Skool uses ClickHouse to visualize real-time observability and monitor user behavior.](https://clickhouse.ac.cn/blog/how-skool-uses-clickhouse-for-observability-behavioral-analytics)

## What is a time-series database?

Now that we've defined time-series data and seen some examples, what does storing this data take? A time-series database (TSDB) is a database that can efficiently store, manage, and analyze time-series data.

Such a database will need to have the following characteristics:

1. **Data volume** \- Time-series data grows rapidly due to the high frequency of measurements, such as sensor readings every second. Traditional databases can struggle to maintain performance when millions of data points are generated in short periods. Instead, we need a database that is optimized for appending new data.  
2. **Write and query performance** \- Time-series data involves frequent writes (inserting new data points continuously) and complex queries for analysis (e.g., aggregations over time). Our database needs efficient time-based indexing or the ability to sort data by timestamp during ingestion.  
3. **Efficient storage and compression**—Since time series data often contains many repeated or very similar values, storing it efficiently is crucial. Column-based storage is an advantage here since values in the same column are stored next to each other. We’ll also want to use codecs that allow us to store deltas between values rather than the raw values each time. Delta encoding is one such codec that’s often used when storing timestamps.  
4. **Time-based aggregations**—Time-series analysis typically involves time-based queries, such as calculating daily averages or summing metrics over weeks. We need to be able to run these types of queries over large volumes of data while also being able to filter by time period.

Many of these characteristics are the same as those required for [real-time analytics databases](/engineering-resources/what-is-real-time-analytics#characteristics-of-a-real-time-analytics-system).

## Examples of time-series databases

Time-series databases can be categorized into three main types: purpose-built time-series databases, extensions of other databases, and real-time analytics/column-based databases. Here are some popular examples:

### Purpose-built time-series databases

Specialized databases engineered from the ground up to efficiently handle time-stamped data, offering optimized temporal data storage and query mechanisms, including as of joins, specialized maths functions, downsampling, grouped gap filling, and more. Some examples are described below:

* [InfluxDB](https://github.com/influxdata/influxdb) \- Specifically designed for time-series data, InfluxDB handles high write loads and provides features like downsampling and data retention policies, making it great for IoT, DevOps metrics, and real-time monitoring.  
* [QuestDB](https://github.com/questdb/questdb) \- A high-performance open-source time-series database that excels at fast SQL queries and high-throughput ingestion.  
* [Prometheus](https://github.com/prometheus/prometheus) \- An open-source monitoring system primarily designed for system and service monitoring. Prometheus excels at scraping metrics from various endpoints, storing them efficiently, and enabling alerting based on those metrics. It’s well-suited for use cases like server health monitoring and application performance metrics.

### Extensions of relational databases

Traditional relational databases can enhanced with time-series capabilities, combining the familiarity and flexibility of SQL with specialized temporal features.

[TimescaleDB](https://github.com/timescale/timescaledb) is an extension of PostgreSQL. TimescaleDB adds time-based features like automatic partitioning and time-based indexing. It's perfect for scenarios where you want to blend relational data with time-series data, such as in business intelligence or IoT.

### Real-Time analytics / column-based Databases

Systems optimized for rapid large-scale data analysis, using [columnar storage](/engineering-resources/what-is-columnar-database) to enable fast aggregations and real-time processing of time-series information. Some examples are described below:

* [Apache Pinot](https://github.com/apache/pinot) \- Designed for real-time, low-latency analytics, Pinot is well-suited for applications like user-facing dashboards or clickstream analysis, offering sub-second query response times.  
* [ClickHouse](https://github.com/ClickHouse/ClickHouse) \- That’d be us\! ClickHouse was initially designed to keep records of all clicks by people from all over the Internet but is now used for various time-centric datasets, with a particular focus on observability.

## Querying time-series data

Time-series databases offer specialized query capabilities designed to handle temporal data efficiently. While many modern time-series databases use SQL with extensions, some have developed their own query languages optimized for time-series operations.

Most time-series databases extend standard SQL with specialized functions for temporal analysis. These extensions typically include:

* Time-based window functions for analyzing data over specific time intervals  
* Gap filling to handle missing data points  
* Interpolation functions to estimate values between known data points  
* Time bucket operations for grouping data into regular time intervals  
* Specialized mathematical functions for time-series analysis

Prometheus uses a domain-specific query language called [PromQL](https://prometheus.ac.cn/docs/prometheus/latest/querying/basics/), specifically designed for time-series analysis and monitoring use cases. PromQL has built-in support for rate calculations and aggregations over time, native handling of labels and label matching, and vector and range vector selectors. It's particularly well-suited for monitoring scenarios where you must analyze metrics over time windows and create alerting rules.

InfluxDB initially used a query language called InfluxQL, which was SQL-like but explicitly designed for time-series operations. With InfluxDB 2.0, they introduced Flux, a more powerful SQL-based language, before adding SQL support to make the platform more accessible to users familiar with traditional database querying.

## Is ClickHouse a time-series database?

While ClickHouse isn't specifically designed as a time-series database, it excels at handling time-series workloads as part of its broader analytical capabilities. 

As a columnar OLAP database, ClickHouse provides the performance and features needed for efficient time-series analysis without the limitations of a specialized solution.

ClickHouse's strengths in handling time-series data come from several key capabilities:

* Real-time querying of large datasets, enabling analysis of historical and current data at scale  
* Support for [high-precision timestamps](https://clickhouse.ac.cn/docs/en/sql-reference/data-types/datetime64) and date-time operations  
* Rich set of temporal functions for [time-based aggregations](https://clickhouse.ac.cn/docs/en/sql-reference/functions/date-time-functions), [window operations](https://clickhouse.ac.cn/docs/en/sql-reference/window-functions), and more.

Additionally, ClickHouse offers features that are particularly valuable for long-term time-series data management:

* [Materialized views](https://clickhouse.ac.cn/docs/en/materialized-view) for maintaining pre-aggregated data efficiently  
* Support for [aggregate states](https://clickhouse.ac.cn/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states#working-with-aggregation-states) that enable flexible roll-up strategies  
* [Time-to-live (TTL) functionality](https://clickhouse.ac.cn/docs/en/guides/developer/ttl) that can automatically manage data retention and aggregation levels


These capabilities mean you can implement sophisticated time-series storage strategies, such as keeping recent data at full granularity while automatically rolling up older data to save space. This approach provides both detailed recent data for operational needs and efficient storage of historical data for long-term analysis.

ClickHouse also expands its time-series capabilities with experimental features like the [Time Series table engine](https://clickhouse.ac.cn/docs/en/engines/table-engines/special/time_series), which can be a backing store for Prometheus data. This allows organizations to leverage ClickHouse's analytical capabilities while maintaining compatibility with popular time-series monitoring tools.

Rather than being limited to time-series-specific functionality, ClickHouse allows you to handle time-series workloads alongside other analytical queries, providing a more versatile solution for organizations with diverse data analysis needs.

➡️ Read more in [Can I use ClickHouse as a Time-Series Database?](https://clickhouse.ac.cn/docs/en/faq/use-cases/time-series) and [Working with Time Series Data in ClickHouse](https://clickhouse.ac.cn/blog/working-with-time-series-data-and-functions-ClickHouse).  

An intro to time-series databases


There isn’t really such a thing as a JSON (JavaScript Object Notation) database, but there are databases that are designed to work well with JSON data or have strong JSON support.

JSON is a text-based data interchange format that [has become the lingua franca of data exhaust](https://x.com/medriscoll/status/1831900730254582115) over the last decade \- APIs return JSON, it is used to represent the state in [single-page applications](https://clickhouse.ac.cn/blog/building-single-page-applications-with-clickhouse-and-http), and metrics or logs are often generated in this format.

## What is JSON?

[JSON](https://en.wikipedia.org/wiki/JSON) is a lightweight data-interchange format that is easy for humans to read and write and for machines to parse and generate. Initially specified by Douglas Crockford in the early 2000s, JSON, which derives its notation from JavaScript, quickly became popular as a language-independent format. By the late 2000s, it had become the de facto standard for transferring application state and facilitating client-server interactions.

JSON documents comprise key/value pairs, where the key is a string, and the value can be a string, an integer, a boolean, an array, or an object. An example of a JSON document representing an order on an e-commerce website is shown below:

```json
{
  "orderId": "ORD-12345",
  "orderDate": "2023-05-15T14:30:00Z",
  "customer": {
    "id": "CUST-789",
    "name": "Alice Johnson",
    "email": "alice.johnson@email.com"
  },
  "items": [
    {
      "productId": "BOOK-001",
      "title": "The Great Gatsby",
      "price": 12.99,
      "quantity": 1
    },
    {
      "productId": "BOOK-002",
      "title": "To Kill a Mockingbird",
      "price": 14.99,
      "quantity": 2
    }
  ],
  "totalAmount": 42.97,
  "status": "processing"
}
```

JSON documents don’t have a schema, which means we could have another document representing an order with slightly different fields. For example, perhaps we don’t have the customer's name, so the `customer.name` field would be missing.

JSON's semi-structured nature provides flexibility, making it easy to modify and extend data structures without altering existing records. It also supports rapid prototyping and agile development.

## JSON database examples

Now that we’ve learned about JSON, let’s explore how to store data in this format. Several different types of databases can store and retrieve data in JSON format. In this section, we’ll learn more about those.

### Document databases

Document databases like CouchDB and MongoDB emerged in the late 2000s/early 2010s. These databases store data in JSON-like documents, allowing for flexible, schema-less data modeling. Their main selling point was the ease with which users could store JSON and retrieve individual documents.

This made them a perfect fit for storing application state in interactive Javascript-heavy web pages, which became popular with the emergence of [jQuery](https://en.wikipedia.org/wiki/JQuery) and later [AngularJS](https://en.wikipedia.org/wiki/AngularJS) and [React](https://en.wikipedia.org/wiki/React_$JavaScript_library$). Social media websites like Twitter gained popularity around the same time and offered JSON APIs to access their data, which users would then store in their document database of choice.

Although MongoDB and CouchDB were the early contenders in this category, others include Couchbase, CosmosDB, and Firestore.

### Relational databases with JSON support

The two most popular open-source relational databases, PostgreSQL and MySQL, initially didn’t have JSON support, and it took them a while to catch up.

PostgreSQL added native support for JSON in [version 9.2](https://www.cloudbees.com/blog/unleash-the-power-of-storing-json-in-postgres), released in September 2012\. They went on to add support for indexes in a subsequent release. MySQL followed suit, adding its own JSON data type in [version 5.7.8](https://dev.mysqlserver.cn/doc/relnotes/mysql/5.7/en/news-5-7-8.html#mysqld-5-7-8-json), released in August 2015\.

These databases are still predominantly used for storing data in rows and columns and retrieving a single or small number of rows. The JSON type allows them to store semi-structured data, but they don’t have as many features for working with JSON as the document databases.

### Real-time analytics databases with JSON support

In the late 2010s and early 2020s, real-time analytics databases like Apache Druid, Apache Pinot, Rockset, and ClickHouse emerged. These databases use column-based storage and focus on optimizing large-scale analytics queries across billions of rows or more. 

They all have functionality for extracting a schema from JSON documents, which works well if all the JSON documents have the same schema.

However, data is often semi-structured for common use cases like observability and logging, which means these databases also had to add support for a JSON data type.

When storing the data, they need to ensure that it’s stored in a way that allows nested JSON queries to be executed quickly—it’s not good enough to store the data as a String and push the work to query time.

## JSON in ClickHouse

ClickHouse added a JSON data type in version 22.6 in June 2022, but this [implementation had some limitations](https://github.com/ClickHouse/ClickHouse/issues/54864) and was replaced by [a new JSON data type](https://clickhouse.ac.cn/docs/en/sql-reference/data-types/newjson) in [version 24.8](https://clickhouse.ac.cn/blog/clickhouse-release-24-08) in August 2024\. 

The new data type [addresses the following challenges](https://clickhouse.ac.cn/blog/a-new-powerful-json-data-type-for-clickhouse):

1. True column-oriented storage \- Implement a column-oriented storage system for JSON data to enable efficient compression and fast, vectorized operations.  
2. Dynamically changing data without type unification \- Handle JSON paths with different data types without unifying them into a common type.  
3. Prevention of an avalanche of column data files on disk \- Avoid creating excessive column files on disk for unique JSON paths.  
4. Dense storage \- Store values of unique JSON paths in a dense, non-redundant way.

It’s an experimental feature at the moment, so you’ll need to enable the following flag to use it:

```sql
SET allow_experimental_json_type = 1;
```

Once you’ve done that, you can create a table with one or more fields of type `JSON`. We will import [a JSON file containing football/soccer events](https://raw.githubusercontent.com/statsbomb/open-data/refs/heads/master/data/events/15946.json) from the [StatsBomb open dataset](https://github.com/statsbomb/open-data/blob/master/data/events/15946.json). It only contains a few thousand events, but it’ll be enough to see how the JSON type works.

We’ll create the following table:

```sql
CREATE TABLE events
(
    matchId String,
    json JSON,
    possession_team_id String MATERIALIZED getSubcolumn(
      json, 'possession_team.id')
)
ENGINE = MergeTree
ORDER BY possession_team_id;
```

And then import the data:

```sql
INSERT INTO events
SELECT
    ‘15946’ AS matchId,
    json
FROM url(
  'https://raw.githubusercontent.com/statsbomb/open-data/refs/heads/master/data/events/15946.json', 
  JSONAsObject
);
```

We can then write the following query to find the most common event types:

```sql
SELECT
    json.type.name,
    count()
FROM events
GROUP BY ALL
ORDER BY count() DESC
LIMIT 10;

┌─json.type.name─┬─count()─┐
│ Pass           │    1163 │
│ Ball Receipt*  │    1058 │
│ Carry          │     890 │
│ Pressure       │     212 │
│ Ball Recovery  │      89 │
│ Duel           │      53 │
│ Clearance      │      37 │
│ Goal Keeper    │      34 │
│ Block          │      32 │
│ Shot           │      28 │
└────────────────┴─────────┘
```

## Frequently asked questions

Let’s review some commonly asked questions about storing JSON in databases.

### What is the best database for JSON?

That depends on what you do with the JSON data once it’s in the database\! If you’re retrieving single documents, a database like MongoDB or Couchbase might be the best choice. If you’re running analytical queries over many JSON documents, ClickHouse might be a better choice.

### Is JSON SQL or NoSQL?

You can store JSON data in both NoSQL and SQL databases. Examples of NoSQL databases that can store JSON are MongoDB and Couchbase. Examples of SQL databases that can store JSON are PostgreSQL and ClickHouse.

### Can you query JSON with SQL?

Yes, ClickHouse lets you query JSON data with SQL For example, imagine that we have JSON documents like this:

```json
{
    "duration": 0,
    "id": "9f6e2ecf-6685-45df-a62e-c2db3090f6c1",
    "index": "1",
    "minute": "0",
    "period": "1",
    "play_pattern": {"id": "1", "name": "Regular Play"},
    "possession": "1",
    "possession_team": {"id": "217", "name": "Barcelona"},
    "second": "0",
    "tactics": {"formation": "442", "lineup": []},
    "team": {"id": "217", "name": "Barcelona" },
    "timestamp": "00:00:00.000",
    "type": {"id": "35", "name": "Starting XI"}
}
```

We could store this data in a table, `events`, with the following schema:

```text
┌─name───────────────┬─type───┐
│ matchId            │ String │
│ json               │ JSON   │
│ possession_team_id │ String │
└────────────────────┴────────┘
```

We can then write the following query to find the most popular event types:

```sql
SELECT json.type.name, count() AS count 
FROM events 
GROUP BY ALL 
ORDER BY count DESC 
LIMIT 10;
```

What is a JSON database?


Data applications have become integral to many industries, from finance to healthcare. But what distinguishes these tools from other types of software?

## What is a data application?

A data application is a software application that derives meaningful insights from complex data sets. These applications use analytical techniques to transform raw data into actionable information.

Data applications are designed to handle large volumes of structured and unstructured data from various sources, such as databases, APIs, and real-time streams. 

They may additionally employ sophisticated algorithms, statistical models, and machine learning capabilities to identify patterns, trends, and correlations that might not be apparent through traditional analysis methods.

It's important to note that not all data applications exist as standalone, pure data-focused software. Many modern applications incorporate data-centric components alongside more traditional, transactional features. This hybrid approach allows for a seamless integration of data insights into everyday business operations.

For example, an e-commerce platform might combine transactional elements (like shopping carts and payment processing) with data application features (such as personalized product recommendations based on user behavior analysis). Similarly, a customer relationship management (CRM) system could include standard contact management functions and advanced data analytics for customer segmentation and predictive lead scoring.

## An example of a data application

To illustrate how data applications work in practice, let's examine a real-world example: [Lifetimely, an analytics service developed by AMP](https://clickhouse.ac.cn/videos/amp-from-batch-processing-to-streaming) for e-commerce businesses using platforms like Shopify or Amazon.

Lifetimely exemplifies the integration of data-focused features within a broader business context. This application gives store owners deep insights into crucial metrics such as profit and loss, customer acquisition cost, lifetime value, and purchasing behavior. By analyzing these complex datasets, Lifetimely transforms raw sales and customer data into actionable information.

The power of this data application lies in its ability to help businesses identify their most significant revenue opportunities and inform data-driven growth strategies. For instance, by understanding customer lifetime value, a store owner might adjust their marketing spend to focus on acquiring customers likely to make repeat purchases.

Below is a screenshot from Lifetimely's interface, showcasing how the application visualizes complex data:

![](/images/engineering-resources/data-app-1.png)

This example demonstrates the capabilities of a standalone data application. While not integrated directly into e-commerce platforms, Lifetimely showcases how specialized data applications can provide valuable insights by analyzing data from other sources, enhancing businesses' decision-making processes.

## The building blocks of a data application

Now that we understand what a data app is and have seen a real-world example let's explore the key components of building one. A typical data application consists of several interconnected elements, as shown in the diagram below.

![](/images/engineering-resources/data-app-2.png)

### Data ingestion

Data ingestion is a crucial component of data applications, serving as the entry point for information from various sources. This process ensures that the application has access to timely and relevant data.

Sources can include:

* Real-time streaming platforms like Confluent or Redpanda allow for continuous data ingestion. This data may be machine-generated (e.g., from IoT sensors) and directly written, or it could be data from a transactional database sent to the streaming platform via a change data capture (CDC) process.  
* Static files (e.g., Parquet, CSV, JSON) stored in cloud storage solutions like Amazon S3 or Google Cloud Storage  
* Third-party APIs providing external data sets

### Data storage

Once ingested, the data needs to be stored for processing and analysis. The choice of storage solution depends on data volume, query requirements, and latency needs.  
Common storage solutions include:

* Data lakes using table formats like Apache Iceberg or Delta Lake, which allow for efficient querying of large-scale data  
* NoSQL databases like MongoDB. These tend to be appropriate for transactional applications but could also be suitable for data apps that deal with a smaller volume of data or don’t have complex queries.  
* Real-time analytics databases like ClickHouse, optimized for fast querying of recent and historical data

### Data processing and transformation

Raw data must often be cleaned, filtered, and transformed before it can be effectively analyzed. This step is crucial for ensuring data quality and usability.

Key aspects include:

* Data cleaning: Removing inconsistencies, duplicates, or errors  
* Data normalization: Standardizing data formats and units  
* Data aggregation: Summarizing data for faster querying and analysis  
* Data enrichment: Combining data from multiple sources to provide more context

Depending on the scale of data and processing requirements, tools and frameworks like Apache Spark, Dask, or Pandas are often used for these tasks. 

Alternatively, you might perform these tasks directly in the database if you prefer working with SQL. Databases such as ClickHouse, with their support for incremental materialized views and extensive collection of functions, offer a viable alternative for data processing and transformation.

### Query engine

The query engine is the heart of a data application, allowing users to interact with the data efficiently. An effective query engine should:

* Handle high concurrency, supporting multiple simultaneous queries  
* Provide low-latency responses, even for complex queries  
* Be highly parallelizable to improve performance by adding more computational resources  
* Support a wide range of query types, from simple lookups to complex analytical queries

Query engines like Presto or Trino can execute distributed queries across multiple data sources. Alternatively, real-time databases like ClickHouse can query data they store themselves and data in external storage.

### Front-end interface

This is what users interact with to access and visualize the data. It serves as a crucial bridge between users and the data. The front end should be intuitive, responsive, and capable of handling complex data interactions. Options include:

* **Custom-built interfaces** for web applications using frameworks like React, Vue.js, or Angular. They offer maximum flexibility and control over user experience and are ideal for unique requirements or specialized data interactions  
* **Rapid development platforms** like Streamlit, Gradio, or Dash enable quick deployment of data applications. They are good choices for data scientists and analysts who must quickly create interactive dashboards. Using tools like this also reduces development time while maintaining professional functionality. 

When selecting a front-end solution, consider your target users' technical expertise, required customization level, development timeline, and maintenance resources. The right choice depends on balancing functionality, development effort, and user needs.

### Visualization library

In addition to a front-end interface, we’ll need a visualization library to create interactive, real-time, attractive visualizations for our users. These could range from a simple bar chart to a complicated multi-layer visualization.

There are plenty of libraries that we can use, including Altair and  Bokeh (Python), Apache Echarts and Tremor (Javascript), or plot.ly (Python or Javascript). The [real-time data visualization guide](https://clickhouse.ac.cn/engineering-resources/real-time-data-visualization) has more about visualization libraries.

## Benefits of data applications

Next, let’s look at some benefits we get from building data applications.

### Enhanced User Engagement and Retention

Data applications significantly improve user engagement by providing interactive and personalized experiences. By incorporating features like real-time analytics and instant feedback, applications become more "sticky," encouraging users to return frequently. 

Social media platforms exemplify this benefit by showing real-time engagement metrics such as likes, comments, and shares, creating compelling feedback loops that keep users engaged. This increased engagement naturally leads to higher retention rates and, consequently, more revenue potential.

### Personalized Customer Experience

Data applications excel at delivering personalized experiences by leveraging user behavior patterns and preferences. Analyzing historical data and real-time interactions allows these applications to provide tailored recommendations and content that resonates with individual users. 

For instance, e-commerce platforms can suggest products based on previous purchases and behavioral patterns of similar customers, creating a more relevant and satisfying shopping experience. This level of personalization not only improves customer satisfaction but also drives higher conversion rates.

### New Revenue Opportunities

By developing data-centered products and services, organizations can create entirely new revenue streams. By packaging and presenting data in valuable ways, companies can offer premium analytical capabilities that users are willing to pay for. 

These include advanced reporting features, predictive insights, or specialized data visualization tools. Such data-driven offerings can transform traditional applications into powerful platforms that generate additional revenue while providing genuine value to users.

## In summary

As explored throughout this article, data applications represent a powerful intersection of analytical capabilities and practical business solutions. As technologies evolve and data volumes grow, these applications will become increasingly central to business operations, transforming raw data into actionable insights that drive decision-making and create new opportunities. 

Organizations that embrace and understand these tools will be well-positioned to thrive in an increasingly data-driven future.

What is a data application?



The emergence of Apache Hadoop in the mid-2000s marked the beginning of the big data era, fundamentally changing how organizations store and process massive datasets. This transformation created a need for specialized file formats designed to handle data analysis and cross-system data transfer at scale efficiently.

Two notable file formats emerged from this evolution: [Apache Avro](https://en.wikipedia.org/wiki/Apache_Avro) (2009) and [Apache Parquet](https://en.wikipedia.org/wiki/Apache_Parquet) (2013). While both formats serve essential roles in the big data ecosystem, each brings distinct advantages to different use cases.

In this article, we'll explore the key differences between Avro and Parquet, helping you understand when and why to use each format. We'll examine their strengths, limitations, and ideal applications to help you make informed decisions for your data architecture.

## What is Avro?

Apache Avro is a row-oriented data serialization framework that emerged from the Apache Hadoop project. It provides a compact, fast, and efficient way to handle data serialization, making it particularly valuable for data streaming and message-based systems.

### Avro’s key features

* Schema-based serialization system  
* Rich schema definition and evolution capabilities  
* Compact binary data format  
* Integration with schema registries  
* Built-in support for data compression

One of Avro's standout features is its sophisticated schema management. While the schema must be present during data writing and reading, organizations can leverage schema registries to optimize this process. Instead of transmitting the complete schema with each data exchange, systems can simply reference a schema ID, significantly reducing overhead and improving performance.

### Avro's architecture and use cases

* Primarily used in streaming data scenarios  
* Strong integration with Apache Kafka for message publishing  
* Optimized for wire transfer of data  
* Typically handles smaller data units, often one record per file  
* Ideal for real-time data processing and messaging systems

The format gained prominence in the streaming data community due to its efficient serialization capabilities and schema evolution features. Its design makes it particularly well-suited for systems where data structures may need to evolve without breaking existing consumers.

### When should I use Avro?

Apache Avro excels in data transfer scenarios, particularly with streaming data pipelines and message-based systems. Its design makes it ideal for several data streaming and messaging systems.

It has the following advantages compared to JSON for data transfer:

1. Schema enforcement  
   1. Strict schema definition ensures data consistency  
   2. Clear contract between producers and consumers  
   3. Reduces data quality issues and runtime errors  
2. Schema evolution  
   1. Supports forward and backward compatibility  
   2. Allows systems to evolve without breaking existing applications  
   3. Clean handling of schema changes over time  
3. Performance benefits  
   1. More compact binary format than JSON  
   2. Reduced network bandwidth usage  
   3. Faster serialization and deserialization  
   4. Lower storage requirements

By choosing Avro over text-based formats like JSON, organizations can ensure better data governance, improved performance, and more reliable data integration across their systems. Combining schema validation and efficient binary serialization makes Avro particularly valuable in enterprise-scale data operations, where data consistency and performance are crucial.

### Working with Avro in Python

Let’s look at how to work with Avro in Python. [fastavro](https://fastavro.readthedocs.io/en/latest/) is a popular library for doing this. 
We're going to use the faker library to create a 1 million row Avro file:

<pre><code type='click-ui' language='bash'>
pip install fastavro faker
</code></pre>

<pre><code type='click-ui' language='python'>
import fastavro
from faker import Faker

fake = Faker()

schema = {
    "type": "record",
    "name": "User",
    "fields": [
        {"name": "name", "type": "string"},
        {"name": "age", "type": "int"},
        {"name": "email", "type": "string"}
    ]
}

records = [
  {
    "name": fake.name(),
    "age": fake.random_int(min=18, max=80),
    "email": fake.email()
  }
  for _ in range(1_000_000)
]

with open('users.avro', 'wb') as out:
    fastavro.writer(out, schema, records)
</code></pre>

When reading from this file, we need to first install the [cramjam](https://pypi.ac.cn/project/cramjam/) library:

<pre><code type='click-ui' language='bash'>
pip install cramjam
</code></pre>

<pre><code type='click-ui' language='python'>
with open('users.avro', 'rb') as file:
    reader = fastavro.reader(file)
    for record in reader:
        print(record)
</code></pre>

```
{'key': '_HYtg3FZj:77pUB1!x:?0', 'number': 55093572, 'value1': 70, 'value2': 199, 'value3': 10.167093690048159}
{'key': 'lSRL3Tk_1ZcY*F-3#\\4?', 'number': 55093573, 'value1': 29, 'value2': 193, 'value3': 27.204761505913336}
^C{'key': '~_=66N0);]JxeF>P74j', 'number': 55093577, 'value1': 93, 'value2': 198, 'value3': 5.050937769552736}
{'key': '\'<;2zu_>c"', 'number': 55093578, 'value1': 94, 'value2': 189, 'value3': 6.70526603589251}
{'key': '*Jl0ur=rJRDNe', 'number': 55093579, 'value1': 74, 'value2': 200, 'value3': 26.631595740252777}
{'key': "gX:DBO.'g+0g", 'number': 55093580, 'value1': 88, 'value2': 210, 'value3': 2.3440855043518796}
```

## What is Parquet?

Apache Parquet is a columnar storage format that has emerged as the industry standard for data lake storage. This sophisticated format revolutionized big data analytics by optimizing how data is stored and queried at scale.

### Parquet’s key features

* Column-oriented storage format  
* Optimized for large-scale data processing  
* Commonly deployed on cloud object stores (Amazon S3, Google Cloud Storage)  
* Foundation for modern data lake implementations  
* Underlying format for Apache Iceberg and other table formats

One of Parquet's standout features is its intelligent data organization. The format uses row groups, column chunks, and metadata filtering to enable efficient querying of large datasets. With its sophisticated compression algorithms tailored to specific data types, Parquet significantly reduces storage costs while maintaining high query performance.

### Parquet's architecture and use cases

* The primary choice for data lake storage, especially on cloud platforms (S3, GCS)  
* Fundamental storage layer for modern table formats like Apache Iceberg  
* Optimized for analytical queries and big data processing  
* Ideal for data warehousing and business intelligence applications  
* Commonly used with query engines like Apache Spark, Presto, and Athena

The format gained prominence during the data lake movement due to its ability to handle large-scale analytical workloads efficiently. Its design makes it particularly well-suited for systems where query performance and storage optimization are critical requirements.

### When should I use Parquet?

Apache Parquet is the ideal choice when working with large-scale analytical data that can be broken down into distinct fields. It excels in a couple of key scenarios:

#### Data Storage and Analytics

* Large-scale data warehousing and data lakes  
* Long-term data storage ("data at rest")
* Business intelligence and analytical processing  
* Complex queries requiring column-specific access

#### ETL and Batch Processing

* Batch processing of records in ETL workflows  
* Aggregation of multiple records into single Parquet files  
* Kafka Connect used for time-based or record-count-based aggregation of streaming data

The format's columnar structure and compression capabilities make it particularly efficient for organizations that need to store and analyze large datasets cost-effectively.

### Working with Parquet in Python

When working with the Parquet file format in Python, we can use the popular [pandas](https://pypi.ac.cn/project/pandas/) library with either [fastparquet](https://pypi.ac.cn/project/fastparquet/) or [pyarrow](https://pypi.ac.cn/project/pyarrow/). We can write the following code to create a file with 1 million rows:

<pre><code type='click-ui' language='bash'>
pip install pandas fastparquet
</code></pre>

<pre><code type='click-ui' language='python'>
import pandas as pd
from faker import Faker

fake = Faker()

records = [
  {
    "name": fake.name(),
    "age": fake.random_int(min=18, max=80),
    "email": fake.email()
  }
  for _ in range(1_000_000)
]

df = pd.DataFrame(records)df.to_parquet('users.parquet', index=False)
</code></pre>

And then to query the file:

<pre><code type='click-ui' language='python'>
import pandas as pd

df = pd.read_parquet('users.parquet')
print(df.head())
</code></pre>

```
                        key    number  value1  value2     value3
0      g1.NHQ(0%-JrY*}jxbCS  46752066      39     196  19.823986
1                jIY(eL!_Hu  46752067      37     213  12.839577
2  x}0I__1$MKvIQ+V@z00`*ol;  46752068      23     214  10.135543
3                   4Yv))Dt  46752069      58     194   5.582311
4   H7AP#bi_}4{p7\^O5`^Cpu:  46752070      34     183   5.432857
```

## Exporting data from ClickHouse to Avro and Parquet

Next, we will generate Avro and Parquet files from ClickHouse. We’ll first create a table called `events`:

<pre><code type='click-ui' language='sql'>
CREATE TABLE events (
  key String,
  number UInt64,
  value1 Int32,
  value2 UInt64,
  value3 Float64
)
ENGINE = MergeTree
ORDER BY number;
</code></pre>

Now, we’ll ingest 100 million records into this table:

<pre><code type='click-ui' language='sql'>
INSERT INTO events
SELECT
    randomPrintableASCII(randUniform(5, 25)) AS key,
    number,
    randUniform(18, 100)::Int AS value1,
    randPoisson(200) AS value2,
    randExponential(1/10) AS value3
FROM numbers(100_000_000);
</code></pre>


```
Ok.

0 rows in set. Elapsed: 14.013 sec. Processed 100.00 million rows, 800.00 MB (7.14 million rows/s., 57.09 MB/s.)
Peak memory usage: 91.82 MiB.
```

We can use the `INTO OUTFILE` clause to output data, so let’s do that first into a file with the Avro format:

<pre><code type='click-ui' language='sql'>
SELECT *
FROM events
INTO OUTFILE 'users.avro' TRUNCATE
FORMAT Avro;
</code></pre>

```
100000000 rows in set. Elapsed: 16.757 sec. Processed 99.61 million rows, 5.13 GB (5.94 million rows/s., 306.12 MB/s.)
Peak memory usage: 29.83 MiB.
```

And now the same thing for Parquet:

<pre><code type='click-ui' language='sql'>
SELECT *
FROM events
INTO OUTFILE 'users.parquet' TRUNCATE
FORMAT Parquet;
</code></pre>


```
100000000 rows in set. Elapsed: 3.161 sec. Processed 94.68 million rows, 4.88 GB (29.95 million rows/s., 1.54 GB/s.)
Peak memory usage: 1.07 GiB.
```

## Querying Avro and Parquet with ClickHouse

ClickHouse supports direct querying of both Avro and Parquet files. Let’s start by querying the Avro version of the data:

<pre><code type='click-ui' language='sql'>
SELECT
    count(),
    quantiles(0.5, 0.9, 0.99)(value1) AS quantV1,
    quantiles(0.5, 0.9, 0.99)(value2) AS quantV2,
    arrayMap(x -> round(x, 2), quantiles(0.5, 0.9, 0.99)(value3)) AS quantV3
FROM file('users.avro');
</code></pre>


```
   ┌───count()─┬─quantV1────┬─quantV2───────┬─quantV3────────────┐
1. │ 100000000 │ [58,91,99] │ [200,219,233] │ [6.85,22.46,46.01] │
   └───────────┴────────────┴───────────────┴────────────────────┘

1 row in set. Elapsed: 20.318 sec. Processed 99.68 million rows, 3.09 GB (4.91 million rows/s., 152.03 MB/s.)
Peak memory usage: 8.75 MiB.
```

Now, let’s do the same thing to the Parquet version of the data:

<pre><code type='click-ui' language='sql'>
SELECT
    count(),
    quantiles(0.5, 0.9, 0.99)(value1) AS quantV1,
    quantiles(0.5, 0.9, 0.99)(value2) AS quantV2,
    arrayMap(x -> round(x, 2), quantiles(0.5, 0.9, 0.99)(value3)) AS quantV3
FROM file('users.parquet');
</code></pre>

```
   ┌───count()─┬─quantV1────┬─quantV2───────┬─quantV3────────────┐
1. │ 100000000 │ [58,92,99] │ [200,218,234] │ [6.96,22.54,44.25] │
   └───────────┴────────────┴───────────────┴────────────────────┘

1 row in set. Elapsed: 0.482 sec. Processed 85.27 million rows, 2.09 GB (177.09 million rows/s., 4.34 GB/s.)
Peak memory usage: 231.83 MiB.
```


Avro vs Parquet

Structured, unstructured, and semi-structured data

Build a dashboard in Python with ClickHouse and Streamlit


Logs are the unsung heroes of the IT world, capturing the critical details of system operations with meticulous precision. While these records might seem like endless data streams, they hold the keys to optimizing performance, enhancing security, and ensuring system reliability. 

This guide will cover practical techniques for managing logs and the tools that simplify this essential task. We'll start with structured and unstructured logging fundamentals, explore what log monitoring is and why we need it, and learn how organizations use ClickHouse at the center of their log monitoring systems.

## What are logs?

Logs are digital records that capture events occurring within computer systems, applications, or networks. Think of them as a system's diary \- a chronological record of everything that happens, from routine operations to critical errors. Every time a user logs in, a file is accessed, or an error occurs, these events are recorded with crucial details like timestamps, event types, and relevant data.

The most common source of logs is the applications themselves. Modern applications are designed to emit logs automatically, documenting their operation, performance metrics, user interactions, and any issues encountered. These logs serve as breadcrumbs for developers and system administrators, helping them understand what's happening inside their systems and troubleshoot when things go wrong.

### Log formats

Logs can be generated and captured in various formats. Raw logs are often simple text files, with each line representing an event, implicitly following a specific format determined by the application or system generating them. For example, an HTTP web server might log each request with details like timestamp, IP address, requested URL, and response code in a predetermined order. An example of this type of log message is shown below:

```
192.168.1.1 - - [22/Jan/2025:12:00:00 +0000] "GET /index.html HTTP/1.1" 200 612 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36"
```

Structured logging has become increasingly popular, especially with modern applications. This approach formats logs in a machine-readable structure, most commonly JSON. A JSON log entry might include clearly labeled fields for timestamp, severity level, message, and any relevant metadata. This structured format makes logs easier to parse, search, and analyze programmatically. An example of this type of log message is shown below:

```json
{
  "timestamp": "2025-01-22T12:00:00Z",
  "level": "INFO",
  "message": "User logged in successfully.",
  "ip_address": "192.168.1.1",
  "user_id": 42,
  "method": "POST",
  "endpoint": "/login",
  "response_code": 200,
  "response_time_ms": 350,
  "user_agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36"
}
```

### Types of logs

Logs generally fall into two main categories: system logs and application logs.

System logs provide information about events happening at the operating system (OS) level. These logs capture system-level events such as:

* Authentication attempts  
* Connection requests  
* Service and process starts/stops  
* System configuration changes  
* System errors  
* Performance metrics  
* Resource usage

Application logs, on the other hand, document events occurring at the software level. These logs are generated by specialized server software (like proxies or firewalls) and other software applications. Application logs typically include:

* Application-level authentication  
* CRUD (Create, Read, Update, Delete) operations  
* Application errors and exceptions  
* Application-specific functions  
* User interactions

Application logs are particularly valuable for developers and system administrators as they provide detailed insights into how their software behaves in production. For example, a web application might log every API request, database query, or user session, while a firewall might log all attempted connections and security-related events.  
The combination of both system and application logs provides a comprehensive view of an organization's IT infrastructure, enabling effective monitoring, troubleshooting, and security analysis.

## What is log monitoring?

Log monitoring involves continuously capturing and observing log data to identify anomalies, issues, or patterns that require attention. While log monitoring could, at its most basic level, mean simply logging into a server and reviewing log files manually, modern systems require a far more sophisticated approach.

The complexity of today's systems has made traditional manual log monitoring impractical for several reasons. First, the rise of microservice architectures means applications generate an enormous volume of logs across numerous services and containers. Second, modern infrastructure treats servers as "cattle rather than pets," \- meaning servers are regularly created and destroyed rather than carefully maintained individual machines. Furthermore, with the growing adoption of serverless architectures, there might not even be a persistent server to access in the traditional sense.

These challenges have led to the development of comprehensive log management systems that automatically collect, process, and analyze logged data from across the entire infrastructure. Modern log monitoring involves systematically gathering logs from all sources, centralizing them in a single system, and implementing automated tools to help identify and respond to issues immediately.

The goal is to transform raw log data into actionable insights, enabling teams to:

* Detect and respond to problems quickly  
* Understand system behavior  
* Track performance metrics  
* Maintain security vigilance  
* Ensure compliance requirements are met

This systematic approach to log monitoring has become essential for maintaining reliable and secure systems in today's complex technological landscape.

## Why is log monitoring necessary?

Log monitoring is essential for maintaining healthy, efficient systems and applications for several reasons.

System visibility is fundamental to understanding your infrastructure's health. Through log monitoring, teams gain real-time insights into system behavior and can track user activities and system operations. This comprehensive view allows organizations to understand exactly what's happening across their entire infrastructure at any given moment.

Service availability is another critical aspect that log monitoring supports. By continuously monitoring logs, teams can quickly detect and diagnose system outages when they occur. This rapid detection enables faster response times to critical issues, helping organizations maintain service level agreements (SLAs) and minimize system downtime. When systems experience issues, logs provide the necessary information to understand what went wrong and how to fix it.

Performance monitoring is equally essential for maintaining efficient operations. Through log monitoring, teams can track system performance metrics over time, identifying bottlenecks and potential issues before they become critical. This includes monitoring resource usage and throughput, helping teams understand when and where performance might be degrading. Organizations can take corrective action by catching performance issues early before users are significantly impacted.

Effective log monitoring makes problem detection and resolution straightforward. When issues arise, logs help pinpoint exactly which services or system components are failing or struggling. This detailed information helps teams identify the root cause of problems and implement solutions more quickly. Log monitoring can also reveal patterns that indicate potential future issues, enabling proactive maintenance and system optimization.

By implementing effective log monitoring, organizations can maintain reliable services, optimize performance, and resolve issues before they significantly impact users or business operations. This proactive approach to system management is essential in today's digital landscape, where system reliability and performance directly impact business success.

## Log monitoring tools

Organizations often begin their logging journey with out-of-the-box monitoring solutions. These tools provide immediate value through pre-built dashboards and visualizations, ready-to-use alerting systems, and built-in integrations with standard services. With quick setup, deployment times, and managed infrastructure, they offer a streamlined path to system observability.

Popular solutions include Datadog, Splunk, Graylog, New Relic, and Sumo Logic. These tools serve as excellent starting points for organizations beginning their observability journey, offering robust functionality without the need to build and maintain custom infrastructure.

## Log monitoring with ClickHouse

As organizations grow, the volume of log data they produce can reach petabyte levels daily. Traditional log monitoring tools often become cost-prohibitive at this scale, leading companies to restrict log retention times. However, having access to extensive historical logs is crucial for engineers to investigate and resolve issues thoroughly. 

ClickHouse offers an economical alternative with its robust capabilities for efficiently managing vast amounts of data.

Our journey at ClickHouse demonstrates this perfectly. When scaling our cloud service, we faced the same challenges many large organizations encounter with traditional logging solutions. Our experience building a 19 PiB logging platform that handles over 37 trillion rows while maintaining 6 months of retention provides a compelling example of what's possible with ClickHouse.

➡️ Read [How we Built a 19 PiB Logging Platform with ClickHouse and Saved Millions](https://clickhouse.ac.cn/blog/building-a-logging-platform-with-clickhouse-and-saving-millions-over-datadog)

This transformation isn't unique to us. Didi, one of the world's largest mobility platforms serving over 450 million users, faced similar challenges with their Elasticsearch implementation. Their successful migration to ClickHouse for log storage resulted in a 30% reduction in hardware costs while handling petabyte-level daily log volumes. Their story provides another excellent example of ClickHouse's capabilities in handling enterprise-scale log monitoring.

➡️ Read [Didi Migrates from Elasticsearch to ClickHouse for a new Generation Log Storage System](https://clickhouse.ac.cn/blog/didi-migrates-from-elasticsearch-to-clickHouse-for-a-new-generation-log-storage-system)

Trip.com's migration to ClickHouse for its logging platform provides another compelling case study of successful large-scale log management. After facing significant challenges with its 4PB Elasticsearch deployment, including cluster instability, performance degradation, and high costs, Trip.com successfully migrated to ClickHouse and scaled to over 40PB of data. 

The results were remarkable: they achieved 50% storage space savings, 4-30 times faster query performance with P90 queries under 300ms, and significantly lower total cost of ownership. Their success stemmed from ClickHouse's columnar storage design, efficient compression, SQL-based querying, and superior scalability. The platform now handles massive concurrent workloads while maintaining longer retention periods, demonstrating ClickHouse's capability to manage enterprise-scale logging requirements cost-effectively and efficiently.

➡️ Read [How trip.com migrated from Elasticsearch and built a 50PB logging solution with ClickHouse](http://trip.com)

Log monitoring

ClickHouse source code is published under the Apache 2.0 License. Software is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

logo_without_text.svg

Stay informed on feature releases, product roadmap, support, and cloud offerings!

Subscribe to our newsletter

icon-github.svg

icon-slack.svg

icon-telegram.svg

icon-twitter.svg

ClickHouse 工程资源

OLTP vs OLAP

列式数据库详解

什么是实时分析？

实时数据可视化

时间序列数据库简介

什么是 JSON 数据库？

什么是数据应用？

Avro vs Parquet

结构化、非结构化和半结构化数据

使用 ClickHouse 和 Streamlit 在 Python 中构建仪表板

日志监控