Генерация суррогатных ключей в различных хранилищах данных

Почему первичные ключи важны

Мы все знаем одно из самых фундаментальных правил в данных: каждая должна иметь . Первичные ключи важны по многим причинам:

• Они гарантируют, что в вашей таблице нет дублирующихся строк
• Они помогают устанавливать связи с другими таблицами
• Они позволяют быстро определить таблицы (например, таблица customers с PK customer_id имеет одну строку на каждого клиента)
• Вы можете тестировать их в dbt, чтобы убедиться, что ваши данные полные и уникальные

Если вам повезет, ваши данные уже будут иметь уникальный первичный ключ. Возможно, это id, сгенерированный вашими внутренними системами, или вы получаете данные из стороннего источника, который генерирует id для вас.

Однако иногда вы оказываетесь в ситуации, когда у вас нет аккуратного первичного ключа.

Например, возможно, вы объединили своих пользователей с набором функций, которые они используют в вашем продукте, так что ваши данные должны быть уникальными на основе user_id + product_id.

Вопрос в том, как в этой ситуации установить первичный ключ? Познакомьтесь с суррогатным ключом.

Что такое суррогатный ключ?

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

Изучение того, когда использовать суррогатные ключи и как внедрять их в ваш проект, является критическим навыком для любого аналитика.

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

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

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

1. Объедините все поля, необходимые для создания уникальной строки (например, user_id и product_id)
2. Примените функцию для создания криптографического хэша (обычно с использованием функции 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

вывод:

USER_ID	PRODUCT_ID	_SURROGATE_KEY
123	123	123123
123	null	null
null	123	null

Вы можете обойти это, обернув каждый из ваших столбцов в функцию 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

вывод:

USER_ID	PRODUCT_ID	_SURROGATE_KEY
123	123	123123
123	null	123_this_used_to_be_null_
null	123	_this_used_to_be_null_123

Гораздо лучше! Но давайте добавим еще одну строку в наш набор данных:

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

вывод:

USER_ID	PRODUCT_ID	_SURROGATE_KEY
123	123	123123
123	null	123_this_used_to_be_null_
null	123	_this_used_to_be_null_123
1231	23	123123

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

Чтобы исправить это, вам нужно добавить разделитель между полями, которые вы хотите объединить.

...
select
  *,
  concat(
    coalesce(cast(user_id as string), ''),
    '|',
    coalesce(cast(product_id as string), '')
    ) as _surrogate_key
from example_ids

вывод:

USER_ID	PRODUCT_ID	_SURROGATE_KEY
123	123	123|123
123	null	123|_this_used_to_be_null_
null	123	_this_used_to_be_null_|123
1231	23	1231|23

Давайте посмотрим, как генерация суррогатных ключей выглядит на практике в различных хранилищах данных, и как вы можете использовать один простой макрос dbt (dbt_utils.generate_surrogate_key), чтобы абстрагировать проблему значения null.

Макрос surrogate_key на помощь

Благодаря удобной функции generate_surrogate_key в пакете dbt_utils, вы можете освободить себя от необходимости оборачивать свои столбцы в coalesce каждый раз, когда хотите сгенерировать суррогатный ключ.

Формирование суррогатных ключей с помощью этого макроса имеет преимущество элегантной + обработки 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, это просто работает. Потому что, честно говоря, кто хочет тратить больше времени, чем нужно, думая о суррогатных ключах?