Генерация суррогатных ключей в различных хранилищах данных
Почему первичные ключи важны
Мы все знаем одно из самых фундаментальных правил в данных: каждая table должна иметь primary key. Первичные ключи важны по многим причинам:
- Они гарантируют, что в вашей таблице нет дублирующихся строк
- Они помогают устанавливать связи с другими таблицами
- Они позволяют быстро определить grain таблицы (например, таблица
customersс PKcustomer_idимеет одну строку на каждого клиента) - Вы можете тестировать их в dbt, чтобы убедиться, что ваши данные полные и уникальные
Если вам повезет, ваши данные уже будут иметь уникальный первичный ключ. Возможно, это id, сгенерированный вашими внутренними системами, или вы получаете данные из стороннего источника, который генерирует id для вас.
Однако иногда вы оказываетесь в ситуации, когда у вас нет аккуратного первичного ключа.
Например, возможно, вы объединили своих пользователей с набором функций, которые они используют в вашем продукте, так что ваши данные должны быть уникальными на основе user_id + product_id.
Вопрос в том, как в этой ситуации установить первичный ключ? Познакомьтесь с суррогатным ключом.
Что такое суррогатный ключ?
Суррогатный ключ — это первичный ключ, который, вместо того чтобы существовать в вашем исходном наборе данных, создается на уровне аналитики.
Изучение того, когда использовать суррогатные ключи и как внедрять их в ваш проект, является критическим навыком для любого аналитика.
Знать, когда использовать суррогатный ключ, на самом деле довольно просто: у вас должен быть суррогатный ключ в любой таблице, которая еще не имеет уникального первичного ключа.
Знание как создать суррогатный ключ может оказаться гораздо более сложной задачей. Причина в том, что не всегда легко определить лучший способ реализации суррогатных ключей. Должны ли вы просто выделять время в своем календаре каждый день, чтобы индивидуально называть каждую из ваших строк?
Оказывается, это относительно хорошо решенная проблема. Чтобы создать суррогатный ключ, вы традиционно следуете этим двум шагам.
- Объедините все поля, необходимые для создания уникальной строки (например,
user_idиproduct_id) - Примените функцию для создания криптографического хэша (обычно с использованием функции md5) поверх этих данных, чтобы сгенерировать уникальный id для каждой комбинации уникальных значений
Хотя процесс создания суррогатного ключа относительно хорошо понятен, вы будете шокированы (ШОКИРОВАНЫ, ГОВОРЮ Я), узнав, что синтаксис SQL может иметь тонкие различия в разных диалектах и базах данных.
Суррогатные ключи в BigQuery, Databricks, Redshift и Snowflake
Функции concat в BigQuery, Redshift и Snowflake возвращают null, если любой из указанных столбцов для этой строки возвращает null, поэтому для создания правильного суррогатного ключа вам нужно обернуть каждый столбец в coalesce перед хэшированием с помощью функции md5:
md5 ( concat ( coalesce(column1, '_this_used_to_be_null_'), coalesce(column2, '_this_used_to_be_null_') ) )
Вы заметите, что вместо объединения в пустую строку используется длинная строка замены. Не имеет значения, что это за строка, пока она остается последовательной в вашем проекте и не появляется в ваших данных. Когда вы объединяете в пустую строку, вы не можете различить null и '', что иногда имеет семантическое значение. Сравните результаты этих суррогатных ключей:
Документация по функции concat в Databricks не упоминает конкретно о возвращении null для concat, если один из столбцов равен null, но я полагаю, что это подразумевается под The result type matches the argument types.
Вы также можете разделить свои столбцы с помощью вертикальных черт ( || ) вместо использования функции concat, но я обычно избегаю вертикальных черт (одна запятая > две вертикальные черты).
Суррогатные ключи в Postgres
Функция concat в Postgres игнорирует null, что избавляет вас от необходимости оборачивать каждый столбец в функцию coalesce, чтобы по умолчанию null заменялся на другое значение (но это имеет тот же недостаток, показанный в таблице выше, где вы можете получить тот же ключ из разных входных данных).
Если вы использовали || вместо concat, один null столбец вызовет возврат null для всего выражения, нарушая ваше объединение.
Таким образом, в обычном PostgreSQL вы бы использовали:
md5 ( concat (column1, column2) )
Проблема значения null в суррогатных ключах
Основная проблема при создании суррогатных ключей возникает, когда вы пытаетесь объединить строку, в которой одно или несколько значений столбцов равны null. Если любое значение равно null, то часто вся объединенная строка возвращается как null - это не подходит!
with
example_ids as (
select
123 as user_id,
123 as product_id
union all
select
123 as user_id,
null as product_id
union all
select
null as user_id,
123 as product_id
)
select
*,
concat(user_id, product_id) as _surrogate_key
from example_ids
вывод:
| Loading table... |
Вы можете обойти это, обернув каждый из ваших столбцов в функцию coalesce, чтобы по умолчанию null заменялся на альтернативное значение, что довольно утомительно. Вы также можете столкнуться с проблемами, если поля имеют разные типы данных (строка против числового), поэтому иногда вам нужно также приводить типы.
...
select
*,
concat(
coalesce(cast(user_id as string), '_this_used_to_be_null_'),
coalesce(cast(product_id as string), '_this_used_to_be_null_')
) as _surrogate_key
from example_ids
вывод:
| Loading table... |
Гораздо лучше! Но давайте добавим еще одну строку в наш набор данных:
with
example_ids as (
select
123 as user_id,
123 as product_id
union all
select
123 as user_id,
null as product_id
union all
select
null as user_id,
123 as product_id
union all
select 1231 as user_id,
23 as product_id
)
select
*,
concat(
coalesce(cast(user_id as string), '_this_used_to_be_null_'),
coalesce(cast(product_id as string), '_this_used_to_be_null_')
) as _surrogate_key
from example_ids
вывод:
| Loading table... |
На первый взгляд, это выглядит как работающий вариант, но на самом деле есть два идентичных ключа: 123123.
Чтобы исправить это, вам нужно добавить разделитель между полями, которые вы хотите объединить.
...
select
*,
concat(
coalesce(cast(user_id as string), ''),
'|',
coalesce(cast(product_id as string), '')
) as _surrogate_key
from example_ids
вывод:
| Loading table... |
Давайте посмотрим, как генерация суррогатных ключей выглядит на практике в различных хранилищах данных, и как вы можете использовать один простой макрос dbt (dbt_utils.generate_surrogate_key), чтобы абстрагировать проблему значения null.
Макрос surrogate_key на помощь
Благодаря удобной функции generate_surrogate_key в пакете dbt_utils, вы можете освободить себя от необходимости оборачивать свои столбцы в coalesce каждый раз, когда хотите сгенерировать суррогатный ключ.
Формирование суррогатных ключей с помощью этого макроса имеет преимущество элегантной + DRY обработки null.
Вместо того чтобы оборачивать свои столбцы в функцию coalesce при их объединении, макрос проходит по вашим столбцам и оборачивает их за вас, чтобы вы могли избежать повторения.
Когда вы вызываете {{ dbt_utils.generate_surrogate_key(['field_a', 'field_b'[,...]]) }}, за кулисами dbt компилирует SQL за вас, проходя по каждому полю и генерируя нужное количество выражений coalesce с приведением типов:
coalesce(cast(" ~ field ~ " as " ~ dbt.type_string() ~ "), '_dbt_utils_surrogate_key_null_')
и с условной логикой, добавляя разделитель между полями:
{%- if not loop.last %}
{%- set _ = fields.append("'-'") -%}
{%- endif -%}
Что это значит на практике?
Ну, вам просто не нужно слишком много думать о своих суррогатных ключах. В любом хранилище данных, с null или без null, это просто работает. Потому что, честно говоря, кто хочет тратить больше времени, чем нужно, думая о суррогатных ключах?
Comments