Amazon-RDS

AWS LambdaでAmazon RDS(SQLserver)を操作してみた！

aws, lambda, rds コメントを残す

全般

RDS(SQLserver)を単発で作成して、いざRDSの設定を行うとなると、AWS Cloud9、SQLツールなどを使用した接続になると思いますが、あえて他に手段がないか調べたところAWS Lambdaがあるとのことで、試しに接続してみました！結論近道ではないです(^^;

前提

AWSアカウントを保有

環境構築

リージョン:東京
VPC:パブリックサブネット2個
RDS:SQLserver(Express Edition)
db.t3.micro
MasterUsername: admin
MasterUserPassword: "abcdefg12345"
※この環境はあくまで検証として作成しています。

AWSTemplateFormatVersion: '2010-09-09'

Parameters:
  AvailabilityZone1:
    Type: AWS::EC2::AvailabilityZone::Name
    Description: First availability zone for the public subnet
    Default: ap-northeast-1a
  AvailabilityZone2:
    Type: AWS::EC2::AvailabilityZone::Name
    Description: Second availability zone for the public subnet
    Default: ap-northeast-1c

Resources:
  VPC:
    Type: AWS::EC2::VPC
    Properties:
      CidrBlock: 10.0.0.0/16
      EnableDnsSupport: true
      EnableDnsHostnames: true
      Tags:
        - Key: Name
          Value: test-keiji-vpc

  InternetGateway:
    Type: AWS::EC2::InternetGateway
    Properties:
      Tags:
        - Key: Name
          Value: test-keiji-igw

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

  PublicSubnet1:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref VPC
      CidrBlock: 10.0.0.0/24
      AvailabilityZone: !Ref AvailabilityZone1
      MapPublicIpOnLaunch: true
      Tags:
        - Key: Name
          Value: test-keiji-public-subnet1

  PublicSubnet2:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref VPC
      CidrBlock: 10.0.1.0/24
      AvailabilityZone: !Ref AvailabilityZone2
      MapPublicIpOnLaunch: true
      Tags:
        - Key: Name
          Value: test-keiji-public-subnet2

  RouteTable:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref VPC
      Tags:
        - Key: Name
          Value: test-keiji-public-rt

  Route:
    Type: AWS::EC2::Route
    Properties:
      RouteTableId: !Ref RouteTable
      DestinationCidrBlock: 0.0.0.0/0
      GatewayId: !Ref InternetGateway

  SubnetRouteTableAssociation1:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref PublicSubnet1
      RouteTableId: !Ref RouteTable

  SubnetRouteTableAssociation2:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref PublicSubnet2
      RouteTableId: !Ref RouteTable

  RDSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Security group for RDS instance
      VpcId: !Ref VPC
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: 1433
          ToPort: 1433
          CidrIp: 0.0.0.0/0
      SecurityGroupEgress:
        - IpProtocol: -1
          FromPort: -1
          ToPort: -1
          CidrIp: 0.0.0.0/0
      Tags:
        - Key: Name
          Value: test-keiji-db-base-sg

  RDSInstance:
    Type: AWS::RDS::DBInstance
    Properties:
      DBInstanceIdentifier: test-keiji-db
      AllocatedStorage: 20
      DBInstanceClass: db.t3.micro
      Engine: sqlserver-ex
      EngineVersion: "15.00.4236.7.v1"
      MasterUsername: admin
      MasterUserPassword: "abcdefg12345"
      VPCSecurityGroups:
        - !Ref RDSecurityGroup
      DBSubnetGroupName: !Ref DBSubnetGroup
      MultiAZ: false
      StorageEncrypted: false
      AutoMinorVersionUpgrade: false
      PubliclyAccessible: true
      BackupRetentionPeriod: 0
      PreferredMaintenanceWindow: "Mon:03:00-Mon:04:00"
      MonitoringInterval: 60
      MonitoringRoleArn: !Sub "arn:${AWS::Partition}:iam::${AWS::AccountId}:role/rds-monitoring-role"
      DeletionProtection: false
      EnableCloudwatchLogsExports:
        - "error"
      StorageType: gp2
      MaxAllocatedStorage: 100
      Timezone: "Tokyo Standard Time"
      Tags:
        - Key: Name
          Value: test-keiji-db

  DBSubnetGroup:
    Type: AWS::RDS::DBSubnetGroup
    Properties:
      DBSubnetGroupDescription: Subnet group for RDS instance
      SubnetIds:
        - !Ref PublicSubnet1
        - !Ref PublicSubnet2
      Tags:
        - Key: Name
          Value: test-keiji-db-subnet-group

AWS Cloud9

  • ローカルのWindowsでライブラリをインストールして持っていくと依存関係などの環境によって、最終的にエラーとなるため、AWS Cloud9(AmazonLinux2環境)でライブラリを取得します。
  • AWS Cloud9セットアップ
  • Pythonのバージョン確認 
    python --version

    Python 3.8.16

  • これがデフォルトのバージョンになります。すでに世間のPythonのバージョンは3.12.4が安定したバージョンですかね。ベータ版ではないものです。
    この後に設定するLambda、レイヤーにおいてのバージョン指定は「Python 3.8.16」こちらで設定していきます。次のリンクは参考までに。

What's New in Python

  • 次のコマンドを実行します。 
    mkdir python
pip install pymssql -t python/
zip -r pymssql-layer.zip python/
  • 作成したzipファイルをローカルにダウンロードします。
    サイドバーにzipファイルが表示されているはずなので、クリックしてダウンロードを実行します。
  • Cloud9での作業はここまでです。
    zipファイルのディレクトリ構成

    pymssql-layer.zip
└── python
├── pymssql
├── pymssql.libs
└── pymssql-2.3.0.dist-info

LambdaからRDS(SQLserver)に接続していきます。

LambdaからRDS(SQLserver)に接続

  1. IAM画面へ移動し、ポリシー作成
    image.png

    {
"Version": "2012-10-17",
"Statement": [
    {
        "Effect": "Allow",
        "Action": [
            "rds:DescribeDBInstances",
            "rds:DescribeDBLogFiles",
            "rds:DownloadDBLogFilePortion",
            "rds:DescribeDBClusters"
        ],
        "Resource": "*"
    },
    {
        "Effect": "Allow",
        "Action": [
            "ec2:DescribeNetworkInterfaces",
            "ec2:CreateNetworkInterface",
            "ec2:DeleteNetworkInterface",
            "ec2:DescribeInstances",
            "ec2:AttachNetworkInterface"
        ],
        "Resource": "*"
    }
]
}
  2. ロールの作成
    • サービスまたはユースケース:Lambda
    • 先程作成したポリシーを選択
      image.png
  • Lambdaのコンソールに移動します。
    1. 一から作成
    2. ランタイム:Python 3.8
    3. アーキテクチャ:x86_64
    4. デフォルトの実行ロールの変更:作成したロール名
    5. VPC を有効化にチェックを付けて該当のサブネットを指定
    6. 関数の作成
    7. レイヤーからzipをアップロード
      image.png
      image.png
      image.png
  • レイヤーの適用
    デフォルトのライブラリ以外のレイヤーを追加しすぎると動作が遅くなるようです。
    5つ以内で設定にする必要がある。

関数へのレイヤーの追加

image.png
image.png
image.png

  • 次のコードをLambdaのコードエディタに貼り付けます。
    コードの内容は↓
  • ユーザーの作成
  • そのユーザーの実行権限を設定（全部乗せ）
  • データベースの作成:test_db
  • テーブルの作成:test_table
  • カラムを２つcolumn1(30文字以内),column2(30文字以内)
import pymssql
import logging

logger = logging.getLogger()
logger.setLevel(logging.INFO)

def lambda_handler(event, context):
    logger.info('Starting lambda_handler')

    # SQL Server connection details
    server = 'test-keiji-db.cp6c4mq6g518.ap-northeast-1.rds.amazonaws.com'
    user = 'admin'
    password = 'abcdefg12345'
    database = 'master'

    try:
        logger.info('Connecting to SQL Server')
        conn = pymssql.connect(server, user, password, database)
        conn.autocommit(True)  # Enable autocommit mode
        cursor = conn.cursor()

        logger.info('Connected to SQL Server')

        new_user = 'test_new_user'
        new_password = 'hijklmn67890'

        # Drop user in the database if exists
        drop_user_query = f"IF EXISTS (SELECT * FROM sys.database_principals WHERE name = '{new_user}') DROP USER {new_user};"
        cursor.execute(drop_user_query)
        logger.info('Dropped existing user in the database if it existed')

        # Drop login if exists
        drop_login_query = f"IF EXISTS (SELECT * FROM sys.server_principals WHERE name = '{new_user}') DROP LOGIN {new_user};"
        cursor.execute(drop_login_query)
        logger.info('Dropped existing login if it existed')

        # Create login for new user
        create_login_query = f"CREATE LOGIN {new_user} WITH PASSWORD = '{new_password}';"
        cursor.execute(create_login_query)
        logger.info('Created login for new user')

        permissions = [
            'ADMINISTER BULK OPERATIONS',
            'ALTER ANY CONNECTION',
            'ALTER ANY CREDENTIAL',
            'ALTER ANY EVENT SESSION',
            'ALTER ANY LINKED SERVER',
            'ALTER ANY LOGIN',
            'ALTER ANY SERVER AUDIT',
            'ALTER ANY SERVER ROLE',
            'ALTER SERVER STATE',
            'ALTER TRACE',
            'CONNECT SQL',
            'CREATE ANY DATABASE',
            'VIEW ANY DATABASE',
            'VIEW ANY DEFINITION',
            'VIEW SERVER STATE'
        ]

        for permission in permissions:
            grant_permission_query = f"GRANT {permission} TO {new_user};"
            cursor.execute(grant_permission_query)
            logger.info(f'Granted {permission} to new user')

        # Create database
        create_db_query = "CREATE DATABASE test_db;"
        cursor.execute(create_db_query)
        logger.info('Created database')

        # Use the new database
        use_db_query = "USE test_db;"
        cursor.execute(use_db_query)
        logger.info('Using new database')

        # Create user in the new database
        create_user_query = f"CREATE USER {new_user} FOR LOGIN {new_user};"
        cursor.execute(create_user_query)
        logger.info('Created user in new database')

        # Create table in the new database
        create_table_query = """
        CREATE TABLE test_table (
            column1 VARCHAR(30),
            column2 VARCHAR(30)
        );
        """
        cursor.execute(create_table_query)
        logger.info('Created table in new database')

        # Close the connection
        conn.close()
        logger.info('Closed connection')

        return {
            'statusCode': 200,
            'body': 'User, database, and table created successfully.'
        }

    except pymssql.DatabaseError as e:
        logger.error(f"Database error: {e}")
        return {
            'statusCode': 500,
            'body': f"Database error: {e}"
        }

    except Exception as e:
        logger.error(f"Error: {e}")
        return {
            'statusCode': 500,
            'body': f"Error: {e}"
        }
  1. Deployをクリック
  2. testをクリック
  3. jsonの項目を空っぽで保存
  4. 再びtestをクリック
    image.png
Test Event Name
pymssql

Response
{
  "statusCode": 200,
  "body": "User, database, and table created successfully."
}

Function Logs

蛇足：
RDSにおいても、Lambdaにおいてもそれぞれのコンソールから直感的に接続を確立できるのですが、RDSのインバウンドはTCP,1433,Lambdaのセキュリティグループを指定、Lambdaに設定するアウトバウンド「All,All,0.0.0.0/0」これには思考がいたらず困惑してしまいました。。（Lambdaアウトバウンドについてもっと追求はできそうですが参考までに。）

RDSはここで接続
image.png
Lambdaはここで接続
image.png

  • ひとまず、作成出来たのか確認します。
import pymssql
import logging

logger = logging.getLogger()
logger.setLevel(logging.INFO)

def lambda_handler(event, context):
    logger.info('Starting lambda_handler')

    # SQL Server connection details
    server = 'test-keiji-db.cp6c4mq6g518.ap-northeast-1.rds.amazonaws.com'
    user = 'admin'
    password = 'abcdefg12345'
    database = 'master'

    try:
        logger.info('Connecting to SQL Server')
        conn = pymssql.connect(server, user, password, database)
        cursor = conn.cursor()

        logger.info('Connected to SQL Server')

        # User to check
        check_user = 'test_new_user'

        # Check if login exists
        check_login_query = f"SELECT name FROM sys.server_principals WHERE name = '{check_user}';"
        cursor.execute(check_login_query)
        login_exists = cursor.fetchone()

        if login_exists:
            logger.info(f"Login '{check_user}' exists.")
        else:
            logger.info(f"Login '{check_user}' does not exist.")

        # Check if user exists in the database
        check_user_query = f"USE test_db; SELECT name FROM sys.database_principals WHERE name = '{check_user}';"
        cursor.execute(check_user_query)
        user_exists = cursor.fetchone()

        if user_exists:
            logger.info(f"User '{check_user}' exists in the database 'test_db'.")
        else:
            logger.info(f"User '{check_user}' does not exist in the database 'test_db'.")

        # Close the connection
        conn.close()
        logger.info('Closed connection')

        return {
            'statusCode': 200,
            'body': 'User existence check completed successfully.'
        }

    except pymssql.DatabaseError as e:
        logger.error(f"Database error: {e}")
        return {
            'statusCode': 500,
            'body': f"Database error: {e}"
        }

    except Exception as e:
        logger.error(f"Error: {e}")
        return {
            'statusCode': 500,
            'body': f"Error: {e}"
        }

次の様にユーザーをデータベース毎に作るようです。


pymssql

Response
{
  "statusCode": 200,
  "body": "User existence check completed successfully."
}

Function Logs
START RequestId: 31ffc80c-9ffa-4463-8672-64442a36b548 Version: $LATEST
[INFO]  2024-07-14T17:01:15.183Z    31ffc80c-9ffa-4463-8672-64442a36b548    Starting lambda_handler
[INFO]  2024-07-14T17:01:15.183Z    31ffc80c-9ffa-4463-8672-64442a36b548    Connecting to SQL Server
[INFO]  2024-07-14T17:01:15.411Z    31ffc80c-9ffa-4463-8672-64442a36b548    Connected to SQL Server
[INFO]  2024-07-14T17:01:15.446Z    31ffc80c-9ffa-4463-8672-64442a36b548    Login 'test_new_user' exists.
[INFO]  2024-07-14T17:01:15.577Z    31ffc80c-9ffa-4463-8672-64442a36b548    User 'test_new_user' exists in the database 'test_db'.
[INFO]  2024-07-14T17:01:15.577Z    31ffc80c-9ffa-4463-8672-64442a36b548    Closed connection
  • カラムを表示させてみましょう。
import pymssql
import logging

logger = logging.getLogger()
logger.setLevel(logging.INFO)

def lambda_handler(event, context):
    logger.info('Starting lambda_handler')

    # SQL Server connection details
    server = 'test-keiji-db.cp6c4mq6g518.ap-northeast-1.rds.amazonaws.com'
    user = 'admin'
    password = 'abcdefg12345'
    database = 'test_db'

    try:
        logger.info('Connecting to SQL Server')
        conn = pymssql.connect(server, user, password, database)
        cursor = conn.cursor()

        logger.info('Connected to SQL Server')

        # Query to get column information
        query = """
        SELECT COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'test_table';
        """
        cursor.execute(query)
        columns = cursor.fetchall()

        logger.info(f"Columns in 'test_table': {columns}")

        # Close the connection
        conn.close()
        logger.info('Closed connection')

        return {
            'statusCode': 200,
            'body': f"Columns in 'test_table': {columns}"
        }

    except pymssql.DatabaseError as e:
        logger.error(f"Database error: {e}")
        return {
            'statusCode': 500,
            'body': f"Database error: {e}"
        }

    except Exception as e:
        logger.error(f"Error: {e}")
        return {
            'statusCode': 500,
            'body': f"Error: {e}"
        }

以下出力結果

pymssql

Response
{
  "statusCode": 200,
  "body": "Columns in 'test_table': [('column1',), ('column2',)]"
}

Function Logs
START RequestId: ecf80e95-7dd6-4da3-bd47-c3959d542034 Version: $LATEST
[INFO]  2024-07-14T17:28:13.844Z    ecf80e95-7dd6-4da3-bd47-c3959d542034    Starting lambda_handler
[INFO]  2024-07-14T17:28:13.844Z    ecf80e95-7dd6-4da3-bd47-c3959d542034    Connecting to SQL Server
[INFO]  2024-07-14T17:28:14.093Z    ecf80e95-7dd6-4da3-bd47-c3959d542034    Connected to SQL Server
[INFO]  2024-07-14T17:28:14.300Z    ecf80e95-7dd6-4da3-bd47-c3959d542034    Columns in 'test_table': [('column1',), ('column2',)]
[INFO]  2024-07-14T17:28:14.301Z    ecf80e95-7dd6-4da3-bd47-c3959d542034    Closed connection

全般の感想

やはりEC2やCloud9などから接続する方が使い勝手は良いと思います。Lambdaから接続することはできるといった認識に至りました。
勉強になりました！
では！

返信を残す

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

CAPTCHA