Compartir a través de

CREATE MATERIALIZED VIEW

  • Artículo

Se aplica a: casilla marcada como Sí Databricks SQL

Una vista materializada es una vista en la que los resultados precalculados están disponibles para su consulta y se pueden actualizar para reflejar cambios en la entrada. Cada vez que se actualice una vista materializada, los resultados de la consulta se volverán a calcular para reflejar los cambios en los conjuntos de datos ascendentes. Todas las vistas materializadas están respaldadas por una canalización DLT. Puede actualizar las vistas materializadas manualmente o según una programación.

Para obtener más información sobre cómo realizar una actualización manual, consulte REFRESH (MATERIALIZED VIEW o STREAMING TABLE).

Para obtener más información sobre cómo programar una actualización, consulte Ejemplos o ALTER MATERIALIZED VIEW.

Nota:

Las operaciones de creación y actualización en vistas materializadas y tablas de streaming están realizadas con tecnología de una canalización de Delta Live Tables sin servidor. Puede usar el Explorador de catálogos para ver detalles sobre las canalizaciones de respaldo en la interfaz de usuario. Consulte ¿Qué es el Catalog Explorer?.

Sintaxis

{ CREATE OR REPLACE MATERIALIZED VIEW | CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] }
  view_name
  [ column_list ]
  [ view_clauses ]
  [schedule_clause]
  AS query

column_list
   ( { column_name column_type column_properties } [, ...]
      [ , table_constraint ] [...])

   column_properties
      { NOT NULL | COMMENT column_comment | column_constraint | MASK clause } [ ... ]

view_clauses
  { PARTITIONED BY (col [, ...]) |
    COMMENT view_comment |
    TBLPROPERTIES clause |
    SCHEDULE [ REFRESH ] schedule_clause |
    WITH { ROW FILTER clause } } [...]

schedule_clause
  { EVERY number { HOUR | HOURS | DAY | DAYS | WEEK | WEEKS } |
    CRON cron_string [ AT TIME ZONE timezone_id ] }

Parámetros

  • REPLACE

    Si se especifica, reemplaza la vista y su contenido si ya existe.

  • IF NOT EXISTS

    Crea la vista si no existe. Si ya existiera una vista con este nombre, se omitirá la instrucción CREATE MATERIALIZED VIEW.

    Puede especificar como máximo uno de IF NOT EXISTS o OR REPLACE.

  • view_name

    Nombre de la vista recién creada. El nombre completo de vista debe ser único.

  • lista_de_columnas

    También puede etiquetar las columnas del resultado de la consulta de la vista. Si proporciona una lista de columnas, el número de alias de columna debe coincidir con el de expresiones de la consulta. Si no se especificase ninguna lista de columnas, los alias se derivarán del cuerpo de la vista.

    • column_name

      Los nombres de columna deben ser únicos y asignarse a las columnas de salida de la consulta.

    • column_type

      Especifica el tipo de datos de la columna. No todos los tipos de datos que admite Azure Databricks son compatibles con todas las vistas materializadas.

    • column_comment

      Literal STRING opcional que describe el nombre de la columna. Esta opción debe especificarse junto con column_type. Si no se especifica el tipo de columna, se omite el comentario de la columna.

    • column_constraint

      Agrega una restricción de clave principal informativa o de clave externa informativa a la columna de una vista materializada. Si no se especifica el tipo de columna, se omite la restricción de la columna.

    • Cláusula MASK

      Importante

      Esta característica está en versión preliminar pública.

      Agrega una función de máscara de columna para anonimizar datos confidenciales. Todas las consultas siguientes desde esa columna recibirán el resultado de evaluar esa función sobre la columna en lugar del valor original de la columna. Esto puede ser útil para fines de control de acceso específicos en los que la función puede inspeccionar la identidad o las pertenencias a grupos del usuario que realiza la invocación para decidir si expurga el valor. Si no se especifica el tipo de columna, se omite la máscara de la columna.

  • table_constraint

    Agrega una restricción de clave principal informativa o de clave externa informativa a la tabla de una vista materializada. Si no se especifica el tipo de columna, se omite la restricción de tabla.

  • view_clauses

    Opcionalmente, especifique la creación de particiones, los comentarios, las propiedades definidas por el usuario y una programación de actualización para la nueva vista materializada. Cada subcláusula solo se puede especificar una vez.

    • PARTITIONED BY

      Lista opcional de columnas de la tabla por la que se va a particionar la tabla.

    • COMMENT view_comment

      Una STRING literal para describir la tabla.

    • TBLPROPERTIES

      Este parámetro opcional le permite establecer una o más propiedades que defina el usuario.

      Use esta configuración para especificar el canal en tiempo de ejecución de Delta Live Tables que se usa para ejecutar esta instrucción. Establezca el valor de la pipelines.channel propiedad en "PREVIEW" o "CURRENT". El valor predeterminado es "CURRENT". Para obtener más información sobre los canales de Delta Live Tables, consulte Canales en tiempo de ejecución de Delta Live Tables.

    • SCHEDULE [ REFRESH ] schedule_clause

      • EVERY number { HOUR | HOURS | DAY | DAYS | WEEK | WEEKS }

        Importante

        Esta característica está en versión preliminar pública.

        Para programar una actualización que se produce periódicamente, use EVERY la sintaxis . Si EVERY se especifica la sintaxis, la tabla de streaming o la vista materializada se actualiza periódicamente en el intervalo especificado según el valor proporcionado, como HOUR, HOURS, DAY, DAYS, WEEKo WEEKS. En la tabla siguiente se enumeran los valores enteros aceptados para number.

        Time unit Valor entero
        HOUR or HOURS 1 <= H <= 72
        DAY or DAYS 1 <= D <= 31
        WEEK or WEEKS 1 <= W <= 8

        Nota:

        Las formas singulares y plurales de la unidad de tiempo incluida son semánticamente equivalentes.

      • CRON cron_string [ AT TIME ZONE timezone_id ]

        Para programar una actualización utilizando un valor cron de cuarzo. Se aceptan time_zone_values válidos. No se admite AT TIME ZONE LOCAL.

        Si AT TIME ZONE no está presente, se usa la zona horaria de la sesión. Si AT TIME ZONE no está presente y no se establece la zona horaria de la sesión, se produce un error. SCHEDULE es equivalente semánticamente a SCHEDULE REFRESH.

    • WITH Cláusula ROW FILTER

      Importante

      Esta característica está en versión preliminar pública.

      Agrega una función de filtro de fila a la tabla. Todas las consultas siguientes desde esa tabla recibirán un subconjunto de las filas para las que la función se evalúa como un valor TRUE booleano. Esto puede ser útil para fines de control de acceso específicos en los que la función puede inspeccionar la identidad o las pertenencias a grupos del usuario que realiza la invocación para decidir si se filtran determinadas filas.

  • AS query

    Consulta que construye la vista a partir de tablas base u otras vistas.

Permisos necesarios

El usuario que crea una vista materializada (MV) es el propietario de la máquina virtual y deberá tener los siguientes permisos:

  • Privilegios de SELECT sobre las tablas base a las que hace referencia la MV.
  • Tener el privilegio USE CATALOG en el catálogo primario y el privilegio USE SCHEMA en el esquema primario.
  • Privilegios de CREATE MATERIALIZED VIEW en el esquema de la MV.

Para que un usuario pueda actualizar la MV, necesita lo siguiente:

  • Tener el privilegio USE CATALOG en el catálogo primario y el privilegio USE SCHEMA en el esquema primario.
  • Propiedad de la MV o REFRESH privilegios en la MV.
  • El propietario de la MV debe tener el privilegio SELECT sobre las tablas base a las que hace referencia la MV.

Para que un usuario pueda consultar la MV, necesitará:

  • Tener el privilegio USE CATALOG en el catálogo primario y el privilegio USE SCHEMA en el esquema primario.
  • Privilegios de SELECT sobre la vista materializada.

Filtros de fila y máscaras de columna

Importante

Esta característica está en versión preliminar pública.

Filtros de fila permiten especificar una función que se aplica como filtro cada vez que un recorrido de tabla captura filas. Estos filtros garantizan que las consultas posteriores solo devuelven filas para las que el predicado de filtro se evalúa como true.

Las máscaras de columna permiten enmascarar los valores de una columna cada vez que un examen de tabla captura filas. Todas las consultas futuras que implican esa columna recibirán el resultado de evaluar la función sobre la columna, reemplazando el valor original de la columna’.

Para obtener más información sobre cómo usar filtros de fila y máscaras de columna, vea Filtrar datos confidenciales de la tabla mediante filtros de fila y máscaras de columna.

Administración de filtros de fila y máscaras de columna

Los filtros de fila y las máscaras de columna en las vistas materializadas deben agregarse a través de la instrucción CREATE.

Comportamiento

  • Actualizar como definidor: Cuando la instrucción REFRESH MATERIALIZED VIEW actualiza una vista materializada, las funciones de filtro de fila se ejecutan con los derechos del definidor’(como propietario de la tabla). Esto significa que la actualización de la tabla usa el contexto de seguridad del usuario que creó la vista materializada.
  • Consulta: Aunque la mayoría de los filtros se ejecutan con los derechos del definidor, las funciones que comprueban el contexto del usuario (como CURRENT_USER y IS_MEMBER) son excepciones. Estas funciones se ejecutan como invocador. Este enfoque aplica controles de acceso y seguridad de datos específicos del usuario en función del contexto del usuario actual.
  • Al crear vistas materializadas sobre tablas de origen que contienen filtros de fila y máscaras de columna, la actualización de la vista materializada siempre es una actualización completa. Una actualización completa vuelve a procesar todos los datos disponibles en el origen con las definiciones más recientes. Esto garantiza que las directivas de seguridad de las tablas de origen se evalúen y apliquen con los datos y definiciones más actualizados.

Observabilidad

Use DESCRIBE EXTENDED, INFORMATION_SCHEMAo el Explorador de catálogos para examinar los filtros de fila y las máscaras de columna existentes que se aplican a una vista materializada determinada. Esta funcionalidad permite a los usuarios auditar y revisar las medidas de acceso y protección de datos en vistas materializadas.

Limitaciones

  • Cuando una vista materializada con un sum agregado a través de una columna que admita valores NULL tenga el último valor distinto de NULL quitado de esa columna y, por tanto, solo NULL valores permanezcan en esa columna: el valor agregado resultante de la vista materializada devolverá cero en lugar de NULL.
  • La referencia de columna no requiere un alias. Las expresiones de referencia que no sean de columna requieren un alias, como en el ejemplo siguiente:
    • Permitido: SELECT col1, SUM(col2) AS sum_col2 FROM t GROUP BY col1
    • No permitido: SELECT col1, SUM(col2) FROM t GROUP BY col1
  • NOT NULL debe especificarse manualmente junto con PRIMARY KEY para que sea una instrucción válida.
  • Las vistas materializadas no admiten columnas de identidad ni claves suplentes.
  • Las vistas materializadas no admiten comandos OPTIMIZE y VACUUM. El mantenimiento se produce automáticamente.
  • Las vistas materializadas no admiten expectativas para definir restricciones de calidad de datos.

Ejemplos

-- 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;

-- Sets the runtime channel to "PREVIEW"
> CREATE MATERIALIZED VIEW mv_preview
  TBLPROPERTIES(pipelines.channel = "PREVIEW")
  AS SELECT * FROM RANGE(10)

-- Create a materialized view with a table constraint
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies(
    member_id int NOT NULL,
    full_name string,
    movie_title string,
    CONSTRAINT movie_pk PRIMARY KEY(member_id)
  )
  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 or replace the materialized view to remove the table constraint and add a partition
> CREATE OR REPLACE MATERIALIZED VIEW subscribed_movies
  PARTITIONED BY (member_id)
  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 a materialized view with a row filter and a column mask
> CREATE MATERIALIZED VIEW masked_view (
    id int,
    name string,
    region string,
    ssn string MASK catalog.schema.ssn_mask_fn
  )
  WITH ROW FILTER catalog.schema.us_filter_fn ON (region)
  AS SELECT id, name, region, ssn
       FROM employees;