CREATE MATERIALIZED VIEW

  • Artículo

Se aplica a:casilla marcada como sí Databricks SQL

Importante

Esta característica está en versión preliminar pública. Para registrarse para obtener acceso, rellene este formulario.

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, según una programación o mediante la programación de la canalización DLT en la que estén contenidas.

Sintaxis

CREATE MATERIALIZED VIEW [IF NOT EXISTS]
  view_name
  [ column_list ]
  [ table_constraints ]
  [ view_clauses ]
  AS query

column_list
   ( { column_alias [ COMMENT column_comment | column_constraint ] [...] } [, ...] [ , table_constraint ] [...])

table_constraints
   ( table_constraint [, ...] )

view_clauses
  { PARTITIONED BY (col [, ...]) |
    COMMENT view_comment |
    TBLPROPERTIES clause |
    SCHEDULE [ REFRESH ] CRON cron_string [ AT TIME ZONE timezone_id ] } [...]

Parámetros

  • IF NOT EXISTS

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

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

  • 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_alias

      Los alias de columna deben ser únicos.

    • column_comment

      Literal STRING opcional que describe el alias de columna.

    • column_constraint

      Importante

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

      Agrega una restricción de clave principal informativa o de clave externa informativa a la columna de una vista materializada. No se admiten restricciones de vista materializada en el catálogo hive_metastore.

  • table_constraint

    Importante

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

    Agrega restricciones de clave principal informativa o de clave externa informativas a una vista materializada. No se admiten restricciones para las vistas materializadas en el catálogo hive-metastore.

  • 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.

    • SCHEDULE [ REFRESH ] CRON cron_string [ AT TIME ZONE timezone_id ]

      Si se proporciona, programa la tabla de streaming para actualizar sus datos con la programación quartz cron especificada. Solo se aceptan valores time_zone_values. 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.

      No se puede usar la sintaxis SCHEDULE en una definición de canalización de Delta Live Tables.

  • 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 en el esquema de la MV.

Solo el propietario puede REFRESH 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.

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
  • Las vistas materializadas no admiten columnas de identidad ni claves suplentes.
  • Las vistas materializadas no admiten comandos OPTIMIZE y VACUUM ad hoc. El mantenimiento se produce automáticamente.

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;

-- Create a materialized view with a column constraint
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies(
    member_id NOT NULL PRIMARY KEY,
    full_name,
    movie_title
  )
  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 table constraint
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies(
    member_id NOT NULL,
    full_name,
    movie_title,
    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 a materialized view without column list
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies(
    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;