GAデータでLambdaからRDSを更新する


##はじめに
概要：GAデータをRDSへ格納してSELECT結果をスプレッドシートへ反映する。

とりあえず考えた以下の３案で最も管理コストが低そうなものをチョイス
◯ 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　⇨　スプレッドシート

1. [Google Analyticsのアドオン](https://gsuite.google.com/marketplace/app/google_analytics/477988381226?hl=ja&pann=sheets_addon_widget)を入れる
2. アドオンの Create new report でレポートを作成 
3. アドオンの Schedule Reports で定期実行のスケージュールを設定

※アドオンはスプレッドシートではなくGoogleアカウントへ適応されるため、
　別の人も運用する場合はアドオンの導入が必要です。

##GASのスケジューラを起点にしたデータの流れ
スプレッドシート ⇔ GAS ⇔ APIGateway ⇔ Lambda ⇔ RDS

###GASでレポートの値を取得

**スプレッドシート ⇔ GAS** ⇔ APIGateway ⇔ Lambda ⇔ RDS

```.js
// スプレッドシート
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へリクエストします。

```.js
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のサービス上から作成する場合**

|<img width="600" alt="from api gateway" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/648275/1cca9bcb-ba88-32e0-1e09-9e8be321288a.png">|
|:--|
**Lambda関数のトリガー追加から作成する場合**

|<img width="600" alt="from lambda" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/648275/4d4621c4-26f5-6211-ec27-fd3528fed090.png">|
|:--|

**ルートの設定**
GASからPOSTリクエストするのでPOSTのルートを設定します。

|<img width="600" alt="root setting" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/648275/68312b16-2ab5-64fc-fe1d-aadcf3ccdc55.png">|
|:--|

作成後APIエンドポイントのURLが発行されます。
API エンドポイント: `https://*******.execute-api.ap-northeast-1.amazonaws.com/default/test`


###Lambdaで利用するDBアクセス情報の秘匿化

今回はDBアクセスを行うためアクセス情報の秘匿化をあわせて行います。
Systems Managerのパラメータストアにデータを保存し、
暗号化鍵の管理にKey Management Serviceを利用します。

**鍵の作成**
Key Management Service → カスタマー管理型のキー　→　キーの作成

ステップ１：キーを設定
「対応」を選択したまま次へ

ステップ２：ラベルを追加
KEYにアクセスするための名称をつけます。

|<img width="600" alt="kms setting" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/648275/06be6a0e-1bff-3904-bb23-861b6746fd83.png">|
|:--|

ステップ３：キーの管理アクセス許可の定義
任意のユーザに管理権限をつけます。

ステップ４：キーの使用アクセス許可を定義
Lambda関数に付与しているロールへ権限を付与します。

**パラメータストアへDB情報の作成**
AWS Systems Manager → パラメータストア　→　パラメータの作成

※ユーザ、パスワードそれぞれ作成します。
名前：任意
タイプ：安全な文字列
KMSキーソース：現在のアカウント
KMSキーID：**先程作成したキー**
値：DB情報

|<img width="600" alt="pstore" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/648275/07f32700-b4ce-6c53-3d1a-ac6c30422fda.png">|
|:--|

###Lambdaで利用するDBアクセス情報の取得
今回はNode.jsでのサンプルを作ります。
SSMへリクエストして先程作成したパラメータを取得します。

Lambda関数からSSMへアクセスするためにSGを設定します。

ssm:GetParametersとsts:AssumeRoleの権限を付与し、
リソースに先程作成したパラメータのARNを設定したポリシーを作成し、ロールへアタッチします。

```json
{
    "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`を利用してパラメータストアから値を取得します。

```.js
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名)の情報を設定しています。

|<img width="600" alt="env" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/648275/6aceea0b-3a8e-df31-5e83-8b77ef3bc340.png">|
|:--|
user / passwordは前述のパラメータストアから取得します。



```.js
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を実行してみる

```.js
// 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'
```

プレースホルダーを利用したデータの取り扱いは[こちらの記事](https://qiita.com/murashik/items/238a25d5c787e010e128)を参考にお試しください。

###おまけ２：LambdaからGASにレスポンスを返す
スプレッドシート ⇔ **GAS ⇔ APIGateway ⇔ Lambda** ⇔ RDS
データを取得してGAS側へ返すレスポンスを作成

```.js
async function getResponse(status, responseBody) {
  return {
    "statusCode": status,
    "headers": {
      "Content-Type": "application/json"
    },
    "isBase64Encoded": false,
    "multiValueHeaders": { 
      "X-Custom-Header": "***************",
    },
    "body": JSON.stringify(responseBody)
  }
};
```

###おまけ２：GASでスプレッドシートにデータを格納する
`getRange`で範囲指定して、`setValues`で配列を渡すことで一括挿入ができます。
合わせて`setBorder`で枠線をつけることもできます。

```.js
// スプレッドシート
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);
}
```

###おまけ３：NatがないVPCでLambdaから他AWSサービスへアクセスする
PrivateLinkを作成してエンドポイント経由することでアクセスが可能です。

VPC　→　エンドポイント　→　エンドポイントの作成

- サービスカテゴリ：AWSサービス
- サービス名：**com.amazonaws.ap-northeast-1.ssm**
- VPC：対象のVPC
- サブネット：対象のサブネット
- セキュリティグループ：Lambdaに利用しているセキュリティグループ

作成してステータスが`使用可能`になれば疎通可能です。

|<img width="600" alt="env" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/648275/6637562b-4648-d105-8382-3e614f3cdac8.png">|
|:--|


###最後に
今回はGAデータを用いたRDS更新までの大まかな設定を行いました。
あくまで疎通できるところの要点のみをピックアップしております。
サービス可する際にはAPIGatewayによるセキュリティ対応やLambdaのerrorハンドリング・通知など
運用面に必要な諸々を設定しましょう。