。

DynamoDB能在任何规模下实现不到10毫秒级的一致响应#xff0c;并且它的存储空间无限#xff0c;可在任何规模提供可靠的性能。

DynamoDB

提…AWS

键值数据库旨在运行任何规模的高性能应用程序。

DynamoDB能在任何规模下实现不到10毫秒级的一致响应并且它的存储空间无限可在任何规模提供可靠的性能。

DynamoDB

提供内置安全性、连续备份、自动多区域复制、内存缓存和数据导出工具。

Redshift简介

Redshift是一个快速、功能强大、完全托管的PB级别数据仓库服务。

用户可以在刚开始使用几百GB的数据然后在后期扩容到PB级别的数据容量。

Redshift是一种联机分析处理OLAPOnline

Analytics

Processing的类型支持复杂的分析操作侧重决策支持并且能提供直观易懂的查询结果。

资源准备

internet-gateway-id(允许与外界进行通信)destination10.10.0.0/16

route

允许任意ip对443端口进行访问允许自己的ipdui22端口进行访问以便ssh到服务器上向数据库插入数据glue

redshift

允许vpc内部10.10.0.0/24对5439端口进行访问允许glue

connection

允许vpc内部10.10.0.0/24对5439端口进行访问允许kenisis

对5439端口进行访问

dynamodb-to-redshift-vpccustom:bucketNamePrefix:

awsregion:

${self:service}deploymentBucket:name:

com.${self:custom.bucketNamePrefix}.deploy-bucketserverSideEncryption:

AES256resources:Parameters:VpcName:Type:

StringDefault:

AWS::EC2::VPCProperties:CidrBlock:

trueInstanceTenancy:

AWS::EC2::InternetGatewayProperties:Tags:-

Key:

VPC_${VpcName}_InternetGatewayVPCGatewayAttachment:Type:

AWS::EC2::VPCGatewayAttachmentProperties:VpcId:

!Ref

groupWebServerSecurityGroup:Type:

AWS::EC2::SecurityGroupProperties:GroupDescription:

Allow

VPC_${VpcName}_WebServerSecurityGroup#

public

AWS::EC2::RouteTableProperties:VpcId:

!Ref

VPC_${VpcName}_RouteTablePublicRouteTablePublicInternetRoute:Type:

VPCGatewayAttachmentProperties:RouteTableId:

!Ref

RouteTablePublicDestinationCidrBlock:

0.0.0.0/0GatewayId:

AWS::EC2::SubnetProperties:AvailabilityZone:

!Select

10.10.0.0/24MapPublicIpOnLaunch:

trueVpcId:

VPC_${VpcName}_SubnetAPublicRouteTableAssociationAPublic:Type:

AWS::EC2::SubnetRouteTableAssociationProperties:SubnetId:

!Ref

RouteTablePublicSubnetBPublic:Type:

AWS::EC2::SubnetProperties:AvailabilityZone:

!Select

10.10.32.0/24MapPublicIpOnLaunch:

trueVpcId:

VPC_${VpcName}_SubnetBPublicRouteTableAssociationBPublic:Type:

AWS::EC2::SubnetRouteTableAssociationProperties:SubnetId:

!Ref

RouteTablePublicSubnetCPublic:Type:

AWS::EC2::SubnetProperties:AvailabilityZone:

!Select

10.10.64.0/24MapPublicIpOnLaunch:

trueVpcId:

VPC_${VpcName}_SubnetCPublicRouteTableAssociationCPublic:Type:

AWS::EC2::SubnetRouteTableAssociationProperties:SubnetId:

!Ref

groupPrivateRedshiftSecurityGroup:Type:

AWS::EC2::SecurityGroupProperties:GroupDescription:

Allow

GlueRedshiftConnectionSecurityGroup.GroupIdTags:-

Key:

VPC_${VpcName}_PrivateRedshiftSecurityGroup#

redshift

groupPublicRedshiftSecurityGroup:Type:

AWS::EC2::SecurityGroupProperties:GroupDescription:

Allow

VPC_${VpcName}_PublicRedshiftSecurityGroupGlueRedshiftConnectionSecurityGroup:Type:

AWS::EC2::SecurityGroupProperties:GroupDescription:

Allow

VPC_${VpcName}_GlueRedshiftConnectionSecurityGroupGlueRedshiftConnectionSecurityGroupSelfReferringInboundRule:Type:

AWS::EC2::SecurityGroupIngressProperties:GroupId:

!GetAtt

GlueRedshiftConnectionSecurityGroup.GroupIdIpProtocol:

tcpFromPort:

GlueRedshiftConnectionSecurityGroup.GroupIdSourceSecurityGroupOwnerId:

!Sub

AWS::EC2::EIPProperties:Domain:

vpcNatGateway:Type:

AWS::EC2::NatGatewayProperties:AllocationId:

!GetAtt

AWS::EC2::RouteTableProperties:VpcId:

!Ref

VPC_${VpcName}_RouteTablePrivateRouteTablePrivateRoute:Type:

AWS::EC2::RouteProperties:RouteTableId:

!Ref

RouteTablePrivateDestinationCidrBlock:

!Ref

AWS::EC2::SubnetProperties:AvailabilityZone:

!Select

VPC_${VpcName}_SubnetAPrivateRouteTableAssociationAPrivate:Type:

AWS::EC2::SubnetRouteTableAssociationProperties:SubnetId:

!Ref

RouteTablePrivateSubnetBPrivate:Type:

AWS::EC2::SubnetProperties:AvailabilityZone:

!Select

VPC_${VpcName}_SubnetBPrivateRouteTableAssociationBPrivate:Type:

AWS::EC2::SubnetRouteTableAssociationProperties:SubnetId:

!Ref

RouteTablePrivateSubnetCPrivate:Type:

AWS::EC2::SubnetProperties:AvailabilityZone:

!Select

VPC_${VpcName}_SubnetCPrivateRouteTableAssociationCPrivate:Type:

AWS::EC2::SubnetRouteTableAssociationProperties:SubnetId:

!Ref

RouteTablePrivateOutputs:VPC:Description:

VPC.Value:

${self:provider.stackName}SubnetsPublic:Description:

Subnets

${self:provider.stackName}-PublicSubnetsSubnetsPrivate:Description:

Subnets

${self:provider.stackName}-PrivateSubnetsDefaultSecurityGroup:Description:

VPC

VPC.DefaultSecurityGroupExport:Name:

!Sub

${self:provider.stackName}-DefaultSecurityGroupWebServerSecurityGroup:Description:

VPC

WebServerSecurityGroupExport:Name:

!Sub

${self:provider.stackName}-WebServerSecurityGroupPrivateRedshiftSecurityGroup:Description:

The

PrivateRedshiftSecurityGroupExport:Name:

!Sub

${self:provider.stackName}-PrivateRedshiftSecurityGroupPublicRedshiftSecurityGroup:Description:

The

PublicRedshiftSecurityGroupExport:Name:

!Sub

${self:provider.stackName}-PublicRedshiftSecurityGroupGlueRedshiftConnectionSecurityGroup:Description:

The

GlueRedshiftConnectionSecurityGroupExport:Name:

!Sub

${self:provider.stackName}-GlueSelfRefringSecurityGroupRedshift

Cluster

job同步数据到redshiftPubliclyAccessible必须设为false否则glue

job无法连接

job同步数据到redshiftPubliclyAccessible必须设为true且security

group允许kinesis

ip对5439端口进行访问否则firehose无法连接到redshift

使用public

dynamodb-to-redshift-redshiftcustom:bucketNamePrefix:

awsregion:

${self:service}deploymentBucket:name:

com.${self:custom.bucketNamePrefix}.deploy-bucketserverSideEncryption:

AES256resources:Parameters:ServiceName:Type:

StringDefault:

dynamodb-to-redshiftResources:PrivateClusterSubnetGroup:Type:

AWS::Redshift::ClusterSubnetGroupProperties:Description:

Private

${ServiceName}-vpc-PrivateSubnetsTags:-

Key:

private-subnetPrivateCluster:Type:

AWS::Redshift::ClusterProperties:ClusterIdentifier:

test-data-sync-redshiftClusterSubnetGroupName:

!Ref

ClusterSubnetGroupVpcSecurityGroupIds:-

Fn::ImportValue:

${ServiceName}-vpc-PrivateRedshiftSecurityGroupDBName:

devMasterUsername:

falsePublicClusterSubnetGroup:Type:

AWS::Redshift::ClusterSubnetGroupProperties:Description:

Public

${ServiceName}-vpc-PublicSubnetsTags:-

Key:

public-subnetPublicCluster:Type:

AWS::Redshift::ClusterProperties:ClusterIdentifier:

test-data-sync-redshift-publicClusterSubnetGroupName:

!Ref

PublicClusterSubnetGroupVpcSecurityGroupIds:-

Fn::ImportValue:

${ServiceName}-vpc-PublicRedshiftSecurityGroupDBName:

devMasterUsername:

trueOutputs:PrivateRedshiftEndpoint:Description:

Redshift

Cluster.Endpoint.AddressExport:Name:

!Sub

${self:provider.stackName}-PrivateRedshiftEndpointPrivateRedshiftPort:Description:

Redshift

Cluster.Endpoint.PortExport:Name:

!Sub

${self:provider.stackName}-PrivateRedshiftPortPublicRedshiftEndpoint:Description:

Public

PublicCluster.Endpoint.AddressExport:Name:

!Sub

${self:provider.stackName}-PublicRedshiftEndpointPublicRedshiftPort:Description:

Public

PublicCluster.Endpoint.PortExport:Name:

!Sub

${self:provider.stackName}-PublicRedshiftPort使用AWS

Glue

一次性整表同步对于典型的时间序列数据当前的数据写入和读取频率高越老的数据读写频率越低通常会采用为每个时间段每天创建一张表的方式来合理的分配WCU和RCU。

如果在当时时间段结束之后需要对该时间段内的所有数据进行复杂的分析操作则需要将dynamodb的整表同步到redshift

使用AWS

Crawler可以自动管理源表和目标表的scheme在Glue

Job

crawlercrawler需要连接dynamodb和redshift的权限以读取表的schemeDynamodb

glue

crawler用于读取dynamodb表生成对应的dynamodb

table

crawler:用于读取redshift表生成对应的redshift

table

job连接redshift需要用到的connectionIAM

role

s3://com.${bucketNamePrefix}.glue-temp-bucket/script/

部署文件glue-etl.yml

dynamodb-to-redshift-glue-etlcustom:bucketNamePrefix:

awsregion:

${self:service}deploymentBucket:name:

com.${self:custom.bucketNamePrefix}.deploy-bucketserverSideEncryption:

AES256resources:Parameters:DynamodbTableName:Type:

StringDefault:

TestSyncToRedshiftServiceName:Type:

StringDefault:

dynamodb-to-redshiftGlueBucketName:Type:

StringDefault:

com.${self:custom.bucketNamePrefix}.glue-etl-temp-bucketResources:TestTable:Type:

AWS::DynamoDB::TableProperties:TableName:

!Sub

${DynamodbTableName}BillingMode:

PAY_PER_REQUESTAttributeDefinitions:-

AttributeName:

AWS::IAM::RoleProperties:RoleName:

CrawlerRoleAssumeRolePolicyDocument:Version:

Effect:

sts:AssumeRoleManagedPolicyArns:-

arn:aws:iam::aws:policy/service-role/AWSGlueServiceRole-

arn:aws:iam::aws:policy/AmazonDynamoDBFullAccess-

arn:aws:iam::aws:policy/AmazonRedshiftFullAccess-

arn:aws:iam::aws:policy/AmazonS3FullAccessDynamodbDatabase:Type:

AWS::Glue::DatabaseProperties:CatalogId:

!Ref

AWS::AccountIdDatabaseInput:Name:

dynamodb-databaseDynamodbCrawler:Type:

AWS::Glue::CrawlerProperties:Name:

dynamodb-crawlerConfiguration:Role:

!GetAtt

DynamodbDatabaseTargets:DynamoDBTargets:-

Path:

${DynamodbTableName}SchemaChangePolicy:UpdateBehavior:

UPDATE_IN_DATABASEDeleteBehavior:

LOGSchedule:ScheduleExpression:

cron(0/10

minutesGlueRedshiftConnection:Type:

AWS::Glue::ConnectionProperties:CatalogId:

!Sub

${aws:accountId}ConnectionInput:Name:

${self:service}-redshift-connectionConnectionType:

JDBCMatchCriteria:

[]PhysicalConnectionRequirements:SecurityGroupIdList:-

Fn::ImportValue:

${ServiceName}-vpc-GlueSelfRefringSecurityGroupSubnetId:Fn::Select:-

Fn::Split:-

${ServiceName}-vpc-PrivateSubnetsConnectionProperties:JDBC_CONNECTION_URL:Fn::Join:-

jdbc:redshift://-

${ServiceName}-redshift-PrivateRedshiftEndpoint-

Fn::ImportValue:

${ServiceName}-redshift-PrivateRedshiftPort-

falseUSERNAME:

Redshift_admin_2022RedshiftDatabase:Type:

AWS::Glue::DatabaseProperties:CatalogId:

!Ref

AWS::AccountIdDatabaseInput:Name:

redshift-databaseRedshiftCrawler:Type:

AWS::Glue::CrawlerProperties:Name:

redshift-crawlerConfiguration:Role:

!GetAtt

RedshiftDatabaseTargets:JdbcTargets:-

ConnectionName:

dev/public/test_sync_to_redshiftSchemaChangePolicy:UpdateBehavior:

UPDATE_IN_DATABASEDeleteBehavior:

AWS::IAM::RoleProperties:RoleName:

RedshiftGlueJobRoleAssumeRolePolicyDocument:Version:

Effect:

sts:AssumeRoleManagedPolicyArns:-

arn:aws:iam::aws:policy/service-role/AWSGlueServiceRole-

arn:aws:iam::aws:policy/AmazonDynamoDBFullAccess-

arn:aws:iam::aws:policy/AmazonRedshiftFullAccess-

arn:aws:iam::aws:policy/AmazonS3FullAccess-

arn:aws:iam::aws:policy/CloudWatchLogsFullAccessGlueTempBucket:Type:

AWS::S3::BucketProperties:BucketName:

!Sub

dynamodb-to-redshift-glue-etl-jobRole:

!GetAtt

RedshiftGlueJobRole.ArnCommand:Name:

!Sub

s3://${GlueBucketName}/script/dynamodb-to-redshift.pyPythonVersion:

!Sub

s3://${GlueBucketName}/tmp/dynamodb-to-redshift/WorkerType:

G.1XNumberOfWorkers:

glueContext.create_dynamic_frame.from_catalog(databasedynamodb-database,table_nametestsynctoredshift,transformation_ctxDynamoDBtable_node1,

glueContext.write_dynamic_frame.from_catalog(frameDynamoDBtable_node1,databaseredshift-database,table_namedev_public_test_sync_to_redshift,redshift_tmp_dirargs[TempDir],transformation_ctxRedshiftCluster_node2,

)job.commit()测试

console](https://ap-southeast-1.console.aws.amazon.com/glue/home?regionap-southeast-1#catalog:tabtables)

create

v2](https://ap-southeast-1.console.aws.amazon.com/sqlworkbench/home?regionap-southeast-1#/client)

CREATE

public.test_sync_to_redshift(pk

varchar(200)

console](https://ap-southeast-1.console.aws.amazon.com/glue/home?regionap-southeast-1#catalog:tabtables)

run

v2](https://ap-southeast-1.console.aws.amazon.com/sqlworkbench/home?regionap-southeast-1#/client).

This

Redshift](https://docs.aws.amazon.com/redshift/latest/dg/t_Defining_constraints.html),

you

持续增量同步表的操作只支持插入部分支持更新不支持删除比如记录传感器每秒收集的数据记录网站用户的行为事件

由于kinesis

COPY命令与redshift进行集成的而redshift是不保证primary

key的唯一性如果在COPY命令的源数据中包含多条primary

key相同的数据比如对一条数据进行多次修改则会导致redshift表出现多条primary

key相同的数据。

部分支持更新的意思就是如果多条primary

kinesis

COPY命令与redshift进行集成的COPY命令是不支持删除的

架构图