テスト用データベースの作成

このセクションでは、分析対象のデータが格納されるRDSインスタンス、およびRDSを配置するためのVPC関連のリソースとして、下記内容を作成していきます。

リソース 名前 説明
VPC my-vpc 10.0.0.0/21のアドレス範囲を持つ仮想ネットワーク
サブネット db-subnet-01, db-subnet-02, api-subnet DB用サブネット2つ(10.0.0.0/24, 10.0.1.0/24)と踏み台サーバ用サブネット(10.0.2.0/24)
インターネットゲートウェイ my-internet-gateway 踏み台サーバへのインターネットアクセス用
ルートテーブル api-route-table api-subnetからインターネットへのルーティング
セキュリティグループ db-sg, jump-sg RDS用とEC2(踏み台サーバ)用
DBサブネットグループ my-subnet-group RDS用のサブネットグループ
RDSインスタンス testdb MySQLデータベース
EC2インスタンス JumpServer 踏み台サーバ

これらのリソースはデータ分析の本質ではないため、今回は説明を省略します。

また、リソースの作成についても簡略化するため、CloudFormationテンプレートにより、リソースを一括で作成します。

📝 CloudFormationとは
CloudFormationは、AWSのリソースをテンプレート(YAML/JSON)で定義し、自動的に作成・管理できるサービスです。手動でリソースを一つずつ作成する代わりに、テンプレートをアップロードするだけで必要なリソースをまとめて構築できます。
IaC(Infrastructure as Code)ツールとしてはTerraformも広く使われていますが、Terraformは事前にインストールが必要です。一方、CloudFormationはAWSマネジメントコンソールからそのまま実行できるため、今回のハンズオンではCloudFormationを使用します。
なお、このセクションではCloudFormationテンプレートの内容を理解する必要はありません。テンプレートをそのままアップロードして環境を構築してください。

また、環境構築が完了した後、踏み台サーバにログインし、MySQLクライアントのインストールとテストデータの登録を手動で行います。

1. CloudFormationによる環境構築

CloudFormationのダッシュボードを開き、「スタックの作成」>「新しいリソースを使用(標準)」をクリックします。

その後、画面に表示されるスタックの作成をクリックします。

スタックの作成の設定を行っていきます。下記表に従い、設定を行ってください。設定後、次へをクリックしてください。

設定項目 値 設定の基準
テンプレートの準備 既存のテンプレートを選択 事前に作成したテンプレートファイルを使用するため
テンプレートソース テンプレートファイルのアップロード ローカルのテンプレートファイルを直接アップロードするため
テンプレートファイルのアップロード 以下のYAMLテンプレートを保存したファイル ハンズオン環境を構築するテンプレートを指定するため

アップロードするのは下記ファイルです。こちらをtemplate.ymlのような任意の名前でローカル環境に保存し、それをアップロードしてください。

AWSTemplateFormatVersion: '2010-09-09'
Description: Data Analysis Hands-on Environment

Parameters:
  DBMasterPassword:
    Type: String
    NoEcho: true
    Description: Password for RDS master user (admin)
    MinLength: 8

  LatestAmiId:
    Type: AWS::SSM::Parameter::Value<AWS::EC2::Image::Id>
    Default: /aws/service/ami-amazon-linux-latest/al2023-ami-kernel-6.1-x86_64

Resources:
  # ==================== VPC ====================
  VPC:
    Type: AWS::EC2::VPC
    Properties:
      CidrBlock: 10.0.0.0/21
      EnableDnsSupport: true
      EnableDnsHostnames: true
      Tags:
        - Key: Name
          Value: my-vpc

  # ==================== Subnets ====================
  DBSubnet01:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref VPC
      CidrBlock: 10.0.0.0/24
      AvailabilityZone: ap-northeast-1a
      Tags:
        - Key: Name
          Value: db-subnet-01

  DBSubnet02:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref VPC
      CidrBlock: 10.0.1.0/24
      AvailabilityZone: ap-northeast-1c
      Tags:
        - Key: Name
          Value: db-subnet-02

  APISubnet:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref VPC
      CidrBlock: 10.0.2.0/24
      AvailabilityZone: ap-northeast-1a
      MapPublicIpOnLaunch: true
      Tags:
        - Key: Name
          Value: api-subnet

  # ==================== Internet Gateway ====================
  InternetGateway:
    Type: AWS::EC2::InternetGateway
    Properties:
      Tags:
        - Key: Name
          Value: my-internet-gateway

  VPCGatewayAttachment:
    Type: AWS::EC2::VPCGatewayAttachment
    Properties:
      VpcId: !Ref VPC
      InternetGatewayId: !Ref InternetGateway

  # ==================== Route Table ====================
  PublicRouteTable:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref VPC
      Tags:
        - Key: Name
          Value: api-route-table

  PublicRoute:
    Type: AWS::EC2::Route
    DependsOn: VPCGatewayAttachment
    Properties:
      RouteTableId: !Ref PublicRouteTable
      DestinationCidrBlock: 0.0.0.0/0
      GatewayId: !Ref InternetGateway

  APISubnetRouteTableAssociation:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref APISubnet
      RouteTableId: !Ref PublicRouteTable

  # ==================== Security Groups ====================
  DBSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Security group for RDS
      GroupName: db-sg
      VpcId: !Ref VPC
      Tags:
        - Key: Name
          Value: db-sg

  JumpSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Security group for Jump Server
      GroupName: jump-sg
      VpcId: !Ref VPC
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: 22
          ToPort: 22
          CidrIp: 0.0.0.0/0
      Tags:
        - Key: Name
          Value: jump-sg

  DBFromJumpIngress:
    Type: AWS::EC2::SecurityGroupIngress
    Properties:
      GroupId: !Ref DBSecurityGroup
      IpProtocol: tcp
      FromPort: 3306
      ToPort: 3306
      SourceSecurityGroupId: !Ref JumpSecurityGroup

  # ==================== DB Subnet Group ====================
  DBSubnetGroup:
    Type: AWS::RDS::DBSubnetGroup
    Properties:
      DBSubnetGroupName: my-subnet-group
      DBSubnetGroupDescription: my subnet group
      SubnetIds:
        - !Ref DBSubnet01
        - !Ref DBSubnet02

  # ==================== RDS ====================
  RDSInstance:
    Type: AWS::RDS::DBInstance
    Properties:
      DBInstanceIdentifier: testdb
      Engine: mysql
      DBInstanceClass: db.t3.micro
      AllocatedStorage: 20
      MasterUsername: admin
      MasterUserPassword: !Ref DBMasterPassword
      DBSubnetGroupName: !Ref DBSubnetGroup
      VPCSecurityGroups:
        - !Ref DBSecurityGroup
      PubliclyAccessible: false
      AvailabilityZone: ap-northeast-1a

  # ==================== EC2 (Jump Server) ====================
  JumpServer:
    Type: AWS::EC2::Instance
    Properties:
      InstanceType: t3.micro
      ImageId: !Ref LatestAmiId
      SubnetId: !Ref APISubnet
      SecurityGroupIds:
        - !Ref JumpSecurityGroup
      Tags:
        - Key: Name
          Value: JumpServer

Outputs:
  RDSEndpoint:
    Description: RDS Endpoint Address
    Value: !GetAtt RDSInstance.Endpoint.Address
  JumpServerInstanceId:
    Description: Jump Server Instance ID
    Value: !Ref JumpServer
  VPCId:
    Description: VPC ID
    Value: !Ref VPC

「次へ」をクリックし、スタックの詳細を設定します。

設定項目 値 設定の基準
スタック名 data-analysis-handson ハンズオン用のスタックであることを識別するため
DBMasterPassword 任意のパスワード RDSへの接続パスワード。後ほど使用するため控えておくこと
LatestAmiId デフォルト Amazon Linux 2023の最新AMIを自動取得するため

「次へ」をクリックし、スタックオプションはデフォルトのまま「次へ」をクリックします。

確認画面で内容を確認し、「送信」をクリックします。

そうするとCloudFormationのスタックが作成されます。ステータスがCREATE_IN_PROGRESSとなり、作成中のステータスであることがわかります。スタックの作成には10〜15分ほどかかります。

ステータスがCREATE_COMPLETEになれば、リソースの作成が完了です。

2. RDSへの接続先を確認

今回作成されたリソースの中から、データ分析を行う上で必要となる項目を確認しておきます。

スタックの詳細画面で「出力」タブをクリックしてください。以下の値が表示されます。

キー 説明 用途
RDSEndpoint RDSインスタンスのエンドポイントアドレス テストデータの登録やGlue Connectionの作成時に使用

また、RDSのコンソールから、testdbの詳細画面でも確認ができます。

また、RDSへの接続には以下の情報も必要です。これらはCloudFormationのスタック作成時に設定した値です。

項目 値 確認方法
ユーザ名 admin CloudFormationテンプレートで固定値として設定済み
パスワード スタック作成時に指定したDBMasterPassword CloudFormationの画面には表示されないため、設定時に控えた値を使用
ポート 3306 MySQLのデフォルトポート

これらの値は後のステップで繰り返し使用するため、テキストエディタなどに控えておいてください。

3. データの登録

続いて、作成したRDSインスタンスに、テストデータを登録していきます。RDSにアクセスするための踏み台サーバとなるEC2インスタンスを作成しているので、これを使ってRDSインスタンスに接続し、データを登録していきます。

3.1 EC2インスタンスにログイン

まずはEC2インスタンスから、先程CloudFormationで作成したJumpServerをチェックし、接続をクリックします。

その後、デフォルトの状態で構いませんので、接続をクリックします。

以下のように接続完了の画面が表示されれば、無事にEC2インスタンスにログインできています。

📝 EC2 Instance Connectとは
EC2 Instance Connectとは、AWSマネジメントコンソールのブラウザ上から直接EC2インスタンスにSSH接続できる機能です。ローカルPCにSSHクライアントをインストールしたり、秘密鍵ファイルを管理したりする必要がなく、ブラウザだけで簡単にサーバへアクセスできます。Amazon Linux 2023やUbuntuなど、対応するAMIで利用可能です。

4. MySQLクライアントのインストール

ログインができたら、MySQLクライアントをインストールします。以下のコマンドによりMySQLクライアントをインストールします。

sudo dnf install -y mariadb105

5. MySQLへの接続

インストールが終わったら、RDSに接続します。以下のコマンドで接続が可能です。RDSのエンドポイントは、先程メモした、CloudFormationで作成されたRDSのエンドポイントに置き換えます。

mysql -h RDSのエンドポイント -u admin -p

パスワードの入力を求められるので、こちらもCloudFormation実行時にパラメータとして指定した、RDSのパスワードを入力します。

MariaDB [(none)]>のような待受状態になれば、無事にRDSインスタンスに接続できています。

5.1 テストデータの登録

SQLを実行し、テストデータを登録していきます。

まずは、datalake_testというデータベースを作成します。

CREATE DATABASE datalake_test;

続いて、usersというテーブルを作ります。

CREATE TABLE datalake_test.users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INT,
    gender INT
);

さらに、usersに10件程度のレコードを追加します。

INSERT INTO datalake_test.users (name, age, gender) VALUES
('Yamada Taro',      28, 1),
('Suzuki Hanako',    25, 2),
('Sato Kenji',       34, 1),
('Tanaka Yuki',      22, 2),
('Kobayashi Shota',  31, 1),
('Watanabe Aiko',    29, 2),
('Ito Naoki',        40, 1),
('Nakamura Mika',    27, 2),
('Kato Kenta',       36, 1),
('Yamamoto Rina',    33, 2);

最後に、無事にデータが登録されているかを、SELECTにより確認します。

SELECT * FROM datalake_test.users;

以下のように10件のレコードが表示されれば、ここまでは成功です。

+----+-----------------+------+--------+
| id | name            | age  | gender |
+----+-----------------+------+--------+
|  1 | Yamada Taro     |   28 |      1 |
|  2 | Suzuki Hanako   |   25 |      2 |
|  3 | Sato Kenji      |   34 |      1 |
|  4 | Tanaka Yuki     |   22 |      2 |
|  5 | Kobayashi Shota |   31 |      1 |
|  6 | Watanabe Aiko   |   29 |      2 |
|  7 | Ito Naoki       |   40 |      1 |
|  8 | Nakamura Mika   |   27 |      2 |
|  9 | Kato Kenta      |   36 |      1 |
| 10 | Yamamoto Rina   |   33 |      2 |
+----+-----------------+------+--------+
10 rows in set (0.000 sec)