CREATE MATERIALIZED VIEW

Crea una vista materializada basada en una o más tablas de Amazon Redshift. También puede basar las vistas materializadas en tablas externas creadas mediante Spectrum o una consulta federada. Para obtener información acerca de Spectrum, consulte Consulta de datos externos mediante Amazon Redshift Spectrum. Para obtener información acerca de las consultas federadas, consulte Consulta de datos con consultas federadas en Amazon Redshift.

Sintaxis

CREATE MATERIALIZED VIEW mv_name
[ BACKUP { YES | NO } ]
[ table_attributes ]
[ AUTO REFRESH { YES | NO } ]
AS query 

Parámetros

BACKUP

Se trata de una cláusula que especifica si la vista materializada se incluye en instantáneas de clúster automatizadas y manuales, las cuales se almacenan en Amazon S3.

El valor predeterminado de BACKUP es YES.

Puede especificar BACKUP NO para ahorrar tiempo de procesamiento cuando crea instantáneas y restaura el contenido a partir de ellas, y para reducir el espacio de almacenamiento necesario en Amazon S3.

nota

El ajuste BACKUP NO no tiene efecto en la replicación automática de datos a otros nodos dentro del clúster, por lo que las tablas con BACKUP NO especificado se restauran en caso de error del nodo.

table_attributes

Cláusula que especifica cómo se distribuyen los datos de la vista materializada, incluida la siguiente:

• El estilo de distribución para la vista materializada en formato DISTSTYLE { EVEN | ALL | KEY }. Si omite esta cláusula, el estilo de distribución es EVEN. Para obtener más información, consulte Estilos de distribución.

• La clave de distribución de la vista materializada en formato DISTKEY ( distkey_identifier ). Para obtener más información, consulte Designación de estilos de distribución.

• Clave de ordenación de la vista materializada en formato SORTKEY ( column_name [, ...] ). Para obtener más información, consulte Uso de claves de ordenación.

AS query

Se trata de una instrucción SELECT válida que define la vista materializada y su contenido. El conjunto de resultados de la consulta define las columnas y filas de la vista materializada. Para obtener información sobre las limitaciones al crear vistas materializadas, consulte Limitaciones.

Además, las construcciones específicas del lenguaje SQL utilizadas en la consulta determinan si la vista materializada se puede actualizar de forma incremental o completa. Para obtener información sobre el método de actualización, consulte REFRESH MATERIALIZED VIEW. Para obtener información acerca de las limitaciones para la actualización incremental, consulte Limitaciones para la actualización incremental.

Si la consulta contiene un comando SQL que no es compatible con la actualización progresiva, Amazon Redshift muestra un mensaje que indica que la vista materializada utilizará una actualización completa. El mensaje se puede mostrar o no, en función de la aplicación cliente de SQL. Active la columna state de STV_MV_INFO para ver el tipo de actualización utilizado por una vista materializada.

AUTO REFRESH

Se trata de una cláusula que define si la vista materializada debe actualizarse de forma automática con los últimos cambios de las tablas base. El valor predeterminado es NO. Para obtener más información, consulte Actualización de una vista materializada.

Notas de uso

Para crear una vista materializada, debe tener los siguientes privilegios:

• Privilegios CREATE para un esquema.

• Privilegio SELECT de nivel de tabla o de columna en las tablas base para crear una vista materializada. Si tiene privilegios de nivel de columna en columnas específicas, no puede crear una vista materializada solo en esas columnas.

Actualización incremental para obtener vistas materializadas en un recurso compartido de datos

Amazon Redshift admite la actualización automática e incremental de las vistas materializadas en un recurso compartido de datos de consumidores cuando se comparten las tablas base. La actualización incremental es una operación en la que Amazon Redshift identifica los cambios en la tabla o tablas base que se produjeron después de la actualización anterior y actualiza solo los registros correspondientes de la vista materializada. Esto se ejecuta más rápido que una actualización completa y mejora el rendimiento de la carga de trabajo. No es necesario cambiar la definición de la vista materializada para aprovechar la actualización incremental.

Hay un par de limitaciones que hay que tener en cuenta a la hora de aprovechar la actualización incremental con una vista materializada:

• La vista materializada debe hacer referencia solo a una base de datos, ya sea local o remota.

• La actualización incremental solo está disponible en las nuevas vistas materializadas. Por lo tanto, debe eliminar las vistas materializadas existentes y volver a crearlas para que se produzca una actualización incremental.

Para obtener más información sobre la creación de vistas materializadas en un recurso compartido de datos, consulte Trabajo con vistas en el uso compartido de datos de Amazon Redshift, que contiene varios ejemplos de consultas.

Actualizaciones DDL en vistas materializadas o tablas base

Cuando utilice vistas materializadas en Amazon Redshift, siga las siguientes notas de uso para las actualizaciones de lenguaje de definición de datos (DDL) en vistas materializadas o tablas base.

• Puede agregar columnas a una tabla base sin que esto afecte a las vistas materializadas que hacen referencia a la tabla base.

• Algunas operaciones pueden dejar la vista materializada en un estado en el que no se puede actualizar. Por ejemplo, operaciones como renombrar o eliminar una columna, cambiar el tipo de columna y cambiar el nombre de un esquema. Dichas vistas materializadas se pueden consultar pero no se pueden actualizar. En este caso, debe eliminar y volver a crear la vista materializada.

• En general, no se puede modificar la definición de una vista materializada (su instrucción SQL).

• No se puede cambiar el nombre de una vista materializada.

Limitaciones

No puede definir una vista materializada que haga referencia a o incluya cualquiera de los siguientes:

• Vistas estándar o vistas y tablas de sistema.

• Tablas temporales.

• Funciones definidas por el usuario.

• La cláusula ORDER BY, LIMIT u OFFSET.

• Referencias de enlace tardío a tablas básicas. En otras palabras, cualquier tabla de base o columnas relacionadas a las que se hace referencia en la definición de la consulta SQL de la vista materializada debe existir y ser válida.

• Funciones específicas del nodo principal: CURRENT_SCHEMA, CURRENT_SCHEMAS, HAS_DATABASE_PRIVILEGE, HAS_SCHEMA_PRIVILEGE, HAS_TABLE_PRIVILEGE.

• No se puede utilizar la opción AUTO REFRESH YES cuando la definición de la vista materializada incluye funciones mutables o esquemas externos. Tampoco puede usarla cuando define una vista materializada en otra vista materializada.

• No hace falta que ejecute manualmente ANALYZE en las vistas materializadas. Actualmente, esto solo ocurre mediante AUTO ANALYZE. Para obtener más información, consulte Análisis de tablas.

Ejemplos

En el siguiente ejemplo, se crea una vista materializada a partir de tres tablas base que se combinan y se agrupan. Cada fila representa una categoría con el número de entradas vendidas. Cuando consulta la vista materializada tickets_mv, accede directamente a los datos precalculados en la vista materializada tickets_mv.

CREATE MATERIALIZED VIEW tickets_mv AS
    select   catgroup,
    sum(qtysold) as sold
    from     category c, event e, sales s
    where    c.catid = e.catid
    and      e.eventid = s.eventid
    group by catgroup;

En el siguiente ejemplo, se crea una vista materializada similar al ejemplo anterior y se utiliza la función agregada MAX().

CREATE MATERIALIZED VIEW tickets_mv_max AS
    select   catgroup,
    max(qtysold) as sold
    from     category c, event e, sales s
    where    c.catid = e.catid
    and      e.eventid = s.eventid
    group by catgroup;

SELECT name, state FROM STV_MV_INFO;

En el siguiente ejemplo, se utiliza una cláusula UNION ALL para combinar la tabla public_sales de Amazon Redshift y la tabla spectrum.sales de Redshift Spectrum para crear una vista material mv_sales_vw. Para obtener información acerca del comando CREATE EXTERNAL TABLE para Amazon Redshift Spectrum, consulte CREATE EXTERNAL TABLE. La tabla externa de Redshift Spectrum referencia los datos de Amazon S3.

CREATE MATERIALIZED VIEW mv_sales_vw as
select salesid, qtysold, pricepaid, commission, saletime from public.sales
union all
select salesid, qtysold, pricepaid, commission, saletime from spectrum.sales

En el siguiente ejemplo, se crea una vista materializada mv_fq basada en una tabla externa de consulta federada. Para obtener información acerca de las consultas federadas, consulte CREATE EXTERNAL SCHEMA.

CREATE MATERIALIZED VIEW mv_fq as select firstname, lastname from apg.mv_fq_example;

select firstname, lastname from mv_fq;
 firstname | lastname
-----------+----------
 John      | Day
 Jane      | Doe
(2 rows)

En el siguiente ejemplo, se muestra la definición de una vista materializada.

SELECT pg_catalog.pg_get_viewdef('mv_sales_vw'::regclass::oid, true);

pg_get_viewdef
---------------------------------------------------
create materialized view mv_sales_vw as select a from t;

El siguiente ejemplo muestra cómo configurar AUTO REFRESH en la definición de vista materializada y también especifica un DISTSTYLE. Primero, crea una tabla base simple.

CREATE TABLE baseball_table (ball int, bat int);

A continuación, cree una vista materializada.

CREATE MATERIALIZED VIEW mv_baseball DISTSTYLE ALL AUTO REFRESH YES AS SELECT ball AS baseball FROM baseball_table;

Ahora puede consultar la vista materializada de mv_baseball. Para comprobar si la actualización automática está activada en una vista materializada, consulte STV_MV_INFO.

En el siguiente ejemplo se crea una vista materializada que hace referencia a una tabla de origen de otra base de datos. Se supone que la base de datos que contiene la tabla de origen, database_A, se encuentra en el mismo clúster o grupo de trabajo que la vista materializada, que se crea en database_B. (Puede sustituir el ejemplo por sus propias bases de datos). Primero, cree una tabla en database_A denominada cities, con una columna de cityname. Haga que el tipo de datos de la columna sea VARCHAR. Después de crear la tabla de origen, ejecute el siguiente comando en database_B para crear una vista materializada cuyo origen sea la tabla cities. Asegúrese de especificar la base de datos y el esquema de la tabla de origen en la cláusula FROM:

CREATE MATERIALIZED VIEW cities_mv AS
SELECT  cityname
FROM    database_A.public.cities;

Consulte la vista materializada que ha creado. La consulta recupera los registros cuyo origen original es la tabla cities de database_A:

select * from cities_mv;

Al ejecutar la instrucción SELECT, cities_mv devuelve los registros. Los registros se actualizan desde la tabla de origen solo cuando se ejecuta una instrucción REFRESH. Además, tenga en cuenta que no puede actualizar los registros directamente en la vista materializada. Para obtener información sobre cómo actualizar los datos en una vista materializada, consulte REFRESH MATERIALIZED VIEW.

Para obtener información detallada sobre la información general de la vista materializada y los comandos SQL utilizados para actualizar y eliminar vistas materializadas, consulte los siguientes temas:

• Creación de vistas materializadas en Amazon Redshift

• REFRESH MATERIALIZED VIEW

• DROP MATERIALIZED VIEW