1Strategy

About 1Strategy

We will be modularly building the glue job following these steps:

Instantiate the Spark environment
Configure a connection to Snowflake
Parallelize reads using a partition column
Write to Amazon S3 We first start by importing some necessary libraries: After that we explicitly configure a couple of our Spark environment parameters: To connect to a database that is not supported natively by Glue (supported db list), we must make a compatible JDBC connector and other necessary jar files available for the Glue Job at runtime Unlike Glue Databrew and Glue Studio, we do not need to create a connector or connection ahead of time We will instead instantiate the connection inside the job

To connect to a Snowflake warehouse from Glue 2 0, two jar files are needed: A JDBC connector and a Spark connector I ended up using the snowflake-jdbc-3 2 4 connector, which can be downloaded from the Maven repo (complete list here), and the spark-snowflake_2 11-2 2 6 connector, which can be found here (complete list here)

After downloading the jar files, we need to upload them to an S3 bucket that the Glue Job can access This can be achieved by adding the S3 paths of both jar files to the “Dependent jars paths” separated by a comma like so: The assumption here is that you already have a secret created in AWS Secrets Manager and know how to retrieve the necessary values in the Glue Job The required values are: This is all great so far, but the problem is that we would only be leveraging one connection to the database and therefore only utilized one executor in the Glue Job After all, the whole appeal of using Glue is scalability without worrying about infrastructure

To utilize all the available executors for our job and read data in parallel, we need to setup a few options for our spark reader API:

partitionColumn is the name of a column used for partitioning
It must be of integer, date, or timestamp type
The best partition columns are ID, primary key, row number, or any column that is as evenly distributed as possible
lowerBound is the minimum value of the ‘partitionColumn’ within the query being executed
upperBound is the maximum value of the ‘partitionColumn’ within the query being executed
numPartitions is the maximum number of partitions that can be used for table parallelism read and consequently when writing to S3
This will also be the number of concurrent connections to the database
When setting this up, we ideally want one partition per executor
I use the number of DPUs as a guide with each having two executors
One executor will be subtracted to be the driver and the number of partitions ends up being: (number_of_DPUs*2) – 1 If you are running a 10 DPU job, you will end up with 19 partitions, connections to the database, and files written to S3
When setting this up, we ideally want one partition per executor
I use the number of DPUs as a guide with each having two executors
One executor will be subtracted to be the driver and the number of partitions ends up being: (number_of_DPUs*2) – 1
If you are running a 10 DPU job, you will end up with 19 partitions, connections to the database, and files written to S3

In the Customer table we are using in this example, we will be using c_CUSTKEY column, which is a unique key column We need to first get the column’s lowerBound and upperBound, then use those two values to execute the query in parallel

Because data was read in parallel, and unless we use spark transformation functions like coalesce or repartition, we will notice little intra-node communication in the Glue Job metrics page Assuming the partitionColumn is evenly distributed, the written files should be almost identical in size We will be using the Spark writer API and use mode overwrite, other options are ErrorIfExists, append, and ignore Read more about spark writing modes here

Putting it all together

Using this approach, you can pass all or some of the parameters used in the job from a step function and run as many of these jobs as you need

Glue Studio also offers a way to run queries against custom connections but as of the time of writing this blog post, it is not possible to run Glue Studio jobs from AWS Step Functions or pass the query dynamically I will, nonetheless, be covering the Glue Studio approach in a following blog post and compare the two approaches

If you feel the process is too complex and could use extra help, feel free to contact an AWS expert here at 1Strategy Reach out to us at info@1strategy com today, we would love to discuss how we can help you through this, and many more cloud-related journeys!