Today, I am pleased to announce that [RisingWave](https://risingwave.com/) is now a supported data source within Cube. RisingWave is an innovative distributed streaming database that enables processing and management of real-time data with an easy to use Postgres-style SQL syntax. RisingWave ingests event data from message queues and operational databases and it can perform inflight joins, filtering, aggregations and other transformations. It is available as an open-source edition and a managed cloud service.

Using a Postgres-style SQL syntax, RisingWave benefits from being easy to implement, easy to learn, and having a wide ecosystem. Belief in Postgres as a standard is something we both share in common, as our SQL-API is Postgres compliant as well. In RisingWave, streams can be processed as a series of materialized views, reducing logical and management complexity. RisingWave can be scaled both horizontally and vertically, and implements a separation of storage of compute resources.

Users can combine the streaming data processing capabilities of RisingWave with Cube’s [universal semantic layer](https://cube.dev/use-cases/semantic-layer) to build custom real-time data applications to solve analytics problems in the most challenging settings. In financial services, this might look like streaming trades into RisingWave and evaluating real-time portfolio performance and risk management with a custom application built on top of the data modeling, security contexts and GraphQL API provided by Cube. In manufacturing, telecom, or utility sectors, this might take the form of streaming sensor (IoT) data into RisingWave and building speciality data applications using Cube and charting libraries to inform preventative maintenance and reduce risk of outages. There are many other possibilities to leverage the power of real-time data in your data applications, business intelligence tools, or other emerging use cases such as AI. 

To use RisingWave as a data source in Cube, simply select the Postgres data source type and use your RisingWave credentials there. Since RisingWave supports a familiar Postgres-style SQL syntax, it is easy to get started with. For more detailed documentation on setting up a connection, check out the [documentation here](https://cube.dev/docs/product/configuration/data-sources/risingwave). As with all of our other data sources, all of the Cube functionality you know and love just works with RisingWave. 

Here at Cube we’re always eager to see what our users are building. We believe that our partnership and integration with RisingWave will allow users to build some incredible things. We can’t wait to see what you all come up with!

Brian Bickell

All Things Cube

Announcing Support for RisingWave in Cube

[Materialize](https://materialize.com) is a different kind of database: on the surface it presents as Postgres, but internally it maintains the results of a query as a materialized view that stays up-to-date as the data changes. While a traditional database does the evaluation when a SELECT statement is issued, Materialize asks for queries upfront and computes the results on the fly as new data arrives.

As we’ll discuss more below, this makes Materialize great for scenarios where you need the complex join and transformation capabilities of a warehouse, but you want to serve the results to applications.

## What is Cube?

Cube is a headless business intelligence platform that makes data accessible and consistent across every application. Cube provides data modeling, access control, caching, and a variety of APIs (SQL, GraphQL, and REST) on top of any data warehouse, database, or query engine, including Materialize.

If you’re familiar with the concepts of “headless CMS” or “headless ecommerce”, you already have the right mental model for Cube. It’s essentially a “headless BI”—say, open-source Looker with less UI and more API—that delivers consistent data to your BI tools, data apps, and notebooks.

## The Integration

Integrating Cube and Materialize was a perfect example of the benefits of building in public and using well-known protocols.

Cube is open source and has an existing [PostgreSQL driver](https://cube.dev/docs/config/databases/postgres). Materialize [contributed](https://github.com/cube-js/cube.js/pull/4320) a lightly modified version of the PostgreSQL driver to help Cube read from Materialize.

![Materialize integration](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/d8ed003d-aa86-497b-9e51-219539351db4.png)

## Why are Materialize and Cube useful together?

Data warehouses were built with the end goal of showing people data. They work best for data exploration, analysis and reporting. But just looking at the data is no longer sufficient: now we’re connecting *systems* (APIs, applications, tools and automation) to the data in the warehouse.

When people point systems at data warehouses, they inevitably face performance problems:
* Not built for lookup queries
* Not built for low latency and high concurrency
* Reactivity is slow and time-to-insight is high

Also, there’s always a gap between how the data is stored and how the data is analyzed. Systems that consume the data (e.g., data apps or BI tools) and people working with them are most comfortable dealing with business-level metrics definitions (e.g., “revenue growth MoM by countries”) rather than calculations over the raw data in the warehouse.

With Materialize, you get warehouse-style views of your business data at application-style availability (always up-to-date, low latency queries). Combine that with Cube’s semantic layer and a variety of APIs to get a “live read API for your entire business”.

## Show me an example!

Here’s how you can make Materialize work with Cube for fun and profit. Let’s build an application that consumes a live stream of Hacker News stories but only shows the ones that ranked in top 3 so you can still have plenty of free time.

**Running Materialize and Cube.** Both tools have managed cloud-based offerings ([Materialize Cloud](https://materialize.com/materialize-cloud-access/) and [Cube Cloud](https://cube.dev/cloud/)) but, for the sake of simplicity, we can run them in Docker. The following `docker-compose.yml` file will run Materialize, seed it with data, and then run Cube:

```yaml
version: '2.2'
 
services:
 materialize:
   image: materialize/materialized:v0.26.1
   ports:
     - 6875:6875
 
 seed:
   image: jbergknoff/postgresql-client
   volumes:
     - .:/seed
   entrypoint: ["sh", "seed/seed.sh"]
   depends_on:
     - materialize
 
 cube:
   image: cubejs/cube:latest
   ports:
     - 4000:4000
   environment:
     - CUBEJS_DEV_MODE=true
     - CUBEJS_DB_TYPE=materialize
     - CUBEJS_DB_HOST=materialize
     - CUBEJS_DB_PORT=6875
     - CUBEJS_DB_NAME=materialize
     - CUBEJS_DB_USER=materialize
     - CUBEJS_API_SECRET=SECRET
   volumes:
     - .:/cube/conf
   depends_on:
     - seed
```

In the `seed.sh` file, we’ll use a vanilla PostgreSQL client to connect to the Materialize instance and run three SQL queries: the first one will connect a live [PubNub stream](https://www.pubnub.com/developers/realtime-data-streams/hacker-news-articles/) with Hacker News stories data, the other one will transform the stream into a normalized table view with columns, and the last one will create a materialized view of the stories:

```bash
#!/bin/bash
 
cat > seed.sql << EOL
 
CREATE SOURCE hn_raw
FROM PUBNUB
SUBSCRIBE KEY 'sub-c-c00db4fc-a1e7-11e6-8bfd-0619f8945a4f'
CHANNEL 'hacker-news';
 
CREATE VIEW hn AS
SELECT
  (item::jsonb)->>'link' AS link,
  (item::jsonb)->>'comments' AS comments,
  (item::jsonb)->>'title' AS title,
  ((item::jsonb)->>'rank')::int AS rank
FROM (
  SELECT jsonb_array_elements(text::jsonb) AS item
  FROM hn_raw
);
 
CREATE MATERIALIZED VIEW hn_top AS
SELECT link, comments, title, MIN(rank) AS rank
FROM hn
GROUP BY 1, 2, 3;
 
EOL
 
psql -U materialize -h materialize -p 6875 materialize -f ./seed.sql
```

The only missing piece is Cube’s data model file, placed under `schema/HN.js`. Cube will read from the live-updated materialized view and expose metrics via its APIs. 

```js
cube(`HN`, {
  sql: `SELECT * FROM public.hn_top`,
 
  refreshKey: {
    every: '1 second'
  },

  measures: {
    count: {
      type: `count`
    },
 
    countTop3: {
      type: `count`,
      filters: [ {
        sql: `${rank} <= 3`
      } ]
    },
 
    bestRank: {
      sql: `rank`,
      type: `min`
    }
  },
  
  dimensions: {
    link: {
      sql: `link`,
      type: `string`
    },
 
    comments: {
      sql: `comments`,
      type: `string`
    },
 
    title: {
      sql: `title`,
      type: `string`
    },
 
    rank: {
      sql: `rank`,
      type: `number`
    }
  },
 
  segments: {
    show: {
      sql: `${title} LIKE 'Show HN:%'`
    }
  }
});
```

You copy and paste these files or [pick them from GitHub](https://github.com/cube-js/cube.js/tree/master/examples/materialize). Now, we can run the whole stack with `docker compose up`.

**Exploring the data.** Now, if you navigate to Cube’s Playground at [localhost:4000](http://localhost:4000), you’ll instantly be able to run queries against the Materialize instance with connected stream by picking measures, dimensions, segments, and filters in the UI.

Here’s how you can check how many stories we have had in the data stream (30) and how many of them have even been ranked in top 3 (no surprise, just three):

![First query](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/14277bc2-2177-4350-9d2a-8abda583df6c.png)

However, if you wait a few minutes and refresh the query results, you’ll see that data changes because new stories are posted to HN and existing stories change their ranks.

Then, here’s how you can combine segments and filters to pick only “Show HN” stories ranked in top 3:

![Second query](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/615c7251-480b-4bdb-98b4-ed5e821d965f.png)

**Connecting to APIs.** Cube exposes SQL, GraphQL, and REST APIs, each with its own query syntax. For instance, the query directly above is expressed as the following JSON query accepted by the REST API:

```json
{
  "dimensions": [
    "HN.title",
    "HN.rank"
  ],
  "order": {
    "HN.rank": "asc"
  },
  "filters": [ {
    "member": "HN.rank",
    "operator": "lte",
    "values": [ "10" ]
  } ],
  "segments": [
    "HN.show"
  ]
}
```
Let’s run this query against the REST API and extract the result set with the amazing `jq` utility:

```bash
curl localhost:4000/cubejs-api/v1/load -G -s \
  --data-urlencode "query=$(cat query.json)" | jq '.data'
```
![REST API usage](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/a3bd5786-df83-406b-bded-f47e5a099641.png)

Also, if you are a GraphQL aficionado, you can use GraphQL API and an alternative syntax:

![GraphQL API usage](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/63a0e4fc-1f67-4e02-8ebc-71479dc8d923.png)

And, there’s one more thing—Cube’s [real-time data fetch](https://cube.dev/docs/real-time-data-fetch) based on WebSockets is a great match to the real-time experience of Materialize. You can use that to live-feed your data from the streams through Materialize and Cube all the way down to your end-user front-end application. 

**Future improvements.**  The current implementation of Cube’s Materialize driver makes Cube periodiucally poll Materialize for updates. A more effective and native for Materialize way to get data out of Materialize is subscribing to changes using the [TAIL feature](https://materialize.com/docs/sql/tail/). This would require more in-depth changes which we are exploring.

## Try Materialize and Cube for yourself

Feel free to check out Materialize and Cube by running the example above on your machine. The full source code is [available on GitHub](https://github.com/cube-js/cube.js/tree/master/examples/materialize). Also, please don’t hesitate to join Cube’s [Slack community](https://slack.cube.dev) or Materialize’s [Slack community](https://materialize.com/s/chat) to share your feedback.

To give both tools a try in a professional setting, check out [Materialize Cloud](https://materialize.com/materialize-cloud-access/) and [Cube Cloud](https://cube.dev/cloud/). The future is real-time, let’s build it together with Materialize and Cube.



Streaming Databases (RTDB)

Materialize

Announcing Support for RisingWave in Cube

Announcing the Cube integration with Materialize: Building real-time applications over data streams

Get Cube updates to your inbox

Upgrade your data stack today