##はじめに
概要:GAデータをRDSへ格納してSELECT結果をスプレッドシートへ反映する。
とりあえず考えた以下の3案で最も管理コストが低そうなものをチョイス
◯ APIGatewayでLambdaリクエストによるデータのやり取りを行う
→ エラー通知さえ作れば保守がラクそう。既存のアカウントで運用可。
× GCPアカウントを利用してスプレッドシートAPIをlambdaで使う
→ 持ってないアカウント管理が増えるからやだ。
× S3にデータを配置してputをトリガーにやり取り
→ S3のデータソースを使う必要性はない、IOが多い。
データの流れ
スプレッドシート ⇔ GAS ⇔ APIGateway ⇔ Lambda ⇔ RDS
使うリソース
- GAS
- GAアドオン
- APIGateway
- Lambda(Node.js)
- RDS(MySQL)
- Systems Managerのパラメータストア
- Key Management Service
##GAアドオンでレポートを取得
GA ⇨ スプレッドシート
- Google Analyticsのアドオンを入れる
- アドオンの Create new report でレポートを作成
- アドオンの Schedule Reports で定期実行のスケージュールを設定
※アドオンはスプレッドシートではなくGoogleアカウントへ適応されるため、
別の人も運用する場合はアドオンの導入が必要です。
##GASのスケジューラを起点にしたデータの流れ
スプレッドシート ⇔ GAS ⇔ APIGateway ⇔ Lambda ⇔ RDS
###GASでレポートの値を取得
スプレッドシート ⇔ GAS ⇔ APIGateway ⇔ Lambda ⇔ RDS
// スプレッドシート
const spreadsheetId = '********************************************';
const ss = SpreadsheetApp.openById(spreadsheetId);
const sheetName = "pv_202008";
const sheet = ss.getSheetByName(sheetName);
// セル指定
// Sheetオブジェクト.getRange(行番号, 列番号)
const title = sheet.getRange(2,2).getValues();
// アドレス指定(範囲)
// Sheetオブジェクト.getRange(アドレス)
const data = sheet.getRange('A16:C3000').getValues();
//---
//よしなに整形しちゃう。
###GASでAPIへリクエストする
スプレッドシート ⇔ GAS ⇔ APIGateway ⇔ Lambda ⇔ RDS
UrlFetchApp.fetch(url, options)
を用いてAPIのURLへリクエストします。
function request(requestParams) {
let result;
const url = "https://hogehoge.execute-api.ap-northeast-1.amazonaws.com/hugafuga/api";
const options = {
"method": "POST",
"contentType": "application/json",
"headers": {
"Accept": "application/json",
"Content-Type": "application/json"
},
"payload": JSON.stringify(requestParams)
};
try {
const response = UrlFetchApp.fetch(url, options);
result = JSON.parse(response.getContentText());
Logger.log(rows);
} catch(e) {
Logger.log("エラーの内容:" + e);
}
return result;
}
###API Gatewayの作成
スプレッドシート ⇔ GAS ⇔ APIGateway ⇔ Lambda ⇔ RDS
今回はHTTP APIを用います。
API Gatewayの作成はLambdaのトリガー追加、又はAPI Gatewayのサービス上で作成します。
それぞれ作成時にLambda関数への紐付けを行えます。
API Gatewayのサービス上から作成する場合
Lambda関数のトリガー追加から作成する場合 |
ルートの設定
GASからPOSTリクエストするのでPOSTのルートを設定します。
作成後APIエンドポイントのURLが発行されます。
API エンドポイント: https://*******.execute-api.ap-northeast-1.amazonaws.com/default/test
###Lambdaで利用するDBアクセス情報の秘匿化
今回はDBアクセスを行うためアクセス情報の秘匿化をあわせて行います。
Systems Managerのパラメータストアにデータを保存し、
暗号化鍵の管理にKey Management Serviceを利用します。
鍵の作成
Key Management Service → カスタマー管理型のキー → キーの作成
ステップ1:キーを設定
「対応」を選択したまま次へ
ステップ2:ラベルを追加
KEYにアクセスするための名称をつけます。
ステップ3:キーの管理アクセス許可の定義
任意のユーザに管理権限をつけます。
ステップ4:キーの使用アクセス許可を定義
Lambda関数に付与しているロールへ権限を付与します。
パラメータストアへDB情報の作成
AWS Systems Manager → パラメータストア → パラメータの作成
※ユーザ、パスワードそれぞれ作成します。
名前:任意
タイプ:安全な文字列
KMSキーソース:現在のアカウント
KMSキーID:先程作成したキー
値:DB情報
###Lambdaで利用するDBアクセス情報の取得
今回はNode.jsでのサンプルを作ります。
SSMへリクエストして先程作成したパラメータを取得します。
Lambda関数からSSMへアクセスするためにSGを設定します。
ssm:GetParametersとsts:AssumeRoleの権限を付与し、
リソースに先程作成したパラメータのARNを設定したポリシーを作成し、ロールへアタッチします。
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "VisualEditor0",
"Effect": "Allow",
"Action": [
"sts:AssumeRole",
"ssm:GetParameters"
],
"Resource": [
"arn:aws:ssm:ap-northeast-1:226920958931:parameter/prod/env/database__password",
"arn:aws:ssm:ap-northeast-1:226920958931:parameter/prod/env/database__user"
]
}
]
}
aws-sdk/clients/ssm
のgetParameters
を利用してパラメータストアから値を取得します。
let connection;
let connectionParams = {
connectTimeout: 600000,
host : ”エンドポイントを設定”,
database : ”DB名を設定”
};
async function getSSMParams() {
const ssm = new (require('aws-sdk/clients/ssm'))();
const ssm_response = await ssm.getParameters({
Names: [
'/prod/env/database__user',
'/prod/env/database__password'
],
WithDecryption: true //暗号化してる場合はtrueで複合化
}).promise();
if (ssm_response.Parameters != null) {
let params = {};
for (const param of ssm_response.Parameters) {
params[param.Name] = param.Value;
}
connectionParams.user = params['/prod/env/database__user'];
connectionParams.password = params['/prod/env/database__password'];
}
}
###LambdaからMySQLへアクセスする
スプレッドシート ⇔ GAS ⇔ APIGateway ⇔ Lambda ⇔ RDS
今回はNode.jsにてMysqlへアクセスを行います。
node_modulesのmysql
を利用します。
createConnection
に下記パラメータを渡してコネクションを作成します。
- host
- database
- user
- password
今回は環境変数にhost (endpoint) / database (db名)の情報を設定しています。
user / passwordは前述のパラメータストアから取得します。 |
const mysql = require('mysql');
let connectionParams = {
host : process.env['endpoint'],
database : process.env['db'],
};
exports.handler = async function(event, context, callback) {
// DB情報取得
await getSSMParams();
connection = mysql.createConnection(connectionParams);
connection.connect(function(err) {
if (err) {
console.error('error connecting: ' + err.stack);
callback(new Error('failure'));
response = getResponse('500', err);
console.log("response: " + JSON.stringify(response));
return response;
}
console.log('connected as id ' + connection.threadId);
});
console.log("Starting connection ...");
// ここでSQL実行したりする
// ~ 省略 ~
});
上記でコネクションが作成できました。
リクエストから値を取得してSQLを実行してみる
// INSERT
const row = JSON.parse(event.body); // {id: 1, title: 'Hello MySQL'};
const sql = "INSERT INTO posts SET ?";
const query = connection.query(sql, post, (error, results, fields) => {
if (error) throw error;
// ...
});
console.log(query.sql); // INSERT INTO posts SET id = 1, title = 'Hello MySQL'
プレースホルダーを利用したデータの取り扱いはこちらの記事を参考にお試しください。
###おまけ2:LambdaからGASにレスポンスを返す
スプレッドシート ⇔ GAS ⇔ APIGateway ⇔ Lambda ⇔ RDS
データを取得してGAS側へ返すレスポンスを作成
async function getResponse(status, responseBody) {
return {
"statusCode": status,
"headers": {
"Content-Type": "application/json"
},
"isBase64Encoded": false,
"multiValueHeaders": {
"X-Custom-Header": "***************",
},
"body": JSON.stringify(responseBody)
}
};
###おまけ2:GASでスプレッドシートにデータを格納する
getRange
で範囲指定して、setValues
で配列を渡すことで一括挿入ができます。
合わせてsetBorder
で枠線をつけることもできます。
// スプレッドシート
const spreadsheetId = 'ほげほげほげお_ふがふがお';
const ss = SpreadsheetApp.openById(spreadsheetId);
const result = requestApi(requestParams);
if (Object.keys(result).length) {
setNewRanking(result);
}
function setNewData(values) {
// データを取得
const sheet = ss.getSheetByName("シート名");
const numRows = values.length;
const numColumns = Object.keys(values[0]).length;
Logger.log(numRows);
Logger.log(numColumns);
// 挿入用objectに整形
let valuesArr = [];
for (let i = 0; i < numRows; i++) {
valuesArr.push(Object.values(values[i]));
}
sheet.insertRows(2,numRows + 2); // 日付と余白を挿入
sheet.getRange(3, 1, numRows + 1, numColumns).setValues(valuesArr).setBorder(true, true, true, true, true, true);
}
###おまけ3:NatがないVPCでLambdaから他AWSサービスへアクセスする
PrivateLinkを作成してエンドポイント経由することでアクセスが可能です。
VPC → エンドポイント → エンドポイントの作成
- サービスカテゴリ:AWSサービス
- サービス名:com.amazonaws.ap-northeast-1.ssm
- VPC:対象のVPC
- サブネット:対象のサブネット
- セキュリティグループ:Lambdaに利用しているセキュリティグループ
作成してステータスが使用可能
になれば疎通可能です。
###最後に
今回はGAデータを用いたRDS更新までの大まかな設定を行いました。
あくまで疎通できるところの要点のみをピックアップしております。
サービス可する際にはAPIGatewayによるセキュリティ対応やLambdaのerrorハンドリング・通知など
運用面に必要な諸々を設定しましょう。