Real-time data analytics with Apache Superset, Redpanda, and RisingWave
Tags: database, tutorial, ui, architecture
Published: 2023-05-20 10:56:20

In today's fast-paced data-driven world, organizations must analyze data in real-time to make timely and informed decisions. Real-time data analytics enables businesses to gain valuable insights, respond to real-time events, and stay ahead of the competition. Also, the analytics engine must be capable of running analytical queries and returning results in real-time. In this article, we will explore how you can build a real-time data analytics solution using the open-source tools [Redpanda](https://redpanda.com/) a distributed streaming platform, [Apache Superset](https://superset.apache.org/), a data visualization, and a business intelligence platform, combined with [RisingWave](https://www.risingwave.com/) a streaming database.

There is a dedicated tutorial on [how to monitor live stream metrics](https://www.risingwave.dev/docs/current/live-stream-metrics-analysis/) where you learn how to set up the project using [Docker Compose](https://github.com/risingwavelabs/risingwave/blob/main/integration_tests/superset/docker-compose.yml), and how to create materialized views and query results with SQL. You can access the demo code needed to complete the tutorial in [the GitHub repo here](https://github.com/risingwavelabs/risingwave/tree/main/integration_tests/superset).

This article breaks down each component of the real-time streaming data pipeline architecture and dives into the details of how they interact with each other for our example **live stream metrics analysis** where data is ingested and analyzed in real-time from live streams to test video quality, or tracking the number of unique live viewers.

## Real-time data analytics stack

A real-time data analytics stack typically consists of a set of tools and technologies that are used to ingest, process, analyze, and visualize data in real-time. This table summarizes some common components of a typical real-time data analytics stack both open-source and enterprise:

![real-time data analytics stack](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zlqyeujmkiai34gyra7u.png)

## Live stream analytics system architecture

Below is a diagram of the architecture for our sample project that process live streams and provides data in a BI (Business intelligence) dashboard.


![the architecture for our sample project that process live streams and provides data in a BI](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f9yw1ipnfh049u85j36z.png)

### Data stream producer

The **first component** in the above architecture is the **data stream generator** which is the [Golang program](https://github.com/risingwavelabs/risingwave/blob/main/integration_tests/datagen/livestream/livestream.go) in our case. Basically, it simulates incoming live metrics data, creates a Kafka topic called `live_stream_metrics`, and populates it with new raw live stream data. In reality, this can be **replaced with your backend service** that creates and write messages to Kafka topics.

> You can also sync data from your persistent data sources like Postgres or MySQL databases using [CDC(Change data capture), Debezium, and Redpanda](https://redpanda.com/blog/change-data-capture-postgres-debezium-kafka-connect). For example, as the database writes occur, Debezium exposes the change feed as streams to Redpanda.

### Streaming data platform

We use [Redpanda](https://redpanda.com/) for real-time stream processing. It provides built-in support for stream processing using its embedded [Kafka Streams API](https://redpanda.com/platform), allowing you to handle millions of events per second and providing low-latency data processing without JVM, code changes. This makes it ideal for ingesting and processing live stream metrics in real-time.

### Streaming database

The next component, [RisingWave](https://www.risingwave.com/) is a streaming database specialized in real-time analytics that is designed to consume data from various sources such as [ingesting data from Redpanda](https://www.risingwave.dev/docs/current/create-source-redpanda/) and creating materialized views, and making it possible to [query Kafka topics using SQL](https://www.iambobur.com/post/query-real-time-data-in-kafka-using-sql). With the streaming database, the same query can run continually as data changes, creating SQL-based data for monitoring. 

**Connect RisingWave to Redpanda**

Now assume those live stream events coming into Redpanda from the stream producer, we can connect RisingWave to Redpanda to receive real-time events. To do so, we create a [source](https://www.risingwave.dev/docs/current/key-concepts/#sources) in RisingWave using the `CREATE SOURCE`SQL statement:

```sql
CREATE SOURCE live_stream_metrics (
    client_ip VARCHAR,
    user_agent VARCHAR,
    user_id VARCHAR,
    -- The live room.
    room_id VARCHAR,
    -- Sent bits per second.
    video_bps BIGINT,
    -- Sent frames per second. Typically 30 fps.
    video_fps BIGINT,
    -- Round-trip time (in ms). 200ms is recommended.
    video_rtt BIGINT,
    -- Lost packets per second.
    video_lost_pps BIGINT,
    -- How long was the longest freeze (in ms).
    video_longest_freeze_duration BIGINT,
    -- Total freeze duration.
    video_total_freeze_duration BIGINT,
    report_timestamp TIMESTAMPTZ,
    country VARCHAR
) WITH (
    connector = 'kafka',
    topic = 'live_stream_metrics',
    properties.bootstrap.server = 'message_queue:29092',
    scan.startup.mode = 'earliest'
) ROW FORMAT JSON;
```

In the above example, RisingWave subscribes to the topic `live_stream_metrics` in Kafka using Redpanda (`message_queue`service is a Redpanda instance in Docker) and we specified what fields we are interested to read from the topic. However, RisingWave only starts to consume data in real-time from the Kafka topic only after we create a materialized view.

**Create a** **materialized view for view counts**

A new materialized view will track the number of unique viewers on the entire streaming site every minute. We will use the [tumble](https://www.risingwave.dev/docs/current/sql-function-time-window/#tumble-time-window-function) function to map each event into a one-minute window and count the number of distinct viewers within each time window.

```sql
-- A real-time dashboard of the total UV.
CREATE MATERIALIZED VIEW total_user_visit_1min AS
SELECT
    window_start AS report_ts,
    COUNT(DISTINCT user_id) AS uv
FROM
    TUMBLE(
        live_stream_metrics,
        report_timestamp,
        INTERVAL '1' MINUTE
    )
GROUP BY
    window_start;
```

Now, we can query the results with the following SQL statement.

```sql
select
    *
FROM
    total_user_visit_1min
LIMIT
    1;
```

### BI and data analytics platform

The **fourth component** in our architecture is a user-facing **data analytics dashboard**. Once our streaming database is set up and receives live stream metrics data, we need to configure [Apache Superset](https://superset.apache.org/) to connect to it. It provides a web-based interface for data exploration and visualization, making it accessible to users with little to no coding experience. It supports a wide range of data sources. With Superset, you can explore real-time data in various ways, such as filtering, drilling down, and aggregating data based on different dimensions and measures.

You can configure Superset to connect to RisingWave by using custom [SQLAlchemy database](https://www.risingwave.dev/docs/current/superset-integration/#establish-the-connection-between-superset-and-risingwave) configurations. This guide shows [how to configure Superset](https://www.risingwave.dev/docs/current/superset-integration/) to read data from RisingWave, create a dashboard and visualize the materialized views we created in a table view.

![visualize the materialized views in Apache Superset](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f0hnu8phd26hl6ey9oiw.png)

## Conclusion

In summary, Redpanda, RisingWave, and Superset provide a powerful and scalable solution for live stream metrics analysis, allowing you to ingest, process, store, visualize, and analyze live stream metrics data in real-time. They are well-suited for handling large-scale, high-velocity data streams and provide an end-to-end solution for monitoring and analyzing live stream metrics in a modern, distributed, and user-friendly manner.

### Related resources

- [Build and Maintain Real-time Applications Faster and Easier with Redpanda and RisingWave.](https://www.risingwave.com/blog/build-with-Redpanda-and-RisingWave/)
- [How to monitor live stream metrics](https://www.risingwave.dev/docs/current/live-stream-metrics-analysis/).
- [Query Real-Time Data in Kafka Using SQL](https://www.iambobur.com/post/query-real-time-data-in-kafka-using-sql).

### Recommended content

- [Is RisingWave the Right Streaming Database?](https://www.risingwave-labs.com/blog/is-risingwave-the-right-streaming-database/#durability)
- [Rethinking Stream Processing and Streaming Databases](https://www.risingwave-labs.com/blog/Rethinking_stream_processing_and_streaming_databases/)

### Community

🙋 [Join the Risingwave Community](https://join.slack.com/t/risingwave-community/shared_invite/zt-120rft0mr-d8uGk3d~NZiZAQWPnElOfw)

### About the author

- Visit my personal blog: [www.iambobur.com](https://www.iambobur.com/)
- Follow me on Twitter: [BoburUmurzokov](https://twitter.com/BoburUmurzokov)