データアナリティクス事業本部の笠原です。
Aurora MySQL / Redshift 間のzero-ELT統合において、フィルタリングサポートがアナウンスされました。また、zeroETL統合に必要なリソース設定をするためのCloudformationサポートもアナウンスされました。
今までは全データベースの全テーブルを統合していましたが、フィルタリングサポートによってRedshiftに取り込むテーブルを選択することで、一部のテーブルのみ統合することができるようになりました。
併せて、Cloudformationテンプレートのサポートがアナウンスされています。
こちらは今までマネジメントコンソール上やCLI上でしかできなかった CreateIntegration
のアクションまでをCloudformationで設定できるようになりました。
zeroETL統合の設定がかなり楽になりましたので、早速試してみましょう。
今回の構成
今回はAurora MySQL Serverless / Redshift Serverless の組み合わせでやっていきます。
Redshift Serverlessは3AZ必須のため、サブネットも3つ作っておきます。 また、Redshift Serverlessのワークグループ作成にはRPUに応じて空きIPアドレスの確保が必要になります。 今回は各サブネットを20ビットマスクにして広めのIPレンジを確保しています。
詳細は以下のドキュメントをご確認ください。
Amazon Redshift サーバーレスを使用する場合の考慮事項 - Amazon Redshift
Cloudformationテンプレート
以下にCloudformationテンプレートを掲載します。
VPC → Aurora → Redshift → Integration の順に適用してください。
VPC Cfnテンプレート
01_VPC.yaml
AWSTemplateFormatVersion: '2010-09-09'
Parameters:
ProjectName:
Type: String
Default: poc-zeroetl
Stage:
Type: String
Default: dev
AllowedValues:
- dev
Mappings:
dev:
VPC:
CidrBlock: 10.160.0.0/16
ServiceSubnet01:
CidrBlock: 10.160.0.0/20
ServiceSubnet02:
CidrBlock: 10.160.16.0/20
ServiceSubnet03:
CidrBlock: 10.160.64.0/20
ClusterSubnet01:
CidrBlock: 10.160.32.0/20
ClusterSubnet02:
CidrBlock: 10.160.48.0/20
ClusterSubnet03:
CidrBlock: 10.160.80.0/20
Resources:
VPC01:
Type: AWS::EC2::VPC
Properties:
CidrBlock: !FindInMap [!Ref Stage, VPC, CidrBlock]
EnableDnsHostnames: true
EnableDnsSupport: true
InstanceTenancy: default
Tags:
- Key: Name
Value: !Sub ${ProjectName}-${Stage}-vpc-vpc-VPC01
ServiceSubnet01:
Type: AWS::EC2::Subnet
Properties:
VpcId: !Ref VPC01
CidrBlock: !FindInMap [!Ref Stage, ServiceSubnet01, CidrBlock]
AvailabilityZone: ap-northeast-1a
MapPublicIpOnLaunch: false
Tags:
- Key: Name
Value: !Sub ${ProjectName}-${Stage}-vpc-subnet-ServiceSubnet01
ServiceSubnet02:
Type: AWS::EC2::Subnet
Properties:
VpcId: !Ref VPC01
CidrBlock: !FindInMap [!Ref Stage, ServiceSubnet02, CidrBlock]
AvailabilityZone: ap-northeast-1c
MapPublicIpOnLaunch: false
Tags:
- Key: Name
Value: !Sub ${ProjectName}-${Stage}-vpc-subnet-ServiceSubnet02
ServiceSubnet03:
Type: AWS::EC2::Subnet
Properties:
VpcId: !Ref VPC01
CidrBlock: !FindInMap [!Ref Stage, ServiceSubnet03, CidrBlock]
AvailabilityZone: ap-northeast-1d
MapPublicIpOnLaunch: false
Tags:
- Key: Name
Value: !Sub ${ProjectName}-${Stage}-vpc-subnet-ServiceSubnet03
ClusterSubnet01:
Type: AWS::EC2::Subnet
Properties:
VpcId: !Ref VPC01
CidrBlock: !FindInMap [!Ref Stage, ClusterSubnet01, CidrBlock]
AvailabilityZone: ap-northeast-1a
MapPublicIpOnLaunch: false
Tags:
- Key: Name
Value: !Sub ${ProjectName}-${Stage}-vpc-subnet-ClusterSubnet01
ClusterSubnet02:
Type: AWS::EC2::Subnet
Properties:
VpcId: !Ref VPC01
CidrBlock: !FindInMap [!Ref Stage, ClusterSubnet02, CidrBlock]
AvailabilityZone: ap-northeast-1c
MapPublicIpOnLaunch: false
Tags:
- Key: Name
Value: !Sub ${ProjectName}-${Stage}-vpc-subnet-ClusterSubnet02
ClusterSubnet03:
Type: AWS::EC2::Subnet
Properties:
VpcId: !Ref VPC01
CidrBlock: !FindInMap [!Ref Stage, ClusterSubnet03, CidrBlock]
AvailabilityZone: ap-northeast-1d
MapPublicIpOnLaunch: false
Tags:
- Key: Name
Value: !Sub ${ProjectName}-${Stage}-vpc-subnet-ClusterSubnet03
RouteTable01:
Type: AWS::EC2::RouteTable
Properties:
VpcId: !Ref VPC01
Tags:
- Key: Name
Value: !Sub ${ProjectName}-${Stage}-vpc-rtb-RouteTable01
RouteTableAssociationServiceSubnet01:
Type: AWS::EC2::SubnetRouteTableAssociation
Properties:
RouteTableId: !Ref RouteTable01
SubnetId: !Ref ServiceSubnet01
RouteTableAssociationServiceSubnet02:
Type: AWS::EC2::SubnetRouteTableAssociation
Properties:
RouteTableId: !Ref RouteTable01
SubnetId: !Ref ServiceSubnet02
RouteTableAssociationServiceSubnet03:
Type: AWS::EC2::SubnetRouteTableAssociation
Properties:
RouteTableId: !Ref RouteTable01
SubnetId: !Ref ServiceSubnet03
RouteTableAssociationClusterSubnet01:
Type: AWS::EC2::SubnetRouteTableAssociation
Properties:
RouteTableId: !Ref RouteTable01
SubnetId: !Ref ClusterSubnet01
RouteTableAssociationClusterSubnet02:
Type: AWS::EC2::SubnetRouteTableAssociation
Properties:
RouteTableId: !Ref RouteTable01
SubnetId: !Ref ClusterSubnet02
RouteTableAssociationClusterSubnet03:
Type: AWS::EC2::SubnetRouteTableAssociation
Properties:
RouteTableId: !Ref RouteTable01
SubnetId: !Ref ClusterSubnet03
Outputs:
VPC01:
Value: !Ref VPC01
Description: VPC 01
Export:
Name: VPC01
VPC01Cidr:
Value: !GetAtt VPC01.CidrBlock
Description: Cidr Block in VPC 01
Export:
Name: VPC01Cidr
ServiceSubnet01:
Value: !Ref ServiceSubnet01
Description: Service Subnet 01
Export:
Name: ServiceSubnet01
ServiceSubnet02:
Value: !Ref ServiceSubnet02
Description: Service Subnet 02
Export:
Name: ServiceSubnet02
ServiceSubnet03:
Value: !Ref ServiceSubnet03
Description: Service Subnet 03
Export:
Name: ServiceSubnet03
ClusterSubnet01:
Value: !Ref ClusterSubnet01
Description: Cluster Subnet 01
Export:
Name: ClusterSubnet01
ClusterSubnet02:
Value: !Ref ClusterSubnet02
Description: Cluster Subnet 02
Export:
Name: ClusterSubnet02
ClusterSubnet03:
Value: !Ref ClusterSubnet03
Description: Cluster Subnet 03
Export:
Name: ClusterSubnet03
RouteTable01:
Value: !Ref RouteTable01
Description: Route Table 01
Export:
Name: RouteTable01
Aurora MySQL (Serverless v2) Cfnテンプレート
02_Aurora.yaml
AWSTemplateFormatVersion: 2010-09-09
Parameters:
ProjectName:
Type: String
Default: poc-zeroetl
Stage:
Type: String
Default: dev
AllowedValues:
- dev
MasterUserName:
Type: String
Default: admin
AllowedPattern: "([a-z])([a-z]|[0-9])*"
MasterUserPassword:
Type: String
NoEcho: true
Mappings:
dev:
Aurora:
AuroraInstancePrefix: aurora-mysql-poc
DBInstanceClass: db.t4g.medium
DatabaseName: zeroetldb
Resources:
## Subnet Group
DBSubnetGroup:
Type: AWS::RDS::DBSubnetGroup
Properties:
DBSubnetGroupName: !Sub "${ProjectName}-${Stage}-db-subnet-group"
DBSubnetGroupDescription: !Sub "${ProjectName}-${Stage}-db-subnet-group"
SubnetIds:
- !ImportValue ClusterSubnet01
- !ImportValue ClusterSubnet02
- !ImportValue ClusterSubnet03
## DB Cluster Parameter Group
DBClusterParameterGroup:
Type: AWS::RDS::DBClusterParameterGroup
Properties:
DBClusterParameterGroupName: !Sub "${ProjectName}-${Stage}-db-cluster-parameter-group"
Description: !Sub "${ProjectName}-${Stage}-db-cluster-parameter-group"
Family: aurora-mysql8.0
Parameters:
time_zone: Asia/Tokyo
character_set_server: utf8
character_set_client: utf8
character_set_connection: utf8
character_set_results: utf8
character_set_database: utf8
performance_schema: 1
## for zero-ETL
aurora_enhanced_binlog: 1
binlog_backup: 0
binlog_format: ROW
binlog_replication_globaldb: 0
binlog_row_image: full
binlog_row_metadata: full
binlog_transaction_compression: 'OFF' ## ONでないこと (デフォルト値: OFF)
binlog_row_value_options: '' ## PARTIAL_JSONでないこと (デフォルト値: '')
## DB Parameter Group
DBParameterGroup:
Type: AWS::RDS::DBParameterGroup
Properties:
Description: !Sub "${ProjectName}-${Stage}-db-parameter-group"
Family: aurora-mysql8.0
Parameters:
general_log: 0
slow_query_log: 1
long_query_time: 5000
log_output: FILE
## Security Group
DBSecurityGroup:
Type: AWS::EC2::SecurityGroup
Properties:
VpcId: !ImportValue VPC01
GroupDescription: !Sub "${ProjectName}-${Stage}-db-security-group"
SecurityGroupIngress:
- IpProtocol: tcp
FromPort: 3306
ToPort: 3306
CidrIp: !ImportValue VPC01Cidr
## Aurora MySQL Cluster
AuroraCluster:
Type: AWS::RDS::DBCluster
Properties:
Engine: aurora-mysql
EngineVersion: 8.0.mysql_aurora.3.05.2
DBSubnetGroupName: !Ref DBSubnetGroup
DBClusterParameterGroupName: !Ref DBClusterParameterGroup
VpcSecurityGroupIds:
- !Ref DBSecurityGroup
DatabaseName: !FindInMap [!Ref Stage, Aurora, DatabaseName]
MasterUsername: !Ref MasterUserName
MasterUserPassword: !Ref MasterUserPassword
ServerlessV2ScalingConfiguration:
MaxCapacity: 16
MinCapacity: 0.5
Port: 3306
PreferredBackupWindow: 20:30-21:00
PreferredMaintenanceWindow: Tue:20:00-Tue:20:30
StorageEncrypted: true
## Aurora MySQL Instances
AuroraInstance1:
Type: AWS::RDS::DBInstance
Properties:
DBClusterIdentifier: !Ref AuroraCluster
DBInstanceIdentifier: !FindInMap [!Ref Stage, Aurora, AuroraInstancePrefix]
DBInstanceClass: db.serverless
DBParameterGroupName: !Ref DBParameterGroup
Engine: aurora-mysql
EngineVersion: 8.0.mysql_aurora.3.05.2
EnablePerformanceInsights: true
PromotionTier: 0
Outputs:
AuroraCluster:
Value: !Ref AuroraCluster
Description: Aurora Cluster
Export:
Name: AuroraCluster
AuroraClusterArn:
Value: !GetAtt AuroraCluster.DBClusterArn
Description: Aurora Cluster Arn
Export:
Name: AuroraClusterArn
Redshift Serverless Cfnテンプレート
03_Redshift.yaml
AWSTemplateFormatVersion: 2010-09-09
Parameters:
ProjectName:
Type: String
Default: poc-zeroetl
Stage:
Type: String
Default: dev
AllowedValues:
- dev
MasterUserName:
Type: String
Default: admin
AllowedPattern: "([a-z])([a-z]|[0-9])*"
MasterUserPassword:
Type: String
NoEcho: true
Mappings:
dev:
Redshift:
WorkgroupNameSuffix: redshiftserverless-workgroup
NamespaceNameSuffix: redshiftserverless-namespace
BaseCapacity: 8
PortNumber: 5439
DatabaseName: poc-zeroetl
Resources:
## Redshift Serverless
RedshiftServerlessWorkgroup:
Type: AWS::RedshiftServerless::Workgroup
Properties:
WorkgroupName: !Sub
- "${ProjectName}-${Stage}-${workgroup_name_suffix}"
- workgroup_name_suffix: !FindInMap [ !Ref Stage, Redshift, WorkgroupNameSuffix ]
BaseCapacity: !FindInMap [ !Ref Stage, Redshift, BaseCapacity ]
NamespaceName: !Ref RedshiftServerlessNamespace
EnhancedVpcRouting: false ## ZeroETL統合には false が必須 (default: false)
PubliclyAccessible: false
ConfigParameters:
- ParameterKey: enable_user_activity_logging
ParameterValue: true
- ParameterKey: enable_case_sensitive_identifier
ParameterValue: true ## ZeroETL統合には true が必須 (default: false)
SecurityGroupIds:
- !Ref RedshiftServerlessSecurityGroup
SubnetIds:
- !ImportValue ClusterSubnet01
- !ImportValue ClusterSubnet02
- !ImportValue ClusterSubnet03
Port: !FindInMap [ !Ref Stage, Redshift, PortNumber ]
RedshiftServerlessNamespace:
Type: AWS::RedshiftServerless::Namespace
Properties:
NamespaceName: !Sub
- "${ProjectName}-${Stage}-${namespace_name_suffix}"
- namespace_name_suffix: !FindInMap [ !Ref Stage, Redshift, NamespaceNameSuffix ]
DbName: !FindInMap [ !Ref Stage, Redshift, DatabaseName ]
AdminUsername: !Ref MasterUserName
AdminUserPassword: !Ref MasterUserPassword
IamRoles:
- !GetAtt RedshiftServerlessRole.Arn
LogExports:
- userlog
- connectionlog
- useractivitylog
NamespaceResourcePolicy:
Version: '2012-10-17'
Statement:
- Effect: Allow
Principal:
Service: redshift.amazonaws.com
Action: redshift:AuthorizeInboundIntegration
Condition:
StringEquals:
aws:SourceArn: !ImportValue AuroraClusterArn
- Effect: Allow
Principal:
AWS: !Sub "arn:${AWS::Partition}:iam::${AWS::AccountId}:root"
Action: redshift:CreateInboundIntegration
RedshiftServerlessSecurityGroup:
Type: AWS::EC2::SecurityGroup
Properties:
GroupName: !Sub "${ProjectName}-${Stage}-vpc-sg-RedshiftServerlessSG"
GroupDescription: Security Group for Redshift Serverless
VpcId: !ImportValue VPC01
SecurityGroupIngress:
- CidrIp: !ImportValue VPC01Cidr
FromPort: !FindInMap [ !Ref Stage, Redshift, PortNumber ]
ToPort: !FindInMap [ !Ref Stage, Redshift, PortNumber ]
IpProtocol: tcp
Tags:
- Key: Name
Value: !Sub "${ProjectName}-${Stage}-vpc-sg-RedshiftServerlessSG"
RedshiftServerlessRole:
Type: AWS::IAM::Role
Properties:
RoleName: !Sub "${ProjectName}-${Stage}-iam-role-RedshiftServerlessRole"
AssumeRolePolicyDocument:
Version: "2012-10-17"
Statement:
- Effect: Allow
Principal:
Service:
- redshift.amazonaws.com
Action: sts:AssumeRole
MaxSessionDuration: 3600
ManagedPolicyArns:
- "arn:aws:iam::aws:policy/AmazonAthenaFullAccess"
- "arn:aws:iam::aws:policy/AmazonS3FullAccess"
- "arn:aws:iam::aws:policy/AWSGlueConsoleFullAccess"
- "arn:aws:iam::aws:policy/AmazonRedshiftAllCommandsFullAccess"
Description: "Allows Redshift clusters to call AWS services on your behalf."
Tags:
- Key: "Name"
Value: !Sub "${ProjectName}-${Stage}-iam-role-RedshiftServerlessRole"
Outputs:
RedshiftServerlessWorkgroup:
Value: !Ref RedshiftServerlessWorkgroup
Description: Redshift Serverless Workgroup
Export:
Name: RedshiftServerlessWorkgroup
RedshiftServerlessWorkgroupName:
Value: !Sub
- "${ProjectName}-${Stage}-${workgroup_name_suffix}"
- workgroup_name_suffix: !FindInMap [ !Ref Stage, Redshift, WorkgroupNameSuffix ]
Description: Redshift Serverless Workgroup Name
Export:
Name: RedshiftServerlessWorkgroupName
RedshiftServerlessNamespace:
Value: !Ref RedshiftServerlessNamespace
Description: Redshift Serverless Namespace
Export:
Name: RedshiftServerlessNamespace
RedshiftServerlessNamespaceArn:
Value: !GetAtt RedshiftServerlessNamespace.Namespace.NamespaceArn
Description: Redshift Serverless Namespace Arn
Export:
Name: RedshiftServerlessNamespaceArn
RedshiftServerlessPort:
Value: !FindInMap [ !Ref Stage, Redshift, PortNumber ]
Description: Redshift Serverless Port Number
Export:
Name: RedshiftServerlessPort
RedshiftServerlessDatabaseName:
Value: !FindInMap [ !Ref Stage, Redshift, DatabaseName ]
Description: Redshift Serverless Database Name
Export:
Name: RedshiftServerlessDatabaseName
RedshiftServerlessHostName:
Value: !Sub
- "${workgroup_name}.${account_number}.ap-northeast-1.redshift-serverless.amazonaws.com"
- workgroup_name: !Ref RedshiftServerlessWorkgroup
account_number: !Ref AWS::AccountId
Description: Redshift Serverless Host Name
Export:
Name: RedshiftServerlessHostName
Integration Cfnテンプレート
03_Integration.yaml
AWSTemplateFormatVersion: 2010-09-09
Parameters:
ProjectName:
Type: String
Default: poc-zeroetl-dna
Stage:
Type: String
Default: dev
AllowedValues:
- dev
Resources:
## Create Aurora zero-ETL Integration with Redshift
AuroraRedshiftZeroETLIntegration:
Type: AWS::RDS::Integration
Properties:
IntegrationName: !Sub "${ProjectName}-${Stage}-zeroETL-Integration"
SourceArn: !ImportValue AuroraClusterArn
TargetArn: !ImportValue RedshiftServerlessNamespaceArn
zeroETL統合の設定に必要なポイントは以下の通り。
Aurora DB Cluster パラメータグループ設定
zeroETL統合に必要なパラメータグループ設定をします。
参考: Amazon Redshift との Aurora ゼロ ETL 統合の開始方法 - Amazon Aurora
## DB Cluster Parameter Group
DBClusterParameterGroup:
Type: AWS::RDS::DBClusterParameterGroup
Properties:
## <中略>
Parameters:
## <中略>
## for zero-ETL
aurora_enhanced_binlog: 1
binlog_backup: 0
binlog_format: ROW
binlog_replication_globaldb: 0
binlog_row_image: full
binlog_row_metadata: full
binlog_transaction_compression: 'OFF' ## ONでないこと (デフォルト値: OFF)
binlog_row_value_options: '' ## PARTIAL_JSONでないこと (デフォルト値: '')
Redshift パラメータ設定
Redshift側の設定もします。
まずRedshiftで大文字小文字を区別する enable_case_sensitive_identifier
を有効化する必要があります。
元々Redshiftで大文字小文字を区別しないでSQLクエリ問い合わせをしていた場合、SQLクエリを見直す必要があります。
Redshift Serverlessではワークグループの ConfigParameters
で設定します。ちなみに、Provisioned Redshiftではクラスタパラメータグループで設定します。
ゼロ ETL 統合の開始方法 - Amazon Redshift
また、zeroETL統合を開始する場合、拡張VPCルーティング ( EnhancedVpcRouting
) は無効化する必要があります。
デフォルトは無効化となっていますが、SecurityHubの推奨事項は有効化です。そのため、実際に利用する際は、セキュリティ要件を事前に確認する必要がありますので、ご注意ください。
プレビューの制限事項 - Amazon Redshift Amazon Redshift のコントロール - AWS Security Hub
RedshiftServerlessWorkgroup:
Type: AWS::RedshiftServerless::Workgroup
Properties:
## <中略>
EnhancedVpcRouting: false ## ZeroETL統合には false が必須 (default: false)
## <中略>
ConfigParameters:
## <中略>
- ParameterKey: enable_case_sensitive_identifier
ParameterValue: true ## ZeroETL統合には true が必須 (default: false)
enable_case_sensitive_identifier
が有効化されているか確認する場合は、以下のAWS CLIコマンドを実行します。
aws redshift-serverless get-workgroup --workgroup-name poc-zeroetl-dev-redshiftserverless-workgroup
また、zeroETL統合を開始する前に、Redshift側に認証設定をする必要があります。
ゼロ ETL 統合の開始方法 - Amazon Redshift
これは、承認されたプリンシパルと、承認された統合ソースを各々追加します。
Cfnテンプレート上では、Redshift Serverless名前空間上に NamespaceResourcePolicy
を設定することで対応できます。
ちなみに、Provisioned Redshiftの場合は、 Type: 'AWS::Redshift::Cluster'
に同様の NamespaceResourcePolicy
が設定可能です。
今回は、以下のように設定しています。
- 承認されたプリンシパル: AWSアカウント ARN
- 承認された統合ソース: Aurora MySQL クラスタ ARN
RedshiftServerlessNamespace:
Type: AWS::RedshiftServerless::Namespace
Properties:
## <中略>
NamespaceResourcePolicy:
Version: '2012-10-17'
Statement:
- Effect: Allow
Principal:
Service: redshift.amazonaws.com
Action: redshift:AuthorizeInboundIntegration
Condition:
StringEquals:
aws:SourceArn: !ImportValue AuroraClusterArn
- Effect: Allow
Principal:
AWS: !Sub "arn:${AWS::Partition}:iam::${AWS::AccountId}:root"
Action: redshift:CreateInboundIntegration
マネジメントコンソール上には、以下のように設定内容が反映されます。
zero-ETL統合開始
zero-ETL統合開始する設定は以下の通りです。 ソース側はAuroraクラスタのARNを指定し、ターゲット側はRedshift Serverless名前空間のARNを指定します。
Resources:
## Create Aurora zero-ETL Integration with Redshift
AuroraRedshiftZeroETLIntegration:
Type: AWS::RDS::Integration
Properties:
IntegrationName: !Sub "${ProjectName}-${Stage}-zeroETL-Integration"
SourceArn: !ImportValue AuroraClusterArn
TargetArn: !ImportValue RedshiftServerlessNamespaceArn
参考: Amazon Redshift との Amazon ゼロ ETL 統合の作成 - Amazon Aurora
zeroETL統合が開始されると、マネジメントコンソール上では、Aurora側・Redshift側にzeroELT統合についてアクティブになっていることが確認できます。
なお、Cfnのページには、Aurora MySQL (Provisioned) / Redshift (Provisioned) 間のzeroETL統合のサンプルテンプレートの他、RDS MySQLのzeroELT統合のサンプルテンプレートもありますので、ご参考ください。
AWS::RDS::Integration - AWS CloudFormation
フィルタリング機能
フィルタリング機能は以下の記事を参考に試してみます。
事前準備(送信先DB作成)
まずはRedshift側に、送信先DBを作成しておきましょう。 クエリエディタv2を開いて、まずは統合IDを以下のクエリから取得します。
ちなみにこの時、Redshiftは大文字・小文字を区別する設定になっていますので、以下のカラム名とビュー名は小文字で入力する必要があります。
SELECT integration_id FROM svv_integration;
続いて、以下のようにSQLクエリを実行します。 integration_id
には、先ほど取得した 統合ID を入力してください。
また、今回は送信先DBを zeroetl_dest_db
としております。
CREATE DATABASE zeroetl_dest_db FROM INTEGRATION 'integration_id';
成功すれば、Redshiftの zeroetl_dest_db
DB内には、Aurora側の zeroetldb
があることが確認できると思います。
事前準備(ソースDB追加)
次に、追加でAurora側に demodb
を作成してみましょう。
この demodb
のDDL文は以下のものを利用しています。
create database / create table クエリ
create database demodb;
create table demodb.users(
userid integer not null primary key,
username char(8),
firstname varchar(30),
lastname varchar(30),
city varchar(30),
state char(2),
email varchar(100),
phone char(14),
likesports boolean,
liketheatre boolean,
likeconcerts boolean,
likejazz boolean,
likeclassical boolean,
likeopera boolean,
likerock boolean,
likevegas boolean,
likebroadway boolean,
likemusicals boolean)
;
create table demodb.venue(
venueid integer not null primary key,
venuename varchar(100),
venuecity varchar(30),
venuestate char(2),
venueseats integer)
;
create table demodb.category(
catid integer not null primary key,
catgroup varchar(10),
catname varchar(10),
catdesc varchar(50))
;
create table demodb.date (
dateid integer not null primary key,
caldate date not null,
day character(3) not null,
week smallint not null,
month character(5) not null,
qtr character(5) not null,
year smallint not null,
holiday boolean default FALSE )
;
create table demodb.event(
eventid integer not null primary key,
venueid integer not null,
catid integer not null,
dateid integer not null,
eventname varchar(200),
starttime timestamp)
;
create table demodb.listing(
listid integer not null primary key,
sellerid integer not null,
eventid integer not null,
dateid integer not null,
numtickets smallint not null,
priceperticket decimal(8,2),
totalprice decimal(8,2),
listtime timestamp)
;
create table demodb.sales(
salesid integer not null primary key,
listid integer not null,
sellerid integer not null,
buyerid integer not null,
eventid integer not null,
dateid integer not null,
qtysold smallint not null,
pricepaid decimal(8,2),
commission decimal(8,2),
saletime timestamp)
;
demodb
が作成されると、Redshift側にも自動的に反映されています。
デフォルトでは、Aurora MySQLの全データベースの全テーブルが、Redshift側に統合されます。
ちなみに、Aurora MySQL (Serverless v2) はData APIが使えず、クエリエディタに対応していないため、 今回は踏み台用のEC2を立てて、Systems Managerのセッションマネージャーを使ってAurora MySQLにポートフォワーディングして接続しています。 参考までに、今回の踏み台用のEC2のCfnも掲載しておきます。
踏み台用EC2 Cfnテンプレート
05_EC2_bastion.yaml
AWSTemplateFormatVersion: 2010-09-09
Parameters:
ProjectName:
Type: String
Default: poc-zeroetl
Stage:
Type: String
Default: dev
AllowedValues:
- dev
InstanceType:
Type: String
Default: "t3a.nano"
KeyPairName:
Type: String
Resources:
EC2SG:
Type: AWS::EC2::SecurityGroup
Properties:
GroupDescription: "Bastion EC2 Security Group"
VpcId: !ImportValue VPC01
Tags:
- Key: Name
Value: !Sub "${ProjectName}-${Stage}-ec2-bastion-sg"
EndpointSG:
Type: AWS::EC2::SecurityGroup
Properties:
GroupDescription: "Bastion Endpoint Security Group"
VpcId: !ImportValue VPC01
SecurityGroupIngress:
- IpProtocol: tcp
FromPort: 443
ToPort: 443
SourceSecurityGroupId: !Ref EC2SG
Tags:
- Key: Name
Value: !Sub "${ProjectName}-${Stage}-endpoint-bastion-sg"
IamRole:
Type: AWS::IAM::Role
Properties:
RoleName: !Sub "${ProjectName}-${Stage}-ec2-bastion-role"
AssumeRolePolicyDocument:
Version: "2012-10-17"
Statement:
- Effect: Allow
Principal:
Service:
- ec2.amazonaws.com
Action:
- sts:AssumeRole
ManagedPolicyArns:
- arn:aws:iam::aws:policy/AmazonSSMManagedInstanceCore
InstanceProfile:
Type: AWS::IAM::InstanceProfile
Properties:
Path: /
Roles:
- !Ref IamRole
EC2Instance:
Type: "AWS::EC2::Instance"
Properties:
Tags:
- Key: Name
Value: !Sub "${ProjectName}-${Stage}-ec2-bastion"
ImageId: 'ami-031134f7a79b6e424' ## Amazon Linux 2023 x86-64
InstanceType: !Ref InstanceType
KeyName: !Ref KeyPairName
DisableApiTermination: false
EbsOptimized: false
SecurityGroupIds:
- !Ref EC2SG
SubnetId: !ImportValue ServiceSubnet01
IamInstanceProfile: !Ref InstanceProfile
SSMVPCEndpoint:
Type: "AWS::EC2::VPCEndpoint"
Properties:
VpcEndpointType: Interface
PrivateDnsEnabled: true
ServiceName: !Sub 'com.amazonaws.${AWS::Region}.ssm'
VpcId: !ImportValue VPC01
SubnetIds:
- !ImportValue ServiceSubnet01
SecurityGroupIds:
- !Ref EndpointSG
SSMMessagesVPCEndpoint:
Type: "AWS::EC2::VPCEndpoint"
Properties:
VpcEndpointType: Interface
PrivateDnsEnabled: true
ServiceName: !Sub 'com.amazonaws.${AWS::Region}.ssmmessages'
VpcId: !ImportValue VPC01
SubnetIds:
- !ImportValue ServiceSubnet01
SecurityGroupIds:
- !Ref EndpointSG
EC2MessagesVPCEndpoint:
Type: "AWS::EC2::VPCEndpoint"
Properties:
VpcEndpointType: Interface
PrivateDnsEnabled: true
ServiceName: !Sub 'com.amazonaws.${AWS::Region}.ec2messages'
VpcId: !ImportValue VPC01
SubnetIds:
- !ImportValue ServiceSubnet01
SecurityGroupIds:
- !Ref EndpointSG
フィルタリング編集
今回は、 demodb
の users
テーブルは連携しないように、フィルタリングをかけてみます。
まずはマネジメントコンソール上で操作してみましょう。 RDSの左側メニュー「ゼロのETL統合」から、今回作成したzeroETL統合をクリックします。
デフォルトでは、「全データベース・全テーブル」がRedshiftに統合されている設定になっています。
「変更」ボタンをクリックして、このフィルタリング設定を変更してみます。
今回は demodb
の users
テーブルを除外するフィルタを設定します。
「ソース」のところの「フィルターを追加する」ボタンをクリックし、追加するフィルタを設定します。
今回の場合、フィルタータイプは「除外する」を選択し、フィルター式には「 demodb.users
」と入力します。
その後、ページ下部にある「続行」ボタンをクリックします。
変更の確認画面で、「データフィルタリングオプション」の内容を確認し、「変更内容を保存」をクリックします。
ステータスが「変更中」となるので、「アクティブ」になるまで待ちます。(最大30分程度かかります)
ステータスがアクティブになったら、Redshift側のクエリエディタを見てみましょう。
更新をかけると、 demodb
の users
テーブルが表示されなくなっています。
また、Selectクエリを実行するとエラーになります。
Redshift側の統合メトリクスを見ると、レプリケートされたテーブルが1つ減っていることも確認できます。
これで、特定のテーブルだけ除外するフィルタリング設定ができました。
今度はCLIで設定してみましょう。
現状の統合の設定は以下のコマンドで確認できます。
aws rds describe-integrations
{
"Integrations": [
{
"SourceArn": "<省略>",
"TargetArn": "<省略>",
"IntegrationName": "poc-zeroetl-dev-zeroETL-Integration",
"IntegrationArn": "<省略>",
"KMSKeyId": "<省略>",
"Status": "active",
"Tags": [
<省略>
],
"CreateTime": "<省略>",
"DataFilter": "include: *.*, exclude: demodb.users"
}
]
}
DataFilter
の項目に、現状のフィルタリング設定が表示されます。
( DataFilter
の項目が表示されない場合は、AWS CLIを最新版にアップデートすると良いでしょう)
次のコマンドを実行して、フィルタリング設定を変更してみましょう。
aws rds modify-integration --integration-identifier "<integration_id>" --data-filter 'exclude: *.*, include: demodb.*, exclude: demodb.users'
{
"Integrations": [
{
"SourceArn": "<省略>",
"TargetArn": "<省略>",
"IntegrationName": "poc-zeroetl-dev-zeroETL-Integration",
"IntegrationArn": "<省略>",
"KMSKeyId": "<省略>",
"Status": "modifying",
"Tags": [
<省略>
],
"CreateTime": "<省略>",
"DataFilter": "exclude: *.*, include: demodb.*, exclude: demodb.users"
}
]
}
ステータスがアクティブになるまで待ちます。
アクティブになると、 demodb
の users
テーブル以外のテーブルが連携されます。
試しに、 zeroetldb
に適当なテーブルを create table
文で作成してみても、 zeroetldb
側にはテーブルが連携されていないことがわかります。
フィルター式の順序
フィルター式の順序は考慮しておく必要があります。 マネジメントコンソール上では「上から下へ」、AWS CLI上では「左から右へ」評価順がなされます。 後続のフィルター式は、前のフィルター式を上書きします。
最初の式は以下の通りでした。
"DataFilter": "include: *.*, exclude: demodb.users"
最初に include: *.*
が評価され、全てのデータベース・全てのテーブルを包含する設定になります。
次に exclude: demodb.users
が評価され、 demodb.users
が除外する設定が追加されます。
そのため、 demodb.users
の除外設定が優先されて、 demodb.users
はRedshift側に統合されないようになりました。
まとめ
いかがでしたか?
今までは Auroraの全てのデータベース・全てのテーブルがRedshiftに取り込まれていましたが、フィルタリング機能によって、一部のテーブルだけRedshiftに連携することができるようになりました。
また、Cloudformationテンプレートで、zeroETL統合を開始できるようになりました。 これで、統合開始までの面倒な設定作業がだいぶ楽になりました。
この記事がお役に立てれば、幸いです。