Суррогатные ключи в dbt: целые числа или хеши?
Те, кто давно занимается построением хранилищ данных, несомненно, сталкивались с задачей создания суррогатных ключей в своих моделях данных. Наличие столбца, который уникально представляет каждую сущность, помогает гарантировать, что ваша модель данных полная, не содержит дубликатов и может объединяться с другими моделями данных в вашем хранилище.
Иногда нам везет, и у нас есть источники данных с уже встроенными ключами — например, данные Shopify, синхронизированные через их API, имеют простые в использовании ключи на всех таблицах, записанных в ваше хранилище. Если это не так, или если вы строите модель данных с составным ключом (то есть данные уникальны по нескольким измерениям), вам придется полагаться на какую-то стратегию для создания и поддержания этих ключей самостоятельно. Как это сделать с помощью dbt? Давайте разберемся.
К ак раньше управляли суррогатными ключами?
До появления аналитических инструментов для хранилищ данных, которые мы используем сегодня, архитектура хранилищ данных имела несколько ключевых ограничений, которые привели к появлению хранилищ в стиле Кимбалла со снежной схемой. Это было связано с тем, что хранение данных было дорогим — было более эффективно хранить данные как можно меньше раз и полагаться на объединения для соединения данных, когда это требовалось для отчета. И чтобы сделать эти объединения эффективными, стало стандартной практикой использовать монотонно возрастающие целочисленные суррогатные ключи (MIISKs), что является сложным способом сказать "считайте каждую запись, начиная с единицы", чтобы ваша модель данных выглядела примерно так (вы — торговец сыром):
| product_id | product_name | created_by | created_at |
|---|---|---|---|
| 1 | mozzarella | 23 | 0001-05-05 |
| 2 | cheddar | 24 | 1150-02-03 |
| 3 | gruyere | 25 | 1655-04-03 |
| order_line_id | order_id | product_id | amount | created_at |
|---|---|---|---|---|
| 1 | 1 | 3 | 40 | 2022-07-01 |
| 2 | 2 | 1 | 50 | 2022-07-05 |
| 3 | 3 | 1 | 10 | 2022-07-07 |
| 4 | 3 | 2 | 30 | 2022-07-07 |
| order_id | customer_id | created_at |
|---|---|---|
| 1 | 5 | 2022-07-01 |
| 2 | 8 | 2022-07-05 |
| 3 | 10 | 2022-07-07 |
Здесь есть очевидные преимущества!
- Существуют четкие, интуитивно понятные отношения между этими сущностями!
- Поскольку ключи здесь — это небольшие целые числа, база данных может а) не беспокоиться о затратах на хранение этих данных б) легко индексировать это поле, что делает объединения быстрыми и эффективными.
Однако здесь также есть некоторые очевидные проблемы с обслуживанием. Обновление, например, вашей таблицы продуктов потребует тщательной работы, чтобы гарантировать, что ассоциация чеддера с id 2 никогда не будет случайно изменена. Вы, возможно, слышали фразу "загружайте измерения перед фактами" — это относится к тщательной работе, необходимой для поддержания этой ссылочной целостности. Кроме того, вам нужно знать о точном состоянии данных перед внесением любых обновлений. Эти данные состоянием, что делает их жесткими и более сложными в работе, если произойдут какие-либо потери данных. Представьте, что нужно восстанавливать эти отношения с нуля!
MIISKs в dbt
Если это ваш предпочтительный подход к моделированию, dbt может полностью поддержать этот рабочий процесс! Это, вероятно, потребует от вас использования встроенной функциональности хранилища для генерации этих MIISKs — в Snowflake мы можем использовать последовательности, которые являются объектами, созданными именно для этой цели. Мы будем использовать Snowflake в качестве примера здесь, но этот подход, вероятно, можно адаптировать и для других хранилищ.
Создание и поддержание последовательностей
Чтобы правильно поддерживать последовательность суррогатных ключей в ваших моделях данных, нам нужно будет создать и поддерживать последовательность для каждой таблицы, которой она нужна. Чтобы сделать это в масштабе, мы воспользуемся конфигурацией meta модели dbt. Эта конфигурация позволяет вам определить любой словарь метаданных, который вы хотите. Используя это, мы можем программно применить конфигурацию суррогатного ключа для каждой модели, которой она нужна, и ссылаться на эту конфигурацию в макросе для правильного создания и обновления суррогатных ключей, когда это необходимо.
Вот пример конфигурации:
# назначьте конфигурацию суррогатного ключа вашей модели
version: 2
models:
- name: dim_customers
description: все клиенты
config:
meta:
surrogate_key: true
Эти метаданные затем могут быть использованы в макросе в операции on-run-start, чтобы гарантировать, что все последовательности существуют для всех моделей, которым они нужны, до выполнения моделей.
# в macros/generate_sequences.sql
{% macro generate_sequences() %}
{% if execute %}
{% set models = graph.nodes.values() | selectattr('resource_type', 'eq', 'model') %}
{# анализируем объект графа, находим все модели с конфигурацией мета суррогатного ключа #}
{% set sk_models = [] %}
{% for model in models %}
{% if model.config.meta.surrogate_key %}
{% do sk_models.append(model) %}
{% endif %}
{% endfor %}
{% endif %}
{% for model in sk_models %}
{% if flags.FULL_REFRESH or model.config.materialized == 'table' %}
{# при необходимости регенерируем последовательности #}
create or replace sequence {{ model.database }}.{{ model.schema }}.{{ model.name }}_seq;
{% else %}
{# создаем только если не существует для инкрементных моделей #}
create sequence if not exists {{ model.database }}.{{ model.schema }}.{{ model.name }}_seq;
{% endif %}
{% endfor %}
{% endmacro %}
Вы можете видеть в приведенном выше макросе, что мы используем соглашение об именах — для любой модели имя последовательности будет существов ать в той же базе данных и схеме и следовать соглашению об именах <model_name>_seq. Соблюдение этого шаблона позволяет нам также создать простой макрос для увеличения последовательностей в наших определениях моделей без необходимости жестко кодировать имя последовательности в каждой модели, которой нужен суррогатный ключ.
# в macros/increment_sequence.sql
{%- macro increment_sequence() -%}
{{ this.name }}_seq.nextval
{%- endmacro -%}
Таким образом, ваш код модели выглядит так:
# в dim_customers
...
with cte_name as (
...
)
...
select
{{ increment_sequence() }} as customer_id,
first_name,
last_name
from cte_name
...
Предостережения
Несмотря на относительную простоту этой стратегии, есть несколько недостатков, связанных с тем, чтобы убедиться, что эти последов ательности работают так, как мы хотим.
-
Ошибки dbt Run - Если инкрементная модель, в которой суррогатные ключи поддерживаются таким образом, не удается из-за какой-либо ошибки SQL, мы можем получить пробелы в нашем суррогатном ключе. Когда dbt выполняет модель, последовательность запрашивается и, следовательно, увеличивается, но ошибка модели предотвращает изменения в целевой таблице модели. Это означает, что в следующий раз, когда мы запускаем модель, инкрементная модель начнет с неправильного значения, и мы можем получить столбец, который выглядит так:
surrogate_key_id 1 2 3 4 5 8 На самом деле, большинство облачных платформ не могут гарантировать, что последовательности будут генерироваться без пробелов из-за их использования параллельной обработки, даже если у нас нет ошибки выполн ения dbt — поскольку запросы будут распределены по нескольким вычислительным кластерам, каждый шаг может запрашивать последовательность в разное время, что делает возможным получение результата последовательности не по порядку. Это важное соображение при использовании последовательностей — если это является препятствием, вам может понадобиться дополнительная логика SQL в наших моделях (например, функция
row_number()), чтобы гарантировать, что ваши ключи монотонно возрастают. -
Представления - Поскольку последовательности в Snowflake увеличиваются при каждом запросе, использование их в качестве суррогатных ключей для представлений означало бы, что каждый раз, когда представление запрашивается, последовательность увеличивается и, следовательно, изменяется. Эта стратегия будет работать только для таблиц или инкрементных моделей.
-
Упорядочение - Поскольку последовательности будут регенерироваться при каждом запуске для таблиц и каждый раз, когда инкрементная модель регенерируется, порядок результирующего запроса определяет, какие записи получают каждый ключ. Чтобы поддерживать ссылочную целостность (т.е. product_id 1 всегда означает моцареллу), вам нужно встроить операторы
ORDER BYв ваши модели. Это может вызвать неблагоприятную производительность при построении таблиц. -
"Загружайте измерения перед фактами" - Эта стратегия также может привести к очень запутанным DAG, чтобы сохранить отношения в вашем проекте. Как упоминалось выше, крайне важно, чтобы каждая запись продукта приводила к одному и тому же значению суррогатного ключа каждый раз, когда dbt выполняется. Кроме того, это означает, что любая таблица, которая должна читать из этой таблицы, должна выполняться ниже этого начального процесса. Это может привести к узким местам во время выполнения.
Несмотря на то, что настройка MIISKs с последовательностями может быть довольно хорошо автоматизирована, это немного хрупкий процесс, который полагается на множество предположений и требует большого количества ресурсов от команды данных для восстановления хранилища, если что-то пойдет не так.
Хешированные суррогат ные ключи
Альтернативой использованию традиционной стратегии MIISK является использование криптографических хеш-функций для вывода значений суррогатных ключей из самих данных, что является сложным способом сказать "создайте случайную строку для каждой уникальной комбинации значений, которую вы найдете в моей таблице". Эти хеш-функции детерминированы, что означает, что один и тот же набор входных данных всегда будет производить один и тот же выход. В наших SQL моделях мы можем передать столбец или столбцы, которые представляют зерно, в эту хеш-функцию и voilà, последовательный, уникальный идентификатор генерируется автоматически! Это было упаковано в макрос surrogate_key() в пакете dbt_utils (source), и работает на всех поставщиках хранилищ! Ознакомьтесь с нашим постом SQL Magic, который более подробно рассматривает эту функцию здесь.
# в models/reports/daily_user_orders.sql
with
orders as (
select * from {{ ref('fct_orders') }}
),
agg as (
select
date_trunc(day, order_date) as report_date
user_id,
count(*) as total_orders
from orders
group by 1,2
),
final as (
select
{{ dbt_utils.generate_surrogate_key([
'report_date',
'user_id'
])
}} as unique_key,
*
from agg
)
select * from final
Использование хешированных ключей делает наши преобразования — каждый запуск dbt приводит к точно таким же результатам каждый раз. Я могу безопасно удалить все свои объекты, не являющиеся источниками, в моем хранилище, выполнить dbt run и вернуться туда, откуда начал (хотя я бы не рекомендовал это делать 😅).
Аналитические хранилища, которые мы используем сейчас, больше не имеют тех же ограничений, что и традиционные хранилища — объединения по строкам не заметно менее производительны, чем по целым числам, и хранение немного больших значений в столбце суррогатного ключа — это мелочь, учитывая относительную стоимость хранения на этих платформах. Эта стратегия также устраняет необходимость в тесной связи преобразований для распространения значений суррогатных ключей по нашему проекту — везде, где присутствуют входные данные для суррогатных ключей, хеш-функция производит те же ключи, так что мы можем воспользоваться параллельной обработкой в нашем хранилище и избежать узких мест, которые у нас были раньше.
Предостережения
Эта стратегия также не лишена своих предостережений!
- Коллизии - Хотя это чрезвычайно редко, в зависимости от используемого алгоритма хеширования, возможно, что два разных набора входных данных могут производить одинаковые выходные данные, вызывая ошибочные дублирующие записи в вашем наборе данных. Используя хеш MD5 (по умолчанию для макроса
dbt_utils.generate_surrogate_key), у вас есть 50% вероятность коллизии, когда вы достигнете 2^64 записей (1.84 x 10E19, то есть очень много данных). Хотя это очень, очень, очень маловероятно, это, безусловно, стоит учитывать для действительно больших наборов данных. - Типы данных - Если вы находитесь в процессе миграции устаревшего кода к новому поставщику хранилищ, у вас, вероятно, есть некоторые ограничения на тип данных ваших ключей от потребителей ваших наборов данных, и могут возникнуть проблемы с преобразованием в строковый ключ. К счастью, некоторые поставщики хранилищ имеют хеш-функции, которые выводят целочисленные значения (например, функции Snowflake
MD5_UPPER/LOWER_64). Однако у них меньше бит в функции хеширования, что может привести к проблемам с коллизиями на больших наборах данных. - Производительность - Хешированные ключи обычно приводят к длинным строковым значениям. На огромных наборах данных в некоторых хранилищах это может вызвать проблемы с производительностью. В отличие от MIISKs, строковые значения не могут быть легко разделены для улучшения производительности запросов. К счастью, как описано в предыдущем пункте, вы можете выбрать использование хеш-функций, которые выводят другие, более производительные типы данных!
- Хранение - Как упоминалось выше, хеш-ключи приведут к более высоким затратам на хранение, чем их аналоги MIISK. Учитывая, что стоимость хранения в облачных хранилищах чрезвычайно низка, вряд ли стоит оптимизировать затраты на хранение.
Итак, какой выбрать?
Суррогатные ключи являются критическим компонентом логической модели данных, и, как и в большинстве случаев, у вас есть варианты, когда дело доходит до их генерации и поддержания с помощью dbt. Уникальные ограничения вашего бизнеса в отношении затрат на обслуживание, производительности и размера данных, вероятно, будут основными факторами, определяющими ваше решение. Также важно учитывать потребности ваших заинтересованных сторон — привыкли ли они видеть данные в определенном формате? есть ли сотня дашбордов, которые взорвутся, если вы измените некоторые ключи с целых чисел на строки? Для многих организаций это нетривиальное решение!
На мой взгляд, простота использования хешированных ключей значительно перевешивает потенциальные преимущества наличия MIISKs в вашей модели данных. Построение с dbt работает лучше всего, когда все части вашего проекта идемпотентны, и хешированные ключи требуют почти нулевого обслуживания. Затраты времени на восстановление ваших суррога тных ключей в ваших моделях данных, если вы не можете воссоздать их с помощью простого dbt run, обычно компенсируют любые скромные улучшения производительности и хранения, которые вы могли бы достичь с помощью MIISKs.
Большое спасибо Майку Фуллеру и Бенни Регенольду за помощь в разработке этой статьи!
Comments