AWS DMS Postgres to OpenSearch LOB handling
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!
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
Yeah, it's confusing how DMS handles varchar even it is not mentioned in the documentation as supported source data type! I'm stuck in the same problem, did you find any solution?
Relevant questions
does AWS DMS support partition tables from postgres
asked 5 months agoAWS DMS doing a SELECT on TEXT column during continuous replication
asked 2 months agoRedshift varchar(max) not enough to store json data type column from Postgres
asked 2 months agoDMS to OpenSearch decimal problem
asked a month agoDMS service for aurora postgres doesnt accept a view as source for full load process
asked 2 months agoHow to import Postgres data that has JSON columns?
asked 2 years agoAWS DMS Postgres to OpenSearch LOB handling
asked 4 months agoETL from an Oracle database into RDS Postgres too slow
Accepted Answerasked 2 years agoPhantom Update on Aurora Postgres from AWS DMS Replication from SQL Server Source
asked 2 months agoAWS DMS + OpenSearch + Index templates
asked 4 months ago
How does it handle varchars? in the same table i have a
name
column which is avarchar(255)
and that get's mapped to astring
type in OpenSearch. Is there no way to migrate atext
type from postgres to OpenSearch?