13.3 C
New York
Monday, March 4, 2024

Materialized Views in SQL Stream Builder

Cloudera SQL Stream Builder (SSB) provides the ability of a unified stream processing engine to non-technical customers to allow them to combine, combination, question, and analyze each streaming and batch knowledge sources in a single SQL interface. This permits enterprise customers to outline occasions of curiosity for which they should repeatedly monitor and reply rapidly.  

There are lots of methods to distribute the outcomes of SSB’s steady queries to embed actionable insights into enterprise processes. On this weblog we’ll cowl materialized viewsa particular kind of sink that makes the output accessible through REST API. 

In SSB we are able to use SQL to question stream or batch knowledge, carry out some kind of aggregation or knowledge manipulation, then output the end result right into a sink. A sink may very well be one other knowledge stream or we might use a particular kind of knowledge sink we name a materialized view (MV). An MV is a particular kind of sink that enables us to output knowledge from our question right into a tabular format endured in a PostgreSQL database. We are able to additionally question this knowledge later, optionally with filters utilizing SSBs REST API. 

If we need to simply use the outcomes of our SQL job from an exterior utility, MVs are the perfect and simplest way to take action. All we have to do is outline the MV on the UI interface and functions will be capable to retrieve knowledge through REST API.

Think about, for example, that we’ve a real-time Kafka stream containing airplane knowledge and we’re engaged on an utility that should obtain all planes in a sure space, above some altitude at any given time through REST. This isn’t a easy activity to do, since planes are consistently shifting and altering their altitudes, and we have to learn this knowledge from an unbounded stream. If we add a materialized view to our SSB job, that may create a REST endpoint from which we will retrieve the newest end result from our job. We are able to additionally add filters to this request, so for instance, our utility can use the MV to indicate all of the planes which might be flying greater than some user-specified altitude.

Creating a brand new job

An MV at all times belongs to a single job, so to create an MV we should first create a job in SSB. To create a job we will even must create a mission first which is able to present us a Software program Improvement Lifecycle (SDLC) for our functions and permits us to gather all our job and desk definitions or knowledge sources in a central place.

Getting the info

For example we’ll use the identical Computerized Dependent Surveillance Broadcast (ADS-B) knowledge we utilized in different posts and examples. For reference, ADS-B knowledge is generated and broadcast by planes whereas flying. The info consists of a airplane ID, altitude, latitude and longitude, pace, and many others.

To raised illustrate how MVs work, let’s execute a easy SQL question to retrieve all the knowledge from our stream. 

SELECT * FROM airplanes;

The creation of the “airplanes” desk has been omitted, however suffice it to say airplanes is a digital desk we’ve created, which is fed by a stream of ADS-B knowledge flowing by way of a Kafka subject. Please examine our documentation to see how that’s executed. The question above will generate output like the next:

As you possibly can see from the output, there are all types of attention-grabbing knowledge factors. In our instance let’s deal with altitude.

Flying excessive

From the SSB Console, click on on the “Materialized View” button on the highest proper:

An MV configuration panel will open that may look just like the next:



SSB permits us to configure the brand new MV extensively, so we’ll undergo them right here.

Allow MV

For the MV to be accessible as soon as we’ve completed configuring it, “Allow MV” should be enabled. This configuration additionally permits us to simply disable this characteristic sooner or later with out eradicating all the opposite settings.

Major key

Each MV requires a main key, as this shall be our main key within the underlying relational database as properly. The important thing is likely one of the fields returned by the SSB SQL question, and it’s accessible from the dropdown. In our case we’ll select icao, as a result of we all know that icao is the identification quantity for every airplane, so it’s a excellent match for the first key. 


Retention and min row retention depend

This worth tells SSB how lengthy it ought to hold the info round earlier than eradicating it from the MV database. It’s set to 5 minutes by default. Every row within the MV is tagged with an insertion time, so if the row has been round longer than the “Retention (Seconds)” time then the row is eliminated. Word, there’s additionally an alternate methodology for managing retention, and that’s the subject beneath the retention time, referred to as “Min Row Retention Depend,” which is used to point the minimal variety of rows we wish to hold within the MV, no matter how outdated the info is likely to be. For instance let’s imagine, “We need to hold the final 1,000 rows irrespective of how outdated that knowledge is.” In that case we might set “Retention (Seconds)” to 0, and set “Min Row Retention Depend” to 1,000.

For this instance we won’t change the default values.

API key

As talked about earlier, each MV is related to a REST API. The REST API endpoint should be protected by an API Key. If none has been added but, one might be created right here as properly.


Lastly we get to essentially the most attention-grabbing half, deciding on how you can question our knowledge within the MV database.

API endpoint

Clicking on the “Add New Question” button opens a pop-up that enables us to configure the REST API endpoint, in addition to deciding on the info we wish to question.

As we mentioned earlier, we have an interest within the airplane’s altitude, however let’s additionally add the flexibility to filter the sphere altitude when calling the REST API. Our MV will be capable to solely present planes which might be flying greater than some consumer specified altitude (i.e., present planes flying greater than 10,000 toes). In that case within the “URL Sample” field we might enter:


Word the {param} worth. The URL sample can take parameters which might be specified inside curly brackets. After we retrieve knowledge for the MV, the REST API will map these parameters in our filters, so the consumer calling the endpoint can set the worth. See beneath. 

Select the info

Now it’s time to choose what knowledge to gather as a part of our MV. The info fields we are able to select come from the preliminary SSB SQL question we wrote, so if we mentioned SELECT * FROM airplanes; the “Choose Columns” dropdown can have issues like fmild, icao, lat, counter, altitude, and many others. For our instance let’s select icao, lat, lon and altitude.


We’ve an issue. The info fields within the stream, together with the altitude, are all of VARCHAR kind, making it infeasible to filter for numeric knowledge. We have to make a easy change to our SQL and convert the altitude into an INT, and name it peak, to distinguish it from the unique altitude subject. Let’s change the SQL to the next: 

SELECT *, CAST(altitude AS INT) AS peak FROM airplanes;

Now we are able to change altitude with peak, and use that to filter.


Now to filter by peak we have to map the parameter we beforehand created ({param})  to the peak subject. By clicking on the “Filters” tab, after which the “+ Rule” button, we are able to add our filter.


For the “Discipline” we select peak, for the “Operator” we wish “greater_or_equal,” and for the “Worth” we use the {param} we used within the REST API endpoint. Now the MV question will filter the rows by the worth of peak being better than the worth that the consumer would give to {param} when issuing the REST request, for instance:


That will output one thing just like the next:


Materialized views are a really helpful out-of-the-box knowledge sink, which offer for the gathering of knowledge in a tabular format, in addition to a configurable REST API question layer on high of that that can be utilized by third celebration functions.

Anyone can check out SSB utilizing the Stream Processing Neighborhood Version (CSP-CE). CE makes creating stream processors simple, as it may be executed proper out of your desktop or every other improvement node. Analysts, knowledge scientists, and builders can now consider new options, develop SQL-based stream processors domestically utilizing SQL Stream Builder powered by Flink, and develop Kafka Customers/Producers and Kafka Join Connectors, all domestically earlier than shifting to manufacturing in CDP.

Related Articles


Please enter your comment!
Please enter your name here

Latest Articles