Jdbc Performance

• Performance Issues — Slow queries, job timeouts
• Incremental Loading Issues — Watermark not advancing, duplicates
• IAM and Permissions Errors — S3 access denied, Glue catalog access
• Monitoring and Alerting — CloudWatch alarms, key metrics
• Troubleshooting Checklist — Systematic diagnosis steps

Performance Issues

Slow Query Execution

Symptom:

• Job runs for hours
• CloudWatch logs show: “Executing query…” but no progress

Root causes:

1. Missing indexes - Source query does full table scan
2. Too much data - Loading entire table instead of incremental
3. Network bandwidth - Limited throughput between database and Glue
4. Source database load - Database under heavy load

Troubleshooting:

1. Check query execution plan in source database:

   sqlCopy

   -- Oracle
   EXPLAIN PLAN FOR
   SELECT * FROM large_table WHERE updated_at > '2024-01-01';
   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
   
   -- SQL Server
   SET SHOWPLAN_TEXT ON;
   SELECT * FROM large_table WHERE updated_at > '2024-01-01';
   
   -- PostgreSQL
   EXPLAIN ANALYZE
   SELECT * FROM large_table WHERE updated_at > '2024-01-01';
   
   -- MySQL
   EXPLAIN
   SELECT * FROM large_table WHERE updated_at > '2024-01-01';

2. Monitor source database load:

   • Check CPU, memory, I/O utilization
   • Review slow query logs
   • Identify concurrent queries

3. Measure network throughput:

   • Check data transfer rate in CloudWatch metrics
   • Look for bandwidth bottlenecks

Solutions:

1. Add index on watermark column:

   sqlCopy

   -- Oracle
   CREATE INDEX idx_updated_at ON large_table(updated_at);
   
   -- SQL Server
   CREATE INDEX idx_updated_at ON large_table(updated_at);
   
   -- PostgreSQL
   CREATE INDEX idx_updated_at ON large_table(updated_at);
   
   -- MySQL
   CREATE INDEX idx_updated_at ON large_table(updated_at);

2. Use parallel reads:

   pythonCopy

   source_df = spark.read.format("jdbc").options(
       url=jdbc_url,
       dbtable="large_table",
       numPartitions=10,  # Read in parallel
       partitionColumn="id",
       lowerBound=1,
       upperBound=10000000
   ).load()

3. Reduce batch size:

   pythonCopy

   # Load 1 day at a time instead of full month
   WHERE updated_at >= '2024-01-01' AND updated_at < '2024-01-02'

4. Increase Glue workers:

   pythonCopy

   'NumberOfWorkers': 20,  # Up from 5
   'WorkerType': 'G.2X'    # Larger workers

Job Timeout

Symptom:

Root causes:

1. Timeout too short - Data volume requires more time
2. Performance issues - See “Slow Query Execution” above

Solution:

Increase job timeout:

Incremental Loading Issues

Watermark Not Advancing

Symptom:

• Job runs successfully but loads 0 records every time
• Watermark file contains same value after each run

Root causes:

1. No new data in source - Actually no changes
2. Timezone mismatch - Source uses local time, watermark uses UTC
3. Watermark filter logic incorrect - Using >= instead of >

Troubleshooting:

1. Check source for new data:

   sqlCopy

   SELECT COUNT(*) FROM table WHERE updated_at > '<last-watermark>';

2. Check timezone:

   pythonCopy

   print(f"Last watermark: {last_watermark}")
   print(f"Last watermark timezone: {last_watermark_tz}")
   
   # Convert to UTC
   from datetime import datetime
   import pytz
   
   utc_watermark = pytz.timezone('America/New_York').localize(
       datetime.strptime(last_watermark, '%Y-%m-%d %H:%M:%S')
   ).astimezone(pytz.utc)

3. Check filter logic:

   pythonCopy

   # Correct: > (strictly greater than)
   filtered_df = source_df.filter(f"{watermark_column} > '{last_watermark}'")
   
   # Incorrect: >= (will reload last batch every time)
   # filtered_df = source_df.filter(f"{watermark_column} >= '{last_watermark}'")

Solution:

Normalize all timestamps to UTC:

Duplicate Records

Symptom:

• Target table contains duplicate records (same primary key multiple times)

Root causes:

1. Using append instead of upsert - For mutable data
2. Job retry - Job failed mid-run, reran from same watermark
3. Late-arriving data - Records arrive after their event timestamp

Solution:

1. Use upsert for mutable data:

   pythonCopy

   # MERGE INTO instead of append
   spark.sql(f"""
   MERGE INTO {target_table} AS target
   USING source_view AS source
   ON target.customer_id = source.customer_id
   WHEN MATCHED THEN UPDATE SET *
   WHEN NOT MATCHED THEN INSERT *
   """)

2. Add deduplication logic:

   pythonCopy

   from pyspark.sql.window import Window
   from pyspark.sql.functions import row_number
   
   window = Window.partitionBy("customer_id").orderBy(col("updated_at").desc())
   deduplicated_df = source_df.withColumn("row_num", row_number().over(window)) \
       .filter(col("row_num") == 1) \
       .drop("row_num")

3. Handle late arrivals with buffer:

   pythonCopy

   # Load from 1 day before watermark
   buffer_watermark = last_watermark - timedelta(days=1)
   filtered_df = source_df.filter(f"{watermark_column} > '{buffer_watermark}'")
   
   # Then upsert to avoid duplicates

IAM and Permissions Errors

S3 Access Denied

Symptom:

Root cause: Glue job IAM role lacks S3 permissions

Solution:

Add S3 permissions to Glue role:

Glue Data Catalog Access Denied

Symptom:

Root cause: Glue job role lacks Glue Data Catalog permissions

Solution:

Add Glue permissions:

Monitoring and Alerting

Set Up CloudWatch Alarms

Job failure alarm:

Long-running job alarm:

Key Metrics to Track

• glue.driver.aggregate.recordsRead - Records read from source
• glue.driver.aggregate.bytesRead - Bytes read from source
• glue.driver.aggregate.elapsedTime - Job duration
• glue.driver.aggregate.numCompletedTasks - Tasks completed
• Job run state (SUCCEEDED, FAILED, TIMEOUT)

Troubleshooting Checklist

When a job fails, follow this systematic approach:

1. Check Job Run Status

2. Review CloudWatch Logs

Look for:

• ERROR messages
• Exception stack traces
• Last successful log message before failure

3. Test Connection

4. Verify Permissions

5. Validate Source Data

6. Check Watermark

Summary

Error resolution workflow:

1. Identify error category - Connection, schema, performance, incremental, or permissions
2. Check CloudWatch logs - Read error messages and stack traces
3. Test connectivity - Verify network, security groups, credentials
4. Validate source data - Query source database directly
5. Review job configuration - Check worker count, timeout, arguments
6. Monitor metrics - Set up CloudWatch alarms for proactive detection
7. Document resolution - Keep runbook of common issues and fixes

With systematic troubleshooting and proper monitoring, external data import pipelines run reliably with minimal intervention.