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 INTEGER. 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 INTEGER, 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

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

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

A JSON collection is designed to store and manage multiple JSON documents within a database. It does not require a fixed schema, allowing each document to have its own flexible structure. This example demonstrates how you can migrate a MongoDB-formatted JSON file to a JSON collection.

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

   When creating a JSON collection table in Oracle NoSQL Database, you only need to define the primary key and its type as per the input MongoDB formatted JSON in the schema. All other attributes will be stored as part of the JSON document and do not need to be explicitly defined in the schema file.

   The following user-defined schema is used in this example:

   CREATE TABLE IF NOT EXISTS sampleMongoDBJSONCollection (id INTEGER, PRIMARY KEY(SHARD(id))) AS JSON COLLECTION

   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/schema/file>"
     },
     "dataPath" : "</complete/path/to/the/MongoDB/Formatted/JSON/file>"
    },
    "sink" : {
     "type" : "nosqldb",
     "storeName" : "kvstore",
     "helperHosts" : ["<localhost:5000>"],
     "table" : "sampleMongoDBJSONCollection",
     "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.

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

Verification

Start the SQL prompt in your KVStore.

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

Verify that the sampleMongoDBJSONCollection table is created with the source data:

sql-> select * from sampleMongoDBJSONCollection;
{"id":2,"name":"Corliss Zuk","scores":[{"score":67.03077096065002,"type":"exam"},{"score":6.301851677835235,"type":"quiz"},{"score":66.28344683278382,"type":"homework"}]}
{"id":4,"name":"Zachary Langlais","scores":[{"score":78.68385091304332,"type":"exam"},{"score":90.2963101368042,"type":"quiz"},{"score":34.41620148042529,"type":"homework"}]}
{"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":3,"name":"Bao Ziglar","scores":[{"score":71.64343899778332,"type":"exam"},{"score":24.80221293650313,"type":"quiz"},{"score":42.26147058804812,"type":"homework"}]}
5 rows returned

1. For this use case, we will use the sample MongoDB-formatted JSON file exported from a Spring application as the source. For more details on this format, see Spring Data.

2. 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. Aggregate the remaining fields to a as a field named kv_json_ of type JSON, adhering to the Spring data format. For more details, see the Persistence Model of spring data framework.
   • 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 sampleMongoDBSpringImp (id STRING, kv_json_ JSON, PRIMARY KEY(SHARD(id)));

   For the Spring data sample given above, you must include the following transformations:

   • renameFields transformation to convert the _id column to id
   • ignoreFields transformation to ignore the _class column and not include it in the sink table
   • aggregateFields transformation to aggregate the remaining fields (other than id) to a field of type JSON
   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": "sampleMongoDBSpringImp",
       "includeTTL": true,
       "schemaInfo": {
         "useSourceSchema": true
       },
       "overwrite": false,
       "requestTimeoutMs": 5000
     },
     "transforms": {
       "renameFields": {
         "_id": "id"
       },
       "ignoreFields": [
         "_class"
       ],
       "aggregateFields": {
         "fieldName": "kv_json_",
         "skipFields": ["id"]
       }
     },
     "abortOnError": true,
     "migratorVersion" : "1.7.0"
   }

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

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

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

5. 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 sampleMongoDBSpringImp (id STRING, kv_json_ JSON, PRIMARY KEY(SHARD(id)))
   [nosqldb sink] : completed loading DDLs
   migration started
   Start writing data to OnDB Sink
   executing for source:onpremisesample
   [mongo file source] : start parsing MongoDB JSON records from file: onpremisesample.json
   Writing data to OnDB Sink completed.
   migration completed.
   Records provided by source=3, Records written to sink=3, Records failed=0,Records skipped=0.
   Elapsed time: 0min 0sec 145ms
   Migration completed.

Verification

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

sql-> select * from sampleMongoDBSpringImp;
{"id":"63d3a87cf564fc21dac3838e","kv_json_":{"address":{"Country":"USA"},"firstName":"Sam","lastName":"David"}}
{"id":"63d3a87cf564fc21dac3838d","kv_json_":{"address":{"Country":"France"},"firstName":"John","lastName":"Smith"}}
{"id":"3","kv_json_":{"address":{"Country":"England"},"firstName":"Dona","lastName":"William"}}

3 rows returned