🔑

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

2022/12/23に公開約5,200字

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 replicationXMIN 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大事。

Discussion

ログインするとコメントできます