Creating Materialized Views in NQL

Materialized views in Narrative SQL (NQL) offer a powerful way to store the results of complex queries as datasets within the Narrative Data Collaboration Platform. These datasets, generated from materialized views, inherit all the benefits of standard datasets, including accessibility, integration capabilities, and governance. This document details the process of creating materialized views in NQL, emphasizing the available metadata options to optimize your data management practices.

Understanding Materialized Views

A materialized view in NQL is not just a view but a stored dataset created from a specific query. Unlike traditional views that dynamically retrieve data, materialized views hold the data as it was at the time of the view's creation, making it a snapshot of the query result. This approach is particularly useful for capturing and analyzing data states over time.

Example Usage: Creating a Materialized View

CREATE MATERIALIZED VIEW "female_audience"
REFRESH_SCHEDULE = '@daily'
DISPLAY_NAME = 'Female Audience'
DESCRIPTION = 'Audience containing demographic of female users.'
EXPIRE = 'retain_everything'
TAGS = ( '_nio_materialized_view', 'user_engagement', 'metrics' )
WRITE_MODE = 'overwrite'
EXTENDED_STATS = 'all'
PARTITIONED_BY event_date DAY
AS
SELECT "unique_identifier",
        "hl7_gender",
        "age"
FROM narrative.rosetta_stone
WHERE 
    "hl7_gender"."gender" = 'female'
    AND "event_timestamp" > CURRENT_TIMESTAMP - INTERVAL 160 DAYS
    AND rosetta_stone._price_cpm_usd <= 2.00
LIMIT 100 USD PER CALENDAR_MONTH

This example creates a materialized view that tracks daily user engagement, with detailed specifications on display name, description, retention policy, and data partitioning.

Metadata Options for Materialized Views

When creating a materialized view in NQL, several metadata options allow for customization and optimization of the resultant dataset. These options include:

REFRESH_SCHEDULE

• Purpose: Defines how often the materialized view is refreshed with new or updated data. For more information go to the Incremental View Maintenance KB article.
• Syntax: REFRESH_SCHEDULE = { 'hourly' | 'daily' | 'weekly' | 'monthly' | 'once' | *cron expression* }
• Options:
  • enums: the following enums allow the user to easily express how often a refresh should occur { 'hourly' | 'daily' | 'weekly' | 'monthly' | 'once' }.
  • cron expression: Alternatively, users can write a cron expression for more nuanced refresh cadences. For example, a refrsh schedule of '15 15 * * 1' would update the materialzied view every monday at 3:15 PM UTC.

DISPLAY_NAME

• Purpose: Assigns a human-readable name to the dataset, often reflecting its content or business context.
• Syntax: DISPLAY_NAME = '<display_name>'

DESCRIPTION

• Purpose: Provides a detailed description of the dataset's purpose, contents, and any relevant contextual information.
• Syntax: DESCRIPTION = '<description_text>'

EXPIRE

• Purpose: Sets a data retention policy for the dataset, specifying how long data should be retained.
• Syntax: EXPIRE = { <ISO 8601 PERIOD> }
• Options:
  • expireWhen > ISO PERIOD: Data expires after the specified ISO 8601 period.
  • retain_everything: Retains all data indefinitely.
  • expire_everything: Specifies that the data should be expired immediately, effectively not retaining any data.

TAGS

• Purpose: Allows for the assignment of tags to the dataset for organizational and searchability purposes.
• Syntax: TAGS = ( '_nio_materialized_view', '<tag1>', ... )

WRITE_MODE

• Purpose: Determines how the data is written to the dataset when the materialized view is refreshed.
• Syntax: WRITE_MODE = { 'append' | 'overwrite' }
• Options:
  • append: Adds new data to the existing dataset.
  • overwrite: Overwrites the existing dataset with the new data.

EXTENDED_STATS

• Purpose: Enables or disables the collection of extended statistics for the dataset.
• Syntax: EXTENDED_STATS = { 'all' | 'none' }

PARTITIONED_BY

• Purpose: Defines how the dataset should be partitioned, based on specified fields and transformations.
• Syntax: PARTITIONED_BY <field> <transform>, <field2> <transform>

Conclusion

Creating materialized views in NQL is a strategic approach to data management, offering enhanced data analysis capabilities through the persistence of query results. By understanding and utilizing the available metadata options, you can tailor your materialized views to fit your specific data management and analysis needs on the Narrative Data Collaboration Platform.