Репозиторий с открытым исходным кодом для этого инструмента можно найти здесь.

При построении хранилища данных или витрины вы часто хотите определять производные таблицы в терминах операторов SELECT, желательно без необходимости явного DDL (например, CREATE TABLE), но без отказа от возможности использовать первичные ключи, внешние ключи или важные, специфичные для базы данных. такие функции, как DISTSTYLE, DISTKEY и SORTKEY в Amazon Redshift. Мы создали небольшой инструмент в Ro, который упрощает выполнение этой задачи без необходимости использования более крупного инструмента для управления конвейером данных.

Фон

В Ro, наш бизнес и операции быстро растут как с точки зрения масштаба, так и сложности. В конце октября 2017 года мы запустили единый бренд (Roman) для лечения одного состояния (эректильная дисфункция), но за последние несколько недель мы добавили новый бренд (Zero, для отказа от курения) и несколько состояний под Roman ( выпадение волос, герпес, генитальный герпес и преждевременная эякуляция).

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

Для контекста, наше хранилище данных находится в Amazon Redshift, и наше решение в настоящее время зависит от Redshift (хотя было бы несложно адаптировать его к большинству других баз данных).

Обычным способом

Если вы определяете производные таблицы с помощью стандартных операторов CREATE TABLE и SELECT, вам нужно будет добавить новые столбцы в обоих местах. Если вы пропустите CREATE TABLE и вместо этого используете SELECT INTO (или его моральный эквивалент, например CREATE TABLE AS), вы можете изменить одно место, но теперь у вас другая проблема - вы не можете определить первичный или внешний ключи, или воспользуйтесь преимуществами функций Redshift DISTSTYLE, DISTKEY и SORTKEY.

Альтернатива

Мы создали простой способ получить преимущества SELECT INTO без потери возможности указывать ключи, которые мы разместили на GitHub здесь.

В README обсуждается, как его использовать, а также есть пара заметных ограничений.

Стратегия реализации состоит в том, чтобы сначала запустить вариант запроса с SELECT INTO и LIMIT 10, создав временную таблицу с небольшой выборкой строк. PostgreSQL и Redshift выбирают соответствующие типы для каждого столбца, к которым мы затем можем получить доступ через pg_table_def [¹]. Мы используем это, чтобы автоматически генерировать соответствующий оператор CREATE TABLE, выполнять его, а затем INSERT INTO конечную таблицу.

Тот факт, что мы генерируем оператор CREATE TABLE, означает, что нам нужно куда-то поместить определения ключей, поэтому вы можете дополнительно указать первичный ключ, внешний ключ, diststyle, distkey и sortkey в файле конфигурации YAML. README дает пример того, как это выглядит на практике.

Итак, теперь мы можем съесть свой торт и съесть его - простота SELECT INTO плюс возможность указывать ключи, которые в противном случае мы потеряли бы.

Другие альтернативы

В PostgreSQL для этого можно использовать материализованные представления. Их можно воссоздавать и обновлять по мере необходимости, и они поддерживают индексы (включая уникальные индексы), но не внешние ключи. К сожалению, в Redshift они не поддерживаются.

Для этого вы также можете использовать такой инструмент, как dbt. Однако что-то вроде dbt делает гораздо больше, чем это единственное [²], и может быть полезно сохранять простоту.

[1]: Или информация_schema.columns, которая также доступна в PostgreSQL, но несколько менее удобна в использовании.
[2]: В ней есть язык шаблонов, макросы, планировщик, преобразователь зависимостей ...