Data Migration from CSV (S3) to DynamoDB using AWS DMS

Question

I am facing an issue when I am migrating a table consisting a JSON in a column which should be inserted in as a DyanmoDB Map. The source data is in the following format

<table border="1"><tr><th>userId</th><th>brandId</th><th>createdBy</th><th>modifiedBy</th><th>preferences</th><th>version</th></tr><tr><td>TU0001</td><td>TEST BRAND</td><td>{"channel":{"S":"website"},"date":{"S": "2022-06-13T08:16:26.300Z"},"userId":{"S":"TU0001"}}</td><td>{"channel":{"S":"website"},"date":{"S": "2022-06-13T015:26:10.200Z"},"userId":{"S":"TU0001"}}</td><td>{"Colour": {"S": "Red" },"Size":{"S": "XL" }}</td><td>1</td></tr></table>

The table in DynamoDB is in the sam structure except that the JSON values are stored as MAP (dynamodb-map). When we migrate using DMS, it inserts the JSON as string value and not MAP as expected.

I have defined the transformation rule as follows:

"attribute-mappings": [
      {
        "target-attribute-name": "userId",
        "attribute-type": "scalar",
        "attribute-sub-type": "string",
        "value": "${userId}"
      },
      {
        "target-attribute-name": "brandId",
        "attribute-type": "scalar",
        "attribute-sub-type": "string",
        "value": "${brandId}"
      },
      {
        "target-attribute-name": "createdBy",
        "attribute-type": "document",
        "attribute-sub-type": "dynamodb-map",
        "value": {
          "M": {
            "S": "${createdBy}"
          }
        }
      },
      {
        "target-attribute-name": "modifiedBy",
        "attribute-type": "document",
        "attribute-sub-type": "dynamodb-map",
        "value": {
          "M": {
            "S": "${modifiedBy}"
          }
        }
      },
      {
        "target-attribute-name": "preferences",
        "attribute-type": "document",
        "attribute-sub-type": "dynamodb-map",
        "value": {
          "M": {
            "S": "${preferences}"
          }
        }
      },
      {
        "target-attribute-name": "version",
        "attribute-type": "scalar",
        "attribute-sub-type": "number",
        "value": "${version}"
      }
    ]

I also tried adding the map as below, and it adds an empty map in DyanmoDB.

"value": {
          "M": "${preferences}"
        }

Hope someone can help.

score 0 · Answer 1 · answered May 21 '23 at 02:47

I think DMS does not support direct conversion of JSON strings to Dynamo maps. Here DMS inserting JSON data as strings instead of converting into DynamoDB Map or List type. But you can process the data before or after migration to make sure that JSON data is correctly parsed into DyanamoDB Map.

Before Migration:

Process data in source db to convert JSON strings to a format that DMS can handle.
Write a script (JS/Python) to read data from your source, transform JSON into DynamoDB map and then write to DynamoDB.

OR

After Migration:

Read the data from DynamoDB, and then parse JSON string into Map, and then write back to DynamoDB.
Write a script which itereate over every time in the DynamoDB table, retrieve the string data, convert it into a Map, and then update item in the DynamoDB.

Below is an Node.js with AWS SDK example of 'Post Processing' that can be done after migration, you can do:

const AWS = require('aws-sdk');

AWS.config.update({
  region: "your_region",
  // Add your credentials
});

const dynamodb = new AWS.DynamoDB.DocumentClient();
const tableName = 'your_table'; // replace with your table name

const scanAndProcessItems = async (startKey) => {
  try {
    const params = {
      TableName: tableName,
      ExclusiveStartKey: startKey
    };
    
    const data = await dynamodb.scan(params).promise();
    
    if (data.Items) {
      for (let item of data.Items) {
        // To parse JSON strings into DynamoDB Maps
        item.createdBy = JSON.parse(item.createdBy);
        item.modifiedBy = JSON.parse(item.modifiedBy);
        item.preferences = JSON.parse(item.preferences);
    
        // To write the item back to the table
        await dynamodb.put({ TableName: tableName, Item: item }).promise();
      }
    }
    
    // If there's more data to scan, it will recursively call this function
    if (data.LastEvaluatedKey) {
      await scanAndProcessItems(data.LastEvaluatedKey);
    }
    
  } catch (err) {
    console.error("An error occurred", err);
  }
};

scanAndProcessItems();

Hope this will help you.

Data Migration from CSV (S3) to DynamoDB using AWS DMS

1 Answers1