RDBMS(PostgreSQL)のデータを、DWH(Snowflake)に入れる時に、再考したPrimary keyの重要性

PostgreSQL Advent Calendar 2022 の25日目の記事です。

RDBMS（以下、データベース）のデータをデータウェアハウスに入れたいというニーズは、データ統合を進めていくと発生すると思います。筆者は、実際にPostgreSQLのデータを、Snowflakeに取り込みを行いました。その際に、テーブルにPrimary keyがついておらず困りました。なぜPrimary keyが必要なのかについて、自分なりの見解をまとめてみました。

※現在は、Primary keyをつける方針になり、事なきを得ました。

解決したかったこと

社内に存在しているデータベース。今回だとPostgreSQLに入ってるデータを、Snowflakeにさくっと入れたい。
なぜ、データベースのデータが必要だったかというと、 でいうところの、ディメンションが、データベースに入ってたからです。
ディメンションがないデータウェアハウスでは、欲しい情報が取れなさすぎるため、取り込みたいニーズが高まりました。

要件

ここでは、細かいビジネス背景については、説明しませんが、ざっくり以下のような要件がありました。

• 同期にリアルタイム性は必要ではない。
  • 例えば、1分で同期とかは必要ない。
• 1時間に1回くらい同期できてれば問題ない。
  • 1日1回だと遅い。
• データの順序は重要ではない。
  • 最新の状態と、過去の履歴が1時間ごとに取れれば良い。
• 今後も色んなデータベースを取り込みたい。
  • PostgreSQLだけではなく、MySQLも。

作ったもの

構成要素について

当記事は、PostgreSQLのアドベントカレンダーに投稿しているので、PostgreSQL以外の構成要素については、知らない方も居る可能性があるので、軽く説明を書いておきます。（分かっている人は、飛ばしてもらっても構いません）

Snowflake

Snowflakeとは、公式の説明は以下の通りです。

Snowflake独自のマルチクラスタ共有データアーキテクチャはクラウド向けに一から構築されており、今日の組織が必要とする性能、拡張性、弾力性、同時実行性を提供します。
Snowflakeは単一の統合プラットフォームであり、サービスとして提供されます。ストレージ、コンピュート、グローバルサービスの3層からなり、これらの層は物理的には分離されているものの、論理的には統合されています。データワークロードを別々に拡張できることから、データウェアハウジング、データレイク、データエンジニアリング、データサイエンス、最新のデータ共有、およびデータアプリケーションの開発に最適なプラットフォームとなっています。

Snowflakeについて

元々は、データウェアハウスに、RedshiftとBigQueryを併用していたのですが、クエリの瞬間馬力とAWSにあるデータのロードのしやすさなどを考慮した結果、Snowflakeにデータ系の業務を移行していってます。
ぼくのかんがえる最高のデータ分析基盤 で、全体像や背景などについて、紹介をしているので、興味あればどうぞ。

Fivetran

Fivetranとは、公式の説明は以下の通りです。

Fivetran is the automated data movement platform moving data out of, into, and across your cloud data platforms. We’ve automated the most time-consuming parts of the ELT process – from automated extracts to schema drift handling to transformations -- so your data engineers can focus on higher-impact projects with total pipeline peace of mind. With 99.9% uptime and self-healing pipelines, you can leave behind the maintenance of DIY pipelines and have confidence that your data will always be flowing to accelerate data-driven decisions.

Fivetran About

簡単に言うと、様々なデータ置き場から、特定の場所に、例えば、今回でいうとSnowflakeにロードする非常にストレスがかかるETL業務から開放してくれます。

今回の採用した理由の主な理由としては、データウェアハウスにロードする部分を、自前で組むのが大変になるのが見えていたので、便利な仕組みがあるなら、乗っかってしまおうです。
実は社内には、PostgreSQL以外にも、MySQLもありますし、それぞれのデータベースのためを、仕組みを構築していって、それをメンテナンスする自信がありませんでした・・・。

ぼくのかんがえる最高のデータ分析基盤 で、全体像や背景などについて、紹介をしているので、興味あればどうぞ。

踏み台

Network Load Balancer(以下 NLB) + Auto Scaling Group(以下、ASG) + EC2

ロードしたい対象であるPostgreSQLは、プライベートネットワークに存在しているため。
Fivetranから参照するための踏み台が必要でした。
最初は、インスタンスを作って終わらせようとしましたが、過去に踏み台サーバーが落ちた時に、復旧手順が仕組み化されておらず、苦労した思い出があるので、ASGを作ってインスタンス自体の復旧を自動化しました。

Fivetranからの接続方法について

Fivetranには、いくつかの接続オプションがありますが、プランによって使える範囲が変わります。今回は要件的に、Standard planで十分でした。
ちなみに、Business Criticalプランであれば、PrivateLinkで接続する方法も用意されています。

FIvetran plan

やってみてどうだった？

Fivetranのお陰もあり、ロード自体は非常にスムーズに終わりました。
実は今回困ったのは、この仕組みを用意して顕在化したPrimary keyなしテーブルでした。

Fivetranは、同期方法をいくつかあり、私が採用したのは、Fivetran Teleport Sync です。
この方法は、Fivetran独自の仕組みで、他の Logical replication や XMIN system column に比べて、設定が非常にシンプルで、低コストです。(詳しい説明は、ドキュメントを参照してください。)

Teleport Syncの制約に以下のような文言があります。

Fivetran Teleport Sync does not support tables without primary keys. It can only sync tables with integer or string primary keys.

Tables without a Primary key

私としては、ビジネスプロセスを表現したテーブルには、必ずPrimary keyがついてるものだと考えていたため、この制約に関して、設定時に特に気にせず採用をしました。

Primary Keyをつける方向に倒したかった理由

同期できないテーブルの制約を見ると、本来ならPrimary keyに設定されてそうなところに、Unique + Not null制約がついていました。一方で、Unique + Not null制約が、2つのカラムについているケースもあったので、こっちは複合Primary keyっぽいやつか？と思いきや、実は片方のカラムだけで、一意になることを期待しているというケースもありました。

こういうテーブルを、データウェアハウスに入れると、機械的に、一意性担保に使うカラムがどれか判断がつかなくなるため、「どのカラムで一意性を担保すればいいんだ？」となるし、そもそも本当に一意に特定できるのかすら怪しくなります。そして、アプリケーションコードまで見に行って、確認する必要が出てくる。
こういった積み重ねが、データに関する仕事を難しくしていくため、暗黙的に一意性を期待してるなら、Primary Keyをつけて！という話をしました。

なぜPrimary keyが大事なのか

一意性が担保できるから！だと、説明が雑なので、私なりの重要性について書いてみます。

まず、物理テーブルを作っていく前に、論理テーブル設計を進めていくと、扱うビジネスプロセスを、一意に特定できる必要があると思います。逆に一意に特定できないビジネスプロセスとは何なんでしょう？例えば、商品を販売するというビジネスプロセスが、一意に特定できないって、どういうこと？って感じですよね。
仮に、ビジネスプロセスを一意に特定できないなら、論理テーブル設計をやり直す必要があると考えています。

※時系列テーブルとかは、例外です。

Unique + Not null != Primary key

では、なぜUnique + Not nullではだめなのか？一意っぽいことできるじゃん。という問いです。
結論から言うと、一意性を担保してるわけではなく、Uniqueは、Unique値が入ってることを担保していて、Not nullは、nullでないことを担保してるだけで、一意性を担保するものではありません。

一方で、Primary keyは、レコードを一意に特定するための制約です。これは、裏側の仕組みにも大きく影響します。例えば、内部的にクラスタインデックスが作成され、高速に特定のレコードを探し出すこともできますし、今回のFivetranのような機械的なデータ連携時にも役立ちますし、何より人間が気をつけなくても、ビジネスプロセスの一意性の破綻を防げます。

サロゲートキーをつけろではない。

Primary keyがつけとけば、良いわけではない。

当初、議論を難しくしていたのは、SQLアンチパターンのIDリクワイアドとごっちゃになっていたことです。
私も、「とりあえずID」は、反対ですし、どう設定すべきか分からないなら、それは、ちゃんとモデリングしてくださいと思っています。

まとめ

• 対話大事。
• Primary key大事。