Продолжая разбираться с новинками Greenplum версии 7, выпущенной в середине декабря 2022 года, сегодня рассмотрим, как теперь работает SQL-команда с DML-запросов изменения таблиц ALTER TABLE. Как динамически менять структуру и характеристики таблицы даже тех, что предназначены только для добавления с новыми методами доступа.
Модели таблиц в Greenplum: Append Only и Column Oriented
Напомним, Greenplum предусматривает несколько моделей хранения таблиц, оптимизированных для разных сценариев использования. В частности, таблицы модели Append Only (AO) оптимизированы для добавления данных и не предназначены для частого обновления после начальной загрузки. Вставки данных в них выполняются только в пакетных операциях. Обычно AO-таблицы очень большие, загружаются один раз, редко обновляются и часто запрашиваются для чтения данных, т.е. аналитических SQL-запросов. Модель AO лучше всего работает с денормализованными таблицами фактов в DWH, которые обычно являются самыми большими таблицами в базе, загружаются пакетами и запрашиваются только для чтения. Подробнее про таблицы фактов и измерений в DWH мы писали здесь и здесь. AO-модель устраняет накладные расходы на хранение информации о видимости обновления для каждой строки, экономя около 20 байтов на строку. Это позволяет сделать структуру табличной страницы более компактной и легкой для оптимизации. Также AO-модель оптимизирована для массовой загрузки данных, а однострочные операторы INSERT здесь не рекомендуются. Подробнее про эту и другие модели таблиц в Greenplum мы рассказывали в этой статье.
Изначально все таблицы Greenplum создаются как таблицы кучи с ориентацией на строки. Поэтому, чтобы создать AO-таблицу, следует указать эти параметры в условии WITH команды CREATE TABLE. Например, следующий код позволит создать AO-таблицу без сжатия с именем tbl:
CREATE TABLE tbl (a int, b text) WITH (appendoptimized=true) DISTRIBUTED BY (a);
Операции обновления (UPDATE) и удаления (DELETE) данных не разрешены для AO-таблиц в повторяющихся транзакциях чтения или сериализуемых транзакциях, т.к. приводят к их преждевременному завершению. Также не поддерживаются конструкции DECLARE…FOR UPDATE и триггеры, а CLUSTER поддерживается только для индексов B-tree.
Помимо модели таблицы, оптимизированной для чтения или записи, Greenplum также позволяет задать ориентацию на строки или столбцы. Например, ориентированные на строки таблицы хорошо подходят для рабочих нагрузок OLTP с большим количеством итерационных транзакций и множеством столбцов одной строки, необходимых одновременно. А таблицы с ориентацией на столбцы (Column Oriented, CO) подходят для рабочих нагрузок DWH с агрегированием данных, вычисляемых по небольшому количеству столбцов, или для отдельных столбцов, требующих регулярных обновлений без изменения данных других столбцов. Для большинства общих или смешанных рабочих нагрузок ориентация таблицы на строки является наилучшим сочетанием гибкости и производительности. Но для некоторых сценариев CO-модель обеспечивает более эффективный ввод-вывод и хранение. В частности, ориентация на столбцы доступна только для AO-таблиц. Также таблицы, ориентированные на столбцы, лучше всего подходят для запросов, объединяющих множество значений одного столбца, где предикат WHERE или HAVING также находится в совокупном столбце. CO-модель может обеспечить более высокую производительность запросов к таблицам с большим количеством столбцов, когда запрашивается только их небольшая часть.
Кроме того, благодаря одному типу данных в столбцах, таблицы такого вида оптимизируют размер DWH, поддерживая различные схемы сжатия данных. Впрочем, стоит помнить, что чем больше данные сжаты, тем сложнее становится произвольный доступ к ним.
Чтобы создать CO-таблицу, следует указать это в параметрах условия WITH команды CREATE TABLE:
CREATE TABLE tbl (a int, b text) WITH (appendoptimized=true, orientation=column) DISTRIBUTED BY (a);
Напомним, SQL-команда ALTER TABLE изменяет определение таблицы, включая ее атрибуты (столбцы), политику распределения данных, модель хранения и структуру разделов. Например, добавить ненулевое ограничение в столбец таблицы, поможет следующая команда:
ALTER TABLE address ALTER COLUMN street SET NOT NULL;
Команда ALTER TABLE также предоставляет опции для изменения политики распределения таблицы. Причем для партиционированных таблиц с несколькими разделами изменения политики распределения рекурсивно применяются к дочерним разделам, сохраняя право собственности и все другие родительские атрибуты. Например, следующая команда перераспределяет таблицу продаж по всем сегментам, используя столбец customer_id в качестве ключа распределения:
ALTER TABLE sales SET DISTRIBUTED BY (customer_id);
При изменении распределения хэша таблицы, ее данные автоматически перераспределяются. Изменение политики распределения на случайное распределение не приводит к перераспределению данных. Например, следующая команда ALTER TABLE не имеет немедленного эффекта:
ALTER TABLE sales SET DISTRIBUTED RANDOMLY;
Изменение политики распределения таблицы на DISTRIBUTED REPLICATED или с DISTRIBUTED REPLICATED автоматически перераспределяет данные таблицы. Чтобы перераспределить данные таблиц с политикой случайного распределения, следует использовать REORGANIZE=TRUE. Реорганизация данных может потребоваться для исправления проблемы перекоса данных или при добавлении в систему ресурсов сегмента. Например, следующая команда перераспределяет данные таблицы по всем сегментам, используя текущую политику распределения, включая случайное распределение.
ALTER TABLE sales SET WITH (REORGANIZE=TRUE);
Изменение политики распределения таблицы на DISTRIBUTED REPLICATED или с DISTRIBUTED REPLICATED всегда приводит к перераспределению данных таблицы, даже при применении REORGANIZE=FALSE.
До выпуска 7-ой версии Greenplum, которая вышла в декабре 2022 года, хранение таблицы, сжатие и ориентация могли быть объявлены только при ее создании. Что изменилось с выходом нового релиза, рассмотрим далее.
Изменение таблиц в GP 7
Итак, в 7-ой версии Greenplum, добавлена возможность изменять характеристики хранения уже заполненной таблицы с помощью команды ALTER TABLE. Это позволяет пользователю перевести таблицу кучи в AO- или AOCO-таблицу, включая партиционированные таблицы. Также поддерживается возможность изменения параметров хранения (reloptions) таблицы, включая сжатие таблицы, размер блока и пр.
Эта функция поможет пользователям динамически изменять характеристики хранения своих таблиц при изменении шаблона доступа к ним. Например, такой сценарий идеально подходит для данных временных рядов или таблиц с несколькими разделами, где хранятся данные разной степени новизны. Разделы с историческими данными обновляются редко, а потому отлично подходят для AO-модели, а более новые и часто обновляемые данные могут быть таблицами кучи. Аналогично можно настроить параметры сжатия старых разделов, которые не будут часто обновляться. Поскольку шаблон доступа к таблице обычно имеет дело с подмножеством столбцов, CO-модель также дает определенный выигрыш.
Для этого в Greenplum 7 определяются методы доступа к таблицам, чтобы понять, какая модель хранения в них используется. Преобразование таблицы кучи в AO-таблицу представляет собой изменение метода доступа к ней с кучи на ao_row:
ALTER TABLE tbl SET ACCESS METHOD ao_row;
Чтобы ориентировать эту таблицу на строки, т.е. сделать ее вида AOCO, используется метод доступа ao_column:
ALTER TABLE tbl SET ACCESS METHOD ao_column;
Изменить параметры сжатия таблицы поможет команда ALTER TABLE SET:
ALTER TABLE tbl SET (compresslevel=9, compresstype=zlib);
Для AOCO-таблиц также можно изменить параметры хранения для каждого столбца:
CREATE TABLE tbl(i int, j int) USING ao_column; ALTER TABLE tbl ALTER COLUMN i SET ENCODING (compresslevel=7, compresstype=zstd), ALTER COLUMN j SET ENCODING (compresslevel=2);
Изменить метод доступа к партиционированной таблице с несколькими разделами также поможет команда ALTER TABLE. Например, выполнить преобразовать всех разделов таблицы в AO можно с помощью всего одной команды ALTER из корневого раздела. А чтобы изменить метод доступа только к новым разделам с более свежими данными, которые будут часто обновляться, следует использовать условие ONLY:
ALTER TABLE ONLY tbl SET ACCESS METHOD heap;
После этого любой новый раздел, который будет присоединен к таблице tbl, будет таблицей кучи, разрешая частые операции UPSERT, а существующие разделы останутся нетронутыми. Если вся иерархия разделов или подразделов стала историческими данными и не требует частых обновлений, можно повысить параметры сжатия, используя тот же синтаксис, и напрямую применить его к корню. А условие ONLY позволит применить эти параметры хранения только к будущим разделам, не затрагивая существующие. Параметры хранения на уровне столбцов для таблиц AOCO можно изменить аналогичным образом во всей иерархии разделов:
ALTER TABLE tbl ALTER COLUMN id SET ENCODING (compresslevel=7, compresstype=zstd);
По сути, поведение условия ONLY аналогично опциям на уровне таблицы. Подробнее про ключевое слово ONLY и управление разделами таблиц читайте в нашей новой статье.
В заключение отметим, что благодаря введению методов доступа к таблицам в Greenplum 7, можно комбинировать все вариации команды семейства ALTER TABLE. Например, одновременно изменить метод доступа, параметры хранения и структуру таблицы, добавив/удалив столбец:
ALTER TABLE tbl SET ACCESS METHOD ao_row, ADD column j int; ALTER TABLE tbl SET (compresslevel=7), ADD COLUMN k int;
Освойте администрирование и эксплуатацию Greenplum с Arenadata DB для эффективного хранения и аналитики больших данных на специализированных курсах в нашем лицензированном учебном центре обучения и повышения квалификации для разработчиков, менеджеров, архитекторов, инженеров, администраторов, Data Scientist’ов и аналитиков Big Data в Москве:
Источники
