nakanoshima.dev #29 LED-2!! (Letʼs enjoy データ分析!!) 2022.08.31 Amazon Redshift Serverless を AWS CDK で構築してみる

藤原 吉規 Amazon Web Services Japan, ⼤阪 Snr. Solutions Architect 好きな AWS サービス: - AWS CDK - AWS Support

© 2022, Amazon Web Services, Inc. or its Affiliates. All rights reserved. Amazon Redshift Serverless

© 2022, Amazon Web Services, Inc. or its affiliates. 処理能⼒は動的に⾃動で管理 Amazon Redshift Serverless JDBC/ODBC Data sharing clusters Data API Compute Storage Acceleration Operational Databases Query Live Data Incremental MVs Intelligent and dynamic compute management Amazon Sagemaker Redshift ML Redshift Managed Storage Amazon S3 ML-based workload monitoring Automatic tuning Automatic scaling Automatic workload management Pay for use Performance at scale Automatic maintenance 4

© 2022, Amazon Web Services, Inc. or its affiliates. エンドポイントへ接続すれば、これまで通りに利⽤可能 アプリケーションをシンプルに Amazon Redshift Serverless endpoint へ 接続すれば、すぐに実⾏可能 Amazon Redshift SQL のすべての機能が利⽤可能 Security and user management Complex joins Semi-structured data Data sharing Machine learning functions Amazon Redshift Data API JDBC/ODBC Tools Your applications Amazon S3 data lake Amazon Redshift Serverless Intelligent and dynamic compute management ML-based workload monitoring Automatic tuning Automatic scaling Automatic workload management Pay for use Performance at scale Automatic maintenance AWS Lambda, AWS Cloud9, Java, Go, PowerShell, Node.js, C#, Python, and Ruby Amazon Redshift managed storage Amazon Aurora/ RDS databases Data lake queries Federated query Durability and transactional guarantees JDBC/ODBC and Data API And more 5

© 2022, Amazon Web Services, Inc. or its affiliates. Serverless は簡単に始められる AWS アカウントで、Amazon Redshift Serverless 使⽤開始画⾯へ 1 デフォルト設定を確認して保存 数分で利⽤可能に 2 6 お好みのツール、また は Amazon Redshift Query Editor で接続 3

© 2022, Amazon Web Services, Inc. or its Affiliates. All rights reserved. Redshift Serverless を AWS CDK で構築してみる

© 2022, Amazon Web Services, Inc. or its Affiliates. All rights reserved. Availability Zone C Availability Zone B Availability Zone A Ingress subnet A Application subnet A Database subnet A : ISOLATED CDK で Redshift Serverless を構築: 全体像 Virtual Private Cloud (VPC) AWS Cloud NAT gateway Bastion EC2 Instance ENI: RedshiftNetworkInterface ・・・ S3 VPC Gateway Endpoint Redshift VPC Endpoint Interface Amazon Redshift Serverless Namespace Workgroup Amazon S3 Buckets AWS KMS key Namespace AWS Secrets Manager Secret Namespace AdminUsername, Password IAM Role Namespace Bastion Security group Redshift Serverless Security group TCP: 5439

AWS Cloud Development Kit (CDK) Your language Just classes and methods AutoComplete Inline documentation Sane defaults Reusable classes

© 2022, Amazon Web Services, Inc. or its Affiliates. All rights reserved. Availability Zone C Availability Zone B Availability Zone A Ingress subnet A Application subnet A 事前構築済みの VPC Virtual Private Cloud (VPC) AWS Cloud NAT gateway Bastion EC2 Instance S3 VPC Gateway Endpoint Amazon S3 Buckets Bastion Security group

© 2022, Amazon Web Services, Inc. or its Affiliates. All rights reserved. Availability Zone C Availability Zone B Availability Zone A Ingress subnet A Application subnet A Database subnet A : ISOLATED Redshift Serverless に必要なリソースを事前作成 Virtual Private Cloud (VPC) AWS Cloud NAT gateway Bastion EC2 Instance S3 VPC Gateway Endpoint Amazon S3 Buckets AWS KMS key Namespace AWS Secrets Manager Secret Namespace AdminUsername, Password IAM Role Namespace Bastion Security group Redshift Serverless Security group TCP: 5439

© 2022, Amazon Web Services, Inc. or its Affiliates. All rights reserved. AWS Secrets Manager Secret Namespace AdminUserName, AdminPassword // Secrets Manager Secret for Redshift Serverless Namespace const redshiftserverlessAdminSecret = new secretmanager.Secret(this, 'RedshiftserverlessAdminSecret', { generateSecretString: { secretStringTemplate: JSON.stringify({ username: 'admin'}), generateStringKey: 'password’, excludeCharacters: '/@" \\\’’, passwordLength: 32, } });

© 2022, Amazon Web Services, Inc. or its Affiliates. All rights reserved. IAM Role Namespace // IAM Role/Policy for Redshift Serverless Namesapce const redshiftserverlessNamesapceRole = new iam.Role(this, 'RedshiftServerlessNamesapceRole', { assumedBy: new iam.CompositePrincipal( new iam.ServicePrincipal('sagemaker.amazonaws.com'), new iam.ServicePrincipal('redshift.amazonaws.com’), new iam.ServicePrincipal('redshift-serverless.amazonaws.com’), ), managedPolicies: [ iam.ManagedPolicy.fromAwsManagedPolicyName('AmazonRedshiftAllCommandsFullAccess’), ], }); https://docs.aws.amazon.com/ja_jp/redshift/latest/mgmt/default-iam-role.html

© 2022, Amazon Web Services, Inc. or its Affiliates. All rights reserved. IAM Role Namespace redshiftserverlessNamesapceRole.addToPolicy( new iam.PolicyStatement({ actions: [ "s3:GetObject", "s3:GetBucketAcl", "s3:GetBucketCors", "s3:GetEncryptionConfiguration", "s3:GetBucketLocation", "s3:ListBucket", "s3:ListAllMyBuckets", "s3:ListMultipartUploadParts", "s3:ListBucketMultipartUploads", "s3:PutObject", "s3:PutBucketAcl", … … "s3:PutBucketCors", "s3:DeleteObject", "s3:AbortMultipartUpload", "s3:CreateBucket" ], effect: iam.Effect.ALLOW, resources: [ "arn:aws:s3:::redshift/*", "arn:aws:s3:::redshift" ] }) );

© 2022, Amazon Web Services, Inc. or its Affiliates. All rights reserved. AWS KMS key Namespace // Redshift Serverless KMS Key const redshiftserverlessKmsKey = new kms.Key(this, 'RedshiftserverlessKmsKey', { enabled: true, enableKeyRotation: true, });

© 2022, Amazon Web Services, Inc. or its Affiliates. All rights reserved. Availability Zone C Availability Zone B Availability Zone A Ingress subnet A Application subnet A Database subnet A : ISOLATED NameSpace, Workgroup を作成 Virtual Private Cloud (VPC) AWS Cloud NAT gateway Bastion EC2 Instance S3 VPC Gateway Endpoint Amazon Redshift Serverless Namespace Amazon S3 Buckets AWS KMS key Namespace AWS Secrets Manager Secret Namespace AdminUsername, Password IAM Role Namespace Bastion Security group Redshift Serverless Security group TCP: 5439 Workgroup

© 2022, Amazon Web Services, Inc. or its Affiliates. All rights reserved. Redshift Serverless Namespace と Workgroup Namespace: 名前空間 名前空間は、データベースオブジェクトとユーザーのコレクションで す。ストレージ関連の名前空間は、スキーマ、テーブル、ユーザー、 またはデータを暗号化するための AWS Key Management Service キーをグループ化します。ストレージプロパティには、管理者ユー ザーのデータベース名とパスワード、権限、暗号化およびセキュリ ティが含まれます。名前空間の下にグループ化されたリソースには、 データ共有、復旧ポイント、使⽤制限などもあります https://docs.aws.amazon.com/ja_jp/redshift/latest/mgmt/serverless-workgroup-namespace.html

© 2022, Amazon Web Services, Inc. or its Affiliates. All rights reserved. Redshift Serverless Namespace と Workgroup Workgroup: ワークグループ ワークグループは、コンピューティングリソースの集合です。コン ピューティング関連のワークグループは、RPU、VPC サブネットグ ループ、セキュリティグループなどのコンピューティングリソースを グループ化します。ワークグループのプロパティには、ネットワーク とセキュリティ設定が含まれます。ワークグループの下にグループ化 されたリソースには、アクセス制限と使⽤制限もあります。 https://docs.aws.amazon.com/ja_jp/redshift/latest/mgmt/serverless-workgroup-namespace.html

© 2022, Amazon Web Services, Inc. or its Affiliates. All rights reserved. Redshift Serverless Namespace // Redshift Serverless Namespace const cfnNamespace = new redshiftserverless.CfnNamespace(this, 'RedshiftServerlessNamespace', { namespaceName: 'default’, adminUsername: redshiftserverlessAdminSecret.secretValueFromJson('username').unsafeUnwrap(), adminUserPassword: redshiftserverlessAdminSecret.secretValueFromJson('password').unsafeUnwrap(), dbName: 'dev’, defaultIamRoleArn: redshiftserverlessNamesapceRole.roleArn, iamRoles: [redshiftserverlessNamesapceRole.roleArn], kmsKeyId: redshiftserverlessKmsKey.keyId, logExports: ['userlog','connectionlog','useractivitylog'], }); https://docs.aws.amazon.com/cdk/api/v2/docs/aws-cdk-lib.aws_redshiftserverless.CfnNamespace.html

© 2022, Amazon Web Services, Inc. or its Affiliates. All rights reserved. Redshift Serverless Workgroup // Redshift Serverless Workgroup const cfnWorkgroup = new redshiftserverless.CfnWorkgroup(this, 'RedshiftServerlessWorkgroup', { workgroupName: 'default’, baseCapacity: 32, configParameters: [ … ], enhancedVpcRouting: true, namespaceName: cfnNamespace.namespaceName, publiclyAccessible: false, securityGroupIds: [redshiftserverlessSecurityGroup.securityGroupId], subnetIds: vpc.selectSubnets({subnetGroupName: 'Database'}).subnetIds, }); cfnWorkgroup.addDependsOn(cfnNamespace); https://docs.aws.amazon.com/cdk/api/v2/docs/aws-cdk-lib.aws_redshiftserverless.CfnWorkgroup.html

© 2022, Amazon Web Services, Inc. or its Affiliates. All rights reserved. ベースRPU容量の設定 • クラスターの性能管理はRPUのBase capacity（ベース RPU 容量の編集） で⾏う • RPUのBase capacityはデフォルトで はAutoとなっており、これは128と同 等。Base capacityは32〜512まで8刻 みで変更が可能 • Base capacityを変更すると、⼀時的 に（数分）Serverlessは利⽤不可とな る

© 2022, Amazon Web Services, Inc. or its Affiliates. All rights reserved. Redshift Serverless ConfigParameterProperty configParameters: [ { parameterKey: 'search_path’, parameterValue: '$user’, }, { parameterKey: 'enable_user_activity_logging’, parameterValue: 'true’, }, { parameterKey: 'datestyle’, parameterValue: 'ISO,MDY’, }, … … { parameterKey: 'query_group’, parameterValue: 'adhoc’, }, { parameterKey: 'max_query_execution_time’, parameterValue: '3600’, }, ], … https://docs.aws.amazon.com/cdk/api/v2/docs/aws-cdk- lib.aws_redshiftserverless.CfnWorkgroup.ConfigParameterProperty.html

© 2022, Amazon Web Services, Inc. or its Affiliates. All rights reserved. 使用制限・クエリの制限の管理 • RPUの利⽤上限を設けることができる • ただしこれはどちらかというとコストコント ロールのための機能で、例えば利⽤するRPUの レンジを決める（32~64の間で使ってほしい など）機能ではない • ⽇次/週次/⽉次で、最⼤何RPU-時まで使うか の上限を決め、超えた場合はアラートを上げる、 システムテーブルへログ記録、クエリを⽌める、 のいずれかを選択する • 同様に、クロスリージョンデータ共有のデータ転送 量も使⽤制限の設定が可能になっている • それぞれ最⼤4つの制限を設定可能 • 使⽤制限とは別に、クエリの実⾏制限として、実⾏ 時間（秒）でのタイムアウト設定を１つだけ設ける ことができる

© 2022, Amazon Web Services, Inc. or its Affiliates. All rights reserved. Availability Zone C Availability Zone B Availability Zone A Ingress subnet A Application subnet A Database subnet A : ISOLATED Redshift Serverless 起動後 Virtual Private Cloud (VPC) AWS Cloud NAT gateway Bastion EC2 Instance ENI: RedshiftNetworkInterface S3 VPC Gateway Endpoint Redshift VPC Endpoint Interface Amazon Redshift Serverless Namespace Workgroup Amazon S3 Buckets AWS KMS key Namespace AWS Secrets Manager Secret Namespace AdminUsername, Password IAM Role Namespace Bastion Security group Redshift Serverless Security group TCP: 5439

© 2022, Amazon Web Services, Inc. or its Affiliates. All rights reserved. ネットワークインターフェイス アイドル時 クエリ実⾏時: BaseCapacity 32

© 2022, Amazon Web Services, Inc. or its Affiliates. All rights reserved. Availability Zone C Availability Zone B Availability Zone A Ingress subnet A Application subnet A Database subnet A : CIDR /24 ネットワークインターフェイス Virtual Private Cloud (VPC) AWS Cloud NAT gateway Bastion EC2 Instance ENI: RedshiftNetworkInterface ・・・ S3 VPC Gateway Endpoint Redshift VPC Endpoint Interface Amazon Redshift Serverless Namespace Workgroup Amazon S3 Buckets AWS KMS key Namespace AWS Secrets Manager Secret Namespace AdminUsername, Password IAM Role Namespace Bastion Security group Redshift Serverless Security group TCP: 5439

© 2022, Amazon Web Services, Inc. or its Affiliates. All rights reserved. Availability Zone C Availability Zone B Availability Zone A Ingress subnet A Application subnet A Database subnet A : ISOLATED 拡張 VPC ルーティング: COPY, UNLOAD Virtual Private Cloud (VPC) AWS Cloud NAT gateway Bastion EC2 Instance ENI: RedshiftNetworkInterface ・・・ S3 VPC Gateway Endpoint Redshift VPC Endpoint Interface Amazon Redshift Serverless Namespace Workgroup Amazon S3 Buckets AWS KMS key Namespace AWS Secrets Manager Secret Namespace AdminUsername, Password IAM Role Namespace Bastion Security group Redshift Serverless Security group TCP: 5439

© 2022, Amazon Web Services, Inc. or its Affiliates. All rights reserved. Availability Zone C Availability Zone B Availability Zone A Ingress subnet A Application subnet A Database subnet A : ISOLATED まとめ Virtual Private Cloud (VPC) AWS Cloud NAT gateway Bastion EC2 Instance ENI: RedshiftNetworkInterface ・・・ S3 VPC Gateway Endpoint Redshift VPC Endpoint Interface Amazon Redshift Serverless Namespace Workgroup Amazon S3 Buckets AWS KMS key Namespace AWS Secrets Manager Secret Namespace AdminUsername, Password IAM Role Namespace Bastion Security group Redshift Serverless Security group TCP: 5439

© 2022, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2022, Amazon Web Services, Inc. or its Affiliates. All rights reserved. THANK YOU!