Simplifying information processing at Capitec with Amazon Redshift integration for Apache Spark

This submit is co-written with Preshen Goobiah and Johan Olivier from Capitec.

Apache Spark is a widely-used open supply distributed processing system famend for dealing with large-scale information workloads. It finds frequent utility amongst Spark builders working with Amazon EMR, Amazon SageMaker, AWS Glue and customized Spark functions.

Amazon Redshift affords seamless integration with Apache Spark, permitting you to simply entry your Redshift information on each Amazon Redshift provisioned clusters and Amazon Redshift Serverless. This integration expands the probabilities for AWS analytics and machine studying (ML) options, making the info warehouse accessible to a broader vary of functions.

With the Amazon Redshift integration for Apache Spark, you possibly can rapidly get began and effortlessly develop Spark functions utilizing common languages like Java, Scala, Python, SQL, and R. Your functions can seamlessly learn from and write to your Amazon Redshift information warehouse whereas sustaining optimum efficiency and transactional consistency. Moreover, you’ll profit from efficiency enhancements by means of pushdown optimizations, additional enhancing the effectivity of your operations.

Capitec, South Africa’s largest retail financial institution with over 21 million retail banking purchasers, goals to offer easy, reasonably priced and accessible monetary providers as a way to assist South Africans financial institution higher in order that they will stay higher. On this submit, we talk about the profitable integration of the open supply Amazon Redshift connector by Capitec’s shared providers Characteristic Platform staff. Because of using the Amazon Redshift integration for Apache Spark, developer productiveness elevated by an element of 10, function technology pipelines had been streamlined, and information duplication decreased to zero.

The enterprise alternative

There are 19 predictive fashions in scope for using 93 options constructed with AWS Glue throughout Capitec’s Retail Credit score divisions. Characteristic data are enriched with details and dimensions saved in Amazon Redshift. Apache PySpark was chosen to create options as a result of it affords a quick, decentralized, and scalable mechanism to wrangle information from numerous sources.

These manufacturing options play an important position in enabling real-time fixed-term mortgage functions, bank card functions, batch month-to-month credit score conduct monitoring, and batch day by day wage identification throughout the enterprise.

The information sourcing drawback

To make sure the reliability of PySpark information pipelines, it’s important to have constant record-level information from each dimensional and reality tables saved within the Enterprise Information Warehouse (EDW). These tables are then joined with tables from the Enterprise Information Lake (EDL) at runtime.

Throughout function growth, information engineers require a seamless interface to the EDW. This interface permits them to entry and combine the required information from the EDW into the info pipelines, enabling environment friendly growth and testing of options.

Earlier answer course of

Within the earlier answer, product staff information engineers spent half-hour per run to manually expose Redshift information to Spark. The steps included the next:

1. Assemble a predicated question in Python.
2. Submit an UNLOAD question by way of the Amazon Redshift Information API.
3. Catalog information within the AWS Glue Information Catalog by way of the AWS SDK for Pandas utilizing sampling.

This strategy posed points for giant datasets, required recurring upkeep from the platform staff, and was advanced to automate.

Present answer overview

Capitec was capable of resolve these issues with the Amazon Redshift integration for Apache Spark inside function technology pipelines. The structure is outlined within the following diagram.

The workflow consists of the next steps:

1. Inner libraries are put in into the AWS Glue PySpark job by way of AWS CodeArtifact.
2. An AWS Glue job retrieves Redshift cluster credentials from AWS Secrets and techniques Supervisor and units up the Amazon Redshift connection (injects cluster credentials, unload areas, file codecs) by way of the shared inside library. The Amazon Redshift integration for Apache Spark additionally helps utilizing AWS Id and Entry Administration (IAM) to retrieve credentials and connect with Amazon Redshift.
3. The Spark question is translated to an Amazon Redshift optimized question and submitted to the EDW. That is achieved by the Amazon Redshift integration for Apache Spark.
4. The EDW dataset is unloaded into a brief prefix in an Amazon Easy Storage Service (Amazon S3) bucket.
5. The EDW dataset from the S3 bucket is loaded into Spark executors by way of the Amazon Redshift integration for Apache Spark.
6. The EDL dataset is loaded into Spark executors by way of the AWS Glue Information Catalog.

These elements work collectively to make sure that information engineers and manufacturing information pipelines have the required instruments to implement the Amazon Redshift integration for Apache Spark, run queries, and facilitate the unloading of knowledge from Amazon Redshift to the EDL.

Utilizing the Amazon Redshift integration for Apache Spark in AWS Glue 4.0

On this part, we display the utility of the Amazon Redshift integration for Apache Spark by enriching a mortgage utility desk residing within the S3 information lake with consumer info from the Redshift information warehouse in PySpark.

The dimclient desk in Amazon Redshift accommodates the next columns:

• ClientKey – INT8
• ClientAltKey – VARCHAR50
• PartyIdentifierNumber – VARCHAR20
• ClientCreateDate – DATE
• IsCancelled – INT2
• RowIsCurrent – INT2

The loanapplication desk within the AWS Glue Information Catalog accommodates the next columns:

• RecordID – BIGINT
• LogDate – TIMESTAMP
• PartyIdentifierNumber – STRING

The Redshift desk is learn by way of the Amazon Redshift integration for Apache Spark and cached. See the next code:

import pyspark.sql.features as F
from pyspark.sql import SQLContext
sc = # current SparkContext
sql_context = SQLContext(sc)

secretsmanager_client = boto3.consumer('secretsmanager')
secret_manager_response = secretsmanager_client.get_secret_value(
    SecretId='string',
    VersionId='string',
    VersionStage="string"
)
username = # get username from secret_manager_response
password = # get password from secret_manager_response
url = "jdbc:redshift://redshifthost:5439/database?consumer=" + username + "&password=" + password

read_config = {
    "url": url,
    "tempdir": "s3://<capitec-redshift-temp-bucket>/<uuid>/",
    "unload_s3_format": "PARQUET"
}

d_client = (
    spark.learn.format("io.github.spark_redshift_community.spark.redshift")
    .choices(**read_config)
    .possibility("question", f"choose * from edw_core.dimclient")
    .load()
    .the place((F.col("RowIsCurrent") == 1) & (F.col("isCancelled") == 0))
    .choose(
        F.col("PartyIdentifierNumber"),
        F.col("ClientCreateDate")
    )
    .cache()
)

Mortgage utility data are learn in from the S3 information lake and enriched with the dimclient desk on Amazon Redshift info:

import pyspark.sql.features as F
from awsglue.context import GlueContext
from pyspark import SparkContext

glue_ctx = GlueContext(SparkContext.getOrCreate())

push_down_predicate = (
    f"meta_extract_start_utc_ms between "
    f"'2023-07-12"
    f" 18:00:00.000000' and "
    f"'2023-07-13 06:00:00.000000'"
)

database_name="loan_application_system"
table_name="dbo_view_loan_applications"
catalog_id = # Glue Information Catalog

# Deciding on solely the next columns
initial_select_cols=[
            "RecordID",
            "LogDate",
            "PartyIdentifierNumber"
        ]

d_controller = (glue_ctx.create_dynamic_frame.from_catalog(catalog_id=catalog_id,
                                            database=database_name,
                                            table_name=table_name,
                                            push_down_predicate=push_down_predicate)
                .toDF()
                .choose(*initial_select_cols)
                .withColumn("LogDate", F.date_format("LogDate", "yyyy-MM-dd").forged("string"))
                .dropDuplicates())

# Left Be a part of on PartyIdentifierNumber and enriching the mortgage utility document
d_controller_enriched = d_controller.be a part of(d_client, on=["PartyIdentifierNumber"], how="left").cache()

In consequence, the mortgage utility document (from the S3 information lake) is enriched with the ClientCreateDate column (from Amazon Redshift).

How the Amazon Redshift integration for Apache Spark solves the info sourcing drawback

The Amazon Redshift integration for Apache Spark successfully addresses the info sourcing drawback by means of the next mechanisms:

• Simply-in-time studying – The Amazon Redshift integration for Apache Spark connector reads Redshift tables in a just-in-time method, guaranteeing the consistency of knowledge and schema. That is notably helpful for Sort 2 slowly altering dimension (SCD) and timespan accumulating snapshot details. By combining these Redshift tables with the supply system AWS Glue Information Catalog tables from the EDL inside manufacturing PySpark pipelines, the connector allows seamless integration of knowledge from a number of sources whereas sustaining information integrity.
• Optimized Redshift queries – The Amazon Redshift integration for Apache Spark performs an important position in changing the Spark question plan into an optimized Redshift question. This conversion course of simplifies the event expertise for the product staff by adhering to the info locality precept. The optimized queries use the capabilities and efficiency optimizations of Amazon Redshift, guaranteeing environment friendly information retrieval and processing from Amazon Redshift for the PySpark pipelines. This helps streamline the event course of whereas enhancing the general efficiency of the info sourcing operations.

Gaining the very best efficiency

The Amazon Redshift integration for Apache Spark robotically applies predicate and question pushdown to optimize efficiency. You possibly can achieve efficiency enhancements through the use of the default Parquet format used for unloading with this integration.

For added particulars and code samples, consult with New – Amazon Redshift Integration with Apache Spark.

Answer Advantages

The adoption of the mixing yielded a number of vital advantages for the staff:

• Enhanced developer productiveness – The PySpark interface supplied by the mixing boosted developer productiveness by an element of 10, enabling smoother interplay with Amazon Redshift.
• Elimination of knowledge duplication – Duplicate and AWS Glue cataloged Redshift tables within the information lake had been eradicated, leading to a extra streamlined information surroundings.
• Decreased EDW load – The combination facilitated selective information unloading, minimizing the load on the EDW by extracting solely the required information.

By utilizing the Amazon Redshift integration for Apache Spark, Capitec has paved the way in which for improved information processing, elevated productiveness, and a extra environment friendly function engineering ecosystem.

Conclusion

On this submit, we mentioned how the Capitec staff efficiently carried out the Apache Spark Amazon Redshift integration for Apache Spark to simplify their function computation workflows. They emphasised the significance of using decentralized and modular PySpark information pipelines for creating predictive mannequin options.

At present, the Amazon Redshift integration for Apache Spark is utilized by 7 manufacturing information pipelines and 20 growth pipelines, showcasing its effectiveness inside Capitec’s surroundings.

Shifting ahead, the shared providers Characteristic Platform staff at Capitec plans to develop the adoption of the Amazon Redshift integration for Apache Spark in several enterprise areas, aiming to additional improve information processing capabilities and promote environment friendly function engineering practices.

For added info on utilizing the Amazon Redshift integration for Apache Spark, consult with the next sources:

In regards to the Authors

Preshen Goobiah is the Lead Machine Studying Engineer for the Characteristic Platform at Capitec. He’s centered on designing and constructing Characteristic Retailer elements for enterprise use. In his spare time, he enjoys studying and touring.

Johan Olivier is a Senior Machine Studying Engineer for Capitec’s Mannequin Platform. He’s an entrepreneur and problem-solving fanatic. He enjoys music and socializing in his spare time.

Sudipta Bagchi is a Senior Specialist Options Architect at Amazon Net Companies. He has over 12 years of expertise in information and analytics, and helps clients design and construct scalable and high-performant analytics options. Exterior of labor, he loves operating, touring, and taking part in cricket. Join with him on LinkedIn.

Syed Humair is a Senior Analytics Specialist Options Architect at Amazon Net Companies (AWS). He has over 17 years of expertise in enterprise structure specializing in Information and AI/ML, serving to AWS clients globally to handle their enterprise and technical necessities. You possibly can join with him on LinkedIn.

Vuyisa Maswana is a Senior Options Architect at AWS, primarily based in Cape City. Vuyisa has a powerful give attention to serving to clients construct technical options to resolve enterprise issues. He has supported Capitec of their AWS journey since 2019.