Часто бывает удобно создать представление для вашей нормализованной схемы для объединения и агрегирования данных, особенно когда это требует сложного запроса.

В PostgreSQL вы можете создать представление на основе запроса.

CREATE VIEW my_view AS SELECT
                         list_id,
                         event_id,
                         sum(price_paid) AS revenue,
                         count(qty_sold) AS qty
                       FROM sales
                         WHERE local_date >= '2018-01-01'
                       GROUP BY list_id, event_id;

Каждый раз, когда вы выбираете данные из такого представления, будет выполняться запрос ниже. Если выполнение запроса занимает много времени, можно использовать материализованное представление.

CREATE MATERIALIZED VIEW my_view AS SELECT (...) ;

Это представление заполняется данными во время создания, поэтому нет необходимости запускать трудоемкий запрос каждый раз, когда вы обращаетесь к данным. Однако каждый раз при изменении данных представление необходимо обновлять вручную с помощью REFRESH MATERIALIZED VIEW my_view запроса. Материализованное представление особенно полезно, когда ваши данные меняются нечасто и предсказуемо. Идеальный вариант использования - это процесс ETL - запрос на обновление может выполняться как его часть.

Поскольку Redshift основан на PostgreSQL, можно ожидать, что Redshift будет иметь материализованные представления. К сожалению, Redshift не поддерживает эту функцию. Обычные просмотры в Redshift имеют два основных недостатка:

  • планировщик запросов Redshift не оптимизируется с помощью представлений; поэтому выборка данных из представления вместо прямого выполнения запроса может быть медленнее,
  • представления в Redshift связаны с таблицей (а не только с ее именем), поэтому вы столкнетесь с ошибками при изменении таблицы; использование предложения WITH NO SCHEMA BINDING указывает Redshift не связываться с базовыми объектами базы данных.

Вместо использования представления мы можем создать таблицу на основе запроса (и каждый раз отбрасывать и воссоздавать ее). Поскольку это обычная таблица, можно определить ключи сортировки для дальнейшего повышения производительности.

Простота обновления данных может считаться преимуществом материализованного представления. Чтобы добиться аналогичного поведения с таблицей, мы можем использовать обычное представление для фактического хранения запроса. Обратной стороной такого решения является то, что вставка данных в таблицу через представление займет больше времени, чем при использовании запроса. Воссоздание таблицы с данными через представление может быть таким же простым, как два следующих оператора, заключенных в блок транзакции:

BEGIN TRANSACTION;
DROP TABLE my_view_table;
CREATE TABLE my_view_table AS SELECT * FROM my_view;
COMMIT TRANSACTION;

У этого фрагмента кода есть две основные проблемы:

  • Команда DROP в транзакции помещает LOCK в таблицу, поэтому другим процессам придется ждать при попытке доступа к данным; более того, если вы зафиксируете транзакцию, ожидающий процесс получит ошибку типа table 1234556 dropped by concurrent transaction,
  • любой ключ сортировки, добавленный в таблицу, будет утерян.

Удаление всех данных из таблицы, хотя и кажется простым в реализации, требует VACUUM и ANALYZE, которые могут быть довольно длинными. Более быстрая альтернатива неквалифицированному DELETE - TRUNCATE. Однако он фиксирует транзакцию, в которой он запущен, и не может быть отменен. Другой способ - использовать оператор CREATE TABLE ... LIKE для создания промежуточной таблицы. Этот оператор копирует имена столбцов, типы данных и NOT NULL ограничения. Таблицы, созданные с помощью параметра LIKE, также наследуют стиль распределения и ключи сортировки (но не наследуют ограничения первичного и внешнего ключей).

BEGIN TRANSACTION;
CREATE TABLE my_view_table_new LIKE my_view_table;
INSERT INTO my_view_table_new SELECT * FROM my_view;
ALTER TABLE RENAME my_view_table TO my_view_table_old;
ALTER TABLE RENAME my_view_table_new TO my_view_table;
DROP TABLE my_view_table_old;
COMMIT;
END TRANSACTION;

Чтобы увидеть код запроса, использованного для создания представления, вы можете войти в базу данных с помощью psql и запустить \d+ my_view.

Redshift не реализует материализованные представления, но имитировать подобное поведение довольно просто. Единственный вопрос, который следует задать, - нужно ли нам обновлять данные проще или быстрее.

Первоначально опубликовано на brightinventions.pl

Агнешка Ольшевска, инженер-программист @ Bright Inventions

"Электронное письмо"