How Swisscom automated Amazon Redshift as a part of their One Information Platform answer utilizing AWS CDK – Half 2

On this sequence, we discuss Swisscom’s journey of automating Amazon Redshift provisioning as a part of the Swisscom One Information Platform (ODP) answer utilizing the AWS Cloud Growth Package (AWS CDK), and we offer code snippets and the opposite helpful references.

In Half 1, we did a deep dive on provisioning a safe and compliant Redshift cluster utilizing the AWS CDK and the most effective practices of secret rotation. We additionally defined how Swisscom used AWS CDK {custom} sources to automate the creation of dynamic consumer teams which can be related for the AWS Identification and Entry Administration (IAM) roles matching totally different job capabilities.

On this publish, we discover utilizing the AWS CDK and a number of the key matters for self-service utilization of the provisioned Redshift cluster by end-users in addition to different managed companies and purposes. These matters embody federation with the Swisscom identification supplier (IdP), JDBC connections, detective controls utilizing AWS Config guidelines and remediation actions, value optimization utilizing the Redshift scheduler, and audit logging.

Scheduled actions

To optimize cost-efficiency for provisioned Redshift cluster deployments, Swisscom carried out a scheduling mechanism. This performance is pushed by the consumer configuration of the cluster, as described in Half 1 of this sequence, whereby the consumer could allow dynamic pausing and resuming of clusters based mostly on specified cron expressions:

redshift_options:
...
  use_scheduler: true                                         # Whether or not to make use of Redshift scheduler
  scheduler_pause_cron: "cron(00 18 ? * MON-FRI *)"           # Cron expression for scheduler pause
  scheduler_resume_cron: "cron(00 08 ? * MON-FRI *)"          # Cron expression for scheduler resume
...

This function permits Swisscom to cut back operational prices by suspending cluster exercise throughout off-peak hours. This results in important value financial savings by pausing and resuming clusters at acceptable occasions. The scheduling is achieved utilizing the AWS CloudFormation motion CfnScheduledAction. The next code illustrates how Swisscom carried out this scheduling:

if config.use_scheduler:
    cfn_scheduled_action_pause = aws_redshift.CfnScheduledAction(
        scope, "schedule-pause-action",
        # ...
        schedule=config.scheduler_pause_cron,
        # ...
        target_action=aws_redshift.CfnScheduledAction.ScheduledActionTypeProperty(
                         pause_cluster=aws_redshift.CfnScheduledAction.ResumeClusterMessageProperty(
                            cluster_identifier="cluster-identifier"
                         )
                      )
    )

    cfn_scheduled_action_resume = aws_redshift.CfnScheduledAction(
        scope, "schedule-resume-action",
        # ...
        schedule=config.scheduler_resume_cron,
        # ...
        target_action=aws_redshift.CfnScheduledAction.ScheduledActionTypeProperty(
                         resume_cluster=aws_redshift.CfnScheduledAction.ResumeClusterMessageProperty(
                            cluster_identifier="cluster-identifier"
                         )
                      )
    )

JDBC connections

The JDBC connectivity for Amazon Redshift clusters was additionally very versatile, adapting to user-defined subnet varieties and safety teams within the configuration:

redshift_options:
...
  subnet_type: "routable-private"         # 'routable-private' OR 'non-routable-private'
  security_group_id: "sg-test_redshift"   # Safety Group ID for Amazon Redshift (referenced group should exists in Account)
...

As illustrated within the ODP structure diagram in Half 1 of this sequence, a substantial a part of extract, remodel, and cargo (ETL) processes is anticipated to function outdoors of Amazon Redshift, throughout the serverless AWS Glue setting. Given this, Swisscom wanted a mechanism for AWS Glue to connect with Amazon Redshift. This connectivity to Redshift clusters is offered by JDBC by creating an AWS Glue connection throughout the AWS CDK code. This connection permits ETL processes to work together with the Redshift cluster by establishing a JDBC connection. The subnet and safety group outlined within the consumer configuration information the creation of JDBC connectivity. If no safety teams are outlined within the configuration, a default one is created. The connection is configured with particulars of the info product from which the Redshift cluster is being provisioned, like ETL consumer and default database, together with community parts like cluster endpoint, safety group, and subnet to make use of, offering safe and environment friendly information switch. The next code snippet demonstrates how this was achieved:

jdbc_connection = glue.Connection(
    scope, "redshift-glue-connection",
    kind=ConnectionType("JDBC"),
    connection_name="redshift-glue-connection",
    subnet=connection_subnet,
    security_groups=connection_security_groups,
    properties={
        "JDBC_CONNECTION_URL": f"jdbc:redshift://{cluster_endpoint}/{database_name}",
        "USERNAME": etl_user.username,
        "PASSWORD": etl_user.password.to_string(),
        "redshiftTmpDir": f"s3://{data_product_name}-redshift-work"
    }
)

By doing this, Swisscom made certain that serverless ETL workflows in AWS Glue can securely talk with newly provisioned Redshift cluster operating inside a secured digital non-public cloud (VPC).

Identification federation

Identification federation permits a centralized system (the IdP) for use for authenticating customers to be able to entry a service supplier like Amazon Redshift. A extra basic overview of the subject may be present in Identification Federation in AWS.

Identification federation not solely enhances safety as a consequence of its centralized consumer lifecycle administration and centralized authentication mechanism (for instance, supporting multi-factor authentication), but in addition improves the consumer expertise and reduces the general complexity of identification and entry administration and thereby additionally its governance.

In Swisscom’s setup, Microsoft Energetic Listing Providers are used for identification and entry administration. On the preliminary construct levels of ODP, Amazon Redshift provided two totally different choices for identification federation:

In Swisscom’s context, in the course of the preliminary implementation, Swisscom opted for IAM-based SAML 2.0 IdP federation as a result of it is a extra basic strategy, which may also be used for different AWS companies, akin to Amazon QuickSight (see Establishing IdP federation utilizing IAM and QuickSight).

At 2023 AWS re:Invent, AWS introduced a new connection choice to Amazon Redshift based mostly on AWS IAM Identification Middle. IAM Identification Middle gives a single place for workforce identities in AWS, permitting the creation of customers and teams straight inside itself or by federation with normal IdPs like Okta, PingOne, Microsoft Entra ID (Azure AD), or any IdP that helps SAML 2.0 and SCIM. It additionally gives a single sign-on (SSO) expertise for Redshift options and different analytics companies akin to Amazon Redshift Question Editor V2 (see Combine Identification Supplier (IdP) with Amazon Redshift Question Editor V2 utilizing AWS IAM Identification Middle for seamless Single Signal-On), QuickSight, and AWS Lake Formation. Furthermore, a single IAM Identification Middle occasion may be shared with a number of Redshift clusters and workgroups with a easy auto-discovery and join functionality. It makes certain all Redshift clusters and workgroups have a constant view of customers, their attributes, and teams. This complete setup matches properly with ODP’s imaginative and prescient of offering self-service analytics throughout the Swisscom workforce with needed safety controls in place. On the time of writing, Swisscom is actively working in the direction of utilizing IAM Identification Middle as the usual federation answer for ODP. The next diagram illustrates the high-level structure for the work in progress.

Audit logging

Amazon Redshift audit logging is helpful for auditing for safety functions, monitoring, and troubleshooting. The logging gives info, such because the IP handle of the consumer’s laptop, the kind of authentication utilized by the consumer, or the timestamp of the request. Amazon Redshift logs the SQL operations, together with connection makes an attempt, queries, and modifications, and makes it simple to trace the modifications. These logs may be accessed by SQL queries in opposition to system tables, saved to a safe Amazon Easy Storage Service (Amazon S3) location, or exported to Amazon CloudWatch.

Amazon Redshift logs info within the following log recordsdata:

• Connection log – Supplies info to watch customers connecting to the database and associated connection info like their IP handle.
• Person log – Logs details about modifications to database consumer definitions.
• Person exercise log – Tracks details about the varieties of queries that each the customers and the system carry out within the database. It’s helpful primarily for troubleshooting functions.

With the ODP answer, Swisscom needed to write down all of the Amazon Redshift logs to CloudWatch. That is at present in a roundabout way supported by the AWS CDK, so Swisscom carried out a workaround answer utilizing the AWS CDK {custom} sources choice, which invokes the SDK on the Redshift motion enableLogging. See the next code:

    custom_resources.AwsCustomResource(self, f"{self.cluster_identifier}-custom-sdk-logging",
           on_update=custom_resources.AwsSdkCall(
               service="Redshift",
               motion="enableLogging",
               parameters={
                   "ClusterIdentifier": self.cluster_identifier,
                   "LogDestinationType": "cloudwatch",
                   "LogExports": ["connectionlog","userlog","useractivitylog"],
               },
               physical_resource_id=custom_resources.PhysicalResourceId.of(
                   f"{self.account}-{self.area}-{self.cluster_identifier}-logging")
           ),
           coverage=custom_resources.AwsCustomResourcePolicy.from_sdk_calls(
               sources=[f"arn:aws:redshift:{self.region}:{self.account}:cluster:{self.cluster_identifier}"]
           )
        )

AWS Config guidelines and remediation

After a Redshift cluster has been deployed, Swisscom wanted to be sure that the cluster meets the governance guidelines outlined in each time limit after creation. For that, Swisscom determined to make use of AWS Config.

AWS Config gives an in depth view of the configuration of AWS sources in your AWS account. This contains how the sources are associated to 1 one other and the way they have been configured previously so you possibly can see how the configurations and relationships change over time.

An AWS useful resource is an entity you possibly can work with in AWS, akin to an Amazon Elastic Compute Cloud (Amazon EC2) occasion, Amazon Elastic Block Retailer (Amazon EBS) quantity, safety group, or Amazon VPC.

The next diagram illustrates the method Swisscom carried out.

If an AWS Config rule isn’t compliant, a remediation may be utilized. Swisscom outlined the pause cluster motion as default in case of a non-compliant cluster (based mostly in your necessities, different remediation actions are doable). That is coated utilizing an AWS Programs Supervisor automation doc (SSM doc).

Automation, a functionality of Programs Supervisor, simplifies widespread upkeep, deployment, and remediation duties for AWS companies like Amazon EC2, Amazon Relational Database Service (Amazon RDS), Amazon Redshift, Amazon S3, and plenty of extra.

The SSM doc relies on the AWS doc AWSConfigRemediation-DeleteRedshiftCluster. It appears like the next code:

description: | 
  ### Doc title - PauseRedshiftCluster-WithCheck 

  ## What does this doc do? 
  This doc pauses the given Amazon Redshift cluster utilizing the [PauseCluster](https://docs.aws.amazon.com/redshift/newest/APIReference/API_PauseCluster.html) API. 

  ## Enter Parameters 
  * AutomationAssumeRole: (Required) The ARN of the position that enables Automation to carry out the actions in your behalf. 
  * ClusterIdentifier: (Required) The identifier of the Amazon Redshift Cluster. 

  ## Output Parameters 
  * PauseRedshiftClusterWithoutSnapShot.Response: The usual HTTP response from the PauseCluster API. 
  * PauseRedshiftClusterWithSnapShot.Response: The usual HTTP response from the PauseCluster API. 
schemaVersion: '0.3' 
assumeRole: '{{ AutomationAssumeRole }}' 
parameters: 
  AutomationAssumeRole: 
    kind: String 
    description: (Required) The ARN of the position that enables Automation to carry out the actions in your behalf. 
    allowedPattern: '^arn:aws[a-z0-9-]*:iam::d{12}:position/[w-/.@+=,]{1,1017}$' 
  ClusterIdentifier: 
    kind: String 
    description: (Required) The identifier of the Amazon Redshift Cluster. 
    allowedPattern: '[a-z]{1}[a-z0-9_.-]{0,62}' 
mainSteps: 
  - title: GetRedshiftClusterStatus 
    motion: 'aws:executeAwsApi' 
    inputs: 
      ClusterIdentifier: '{{ ClusterIdentifier }}' 
      Service: redshift 
      Api: DescribeClusters 
    description: |- 
      ## GetRedshiftClusterStatus 
      Will get the standing for the given Amazon Redshift Cluster. 
    outputs: 
      - Title: ClusterStatus 
        Selector: '$.Clusters[0].ClusterStatus' 
        Kind: String 
    timeoutSeconds: 600 
  - title: Situation 
    motion: 'aws:department' 
    inputs: 
      Decisions: 
        - NextStep: PauseRedshiftCluster 
          Variable: '{{ GetRedshiftClusterStatus.ClusterStatus }}' 
          StringEquals: obtainable 
      Default: End 
  - title: PauseRedshiftCluster 
    motion: 'aws:executeAwsApi' 
    description: | 
      ## PauseRedshiftCluster 
      Makes PauseCluster API name utilizing Amazon Redshift Cluster identifier and pauses the cluster with out taking any remaining snapshot. 
      ## Outputs 
      * Response: The usual HTTP response from the PauseCluster API. 
    timeoutSeconds: 600 
    isEnd: false 
    nextStep: VerifyRedshiftClusterPause 
    inputs: 
      Service: redshift 
      Api: PauseCluster 
      ClusterIdentifier: '{{ ClusterIdentifier }}' 
    outputs: 
      - Title: Response 
        Selector: $ 
        Kind: StringMap 
  - title: VerifyRedshiftClusterPause 
    motion: 'aws:assertAwsResourceProperty' 
    timeoutSeconds: 600 
    isEnd: true 
    description: | 
      ## VerifyRedshiftClusterPause 
      Verifies the given Amazon Redshift Cluster is paused. 
    inputs: 
      Service: redshift 
      Api: DescribeClusters 
      ClusterIdentifier: '{{ ClusterIdentifier }}' 
      PropertySelector: '$.Clusters[0].ClusterStatus' 
      DesiredValues: 
        - pausing 
  - title: End 
    motion: 'aws:sleep' 
    inputs: 
      Period: PT1S 
    isEnd: true

The SSM automations doc is deployed with the AWS CDK:

from aws_cdk import aws_ssm as ssm  

ssm_document_content = #learn yaml doc as dict  

document_id = 'automation_id'   
document_name="automation_name" 

doc = ssm.CfnDocument(scope, id=document_id, content material=ssm_document_content,  
                           document_format="YAML", document_type="Automation", title=document_name) 

To run the automation doc, AWS Config wants the best permissions. You'll be able to create an IAM position for this goal:

from aws_cdk import iam 

#Create position for the automation 
role_name="role-to-pause-redshift"
automation_role = iam.Position(scope, 'role-to-pause-redshift-cluster', 
                           assumed_by=iam.ServicePrincipal('ssm.amazonaws.com'), 
                           role_name=role_name) 

automation_policy = iam.Coverage(scope, "policy-to-pause-cluster", 
                               policy_name="policy-to-pause-cluster", 
                               statements=[ 
                                   iam.PolicyStatement( 
                                       effect=iam.Effect.ALLOW, 
                                       actions=['redshift:PauseCluster', 
                                                'redshift:DescribeClusters'], 
                                       sources=['*'] 
                                   ) 
                               ]) 

automation_role.attach_inline_policy(automation_policy) 

Swisscom outlined the foundations to be utilized following AWS finest practices (see Safety Finest Practices for Amazon Redshift). These are deployed as AWS Config conformance packs. A conformance pack is a group of AWS Config guidelines and remediation actions that may be rapidly deployed as a single entity in an AWS account and AWS Area or throughout a company in AWS Organizations.

Conformance packs are created by authoring YAML templates that include the listing of AWS Config managed or {custom} guidelines and remediation actions. It’s also possible to use SSM paperwork to retailer your conformance pack templates on AWS and straight deploy conformance packs utilizing SSM doc names.

This AWS conformance pack may be deployed utilizing the AWS CDK:

from aws_cdk import aws_config  
  
conformance_pack_template = # learn yaml file as str 
conformance_pack_content = # substitute `role_arn_for_substitution` and `document_for_substitution` in conformance_pack_template

conformance_pack_id = 'conformance-pack-id' 
conformance_pack_name="conformance-pack-name" 


conformance_pack = aws_config.CfnConformancePack(scope, id=conformance_pack_id, 
                                                 conformance_pack_name=conformance_pack_name, 
                                                 template_body=conformance_pack_content) 

Conclusion

Swisscom is constructing its next-generation data-as-a-service platform by a mix of automated provisioning processes, superior safety features, and user-configurable choices to cater for numerous information dealing with and information merchandise’ wants. The combination of the Amazon Redshift assemble within the ODP framework is a big stride in Swisscom’s journey in the direction of a extra linked and data-driven enterprise panorama.

In Half 1 of this sequence, we demonstrated the right way to provision a safe and compliant Redshift cluster utilizing the AWS CDK in addition to the right way to cope with the most effective practices of secret rotation. We additionally confirmed the right way to use AWS CDK {custom} sources in automating the creation of dynamic consumer teams which can be related for the IAM roles matching totally different job capabilities.

On this publish, we confirmed, by the utilization of the AWS CDK, the right way to handle key Redshift cluster utilization matters akin to federation with the Swisscom IdP, JDBC connections, detective controls utilizing AWS Config guidelines and remediation actions, value optimization utilizing the Redshift scheduler, and audit logging.

The code snippets on this publish are offered as is and can should be tailored to your particular use circumstances. Earlier than you get began, we extremely advocate talking to an Amazon Redshift specialist.

In regards to the Authors

Asad bin Imtiaz is an Professional Information Engineer at Swisscom, with over 17 years of expertise in architecting and implementing enterprise-level information options.

Jesús Montelongo Hernández is an Professional Cloud Information Engineer at Swisscom. He has over 20 years of expertise in IT techniques, information warehousing, and information engineering.

Samuel Bucheli is a Lead Cloud Architect at Zühlke Engineering AG. He has over 20 years of expertise in software program engineering, software program structure, and cloud structure.

Srikanth Potu is a Senior Advisor in EMEA, a part of the Skilled Providers group at Amazon Internet Providers. He has over 25 years of expertise in Enterprise information structure, databases and information warehousing.