Виды команд SQL, DDL и права доступа

Инженер данных создаёт таблиц больше, чем аналитик пишет запросов: staging, промежуточные, витрины, копии для проверки. Урок про то, какие решения при этом принимаются и как они аукаются потом.

Виды команд SQL

Команды SQL делят на пять групп по тому, что они меняют. Названия групп встречаются в документации, в настройках прав доступа и на собеседованиях, поэтому их стоит знать.

По одному примеру на группу:

-- DDL: описать структуру
CREATE TABLE dim_city (city text PRIMARY KEY, region text NOT NULL);
 
-- DML: изменить данные
INSERT INTO dim_city SELECT city, region FROM cities;
 
-- DQL: прочитать
SELECT region, count(*) FROM dim_city GROUP BY region;
 
-- DCL: разрешить чтение роли аналитиков
GRANT SELECT ON dim_city TO analyst;
 
-- TCL: зафиксировать или отменить группу изменений
BEGIN;
DELETE FROM dim_city WHERE region = 'Урал';
ROLLBACK;

Зачем это деление на практике:

• Права выдаются по группам. Сервисной учётной записи загрузки нужны DML и часть DDL в своей схеме, аналитику только DQL на витрины. Заявка «дайте доступ на запись» превращается в конкретный список: INSERT, UPDATE, DELETE на таких-то таблицах.
• Группы по-разному ведут себя в транзакциях. В PostgreSQL DDL откатывается вместе с транзакцией, в MySQL и Oracle команда DDL фиксирует транзакцию неявно. Об этом урок про транзакции.
• Группы по-разному блокируют таблицу. SELECT и DML работают одновременно, ALTER TABLE и TRUNCATE требуют исключительной блокировки и останавливают всех.
• TRUNCATE относится к DDL, хотя удаляет данные: он не обрабатывает строки по одной, а освобождает хранилище таблицы целиком. Поэтому он быстрый, не принимает WHERE и требует других прав, чем DELETE.

В некоторых источниках SELECT относят к DML и отдельную группу DQL не выделяют. Это вопрос классификации, на работу команд он не влияет.

Дальше в уроке подробно DDL и DCL. DML разобран в уроке про изменение данных, TCL в уроке про транзакции.

CREATE TABLE AS: таблица из запроса

Самый частый способ создать таблицу в хранилище:

CREATE TABLE dim_customer AS
SELECT customer_id, full_name, city, segment
FROM customers;

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

Копия структуры без данных:

CREATE TABLE orders_copy (LIKE orders INCLUDING ALL);

INCLUDING ALL переносит умолчания, ограничения и индексы. Удобно для staging-таблицы, которая обязана совпадать с целевой.

Ограничения как документация

CREATE TABLE fact_payment (
  payment_id  int            PRIMARY KEY,
  order_id    int            NOT NULL,
  paid_at     timestamptz    NOT NULL,
  amount      numeric(12,2)  NOT NULL CHECK (amount <> 0),
  method      text           NOT NULL CHECK (method IN ('card', 'sbp', 'cash')),
  status      text           NOT NULL
);

Каждое ограничение это утверждение о данных, которое база проверит за вас. NOT NULL говорит, что колонка обязательна. CHECK фиксирует допустимые значения. PRIMARY KEY объявляет зерно таблицы и заодно создаёт индекс. Попытка вставить строку с amount = -5 в таблицу с CHECK (amount >= 0) упадёт сразу с понятной ошибкой, а не всплывёт через месяц в отчёте.

В аналитических хранилищах ограничения ставят реже, чем в приложениях. Greenplum и ClickHouse не проверяют внешние ключи вовсе, а уникальность в MPP стоит дорого. Поэтому те же утверждения оформляют проверками после загрузки, об этом блок про качество данных. Но там, где база умеет проверять, пусть проверяет: первичный ключ на витрине защищает от дублей надёжнее любого теста.

Выбор типа: решения, которые потом не изменить

Про типы был отдельный урок, здесь три правила для DDL.

numeric(12,2) для денег означает максимум 10 знаков до запятой. Значение 123 456 789.99 в numeric(10,2) не поместится: numeric field overflow. Закладывайте запас, смена типа на таблице в миллиард строк это часы простоя.

text против varchar(n): в PostgreSQL разницы в хранении и скорости нет. varchar(2) обрежет значение при явном приведении и упадёт при вставке более длинного, то есть работает как ограничение. Если длина не часть бизнес-правила, берите text.

timestamptz для моментов времени, date для дат без времени, timestamp без зоны только если вы точно знаете, зачем. Идентификаторы из внешних систем, коды, телефоны это text, даже если состоят из цифр.

ALTER TABLE и чем он опасен

ALTER TABLE orders ADD COLUMN source text DEFAULT 'web';

Добавить колонку с умолчанием в PostgreSQL дёшево: значение по умолчанию хранится в метаданных, существующие строки не переписываются. Изменение типа колонки, напротив, перезаписывает всю таблицу и блокирует её на время операции. В хранилищах с таблицами в терабайты миграция схемы это отдельный проект, поэтому схему стоит продумывать заранее, а изменения делать через новую таблицу и переключение имени.

Временные таблицы и CTE

CREATE TEMP TABLE recent_orders AS
SELECT * FROM orders WHERE created_at >= '2026-06-01';

Временная таблица живёт до конца сессии и видна только ей. Она полезна, когда промежуточный результат нужен нескольким запросам подряд, или когда он большой и по нему хочется собрать статистику через ANALYZE, чтобы планировщик строил хорошие планы. CTE через WITH этого не даёт: он часть одного запроса, и в PostgreSQL 12+ планировщик может встроить его в основной запрос как подзапрос.

В Airflow и dbt промежуточные результаты обычно материализуют обычными таблицами в отдельной схеме: их видно, их можно проверить, к ним можно вернуться при разборе инцидента.

Представления

CREATE VIEW v_order_paid AS
SELECT order_id, sum(amount) AS paid_amount
FROM payments
WHERE status = 'success'
GROUP BY order_id;

Обычное представление это сохранённый запрос. Данных оно не хранит, каждое обращение выполняет запрос заново. Хорошо для стандартизации логики: правило «успешный платёж» записано в одном месте, а не в двадцати отчётах. Плохо для тяжёлых агрегаций, которые дёргают сто раз в день.

Материализованное представление хранит результат и обновляется командой REFRESH MATERIALIZED VIEW. По сути это витрина с автоматическим пересчётом, и в проде её роль чаще играет обычная таблица, которую заполняет пайплайн: так проще управлять расписанием и инкрементом.

DCL: роли и права доступа

Права в PostgreSQL выдаются ролям. Роль может быть учётной записью человека или сервиса, а может быть группой, в которую включают другие роли. Права выдают группам, а людей и сервисы добавляют в группы: так доступ нового сотрудника настраивается одной командой.

-- группа «аналитики: только чтение»
CREATE ROLE analyst_ro NOLOGIN;
 
-- чтобы видеть объекты схемы, нужно право на саму схему
GRANT USAGE ON SCHEMA mart TO analyst_ro;
 
-- чтение всех существующих таблиц схемы
GRANT SELECT ON ALL TABLES IN SCHEMA mart TO analyst_ro;
 
-- и тех, что появятся позже
ALTER DEFAULT PRIVILEGES IN SCHEMA mart GRANT SELECT ON TABLES TO analyst_ro;
 
-- включить человека в группу
GRANT analyst_ro TO ivanov;

Три места, где на этом спотыкаются:

• Право на таблицу без права на схему не работает. Без USAGE на схему запрос упадёт с permission denied for schema, хотя SELECT на таблицу выдан.
• GRANT ... ON ALL TABLES действует только на существующие таблицы. Витрина, созданная завтра, аналитикам не видна, пока не настроены DEFAULT PRIVILEGES. Типичная жалоба «таблица пропала» после пересоздания витрины через DROP и CREATE имеет ту же причину: права были выданы на старый объект.
• Права складываются. Чтобы отнять доступ, мало выполнить REVOKE у одной роли: право может приходить через другую группу или через PUBLIC.

Отзыв права и отдельных действий:

REVOKE SELECT ON mart.fact_payment FROM analyst_ro;
 
-- права можно выдавать на отдельные действия и даже колонки
GRANT INSERT, UPDATE ON stg.orders TO etl_loader;
GRANT SELECT (customer_id, city, segment) ON customers TO analyst_ro;   -- без email и имени

Право на отдельные колонки это простой способ закрыть персональные данные: аналитик видит город и сегмент клиента, но не почту. Для более тонких правил есть представления без чувствительных колонок и политики на уровне строк.

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

Проверить права можно функциями has_table_privilege('analyst_ro', 'orders', 'SELECT') и has_schema_privilege, а посмотреть выданные — в представлении information_schema.role_table_grants.

Удаление и очистка

DROP TABLE удаляет таблицу вместе с зависимыми объектами, если добавить CASCADE. TRUNCATE очищает таблицу мгновенно, без построчного удаления и без возможности отката вне транзакции в некоторых базах. DELETE без WHERE делает то же самое, но медленно и с журналированием каждой строки. Для полной перезаписи витрины TRUNCATE плюс INSERT внутри одной транзакции это стандартный приём: читатели видят либо старую версию, либо новую, но не пустую таблицу.

Практика

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