外部システムで管理しているマスタデータを Questetra BPM Suite でも利用したいということが考えられます。今回は Amazon RDS（リレーショナルデータベースサービス）で管理しているデータを、Questetra BPM Suite の「検索セレクトボックス」の外部マスタとして使用します。
「検索セレクトボックス」とは？
「検索セレクトボックス」は、データ項目の種類の一つです。検索文字列を入力して検索ボタンを押すとマスタデータから検索文字列を含む選択肢が表示され、そこからデータを選択して入力できます。
RDS を外部マスタとして使用する利点
RDS を外部マスタとして使用すると、大量のデータを扱えるうえ、データの変更をリアルタイムに反映させることができます。マスタデータの管理方法とそれぞれのメリット、デメリットについては「Questetra でマスタデータを管理するいくつかの方法」をご参照ください（本記事で紹介する方法は「パターン1」に該当します）。
利用する AWS のサービス
本記事で紹介する方法では、AWS（Amazon Web Service）の以下のサービスを利用します。カッコ内に用途を示します。
- RDS（データベース）
- RDS Proxy（データベース接続プロキシ）
- Secrets Manager（認証情報の管理）
- Lambda（アプリケーションの作成）
- API Gateway（API の作成）
仕組み
大枠の仕組みは下図の通りです。
「検索セレクトボックス」が API Gateway に GET リクエストを送信すると、Lambda にイベントが送られます。Lambda はイベントをもとに RDS にデータを問い合わせ、結果を返します。
図には記載していませんが、RDS への接続は RDS Proxy を経由し、認証情報を Secrets Manager で管理します。
RDS Proxy を経由せずに Lambda から直接 RDS に接続することもできますが、多数の接続が開始されてリソースを消費してしまうことがあります。RDS Proxy を経由することで接続を効率的に管理できます。
手順概要
API Gateway と Lambda を用いて、RDS のデータを取得する API を作成します。作成した API のエンドポイントを「検索セレクトボックス」の「選択肢データの URL」として設定し、RDS を外部マスタとして利用します。
大まかな流れを示します。
- RDS でデータベースを作成する
- RDS Proxy 経由でデータベースに接続するための設定を行う
- Lambda 関数を作成する（本記事では Node.js を使用します）
- Lambda 関数に RDS Proxy をアタッチする
- Lambda 関数のトリガーとして API Gateway を設定する
- Questetra BPM Suite で「検索セレクトボックス」を用いたアプリを作成する
必要環境
本記事の手順を進めるには以下のものが必要です。
- MySQL クライアント（データベースの作成に使用）
- Node.js / npm コマンド（Lambda 関数コードの作成に使用）
手順
1. RDS でデータベースを作成する
1-1．データベースの作成
RDS でデータベースを作成します。RDS Proxy がサポートしているエンジンのタイプ、エディションを選択してください（RDS Proxy の制約事項）。ここでは「MySQL 5.6 との互換性を持つ Amazon Aurora」を選択しています。
「DB クラスター識別子」と「マスターユーザー名」を入力します。ここでは「DB クラスター識別子」は sample-database-1 、「マスターユーザー名」は admin としています。
「データベースの作成」をクリックしてしばらく待つと、上部に「データベース {DB クラスター識別子} が正常に作成されました。」と表示されます。
「認証情報の詳細を表示」をクリックし、マスターユーザー名、マスターパスワード、エンドポイントをメモしておきます。
1-2．データベースへの接続、テーブルの作成
次のmysql コマンドでデータベースに接続します（コマンドの後、マスターパスワードを入力します）。
mysql -h {エンドポイント} -P {ポート番号} -u {マスターユーザー名} -pポート番号はデータベースの一覧でデータベース識別子をクリックすると表示されます。デフォルトでは 3306 に設定されています。
接続に成功すると次のように表示されます。
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.10 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>接続に失敗するときは、データベースインスタンスの設定（パブリックアクセスの許可、VPC セキュリティグループの設定）を確認します。具体的な手順は「Amazon RDS を外部マスタとして使用する（トラブルシューティング）」を参照してください。
データベースに接続できたら、文字化けを防ぐためにまず文字コードの設定を確認します（データに日本語を含まない場合はこの手順を飛ばしても問題ありません）。mysql> に続けて次の MySQL コマンドを入力し、実行します。
show variables like 'char%';次のように、 character_set_client、character_set_connection、character_set_database、character_set_results、character_set_server が utf8 になっていれば大丈夫です。
+--------------------------+--------------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /rdsdbbin/oscar-5.6.10a.200340.0/share/charsets/ |
+--------------------------+--------------------------------------------------+utf8 でないパラメータがある場合は、RDS でパラメータグループを作成し、データベースに適用します。具体的な手順は「Amazon RDS を外部マスタとして使用する（トラブルシューティング）」を参照してください。
文字コードの設定ができたら、データベーススキーマとテーブルを作成します。ここでは「sample_db」というデータベーススキーマを作成し、そこに「nations」というテーブルを作成します。
データベーススキーマの作成
mysql> に続けて次のSQL 文を実行し、「sample_db」という名前のデータベーススキーマを作成します。
create database sample_db;データベーススキーマの切り替え
次の SQL 文を実行し、先ほど作成した「sample_db」に切り替えます。
use sample_db;テーブルの作成
value と display の２つの列からなるテーブルを作成します。value は選択肢ID、display は表示ラベルです。
CREATE TABLE `nations` (
`value` VARCHAR(10) NOT NULL PRIMARY KEY,
`display` VARCHAR(100) NOT NULL
) DEFAULT CHARSET=utf8;データの挿入
ここでは４行のデータを挿入します。
INSERT INTO nations (value, display)
VALUES
("JP", "日本"),
("US", "アメリカ"),
("UK", "イギリス"),
("AU", "オーストラリア");2. RDS Proxy 経由でデータベースに接続するための設定を行う
2-1．データベースの認証情報をシークレットとして保存
Secrets Manager を開き、「新しいシークレットを保存する」をクリックします。
「RDS データベースの認証情報」が選択された状態で、ユーザー名、パスワードにそれぞれデータベースのマスターユーザー名、マスターパスワードを入力します。DB インスタンスを選択し、「次へ」をクリックします。
シークレットの名前を入力します。ここでは「sample-rds-secret」としています。「次へ」をクリックします。
他はデフォルトのまま「次へ」をクリックし、「保存」します。
シークレットが作成されたら詳細を開き、「シークレットの ARN」をメモしておきます。
2-2．RDS Proxy がシークレットを読み取るための IAM ロールを作成
まず IAM ロールに設定するポリシーを作成します。IAM を開き、左メニューの「アクセス管理」から「ポリシー」を選択し、「ポリシーの作成」をクリックします。
JSON のタブを選択し、下記のようにポリシーを記述し（Resource には先ほど作成したシークレットの ARN を記載します）、「ポリシーの確認」へと進みます。
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "VisualEditor0",
"Effect": "Allow",
"Action": [
"secretsmanager:GetResourcePolicy",
"secretsmanager:GetSecretValue",
"secretsmanager:DescribeSecret",
"secretsmanager:ListSecretVersionIds"
],
"Resource": [
"{シークレットの ARN}"
]
},
{
"Sid": "VisualEditor1",
"Effect": "Allow",
"Action": [
"secretsmanager:GetRandomPassword",
"secretsmanager:ListSecrets"
],
"Resource": "*"
}
]
}
ポリシーの名前を入力し、「ポリシーの作成」をクリックします。ここでは「AllowAccessToSampleRdsSecret」としています。
ポリシーが作成できたら、IAM ロールを作成します。IAM の左メニューの「アクセス管理」から「ロール」を選択し、「ロールの作成」をクリックします。
「AWS サービス」を選択し、「ユースケースの選択」では「RDS」から「RDS – Add Role to Database」を選択して「次のステップ：アクセス権限」へと進みます。
先ほど作成したポリシーを選択し、「次のステップ：タグ」へと進みます。
タグの追加はせずに「次のステップ：確認」へと進み、ロール名を入力して「ロールの作成」をクリックします。ここでは「rds-getting-secret-role」としています。
ロールが作成されたら詳細を開き、「ロール ARN」をメモしておきます。
3. Lambda 関数を作成する
3-1．Lambda 関数の作成
Lambda を開き、左メニューの「関数」を選択し、「関数の作成」をクリックします。
「一から作成」を選択し、関数名を入力します。ここでは「myRdsFunction」としています。ランタイムには「Node.js 12.x」を選択し、「関数の作成」をクリックします。
3-2．ローカルで関数コードを作成
ローカルで関数コードを作成していきます。本記事では次の２つのパッケージを使用します。
まずローカルディレクトリを作成し、ディレクトリ内で次のコマンドを実行、package.json を作成します。
npm init -y次のコマンドで２つのパッケージ（promise-mysql と xmlbuilder）をそれぞれインストールします。
npm install {パッケージ名} -saveファイル「index.js」を作成し、コードを記述します。
コード例（index.js）
コードの例を以下に示します。
const mysql = require('promise-mysql');
const builder = require('xmlbuilder');
const db_config = {
host : process.env['endpoint'],
user : process.env['user'],
password : process.env['password'],
database : process.env['db']
};
const table = process.env['table'];
const pool = mysql.createPool(db_config);
exports.handler = async (event) => {
let response = {};
let connection;
try {
connection = await (await pool).getConnection();
console.log("Connected");
} catch (e) {
console.log(e);
response = formatError(e);
return response;
}
console.log("Starting query ...");
try {
const {sql, inserts} = buildSql(event);
const results = await connection.query(sql, inserts);
const xml = buildXml(results);
response = formatResponse(xml);
} catch (e) {
console.log(e);
response = formatError(e);
} finally {
await connection.release();
console.log("Connection released");
return response;
}
};
function buildSql (event) {
let sql = "SELECT * FROM ??";
let inserts = [table];
let conditions = new Array();
if (event.queryStringParameters && event.queryStringParameters.query) {
const query = event.queryStringParameters.query;
conditions.push('display LIKE ?');
inserts.push(`%${query}%`);
}
if (event.queryStringParameters && event.queryStringParameters.parent) {
const parentItemId = event.queryStringParameters.parent;
conditions.push('value LIKE ?');
inserts.push(`${parentItemId}%`);
}
const condNum = conditions.length;
if (condNum >= 1) {
sql += ` WHERE ${conditions[0]}`;
if (condNum == 2) {
sql += ` AND ${conditions[1]}`;
}
} else if (event.multiValueQueryStringParameters && event.multiValueQueryStringParameters.values) {
const values = event.multiValueQueryStringParameters.values;
sql += " WHERE value IN (?";
for (let i = 1; i < values.length; i++) {
sql += ", ?";
}
sql += ")";
inserts = inserts.concat(values);
}
sql += ';';
return {sql, inserts};
}
function buildXml (results) {
const resultsNum = results.length;
let root = builder.create('items');
for (let i = 0; i < resultsNum; i++) {
let item = root.ele('item');
item.att('value', results[i].value);
item.att('display', results[i].display);
}
const xml = root.end({ pretty: true});
return xml;
}
function formatResponse (body) {
const response = {
"statusCode": 200,
"headers": {
"Content-Type": "text/plain; charset=utf-8"
},
"isBase64Encoded": false,
"body": body,
};
return response;
}
function formatError (error) {
const response = {
"statusCode": error.statusCode,
"headers": {
"Content-Type": "text/plain",
"x-amzn-ErrorType": error.code
},
"isBase64Encoded": false,
"body": error.code + ": " + error.message
};
return response;
}このコードでは、Lambda 関数の環境変数からデータベースへの接続情報を取得しています（4〜7行目）。環境変数の設定方法は後ほど説明します。
API Gateway に送られた GET リクエストを Labmda は event として受け取ります。リクエストパラメータに応じて SQL の SELECT 文を作成し（25行目）、データベースへの問い合わせを行い（26行目）、結果を「選択肢 XML 書式」に整形し（27行目）、レスポンスとして返します。
リクエストパラメータは以下の3つです。
- query ：検索文字列。display（表示ラベル）に検索文字列が含まれる行を返します。
- parent ：親選択肢の選択肢 ID。前方一致する value（選択肢 ID）をもつ行を返します。親選択肢を設定しない場合には考慮しなくてもよいパラメータです。
- values ：value（選択肢 ID）を直接指定して検索する場合に使用するパラメータです。
3-2．.zip ファイルでアップロード
ローカルディレクトリ内で次のコマンドを実行し、.zip ファイルを作成します（Windows など zip コマンドが使えない環境では、ディレクトリを右クリックして .zip ファイルに圧縮してください）。
zip -r myRdsFunction.zip .Lambda 関数を開き関数コード右上の「アクション」から「.zip ファイルをアップロード」へと進みます。
作成した .zip ファイルを選択し、「保存」をクリックしてアップロードします。
4. Lambda 関数に RDS Proxy をアタッチする
Lambda 関数に RDS Proxy をアタッチし、接続関係の設定を行います。
4-1．RDS Proxy をアタッチする
Lambda 関数を開き、下部の「データベースプロキシ」ブロックの「データベースプロキシの追加」をクリックします。
新しいデータベースプロキシを作成します。プロキシ識別子はここでは「sample-rds-proxy」としています。「RDS DB インスタンス」では作成したデータベースを選択し、「シークレット」には作成したシークレットの ARN を入力、IAM ロールも作成したものを選択します。「認証」は「パスワード」を選択して「追加」をクリックします。
プロキシの詳細を表示し、プロキシエンドポイントをメモしておきます。
4-2．Lambda 関数の環境変数を設定
本記事の Lambda 関数コードでは、環境変数から必要な情報を読み出してデータベースへ接続しています。Lambda 関数の環境変数にデータベースの情報を設定していきます。
Lambda 関数を開き、「環境変数」ブロックの「環境変数を管理」をクリックします。
キーと値を下表のように設定し、「保存」をクリックします。
| キー | 値 |
| db | データベーススキーマ名 |
| endpoint | RDS Proxy のエンドポイント |
| user | データベースのマスターユーザー名 |
| password | データベースのマスターパスワード |
| table | テーブル名 |
4-3．Lambda 関数に VPC を設定
Lambda 関数が RDS Proxy を使用するには、RDS や RDS Proxy と同じ VPC（仮想ネットワーク）に Lambda 関数を配置する必要があります。
まず VPC にアクセスするためのポリシーを Lambda 関数の実行ロールに付与します。Lambda 関数を開き、「基本設定」ブロック右上の「編集」をクリックします。
下部の「〜ロールを表示」をクリックします。
「ポリシーをアタッチします」をクリックします。
「AWSLambdaVPCAccessExecutionRole」を検索して選択し、「ポリシーのアタッチ」をクリックします。
再度 Lambda 関数の画面に戻り、「VPC」ブロックの「編集」をクリックします。
「カスタムVPC」を選択、RDS と同じ VPC、サブネット、セキュリティグループを設定し、「保存」をクリックします（RDS の VPC 設定はデータベースインスタンスの「接続とセキュリティ」タブで確認できます）。
5. Lambda 関数のトリガーとして API Gateway を設定する
5-1．API Gatewayの設定
Lambda 関数を開き、「設定」タブの「Designer」ブロックの「トリガーを追加」をクリックします。
「API Gateway」「Create an API」を選択します。「API type」は「REST API」を選択します。セキュリティは「オープン」にし、「追加」をクリックします。
API Gateway を追加できたらエンドポイントをメモしておきます。エンドポイントは Lambda 関数の「デザイナー」ブロックで API Gateway を選択すると表示されます。
5-2．ブラウザでの動作確認
API Gateway のエンドポイントにブラウザでアクセスし、動作確認してみましょう。次のように XML が表示されれば成功です。
エラーが表示される場合、原因として Lambda 関数のタイムアウトが短すぎることが考えられます。「Amazon RDS を外部マスタとして使用する（トラブルシューティング）」を参照し、タイムアウトの設定を変更してみてください。
日本語が文字化けする場合も「Amazon RDS を外部マスタとして使用する（トラブルシューティング）」を参照してください。
6. Questetra BPM Suite で「検索セレクトボックス」を用いたアプリを作成する
Questetra BPM Suite でアプリを作成し、データ項目に「検索セレクトボックス」を追加します（表示名は「検索セレクト」です）。「選択肢種別」で「HTTP 経由で取得した選択肢」を選択し、「選択肢データの URL」に先ほど作成した API Gateway のエンドポイントを入力し、「適用して閉じる」をクリックします。
アプリをリリースし、フォームを確認してみましょう。検索文字列を入力して検索ボタンを押すと、条件に合致する選択肢が表示されます。
いかがでしたでしょうか。今回は Amazon RDS を使用しましたが、同様の方法で他のデータベースを外部マスタとして使用することもできます。マスタデータの性質、用途に合わせて外部サービスの利用も検討してみてください。
ピンバック: Using Amazon RDS as an External Master (Terraform) – Questetra Support