Working with Spark Redshift Connector: Made Easy 101

In this day and age, data has become an invaluable tool in the business and corporate world. It has become so prominent that numerous fields related to data emerged. Some of these fields include Data Science and Data Analysis. They rely on data and help draw conclusions that would help companies. With this in mind, these individuals need storage systems to house this information. This brings us to Data Warehousing tools, and more specifically, AWS Redshift. This is one of the most prominent Data Warehousing tools on the market today. Accordingly, it can handle data on the exabytes scale. While it has prominent features, it can be connected to other platforms for additional functionality such as Apache Spark SQL using the Spark Redshift Connector.

This article gives you a brief but sufficient introduction to working with the Spark Redshift Connector. By the end, you should have a rough idea of what this two software are and the benefits you stand to gain by using them together. Have a read below to learn how to set up Spark Redshift Connector.

Introduction to Amazon Redshift

Amazon Redshift, AWS Redshift for short, is a popular data warehousing solution capable of handling data on an exabytes scale. It has become one of the most popular leaders in the Data Warehouse category due to the numerous benefits it offers. Here’s a post that talks about the best practices for AWS Redshift for 2023.

Amazon Redshift leverages Massively Parallel Processing (MPP) technology, which allows it to perform complex operations on large data volumes at fast speed. It can work with data on the exabytes scale usually denoted by 10¹⁸. That’s pretty impressive!

Data stored in Amazon Redshift is encrypted which provides an extra layer of security for users. It offers various features to enable users to import data easily with just few clicks.

Introduction to Apache Spark 

Apache Spark is an open-source, distributed processing system used for large data workloads. Numerous companies have embraced this software due to its numerous benefits such as speed. The platform utilizes RAM for data processing, making it much faster than disk drives. 

Now that you have a rough idea of Apache Spark, what does it entail? One of the most widely used components is Apache Spark SQL. Simply put, this is Apache Spark’s SQL wing, meaning it brings native support for SQL to the platform. Moreover, it streamlines the query process of data stored in RDDs and external sources. Other components include Spark Core, Spark Streaming, GraphX, and MLib. 

Why should you work with Spark Redshift Connector?

• Move Data Easily: The Spark Redshift Connector helps you transfer data smoothly between Amazon Redshift and Spark, making it easier to manage your data in both places.
• Best of Both Worlds: You get to use Spark’s super-fast data processing along with Redshift’s powerful querying abilities, meaning you can handle big data tasks and analyze it efficiently.
• Speed Up Workflows: Spark’s ability to handle large data loads in parallel allows you to process information quickly, while Redshift handles complex queries.
• Build Efficient Pipelines: The connector helps set up scalable data pipelines, improving your overall data movement between the two tools.

For installation and setup, you can check out detailed instructions on GitHub.

Steps to Set Up Spark Redshift Connector

Now, let’s get to the actual process of loading data from Redshift to Spark and vice versa. Before using the mentioned library, we need to perform a few simple tasks. Follow the steps below:

• Step 1: Add JAR File for Spark Redshift Connector
• Step 2: Add Packages for Spark Redshift Connector
• Step 3: Read & Write Data using Spark Redshift Connector

Step 1: Add JAR File for Spark Redshift Connector

You need to add the Jar file to the spark-submit command. Enter the following command:

--jars https://s3.amazonaws.com/redshift-downloads/drivers/jdbc/1.2.36.1060/RedshiftJDBC42-no-awssdk-1.2.36.1060.jar

Keep in mind that the path should be of the same location as the Redshift Jar file. 

Step 2: Add Packages for Spark Redshift Connector

Next up, you need to add the package names in the spark-submit command as illustrated below: 

--packages org.apache.spark:spark-avro_2.11:2.4.2,io.github.spark-redshift-community:spark-redshift_2.11:4.0.1

Step 3: Read & Write Data using Spark Redshift Connector

After finishing up with the outlined steps, you can now use the Spark Redshift Connector. The pyspark code below is used to read and write data:

# Create or get a Spark Session with the name spark
spark = SparkSession 
    .builder() 
    .appName("sparkRedshiftExample") 
    .getOrCreate()
# Read data from a table
df_read_1 = spark.read 
    .format("io.github.spark_redshift_community.spark.redshift") 
    .option("url", "jdbc:redshift://redshifthost:5439/database?user=username&password=pass") 
    .option("dbtable", "table_name") 
    .option("tempdir", "s3n://path/for/temp/data") 
    .load()
# Read data from a query
df_read_2 = spark.read 
    .format("io.github.spark_redshift_community.spark.redshift") 
    .option("url", "jdbc:redshift://redshifthost:5439/database?user=username&password=pass") 
    .option("query", "select x, count(*) table_name group by x") 
    .option("tempdir", "s3n://path/for/temp/data") 
    .load()
# Write back to a table
df_read_1.write 
  .format("io.github.spark_redshift_community.spark.redshift") 
  .option("url", "jdbc:redshift://redshifthost:5439/database?user=username&password=pass") 
  .option("dbtable", "table_name") 
  .option("tempdir", "s3n://path/for/temp/data") 
  .mode("error") 
  .save()
# Using IAM Role based authentication
df_read_2.write 
  .format("io.github.spark_redshift_community.spark.redshift") 
  .option("url", "jdbc:redshift://redshifthost:5439/database?user=username&password=pass") 
  .option("dbtable", "table_name") 
  .option("tempdir", "s3n://path/for/temp/data") 
  .option("aws_iam_role", "your-role-here") 
  .mode("error") 
  .save()

Upon analyzing the code, you will realize that it follows a stepwise procedure. The first step is to create the Spark Session which is the entry point for the spark application. The driver uses the spark session to coordinate resources provided by the resource manager. 

After you create the Spark session inside the code, the read function is what you use to read data from Redshift Tables and assign it to the dataframe called df_read_1. Upon a closer look at the table, you will realize that instead of reading the entire table, you are reading the result of a query. Next up, the write function in the code is used to write data from the dataframe to the Redshift database. Note that the code specified the mode option, which is the mode used to write data on Redshift. 

In summary, the Pyspark code has two major components: the read section used to read data from the dataframe and the write option used to write data to Redshift.

Conclusion

This post taught you what AWS Redshift and Spark are and how and why you should use them together. You can also learn about the differences between Apache Spark and Redshift. Using the Spark Redshift Connector, you can load data from Redshift to Spark and write them back to Redshift. 

However, streaming data from various sources to Amazon Redshift can be quite challenging and cumbersome. If you are facing these challenges and are looking for some solutions, then check out a simpler alternative like Hevo.

Give Hevo a try 14-day free trial today. Hevo offers plans & pricing for different use cases and business needs; check them out!

FAQ