
表題の通りAWS RDSのORACLE DBでスナップショットから新しくDBを作るとSIDが元のままだったよって報告です。

### 環境

- AWS RDS
- エンジン: Oracle Standard Edition Two

※ OracleDBに慣れていないので変な表現があるかも。

## 現象

新しくRDSのORACLE DBでスナップショットから別のDBを作りました。
手順は[スナップショットをコピー | docs.aws.amazon.com](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/USER_CopySnapshot.html)の通りです。

コピーしたスナップショットを元に「DB インスタンスの復元」を行うと**DB インスタンス識別子**を入力できます。

新しくDBを作成した場合は、DBインスタンス識別子がエンドポイントとSIDに登録されます。
コピーしたスナップショットを元に作成した場合は、SIDが元のインスタンスのままでエンドポイントのみ新しく設定した識別子でした。

DBに接続する際に気をつけてください。

```sql
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
前のSID
```

## 参考

- [Oracle SIDを確認・設定する方法](https://sql-oracle.com/?p=1706)


AWS RDSのORACLE DBでスナップショットから新しくDBを作るとSIDが元のままだった


OracleDBの操作のため必要な**SQL*Plus**をyumでインストールする手順です。

## 環境

- Red Hat Enterprise Linux Server release 7.5 (Maipo)
- CentOS Linux release 7.6.1810 (Core)

RHELとCentOSの２つで試しました。

違いはRHELにwgetを別途インストールしたくらい。
`sudo yum install wget`

## SQL*Plusをインストールする

注意として、バDBのバージョンにInstant clientのバージョンと合わせろとエラーが出ます。
合わせたほうが無難。

```sh
UDE-00018: Data Pump client is incompatible with database version 12.1.0.2.0
```

### 1. basicとsqlplusのrpmをダウンロードする。
[Instant Client Downloads for Linux x86-64 (64-bit) | oracle.com](https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html#ic_x64_inst)
のRPMをダウンロードします。
インストール手順も書いてある。

```sh
$ wget https://download.oracle.com/otn_software/linux/instantclient/19600/oracle-instantclient19.6-basic-19.6.0.0.0-1.x86_64.rpm
$ wget https://download.oracle.com/otn_software/linux/instantclient/19600/oracle-instantclient19.6-sqlplus-19.6.0.0.0-1.x86_64.rpm

$ ll
-rw-r--r-- 1 root       root        52M  2月 13 02:30 oracle-instantclient19.6-basic-19.6.0.0.0-1.x86_64.rpm
-rw-r--r-- 1 root       root       687K  2月 13 02:30 oracle-instantclient19.6-sqlplus-19.6.0.0.0-1.x86_64.rpm
```

`impdp`と`expdp`は「tool」に含まれています。
必要な場合はtoolもインストールして下さい。

### 2. ローカルのrpmをインストールする。
```sh
$ sudo yum install oracle-instantclient19.6-basic-19.6.0.0.0-1.x86_64.rpm
$ sudo yum install oracle-instantclient19.6-sqlplus-19.6.0.0.0-1.x86_64.rpm 
```

インストール手順の3.はバージョン19.3以前のバージョンでパスを通すやり方が書いてあり、4.はローカルユーザでも実行できたのでどちらも19.6では必要ないため飛ばした。

```sh
# バージョン確認。
$ sqlplus64 -V

SQL*Plus: Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
```
インストールしたrootユーザでなくとも実行できました。
実行ファイルは`/usr/lib/oracle/19.6/client64/bin`にある。

### Oracle DBコマンドラインで接続する

`sqlplus`を実行すると接続先など聞かれる。
コマンドラインで`sqlplus hr@\"//mymachine.mydomain:port/MYDB\"`と実行できます。

```sh
$ sqlplus hr@\"//mymachine.mydomain:port/MYDB\"

$ sqlplus user_name@\"//database.xxxx.ap-northeast-1.rds.amazonaws.com:port/DB_NAME\"
Enter password: # パスワード入力
SQL> # ログイン成功
```

```sh
$ ll /usr/lib/oracle/19.6/client64/bin
合計 128
-rwxr-xr-x 1 root root 41448 12月 18 01:40 adrci
-rwxr-xr-x 1 root root 59016 12月 18 01:40 genezi
-rwxr-xr-x 1 root root 24472 12月 18 01:41 sqlplus

# echo "/usr/lib/oracle/19.6/client64/lib" >> /etc/ld.so.conf.d/oracle.conf
# cat !$
cat /etc/ld.so.conf.d/oracle.conf
/usr/lib/oracle/19.6/client64/lib
```

## 参考
- [Instant Client Downloads for Linux x86-64 (64-bit) | oracle.com](https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html#ic_x64_inst)
  - ダウンロードページとインストール手順
- [SQL*Plus Quick Start | docs.oracle.com](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/SQL-Plus-quick-start.html#GUID-D65BD2EF-72C6-4E61-812B-9FFCAAF6F305)
  - Command-lineで接続する
- [CentOS7からsqlplusでOracleRDSに接続する - Qiita](https://qiita.com/forestkinoko/items/c60c79f2bb04bb023892)
  - バージョンが新しくなってこちらより手順が減って楽になったようです。こちらはバージョン18.3。

日本語ページがあったので載せる。
- [SQL*Plusのクイック・スタート | docs.oracle.com](https://docs.oracle.com/cd/E82638_01/sqpug/SQL-Plus-quick-start.html#GUID-D65BD2EF-72C6-4E61-812B-9FFCAAF6F305)


SQL*Plusをyumでインストールする


OracleDBで `ORA-00054:resource busy and acquire with NOWAIT specified or timeout expired`が出て解決したのでメモ

バックエンドでこのエラーが出た。
```sh
; SQL []; ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
; nested exception is java.sql.SQLException: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
```

[IBM Knowledge Center](https://oracle.programmer-reference.com/ora-00054/)によると、どうやら該当レコードにの排他ロックがかかっているためこのエラーが出たようです。

問題の解決から、排他ロックを妨げているセッションを見つけて停止をしてみます。

## ロックしているセッションの停止

[こちら](https://oracle.programmer-reference.com/ora-00054/)の通りのことを試してみます。

まず、ロックしているセッションの**SID**と**SERIAL#**を探します。
```sql
SELECT SID, SERIAL#
FROM V$SESSION
WHERE
 SID IN(
  SELECT SID
  FROM V$LOCK
  WHERE TYPE IN('TM', 'TX')
 )
```

`SID=68, SERIAL#=36478`でした。ここまで分かるとセッションをKILLできます。

```sql
ALTER SYSTEM KILL SESSION '＜SID＞, ＜SERIAL#＞';

ALTER SYSTEM KILL SESSION '68, 36478';
```

## 根本的な原因

その後も何度か`ORA-00054`が出てしまいました。
DBの操作に利用していたOracle SQL Developerが原因だったようです。

SQL Developerでupdateのクエリを走らせるとロックします。
終了するとロックもなくなる。
バージョンは 19.1.0.094です。

慣れたDBじゃないのでエラーは怖い。

## 参考
- [IBM Oracle データベースのマイグレーションまたは変更中のエラー](https://oracle.programmer-reference.com/ora-00054/)
- [[ORAエラー] ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました](https://oracle.programmer-reference.com/ora-00054/)

OracleDBで ORA-00054:resource busy and acquire with NOWAIT specified or timeout expiredが出た


AWSのAmazon RDSのSSL/TSL 証明書の期限が近づき更新するようアナウンスがあり、 2020/03/06 に切れるためその前に対応が必要でした。
更新にはオペレーションが必要なため、備忘録も兼ね手順を残します。

## AWSのアナウンスについて

Classmethodさんの記事が分かり易いので引用させて頂きます。

> - 2020年2月5日: 新CA証明書への強制アップデートについての通知が送付される予定日
> - 2020年2月5日〜3月5日: メンテナンスウィンドウ準拠で新CA証明書へのアップデートが実施される予定。 新CA証明書適用が実施されると短時間のダウンタイムが発生する
> - 2020年3月5日: 現行のCA証明書の利用期限日。現行のCA証明書は利用できなくなり、アップデートがこの日までに実施されていなかった場合は強制アップデートが発生する( ダウンタイム発生 )。新しいCA証明書を持っていないアプリケーションはこの日以降SSL/TLS通信が不可能になる
> 
> [Developers.IO](https://dev.classmethod.jp/cloud/aws/notification-about-certificate-rotation-for-rds-and-aurora/)より

更新には今すぐ更新と次のメンテナンスで更新かを選べます。

詳しくは、[公式](https://aws.amazon.com/jp/blogs/news/amazon-rds-certificate-rotation-2019/)か[Developers.IO](https://dev.classmethod.jp/cloud/aws/notification-about-certificate-rotation-for-rds-and-aurora/)を参照してください。

## SSL/TSL 証明書の期限の確認

データベースの「接続とセキュリティ」タブで証明書の期限を確認できます。
今回の対象は`rds-ca-2015`で以下のように認証機関の項に載っています。

![ssl](/images/posts/220/227/rds-ssl2.png)

## 証明書を更新する

証明書更新が必要なデータベースがある場合、左メニューの「Certificate update」に通知があり、
それをクリックすると更新のメニューが開きます。

![ssl](/images/posts/220/227/rds-ssl.png)

更新したいDBを選択して、即時更新したい場合は「今すぐ更新」を選択。

![ssl](/images/posts/220/227/ssl3.png)

チェックをして「今すぐ適用」をクリックすると証明書が更新されます。

![ssl](/images/posts/220/227/ssl4.png)

更新が完了すると認証機関と証明書の日付が新しくなりました。

## 更新に伴うダウンタイムについて

証明書の更新には再起動がかかるためダウンタイムが生じます。

個人的に３回実行したのでかかった時間をまとめました。

| インスタンス  | DB                          | AZ        | 再起動にかかった時間 |
| ------------- | --------------------------- | --------- | -------------------- |
| db.t3.small   | MySQL 5.6.41                | Single AZ | 11秒                 |
| db.t3.2xlarge | MySQL 5.6.41                | Single AZ | 12秒                 |
| db.t2.small   | Oracle Standard Edition Two | Single AZ | 不明?                |

- フェイルオーバーも特になく `再起動にかかった時間 = ダウンタイム` となっていました。
- 再起動にかかった時間は10秒程でインスタンスクラスの差はほぼなさそう
- アベイラビリティゾーン(AZ)がmultiの場合でも差はないそうです(Developers.IOより)
- 再起動にかかった時間は最近のイベントに`shutdown` `restarted` のログが出るためそこで確認。
  - Oracleはそのログがないため不明としている。（更新してから認証機関を見に行くと既に新しくなっていたため、体感10秒以内に完了している）
- コンソールでの手順はMySQLとOracleで違いはありませんでした。


次の証明書の期限は**2024/08/23** までです。
次回も半年ぐらい前には通知が来ると思うので早めに対応したい。

Amazon RDSのSSL/TSL 証明書を更新する


開発中に突然エラーが発生したのでメモ。

## Error Number: 1030

> データベースエラーが発生しました。  
> Error Number: 1030  
>   
> Got error 28 from storage engine

どうやら、**サーバの容量がいっぱい**になったことでエラーが出たようです。
サーバ自体は生きていました。

## 対処

溜まっていたログファイルや過去データを削除して、対処しました。

サーバのディスク使用量を返すコマンド。
```sh
$ df -h
```

## 公式の説明

> エラー: 1030 SQLSTATE: HY000 (ER_GET_ERRNO)  
> メッセージ: ストレージエンジンがエラー %d を返しました  
> OS エラーの意味を確認するには、%d 値をチェックします。たとえば、28 はディスクに空き領域がないことを示します。

[MySQL 5.6 リファレンスマニュアル](https://dev.mysql.com/doc/refman/5.6/ja/error-messages-server.html)

28番が出ているのでディスクに空き領域がないことが示されています。

### その他の番号は？

28番以外はあまりでなさそうですが、検索して出てきたものを集めてみました。

|Got error number|概要|
|---|---|
|139 |１レコード（行）当たりの容量制限に引っかかっている。|
|168 |ディレクトリ内の容量制限に引っかかっている。|

MySQLのError Number:1030 Got error 28 from storage engine


MySQLに溜め込んだデータから月次のレポートを作成するためのSQLです。
月毎の売上や購入数など集計していきます。


## テーブル

テーブルは購入履歴（purchase_history）とします。

```sql
CREATE TABLE purchase_history (
  purchase_id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  price INT,
  purchase_datetime DATETIME
 );
```

priceに購入金額、created_atに購入日があるとしておきます。
データは適当につくりました。

## 月毎の集計

上記のテーブルで毎月の**購入数**を出力してみます。

```sql
SELECT
    DATE_FORMAT(purchase_datetime, '%Y-%m') as purchase_month,
    COUNT(*) as count
FROM
    purchase_history
GROUP BY
    DATE_FORMAT(purchase_datetime, '%Y%m');
```

出力をMarkdownで整形したもの

| purchase_month | count  |
| -------------- | :----- |
| 2018-03        | 2,035 |
| 2018-04        | 2,369 |
| 2018-05        | 2,715 |
| 2018-06        | 2,994 |
| 2018-07        | 3,203 |
| 2018-08        | 3,338 |
| 2018-09        | 3,723 |
| 2018-10        | 4,571 |
| 2018-11        | 4,601 |
| 2018-12        | 4,975 |
| 2019-01        | 4,807 |
| 2019-02        | 5,375 |

**DATETIME型**だと`GROUP BY DATE_FORMAT(purchase_datetime, '%Y%m')`のようにして集計できる。

### 年、日時での集計

これを応用すれば年や日時での集計もできる。

```sql
--- 年単位
GROUP BY DATE_FORMAT(purchase_datetime, '%Y')
--- 月単位
GROUP BY DATE_FORMAT(purchase_datetime, '%Y%m')
--- 日単位
GROUP BY DATE_FORMAT(purchase_datetime, '%Y%m%d')
```

### 売上の合計

金額の合計も出してみる。

```sql
SELECT
    DATE_FORMAT(purchase_datetime, '%Y-%m') as purchase_month,
    COUNT(*) as count,
    SUM(price) as price
FROM
    purchase_history
GROUP BY
    DATE_FORMAT(purchase_datetime, '%Y%m');
```

`SUM()`で金額を合計した。

| purchase_month | count |     price |
| -------------- | ----: | --------: |
| 2018-03        | 2,035 |    40,772 |
| 2018-04        | 2,369 |    66,054 |
| 2018-05        | 2,715 |   100,381 |
| 2018-06        | 2,994 |   163,641 |
| 2018-07        | 3,203 |    83,310 |
| 2018-08        | 3,338 |   114,945 |
| 2018-09        | 3,723 |   235,429 |
| 2018-10        | 4,571 |   312,217 |
| 2018-11        | 4,601 |   454,915 |
| 2018-12        | 4,975 |   417,500 |
| 2019-01        | 4,807 | 1,584,720 |
| 2019-02        | 5,375 |   138,980 |


## 参考

- [MySQLで月別、日別、時間、曜日別にレコード数を集計する方法](http://fuminori14.hatenablog.com/entry/2012/11/30/101816)

MySQLで月次レポートのための集計をする


OpenWeatherMapで取得したデータを**DynamoDB**に保存します。
Pythonでコードを書き、**AWS Lambda**を使って定期的に気象情報を保存することを目的としています。
DynamoDBの操作はAWSのSDKである**Boto3**を利用しました。

[OpenWeatherMapで都市名から気象情報を取得する](https://www.suzu6.net/posts/78-owm)の続きです。  
[ファイルの権限を変更できない。Googleドライブのシンボリックリンクが原因でした](https://www.suzu6.net/posts/79-mac-chmod-700) こんなこともあったので環境を少し変えています。

今回はローカルからBoto3でデータを保存するところまで。

### 環境

- Mac
- Python 3.6
- Boto3  :  v1.9.86
- Requests : v2.21

Requestsはインストール済みとします。

## Boto3

- [AWS SDK for Python - Amazon.com](https://aws.amazon.com/jp/sdk-for-python/)
- [github](https://github.com/boto/boto3)

AWS謹製のPythonモジュール。
Boto3を利用するとAmazon S3、Amazon EC2、Amazon DynamoDB などAWSの各種サービスと容易に統合できます。

ドキュメントは[こちら](https://boto3.readthedocs.io/)

### インストール方法

pipでインストールできます。

```sh
$ pip install boto3
```

## DynamoDBにテーブルを用意する

DynamoDBは**NoSQL**です。
PostgreSQLなどの**リレーショナルDB**とデータの持ち方が違うため、まずそれ用のテーブルの設計をします。

### テーブル設計

保存したいのは気象情報の時系列データです。
また、複数の観測点がありそれぞれ都市名が付いています。

よって、**プライマリパーティションキー**を`city_name (文字列)`、**プライマリソートキー**を`timestamp (数値)`にしました。
これで、都市ごとのとある期間のデータをまとめて取得できるはず。

その他の項目は自由に追加できます。
気象データは項目`data`にJSON形式でぶち込む予定です。

![hongkong](/images/posts/80/hongkong.png)

登録してみた香港の気象データ。
dataの中身は変更があると思うので適当。

テーブル名は`current-weather`とした。
scanなどテーブル全体の操作で無駄を減らすため、月ごとにテーブルを分けていく。
年月をつけて`current-weather.201901`とした。


### テーブルを作成する

設計を基にDynamoDBに気象情報を溜め込むテーブルを作成します。
リージョンは**アジアパシフィック（東京）**にしています。

![createdb](/images/posts/80/createdb.png)

ダッシュボードからテーブルを作成する。

![init-dynamodb](/images/posts/80/init-dynamodb.png)

テーブル名、プライマーキーを設定する。
ソートキーとしてtimestampを登録した。

## AWSのアクセスキーを取得する。

セキュリティのため、AWSの各種サービスの操作にはアクセスキーが必要です。
DynamoDBの読み書きやテーブルの作成するため`AmazonDynamoDBFullAccess`を持ったユーザーのアクセスキーを取得します。

マスターユーザーで試すことは良いですが、アクセスキーが流出したとき[えらいこと](https://qiita.com/mochizukikotaro/items/a0e98ff0063a77e7b694)になるので権限を絞ったユーザーを使います。

サービスの**IAM**から、左メニューの「ユーザー」→「ユーザーを追加」から気象情報をDynamoDBに書き込むユーザーを追加します。

![user-name](/images/posts/80/user-name.png)

名前を`weatherAPI`として「プログラムによるアクセス」をチェックする。

![policy](/images/posts/80/policy.png)

「既存のポリシーを直接アタッチ」を選んで、`AmazonDynamoDBFullAccess`を探してチェックします。
データを読むだけなら`AmazonDynamoDBReadOnlyAccess`があります。

![access-key](/images/posts/80/access-key.png)

必要であればタグを付け、進めるとアクセスキーが表示されます。
**アクセスキーID**と**シークレットアクセスキー**を保存してください。
シークレットアクセスキーはここでしか見ることができない（はず）です。
csvも保存できます。

くれぐれも、[GitHubにアクセスキーを上げないよう気をつけて](https://qiita.com/saitotak/items/813ac6c2057ac64d5fef)ください。

※ 正直このセキュリティ関連は詳しくないため、公式や他のサイトも参考にしてください。

## プログラムからデータを保存する

やっと本題。
気象情報を取得する部分は[前回](https://www.suzu6.net/posts/78-owm)作成したコードを使います。

全体像は、定期的に気象情報を取得して保存するプログラム。

- 複数の都市名をキーに現在の気象情報を取得する。
- データを整形する。
- 都市名をパーティションキーにしてDynamoDBに保存する。

※ テーブルを月ごとに分けるよう設計していますが、テーブルの作成はまだ機能していません。

以下のソースコード部分を繋いで一つのファイルにすると実行できます。

### 使用するモジュール

```python

"""
OpenWeatherMapから現在の気象データを取得してDynamoDBに保存する。
"""

import boto3    # AWSのSDK
import requests # OpenWeatheMapのAPI取得
import json
import datetime
import decimal  # DynamoDB用にFloat型をDecimal型に変換する
```

**boto3**をインポートする。
DynamoDBにFloat型がないためDecimal型に変換するように**decimal**を利用する。decimalは標準モジュール。

### 気象情報の取得

詳細は[前回](https://www.suzu6.net/posts/78-owm)を見てください。

```python
def getWheather(city_name):
    API_KEY = "xxxxxxxxxxxxxxxxxxxxxxxx"  # xxxにOWMのAPI Keyを入力。
    api = "http://api.openweathermap.org/data/2.5/weather?units=metric&q={city}&APPID={key}"

    url = api.format(city=city_name, key=API_KEY)
    print(url)

    response = requests.get(url)
    # APIレスポンスの表示
    jsonText = json.dumps(response.json(), indent=2)
    print(jsonText)

    return response.json(parse_float=decimal.Decimal) # dict型に変換する際にFloatをDecimalにする
```

Requestsで受け取ったresponseを**dict型**にする際、Float型をDecimal型にします。
そうしないと以下のエラーが出ます。

```error
TypeError: Float types are not supported. Use Decimal types instead.
```

参考：[DynamoDB x Python / Decimal を登録する](https://qiita.com/dskst/items/10ea3f0d5899d058734e)

### データを整形する

DynamoDBのテーブルに合わせてデータを整形する。

```python

def formatter(response):
    data = response
    # 表示用時刻
    unix = data["dt"]
    now = datetime.datetime.fromtimestamp(unix)

    # 保存するデータを作成
    item = {
        'city_name': data['name'],  # プライマリパーティションキー
        'timestamp': data["dt"],   # プライマリソートキー
        'datetime': now.strftime("%Y-%m-%dT%H:%M:%S"),
        'latitude': data['coord']['lat'],
        'longitude': data['coord']['lon'],
        'data': {
            'weather': data['weather'],
            'temp': data['main']['temp'],
            'humidity': data['main']['humidity'],
            'pressure': data['main']['pressure'],
            'clouds': data['clouds']['all'],
            'wind': data['wind']
        }
    }
    return item
```

itemの形式で保存される。
timestampはUNIX時間。

### DynamoDBへ登録する

前半にDynamoDBと接続をして、後半にitemを保存している。

先ほど登録したAWSのユーザーのアクセスキーを渡す。
コードに書きたくない場合は、設定ファイルや環境変数に登録して使うと良い。

```python

def insert(items):
    # データベース接続の初期化
    session = boto3.session.Session(
        region_name='DBのリージョン',
        aws_access_key_id='アクセスキーID',
        aws_secret_access_key='シークレットアクセスキー'
    )
    dynamodb = session.resource('dynamodb')

    # tableのパーティション用
    now = datetime.datetime.now()
    ym = now.strftime("%Y%m")

    # テーブルと接続
    table_name = 'current-weather.' + ym
    table = dynamodb.Table(table_name)

    for item in items:
        # 追加する
        response = table.put_item(
            TableName=table_name,
            Item=item
        )
        if response['ResponseMetadata']['HTTPStatusCode'] is not 200:
            # 失敗処理
            print(response)
        else:
            # 成功処理
            print('Successed :', item['city_name'])
    return
```

`table.put_item()`ではなく`table..batch_writer()`を使い一括で登録しても良さそう。

### 本体部分

取得する都市の名前をリストで持ち、順次処理していく。

```python

def weather_api(cities):
    items = []

    for city_name in cities:
        response = getWheather(city_name)

        if response['cod'] is not 200:
            print(city_name, ', status code :',
                  response['cod'], response['sys']['message'])
            continue
        item = formatter(response)
        print(item)
        items.append(item)

    insert(items)
    return True


def lambda_handler(event, context):
    # 都市と送信先を指定する。
    # OWMで指定可能な都市名は以下で確認する。
    # https://openweathermap.org/weathermap?basemap=map&cities=true&layer=temperature&lat=44.0079&lon=144.2487&zoom=12
    cities = [
        'Sapporo',
        'Tokyo',
        'Osaka-shi',
        'Okinawa',
        'Hong Kong',
        'New York',
        'City of London'
    ]
    weather_api(cities)

    return


if __name__ == "__main__":
    lambda_handler(None, None)

```

## 終わりに

無事にDynamoDBに気象データを保存することができました。
続いてはAWS Lambdaにあげて定期的に取得するように自動化していきます。

[AWS LambdaにPythonコードをzipでアップして実行する方法を詳しく](https://www.suzu6.net/posts/83-lambda-zip)

【Python】Boto3でDynamoDBにデータを保存する




## この記事について

こんにちは、suzu6です。

本ブログはAWSの**Lambda**と**DynamoDB**を使って、サーバレスなシステム構成になっています。

現在の構成はこちら

![アーキテクチャ](https://www.suzu6.net/images/posts/7/blog-website_constitution.svg)

Lambdaのところに、PythonのFlaskで作ったシステムがのっています。全体像は[こちら](https://www.suzu6.net/posts/2)で解説しています。

この記事では、DynamoDBを使う中で感じた、特徴とLambdaとの相性を紹介します。  

## DynamoBDの特徴

- NoSQLでタイプはキーバリューストア型
- 自動的なスケールアップ。
- 読み込み書き込みが早い。
- プライマリキーは`HASH` か `HASH + RANGE`の２通り
- コピーを３箇所に分散して保管してくれる
- SQLではなく、APIで操作する。
    - SQLに比べて、学習コストが高い。

## NoSQLなDynamoDB

![DynamoDB](https://www.suzu6.net/images/posts/35/DynamoDB.png)

**DynamoDBはNoSQL**であり、（馴染み深い）Postgresqlなどのリレーショナルデーターベースとは大きく異なります。

> [NoSQL データベースとは](https://aws.amazon.com/jp/nosql/)  
> NoSQL データベースは、スケーラブルなパフォーマンスとスキーマレスなデータモデル向けに最適化された非リレーショナルデータベースです。

NoSQL データベースには、列指向、ドキュメント、グラフ、キーバリューストアという4つのタイプがあります。
その内、DynamoDBはキーバリューストア（Kye-Value Store）にあたります。

キーバリューストアは、保存する情報（value）に識別記号（key）をつけて管理しようというもの。
保存できる情報を固定化せず大小様々なものを保管し、検索するときに識別記号だけ見みます。

## AWS LambdaとDBの相性について

AWSで利用できるデータベースは、Postgresなどが使える`RDS`とNoSQLの`DynamoDB`があり、この２つを比較しました。　　
(調査不足のため`RedShift`は考えていませんでした。。。)

`Lambda`の特徴は、リクエスト受信の回数に合わせて自動的にスケールすることです。
ブログへリクエストが頻発すると、データベースへのアクセス数も多くなります。

ここで、RDSでは接続数が問題になります。
当然ながら同時に接続される数が多ければ多いほど必要なCPU数、メモリ数が増えていきます。

また、DBへ都度接続することは負荷になるため、一般的にはコネクションプールを使って接続を使いまわします。
しかし、Lambdaは処理が終われば接続は切れますし、接続を共有することもありません。

RDSではピークの処理に合わせて始めから大きなスケールアップが必要となります。
よって、Lambdaとの相性はよくありません。

一方、`DynamoDB`はLambdaと同じくスケーリングが可能です。  
そのため、Lambdaと合わせるDBはDynamoDBを選ぶと良いです。

さらに詳しくは、[こちら](http://keisuke69.hatenablog.jp/entry/2017/06/21/121501)をご参照ください。

## 設計のポイント

- プライマリーキーを3つ以上指定できない
- プライマリーキー以外でのソートはできない
- 空文字が利用出来ない
- テーブル結合した検索はできない
- 複雑なデータ更新の多いシステムの利用には向かない
- キャパシティユニットは自動ではスケールしない
- 一回の検索で得られるのは１MBまで


## 使ってみた感想

本ブログでは、記事やタグの管理をDynamoDBを使っています。  
PV数も月に数百PVくらいなので、スケールアップの恩恵はまだ受けていませんが、料金が無料利用枠に十分収まっています。

良い点は、コンソール画面が使いやすいところでしょうか。
ちょっとした編集やテスト用の項目を手軽に消したりできます。

困った点は、SQLが使えずライブラリの[boto3](https://boto3.readthedocs.io/en/latest/)を利用したところです。
SQLならこう書くのにとすぐ思い浮かんでも、DynamoDBとboto3の書き方を頑張って調べて、英語のドキュメントも読んでどうにか実装していきます。
学習コストが高い点が最大の壁かもしれません。

もう一点困ったことは、ページネーションです。  
SQLでは、`offset`と`limit`を使って取得する範囲と件数を簡単に書けます。
DynamoDBでは、boto3のPagenatorを利用するようです。
情報が少なくまだ理解しきれてないため、現在はscanで全件取得して必要な範囲を得ています。

重そうな処理ですが、記事数が少ないためか、記事一覧のページネーションが意外とサクサク動いています。
今後、ベストな方法に変更していきたいです。


## 参考

- [なぜAWS LambdaとRDBMSの相性が悪いかを簡単に説明する](http://keisuke69.hatenablog.jp/entry/2017/06/21/121501)
- [NoSQL データベースとは](https://aws.amazon.com/jp/nosql/) 
- [何が違う？DynamoDBとRDS](http://blog.serverworks.co.jp/tech/2017/04/12/what_is_different_dynamodb_and_rds/) 
- [AWS DynamoDBのページング ](https://www.magata.net/memo/index.php?AWS%20DynamoDB%A4%CE%A5%DA%A1%BC%A5%B8%A5%F3%A5%B0)

---

サーバレスアーキテクチャを勉強される方は、こちらの本がおすすめです。
<div class="amakuri-default-comment" style="text-align: left; line-height: 1.5em; margin-bottom: 10px; overflow:hidden; _zoom:1;"><div class="amakuri-default-comment-image" style="float: left; margin: 0 20px 0 0;"><a href="https://www.amazon.co.jp/exec/obidos/ASIN/4798155160/suzu60c-22" target="_blank"><img src="https://images-fe.ssl-images-amazon.com/images/I/51L3N%2B3pL1L._SL160_.jpg" width="128" height="160" alt="AWSによるサーバーレスアーキテクチャ" style="border: none"></a></div><div class="amakuri-default-comment-desc" style="overflow: hidden; _zoom:1;"><div class="amakuri-default-comment-title" style="margin-bottom: 0.5em;"><a href="https://www.amazon.co.jp/exec/obidos/ASIN/4798155160/suzu60c-22" target="_blank">AWSによるサーバーレスアーキテクチャ</a></div><div class="amakuri-default-comment-posted" style="margin-bottom: 0.5em; font-size: small;">Posted at 2018.12.21</div><div class="amakuri-default-comment-author">Peter Sbarski</div><div class="amakuri-default-comment-label" style="margin-bottom: 0.5em;">翔泳社</div><div class="amakuri-default-comment-link" style="margin-top: 1em;"><a href="https://www.amazon.co.jp/exec/obidos/ASIN/4798155160/suzu60c-22" target="_blank">Amazonで詳細を見る</a></div></div></div>

動画シェアサイトをAWS Lambda, S3を使って実際に構築する手順が載っています。
その中で、どのようにLambdaを構成したら良いか大変参考になりました。また、DynamoDBとAuth0も詳しく書かれています。

AWS DynamoDBの特徴とLambdaとの相性+使ってみた感想


2018年12月にブログを移行した際にこの機能は削除しました。

## はじめに

解析の勉強のため、このブログの統計情報を集めようと思い立ちました。  

手始めに、記事のアクセス時刻や利用しているブラウザなどの情報を取得します。
本ブログは、Python + Flaskで構築しているため、**Flask request**を利用します。

この記事では、記事のページを見るユーザーの情報をFlask requestで集め、DynamoDBに保存するところまで設計します。

普段使っている、**Google Analytics**が高機能すぎますね。
調べると、[Core Reporting API](https://developers.google.com/analytics/devguides/reporting/core/v3/?hl=ja)で色々できるみたいです。
追々差別化できるようにしないと。。。

実装編はこちら

[リクエスト情報から訪問者の情報を集めてみる[Python Flask + DynamoDB]-2.実装編](https://www.suzu6.net/posts/34)

## 概要

Flaskは、`request.environ`にアクセス元の情報がまとめられています。
そこから情報を取り出し、DynamoDBに保存します。

Flaskについては、以前の記事[[Python] Flask入門とサーバレスアーキテクチャ](https://www.suzu6.net/posts/6)をご覧下さい。

## 環境

```sh
$ python --version
Python 3.6.5
$ pip show Flask
Name: Flask
Version: 1.0.2
```

## request.environの中身

ちょうど良いドキュメントが見つからないので、直接見てしまいましょう！

```python
print(request.environ)
```

これで表示したものを、見やすくjsonに加工したものが以下になります。

```json
{
    "wsgi.version": (1,0),
    "wsgi.url_scheme": "http",
    "wsgi.input": <_io.BufferedReader name=5>,
    "wsgi.errors": <_io.TextIOWrapper name="<stderr>" mode="w" encoding="UTF-8">,
    "wsgi.multithread": True,
    "wsgi.multiprocess": False,
    "wsgi.run_once": False,
    "werkzeug.server.shutdown": <function WSGIRequestHandler.make_environ.<locals>.shutdown_server at 0x10f00c950>,
    "SERVER_SOFTWARE": "Werkzeug/0.14.1",
    "REQUEST_METHOD": "GET",
    "SCRIPT_NAME": "",
    "PATH_INFO": "/article/8",
    "QUERY_STRING": "",
    "REMOTE_ADDR": "127.0.0.1",
    "REMOTE_PORT": 62057,
    "SERVER_NAME": "127.0.0.1",
    "SERVER_PORT": "5000",
    "SERVER_PROTOCOL": "HTTP/1.1",
    "HTTP_HOST": "127.0.0.1: 5000",
    "HTTP_CONNECTION": "keep-alive",
    "HTTP_PRAGMA": "no-cache",
    "HTTP_CACHE_CONTROL": "no-cache",
    "HTTP_UPGRADE_INSECURE_REQUESTS": "1",
    "HTTP_USER_AGENT": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.99 Safari/537.36",
    "HTTP_ACCEPT": "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8",
    "HTTP_REFERER": "http://127.0.0.1:5000/contents/Google%20Adsence",
    "HTTP_ACCEPT_ENCODING": "gzip, deflate, br",
    "HTTP_ACCEPT_LANGUAGE": "ja-JP,ja;q=0.9,en-US;q=0.8,en;q=0.7",
    "HTTP_COOKIE": "gsScrollPos-1901=; gsScrollPos-2345=0; gsScrollPos-1735=0; _ga=GA1.1.1286324805.1530497168; session=eyJ1c2VybmFtZSI6ImFkbWluIn0.Dh3nXg.HvlJWyVbh3Zsa2-6fEi_lMVaDJo; _gid=GA1.1.316787490.1532584271; _gat_gtag_UA_106883433_1=1",
    "werkzeug.request": <Request "http://127.0.0.1:5000/article/8" [GET]>
}
```
※ local環境でChromeからテストした情報です。

httpのヘッダーの情報が入っていることがわかります。
Google Analyticsもきっと、ヘッダーの情報を収集しているんでしょうね。


貯めておくと面白そうな情報が色々ありました。

key|説明|value
---|-----|-----------
REMOTE_ADDR|IPアドレス|127.0.0.1
HTTP_USER_AGENT|ブラウザやOSの情報|Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.99 Safari/537.36
HTTP_CONNECTION|継続したコネクションかどうか|keep-alive
PATH_INFO|URLのパス|/article/8


## 取得する項目とDynamoDBのテーブル設計

以下の項目を取得します。
DynamoDBのテーブル設計も兼ねます。


Table: page_view_logs

field name|primary key|説明
----------|-----------|----
article_id|hash key|記事のID
view_at|range key|閲覧時刻'%Y-%m-%d %H:%M:%s'日本時間
remote_addr|　|IPアドレス
browser|　|利用したブラウザ
user_os|　|osの種類
is_keep_alive|　|継続したコネクションか


記事毎に集計できるように、記事IDと閲覧時刻をプライマリーキーにしました。この２項目は、URLとdatetimeから得ます。

６項目を取得していきます。

---

実装編はこちら

[リクエスト情報から訪問者の情報を集めてみる[Python Flask + DynamoDB]-2.実装編](https://www.suzu6.net/posts/34)

---
本ブログの構成について

- [AWSで始めるサーバレスなブログサービス](https://www.suzu6.net/posts/2)
- [CroudFrontとAPI Gatewayで自作ブログサイトを改善しました](https://www.suzu6.net/posts/7)
- [[Python] Flask入門とサーバレスアーキテクチャ](https://www.suzu6.net/posts/6)


リクエスト情報から訪問者の情報を集めてみる[Python Flask + DynamoDB]-1.設計編


2018年12月にブログを移行した際にこの機能は削除しました。

## はじめに

解析の勉強のため、このブログの統計情報を集めようと思い立ちました。  

この記事は、設計編からの続きとなります。

[リクエスト情報から訪問者の情報を集めてみる[Python Flask + DynamoDB]-1.設計編](https://www.suzu6.net/posts/33)

機能を実装していきましょう。

## ブログに実装する


記事にリクエストがきた場合は、記事IDとreuestsを`VieLogsManager`に渡します。
`insert()`が、記事の閲覧履歴を追加します。

```python
@article.route('/<article_id>', methods=['GET'])
def get_article(article_id='1'):
    """
    記事のテンプレートを返す
    """
    article = ArticleManager().get_article(article_id)
    sidebar = SidebarManager()
    # 閲覧記録を保存する。
    VieLogsManager().insert(article_id, request)
    return render_template('article.html', title=article.title, article=article, sidebar=sidebar)
```

**ViewLogModel.py**

```python
import os
import boto3
from boto3.dynamodb.conditions import Attr, Key

from datetime import datetime, timedelta, timezone


class VieLogsManager:
    """
    ページビュー記録の操作
    """
    def __init__(self):
        # Initialize connection with database
        session = boto3.session.Session(
            region_name=os.environ['MY_REGION'], 
            aws_access_key_id=os.environ['MY_ACCESS_KEY_ID'], 
            aws_secret_access_key=os.environ['MY_SECRET_ACCESS_KEY']
        )

        dynamodb = session.resource('dynamodb')

        # Connect to db table
        self.table_name = 'page_view_logs'
        self.table = dynamodb.Table(self.table_name)
        self.JST = timezone(timedelta(hours=+9), 'JST')
    
    def insert(self, article_id, request):
        """
        閲覧履歴を記録する。
        """
        # if request.environ['REMOTE_ADDR'] is '127.0.0.1':
        #     # localでのテストは集計しない
        #     return

        http_connection = 'close'
        try:
            http_connection = request.environ['HTTP_CONNECTION']
        except Exception, e:
            print(e)

        item = {
            'article_id': article_id,
            'view_at': datetime.now(self.JST).strftime('%Y-%m-%d %H:%M:%s'), 
            'remote_addr': request.environ['REMOTE_ADDR'],
            'is_keep_alive': self.is_keep_alive(http_connection),
            'browser': self.accessed_browser(request.environ['HTTP_USER_AGENT']),
            'user_os': self.user_os(request.environ['HTTP_USER_AGENT'])
        }
        
        # 追加する
        responce = self.table.put_item(
            TableName=self.table_name,
            Item=item
        )
        return responce
```

クラス内のメソッドは順次解説します。

**解説**

`__init__(self)`では、クラスの呼び出し時にDynamoDBとの接続をします。
キー情報は、環境変数に入れてあります。

`put_item()`では、保存する項目をdictで渡せます。DynamoDbではプライマリーキーを必ず渡しましょう。


### コネクションの有無

```python
    @staticmethod
    def is_keep_alive(http_connection):
        return http_connection == 'keep-alive'
```

```
20180728追記：
request.environ['HTTP_CONNECTION']がローカルでは取得できましたが、Lamabda上ではKeyErrorとなっていしまいます。
現在、コネクションの有無は判定できません。
```

### ユーザーのブラウザを判定

`HTTP_USER_AGENT`から、ユーザーのブラウザを判定する関数です。
下記を参考にしました。

[UserAgentからOS/ブラウザなどの調べかたのまとめ](https://qiita.com/nightyknite/items/b2590a69f2e0135756dc)

keywordsに判定するkeyとブラウザをおきます。
異なるブラウザも同じキーワードが入っているため、判定する順序に気をつけてください。

※[python3.6からdictの順序が保たれます](http://www.freia.jp/taka/blog/python3-hash-randomie/index.html)。


```python
    @staticmethod
    def accessed_browser(http_user_agent):
        """
        return accessed browser
        """
        http_user_agent = http_user_agent.lower()
        keywords = {
            'trident': 'IE11',
            'edge': 'Edge',
            'chrome': 'Chrome', 
            'opr': 'Opera', 
            'firefox': 'Firefox',
            'safari': 'safari'
        }
        for key, val in keywords.items():
            if http_user_agent.find(key) > -1:
                return val
        return 'othres'
```

対象は、IE11, Edge, Chrome, Opera, Firefox, safariとその他otherとします。

### ユーザーのOSを判定

OSも同じく、`HTTP_USER_AGENT`から判定します。
[参考](https://qiita.com/nightyknite/items/b2590a69f2e0135756dc)も同じ。

```python
    @staticmethod
    def user_os(http_user_agent):
        """
        return user os
        """
        http_user_agent = http_user_agent.lower()

        keywords = {
            'nt 10.0': 'Windows 10',
            'mac': 'Mac', 
            'android': 'Android',
            'iphone': 'iPhone',
            'nt 6.1': 'Windows 7',
            'nt 5': 'Windows XP', 
            'linux': 'Linux'
        }
        for key, val in keywords.items():
            if http_user_agent.find(key) > -1:
                return val
        return 'othres'
```

OSでは、パソコンの他にスマホやタブレットも判定できるようです。
この関数では、Windows XP, 7, 10, Mac, Linux, iPhone, Androidを判定します。

## 結果

local環境で動かした結果です。

![table_page_view_logs](https://www.suzu6.net/images/posts/33/table_page_view_logs.png)

ChromeとFireFox、それからMacを認識できていますね。
その他の、ブラウザとOSは手元にないため、運用しつつテストしていこうと思います。


## IPアドレスの取り扱いについて

IPアドレスによって個人情報までばれてしまうと、心配になる方がいらっしゃるかと思います。
当サイトでは、IPアドレスを使って個人を特定することはありません。
あくまでも、統計解析の材料として扱います。

個人のIPアドレスで分かるのは、最寄りのルーター位置のみで、最大でも市区町村単位までです。
また、IPアドレスはリクエスト先や途中でも、常に公開されるものです。

> **IPアドレスから得られる情報**  
> IPアドレスの経路を確認した場合に、ルータのホスト名から位置を推定できる場合もあります。しかし、その場合であっても、追跡できるのは最寄りのルータまでとなり、そこから先の詳細な住所などはわかりません。
> 弊社では専任の調査員による位置情報調査を行っていますが、ルータ単位での位置判定は、最大で市区町村単位となっています。  
> 引用元：[IPアドレスから個人情報はどこまでわかる？](http://www.geolocation.co.jp/learn/dns/26.html)

ご自身のIPアドレスの情報は[IPアドレスから住所を検索](http://keiromichi.com/index.php)から確認できます。
私が試した結果は、位置情報は隣の市が表示されました。

それでも心配な方は、セキュリティソフトやファイヤーウォールの設定をしっかりと見直してください。

## おわりに

機能の構築と本記事の作成の中で、ウェブの解析ソフトが見ている情報や情報の扱いまで学ぶことが出来ました。
ネットワークの技術も、これを機に調べていこうと思います。

収集したデータを使った解析は、また次回行います。

最後までご覧いただき、ありがとうございます。

---

設計編はこちら
[リクエスト情報から訪問者の情報を集めてみる[Python Flask + DynamoDB]-1.設計編](https://www.suzu6.net/posts/33)

---
本ブログの構成について

- [AWSで始めるサーバレスなブログサービス](https://www.suzu6.net/posts/2)
- [CroudFrontとAPI Gatewayで自作ブログサイトを改善しました](https://www.suzu6.net/posts/7)
- [[Python] Flask入門とサーバレスアーキテクチャ](https://www.suzu6.net/posts/6)

Got error number	概要
139	１レコード（行）当たりの容量制限に引っかかっている。
168	ディレクトリ内の容量制限に引っかかっている。