Materialized Views can be used to run scheduled queries that pre-compute and store aggregated data in order to enhance the performance of queries running on large scales of data. Materialized Views automatically prepare scheduled pre-calculated data joins and data aggregations and save them in a separate permanent table. This data is prepared ahead of time in order to enhance query response times. Materialized View data is retained until intentionally removed.
CoolaData project Admin users can create Materialized Views of the data in their project.
To create a Materialized View –
- In the CoolaData Administrator console, select Project → Materialized Views. A list of the existing Materialized Views is displayed. The Created column indicates when the data in the Materialized View was last replaced or updated.
- Click the Add + button or the Create + button. The following displays –
- Fill in the following –
- Enter Name – The name of the Materialized View and of the table to which the data will be saved. Like all table names, this name is case sensitive and cannot include spaces or special characters.
- Target – The target where the table will be saved. Select CoolaData to save the table in your CoolaData project.
- Strategy –
- Append – New lines are added to the Materialized View each time the query is computed.
- Replace – The Materialized View data is overwritten each time the query is computed.
- Email – The email of the person to be notified if the query fails.
- Frequency – The frequency when to compute and run the query.
- Daily – At a specific hour of the day (UTC).
- Weekly – On a specific day of the week, at a specific hour of the day (UTC).
- Monthly – On a specific day of the month, at a specific hour of the day (UTC).
- CRON – Set the frequency by specifying a CRON expression. The CRON expression should be in the format of – minutes (0 – 59) hour (0 – 23) day of month (1 – 31) month (1 – 12) day of week (0 – 6). The star symbol (*) should be placed as a wild card. For example, this CRON expression will run the Materialized View daily at 3:30 am UTC: 30 3 * * *. You may refer to www.cronmaker.com for a description of CRON expression syntax.
- In the center of the window, write the CQL query that defines the data to be retrieved from CoolaData and saved in the Materialized View. The Select clause determines which fields are used from the Materialized View and what their column title is in the results.
Note – Do not use the filters (context) and the date_range (context) features in CQL queries run via Materialized Views. This is because there is no way for the system to know the context of this kind of query – and therefore the scheduled run will fail. Instead write an explicit date range or a relative date range.
- Click Compute to display the results of your query at the bottom of the window and verify its correctness.
- Click Save to save this query as a Materialized View which will run according to the schedule defined in the Frequency and Hour of Day fields. The Materialized View run creates/writes to a table with the same name as the Materialized View in CoolaData.
- Use the Materialized View in a report. The following shows an example of CQL code in a report that extracts data from the Materialized View named user_aggregation from the last seven days. This report will show the latest data that was extracted and stored in the Materialized View.
To read about including data from a Materialized View in a CQL report.
To read about Joining Materialized View Data with CoolaData Behavioral Data.