AWS DMS Postgres to OpenSearch LOB handling

Question

Source: postgres
Target: OpenSearch

I have a `text` column called `description` in one of my postgres tables. Per the documentation, this data type is mapped to a `NCLOB`.  Since OpenSearch does not not offer LOB support, my `description` is missing in my OpenSearch documents.

I tried using the mapping rule bellow, but does not seem to be doing anything
```
{
    "rule-type": "transformation",
    "rule-id": "3",
    "rule-name": "3",
    "rule-target": "column",
    "object-locator": {
        "schema-name": "public",
        "table-name": "jobs",
        "column-name": "description"
    },
    "rule-action": "change-data-type",
    "data-type": {
        "type": "string",
        "length": 500
    }
}
```

When i check the logs i see the following
```
Column 'description' is unsupported in table def 'public.jobs' since the LOB support is disabled 
```
However, i do have LOB enabled under task settings:
```
"TargetMetadata": {
        "ParallelApplyBufferSize": 0,
        "ParallelApplyQueuesPerThread": 0,
        "ParallelApplyThreads": 0,
        "TargetSchema": "",
        "InlineLobMaxSize": 0,
        "ParallelLoadQueuesPerThread": 0,
        "SupportLobs": true,
        "LobChunkSize": 10,
        "TaskRecoveryTableEnabled": false,
        "ParallelLoadThreads": 0,
        "BatchApplyEnabled": false,
        "FullLobMode": true,
        "LimitedSizeLobMode": false,
        "LoadMaxFileSize": 0,
        "ParallelLoadBufferSize": 0
    },
```

Is that transformation rule supposed to work? Or will any LOB column be skipped because OpenSearch does not have LOB support? 
Any way to make this work?

Thanks!

Aritrabi · Answer

Transformation rules like - "rule-action": "change-data-type",  work on DMS data types. "type": "string" , "length": 500 will be the DMS data type here which will be converted to the target engine data type. Since DMS does not support string as a data type for Opensearch target, this attribute is not getting converted. 
Please refer : https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.Elasticsearch.html#CHAP_Target.Elasticsearch.DataTypes
AWS DMS data type - Boolean,Date,Time, Timestamp,INT4,Real4,UINT4 only supported DMS data types.  No string data type allowed for Opensearch as a target

sokoow · Answer

If anyone stumbled upon this:

we ended up changing TEXT to VARCHAR type on all columns that were affected in our postgres. According to this: https://stackoverflow.com/questions/4848964/difference-between-text-and-varchar-character-varying  : "There is no difference, under the hood it's all varlena (variable length array)."

This is the only way so far (12/2024) to make indexing work on these fields.

xoapit · Answer

Do you have any solutions?

AWS DMS Postgres to OpenSearch LOB handling

Add your answer

Relevant content