Adding a Materialized View

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.

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.

First, you need a New Job with a SELECT query. Add the following SQL query to the SQL window to run the query:
SELECT * FROM summaries
Before executing the query, you need to enable the Materialized View feature. On the Materialized View tab, set the Materialized View configuration to Enabled, and select the order_status as the primary key.

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, order_status, order_count, total_amount and window_start 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 status.

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.