Creating Materialized Views

After executing a SQL Stream job, you can set up the Materialized Views to have a snapshot of your queried data. You can use the URL Pattern from the Materialized View to visualize the generated data.

  • You need to have a running SQL job on which you apply the Materialized Views configuration.
  1. Navigate to the Streaming SQL Console.
    1. Navigate to Management Console > Environments, and select the environment where you have created your cluster.
    2. Select the Streaming Analytics cluster from the list of Data Hub clusters.
    3. Select Streaming SQL Console from the list of services.
    The Streaming SQL Console opens in a new window.
  2. Run a SQL Stream job, and wait until data is shown on the results tab.
    For more information on running a SQL job, see the Running a SQL Stream job documentation.
  3. Select the Materialized View sub-tab on the Compose tab.
  4. Select Enabled for Materialized View.
  5. Select a Primary Key.
    If this list is empty, then no SQL is specified on the SQL sub-tab, or that SQL is invalid. Select a key as a primary key for the Materialized View. All data will be updated by this key.
  6. Select a Retention Period.
    Data not being mutated during this period is removed from the view.
  7. Enable or disable Recreate on Job Start.
    If enabled, the Materialized View is deleted when a job is started or restarted.
  8. Enable or disable Ignore NULLS.
    If enabled, NULL values will NOT update values that are non null - they are ignored.
  9. Select an API Key.
    In case there are no API Keys, click Add API key, or click Materialized Views from the main menu. The add API key window appears. Provide a name for the API key, and click Save Changes.
    To check your created API keys:
    1. Click Materialized Views from the main menu.

      The Materialized Views page appears.

    2. Click [show] under Key to see the API Key.
  10. Click Advanced Settings for more configureation.
    1. Set Delete data to True to delete and recreate the Materialized View when restarting the SQL job. This setting is needed when the schema is changed for a job.
  11. Click Apply Configuration.
  12. Click Add Query to create the Materialized View query.
    The Materialized View Query Configuration window appears.
  13. Provide a name to the URL Pattern.
  14. Provide a description of the Materialized View Query, if needed.
  15. Customize the Materialized View in the Query Builder.
    1. Select the columns of the SQL job you want to use in the Materialized View Query.
    2. Click Add filters to apply computations and further enrichment of your data.
    3. Click Add rule to save the filter.
  16. Click Save Changes.
    The Pattern URL appears under Materialized View Queries header.
You can click the created REST endpoint to review the data, or copy it and visualize the queried data in a Business Intelligence tool, notebook, code and so on.