Migration of Mongodb collections To Oracle Json — Part 1

Introduction:

In this article we will discuss about two famous databases which handles Json like documents

MongoDB is one of the popular open-source database used to create and stores data in flexible, JSON-like documents, meaning fields can vary from document to document and data structure can be changed over time.

Oracle which is one of world famous leading relational , multi-model database called as Converge Database , has good capability of handling json. Oracle introduced support for Json from v12c , stored JSON data as varchar2 or LOB ( Clob / Blob).

Advantages of MongoDB

• MongoDB is easy to use and fast to get started
• No complex schema design
• No verbose SQL
• Intuitive data model: one JSON document vs normalized relational tables
• Simple schema changes

Challenges of MongoDB

• MongoDB is fast to get started, but it lacks key features for enterprise deployments
• Limited transaction support leads to data consistency challenges
• Difficult and inefficient analytic queries (and no parallel query)
• Immature security features
• Ansi-Sql functionality

Json Data Type - Advantage in Oracle

From Oracle version 21c , json stored in native format, so users can query JSON documents using standard SQL which enabled to build application with the flexibility of a schema-less design model with all the power of Oracle Database like replication , partitioning , indexing with all the functionalities that the Oracle Database is capable of.

• JSON can be manipulated through Sql query
• Stored as OSON — optimized native binary representation

Integrated with the Oracle Database platform

• Parallel Query
• Partitioning
• RAC and Dataguard
• Golden Gate
• Datapump
• Exadata
• Materialized Views
• Advanced Queuing
• Sharding
  …
• Leverage Advanced Security features
• Virtual Private Database, Encryption, ..
• Manage JSON together with other data
• Operational simplicity from converged database

In the above background, Oracle Database is a niche software because it is easy to migrate the Mongodb collection and the data is secure, having the advantages that Oracle Database offers. It would be my endeavor to present this topic on migration of Mongodb collection to Oracle Database in following articles.

Each article would deal with the methods, advantages and the ease of use of Oracle Database over any other comparative open-source methods.

In the above background and as a part of 1st article of the series, let us examine various methods to migrate Mongodb collections to Oracle Database ( Json)

There are different methods to migrate and let us examine them as below:

Method 1 : On-prem Mongdb collections to Oracle Database (Using ORACLE_LOADER)

In Detail :

Assuming we have mongodb deployed in local windows m/c

In this example,

Step 1/ Let us examine the export of one collection , stored as flat file

Mongodb collections will come with unique oid value with key column as “_id”

Sample Data of MongoDB Collections

{“_id”:{“$oid”:”573a1390f29313caabcd4323"},”plot”:”A young boy, opressed by his mother, goes on an outing in the country with a social welfare group where he dares to dream of a land where the cares of his ordinary life fade.”,”genres”:[“Short”,”Drama”,”Fantasy”],”runtime”:14,”rated”:”UNRATED”,”cast”:[“Martin Fuller”,”Mrs. William Bechtel”,”Walter Edwin”,”Ethel Jewett”],”num_mflix_comments”:1,”poster”:”https://m.media-amazon.com/images/M/MV5BMTMzMDcxMjgyNl5BMl5BanBnXkFtZTcwOTgxNjg4Mg@@._V1_SY1000_SX677_AL_.jpg","title":"The Land Beyond the Sunset”,”fullplot”:”Thanks to the Fresh Air Fund, a slum child escapes his drunken mother for a day’s outing in the country. Upon arriving, he and the other children are told a story about a mythical land of no pain. Rather then return to the slum at day’s end, the lad seeks to journey to that beautiful land beyond the sunset.”,”languages”:[“English”],”released”:{“$date”:{“$numberLong”:”-1804377600000"}},”directors”:[“Harold M. Shaw”],”writers”:[“Dorothy G. Shore”],”awards”:{“wins”:1,”nominations”:0,”text”:”1 win.”},”lastupdated”:”2015–08–29 00:27:45.437000000",”year”:1912,”imdb”:{“rating”:7.1,”votes”:448,”id”:488},”countries”:[“USA”],”type”:”movie”,”tomatoes”:{“viewer”:{“rating”:3.7,”numReviews”:53,”meter”:67},”lastUpdated”:{“$date”:”2015–04–27T19:06:35Z”}}}

Step 2/ Login to TARGET oracle database

create oracle directory and grant appropriate privilege's to oracle user

SQL> create or replace directory stage_dir as ‘C:\MyFolder\sqlcode’;
SQL> GRANT READ, WRITE ON DIRECTORY STAGE_DIR TO SCOTT;

Step 3/ Create external table with TYPE ORACLE_LOADER

The ORACLE_LOADER access driver provides a set of access parameters unique to external tables of the type ORACLE_LOADER To use the external table management features that the ORACLE_LOADER access parameters provide, you must have some knowledge of the file format and record format (including character sets and field data types) of the data files on your platform.

For more information about ORACLE_LOADER refer here

CREATE TABLE MOVIES_EXT(JSON_DOCUMENT CLOB)
ORGANIZATION EXTERNAL( TYPE ORACLE_LOADER
DEFAULT DIRECTORY STAGE_DIR
ACCESS PARAMETERS (
RECORDS DELIMITED BY 0x’0A’
DISABLE_DIRECTORY_LINK_CHECK
BADFILE STAGE_DIR: ‘movies_ext.bad’
LOGFILE STAGE_DIR: ‘movies_ext.log’
FIELDS(JSON_DOCUMENT CHAR(5000)) )
LOCATION ( STAGE_DIR:’movies.json’ ) )
PARALLEL REJECT LIMIT UNLIMITED
/

As you can see from the above we could easily read mongo db collections via oracle sql while retaining the advantages of the Oracle Sql

Step 4/ Now we can manipulate Json data through standard sql

set lines 150
col _id for a40
col title for a40
col cast for a40
select j.JSON_DOCUMENT.”_id”,
j.JSON_DOCUMENT.title,
j.JSON_DOCUMENT.cast[1]
from movies_ext j where rownum <= 5;

Now , we can create relational table by selecting external table. I have given more than 14+ use cases of manipulating Json Data using Oracle Json Sql functions in this video

Also watch above method in this video

Conclusion:

The open software nature its uses and challenges; Oracle software’s uses its advantages and how to harness the benefits of Oracle software while retaining the original structure of Mongodb. This would ensure the best of both worlds, the convenience of retaining the original database and yet surmounting the challenges faced by the open software users. This is where Oracle Database comes in handy. Once the migration is done, the users would have a seamless experience for the years in the row.

Prior to knowing about migration method -2 ( Migrating MongoDB collections to Oracle Autonomous Database using Oracle Database MongoDB API) , let us see Oracle Json’s sql functions which can be used to manipulate json data as simple as traditional relational data.

Click here or navigate to below link to view

https://medium.com/@mail2crsathishkumar/oracle-capabilities-handling-of-json-bf1e2fbdc8a7