
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.com/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.mysql.com/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.com/docs/en/sql-reference/data-types/newjson) in [version 24.8](https://clickhouse.com/blog/clickhouse-release-24-08) in August 2024\. 

The new data type [addresses the following challenges](https://clickhouse.com/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?

OLTP vs OLAP


> 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.com/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.com/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.com/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://developer.mozilla.org/en-US/docs/Web/API/WebSockets_API) and [server-side events](https://developer.mozilla.org/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://www.sqlalchemy.org/) 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.apache.org/) 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.com/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.com/docs/en/integrations/looker), but it has some limitations on how it handles ClickHouse’s data types.  
* [**Metabase**](https://www.metabase.com/) 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://www.metabase.com/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.com/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/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.com/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.apache.org/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


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.com/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.com/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.com/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.com/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.com/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.

## 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 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.com/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.com/docs/en/integrations) with Apache Iceberg, MongoDB, MySQL, AirByte, and more.

### Query latency

[Query performance is a top priority](https://clickhouse.com/docs/en/concepts/why-clickhouse-is-so-fast) during the development of ClickHouse. ClickHouse Cloud customers typically see [second](https://clickhouse.com/blog/data-hive-the-story-of-beehiivs-journey-from-postgres-to-clickhouse) or [sub-second](https://clickhouse.com/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.com/blog/behind-the-scenes-how-clickhouse-helps-vimeo-power-video-analytics-at-scale).  It's also used in [real-time dashboards](https://clickhouse.com/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.com/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://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.com/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.com/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.com/blog/adding-analytics-to-an-application-with-clickhouse-query-endpoints)  



What is real-time analytics?

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 Engineering Resources

What is a JSON database?

OLTP vs OLAP

Real-time data visualization

What is real-time analytics?