アライアンス事業部 エンジニアグループ モダンデータスタック(MDS)チームのしんやです。
以前執筆、公開した下記のエントリは、dbtでSELECT文以外のDML/DDLを扱う時に「こういう風にSELECT文に置き換えると良いですよ」という案内、紹介をするものでした。
この中で「ストアドプロシージャの移行」に関するトピックもあったのですが、このエントリ内では情報源の紹介のみに留まる形としており、詳細な内容の紹介はしていませんでした。
ですのでその部分の紹介を当エントリで進めていこうと思います。
目次
なぜストアド・プロシージャの代わりにdbtモデルを使うのか
まぁ、これは単に『ストアドプロシージャをそのまま動かすことはdbtでは対応していない、けど動かしたいんだ』という要件があるからに他ならないのですが(←身も蓋もない)、dbt Labs社ではストアドプロシージャのコードをdbtに移行する、移し変える、リファクタリングするために多くのアナリティクスチームと協働しています。その成果がこのブログエントリ(の元ネタ)でもあります。多くはデータ変換へのアプローチを近代化するための先行投資が大きすぎると考えていますが、長期的にはそうではないことがわかります。
あるdbt Cloudのユーザーの例を挙げてみます。ストアドプロシージャをdbtに移行することで以下のような結果を得ることが出来ました。
- 理由: アップタイムの改善
- dbtに移行する前、チームはパイプラインのリフレッシュに1日あたり6～8時間を費やしており、ダウンタイム中はデータウェアハウスへの投資が実質的に無価値になっていた。
- 移行後、稼働率は65%から99.9%に向上した。これは、データ利用者のパイプラインに対する信頼性にも劇的な影響を与えた。
- 理由: 新しいユースケースへの取り組みのため
- 移行を経て、チームは新しいミッション・クリティカルなユースケースをサポートできるようになった。
- これは、チームがこれまでと同じ手法を使い続けていたら出来なかったことでもある。
ストアドプロシージャの問題点とは
ストアドプロシージャを使うことで発生する問題としては、データパイプラインに期待される現代的な要素を考慮すると見えてくるものがあります。大きく以下の3つです。
- ストアドプロシージャはデータフローを文書化することに適していない
- ストアドプロシージャはテストがしづらい(あまりテスト可能な状態ではない)
- ストアドプロシージャの中間ステップのロジックが色々な箇所でコピーされている現状がある(開発チームのコードベースが余分に肥大化し、チーム効率を低下させる)
代替案としてdbtを検討する理由
代替案としてdbtを活用することで、自己文書化され、テスト可能で、開発中のコードの再利用を促進するアプローチを取ることが出来るようになります。
dbtで作業する上で最も重要な要素の1つは、データパイプラインにアプローチする際にモジュール性を採用することです。
dbtでは、データパイプラインによって管理される各ビジネスオブジェクトは個別のモデルで定義されます（受注データを考えてみてください）。これらのモデルは、生データから消費準備の整ったデータへの進行を反映するために、柔軟にレイヤーにグループ化されます。このようにして再利用可能なコンポーネントを作成することで、データの重複や開発チーム間の混乱を避けることができます。
dbtでは、以下のようによりシンプルで透明性の高いデータパイプラインの構築を実現可能です。
gitリポジトリサービスと連携することで得られるバージョン管理との統合は、dbtを利用する上で得られる大きな付加価値、メリットです。このメリットを最大限享受、活用することで、dbtは変換パイプラインへの変更を他のアプローチよりもはるかに速く統合し、テストすることができます。
ストアドプロシージャでは、その変更を長期にわたって追跡するという考えなしにコードに変更が加えられることが往々にしてありますが、これは、ストアドプロシージャそのものの問題というよりは、チームが選択したワークフローの問題でもあります。またレガシーなツールが分析作業を必要以上に難しくしていることも言えるかもしれません。
ストアドプロシージャからdbtへの移行手法
ここからは実際の移行手順についてその詳細を見ていきます。
T-SQL、PL/SQL、BTEQ、またはその他のSQL方言のいずれを使用している場合でも、ストアドプロシージャアプローチからdbtアプローチへの移行プロセスは、一般的に同様のステップに分けることができます。
『SQLの移行』という観点では下記ブログエントリも参考になります。是非ご参照ください。
ステップ0: dbtの仕組みを少し理解する
dbtを初めて触ります！という場合であれば、リファクタリングを検討する前にまずはdbtの初歩的な部分を把握する、実際に触ってみるとというところから始めてください。
DevelopersIOでも入門的位置付けのエントリを幾つか公開しています。宜しければご参照頂けますと幸いです。
- 個人的dbt概要まとめ2023 – dbtで何が出来るのか、dbtを使うメリットなどを紹介 #dbt | DevelopersIO
- dbt Coreとdbt Cloudの関係性・違いなどについてまとめてみた #dbt | DevelopersIO
- dbt Cloudにおけるプランの違いについて(Developer, Team, Enterprise) #dbt | DevelopersIO
- Snowflake and dbt Cloudハンズオン実践「Accelerating Data Teams with Snowflake and dbt Cloud Hands On Lab」 の記事一覧 | DevelopersIO
- [レポート] クラスメソッドxメソドロジック共催：イチから始めるデータ活用！『dbt編』で登壇しました。 #dbt | DevelopersIO
- dbtベストプラクティスまとめ の記事一覧 | DevelopersIO
ステップ1: dbtとストアドプロシージャの違いを理解する
過去にストアドプロシージャを書いた事がある場合、『行ごとに進行するステートフルなプロセス』で進行するという風に考えていることでしょう。最初にテーブルを作成し、DMLを使用してデータの挿入、更新、削除を行い、変換の過程で同じベーステーブルに操作を適用し続ける...といった形です。
一方、dbtでは、SELECT文を使用してテーブルを構成するデータセットを記述することで、データセットを宣言的に管理します。
この方法で定義されたテーブル（またはビュー）は、変換作業の各段階または単位を表し、各ステートメントの実行順序を決定するために、DAG（Directed Acyclic Graph）に組み立てられます。これは手続き型変換と同じ目的を達成するものではありますが、1つのデータセットに多くの操作を適用するのではなく、よりモジュール化されたアプローチを取る形となります。
これにより、変換パイプラインの推論、文書化、テストが非常に簡単になります。
ステップ2: ストアドプロシージャをdbtコードに変換する方法を計画
一般的には、以下に紹介するレシピが効果的な変換プロセスになり得ることが分かっています。
- 『ストアドプロシージャのデータフロー』をマップする(一旦フロートして書き出す)
- その中から『生のソースデータ』を特定する
- 「データ型のキャスト」や「名前の変更」など、初期データ変換のための『ステージングレイヤー』を未加工データソースをカバーする形で作成
- ハードコードされたテーブル参照を、dbtのsource()関数やref()関数で置き換える。これにより、処理が然るべき正しい順序で実行され、更に自動ドキュメンテーションが可能となります。
- ストアドプロシージャのINSERTSとUPDATESをdbtモデルのSELECTにマッピング
- ストアドプロシージャのDELETESをdbtモデルのWHEREフィルターにマップ
- 必要であれば、ストアドプロシージャに渡される引数のように、実行時に動的に値を代入するためにdbtで変数を使用
- 上記プロセスを繰り返し、dbt DAGをさらに洗練(リファクタリング)させる。
- 通常は、ストアドプロシージャと最終的なdbtモデルからの出力が同等になった時点で、リファクタリングは停めて良いと思います。
時には、あまりに複雑なコードに直面し、エンドユーザーが何をしているのか正確に理解できないことがあります。このような場合、元のストアドプロシージャに埋め込まれているプロセスのマッピングを実行することができない場合があります。このような場合は後述『結果の監査』セクションをご参照ください。
ステップ3: 計画を実行に移す
ここではある作業の数ステップを、オリジナルのストアドプロシージャのアプローチからdbtを使った新しいアプローチにマッピングして、実際にどのように見えるかを紹介していきます。
ストアドプロシージャのアプローチ（SQL Serverのコードを使用した例)
まずはストアドプロシージャのアプローチから。
- 未加工のテーブルからデータを選択するテンポラリテーブルを定義し、そこにデータを挿入。
IF OBJECT_ID('tempdb..#temp_orders') IS NOT NULL DROP TABLE #temp_orders
SELECT messageid
,orderid
,sk_id
,client
FROM some_raw_table
WHERE . . .
INTO #temp_orders- 2つ目の未加工テーブルから別のINSERTを実行。
INSERT INTO #temp_orders(messageid,orderid,sk_id, client)
SELECT messageid
,orderid
FROM another_raw_table
WHERE . . .
INTO #temp_orders- tempテーブルでDELETEを実行し、本番環境にあるテストデータを削除。
DELETE tmp
FROM #temp_orders AS tmp
INNER JOIN
criteria_table cwo WITH (NOLOCK)
ON tmp.orderid = cwo.orderid
WHERE ISNULL(tmp.is_test_record,'false') = 'true'このプロセスはかなりの時間を要し、作業時間も続くことが多くなるでしょう。要約するとこのアプローチの問題点は以下の通りとなります。
- a）コードが非常に長く、b）自動的に文書化されないため、データフローをトレースするのが非常に難しくなる。
- プロセスはステートフルである。私たちの例の#temp_ordersテーブルはプロセスを通して変化する。
- テストが容易ではない。
dbtのアプローチ
次いで同じ事をdbtからアプローチした例です。
- 未加工のソース・テーブルを特定し、上記の各INSERTステートメントを別々のdbtモデルにマッピングし、自動生成されたWHEREステートメントを含めて、上記の3番目のステップからテスト・レコードを除外。
— orders_staging_model_a.sql
{{
config(
materialized='view'
)
}}
with raw_data as (
select *
from {{ source('raw', 'some_raw_table')}}
where is_test_record = false
),
cleaned as (
select messageid,
orderid::int as orderid,
sk_id,
case when client_name in ['a', 'b', 'c'] then clientid else -1 end
from raw_data
)
select * from cleaned- コードが適切な粒度で動作していることを確認するために、モデルのテストを作成。
version: 2
models:
- name: stg_orders
columns:
- name: orderid
tests:
- unique
- not_null- モデルを結合。
{{
config(
materialized='table'
)
}}
with a as ( select * from {{ ref('stg_orders_a') }} ),
b as (select * from {{ ref('stg_orders_b') }} ),
unioned as (
select * from a
union all
select * from b
)
select * from unionedsource()やref()を活用したことにより、またCTE(Common Table Expression/共通テーブル式)を活用したことにより、ここまでの内容はDAGで以下のように表示されます。
ステップ4: 結果の確認・監査
技術的なプロセスに変更を加える場合、その結果をチェックすることが不可欠です。
幸いなことに、dbt Labsでは、まさにこのようなケースを想定してaudit_helperパッケージを用意しています。
audit_helperを使用することで、行数の比較や、レガシーなストアドプロシージャで更新されたテーブルとdbtパイプラインの結果で更新されたテーブルの行ごとの検証などの処理を実行し、両者が全く同じであること（または合理的な偏差%以内であること）を確認することができます。
こうすることで、新しいdbtパイプラインが、以前存在した変換パイプラインと同じ目標を達成しているという確信を持つことができます。
要約
ストアドプロシージャを使用する際のペインポイント（主にトレーサビリティとデータテストの欠如）をいくつか取り上げ、dbtアプローチがどのように役立つかを説明しました。よく文書化され、モジュール化され、テスト可能なコードは、エンジニアとビジネスユーザーを幸せにします。また、パイプラインの信頼性を高め、更新を容易にすることで、時間とコストの節約にもつながります。
時間をかけて、このアプローチは、扱いにくいプロセスの上にコードを積み重ね続けるよりも、はるかに拡張性があります。また、自動的に文書化され、テストを使用することで、パイプラインが時間の経過による変更に強いことを保証します。既存のストアドプロシージャからdbtデータパイプラインにデータフローをマッピングし、前と同じアウトプットが得られるまで繰り返します。
まとめ
という訳で、dbtにおける「ストアドプロシージャの移行」に関する推奨手順の紹介でした。
基本的な部分の流れは把握出来ましたが、やはりここは実践で実際にやってみるのが一番かと思います。dbtにおけるSELECT文以外の処理のSELECT文への移行に関しては下記エントリでも採り上げていますので、こちらも合わせて参考にしてみてください。
2
