Migration of MongoDB collections To Oracle JSON — Part 1
- Sathishkumar Rangaraj
- Jun 12, 2022
- 3 min read
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
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 MongoDB 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.JSONDOCUMENT.”_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 ( CRUD Operations ) video
Also watch above migration 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
댓글