Hey there! Let’s break down your two key questions for migrating that massive 1B-row PostgreSQL dataset to S3 using AWS Glue. These are common pain points when dealing with large-scale incremental or targeted data loads, so I’ll share practical, actionable solutions below.

Q1: Can I make AWS Glue load specific rows (e.g., post a certain date) since PostgreSQL lacks native bookmarks? #

Absolutely—you just need to implement custom bookmark-like logic, since Glue’s native bookmarks don’t work with PostgreSQL sources out of the box. Here’s how to do it reliably:

• Use a timestamp/auto-increment ID as a filter key: Pick a column that’s sequential (like created_at, updated_at, or a serial ID) to track which rows you’ve already migrated. This avoids reprocessing the entire dataset every time.
• Maintain a sync state store: Create a small tracking table (either in PostgreSQL itself, DynamoDB, or even a Glue Data Catalog table) to record the last processed value (e.g., the max created_at from your last run).
• Filter during data ingestion: In your Glue job, fetch the last sync value first, then use it to build a filtered SQL query when reading from PostgreSQL. For example:

  from awsglue.context import GlueContext
  from pyspark.context import SparkContext
  
  sc = SparkContext()
  glueContext = GlueContext(sc)
  
  # Fetch last sync date from your state store (example using a hardcoded value for demo)
  last_sync_date = "2024-01-01"
  
  # Read only rows after the last sync date
  postgres_dyf = glueContext.create_dynamic_frame.from_options(
      connection_type="postgresql",
      connection_options={
          "url": "jdbc:postgresql://your-rds-endpoint:5432/your-db",
          "dbtable": f"(SELECT * FROM your_table WHERE created_at > '{last_sync_date}') AS filtered_data",
          "user": "your-user",
          "password": "your-password"
      }
  )
  

• Batch large datasets: For 1B rows, don’t load everything in one go. Split the load by date ranges (e.g., 1 day at a time) to keep your Glue job’s memory usage manageable and avoid timeouts.

Q2: How to control S3 output object naming/path structure instead of using Glue’s auto-generated names? #

Glue does let you customize where and how your data is written to S3—you have a couple of flexible options depending on your needs:

Option 1: Partition your output by a logical key (e.g., date) #

Use partitionKeys in the write options to organize data into S3 prefixes based on a column (like the same created_at date you used for filtering). This makes it easy to query specific date ranges later and keeps your S3 structure clean:

# Write partitioned data to S3 in Parquet format (optimal for large datasets)
glueContext.write_dynamic_frame.from_options(
    frame=postgres_dyf,
    connection_type="s3",
    connection_options={
        "path": "s3://your-bucket/postgres-history/",
        "partitionKeys": ["date_created"]  # Replace with your date column (cast to date type first if needed)
    },
    format="parquet"
)

This will create paths like s3://your-bucket/postgres-history/date_created=2024-01-01/ with auto-generated part files inside—auto-named files are fine here because the partition prefixes give you the organization you need.

Option 2: Customize filenames (for smaller batches) #

If you need more control over individual filenames (e.g., for smaller daily batches), convert the DynamicFrame to a Spark DataFrame and use Spark’s write API. Note: For 1B rows, avoid merging into a single file (it’ll be too large!), but you can control the filename prefix:

# Convert DynamicFrame to DataFrame
postgres_df = postgres_dyf.toDF()

# Write with custom filename prefix (Spark will add suffixes like .part-00000.parquet)
postgres_df.write \
    .format("parquet") \
    .option("path", "s3://your-bucket/postgres-history/daily-batches/") \
    .option("header", "false") \
    .save()

If you must have a single named file for a small batch, use coalesce(1) before writing—but again, this isn’t recommended for large datasets due to performance and size limits.

Pro Tip: Combine with sync state #

Link your output path to the same date range you used for filtering. For example, if you’re processing 2024-01-01 data, write it directly to s3://your-bucket/postgres-history/2024-01-01/ to make it easy to track which data is where.

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