CREATE MATERIALIZED VIEW
Applies to: Databricks SQL
Important
This feature is in Public Preview.
A materialized view is a view where precomputed results are available for query and can be updated to reflect changes in the input. Each time a materialized view is refreshed, query results are recalculated to reflect changes in upstream datasets. All materialized views are backed by a DLT pipeline. You can refresh materialized views manually, on a schedule, or by scheduling the DLT pipeline in which they’re contained.
Syntax
CREATE MATERIALIZED VIEW [IF NOT EXISTS]
view_name
[ column_list ]
[ view_clauses ]
AS query
column_list
( { column_name [ MASK clause ] [...] } [, ...] )
view_clauses
{ PARTITIONED BY (col [, ...]) |
COMMENT view_comment |
TBLPROPERTIES clause |
SCHEDULE [ REFRESH ] CRON cron_string [ AT TIME ZONE timezone_id ] |
WITH { ROW FILTER clause } } [...]
Parameters
IF NOT EXISTS
Creates the view if it does not exist. If a view by this name already exists, the
CREATE VIEW
statement is ignored.You may specify at most one of
IF NOT EXISTS
orOR REFRESH
.-
The name of the newly created view. The fully qualified view name must be unique.
column_list
Optionally labels the columns in the query result of the view. If you provide a column list the number of column aliases must match the number of expressions in the query. If no column list is specified, aliases are derived from the body of the view.
-
The column names must be unique and map to the output columns of the query.
-
Important
This feature is in Public Preview.
Adds a column mask function to anonymize sensitive data. All future queries from that column will receive the result of evaluating that function over the column in place of the column’s original value. This can be useful for fine-grained access control purposes wherein the function can inspect the identity and/or group memberships of the invoking user in order to decide whether to redact the value.
-
view_clauses
Optionally specify partitioning, comments, user defined properties, and a refresh schedule for the new materialized view. Each sub clause may only be specified once.
-
An optional list of columns of the table to partition the table by.
COMMENT view_comment
A
STRING
literal to describe the table.-
Optionally sets one or more user defined properties.
SCHEDULE [ REFRESH ] CRON cron_string [ AT TIME ZONE timezone_id ]
If provided, schedules the streaming table or the materialized view to refresh its data with the given quartz cron schedule. Only time_zone_values are accepted.
AT TIME ZONE LOCAL
is not supported. IfAT TIME ZONE
is absent, the session time zone is used. IfAT TIME ZONE
is absent and the session time zone is not set, an error is thrown.SCHEDULE
is semantically equivalent toSCHEDULE REFRESH
.You cannot use the
SCHEDULE
syntax in a Delta Live Tables pipeline definition.WITH ROW FILTER clause
Important
This feature is in Public Preview.
Adds a row filter function to the table. All future queries from that table will receive subset of its rows for which the function evaluates to boolean TRUE. This can be useful for fine-grained access control purposes wherein the function can inspect the identity and/or group memberships of the invoking user in order to decide whether to filter certain rows.
-
AS query
A query that constructs the view from base tables or other views.
Required permissions
The user who creates a materialized view (MV) is the MV owner and needs to have the following permissions:
SELECT
privilege over the base tables referenced by the MV.USE CATALOG
privilege on the parent catalog and theUSE SCHEMA
privilege on the parent schema.CREATE
privilege on the schema for the MV.
For a user to be able to refresh the MV, they require:
USE CATALOG
privilege on the parent catalog and theUSE SCHEMA
privilege on the parent schema.- Ownership of the MV or
REFRESH
privilege on the MV. - The owner of the MV must have the
SELECT
privilege over the base tables referenced by the MV.
For a user to be able to query the MV, they require:
USE CATALOG
privilege on the parent catalog and theUSE SCHEMA
privilege on the parent schema.SELECT
privilege over the materialized view.
Row filters and column masks
Important
This feature is in Public Preview.
Row filters let you specify a function that applies as a filter whenever a table scan fetches rows. These filters ensure that subsequent queries only return rows for which the filter predicate evaluates to true.
Column masks let you mask a column’s values whenever a table scan fetches rows. All future queries involving that column will receive the result of evaluating the function over the column, replacing the column’s original value.
For more information on how to use row filters and column masks, see Filter sensitive table data using row filters and column masks.
Managing Row Filters and Column Masks
Row filters and column masks on materialized views should be added through the CREATE
statement.
Behavior
- Refresh as Definer: When the
REFRESH MATERIALIZED VIEW
statement refreshes a materialized view, row filter functions run with the definer’s rights (as the table owner). This means the table refresh uses the security context of the user who created the materialized view. - Query: While most filters run with the definer’s rights, functions that check user context (such as
CURRENT_USER
andIS_MEMBER
) are exceptions. These functions run as the invoker. This approach enforces user-specific data security and access controls based on the current user’s context. - When creating materialized views over source tables that contain row filters and column masks, the refresh of the materialized view is always a full refresh. A full refresh reprocesses all data available in the source with the latest definitions. This ensures that security policies on the source tables are evaluated and applied with the most up-to-date data and definitions.
Observability
Use DESCRIBE EXTENDED
, INFORMATION_SCHEMA
, or the Catalog Explorer to examine the existing row filters and column masks that apply to a given materialized view. This functionality allows users to audit and review data access and protection measures on materialized views.
Limitations
- When a materialized view with a
sum
aggregate over a NULL-able column has the last non-NULL value removed from that column - and thus onlyNULL
values remain in that column - the materialized view’s resultant aggregate value returns zero instead ofNULL
. - Column-reference does not require an alias. Non-column reference expressions require an alias, as in the following example:
- Allowed:
SELECT col1, SUM(col2) AS sum_col2 FROM t GROUP BY col1
- Not Allowed:
SELECT col1, SUM(col2) FROM t GROUP BY col1
- Allowed:
- Materialized views do not support identity columns or surrogate keys.
- Materialized views do not support ad-hoc
OPTIMIZE
andVACUUM
commands. Maintenance happens automatically. - Materialized views do not support table constraints such as
PRIMARY KEY
andFOREIGN KEY
. - Materialized views do not support expectations to define data quality constraints.
Examples
-- Create a materialized view if it doesn't exist
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies
AS SELECT mo.member_id, mb.full_name, mo.movie_title
FROM movies AS mo INNER JOIN members AS mb ON mo.member_id = mb.id;
-- Create and schedule a materialized view to be refreshed daily at midnight.
-- Note: All columns in a GROUP BY need to be explicitly aliased
> CREATE MATERIALIZED VIEW daily_sales
COMMENT 'Daily sales numbers'
SCHEDULE CRON '0 0 0 * * ? *'
AS SELECT date AS date, sum(sales) AS sumOfSales
FROM table1
GROUP BY date;
-- Create a materialized view with a row filter and a column mask
> CREATE MATERIALIZED VIEW masked_view (
id,
name,
region,
ssn MASK catalog.schema.ssn_mask_fn
)
WITH ROW FILTER catalog.schema.us_filter_fn ON (region)
AS SELECT id, name, region, ssn
FROM employees;
Related articles
Σχόλια
https://aka.ms/ContentUserFeedback.
Σύντομα διαθέσιμα: Καθ' όλη τη διάρκεια του 2024 θα καταργήσουμε σταδιακά τα ζητήματα GitHub ως μηχανισμό ανάδρασης για το περιεχόμενο και θα το αντικαταστήσουμε με ένα νέο σύστημα ανάδρασης. Για περισσότερες πληροφορίες, ανατρέξτε στο θέμα:Υποβολή και προβολή σχολίων για