使用AWS DMS迁移表时，如何实现加载数据时修剪源表列并同步至目标端？

Got it, this is a common pain point since AWS DMS doesn’t include a built-in trim transformation rule out of the box. But don’t worry—there are several reliable ways to implement this, depending on your source/target environment and migration requirements. Let’s break down the most practical options:

1. Use AWS DMS Custom Transformations with Lambda (Recommended for Flexible, End-to-End Control) #

This is the most versatile approach, especially if you need to handle trimming alongside other data transformations. Here’s how to set it up:

• First, create an AWS Lambda function that takes a DMS record, trims the target columns, and returns the modified record. For example, a Python function might look like this:

  import json
  
  def lambda_handler(event, context):
      # Customize this list to match your columns that need trimming
      columns_to_trim = ["first_name", "last_name", "address"]
      
      processed_records = []
      for record in event["records"]:
          # Parse the base64-encoded payload
          payload = json.loads(record["data"])
          
          # Trim each target column if it's a string
          for col in columns_to_trim:
              if col in payload and isinstance(payload[col], str):
                  payload[col] = payload[col].strip()
          
          # Convert back to base64 for DMS processing
          processed_record = {
              "recordId": record["recordId"],
              "result": "Ok",
              "data": json.dumps(payload).encode("base64").replace("\n", "")
          }
          processed_records.append(processed_record)
      
      return {"records": processed_records}
  

• Next, grant your DMS replication instance permission to invoke this Lambda function (add a policy to the DMS IAM role that allows lambda:InvokeFunction on your function’s ARN).
• Finally, in your DMS task’s Table mapping, add a custom transformation rule that routes records through this Lambda function. You can apply it to specific tables or all tables in the migration.

2. Pre-Process Data at the Source (Simple for Static Schemas) #

If your source is a relational database, you can create a view that automatically trims the required columns, then have DMS migrate the view instead of the original table. This avoids adding extra components like Lambda.

For example, in MySQL:

CREATE VIEW trimmed_customer_data AS
SELECT 
    customer_id,
    TRIM(first_name) AS first_name,
    TRIM(last_name) AS last_name,
    TRIM(email) AS email,
    signup_date
FROM customers;

Then configure your DMS source endpoint to use this view as the source table. Just remember to update the view if the source table schema changes (like adding a new column that needs trimming).

3. Post-Process Data at the Target with Database Triggers (Good for Target-Side Control) #

If you prefer handling the trimming on the target end, you can create database triggers that automatically trim columns when data is inserted or updated. This works for both full-load and CDC (change data capture) migrations.

Here’s an example for PostgreSQL:

-- Create a trigger function to trim specified columns
CREATE OR REPLACE FUNCTION trim_target_columns()
RETURNS TRIGGER AS $$
BEGIN
    -- Adjust these columns to match your target table's needs
    NEW.first_name := TRIM(NEW.first_name);
    NEW.last_name := TRIM(NEW.last_name);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Attach the trigger to run before INSERT/UPDATE operations
CREATE TRIGGER trim_before_write
BEFORE INSERT OR UPDATE ON target_customers
FOR EACH ROW EXECUTE FUNCTION trim_target_columns();

⚠️ Note: Be mindful of performance overhead if you’re migrating large datasets or have high CDC throughput. Also, ensure the trigger doesn’t cause unintended side effects (like trimming data that shouldn’t be modified).

4. Use DMS Filter Rules with SQL Expressions (Limited to Supported Sources) #

Some source databases (like Oracle, SQL Server) let you use SQL functions directly in DMS’s selection rules. You can define a rule that selects the trimmed version of columns instead of the raw values.

For example, in your DMS task’s Table mapping, add a selection rule like this (for Oracle):

{
  "rules": [
    {
      "rule-type": "selection",
      "rule-id": "1",
      "rule-name": "1",
      "object-locator": {
        "schema-name": "SOURCE_SCHEMA",
        "table-name": "CUSTOMERS"
      },
      "rule-action": "include",
      "column-inclusion-rule": "TRIM(FIRST_NAME) AS FIRST_NAME, TRIM(LAST_NAME) AS LAST_NAME, CUSTOMER_ID, SIGNUP_DATE"
    }
  ]
}

Check the AWS DMS documentation for your specific source database to confirm if this syntax is supported.

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