Concepts → Materialized View

About a Materialized View

A materialized view (MV) is an entity object in a physical schema that defines an Apache Spark job. The definition of the job consists of a script in a Spark compatible language. The job typically reads data for existing entity objects in a physical schema using the related Apache Parquet files in shared storage. During a load job of the parent physical schema or the materialized view, the related Spark job saves the resulting dataframe to shared storage in a Parquet file.

A materialized view depends on existing entity objects in a physical schema (and in certain cases, can include runtime business views). This dependency defines a materialized view as a type of derived table. As a derived table, a materialized view processes already extracted data and typically functions to transform and enrich data within a given physical schema.

Properties of a Materialized View

The properties of a materialized view are:

• Name
• Data Source(s)
• Columns
• Formula Columns
• Runtime Security Filters
• Parent Joins
• Child Joins
• Load Filter

Name

A materialized view name must adhere to the following name validation rules:

• Must be unique to the physical schema
• Must be between 1 and 128 characters in length
• Must begin with an alpha character, lower or upper case, (_), or ($)
• After the first alpha character, can contain zero or more alphanumeric characters in lower, upper, or mixed case
• After the first alpha character, can contain zero or more underscore (_) or Dollar sign ($) characters
• Besides underscore (_) and Dollar sign ($), cannot contain special characters, symbols, or spaces

Data Source

The data source for a materialized view is Apache Spark. A materialized view only supports a single-source dataset. A schema developer can combine multiple dataframes with the script of a materialized view.

Starting with the 2021.4.2 release, you can define the number of the data frame partitions resulting from loading a materialized view. In the Data Source dialog, you can add the spark.dataframe.partitions property and set the number of the data frame partitions as appropriate. If required, the Loader Service will perform either a coalesce or reparation operation to create the required number of data frame partitions.

Type

The data source type should be set as Materialized View. Changing the data source type will change the type of table created.

Language

Following are the languages available for writing your script:

• Spark SQL
• Spark Python
• Spark Scala
• Incorta PostgreSQL

Incremental

A materialized view supports Incremental loads. You can enable incremental consumption of rows from the data source dialog. When enabled, the Incremental Script dialog will become available. A schema developer must define the materialized view script for the incremental load.

For more details, see Additional Considerations → Incremental loads strategies

Script

You write the script for a materialized view using two different editor options:

Edit Query

The Edit Query window will provide syntax highlighting and formatting capabilities. Incorta will validate the script when completing the data source configuration.

Edit in Notebook

The Notebook Editor allows you to iteratively build, validate, and test queries within the editor. You can enable the Notebook Editor from the Cluster Management Console (CMC). The Notebook Editor is an Incorta Labs feature in your tenant configurations.

IncortaML

From the Notebook Editor, you can use IncortaML (Incorta Machine Learning) to add machine learning models to your materialized view. You can utilize IncortaML to train models using regression and classification modeling.

Properties

When you create a materialized view, you can define the Spark properties for the load job. A CMC Administrator sets the default properties for a materialized view in the tenant’s configuration.

Starting with the 2022.1.0 release, you can submit Spark jobs to the Spark cluster by setting the cluster deploy mode at the MV level for the spark.submit.deployMode property in the Data Source dialog, or at the server level in Spark Integration configurations in the CMC. If the MV deploy mode is defined, it overrides the server configuration, otherwise, the server deploy mode is applied.

Following are configurable Spark properties:

Columns

Once you create a script, the referenced columns will be visible in the Table Editor. See Concepts → Materialized View Column for information on the column properties.

Referenceable Columns

Following are the possible types of referenceable columns in a materialized view script:

Formula Columns

You create a materialized view formula column using the Formula Builder. Materialized view formula columns have several restrictions related to the materialized view’s join configurations. For more information see Concepts → Materialized View Formula Column.

Global Variables

Starting the 2022.1.0 release, you can reference global variables in MVs when you use the Notebook Editor or the Query Builder to add or edit the MV script (or incremental script) using any supported language.

To reference a global variable, precede the variable name with a double dollar sign, for example, $$gvar_name. When referencing string, date, and timestamp global variables, you must use single or double quotes depending upon the MV script language. For Spark Scala, use double quotes, for other languages, use single quotes, for example '$$string_gvar_name'.

Runtime Security Filters

As a schema developer, you can apply one or more runtime security filters to restrict row access to a materialized view. Any dependent object such as a runtime business view, dashboard insight, dashboard filter, or internal session variable will automatically apply the runtime security filter. To learn more about a physical schema table runtime security filters, see Concepts → Runtime Security Filter.

Parent Joins

The materialized view is the parent table in a join relationship to another entity object in the same or different physical schema. By default, the join condition represents a left outer join where the child is on the left side of the join and the parent is on the right side of the join.

A parent table typically has one key column or composite key columns. In both cases, the key column functions as a primary key. Where there is a key column, the join condition typically contains an equals = join operator.

It is possible to create a join from a child to parent without using a defined key column in the parent table. A table without a key column allows for duplicate values. If the parent table contains duplicate non-key rows, the Loader Service randomly selects a parent table row based on the non-key join. To help simulate a key for the parent table, consider using one or more conditions in the join definition such as > greater than and < less than. Multiple join operators may significantly narrow join values from the parent table so as to create row uniqueness. A join condition with multiple join operators requires internal sorting which may require high calculation costs for the join path.

Child Joins

The materialized view is the child table in a join relationship to another entity object in the physical schema or another physical schema within the tenant. A child table typically has a column that serves as a foreign key. By default, the join condition represents a left outer join where the child is on the left side of the join and the parent is on the right side of the join.

Join inheritance

Materialized views do not inherit join relationships from source entities and will need to have their own join relationships created. The data sources available to a materialized view formula column are directly related to the child joins of the materialized view.

Load Filter

For a materialized view, you can specify a load filter in the Table Editor. A load filter reduces the number of rows that the Analytics Service loads into memory from shared storage, but does not affect how many rows the Loader Service extracts from the data source of the table.

Additional Considerations

Script referencing a schema entity

A script referencing a column in a physical schema or business schema entity must identify the schema and entity. As an example, a SQL query would identify the schema and entity as SCHEMA.ENTITY.

Incremental load strategies

In incremental load jobs, the Loader Service instructs Spark to fetch only new and updated records. Starting with the 2021.4.3 release, MVs support two different incremental load strategies: the MV last successful transformation time and the maximum value of a column in the MV.

The last successful transformation time is the only strategy available in releases before 2021.4.3. The Loader Service evaluates the ? in the Incremental Script query to the time of the last successful transformation of the MV. In this strategy, some records may be skipped in the next incremental load jobs if the MV’s source object is updated during the transformation time.

The maximum value of a column is the newly supported strategy in MVs. This strategy depends on comparing the maximum available value of a column in the MV with the values of a column in the MV’s source object. You specify this MV column in the MV data source properties. This column can be a timestamp/date or numeric column. The Loader Service resolves the ? in the Incremental Script to the maximum value available in the selected MV column before passing the script to Spark.

Here is an example of an MV script and incremental script using Spark SQL:

• Script: SELECT * FROM Sales.invoices
• Incremental Script: SELECT * FROM Sales.invoices WHERE Sales.invoices.ModifiedDate > ?

And the same example using Spark Python:

• Script:

  Copy
  df = read("Sales.invoices")
  save(df)
  
  
  

• Incremental Script:

  Copy
  df = read("Sales.invoices")
  df = df.filter("ModifiedDate > ?")
  save(df)
  
  

Query methods in Notebook editor

Starting in 2022.10.0, you can query the physical schema and business schema from the Notebook editor using the following methods.

Script restrictions

Schema restrictions

When you create your materialized view using Incorta PostgreSQL, you are able to reference a business schema view or an alias. These options are not available when using the other provided spark languages.

Session variables

A materialized view cannot reference business schema entities that contain session variables. Attempting to do so will result in an error when attempting to validate the script.

Spark jobs concerning a materialized view

When you modify the Spark properties of a materialized view, you may need to see the spark job execution of the materialized view. Any Spark job changes should be made in coordination with your System Administrator.

You can view the Spark job processes at the following URL: http://<server address>:9091

When you validate your script in the query editor, the materialized view will display as one spark job.

While you are running the Notebook Editor in a materialized view, Spark will display the Notebook Editor as a running application. Once you finish using the Notebook Editor and you validate the materialized view script, you will see a second job run when the materialized view is validated.