Adding a Materialized View
One of the features of SQL Stream Builder is creating Materialized Views. Materialized Views let you create a persistent snapshot of the stream that can be queried through a REST endpoint. This can then be queried by external applications and tools. Now, that you know how to query simple and aggregated data, learn how to create Materialized Views.
- How to create a Materialized View
- How to create an API key
- How to add filters to the Materialized View query
In the following example, you will create a Materialized View that shows the latest order count and total amount for each order status. The key will be the status, and the other fields will be updated with the value from the latest 20 second interval.
SELECT * FROM summaries
You also must add an API key to the configuration that will be a part of the generated URL.
Click on the Add API Key button, and provide any type of name to the API key. For example
orders_api_key, and click Save.
After configuring the Materialized View, you can add a query by clicking on the Add
Query button. On the Query Configuration page, specify the URL
and select the fields that you want to include in the Materialized View. You can also add
filters to further narrow down the result. In the following example,
summary_query is the name of the URL,
columns are selected as fields. You can add these fields by clicking Select
All, and removing
window_end with the red
bin icon under Actions. You also have the option to select the
fields from the drop-down menu under Query Builder, and add the fields
individually. A filer is also added to exclude the orders with a CANCELED
Click on the Save Changes button to add the query to the Materialized View. The URL pattern will appear under the Materialized View Queries.
When you click on the URL pattern, you are redirected to the localhost:18131 address. You will receive an error message that no data is generated, because the SELECT query is not executed yet. Go back to the SQL tab on the Streaming SQL Console, and click on the Execute button to start the SQL job. After a couple of seconds, the endpoint becomes active and the results show up on the localhost addrress.
You have completed the Getting Started Guide of the CSA Community Edition. As you already know the basic steps to use SQL Stream Builder, feel free to experiment more and get more familiar with more features of SSB, or you can also continue the tutorial with trying out the PostgreSQL CDC and Filesystem connector guides.