Vistas Volátiles y Materializadas en Bases de Datos

Oscar Eduardo Palomino Cárdenas

student•

Agarra una consulta que se realice muchas veces y colocarla bajo un solo nombre. Centraliza muchos esfuerzos en una sola función. Vista volátil: Siempre que se haga la consulta en la vista, la BD hace la ejecución de la consulta en la BD, por lo que siempre se va a tener información reciente. Vista materializada: Hace la consulta una sola vez, y la información queda almacenada en memoria, la siguiente vez que se consulte, trae el dato almacenado, eso es bueno y malo a la vez, bueno porque la velocidad con la que se entrega la información es rápida, malo porque la información no es actualizada. Es ideal utilizar este tipo de vista en procesos que utilice días anteriores, porque el día de ayer, ya pasó y no hay razón para actualizarlo. Para crear una vista volátil en postgres, damos click derecho a views, create, view, le damos un nombre, y en la pestaña code escribimos o pegamos el código de la consulta que queremos guardar, la guardamos y para usar la vista usamos:

    SELECT * FROM <nombre_vista>; y listo.

Para crear una vista materializada, primero creamos la consulta, y definimos si los datos nos interesan o no, luego, vamos a la opción materialized views, click derecho, create, materialized view. Se abre la ventana, le damos un nombre, termina con _mview, y en la pestaña Definition escribimos la consulta que necesitamos guardar. Guardamos. Al probarla en este momento nos lanza un error, ¿por qué? porque no tiene datos almacenados. Para almacenar datos usamos:

    REFRESH MATERIALIZED VIEW <nombre vista>;

Y ahora si podemos consultarla:

    SELECT * FROM <nombre_vista_mview>;

Omar Daniel Centeno

student•

Buen resumen, deberían de agregarlo al resumen oficial faltante.

Jorge Marquez Robinson

student•

Buen aporte 👍

Gabriel Calzada

student•

En el caso de las vistas materializadas, es conveniente agregar una columna para indicar la fecha del último "refresh" de la información.

José Rodrigo Arana Hi

student•

¿Cómo quedaría el query?

Agustin Caldart

student•

@ZeroRoy podes crear una nueva columna que sea

now() as updated

Con eso cada vez que se corra el refresh materialized view vas a tener el nuevo now

Israel Yance

student•

Tipos de vistas: - Vista Volátil: Consulta con data actualizada - Vista Materializada: Consulta con data persistente

Boris Vargas Paucara

student•

Vistas

Vista Volatil
Vista Materializada: persistente (Ayer)

-- Creamos la vista
CREATE OR REPLACE VIEW public.rango_view
AS
    SELECT *,
        CASE
        WHEN fecha_nacimiento &gt; '2015-01-01' THEN
            'Mayor'
        ELSE
            'Menor'
        END AS tipo
    FROM pasajero ORDER BY tipo;
ALTER TABLE public.rango_view OWNER TO postgres;

-- mostramos la vista
SELECT * FROM public.rango_view;

-- Vistas Materializada, no se cambia a menos que queramos que se cambie
SELECT * FROM viaje WHERE inicio &gt; '22:00:00';

CREATE MATERIALIZED VIEW public.despues_noche_mview
AS
    SELECT * FROM viaje WHERE inicio &gt; '22:00:00';
WITH NO DATA;
ALTER TABLE public.despues_noche_mview OWNER TO postgres;

-- observamos la vista
SELECT * FROM despues_noche_mview;

-- Damos refresh
REFRESH MATERIALIZED VIEW despues_noche_mview;

-- Borramos una tupla de viaje cuando el id = 2, para observar que no se borro
DELETE FROM viaje WHERE id = 2;

Gerardo Mayel Fernández Alamilla

student•

hay otro concepto que me parece importante y son las tablas temporales que se guardan en disco y se eliminan al cerrar la sesión,

create temporary table viajes_temp AS
select * from viajes;

Omar Daniel Centeno

student•

Vistas 👀

. Una vista es una alternativa para mostrar datos de varias tablas. Una vista es como una tabla virtual que almacena una consulta. Los datos accesibles a través de la vista no están almacenados en la base de datos como un objeto. . Entonces, una vista almacena una consulta como un objeto para utilizarse posteriormente. Las tablas consultadas en una vista se llaman tablas base. En general, se puede dar un nombre a cualquier consulta y almacenarla como una vista. .

Las vistas permiten:

Ocultar información: generando el acceso a algunos datos y manteniendo oculto el resto de la información que no se incluye en la vista. El usuario solo puede consultar la vista. .
Simplificar la administración de los permisos de usuario: se pueden dar al usuario permisos para que solamente pueda acceder a los datos a través de vistas, en lugar de concederle permisos para acceder a ciertos campos, así se protegen las tablas base de cambios en su estructura. .
Mejorar el rendimiento: se puede evitar tipear instrucciones repetidamente almacenando en una vista el resultado de una consulta compleja que incluya información de varias tablas. . Podemos crear vistas con: un subconjunto de registros y campos de una tabla; una unión de varias tablas; una combinación de varias tablas; un resumen estadístico de una tabla; un subconjunto de otra vista, combinación de vistas y tablas. .

La sintaxis básica parcial para crear una vista es la siguiente:

 create view NOMBREVISTA as
  SENTENCIAS SELECT
   from TABLA;
El contenido de una vista se muestra con un "select":

 select *from NOMBREVISTA;

Gabriela Obregón Ortiz

student•

¡Genial! Gracias.

Hugo Guillermo Pelinski

student•

bueno aporte

DOUGLAS OSMAR CALDERON MENDOZA

student•

Vista Volátil: Consulta información en tiempo real
Vista Materializada: Consulta información guardada al momento de generar la vista. Si se desea actualizar la vista Materializada se debe ejecutar lo siguiente: REFRESH MATERIALIZED VIEW <Nombre de la vista materializada>

Omar Daniel Centeno

student•

Ese Refresh Materialized es como un iniciador sin querer queriendo jajaja.

Angel de Jesus Quintero Pereira

student•

Las vistas son útiles porque nos ayudan a centralizar todos los esfuerzo en un solo lugar por otra parte las vistas volátil no guardan información de forma persistente, en cambio la vista materializada si, esto es útil cuando queremos consultar que datos ocurrieron el día ant.

Luis Miguel Rodriguez

student•

Vistas en PostgreSQL

Tipos de vistas

Vistas estándar: consultas SELECT almacenadas como objetos que pueden ser referenciados por otras consultas o programas de aplicación.
Vistas volátiles: vistas que se recalculan cada vez que se consultan. Se utilizan para consultas que requieren datos en tiempo real y que se ejecutan con poca frecuencia.
Vistas materializadas: vistas que almacenan los resultados de una consulta SELECT en una tabla temporal para una consulta más rápida. Se utilizan para consultas que se ejecutan con frecuencia y que no cambian con frecuencia.

Creación de vistas

Vistas estándar: se utiliza la sintaxis CREATE VIEW, seguida de la consulta SELECT que define la vista.

CREATE VIEW vista_ejemplo AS
SELECT columna1, columna2, columna3
FROM tabla_ejemplo
WHERE columna4 = 'valor';

Vistas volátiles: se utiliza la sintaxis CREATE OR REPLACE VIEW, seguida de la consulta SELECT que define la vista.

CREATE OR REPLACE VIEW vista_volatil AS
SELECT columna1, columna2
FROM tabla_ejemplo
WHERE columna3 = 'valor';

Vistas materializadas: se utiliza la sintaxis CREATE MATERIALIZED VIEW, seguida de la consulta SELECT que define la vista.

CREATE MATERIALIZED VIEW vista_materializada AS
SELECT columna1, columna2
FROM tabla_ejemplo
WHERE columna3 = 'valor';

Actualización de vistas

Vistas volátiles: se recalculan automáticamente cada vez que se consultan.
Vistas materializadas: deben ser actualizadas manualmente utilizando la sintaxis REFRESH MATERIALIZED VIEW.
```
REFRESH MATERIALIZED VIEW vista_materializada;
```

Beneficios de las vistas

Simplifican consultas complejas.
Reducen la cantidad de código necesario para realizar consultas comunes.
Proporcionan una vista personalizada de los datos de la base de datos para diferentes usuarios o aplicaciones.

Nicolás Méndez Ayala

student•

Gracias, excelentes apuntes.

Alejandro Merino Recio

student•

Muy buen aporte!!!

Brian Hawkins

student•

Supongamos que somos los encargados de proporcionar información de los nuevos casos de COVID-19 registrados por los hospitales de nuestro país. Si la información se registra de forma diaria, utilizaríamos las vistas de la siguiente forma: Vista materializada: Consultar datos del mes anterior. Vista Volátil: Consultar datos actualizados del mes actual.

Omar Daniel Centeno

student•

Buen ejemplo, así como va el covid en realidad puedes tener vistas por horas, día anterior, semana, mes. Mucha información se genera.

Nelson Eduardo Visbal Jensen

student•

Las VISTAS sirven también cuando se desea compartir cierta información de una tabla con un grupo de usuarios que no debe ver todas las columnas, es decir, sobre la tabla original podría haber información sensible que no debe compartirse con todos.

Angel de Jesus Quintero Pereira

student•

Una vista en esencia es tomar una consulta convertirla en un solo nombre. De modo que SELECT * FROM view_name

José Rodrigo Arana Hi

student•

asignamos un query a una variable.

Juan José Calderón

student•

Data Tree Gardener Aqui VOOOOOOOOY

Carlos Augusto Sánchez Seminario

student•

Hola, tengo un ligero problema. Después de crear la vista materializada, al ejecutar el comando REFRESH MATERIALIZED VIEW me arroga el siguiente error: ERROR: no existe la relación «despues_noche_view» SQL state: 42P01

¿Alguien saber porque pasa esto?

Diego Armando Daza Quiroga

student•

Pruebalo ejecutandolo por la pantalla shell.

José Rafael Castro Chacín

student•

Cuando se generan consultas se debe tener presente de no usar el comodín *, ya que esto le trae una carga adicional a la base de datos y más en tablas que son de muchas columnas. El deber ser es traer solo los datos a usar, el comodín es útil cuando se utilizan funciones de agregación. En conclusión, es muy mala practica hacerlo, en grandes sistemas esto pasa factura.

Catalina Murcia

student•

Es muy cierto lo que dices!! en la vida real hay que traer la información que da valor y no todo lo que pueda traer una consulta a una tabla

Cristian Camilo Hernández Ramirez

student•

Comandos útiles:

Para listar vistas materializadas usar: \dm
Para listar vistas volátiles: \dv
Para ver todo lo que hay tras una vista volátil en especifico \sv <nombre_vista>
pd: no encontré el equivalente de '\sv' para vistas materializadas, lo único que encontré fue una forma de preguntar usando SELECT, pero siendo así encuentro mucho más práctico usar PgAdmin.

Flavio César Sandoval Muñoz

student•

esto nos va a ayudar tanto a escribir menos y optimizar las consultas ahora voy a revisar si a esas vistas se le pueden hacer joins

DAVID EDUARDO BAEZ SANCHEZ

student•

Entendido

CREATE OR REPLACE VIEW public.rango_view
 AS
 SELECT COALESCE(pasajero.nombre, 'Nombre en Null'::character varying) AS nombrenull,
    pasajero.id,
    pasajero.nombre,
    pasajero.direccion_residencia,
    pasajero.fecha_nacimiento,
        CASE
            WHEN pasajero.fecha_nacimiento > '2015-01-01'::date THEN 'Niño'::text
            ELSE 'Mayor'::text
        END AS tipo,
        CASE
            WHEN pasajero.nombre::text ~~* 'D%'::text THEN 'Empieza con D'::text
            ELSE 'No empieza con D'::text
        END AS letra,
        CASE
            WHEN date_part('years'::text, age(CURRENT_TIMESTAMP, pasajero.fecha_nacimiento::timestamp without time zone::timestamp with time zone)) >= 18::double precision THEN 'Mayor de edad.'::text
            ELSE 'Menor de edad.'::text
        END AS edad
   FROM pasajero;

ALTER TABLE public.rango_view
    OWNER TO postgres;

CREATE MATERIALIZED VIEW public.despues2014
AS
SELECT * FROM pasajero WHERE fecha_nacimiento > '2015-01-01'
WITH NO DATA;

ALTER TABLE public.despues2014
    OWNER TO postgres;

SELECT * FROM rango_view;
SELECT * FROM  public.despues2014;
REFRESH MATERIALIZED VIEW despues2014;
SELECT * FROM  public.despues2014;
DELETE FROM pasajero WHERE id = 89;
SELECT * FROM  public.despues2014;
REFRESH MATERIALIZED VIEW despues2014;
SELECT * FROM  public.despues2014;

Angel de Jesus Quintero Pereira

student•

Hay dos tipos de vistas: 1. Vistas Volátil, 2.Vistas Materializada: Persistentes

Christian David Tovar Tirado

student•

Hola a todos, Una consulta, estas vistas yo las puedo definir y consumir a través de alguna aplicación bi como power bi o debo usar la consulta completa?. De antemano muchas gracias

-- Creamos la vista
CREATE OR REPLACE VIEW public.rango_view
AS
    SELECT *,
        CASE
        WHEN fecha_nacimiento &gt; '2015-01-01' THEN
            'Mayor'
        ELSE
            'Menor'
        END AS tipo
    FROM pasajero ORDER BY tipo;
ALTER TABLE public.rango_view OWNER TO postgres;

-- mostramos la vista
SELECT * FROM public.rango_view;

-- Vistas Materializada, no se cambia a menos que queramos que se cambie
SELECT * FROM viaje WHERE inicio &gt; '22:00:00';

CREATE MATERIALIZED VIEW public.despues_noche_mview
AS
    SELECT * FROM viaje WHERE inicio &gt; '22:00:00';
WITH NO DATA;
ALTER TABLE public.despues_noche_mview OWNER TO postgres;

-- observamos la vista
SELECT * FROM despues_noche_mview;

-- Damos refresh
REFRESH MATERIALIZED VIEW despues_noche_mview;

-- Borramos una tupla de viaje cuando el id = 2, para observar que no se borro
DELETE FROM viaje WHERE id = 2;

La sintaxis básica parcial para crear una vista es la siguiente:

 create view NOMBREVISTA as
  SENTENCIAS SELECT
   from TABLA;
El contenido de una vista se muestra con un "select":

 select *from NOMBREVISTA;

CREATE OR REPLACE VIEW public.rango_view
 AS
 SELECT COALESCE(pasajero.nombre, 'Nombre en Null'::character varying) AS nombrenull,
    pasajero.id,
    pasajero.nombre,
    pasajero.direccion_residencia,
    pasajero.fecha_nacimiento,
        CASE
            WHEN pasajero.fecha_nacimiento > '2015-01-01'::date THEN 'Niño'::text
            ELSE 'Mayor'::text
        END AS tipo,
        CASE
            WHEN pasajero.nombre::text ~~* 'D%'::text THEN 'Empieza con D'::text
            ELSE 'No empieza con D'::text
        END AS letra,
        CASE
            WHEN date_part('years'::text, age(CURRENT_TIMESTAMP, pasajero.fecha_nacimiento::timestamp without time zone::timestamp with time zone)) >= 18::double precision THEN 'Mayor de edad.'::text
            ELSE 'Menor de edad.'::text
        END AS edad
   FROM pasajero;

ALTER TABLE public.rango_view
    OWNER TO postgres;

CREATE MATERIALIZED VIEW public.despues2014
AS
SELECT * FROM pasajero WHERE fecha_nacimiento > '2015-01-01'
WITH NO DATA;

ALTER TABLE public.despues2014
    OWNER TO postgres;

SELECT * FROM rango_view;
SELECT * FROM  public.despues2014;
REFRESH MATERIALIZED VIEW despues2014;
SELECT * FROM  public.despues2014;
DELETE FROM pasajero WHERE id = 89;
SELECT * FROM  public.despues2014;
REFRESH MATERIALIZED VIEW despues2014;
SELECT * FROM  public.despues2014;

Vistas Volátiles y Materializadas en Bases de Datos

Configurar Postgres

Instalación y Configuración de Podres para Bases de Datos

Fundamentos de Postgres: Motor, Lenguaje y Servidor de Bases de Datos

Instalación y Configuración de PostgreSQL en Windows

Acceso y uso de la consola de PostgreSQL

Uso de PGAdmin para gestionar bases de datos PostgreSQL

Configuración de Archivos Críticos en PostgreSQL

Comandos esenciales de consola en PostgreSQL

Modelado de un Sistema de Transporte Masivo en Bases de Datos

Tipos de Datos Soportados por PostgreSQL

Diseño de Base de Datos para Sistema de Transporte

Estructura de Bases de Datos en PostgreSQL para Transporte

Gestión de la información en bases de datos

Creación y Gestión de Tablas en Bases de Datos con PGAdmin

Creación y gestión de tablas particionadas en bases de datos

Creación y Gestión de Roles en Bases de Datos PostgreSQL

Creación de Llaves Foráneas en Bases de Datos con PGAdmin

Gestión de Datos con PGAdmin: Inserción, Consulta y Borrado

Inserción Masiva de Datos en Bases de Datos con Mockaroo

Generar consultas avanzadas

Cruce de Datos en Bases de Datos Usando Teoría de Conjuntos

Funciones Avanzadas de PostgreSQL para Optimización de Aplicaciones

Funciones Avanzadas en Consultas SQL para Bases de Datos

Vistas Volátiles y Materializadas en Bases de Datos

Creación de Procedimientos Almacenados en PostgreSQL

Creación y Uso de Disparadores en PostgreSQL

Integrar bases de datos con servicios externos

Conexión y Consulta de Datos en Bases de Datos Remotas con Postgres

Transacciones ACID en Bases de Datos: Teoría y Práctica

Activación de Extensiones para Comparar Palabras en PostgreSQL

Implementar mejores prácticas

Copia de Seguridad y Restauración con PGAdmin y PGDump

Mantenimiento y Optimización de Bases de Datos: Conceptos Clave

Implementación de Réplicas en Bases de Datos para Optimizar Rendimiento

Creación y Configuración de Servidores PostgreSQL Maestro-Esclavo

Optimización de Tablas en Postgres para Evitar Bloqueos

Examen Final: Aplicación de Estrategias de Innovación Empresarial