Previous Next JavaScript must be enabled to correctly display this content

## Migrate from MongoDB JSON file to Oracle NoSQL Database

This example shows how to use the Oracle NoSQL Database Migrator to copy MongoDB-formatted data to Oracle NoSQL Database.

Use Case

After evaluating multiple options, an organization finalizes Oracle NoSQL Database as its NoSQL Database platform. The tables and data are in MongoDB and the organization wants to migrate both to Oracle NoSQL Database.

You can copy a file or directory containing the MongoDB exported JSON data for migration by specifying the file or directory in the source configuration template.

A sample MongoDB-formatted JSON file is as follows:

    {"_id":0,"name":"Aimee Zank","scores":[{"score":1.463179736705023,"type":"exam"},{"score":11.78273309957772,"type":"quiz"},{"score":35.8740349954354,"type":"homework"}]}
    {"_id":1,"name":"Aurelia Menendez","scores":[{"score":60.06045071030959,"type":"exam"},{"score":52.79790691903873,"type":"quiz"},{"score":71.76133439165544,"type":"homework"}]}
    {"_id":2,"name":"Corliss Zuk","scores":[{"score":67.03077096065002,"type":"exam"},{"score":6.301851677835235,"type":"quiz"},{"score":66.28344683278382,"type":"homework"}]}
    {"_id":3,"name":"Bao Ziglar","scores":[{"score":71.64343899778332,"type":"exam"},{"score":24.80221293650313,"type":"quiz"},{"score":42.26147058804812,"type":"homework"}]}
    {"_id":4,"name":"Zachary Langlais","scores":[{"score":78.68385091304332,"type":"exam"},{"score":90.2963101368042,"type":"quiz"},{"score":34.41620148042529,"type":"homework"}]}

MongoDB supports two types of extensions to the formatted JSON files, Canonical mode and Relaxed mode. You can supply the MongoDB-formatted JSON file that is generated using the mongoexport tool in either Canonical or Relaxed mode. NoSQL Database Migrator supports both the modes.

For more information on the MongoDB Extended JSON (v2) file, See mongoexport_formats.

For more information on the generation of MongoDB-formatted JSON file, See mongoexport.

Example

For the demonstration, let us look at how to migrate a MongoDB-formatted JSON file to Oracle NoSQL Database. We will use a manually created configuration file for this example.
Prerequisites

- Identify the source and sink for the migration.
  - Source: MongoDB-formatted JSON File
  - Sink: Oracle NoSQL Database
- Extract the data from MongoDB using the mongoexport utility. See mongoexport for more information.

Procedure

To migrate the MongoDB-formatted JSON data to Oracle NoSQL Database, you can choose from one of the following options:

1. Prepare the configuration file (in JSON format) with the identified Source and Sink details. See Source Configuration Templates and Sink Configuration Templates .

   Here, you set the `defaultSchema` configuration parameter to true. Therefore, NoSQL Database Migrator creates a table with the default schema at the sink.

       {
         "source" : {
           "type" : "file",
           "format" : "mongodb_json",
           "dataPath" : "<complete/path/to/the/MongoDB/Formatted/JSON/file>"
         },
         "sink" : {
           "type" : "nosqldb",
           "storeName" : "kvstore",
           "helperHosts" : ["phoenix126166:5000"],
           "table" : "mongoImport",
           "includeTTL" : true,
           "schemaInfo" : {
             "defaultSchema" : true
           },
           "overwrite" : true,
           "requestTimeoutMs" : 5000
         },
         "abortOnError" : true,
         "migratorVersion" : "1.7.0"
       }

   The default schema for MongoDB-formatted JSON file source is as follows:

       CREATE TABLE IF NOT EXISTS <tablename>(id STRING, document JSON,PRIMARY KEY(SHARD(id));

   Where:
   - `tablename` = value provided for the `table` attribute in the configuration.
   - `id` = The `_id` value from each document of the MongoDB exported JSON source file.
   - `document` = For each document in the MongoDB exported file, the contents excluding the`_id` field are aggregated into the `document` column.

   Note:

   If the table <tablename> already exists in Oracle NoSQL Database and you want to migrate data to the table using the `defaultSchema` configuration, you must ensure that the existing table has the ID column in lower case (id) and is of the type STRING.

2. Open the command prompt and navigate to the directory where you extracted the NoSQL Database Migrator utility.

3. Run the `runMigrator` command by passing the configuration file. Use `--config` or `-c` option.

       $./runMigrator --config <complete/path/to/the/JSON/config/file>

4. The utility proceeds with the data migration, as shown below.

       creating source from given configuration:
       source creation completed
       creating sink from given configuration:
       sink creation completed
       creating migrator pipeline
       [nosqldb sink] : start loading DDLs
       [nosqldb sink] : executing DDL: CREATE TABLE IF NOT EXISTS mongoImport (id STRING, document JSON, PRIMARY KEY(SHARD(id)))
       [nosqldb sink] : completed loading DDLs
       migration started
       Start writing data to OnDB Sink
       executing for source:mongoDBSample
       [mongo file source] : start parsing MongoDB JSON records from file: mongoDBSample.json
       Writing data to OnDB Sink completed.
       migration completed.
       Records provided by source=5, Records written to sink=5, Records failed=0,Records skipped=0.
       Elapsed time: 0min 0sec 108ms
       Migration completed.

Verification

Start the SQL prompt in your KVStore.

     java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore

Verify that the `mongoImport` table is created with the source data:

    sql-> select * from mongoImport;
    {"id":"4","document":{"name":"Zachary Langlais","scores":[{"score":78.68385091304332,"type":"exam"},{"score":90.2963101368042,"type":"quiz"},{"score":34.41620148042529,"type":"homework"}]}}
    {"id":"1","document":{"name":"Aurelia Menendez","scores":[{"score":60.06045071030959,"type":"exam"},{"score":52.79790691903873,"type":"quiz"},{"score":71.76133439165544,"type":"homework"}]}}
    {"id":"2","document":{"name":"Corliss Zuk","scores":[{"score":67.03077096065002,"type":"exam"},{"score":6.301851677835235,"type":"quiz"},{"score":66.28344683278382,"type":"homework"}]}}
    {"id":"3","document":{"name":"Bao Ziglar","scores":[{"score":71.64343899778332,"type":"exam"},{"score":24.80221293650313,"type":"quiz"},{"score":42.26147058804812,"type":"homework"}]}}
    {"id":"0","document":{"name":"Aimee Zank","scores":[{"score":1.463179736705023,"type":"exam"},{"score":11.78273309957772,"type":"quiz"},{"score":35.8740349954354,"type":"homework"}]}}

    5 rows returned

1. Prepare the configuration file (in JSON format) with the identified Source and Sink details. See Source Configuration Templates and Sink Configuration Templates .

   Here, you specify the file containing the DDL statement of the sink table in the `schemaPath` parameter of the source configuration template. Correspondingly, set the `useSourceSchema` configuration parameter to true in the sink configuration template.

   You can generate a custom schema as follows:

   - Note the names and data types for each column from the MongoDB-formatted JSON data. Use this information to create a schema DDL file for the Oracle NoSQL Database table.
   - In the schema file, name the first column (primary key) as `id` of type STRING. Include the same name and type for the remaining columns as recorded in the MongoDB-formatted JSON file.
   - Save the schema file and note down it's complete path.
   The following user-defined schema is used in this example:

       CREATE TABLE IF NOT EXISTS sampleMongoDBImp (id STRING, name STRING, scores JSON, PRIMARY KEY(SHARD(id)));

   You must include a `renameFields` transformation instructing NoSQL Database Migrator to convert the `_id` column to `id` while creating the table. For parameter details, see Transformation Configuration Templates. NoSQL Database Migrator creates a table with the custom schema at the sink.

       {
         "source" : {
           "type" : "file",
           "format" : "mongodb_json",
           "schemaInfo" : {
             "schemaPath" : "<complete/path/to/the/schema/file>"
           },
           "dataPath" : "<complete/path/to/the/MongoDB/Formatted/JSON/file>"
         },
         "sink" : {
           "type" : "nosqldb",
           "storeName" : "kvstore",
           "helperHosts" : ["phoenix126166:5000"],
           "table" : "sampleMongoDBImp",
           "includeTTL" : true,
           "schemaInfo" : {
             "useSourceSchema" : true
           },
           "overwrite" : false,
           "requestTimeoutMs" : 5000
         },
         "transforms": {
           "renameFields" : {
             "_id":"id"
           }
         },
         "abortOnError" : true,
         "migratorVersion" : "1.7.0"
       }

2. Open the command prompt and navigate to the directory where you extracted the NoSQL Database Migrator utility.

3. Run the `runMigrator` command by passing the configuration file. Use `--config` or `-c` option.

       $./runMigrator --config <complete/path/to/the/JSON/config/file>

4. The utility proceeds with the data migration, as shown below.

       creating source from given configuration:
       source creation completed
       creating sink from given configuration:
       sink creation completed
       creating migrator pipeline
       [nosqldb sink] : start loading DDLs
       [nosqldb sink] : executing DDL: CREATE TABLE IF NOT EXISTS sampleMongoDBImp (id INTEGER, name STRING, scores JSON, PRIMARY KEY(SHARD(id)))
       [nosqldb sink] : completed loading DDLs
       migration started
       Start writing data to OnDB Sink
       executing for source:mongoDBSample
       [mongo file source] : start parsing MongoDB JSON records from file: mongoDBSample.json
       Writing data to OnDB Sink completed.
       migration completed.
       Records provided by source=5, Records written to sink=5, Records failed=0,Records skipped=0.
       Elapsed time: 0min 0sec 72ms
       Migration completed.

Verification

Start the SQL prompt in your KVStore.

     java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore

Verify that the `sampleMongoDBImp` table is created with the source data:

    sql-> select * from sampleMongoDBImp;
    {"id":0,"name":"Aimee Zank","scores":[{"score":1.463179736705023,"type":"exam"},{"score":11.78273309957772,"type":"quiz"},{"score":35.8740349954354,"type":"homework"}]}
    {"id":2,"name":"Corliss Zuk","scores":[{"score":67.03077096065002,"type":"exam"},{"score":6.301851677835235,"type":"quiz"},{"score":66.28344683278382,"type":"homework"}]}
    {"id":1,"name":"Aurelia Menendez","scores":[{"score":60.06045071030959,"type":"exam"},{"score":52.79790691903873,"type":"quiz"},{"score":71.76133439165544,"type":"homework"}]}
    {"id":3,"name":"Bao Ziglar","scores":[{"score":71.64343899778332,"type":"exam"},{"score":24.80221293650313,"type":"quiz"},{"score":42.26147058804812,"type":"homework"}]}
    {"id":4,"name":"Zachary Langlais","scores":[{"score":78.68385091304332,"type":"exam"},{"score":90.2963101368042,"type":"quiz"},{"score":34.41620148042529,"type":"homework"}]}

    5 rows returned