Часто бывает удобно создать представление для вашей нормализованной схемы для объединения и агрегирования данных, особенно когда это требует сложного запроса.
В 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
"Электронное письмо"