Amazon Aurora DSQLハンズオン：SQL実行からデータ可視化まで

By /

はい、承知いたしました。Amazon Aurora DSQLハンズオン：SQL実行からデータ可視化までを約5000語で詳細に解説する記事を作成します。

Amazon Aurora DSQLハンズオン：SQL実行からデータ可視化まで

はじめに

クラウドネイティブなリレーショナルデータベースサービスであるAmazon Auroraは、高いパフォーマンス、可用性、そしてMySQLおよびPostgreSQLとの互換性により、多くの企業で採用されています。Auroraの利用を始めるにあたり、SQLの実行からデータ可視化までの一連のプロセスを理解することは重要です。

本記事では、Amazon Aurora DSQL（Data Definition Language、Data Manipulation Language、Data Control Language）の基本的な使い方を、実践的なハンズオン形式で解説します。Amazon Aurora環境のセットアップから、SQLコマンドの実行、そして実行結果のデータ可視化までをステップバイステップで説明します。

対象読者

  • Amazon Auroraをこれから利用する開発者、データベース管理者
  • SQLの基礎知識があり、Aurora環境でSQLを実行してみたい方
  • データベースのデータを可視化して分析したい方

前提条件

  • AWSアカウントをお持ちであること
  • AWSマネジメントコンソールへのアクセス権限があること
  • 基本的なSQLの知識（SELECT、INSERT、UPDATE、DELETEなど）があること

ハンズオンのゴール

本ハンズオンでは、以下のことを習得することをゴールとします。

  1. Amazon Auroraクラスタの作成
  2. Auroraクラスタへの接続（EC2インスタンスからの接続、Cloud9からの接続）
  3. データベースとテーブルの作成
  4. データの投入、更新、削除
  5. 基本的なSQLクエリの実行
  6. クエリ結果のデータ可視化（Tableau、QuickSightなど）

1. Amazon Auroraクラスタの作成

まず、Amazon Auroraのクラスタを作成します。以下の手順でAuroraクラスタを作成します。

  1. AWSマネジメントコンソールにログインし、RDS（リレーショナルデータベースサービス）を検索して選択します。

  2. RDSダッシュボードで、「データベースの作成」をクリックします。

  3. データベースの作成方法を選択します。ここでは「標準作成」を選択します。

  4. エンジンのオプションで、「Amazon Aurora」を選択します。

  5. Auroraエディションを選択します。ここでは「MySQL互換」または「PostgreSQL互換」を選択します。今回は「MySQL互換」を選択します。

  6. Auroraのバージョンを選択します。最新の安定版を選択することをお勧めします。

  7. テンプレートを選択します。「開発/テスト」を選択すると、コストを抑えられます。本番環境では「本番」を選択してください。

  8. 設定を指定します。

    • DB クラスター識別子: クラスタの名前を入力します（例: aurora-hands-on）。
    • マスターユーザー名: データベースの管理者ユーザー名を入力します（例: admin）。
    • マスターパスワード: 管理者ユーザーのパスワードを入力します（確認のため再入力も必要です）。必ず安全なパスワードを設定してください。

  9. DBインスタンスのサイズを選択します。「db.t3.small」など、小さめのインスタンスタイプを選択するとコストを抑えられます。

  10. ストレージの設定は、デフォルトのままで構いません。

  11. 接続設定を行います。

    • 仮想プライベートクラウド (VPC): 既存のVPCを選択するか、新規に作成します。
    • サブネットグループ: VPC内のサブネットグループを選択します。
    • パブリックアクセス: EC2インスタンスから接続する場合は「はい」、Cloud9から接続する場合は「いいえ」を選択します。
    • VPCセキュリティグループ: 新規に作成するか、既存のものを選択します。セキュリティグループの設定は後ほど調整します。

  12. データベースのオプションを設定します。

    • データベース名: データベースの名前を入力します（例: hands_on_db）。
    • DB クラスターのパラメータグループ: デフォルトのままで構いません。

  13. その他の設定は、必要に応じて調整します。

  14. 「データベースの作成」をクリックします。

Auroraクラスタの作成には数分かかる場合があります。ステータスが「利用可能」になるまで待ちます。

2. Auroraクラスタへの接続

Auroraクラスタが作成されたら、実際に接続してみましょう。ここでは、EC2インスタンスから接続する方法と、Cloud9から接続する方法を解説します。

2.1. EC2インスタンスからの接続

EC2インスタンスからAuroraクラスタに接続するには、以下の手順が必要です。

  1. EC2インスタンスの作成: EC2インスタンスを作成し、Auroraクラスタと同じVPCに配置します。

  2. セキュリティグループの設定:

    • Auroraクラスタのセキュリティグループで、EC2インスタンスからのMySQL(またはPostgreSQL)トラフィックを許可するインバウンドルールを追加します。ソースはEC2インスタンスのセキュリティグループを指定します。
    • EC2インスタンスのセキュリティグループで、アウトバウンドトラフィックが許可されていることを確認します。

  3. MySQLクライアントのインストール: EC2インスタンスにMySQLクライアントをインストールします。

    bash
    sudo apt update
    sudo apt install mysql-client

  4. Auroraクラスタへの接続: 以下のコマンドでAuroraクラスタに接続します。

    bash
    mysql -h <Auroraのエンドポイント> -u <マスターユーザー名> -p

    <Auroraのエンドポイント>は、Auroraクラスタの詳細ページに表示されるエンドポイントです。<マスターユーザー名>は、クラスタ作成時に指定したマスターユーザー名です。

2.2. Cloud9からの接続

Cloud9からAuroraクラスタに接続するには、以下の手順が必要です。

  1. Cloud9環境の作成: Cloud9環境を作成し、Auroraクラスタと同じVPCに配置します。

  2. セキュリティグループの設定:

    • Auroraクラスタのセキュリティグループで、Cloud9環境からのMySQL(またはPostgreSQL)トラフィックを許可するインバウンドルールを追加します。ソースはCloud9環境のセキュリティグループを指定します。
    • Cloud9環境のセキュリティグループで、アウトバウンドトラフィックが許可されていることを確認します。

  3. MySQLクライアントのインストール: Cloud9環境にMySQLクライアントがインストールされていることを確認します。通常はデフォルトでインストールされています。

  4. Auroraクラスタへの接続: Cloud9ターミナルから、以下のコマンドでAuroraクラスタに接続します。

    bash
    mysql -h <Auroraのエンドポイント> -u <マスターユーザー名> -p

    <Auroraのエンドポイント>は、Auroraクラスタの詳細ページに表示されるエンドポイントです。<マスターユーザー名>は、クラスタ作成時に指定したマスターユーザー名です。

3. データベースとテーブルの作成

Auroraクラスタに接続できたら、データベースとテーブルを作成します。ここでは、MySQLクライアントを使用してSQLコマンドを実行します。

  1. データベースの作成: 以下のSQLコマンドでデータベースを作成します。

    sql
    CREATE DATABASE hands_on_db;
    USE hands_on_db;

  2. テーブルの作成: 以下のSQLコマンドでテーブルを作成します。ここでは、productsテーブルとcustomersテーブルを作成します。

    “`sql
    CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    category VARCHAR(255)
    );

    CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    address VARCHAR(255)
    );
    “`

4. データの投入、更新、削除

テーブルを作成したら、データを投入、更新、削除してみましょう。

  1. データの投入: 以下のSQLコマンドでデータを投入します。

    “`sql
    INSERT INTO products (name, price, category) VALUES
    (‘ノートパソコン’, 1200.00, ‘エレクトロニクス’),
    (‘スマートフォン’, 800.00, ‘エレクトロニクス’),
    (‘コーヒーメーカー’, 50.00, ‘家電’),
    (‘Tシャツ’, 25.00, ‘アパレル’);

    INSERT INTO customers (name, email, address) VALUES
    (‘山田太郎’, ‘[email protected]’, ‘東京都’),
    (‘田中花子’, ‘[email protected]’, ‘大阪府’),
    (‘佐藤健’, ‘[email protected]’, ‘福岡県’);
    “`

  2. データの更新: 以下のSQLコマンドでデータを更新します。

    sql
    UPDATE products SET price = 1300.00 WHERE name = 'ノートパソコン';

  3. データの削除: 以下のSQLコマンドでデータを削除します。

    sql
    DELETE FROM customers WHERE email = '[email protected]';

5. 基本的なSQLクエリの実行

データの投入、更新、削除ができたら、基本的なSQLクエリを実行してみましょう。

  1. データの検索: 以下のSQLコマンドでデータを検索します。

    sql
    SELECT * FROM products;
    SELECT name, price FROM products WHERE category = 'エレクトロニクス';
    SELECT * FROM customers WHERE address LIKE '%東京%';

  2. データの集計: 以下のSQLコマンドでデータを集計します。

    sql
    SELECT category, AVG(price) AS average_price FROM products GROUP BY category;
    SELECT COUNT(*) AS total_products FROM products;

  3. JOIN: 複数のテーブルを結合してデータを検索します。ここでは、ordersテーブルを作成し、productsテーブルとcustomersテーブルを結合して、注文情報を取得します。

    “`sql
    CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    product_id INT,
    order_date DATE,
    quantity INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
    );

    INSERT INTO orders (customer_id, product_id, order_date, quantity) VALUES
    (1, 1, ‘2023-10-26’, 1),
    (1, 3, ‘2023-10-26’, 2),
    (2, 2, ‘2023-10-27’, 1),
    (3, 4, ‘2023-10-28’, 3);

    SELECT
    c.name AS customer_name,
    p.name AS product_name,
    o.order_date,
    o.quantity
    FROM
    orders o
    JOIN
    customers c ON o.customer_id = c.id
    JOIN
    products p ON o.product_id = p.id;
    “`

6. クエリ結果のデータ可視化

SQLクエリを実行して取得したデータを、TableauやQuickSightなどのツールを使って可視化します。ここでは、Tableauを使った可視化の例を説明します。

  1. Tableauのインストール: Tableau Desktopをインストールします。Tableau Publicは無料で利用できます。

  2. Tableauへの接続: Tableauを起動し、データベースへの接続を設定します。

    • 接続の種類として「MySQL」または「PostgreSQL」を選択します。
    • サーバー名にAuroraのエンドポイントを入力します。
    • データベース名、ユーザー名、パスワードを入力します。

  3. データの選択: 可視化するテーブルを選択します。

  4. グラフの作成: Tableauのインターフェースを使って、グラフを作成します。

    • ディメンションとメジャーをドラッグ＆ドロップして、グラフの種類を選択します。
    • フィルターやソートを追加して、データを絞り込んだり並べ替えたりします。

    例えば、productsテーブルのcategoryごとのaverage_priceを棒グラフで表示することができます。また、ordersテーブルとproductsテーブルを結合して、商品ごとの注文数をグラフで表示することもできます。

  5. QuickSightでの可視化: QuickSightを利用してデータを可視化することも可能です。

    • AWSコンソールからQuickSightにアクセスします。
    • 新しいデータセットを作成し、Auroraクラスタに接続します。
    • Tableauと同様に、ディメンションとメジャーを選択して、適切なグラフを作成します。
    • QuickSightはAWSのサービスであるため、AWS環境との連携が容易です。

その他のDML/DDL操作

これまでの基本的な操作に加えて、より高度なSQLの機能を紹介します。

トランザクション: 複数のSQL操作をまとめて処理し、データの整合性を保つことができます。

sql
START TRANSACTION;
UPDATE products SET price = price * 1.1 WHERE category = 'エレクトロニクス';
INSERT INTO orders (customer_id, product_id, order_date, quantity) VALUES (1, 1, '2023-10-29', 1);
COMMIT; -- または ROLLBACK;

インデックス: クエリのパフォーマンスを向上させるために、特定のカラムにインデックスを作成します。

sql
CREATE INDEX idx_category ON products (category);

ビュー: 複雑なクエリを簡略化するために、仮想的なテーブルを作成します。

“`sql
CREATE VIEW product_summary AS
SELECT category, AVG(price) AS average_price, COUNT(*) AS product_count
FROM products
GROUP BY category;

SELECT * FROM product_summary;
“`

ベストプラクティス

Amazon Auroraを利用する際のベストプラクティスをいくつか紹介します。

  • 適切なインスタンスサイズの選択: ワークロードに合わせて適切なインスタンスサイズを選択します。CPU、メモリ、IOPSなどを考慮して、パフォーマンスボトルネックを解消します。
  • インデックスの適切な設計: クエリの実行計画を分析し、適切なインデックスを作成します。過剰なインデックスは書き込みパフォーマンスに影響を与えるため、注意が必要です。
  • クエリの最適化: EXPLAINコマンドを使ってクエリの実行計画を確認し、必要に応じてクエリを書き換えます。
  • バックアップとリストア: 定期的なバックアップを設定し、障害発生時に迅速にデータを復旧できるようにします。
  • モニタリング: CloudWatchを使ってAuroraクラスタのパフォーマンスをモニタリングし、異常を早期に検知します。

トラブルシューティング

Auroraクラスタの利用中に発生する可能性のあるトラブルと、その解決策をいくつか紹介します。

  • 接続エラー: セキュリティグループの設定、ネットワーク設定、エンドポイントの確認を行います。
  • パフォーマンス低下: クエリの最適化、インデックスの確認、インスタンスサイズの変更を検討します。
  • ストレージ不足: ストレージの自動拡張を有効にするか、不要なデータを削除します。
  • 障害発生: バックアップからのリストア、フェイルオーバーを検討します。

まとめ

本ハンズオンでは、Amazon Auroraの基本的な使い方を解説しました。Auroraクラスタの作成から、SQLコマンドの実行、そしてデータ可視化までの一連のプロセスを実践することで、Auroraの利用を開始するための基礎知識を習得できたはずです。

Auroraは、高いパフォーマンス、可用性、そしてMySQL/PostgreSQLとの互換性により、様々なアプリケーションで利用できます。本記事を参考に、ぜひAuroraを活用して、より高度なデータ分析やアプリケーション開発に挑戦してみてください。

次のステップ

  • Amazon Auroraのドキュメントを読む: https://aws.amazon.com/jp/rds/aurora/
  • TableauやQuickSightのチュートリアルを試す
  • 実際にアプリケーションを開発して、Auroraに接続してみる

この記事が、Amazon Auroraの利用を始める皆様のお役に立てば幸いです。

コメントする

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

上部へスクロール