AWS RDSでデータベースを作成しCloudShellから接続してSQL文からデータを格納する

 AWS RDSを利用してデータベースを作成していきます。AWS RDSはリレーショナルデータベースで、構造的な関係を持つデータの格納に便利です。今回はクラウド環境で使えるAWS RDSを使って、データベースを作成していきます。今回の目標は、「RDSでデータベースを作成し、CloudShellから接続して、SQL文からデータを格納する」ことです。本当はデータをプログラムで分析し、データベースに格納したいところですが、データベースの基本を確認するために、今回はSQL文を利用していきます。

(1) AWS RDSにデータベースを作成

AWS マネージメントコンソールからRDSにはいり、データベースを作成していきます。今回はできる限り簡易的にやっていきます。画面の「データベースの作成」ボタンを押します。

データベース作成方法を選択 -> 簡単に作成

エンジンのタイプ -> MySQL

DBインスタンスタイプ -> 無料利用枠

DBインスタンス識別子 -> database-001

マスターユーザー名 -> admin (そのまま)

認証情報管理 -> セルフマネージド

マスターパスワード -> 任意ですが授業の場合は「LinuxOS99」とします。(@は使えません)

データベースの作成をクリックします。下記の内容が出てきてもそのまま閉じるで構いません。

AWS Acdemyでやっている場合は、エラーが出る場合もありますが、そのままやっていきます。データベースの作成には少し時間がかかります(3分程度)。

ステータスが利用可能になり、これで作成ができました。

(2) パブリックアクセス可能に変更する

次に、データベースをCloudShellや外部プログラムから接続できるように、設定を変更します。

DB識別子「database-001」をクリック

画面右上の「変更」をクリック

接続 -> 追加設定をクリック -> 「パブリック接続可能」に変更

画面をスクロールして一番下にいき、続行をクリック

「すぐに適用」に変更し、DBインスタンスを変更をクリック

ステータスが変更中となり、しばらくすると、利用可能になります。これでパブリックアクセス可能に変更ができました。

(3) セキュリティグループを変更して、データベースに接続できるようにする

次に、セキュリティグループを変更して、データベースに接続できるようにします。つまりファイアーウォールでデータベースに接続できない状況となっています。

DB識別子のdatabase-001をクリック、

セキュリティ -> VPCセキュリティグループをクリック

セキュリティグループIDをクリック

インバウンドのルールを編集

ルールを追加

MySQL/Aurola、0.0.0.0/0 (どこからでもアクセス可能)にして、ルールを保存をクリック

ポートは3306のまま

これで外部からのデータベースアクセスが許可されました。

RDSに戻ります。

(4) Cloud Shellから接続する

これでデータベースにアクセスができるようになったので、今回はCloud Shellを使って、データベースにアクセスします。Cloud Shellは画面上部のボタンをクリックします。

クリックすると、画面下部にプロンプトが出てきます。

このプロンプトに次のコマンドを入力します。

mysql -h エンドポイント(各自のもの) -P 3306 -u admin -p

エンドポイントはDB識別子をクリックして、接続とセキュリティ画面にあるので、確認してください。

パスワード(授業ではLinuxOS99)を入れてログインできれば成功です。データベースで何があるかを確認するには、

show databases;

とします。最後に;(セミコロン)があるので忘れないようにしてください。

MySQLからログアウトするには

exit;

とします。

(5) SQL文を使って、データをインポートする

データベースに接続ができたので、SQL文からデータをインポートします。段取りとしては

(i) データベースの作成(create)、データベースの指定(use)

(ii) テーブルの作成(create)

(iii) データの挿入(insert)

(iv) データの確認(select)

	-- データベースの作成
	CREATE DATABASE IF NOT EXISTS urayasu;
	USE urayasu;
	-- locations テーブルの作成
	CREATE TABLE IF NOT EXISTS locations (
	id INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL,
	latitude VARCHAR(255) NOT NULL,
	longitude VARCHAR(255) NOT NULL
	);
	-- distances テーブルの作成
	CREATE TABLE IF NOT EXISTS distances (
	id INT AUTO_INCREMENT PRIMARY KEY,
	from_location INT NOT NULL,
	to_location INT NOT NULL,
	distance FLOAT,
	FOREIGN KEY (from_location) REFERENCES locations(id),
	FOREIGN KEY (to_location) REFERENCES locations(id)
	);
	-- urayasu_bunkazai.csvのデータをlocationsテーブルに挿入
	INSERT INTO locations (name, latitude, longitude)
	VALUES
	('宝城院', '35.66167', '139.8911'),
	('浦安市郷土博物館', '35.65536', '139.90122'),
	('旧大塚家住宅', '35.66187', '139.8927'),
	('花蔵院', '35.66078', '139.89685'),
	('豊受神社', '35.66067', '139.89758'),
	('善福寺', '35.66989', '139.89277'),
	('大蓮寺', '35.66203', '139.89049'),
	('庚申塔', '35.66408', '139.8938'),
	('旧宇田川家住宅', '35.66196', '139.89228'),
	('清瀧神社', '35.66238', '139.89122'),
	('稲荷神社', '35.67241', '139.89154'),
	('浦安駅','35.66604888473298','139.89326652577006');
	-- distancesテーブルにデータをインポート
	INSERT INTO distances (from_location, to_location, distance)
	VALUES
	(1, 1, 0.0), (1, 2, 1647.0), (1, 3, 174.0), (1, 4, 997.0), (1, 5, 1092.0), (1, 6, 1010.0), (1, 7, 69.0), (1, 8, 713.0), (1, 9, 92.0), (1, 10, 133.0), (1, 11, 1462.0), (1, 12, 919.0),
	(2, 1, 1819.0), (2, 2, 0.0), (2, 3, 1928.0), (2, 4, 1100.0), (2, 5, 1164.0), (2, 6, 2534.0), (2, 7, 1888.0), (2, 8, 1809.0), (2, 9, 1846.0), (2, 10, 1887.0), (2, 11, 2932.0), (2, 12, 2443.0),
	(3, 1, 872.0), (3, 2, 1643.0), (3, 3, 0.0), (3, 4, 639.0), (3, 5, 710.0), (3, 6, 1683.0), (3, 7, 1118.0), (3, 8, 823.0), (3, 9, 900.0), (3, 10, 941.0), (3, 11, 2248.0), (3, 12, 1592.0),
	(4, 1, 997.0), (4, 2, 1100.0), (4, 3, 1107.0), (4, 4, 0.0), (4, 5, 65.0), (4, 6, 1466.0), (4, 7, 1243.0), (4, 8, 606.0), (4, 9, 1025.0), (4, 10, 632.0), (4, 11, 1864.0), (4, 12, 1375.0),
	(5, 1, 1068.0), (5, 2, 960.0), (5, 3, 1178.0), (5, 4, 65.0), (5, 5, 0.0), (5, 6, 1537.0), (5, 7, 1314.0), (5, 8, 677.0), (5, 9, 1096.0), (5, 10, 703.0), (5, 11, 1935.0), (5, 12, 1446.0),
	(6, 1, 1370.0), (6, 2, 2707.0), (6, 3, 1461.0), (6, 4, 1826.0), (6, 5, 2153.0), (6, 6, 0.0), (6, 7, 1251.0), (6, 8, 1323.0), (6, 9, 1379.0), (6, 10, 1314.0), (6, 11, 462.0), (6, 12, 669.0),
	(7, 1, 69.0), (7, 2, 1893.0), (7, 3, 243.0), (7, 4, 1243.0), (7, 5, 1338.0), (7, 6, 1251.0), (7, 7, 0.0), (7, 8, 1076.0), (7, 9, 162.0), (7, 10, 202.0), (7, 11, 1374.0), (7, 12, 1140.0),
	(8, 1, 713.0), (8, 2, 1622.0), (8, 3, 804.0), (8, 4, 606.0), (8, 5, 677.0), (8, 6, 963.0), (8, 7, 782.0), (8, 8, 0.0), (8, 9, 722.0), (8, 10, 656.0), (8, 11, 1362.0), (8, 12, 872.0),
	(9, 1, 864.0), (9, 2, 1635.0), (9, 3, 82.0), (9, 4, 630.0), (9, 5, 701.0), (9, 6, 1674.0), (9, 7, 1110.0), (9, 8, 815.0), (9, 9, 0.0), (9, 10, 933.0), (9, 11, 2240.0), (9, 12, 1583.0),
	(10, 1, 133.0), (10, 2, 1716.0), (10, 3, 224.0), (10, 4, 632.0), (10, 5, 1160.0), (10, 6, 954.0), (10, 7, 379.0), (10, 8, 656.0), (10, 9, 142.0), (10, 10, 0.0), (10, 11, 1405.0), (10, 12, 863.0),
	(11, 1, 1493.0), (11, 2, 2830.0), (11, 3, 1584.0), (11, 4, 1949.0), (11, 5, 2276.0), (11, 6, 462.0), (11, 7, 1374.0), (11, 8, 1446.0), (11, 9, 1502.0), (11, 10, 1437.0), (11, 11, 0.0), (11, 12, 1024.0),
	(12, 1, 547.0), (12, 2, 1884.0), (12, 3, 638.0), (12, 4, 1003.0), (12, 5, 1330.0), (12, 6, 798.0), (12, 7, 616.0), (12, 8, 500.0), (12, 9, 556.0), (12, 10, 491.0), (12, 11, 1196.0), (12, 12, 0.0);

view raw gistfile1.txt hosted with ❤ by GitHub

このSQL文は、データベース：urayasu、テーブル：locations、distances となっていて、locationsではid, 名前、緯度、経度を持ち、distancesではlocationsのidを外部キーとして、from_locationとto_locationで２拠点間の距離を格納しています。このように、locationsのidがdistancesの外部キーとなっており、リレーションがはられている構造です。

これをCloud Shellから文ごと確認して、データのインポートができるか確認してください。

インポートができたら

select * from locations;

select * from distances;

で確認しましょう。

これで、AWS RDSでデータベースを作成しCloudShellから接続してSQL文からデータを格納することができました。