Scenario: External Database JDBC
Scenario: Migrating Data from PostgreSQL to Snowflake
The Challenge
A data engineering pipeline needs to pull millions of records from a legacy transactional PostgreSQL database, apply business transformations, and load the enriched dataset into a Snowflake cloud warehouse. The job requires third-party JDBC drivers and Snowflake connector classes that are not pre-packaged in the standard PySpark environment.
1. Optimal Spark-Submit Configuration
spark-submit \
--master yarn \
--deploy-mode cluster \
--name "Postgres-To-Snowflake-Migration" \
--num-executors 8 \
--executor-cores 4 \
--executor-memory 12g \
--driver-memory 6g \
--jars hdfs:///connectors/postgresql-42.5.1.jar,hdfs:///connectors/spark-snowflake_2.12-2.11.0-spark_3.3.jar,hdfs:///connectors/snowflake-jdbc-3.13.22.jar \
--conf spark.sql.shuffle.partitions=100 \
hdfs:///scripts/db_migration_job.py \
--postgres_url "jdbc:postgresql://postgres-db.internal:5432/orders" \
--snowflake_stage "my_snowflake_stage"
2. Parameter Explanations & Rationale
--jars ...: Essential for database connectors. We pass a comma-separated list of JDBC and third-party connector.jarfiles stored on HDFS.spark-submitautomatically distributes these files to the Spark Driver and all Spark Executor JVMs, making the database connector classes available to the classloader at runtime.- Moderate Executor Sizes: Pulling data from transactional databases (PostgreSQL) is constrained by the database's database connection pool limits and CPU. Running too many concurrent executors or tasks will overwhelm PostgreSQL, resulting in connection timeouts. Sizing 8 executors with 4 cores (32 tasks total) provides a safe, parallel read rate.
spark.sql.shuffle.partitions=100: Set lower than the 200 default to match the moderate scale of database ingestion pipelines.
Find this content helpful?
☕ Buy me a coffee