Working with Oracle API for MongoDB, External Tables and Views

One of the best thing that Oracle Database is capable is to run various workload type using a single database “engine”, here we call it a Container Database, with every new release, Oracle adds more and more compatibility functions and the Oracle Database API for MongoDB is a combination of Oracle DB features plus Oracle REST Data Services(ORDS), in this article I will show it running in a ATP-S database but you can deploy ORDS and use it in your DB.

As usual, this is not an official documentation!
Always refer to Oracle Official documentation and support.

Enabling

To enable the API in the ATP-S you need to choose the “Secure access from allowed IPs and VCNs only” or the Private endpoint access only, this access mode increase your security because you minimize the allowed connection source :

After choosing the right network option, go to Tool Configuration tab and enable it under Edit tool Configuration menu:

To keep it simple I will use the ADMIN user but in the real world, after you enable the MongoDB API you must create a dedicated user, follow this procedure, the user needs some specific permissions to work.

Connecting

The connection string follows the MongoDB pattern and you can get it from Tool Configuration page:

If you are using MongoDB Compass, you need to:

Remove the [USER:PASSWORD@] and let only user@ and after the port(27017) change [user] to your user too
Authentication > LDAP (put your user and password here)
TLS > SSL/TLS Connection: ON
TLS > tlsAllowInvalidCertificates

Now that you are in the Mongo Compass(or other tool) you can create and interact with your collections:

Objects

Now that we have the basics, our goal is to create some custom objects that are accessible from Oracle MongoDB API so we need to follow some patterns, first I will create my “local” table, you can create a collection from your preferred MongoDB client and get the Oracle Table DDL, it should be something like this:

CREATE TABLE ADMIN.LOCAL_TABLE 
    ( 
     ID            VARCHAR2 (255) , 
     CREATED_ON    TIMESTAMP DEFAULT sys_extract_utc(SYSTIMESTAMP) , 
     LAST_MODIFIED TIMESTAMP DEFAULT sys_extract_utc(SYSTIMESTAMP) , 
     VERSION       VARCHAR2 (255) , 
     JSON_DOCUMENT BLOB ,
      CHECK ("JSON_DOCUMENT" is json format oson (size limit 32m)) ENABLE, 
	 PRIMARY KEY ("ID")
    ) 
    TABLESPACE DATA 
    LOGGING 
;

Now we need to create our SODA Collection, you must use the DBMS_SODA.CREATE_COLLECTION to it, here is a simple example:

DECLARE
   L_RETURN_VALUE SYS.SODA_COLLECTION_T;
   L_COLLECTION_NAME NVARCHAR2(200) := 'LOCAL_TABLE';
   L_METADATA VARCHAR2(2000) := '{
   "schemaName":"ADMIN",
   "tableName":"LOCAL_TABLE",
   "keyColumn":{
      "name":"ID",
      "sqlType":"VARCHAR2",
      "maxLength":255,
      "assignmentMethod":"EMBEDDED_OID",
      "path":"_id"
   },
   "contentColumn":{
      "name":"JSON_DOCUMENT",
      "sqlType":"BLOB",
      "jsonFormat":"OSON"
   },
   "lastModifiedColumn":{
      "name":"LAST_MODIFIED"
   },
   "versionColumn":{
      "name":"VERSION",
      "method":"UUID"
   },
   "creationTimeColumn":{
      "name":"CREATED_ON"
   },
   "readOnly":false
}';
   L_CREATE_MODE BINARY_INTEGER := 1;
BEGIN
   L_RETURN_VALUE := SYS.DBMS_SODA.CREATE_COLLECTION(
      COLLECTION_NAME => L_COLLECTION_NAME,
      METADATA => L_METADATA,
      CREATE_MODE => DBMS_SODA.CREATE_MODE_MAP
   );
END;
/

As you can see, we are specifying our table structure in the METADATA parameter, the important parameter here is the “readOnly”:false, if you don’t specify it your object will be Read-Only, and you will get an error.

The CREATE_MODE is defined to CREATE_MODE_MAP , and according to the documentation:

DBMS_SODA.CREATE_MODE_MAP. Tries to map an existing table to the collection. Minimal checking is performed to ensure the table shape matches supplied collection metadata (if not, then an error is returned).

I got this schema from the USER_SODA_COLLECTIONS in the JSON_DESCRIPTION column .

Now you collection should be showing:

And you can interact with it:

And from SQL:

The External Table

The ideia here is to have some data in the ATP-S storage and part in Object Storage, the first idea was to create an Hybrid External table but our “json” object is stored in LOB column and it is not supported, so we need to do an workaround.

I first ran an import from a sample json file:

I can use SQL query to count how many languages I have:

We will use the DBMS_CLOUD.EXPORT_DATA to copy existing data from our table to an object storage bucket:

BEGIN
 DBMS_CLOUD.EXPORT_DATA(
  credential_name =>'obj_store_cred2',
  file_uri_list =>'https://objectstorage.us-ashburn-1.oraclecloud.com/n/XXXXX/b/XXXXX/o/mongoAPI/mongo_en.dmp',
  format => json_object('type' value 'datapump'),
  query => 'SELECT * FROM local_table l where l.json_document.Language=''en'''
);
END;
/

Now create the External table and point it to the exported dumpfile:

BEGIN  
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
      table_name =>'EX_EN',  
      credential_name =>'obj_store_cred2',  
     format => json_object('type' value 'datapump'),   
        file_uri_list     => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/xxxxx/b/xxxxx/o/mongoAPI/mongo_en.dmp', 
    column_list => 'ID VARCHAR2 (255) , CREATED_ON TIMESTAMP DEFAULT sys_extract_utc(SYSTIMESTAMP) , LAST_MODIFIED TIMESTAMP DEFAULT sys_extract_utc(SYSTIMESTAMP) , VERSION VARCHAR2 (255) ,JSON_DOCUMENT  BLOB'    );
   END;
/

And verify if we have the right data:

Now you can delete the data from the LOCAL_TABLE:

Check from MongoDB API we don’t have the Language=en documents:

But we have other languages:

The View

Now we can create our “Hybrid External Table” joining the LOCAL_TABLE and the EX_EN(external table on Object Storage Bucket), for this we will create a very simple view:

CREATE VIEW M_VIEW AS 
SELECT * FROM LOCAL_TABLE
UNION ALL
SELECT * FROM EX_EN;

But this view is not available from the API:

show collections
col_teste
EX_EN
EX_NORMAL
EXAMPLE_API
LOCAL_TABLE
mCol
movies
movies2
mycollection
posts
TAB_ORACLE
tab2
teste_external
teste123
tttt
V_TESTE

We need to expose it using the SODA, see the difference from the previous SODA_COLLECTION, here we are using the viewName parameter:

DECLARE
   L_RETURN_VALUE SYS.SODA_COLLECTION_T;
   L_COLLECTION_NAME NVARCHAR2(200) := 'M_VIEW';
   L_METADATA VARCHAR2(2000) := '{
    "schemaName": "ADMIN",
    "viewName": "M_VIEW",
    "keyColumn": {
        "name": "ID",
        "sqlType": "VARCHAR2",
        "maxLength": 255,
        "assignmentMethod": "EMBEDDED_OID",
        "path": "_id"
    },
    "contentColumn": {
        "name": "JSON_DOCUMENT",
        "sqlType": "BLOB",
        "jsonFormat":"OSON"
    },
    "lastModifiedColumn": {
        "name": "LAST_MODIFIED"
    },
    "versionColumn": {
        "name": "VERSION",
        "method": "UUID"
    },
    "creationTimeColumn": {
        "name": "CREATED_ON"
    },
    "readOnly": false
}';
   L_CREATE_MODE BINARY_INTEGER := 1;
BEGIN
   L_RETURN_VALUE := SYS.DBMS_SODA.CREATE_COLLECTION(
      COLLECTION_NAME => L_COLLECTION_NAME,
      METADATA => L_METADATA,
      CREATE_MODE => DBMS_SODA.CREATE_MODE_MAP
   );
END;
/

And from MongoDB we can see the view

show collections
col_teste
EX_EN
EX_NORMAL
EXAMPLE_API
LOCAL_TABLE
M_VIEW
mCol
movies
movies2
mycollection
posts
TAB_ORACLE
tab2
teste_external
teste123
tttt
V_TESTE

And we can see the “en” values