constraints
Ограничения — это функция многих платформ данных. Когда они указаны, платформа выполняет дополнительную проверку данных при их добавлении в новую таблицу или вставке в уже существующую таблицу. Если проверка не проходит, создание или обновление таблицы не удается, операция откатывается, и вы увидите четкое сообщение об ошибке.
Когда ограничения применяются, они гарантируют, что вы никогда не увидите недопустимые данные в таблице, материализованной вашей моделью. Применение варьируется в зависимости от платформы данных.
Предварительные требования
Перед использованием ограничений убедитесь, что выполнены следующие требования:
- Вы используете поддерживаемые материализации — Ограничения работают только для моделей с материализацией
tableиincremental. Ограничения никогда не применяются к моделям с материализациейephemeralилиview. - Вы обеспечиваете соблюдение контракта — Для использования ограничений ваша модель должна объявлять контракт и обеспечивать его соблюдение. Это означает, что необходимо явно определить
data_typeдля каждого столбца в конфигурации схемы модели.
Поддержка ограничений на платформах
Выберите вкладку нужного адаптера, чтобы узнать больше о поддержке ограничений на разных платформах. Ограничения делятся на три категории в зависимости от возможности их определения и обеспечения их соблюдения платформой:
- Можно определить, соблюдение обеспечивается — Модель не будет построена, если она нарушает ограничение.
- Можно определить, соблюдение не обеспечивается — Платформа позволяет указать тип ограничения, но модель всё равно может быть построена, даже если её построение нарушает это ограничение. Такое ограничение существует только в качестве метаданных. Этот подход более характерен для облачных хранилищ данных, чем для транзакционных баз данных, где чаще обеспечивается строгое соблюдение правил.
- Нельзя определить, соблюдение не обеспечивается — Указать этот тип ограничения для платформы невозможно.
- Redshift
- Snowflake
- BigQuery
- Postgres
- Spark
- Databricks
- Fabric
- Athena
| Тип ограничения | Можно определить | Соблюдение обеспечивается |
|---|---|---|
| not_null | ✅ | ✅ |
| primary_key | ✅ | ❌ |
| foreign_key | ✅ | ❌ |
| unique | ✅ | ❌ |
| check | ❌ | ❌ |
| Тип ограничения | Можно определить | Соблюдение обеспечивается |
|---|---|---|
| not_null | ✅ | ✅ |
| primary_key | ✅ | ❌ |
| foreign_key | ✅ | ❌ |
| unique | ✅ | ❌ |
| check | ❌ | ❌ |
| Тип ограничения | Можно определить | Соблюдение обеспечивается |
|---|---|---|
| not_null | ✅ | ✅ |
| primary_key | ✅ | ❌ |
| foreign_key | ✅ | ❌ |
| unique | ❌ | ❌ |
| check | ❌ | ❌ |
| Тип ограничения | Можно определить | Соблюдение обеспечивается |
|---|---|---|
| not_null | ✅ | ✅ |
| primary_key | ✅ | ✅ |
| foreign_key | ✅ | ✅ |
| unique | ✅ | ✅ |
| check | ✅ | ✅ |
В настоящее время соблюдение ограничений not_null и check обеспечивается только после построения модели. Из-за этого ограничения платформы dbt считает, что эти ограничения можно определить, но их соблюдение не обеспечивается. Это означает, что они не входят в контракт модели, поскольку их соблюдение невозможно обеспечить во время построения. Эта таблица будет меняться по мере развития функциональности.
| Тип ограничения | Можно определить | Соблюдение обеспечивается |
|---|---|---|
| not_null | ✅ | ❌ |
| primary_key | ✅ | ❌ |
| foreign_key | ✅ | ❌ |
| unique | ✅ | ❌ |
| check | ✅ | ❌ |
В настоящее время соблюдение ограничений not_null и check обеспечивается только после построения модели. Из-за этого ограничения платформы dbt считает, что эти ограничения можно определить, но их соблюдение не обеспечивается. Это означает, что они не входят в контракт модели, поскольку их соблюдение невозможно обеспечить во время построения. Эта таблица будет меняться по мере развития функциональности.
| Тип ограничения | Можно определить | Соблюдение обеспечивается |
|---|---|---|
| not_null | ✅ | ✅ |
| primary_key | ✅ | ❌ |
| foreign_key | ✅ | ❌ |
| unique | ❌ | ❌ |
| check | ✅ | ✅ |
Microsoft Fabric сначала создаёт таблицу, а затем добавляет некоторые ограничения отдельными шагами. При построении модели проверяется только not_null. Ограничения первичного ключа, внешнего ключа и уникальности сохраняются в определении таблицы для справки, но Fabric не проверяет их при добавлении новых данных. Определяйте primary_key, foreign_key и unique на уровне модели. Адаптер игнорирует эти типы ограничений на уровне столбцов.
| Тип ограничения | Можно определить | Соблюдение обеспечивается |
|---|---|---|
| not_null | ✅ | ✅ |
| primary_key | ✅ | ❌ |
| foreign_key | ✅ | ❌ |
| unique | ✅ | ❌ |
| check | ❌ | ❌ |
| Тип ограничения | Можно определить | Соблюдение обеспечивается |
|---|---|---|
| not_null | ❌ | ❌ |
| primary_key | ❌ | ❌ |
| foreign_key | ❌ | ❌ |
| unique | ❌ | ❌ |
| check | ❌ | ❌ |
Определение ограничений
Ограничения могут быть определены для одного столбца или на уровне модели для одного или нескольких столбцов. Как общее правило, мы рекомендуем определять ограничения для одного столбца непосредственно на этих столбцах.
Если вы определяете несколько ограничений primary_key для одной модели, они должны быть определены на уровне модели. Определение нескольких ограничений primary_key на уровне столбца не поддерживается.
Структура ограничения:
type(обязательно): одно изnot_null,unique,primary_key,foreign_key,check,customexpression: Свободный текст для уточнения ограничения. Обязательно для некоторых типов ограничений и необязательно для других.name(необязательно): Удобочитаемое имя для этого ограничения. Поддерживается некоторыми платформами данных.columns(только на уровне модели): Список имен столбцов, к которым применяется ограничение.
Ограничения внешнего ключа принимают два дополнительных параметра:
Этот синтаксис определения внешних ключей использует ref, а значит, учитывает зависимости и работает в разных окружениях. Он доступен в dbt v1 Latest и dbt v1.9+.
Этот синтаксис определения внешних ключей использует ref, что означает автоматический учет зависимостей и корректную работу в разных окружениях. Он доступен в "Latest" и dbt v1 v1.9+.
Поскольку поддержка и применение ограничений различаются в зависимости от платформы, dbt предоставляет два необязательных поля, которые можно указать для любого ограничения:
warn_unenforced: Установите вFalse, чтобы отключить предупреждения для ограничений, которые поддерживаются вашей платформой, но фактически не применяются (например,primary_keyв Snowflake).warn_unsupported: Установите вFalse, чтобы отключить предупреждения для ограничений, которые вообще не поддерживаются вашей платформой (например,checkв Redshift).
models:
- name: <model_name>
# обязательно
config:
materialized: table
contract: {enforced: true}
# ограничения на уровне модели
constraints:
- type: primary_key
columns: [first_column, second_column, ...]
warn_unsupported: True # show a warning if unsupported
- type: foreign_key # multi_column
columns: [first_column, second_column, ...]
to: ref('my_model_to') | source('source', 'source_table')
to_columns: [other_model_first_column, other_model_second_columns, ...]
- type: check
columns: [first_column, second_column, ...]
expression: "first_column != second_column"
name: human_friendly_name
- type: ...
columns:
- name: first_column
data_type: string
# ограничения на уровне столбца
constraints:
- type: not_null
- type: unique
- type: foreign_key
to: ref('my_model_to') | source('source', 'source_table')
to_columns: [other_model_column]
warn_unenforced: False # skips warning if supported but not enforced
- type: ...
Адаптеры, поддерживающие эти поля, используют их вместо expression при построении ограничения внешнего ключа, если поля заполнены.
Для получения дополнительной информации об адаптерах, которые поддерживают ограничения внешних ключей, ознакомьтесь с нашим руководством по поддержке ограничений платформами.
Поддержка, специфичная для платформы
В транзакционных базах данных возможно определение "ограничений" на допустимые значения определенных столбцов, более строгих, чем просто тип данных этих значений. Например, Postgres поддерживает и применяет все ограничения стандарта ANSI SQL (not null, unique, primary key, foreign key), а также гибкое ограничение на уровне строки check, которое оценивается в логическое выражение.
Большинство аналитических платформ данных поддерживают и обеспечивают соблюдение ограничения not null, но остальные ограничения они либо не поддерживают, либо не обеспечивают их соблюдение. Иногда всё же имеет смысл добавить «информационное» ограничение, зная, что его соблюдение не обеспечивается, для интеграции с устаревшими каталогами данных или инструментами построения диаграмм «сущность — связь» (dbt-labs/dbt#3295). Некоторые платформы данных могут использовать ограничения первичного или внешнего ключа для оптимизации запросов, если указать дополнительное ключевое слово.
Для этого вы можете указать два дополнительных поля для любого фильтра:
warn_unenforced: False, чтобы пропустить предупреждение об ограничениях, которые поддерживаются, но не применяются этой платформой данных. Ограничение будет включено в шаблонный DDL.warn_unsupported: False, чтобы пропустить предупреждение об ограничениях, которые не поддерживаются этой платформой данных, и поэтому не будут включены в шаблонный DDL.
- Postgres
- Redshift
- Snowflake
- BigQuery
- Databricks
- Fabric
- Документация по ограничениям PostgreSQL: здесь
{{
config(
materialized = "table"
)
}}
select
1 as id,
'My Favorite Customer' as customer_name,
cast('2019-01-01' as date) as first_transaction_date
models:
- name: constraints_example
config:
contract:
enforced: true
columns:
- name: id
data_type: int
constraints:
- type: not_null
- type: primary_key
- type: check
expression: "id > 0"
- name: customer_name
data_type: text
- name: first_transaction_date
data_type: date
Ожидаемый DDL для применения ограничений:
create table "database_name"."schema_name"."constraints_example__dbt_tmp"
(
id integer not null primary key check (id > 0),
customer_name text,
first_transaction_date date
)
;
insert into "database_name"."schema_name"."constraints_example__dbt_tmp"
(
id,
customer_name,
first_transaction_date
)
(
select
1 as id,
'My Favorite Customer' as customer_name,
cast('2019-01-01' as date) as first_transaction_date
);
Redshift в настоящее время применяет только ограничения not null; все остальные ограничения являются только метаданными. Кроме того, Redshift не позволяет проверять столбцы при создании таблицы. Подробнее см. в документации Redshift здесь.
{{
config(
materialized = "table"
)
}}
select
1 as id,
'My Favorite Customer' as customer_name,
cast('2019-01-01' as date) as first_transaction_date
models:
- name: constraints_example
config:
contract:
enforced: true
columns:
- name: id
data_type: integer
constraints:
- type: not_null
- type: primary_key # не применяется -- будет предупреждение и включение
- type: check # не поддерживается -- будет предупреждение и пропуск
expression: "id > 0"
data_tests:
- unique # ограничение primary_key не применяется
- name: customer_name
data_type: varchar
- name: first_transaction_date
data_type: date
Обратите внимание, что Redshift ограничивает максимальную длину значений varchar до 256 символов по умолчанию (или когда указано без длины). Это означает, что любые строковые данные, превышающие 256 символов, могут быть усечены или вернуть ошибку "значение слишком длинное для типа символов". Чтобы разрешить максимальную длину, используйте varchar(max). Например, data_type: varchar(max).
Ожидаемый DDL для применения ограничений:
create table "database_name"."schema_name"."constraints_example__dbt_tmp"
(
id integer not null,
customer_name varchar,
first_transaction_date date,
primary key(id)
)
;
insert into "database_name"."schema_name"."constraints_example__dbt_tmp"
(
select
1 as id,
'My Favorite Customer' as customer_name,
cast('2019-01-01' as date) as first_transaction_date
);
Snowflake поддерживает четыре типа ограничений: unique, not null, primary key и foreign key.
Важно отметить, что в настоящее время проверяются только not null (и свойство not null для primary key).
Остальные ограничения являются чисто метаданными и не проверяются при вставке данных. Хотя Snowflake не проверяет ограничения unique, primary или foreign_key, вы можете опционально указать Snowflake использовать их для оптимизации запросов, указав rely в поле expression ограничения.
В настоящее время Snowflake не поддерживает синтаксис check, и dbt пропустит конфигурацию check и выдаст предупреждающее сообщение, если она установлена на некоторых моделях в проекте dbt.
{{
config(
materialized = "table"
)
}}
select
1 as id,
'My Favorite Customer' as customer_name,
cast('2019-01-01' as date) as first_transaction_date
models:
- name: constraints_example
config:
contract:
enforced: true
columns:
- name: id
data_type: integer
description: hello
constraints:
- type: not_null
- type: primary_key # не применяется -- будет предупреждение и включение
- type: check # не поддерживается -- будет предупреждение и пропуск
expression: "id > 0"
data_tests:
- unique # этот тест нужен, потому что ограничение primary_key не применяется
- name: customer_name
data_type: text
- name: first_transaction_date
data_type: date
Ожидаемый DDL для применения ограничений:
create or replace transient table <database>.<schema>.constraints_example
(
id integer not null primary key,
customer_name text,
first_transaction_date date
)
as
(
select
1 as id,
'My Favorite Customer' as customer_name,
cast('2019-01-01' as date) as first_transaction_date
);
BigQuery позволяет определять и обеспечивать соблюдение ограничений not null, а также определять (но не обеспечивать соблюдение) ограничения primary key и foreign key (которые могут использоваться для оптимизации запросов). BigQuery не поддерживает определение или обеспечение соблюдения других ограничений. Подробнее см. в разделе Platform constraint support.
Документация: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language
Типы данных: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types
{{
config(
materialized = "table"
)
}}
select
1 as id,
'My Favorite Customer' as customer_name,
cast('2019-01-01' as date) as first_transaction_date
models:
- name: constraints_example
config:
contract:
enforced: true
columns:
- name: id
data_type: int
constraints:
- type: not_null
- type: primary_key # не применяется -- будет предупреждение и включение
- type: check # не поддерживается -- будет предупреждение и пропуск
expression: "id > 0"
data_tests:
- unique # ограничение primary_key не обеспечивается (не проверяется)
- name: customer_name
data_type: string
- name: first_transaction_date
data_type: date
Ожидаемый DDL для обеспечения соблюдения ограничений:
create or replace table `<project>`.`<dataset>`.`constraints_example`
(
id integer not null,
customer_name string,
first_transaction_date date
)
as
(
select
1 as id,
'My Favorite Customer' as customer_name,
cast('2019-01-01' as date) as first_transaction_date
);
Ограничение уровня столбца для вложенного столбца:
{{
config(
materialized = "table"
)
}}
select
'string' as a,
struct(
1 as id,
'name' as name,
struct(2 as id, struct('test' as again, 2 as even_more) as another) as double_nested
) as b
models:
- name: nested_column_constraints_example
config:
contract:
enforced: true
columns:
- name: a
data_type: string
- name: b.id
data_type: integer
constraints:
- type: not_null
- name: b.name
description: test description
data_type: string
- name: b.double_nested.id
data_type: integer
- name: b.double_nested.another.again
data_type: string
- name: b.double_nested.another.even_more
data_type: integer
constraints:
- type: not_null
Databricks позволяет вам определять:
- ограничение
not null - и/или дополнительные ограничения
check, с условными выражениями, включающими один или несколько столбцов
Поскольку Databricks не поддерживает транзакции и не позволяет использовать create or replace table с схемой столбцов, таблица сначала создается без схемы, а затем выполняются операторы alter для добавления различных ограничений.
Это означает, что:
- Имена и порядок столбцов проверяются, но не их тип
- Если
constraintsи/илиconstraint_checkне проходят, таблица с ошибочными данными все равно будет существовать в хранилище
См. эту страницу с более подробной информацией о поддержке ограничений в Databricks.
{{
config(
materialized = "table"
)
}}
select
1 as id,
'My Favorite Customer' as customer_name,
cast('2019-01-01' as date) as first_transaction_date
models:
- name: constraints_example
config:
contract:
enforced: true
columns:
- name: id
data_type: int
constraints:
- type: not_null
- type: primary_key # не применяется -- будет предупреждение и включение
- type: check # не поддерживается -- будет предупреждение и пропуск
expression: "id > 0"
data_tests:
- unique # ограничение primary_key не применяется
- name: customer_name
data_type: text
- name: first_transaction_date
data_type: date
Ожидаемый DDL для применения ограничений:
create or replace table schema_name.constraints_example
using delta
as
select
1 as id,
'My Favorite Customer' as customer_name,
cast('2019-01-01' as date) as first_transaction_date
После чего выполняются операторы
alter table schema_name.constraints_example change column id set not null;
alter table schema_name.constraints_example add constraint 472394792387497234 check (id > 0);
В Microsoft Fabric можно задать:
- Ограничение
not_null, которое проверяется при сборке модели. - Ограничения
primary_key,foreign_keyиunique. Они сохраняются как справочные сведения о таблице и не проверяются при добавлении данных.
Учитывайте ограничения и требования:
- Microsoft Fabric не поддерживает ограничения
check. Для проверки собственных правил используйте тесты данных, напримерdbt_utils.expression_is_true. - Microsoft Fabric включает не все ограничения в исходную инструкцию
create table. dbt сначала создаёт таблицу, а затем добавляет ограничения уровня модели отдельными инструкциями. - Для каждого ограничения уровня модели укажите
name. - Столбцы исходных таблиц для снимков не могут иметь ограничений. Если у исходного столбца есть
not_nullили аналогичное ограничение, создание снимка может завершиться ошибкой.
Подробнее см. Настройки Microsoft Fabric Data Warehouse.
{{
config(
materialized = "table"
)
}}
select
1 as id,
'My Favorite Customer' as customer_name,
cast('2019-01-01' as date) as first_transaction_date
models:
- name: constraints_example
config:
contract:
enforced: true
constraints:
- type: primary_key
columns: [id]
name: pk_constraints_example
columns:
- name: id
data_type: int
constraints:
- type: not_null
- type: check # not supported -- will warn & skip
expression: "id > 0"
data_tests:
- unique # primary_key constraint is not enforced
- name: customer_name
data_type: varchar(100)
- name: first_transaction_date
data_type: date
Ожидаемый SQL для применения ограничений:
create table schema_name.constraints_example
(
id int not null,
customer_name varchar(100),
first_transaction_date date
);
alter table schema_name.constraints_example add constraint pk_constraints_example primary key nonclustered(id) not enforced;
Пользовательские ограничения
В dbt можно использовать пользовательские ограничения для моделей, чтобы более гибко настраивать таблицы. Разные хранилища данных поддерживают разный синтаксис и разные возможности.
Пользовательские ограничения позволяют добавлять конфигурацию к конкретным столбцам. Например:
-
Установить политику маскирования в Snowflake при использовании Create Table As Select (CTAS).
-
Другие хранилища данных (такие как Databricks и BigQuery имеют свои собственные наборы параметров, которые могут быть установлены для столбцов в их операторах CTAS.
Вы можете реализовать ограничения несколькими способами: