DMS Mongo-OpenSearch Error: Source column type 20 (nested field) not supported for target (DMS 3.5.3 - OS 2.17)

Question

¡Por supuesto! Aquí tienes el texto formateado en Markdown para que lo copies y pegues fácilmente en AWS re:Post:

Hello AWS Community,

I'm trying to migrate nested documents from MongoDB (Atlas) to Amazon OpenSearch Service using AWS DMS, but I'm encountering a persistent error during the task's load phase (TARGET_LOAD).

**Environment:**

* DMS Engine: 3.5.3
* Source: MongoDB (Atlas, *specify version if known*)
* Target: Amazon OpenSearch Service v2.17

**Problem:**

The DMS task fails (table state `Table error`) or loads incomplete documents to OpenSearch (only the `id` field, renamed from `_id`, appears in `_source`). CloudWatch logs (with DEBUG enabled for TARGET_LOAD) repeatedly show the following error for nested fields:

```
Source column type = 20 of column  not supported for given target
```

*(Example path from log: `listas.someCheckResult.data.someField`)*

This causes the table load to fail or prevents fields other than the renamed `_id` from being indexed, indicating DMS cannot map this internal data type (`20`) for nested fields when targeting OpenSearch 2.17.

**Configuration and Troubleshooting Steps Taken:**

1.  **Source Endpoint (MongoDB):**
    * `engine-name: mongodb`.
    * Settings include: `"NestingLevel": "ONE"` (to process 1st level fields).
    * Option "Treat MongoDB _id field as separate column" is **CHECKED**.
    * Connects to a specific Atlas shard hostname (DNS resolution verified from VPC).
2.  **Target Endpoint (OpenSearch):**
    * `engine-name: opensearch`.
    * OpenSearch domain v2.17, cluster health **Green**.
    * Endpoint Settings include: `UseNewMappingType=true` (to use `/_doc/id` API path) via `--elasticsearch-settings` parameter (as per documentation).
    * Authentication via IAM Role (`ServiceAccessRoleArn`).
    * `test-connection` for this endpoint: **Successful**.
    * Basic network/TLS connectivity confirmed via `curl` from an EC2 instance in the same subnet (gets expected 403).
3.  **IAM Role (for Target):**
    * Trust Policy allows `dms.amazonaws.com`.
    * Permissions Policy includes `es:Describe*` and `es:ESHttp*` (GET, POST, PUT, PATCH, DELETE) on the OS domain resources.
4.  **OpenSearch Configuration:**
    * Domain Access Policy explicitly allows the DMS IAM Role ARN principal.
    * Role Mapping (FGAC): The DMS IAM Role ARN is mapped to the `all_access` internal role in OpenSearch.
    * Index Mapping (`fichacliente`): Index was deleted and recreated dynamically by DMS; mapping only shows `id` property, but dynamic mapping is `true` (default). No conflicting index templates found (`GET /_index_template`). Cluster dynamic mapping setting is `true`.
5.  **DMS Task:**
    * `migration-type: full-load-and-cdc`.
    * Table Mapping: Includes `selection` (`include`) rules and a `transformation` (`rename-column`) rule to change `_id` to `id` (this successfully resolved a previous `mapper_parsing_exception`). No other transformations active.
6.  **DMS Replication Instance:** Engine v3.5.3. Instance was rebooted.

**Question:**

Despite setting `NestingLevel=ONE`, DMS still encounters this unsupported "Source column type 20" for nested fields when targeting OpenSearch 2.17. What is the correct way to configure DMS (endpoints or task) or what specific mapping/transformation rule is needed to properly handle these nested MongoDB fields/types and avoid this error? Is there a known limitation or specific handling required for DMS 3.5.3 and OS 2.17 with nested structures?

Thank you for your help!

rePost-User-8177986 · Answer

Hello,

The data-type limitations between MongoDB and OpenSearch when it comes to migrating string, array, REGEX, CODE, and Binary data which are converted to CLOB through DMS (not supported on OpenSearch).

Source column type = 20 of column  not supported for given target

The only workaround for this particular limitation, It would be to utilize a transformation rule to change the data-type of every column (%) of every table (%) to a string. Unfortunately, as we know this workaround comes with it's own problems as it will string-ify every piece of data that is being migrated between source and target which is of course not ideal.

As this appears to be combination of the way DMS handles MongoDB source data and which data-types OpenSearch supports, it is not evident at this moment in time if and/or when these limitations will be lifted.

Therefore, I kindly request you to raise a case with AWS Support team. This will enable our team to conduct a thorough investigation and efficiently identify the root cause of the problem.

Link to raise case: https://support.console.aws.amazon.com/support/home#/case/create

Thanks!

re:Post Agent · Answer

The issue you're experiencing with AWS DMS migrating nested documents from MongoDB to Amazon OpenSearch Service is likely due to the handling of nested fields in the migration process. Here are some potential solutions and considerations:

1. JSON Coercion: 
Instead of using NestingLevel, try using JSON coercion for nested fields. You can prefix the source column names with 'json_' to create nested JSON documents within the target document. This can be done manually or through transformation rules in your DMS task.

2. Transformation Rules:
Create transformation rules to handle the nested fields. For example, you could use a rule to rename the nested fields with the 'json_' prefix. This tells DMS to treat these fields as JSON objects rather than trying to flatten them.

3. Check OpenSearch Mapping:
Ensure that the mapping in your OpenSearch index can accommodate nested fields. You might need to explicitly define mappings for nested fields if dynamic mapping isn't handling them correctly.

4. DMS Version:
Consider upgrading to the latest version of DMS if possible. Newer versions may have improved support for handling complex data types and nested structures.

5. LOB Handling:
If any of your nested fields contain large objects, ensure that LOB columns are set to nullable in the target database. This is a requirement for DMS when dealing with LOB data.

6. OpenSearch Client Version:
The error message suggests there might be a version mismatch or compatibility issue between the DMS OpenSearch client and your OpenSearch Service version. Double-check that your DMS version is fully compatible with OpenSearch 2.17.

7. Column Mapping:
In your DMS task settings, you might need to explicitly map the nested fields to their corresponding OpenSearch data types. This can help DMS understand how to handle these fields during migration.

8. Task Logs:
Continue to monitor the detailed task logs. Look for any patterns in the fields that are causing issues and consider creating specific transformation rules for those fields.

If these steps don't resolve the issue, you may need to consider alternative approaches, such as using a custom ETL process or leveraging AWS Glue for more complex data transformations before loading into OpenSearch.

Remember to test any changes in a non-production environment first to ensure they don't cause unintended consequences in your data migration process.
      
**Sources**  
[Using Amazon DocumentDB as a target for AWS Database Migration Service - AWS Database Migration Service](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.DocumentDB.html)  
[AWS DMS Failing to Migrate Tables with LOBs and JSONB Columns | AWS re:Post](https://repost.aws/questions/QUhDpPCYsHTimr-EsoI7I4Cw/aws-dms-failing-to-migrate-tables-with-lobs-and-jsonb-columns)

DMS Mongo->OpenSearch Error: Source column type 20 (nested field) not supported for target (DMS 3.5.3 -> OS 2.17)

Relevant content