Materialized Views in SQL Stream Builder

neub9
By neub9
4 Min Read


Cloudera SQL Stream Builder Materialized Views

Cloudera SQL Stream Builder (SSB) empowers non-technical users with the ability to integrate, aggregate, query, and analyze both streaming and batch data sources in a single SQL interface. This allows for continuous monitoring and quick response to events of interest for business users.

One way to distribute the results of SSB’s continuous queries is through materialized views—a special type of sink that makes the output available via REST API. Materialized views (MVs) allow for the output of data from queries to be persisted in a tabular format in a PostgreSQL database, making it easily accessible for external applications.

Example Use Case

For example, imagine we have a real-time Kafka stream containing plane data and we need to retrieve information on planes flying above a certain altitude at any given time via REST. By creating a materialized view in SSB, we can easily make the results of our SQL job accessible through a REST endpoint, with the option to add filters for more specific queries.

Creating a New Job

An MV always belongs to a single job, so the first step in creating an MV is to create a job in SSB. This allows for the management of job and table definitions in a central place.

Getting the Data

As an example, we can use Automatic Dependent Surveillance Broadcast (ADS-B) data, which consists of plane ID, altitude, latitude, longitude, speed, and more, generated and broadcast by planes. By executing a simple SQL query, such as SELECT * FROM airplanes;, we can retrieve data from our stream.

Flying High

From the SSB Console, users can configure a new materialized view extensively, enabling features such as identifying a primary key, setting retention and min row retention count, creating an API key, and specifying query parameters through the REST API.

Choose the Data

It’s also possible to select what data to include as part of the materialized view, based on the initial SQL query. If needed, users can make simple changes to the SQL query to ensure compatibility and the ability to filter by specific criteria.

Filtering

By adding filters to the MV query, users can customize the output based on specific conditions, making it possible to retrieve only the data they are interested in via the REST API, for example, retrieving planes flying higher than a certain altitude.

Conclusion

Materialized views in Cloudera SQL Stream Builder are a valuable tool for collecting data in a tabular format and creating configurable REST API query layers, making it easy for external applications to retrieve, filter, and use data from SSB jobs. With extensive configuration options, users can tailor their materialized views to suit their specific needs, allowing for dynamic and efficient data retrieval and analysis.


Share This Article
Leave a comment

Leave a Reply

Your email address will not be published. Required fields are marked *