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

On this sequence, we speak about Swisscom’s journey of automating Amazon Redshift provisioning as a part of the Swisscom One Information Platform (ODP) answer utilizing the AWS Cloud Growth Equipment (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 perfect practices of secret rotation. We additionally defined how Swisscom used AWS CDK {custom} assets to automate the creation of dynamic person teams which can be related for the AWS Identification and Entry Administration (IAM) roles matching completely different job features.

On this publish, we discover utilizing the AWS CDK and a number of the key subjects for self-service utilization of the provisioned Redshift cluster by end-users in addition to different managed providers and purposes. These subjects 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 person configuration of the cluster, as described in Half 1 of this sequence, whereby the person could allow dynamic pausing and resuming of clusters primarily based 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 characteristic permits Swisscom to scale 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 sorts 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 atmosphere. Given this, Swisscom wanted a mechanism for AWS Glue to connect with Amazon Redshift. This connectivity to Redshift clusters is offered by way of 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 person 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 person and default database, together with community components like cluster endpoint, safety group, and subnet to make use of, offering safe and environment friendly knowledge 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 positive that serverless ETL workflows in AWS Glue can securely talk with newly provisioned Redshift cluster operating inside a secured digital personal cloud (VPC).

Identification federation

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

Identification federation not solely enhances safety on account of its centralized person lifecycle administration and centralized authentication mechanism (for instance, supporting multi-factor authentication), but additionally improves the person 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 supplied two completely 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 common strategy, which may also be used for different AWS providers, corresponding 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 primarily based on AWS IAM Identification Middle. IAM Identification Middle gives a single place for workforce identities in AWS, permitting the creation of customers and teams instantly 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 providers corresponding 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 might be shared with a number of Redshift clusters and workgroups with a easy auto-discovery and join functionality. It makes positive all Redshift clusters and workgroups have a constant view of customers, their attributes, and teams. This entire setup matches effectively with ODP’s imaginative and prescient of offering self-service analytics throughout the Swisscom workforce with essential 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 data, such because the IP tackle of the person’s pc, the kind of authentication utilized by the person, or the timestamp of the request. Amazon Redshift logs the SQL operations, together with connection makes an attempt, queries, and modifications, and makes it easy to trace the modifications. These logs might be accessed by way of 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 data within the following log recordsdata:

• Connection log – Gives data to observe customers connecting to the database and associated connection data like their IP tackle.
• Consumer log – Logs details about modifications to database person definitions.
• Consumer exercise log – Tracks details about the sorts 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 the moment in a roundabout way supported by the AWS CDK, so Swisscom carried out a workaround answer utilizing the AWS CDK {custom} assets 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(
               assets=[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 guarantee that the cluster meets the governance guidelines outlined in each cut-off date after creation. For that, Swisscom determined to make use of AWS Config.

AWS Config gives an in depth view of the configuration of AWS assets in your AWS account. This consists of how the assets are associated to at least one 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, corresponding 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 might be utilized. Swisscom outlined the pause cluster motion as default in case of a non-compliant cluster (primarily based in your necessities, different remediation actions are attainable). That is coated utilizing an AWS Methods Supervisor automation doc (SSM doc).

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

The SSM doc is predicated on the AWS doc AWSConfigRemediation-DeleteRedshiftCluster. It seems to be like the next code:

description: | 
  ### Doc identify - 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 permits 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 permits 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: 
  - identify: 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' 
        Sort: String 
    timeoutSeconds: 600 
  - identify: Situation 
    motion: 'aws:department' 
    inputs: 
      Decisions: 
        - NextStep: PauseRedshiftCluster 
          Variable: '{{ GetRedshiftClusterStatus.ClusterStatus }}' 
          StringEquals: accessible 
      Default: End 
  - identify: 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: $ 
        Sort: StringMap 
  - identify: 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 
  - identify: End 
    motion: 'aws:sleep' 
    inputs: 
      Length: 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", identify=document_name) 

To run the automation doc, AWS Config wants the correct permissions. You possibly can create an IAM position for this objective:

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'], 
                                       assets=['*'] 
                                   ) 
                               ]) 

automation_role.attach_inline_policy(automation_policy) 

Swisscom outlined the foundations to be utilized following AWS finest practices (see Safety Greatest 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 record of AWS Config managed or {custom} guidelines and remediation actions. You can too use SSM paperwork to retailer your conformance pack templates on AWS and instantly deploy conformance packs utilizing SSM doc names.

This AWS conformance pack might 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 way of a mix of automated provisioning processes, superior security measures, and user-configurable choices to cater for numerous knowledge dealing with and knowledge merchandise’ wants. The mixing of the Amazon Redshift assemble within the ODP framework is a big stride in Swisscom’s journey in the direction of a extra related and data-driven enterprise panorama.

In Half 1 of this sequence, we demonstrated learn how to provision a safe and compliant Redshift cluster utilizing the AWS CDK in addition to learn how to take care of the perfect practices of secret rotation. We additionally confirmed learn how to use AWS CDK {custom} assets in automating the creation of dynamic person teams which can be related for the IAM roles matching completely different job features.

On this publish, we confirmed, by way of the utilization of the AWS CDK, learn how to tackle key Redshift cluster utilization subjects corresponding 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 suggest talking to an Amazon Redshift specialist.

In regards to the Authors

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

Jesús Montelongo Hernández is an Knowledgeable Cloud Information Engineer at Swisscom. He has over 20 years of expertise in IT programs, knowledge warehousing, and knowledge 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 Guide in EMEA, a part of the Skilled Providers group at Amazon Internet Providers. He has over 25 years of expertise in Enterprise knowledge structure, databases and knowledge warehousing.