Blog

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 knowledge workloads. It finds frequent utility amongst Spark builders working with Amazon EMR, Amazon SageMaker, AWS Glue and customized Spark functions.

Amazon Redshift gives seamless integration with Apache Spark, permitting you to simply entry your Redshift knowledge on each Amazon Redshift provisioned clusters and Amazon Redshift Serverless. This integration expands the chances 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 may rapidly get began and effortlessly develop Spark functions utilizing well-liked languages like Java, Scala, Python, SQL, and R. Your functions can seamlessly learn from and write to your Amazon Redshift knowledge 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 greatest retail financial institution with over 21 million retail banking purchasers, goals to supply easy, inexpensive and accessible monetary companies so as to assist South Africans financial institution higher in order that they’ll dwell higher. On this submit, we focus on the profitable integration of the open supply Amazon Redshift connector by Capitec’s shared companies Characteristic Platform crew. On account of using the Amazon Redshift integration for Apache Spark, developer productiveness elevated by an element of 10, characteristic era pipelines had been streamlined, and knowledge duplication diminished 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 information and dimensions saved in Amazon Redshift. Apache PySpark was chosen to create options as a result of it gives a quick, decentralized, and scalable mechanism to wrangle knowledge from various sources.

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

The information sourcing drawback

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

Throughout characteristic improvement, knowledge engineers require a seamless interface to the EDW. This interface permits them to entry and combine the mandatory knowledge from the EDW into the info pipelines, enabling environment friendly improvement and testing of options.

Earlier answer course of

Within the earlier answer, product crew knowledge engineers spent half-hour per run to manually expose Redshift knowledge 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 Knowledge API.
3. Catalog knowledge within the AWS Glue Knowledge Catalog by way of the AWS SDK for Pandas utilizing sampling.

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

Present answer overview

Capitec was in a position to resolve these issues with the Amazon Redshift integration for Apache Spark inside characteristic era pipelines. The structure is outlined within the following diagram.

The workflow contains the next steps:

1. Inside 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 places, file codecs) by way of the shared inner library. The Amazon Redshift integration for Apache Spark additionally helps utilizing AWS Identification 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 completed 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 Knowledge Catalog.

These elements work collectively to make sure that knowledge engineers and manufacturing knowledge pipelines have the mandatory 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 show the utility of the Amazon Redshift integration for Apache Spark by enriching a mortgage utility desk residing within the S3 knowledge lake with consumer info from the Redshift knowledge 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 Knowledge 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 = # present 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?person=" + 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 knowledge 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 Knowledge Catalog

# Choosing 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").solid("string"))
                .dropDuplicates())

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

Because of this, the mortgage utility document (from the S3 knowledge 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 significantly beneficial for Kind 2 slowly altering dimension (SCD) and timespan accumulating snapshot information. By combining these Redshift tables with the supply system AWS Glue Knowledge Catalog tables from the EDL inside manufacturing PySpark pipelines, the connector allows seamless integration of knowledge from a number of sources whereas sustaining knowledge integrity.
• Optimized Redshift queries – The Amazon Redshift integration for Apache Spark performs an important function in changing the Spark question plan into an optimized Redshift question. This conversion course of simplifies the event expertise for the product crew by adhering to the info locality precept. The optimized queries use the capabilities and efficiency optimizations of Amazon Redshift, guaranteeing environment friendly knowledge 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 acquire efficiency enhancements through the use of the default Parquet format used for unloading with this integration.

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

Answer Advantages

The adoption of the combination yielded a number of important advantages for the crew:

• Enhanced developer productiveness – The PySpark interface offered by the combination 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 knowledge lake had been eradicated, leading to a extra streamlined knowledge atmosphere.
• Diminished EDW load – The combination facilitated selective knowledge unloading, minimizing the load on the EDW by extracting solely the mandatory knowledge.

Through the use of the Amazon Redshift integration for Apache Spark, Capitec has paved the best way for improved knowledge processing, elevated productiveness, and a extra environment friendly characteristic engineering ecosystem.

Conclusion

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

At the moment, the Amazon Redshift integration for Apache Spark is utilized by 7 manufacturing knowledge pipelines and 20 improvement pipelines, showcasing its effectiveness inside Capitec’s atmosphere.

Transferring ahead, the shared companies Characteristic Platform crew at Capitec plans to increase the adoption of the Amazon Redshift integration for Apache Spark in several enterprise areas, aiming to additional improve knowledge processing capabilities and promote environment friendly characteristic engineering practices.

For extra 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 knowledge and analytics, and helps prospects design and construct scalable and high-performant analytics options. Outdoors of labor, he loves working, 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 Knowledge and AI/ML, serving to AWS prospects 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, based mostly in Cape City. Vuyisa has a powerful concentrate on serving to prospects construct technical options to unravel enterprise issues. He has supported Capitec of their AWS journey since 2019.