Spark 2.2.x在Amazon EMR 5.12.0中JDBC写入数据库故障排查

Hey there, sorry to hear you're stuck with this Spark PostgreSQL write issue after upgrading EMR versions. Let’s walk through some targeted troubleshooting steps that often resolve these cross-version compatibility hiccups:

1. Verify JDBC Driver Compatibility #

EMR 5.12.0 ships with Spark 2.2.0 (compared to 2.0.0 in EMR 5.2.1), which can introduce compatibility gaps with older PostgreSQL JDBC drivers.

• Check the driver version: If you’re relying on EMR’s default driver, it might have been updated. For example, EMR 5.12.0 uses a newer postgresql-42.x.x driver, while 5.2.1 used postgresql-9.x.x. Mismatches here can cause silent failures or connection errors.
• Fix: Explicitly include a driver version that matches both your PostgreSQL server version and Spark 2.2.0. Add it via your Spark submit command:

  spark-submit --jars s3://your-bucket/postgresql-42.2.5.jar your-job.py
  

  (Pick a version compatible with your PostgreSQL server—e.g., 42.2.x works for PostgreSQL 9.6+.)

2. Audit Spark Write Mode & Schema Alignment #

Spark 2.2.0 tightened up JDBC write behavior, especially around schema matching and write modes:

• Write mode checks: If using overwrite, ensure the Spark service account has DROP and CREATE permissions on the target PostgreSQL table. If using append, verify there are no duplicate key constraints or data type mismatches that would cause row-level failures.
• Schema validation: Run df.printSchema() in your job and compare it directly to your PostgreSQL table schema. Look for mismatches like:
  • Spark’s timestamp vs PostgreSQL’s date
  • Spark’s integer vs PostgreSQL’s bigint
  • Missing nullable flags (Spark might default to non-null, while your table allows nulls)

3. Check EMR Spark Configuration Differences #

EMR 5.12.0 changed some default Spark settings that can impact JDBC writes:

• Confirm these configs in your cluster’s spark-defaults.conf:
  • spark.driver.extraClassPath and spark.executor.extraClassPath: Ensure the PostgreSQL JDBC driver path is included here if you’re not using --jars.
  • spark.sql.jdbc.batchSize: Newer Spark versions default to larger batch sizes, which can hit PostgreSQL’s connection limits or transaction timeouts. Try reducing it to 1000 or lower.
  • spark.sql.catalogImplementation: Stick to the default hive unless you’re using a custom catalog—non-default settings can interfere with JDBC write logic.

4. Dig Into PostgreSQL Server Logs #

Don’t overlook the PostgreSQL side! Check your server’s pg_log directory for errors when the write attempt fails. Common issues here include:

• Connection refusals (due to security group rules or pg_hba.conf restrictions)
• Constraint violations (unique keys, foreign key references)
• Insufficient disk space or memory on the PostgreSQL server

5. Test a Minimal Reproducible Job #

Strip your job down to the simplest possible write to isolate the problem. Use this PySpark snippet as a starting point:

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("PostgresTest").getOrCreate()

# Create a tiny test DataFrame
test_data = [("sample1", 100), ("sample2", 200)]
df = spark.createDataFrame(test_data, ["label", "count"])

# Attempt write to PostgreSQL
df.write.format("jdbc") \
  .option("url", "jdbc:postgresql://your-db-host:5432/your-db-name") \
  .option("dbtable", "public.test_write_table") \
  .option("user", "your-db-user") \
  .option("password", "your-db-password") \
  .mode("overwrite") \
  .save()

If this works, gradually reintroduce parts of your original job to pinpoint where the failure occurs.

If none of these steps resolve the issue, sharing specific error logs (from Spark driver logs or PostgreSQL logs), your exact Spark write code, and versions of PostgreSQL/Spark would help narrow things down further.

内容的提问来源于stack exchange，提问作者Evan Zamir