Using Dynamic Materialized View Endpoints
You can use static or dynamic REST endpoints when creating Materialized Views in SQL Stream Builder. After setting filters for the Materialized View query, you can further filter down the results by using variables in the endpoint URL.
Difference between static and dynamic endpoints
When using a static endpoint, the endpoint URL is a constant string. The logic of filtering is
defined statically at the endpoint configuration and the results of the endpoint call will
always reflect this. A static endpoint is one that only uses filters with static values. For
example, in the following static endpoint the Filter is set to
age greater than 0
, and the endpoint URL is always
/api/v1/query/5200/age
:Dynamic materialized view endpoints use filters that reference parameters instead of
static values. The value of these parameters are specified when the endpoints are used.To create a dynamic filter, after selecting the Value type as
Dynamic, click on the button to
add a new parameter, or select the parameter from the drop-down list, if it had already been
created previously. When creating a new parameter, you will be prompted to provide the name of
the Dynamic Parameter.After specifying the dynamic parameter, click Create. Once the job
is running, you can test the Materialized View endpoint by clicking on the endpoint URL in the
endpoint page:
For dynamic endpoints, you will be prompted for the values of the dynamic parameters when
opening the REST endpoint of the Materialized View.The dynamic parameters are defined as URL query parameters as shown
below:
<endpoint_URL>?key=<api_key>&limit=<max_records>¶m1=<value1>¶m2=<value2>...
The advantage of a dynamic endpoint is that by providing dynamic parameters and a corresponding value, you are able to filter the queried results at runtime, returning only the values you exactly need from that dataset.
You can use multiple dynamic parameters in the endpoint and you can also use the same dynamic
parameter at the different filters to create more specific filtering methods for the results of
the Materialized View query. When removing a filter from a Materialized View, the Dynamic
parameter is not affected, which means that if a filter is deleted from the Materialized View
query, the value for Dynamic Parameter still needs to be provided when opening the REST
endpoint. The Dynamic Parameter needs to be deleted separately from the filter by clicking on
the Name of the Dynamic Parameter, and selecting the delete option from the drop-down menu as
shown in the following example: