How can AWS SCT & DMS be used to migrate tables with SDO_GEOMETERY types to Aurora Postgres?

Question

I've successfully used the Schema Migration Tool from AWS's Database Migration Service to migrate dozens of tables from Oracle (19.20.0) to Aurora Postgres.

For most of the tables I used SMT to convert, I was able to subsequently populate with data in Aurora Postgres using Database Migration Tasks!

SCT does a great converting tables with sdo_geometry objects into postgis geometry types. For example:

CREATE TABLE GENERAL_SHAPES 
    ( 
        GENERAL_SHAPE_NAME    VARCHAR2(32) NOT NULL, 
        SHAPE_IDX             NUMBER NOT NULL, 
        GENERAL_SHAPE_CONTEXT VARCHAR2(32), 
        SHAPE PUBLIC.SDO_GEOMETRY NOT NULL, 
        CONSTRAINT GENERAL_SHAPES_PK PRIMARY KEY (GENERAL_SHAPE_NAME, SHAPE_IDX) 
    );

becomes:

CREATE TABLE public.general_shapes
    (
        general_shape_name character varying(32),
        shape_idx numeric,
        general_shape_context character varying(32),
        shape geometry NOT NULL,
        CONSTRAINT general_shapes_pk PRIMARY KEY (general_shape_name, shape_idx)
    );

That all looks right, and going from SDO_GEOMETRY to postgis `GEOMETRY` makes sense to me! Unfortunately, when I try use DMS Database Migration Tasks to populate tables from oracle sdo_geometry to postgres geometry, I get the following errors:

2023-10-12T21:16:45 [TASK_MANAGER ]I: Start loading table 'MYDB'.'GENERAL_SHAPES' (Id = 1) by subtask 1. Start load timestamp 0006078B74F00700 (replicationtask_util.c:755)

2023-10-12T21:16:46 [SOURCE_UNLOAD   ]I:  Oracle endpoint get table definition  {tableName:GENERAL_SHAPES, schemaName:MYDB, connectionId:750}  (oracle_endpoint_imp.c:1849)

2023-10-12T21:16:46 [SOURCE_UNLOAD   ]I:  Sent unloaded record 1 to internal queue  {operation:LOAD_START_OF_TABLE (31), tableName:GENERAL_SHAPES, schemaName:MYDB, connectionId:750}  (streamcomponent.c:2933)

2023-10-12T21:16:46 [TARGET_LOAD     ]I:  Table 'public'.'general_shapes' contains LOB columns, change working mode to default mode  (odbc_endpoint_imp.c:6074)

2023-10-12T21:16:46 [TARGET_LOAD     ]I:  Table 'public'.'general_shapes' has Non-Optimized Full LOB Support  (odbc_endpoint_imp.c:6087)

2023-10-12T21:16:47 [TARGET_LOAD     ]E:  Failed (retcode -1) to execute statement [1022502]  (ar_odbc_stmt.c:2868)

2023-10-12T21:16:47 [TARGET_LOAD     ]E:  RetCode: SQL_ERROR  SqlState: XX000 NativeError: 1 Message: ERROR: unexpected end of data (at offset 0); Error while executing the query [1022502]  (ar_odbc_stmt.c:2874)

2023-10-12T21:16:47 [TASK_MANAGER    ]W:  Table 'MYDB'.'GENERAL_SHAPES' was errored/suspended (subtask 1 thread 1). Failed (retcode -1) to execute statement; RetCode: SQL_ERROR  SqlState: XX000 NativeError: 1 Message: ERROR: unexpected end of data (at offset 0); Error while executing the query  (replicationtask.c:3023)

This happens for all tables with sdo_geometry type columns. If I use the task transformation rules to insert the table into a NEW/Non-existent table, It will create and populate a table, except that it will ignore/drops the shape column. So now it becomes:

CREATE TABLE "GENERAL_SHAPES" (
    "GENERAL_SHAPE_NAME" character varying(32),
    "SHAPE_IDX" numeric(38,10),
    "GENERAL_SHAPE_CONTEXT" character varying(32),
    CONSTRAINT "GENERAL_SHAPES_pkey" PRIMARY KEY ("GENERAL_SHAPE_NAME", "SHAPE_IDX")
);

I suspect the key might be to use SpatialDataOptionToGeoJsonFunctionName:

Use this attribute to convert SDO_GEOMETRY to GEOJSON format. By default, DMS calls the SDO2GEOJSON custom function if present and accessible. Or you can create your own custom function that mimics the operation of SDOGEOJSON and set SpatialDataOptionToGeoJsonFunctionName to call it instead.

And thats where I get stuck. I have no idea how to go about constructing a SDOGEOJSON function that can output something that will JustWork™ with an insert into the geometrey shape function! I can't find any documentation on what that function should look like, or input it should accept, what the output should be, or anything else. I can't find anything named SDOGEOJSON in all_objects, so if some toolset should be injecting that into the source Oracle DB, I don't have it!

Answer 1

For anyone who bumps into this later, it does actually work completely as expected. The sdo_geometry to postgis geometery is pretty slick and completely seamless.

The problems I was running into were partially operator error, partially unfamiliarity, and partially complications from Schema Conversion Tool. One thing that is critical is to enable FULL LOB support in the DSM task.

After sorting out a mountain of data issues, invalid triggers, and other unrelated errors, I was able to confirm that what appeared to be garbage data in the migrated columns, was in fact just my db client's inability to understand/render geometry columns.

The following SQL confirmed that my shapes WERE being problems converted.

select ST_AsText(shape) from general_shapes;