ストアド・プロシージャはデータベースに対する処理をいくつかまとめておき、この処理をデータベースに保存し、処理内容を再利用することを簡単にすることができる機能です。
Alteryxを導入する場合、データの処理自体はAlteryx側で行うようになると思いますが、以前から利用していたストアドプロシージャによる処理を継続的に行いたい、という要望もあるかと思います。
ストアドプロシージャのAlteryx内での扱い
Alteryxは多くのデータベースに対応していますが、ストアドプロシージャの呼び出しを直接できるデータベースシステムは限られています。以下は、ナレッジベースに記載されているリストです。
- Microsoft SQL Server
- Oracle
- SAP HANA
実際にMicrosoft SQL Serverでストアド・プロシージャがどのように利用できるか見てみましょう。まず、以下のようにストアド・プロシージャが設定されているとします（testdb内のuspStudentDeleteというプロシージャです）。
これをデータ入力ツールで開いてみます。
このように、ストアドプロシージャタブにて選択することが可能です。
つまり、正式サポートされているデータベースシステムであれば、単純に選択できる、ということになります。
PostgreSQLでストアドプロシージャを呼び出す
PostgreSQLのストアド・プロシージャの呼び出しは、Alteryxは正式にサポートしていません。
しかしながら、なんとかこれを実現する方法があります。
データ入力ツールには「SQLステートメント事前実行」という機能があります。
これは、SQLを実行する前に実行するSQL構文を設定することができ、実際のSQLを実行する前にここで設定されているものが実行されます。
そのため、SELECTする前にここでストアド・プロシージャを実行することが可能です。それでは実際にやってみましょう。
残念ながら筆者はPostgreSQLのストアド・プロシージャを作ったことがないので、こちらのサイトにあるプロシージャを作成してみました。pdAdmin4で見ると以下の通り設定されています。
ストアド・プロシージャを実行する時の構文は以下のとおりです（これは、idが1からidが2のレコードに対して1000ドルが移動される、という内容になります）。
call transfer(1,2,1000);これを、データ入力ツールのSQLステートメント事前実行に設定します。
「SQLステートメント事前実行」の右端の三点ボタンをクリックすると、「SQLステートメント事前実行とストアドプロシージャを設定」ダイアログが出現します。ここで、「SQLエディター」タブをクリックし、上のストアドプロシージャを設定します。
これで「OK」ボタンをクリックすると、設定されます。
なお、「ツール設定でPreSQLを実行する」にはデフォルトでチェックが入っていますが、ここにチェックが入っていると意図しないタイミングでPreSQLが実行されるので注意してください（ここにチェックが入っていると、データ入力ツールの設定画面を開く度にPreSQLが実行されます）。基本的に、PreSQLを動作させたいのはワークフローが実行する場合に限られると思うので、チェックは外しておきましょう。
なお、最終的に実行するクエリは、「SELECT * FROM accounts;」という構文ですので、プロシージャが実行された後に、そのテーブルのデータを読み込む形になっています。
実際に実行してみましょう
それでは、初期状態はこのようになっています。
これに対して、PreSQLを設定したデータ入力ツールを実行してみましょう。実行後のログを見ると、なんかうまくいってそうですね！
結果を見てみましょう。
無事にidが1のBobからidが2のAliceに1000ドル送金されています。
なお、単にストアドプロシージャを実行したいだけで、結果は見る必要がない、ということであれば、処理的にテーブルのカウントを取るようなSQLに変えてあげたほうが良いでしょう。例えば以下のようなSQLをSQLエディターで設定しておきます。
2024/3/9追記：
DBによってはCount(*)は遅いという問題があるので、パフォーマンスが問題になるようなケースではSQLのチューニングが必要になります。カウントが必要でなければ、いちばん簡単なのはlimit句でレコード数をしぼることです。例えば、以下のようなSQLも利用可能です。
select 1 as "dummy" from accounts Limit 1;カウントが欲しい場合は、PostgreSQLでは以下のような構文で高速に動作できました。
select count(1) from accounts;Count(*)でも遅くないDBもあるので、お使いのDBに応じてチューニングしてください。
まとめ
- PostgreSQLでストアドプロシージャを呼び出す方法をご紹介しました
- ストアド・プロシージャに正式対応していないデータベースでは、「SQLステートメント事前実行」を使う必要があります
- Microsoft SQL Server、Oracle、SAP HANAでは正式サポートされているので、実行したいストアド・プロシージャを選択するだけです
次回
次回もDB系のお話です。
コメント