Node-RED でRDBを利用したい場面、結構遭遇することがあると思います。
RDBとして IBM Db2 on Cloud をNode-REDから使用する際に、
いままで node-red-nodes-cf-sqldb-dashdb を利用させてもらっていたのですが、enebular でパレットの管理でノードをインストールするときにエラーになってしまい…
IBM Db2 on Cloud を操作するAPIが公開されているとのことなので、この際フローを自分で作ってしまおうと思い実践してみました。
パレットの管理でノード追加せずに、初期状態のenebularで動作確認することができます。
使い回しできるようにサブフローとして作成します。
IBM Db2 on Cloud REST API
公式リファレンスを頼りにフローを作成していきます。
APIの呼び出し手順としては以下の通りになります。
- IDパスワード認証と各種API呼び出しのためのトークンを取得API (authenticate)
- SQLを実行するAPI (runsql)
- SQLの結果を取得するAPI (fetchsqlresults)
フロー作成
全体フロー
フローの全体像は以下のようになります。
サブフローのインプットパラメータは以下の通りです。
- msg.payload : SQL文
サブフローのアウトプットパラメータは以下の通りです。
- msg.payload : SQL の実行結果APIのレスポンス
大きく分けて４つのブロックに分かれます
- Token取得部分
- SQL実行部分
- SQL結果取得部分
- エラーチェック部分
http request ノード共通設定
フローで利用する http request ノードは共通で下記の設定にします。
- メソッド： 「-msg.methodに定義-」
- URL: 「空」
- 出力形式: 「JSONオブジェクト」
Token取得
使用ノード
- Function
- http request
- Switch
function
認証APIにアクセスするためのリクエストパラメータをコーディングします
function
msg.method = "POST";
msg.url = "https://[Db2 on Cloud の URL]";
msg.url += "/dbapi/v4/auth/tokens";
msg.headers = {
"Content-Type" : "application/json",
"x-deployment-id" : encodeURIComponent("crn:v1:bluemix:public:dashdb-for-transactions:us-south:a/---:***::")
};
msg.payload = {
"userid" : "[ユーザID]",
"password" : "[パスワード]",
};
msg.rejectUnauthorized = false; //オレオレ証明を許可
return msg;
Switch
Payload にトークンが格納されています。一度取得したトークンはしばらく使い回しができるので、何度もトークンを取得しなくてもいいように グローバルコンテキストに格納しておきます。
- 代入先： global.token
- 代入元： msg.payload.token
SQL実行(runsql)
SQLを実行するAPIにアクセスするためのリクエストパラメータをコーディングします。
使用ノード
- Function
- http request
function
function
msg.method = "POST";
msg.url = "https://[Db2 on Cloud の URL]";
msg.url += "/dbapi/v4/sql_jobs";
msg.headers = {
"authorization" : "Bearer " + global.get("token"),
"Content-Type" : "application/json",
"x-deployment-id" : encodeURIComponent("crn:v1:bluemix:public:dashdb-for-transactions:us-south:a/---:***::") )
};
msg.payload = {
"commands" : "[実行したいSQL]", // 実行したいSQLを指定
"limit" : 300,
"separator" : ";",
"stop_on_error" : "no",
};
msg.rejectUnauthorized = false; //オレオレを許可
return msg;
SQL結果取得(fetchsqlresults)
実行したSQLの結果を取得するAPIにアクセスするためのリクエストパラメータをコーディングします。
使用ノード
- Function
- http request
- Switch
- delay
Function
function
msg.method = "GET";
msg.url = "https://[Db2 on Cloud の URL]";
msg.url += "/dbapi/v4/sql_jobs/" + msg.payload.id;
msg.headers = {
"authorization" : "Bearer " + global.get("token"),
"content-type" : "application/json",
"x-deployment-id" : encodeURIComponent("crn:v1:bluemix:public:dashdb-for-transactions:us-south:a/---:***::") )
};
msg.payload = null;
msg.rejectUnauthorized = false; //オレオレを許可
return msg;
Switch
SQLの実行に時間がかかる場合、APIの呼び出しタイミングによっては 実行中(runnning)、もしくは 失敗(failed) を返すこともあるので、API呼び出しが完了(completed)になるまで状態をチェックします。
Switchノードに設定する分岐条件は以下の通りです。
- プロパティ： msg.payload.status
| 条件順番 | 条件式 | 値 | 接続先ノード |
|---|---|---|---|
| 1 | == | running | delay |
| 2 | == | failed | debug |
| 3 | その他 | サブフローのアウトプット |
!
delay
APIのステータスが実行中(running)だった場合、再度APIを呼び出して結果を確認します。間隔を開けて結果取得APIにアクセスするために、delay ノードを挟みます。
エラーチェック
API呼び出しがエラーだったかどうかを判定します。呼び出しのエラーが１回目の場合はAPI呼び出しを再試行し、２回目のAPI呼び出しもエラーの場合、フローをエラーとして扱います。
使用ノード
- Switch
- change
SQLAPIコールエラー確認
APIの呼び出しがエラーだったかどうかを確認します。
- プロパティ： msg.payload.errors
| 条件順番 | 条件式 | 値 | 接続先ノード |
|---|---|---|---|
| 1 | is not null | 次の処理へ | |
| 2 | その他 | SQL実行のFunctionノード |
エラー回数チェック
API呼び出しのエラー回数をカウントチェックします。
- プロパティ： msg.error_count
| 条件順番 | 条件式 | 値 | 接続先ノード |
|---|---|---|---|
| 1 | == | 1 | Debugノード(エラー処理) |
| 2 | その他 | set msg.error_count(リトライ処理へ) |
set msg.error_count
エラーカウントを１にします
- 代入先： msg.error_count
- 代入元： 1
実行
サブフローを実行するためのメインフローを準備します
injectノードの ペイロードに、実行したいSQL文を「文字列」で入力します。
**「デプロイ」**したあと、injectノードを実行すると、デバッグウインドウにSQL実行結果が表示されます。
フローソースコード
メインフロー
[{"id":"73fafa06.250d84","type":"inject","z":"eba0be30.3bd82","name":"","topic":"","payload":"SELECT MAX(DEREPO_POSTTIME) FROM TR_DEREPO","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":90,"y":40,"wires":[["e29006b9.c80008"]]}]
サブフロー
[{"id":"e8836514.834558","type":"subflow","name":"Subflow 1","info":"","in":[{"x":60,"y":340,"wires":[{"id":"15828c28.4cfb74"}]}],"out":[{"x":980,"y":700,"wires":[{"id":"5c39894f.1f9918","port":2}]}]},{"id":"42dcae6b.83c96","type":"function","z":"e8836514.834558","name":"","func":"msg.method = \"POST\";\n\nmsg.url = [Db2 on Cloud のURL];\nmsg.url += \"/dbapi/v4/auth/tokens\";\n\nmsg.headers = {\n \"Content-Type\" : \"application/json\",\n \"x-deployment-id\" : encodeURIComponent([crn から始まる deployment_id]) \n};\n\nmsg.payload = {\n \"userid\" : [user_name],\n \"password\" : [pass_word],\n};\n\nmsg.rejectUnauthorized = false; //オレオレを許可\n\nreturn msg;","outputs":1,"noerr":18,"x":490,"y":200,"wires":[["70419f5b.d3b71"]]},{"id":"70419f5b.d3b71","type":"http request","z":"e8836514.834558","name":"","method":"use","ret":"obj","paytoqs":"ignore","url":"","tls":"","persist":false,"proxy":"","authType":"","x":560,"y":240,"wires":[["d30d394f.127d08"]]},{"id":"3d472c20.76c1c4","type":"inject","z":"e8836514.834558","name":"","topic":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":270,"y":120,"wires":[["42dcae6b.83c96"]]},{"id":"67cefa56.291034","type":"debug","z":"e8836514.834558","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1030,"y":640,"wires":[]},{"id":"61fd4a77.876574","type":"comment","z":"e8836514.834558","name":"Token取得","info":"","x":490,"y":160,"wires":[]},{"id":"15828c28.4cfb74","type":"change","z":"e8836514.834558","name":"SQLバックアップ","rules":[{"t":"set","p":"query","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":240,"y":340,"wires":[["1ae60db9.b8d132"]]},{"id":"d30d394f.127d08","type":"change","z":"e8836514.834558","name":"Backup token(global)","rules":[{"t":"set","p":"token","pt":"global","to":"payload.token","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":650,"y":280,"wires":[["1ae60db9.b8d132"]]},{"id":"1ae60db9.b8d132","type":"function","z":"e8836514.834558","name":"SQL 実行","func":"msg.method = \"POST\";\n\nmsg.url = [Db2 on Cloud のURL];\nmsg.url += \"/dbapi/v4/sql_jobs\";\n\nmsg.headers = {\n \"authorization\" : \"Bearer \" + global.get(\"token\"),\n \"Content-Type\" : \"application/json\",\n \"x-deployment-id\" : encodeURIComponent([crn から始まる deployment_id]) \n};\n\nmsg.payload = {\n \"commands\" : msg.query,\n \"limit\" : 300,\n \"separator\" : \";\",\n \"stop_on_error\" : \"no\",\n};\n\nmsg.rejectUnauthorized = false; //オレオレを許可\n\nreturn msg;","outputs":1,"noerr":18,"x":520,"y":400,"wires":[["f7c40e7c.66f8e"]]},{"id":"f7c40e7c.66f8e","type":"http request","z":"e8836514.834558","name":"","method":"use","ret":"obj","paytoqs":"ignore","url":"","tls":"","persist":false,"proxy":"","authType":"","x":580,"y":440,"wires":[["432f6bc.ea2a394","67cefa56.291034"]]},{"id":"727e4a73.67e394","type":"function","z":"e8836514.834558","name":"結果取得","func":"msg.method = \"GET\";\n\nmsg.url = [Db2 on Cloud のURL];\nmsg.url += \"/dbapi/v4/sql_jobs/\" + msg.payload.id;\n\nmsg.headers = {\n \"authorization\" : \"Bearer \" + global.get(\"token\"),\n \"content-type\" : \"application/json\",\n \"x-deployment-id\" : encodeURIComponent([crn から始まる deployment_id]) \n};\nmsg.payload = null;\nmsg.rejectUnauthorized = false; //オレオレを許可\n\nreturn msg;","outputs":1,"noerr":18,"x":550,"y":580,"wires":[["10db9a81.3f6ee5"]]},{"id":"10db9a81.3f6ee5","type":"http request","z":"e8836514.834558","name":"","method":"use","ret":"obj","paytoqs":"ignore","url":"","tls":"","persist":false,"proxy":"","authType":"","x":600,"y":620,"wires":[["67cefa56.291034","5c39894f.1f9918"]]},{"id":"432f6bc.ea2a394","type":"switch","z":"e8836514.834558","name":"SQL API コールエラー確認","property":"payload.errors","propertyType":"msg","rules":[{"t":"nnull"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":1050,"y":80,"wires":[["d5839f31.88bef"],["727e4a73.67e394"]]},{"id":"d5839f31.88bef","type":"switch","z":"e8836514.834558","name":"エラー回数チェック","property":"error_count","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"num"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":1030,"y":120,"wires":[[],["907ea484.0ecd18"]]},{"id":"907ea484.0ecd18","type":"change","z":"e8836514.834558","name":"","rules":[{"t":"set","p":"error_count","pt":"msg","to":"1","tot":"num"}],"action":"","property":"","from":"","to":"","reg":false,"x":1030,"y":160,"wires":[["42dcae6b.83c96"]]},{"id":"e4829343.1a91c","type":"comment","z":"e8836514.834558","name":"エラーチェック","info":"","x":1010,"y":40,"wires":[]},{"id":"ac06d1b2.49b19","type":"comment","z":"e8836514.834558","name":"SQLー実行","info":"","x":530,"y":360,"wires":[]},{"id":"5702cc3f.b65274","type":"comment","z":"e8836514.834558","name":"https://cloud.ibm.com/apidocs/db2-on-cloud/db2-on-cloud-v4","info":"","x":400,"y":80,"wires":[]},{"id":"5c39894f.1f9918","type":"switch","z":"e8836514.834558","name":"","property":"payload.status","propertyType":"msg","rules":[{"t":"eq","v":"running","vt":"str"},{"t":"eq","v":"failed","vt":"str"},{"t":"else"}],"checkall":"true","repair":false,"outputs":3,"x":620,"y":680,"wires":[["6000ccd7.171784"],["67cefa56.291034"],[]]},{"id":"6000ccd7.171784","type":"delay","z":"e8836514.834558","name":"","pauseType":"delay","timeout":"2","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":290,"y":580,"wires":[["727e4a73.67e394"]]},{"id":"16464055.e7f15","type":"comment","z":"e8836514.834558","name":"SQL-結果取得","info":"","x":540,"y":540,"wires":[]}]