Migrate from JSON file source to Oracle NoSQL Database

This example shows the usage of Oracle NoSQL Database Migrator to copy data from a JSON file source to Oracle NoSQL Database.

Use Case

After evaluating multiple options, an organization finalizes Oracle NoSQL Database as its NoSQL Database platform. As its source contents are in JSON file format, they are looking for a way to migrate them to Oracle NoSQL Database.

Example

In this example, you will learn to migrate the data from a JSON file called SampleData.json. You run the runMigrator utility by passing a pre-created configuration file. If the configuration file is not provided as a run time parameter, the runMigrator utility prompts you to generate the configuration through an interactive procedure.

Prerequisites

Procedure

To migrate the JSON source file from SampleData.json to Oracle NoSQL Database Cloud Service, perform the following:

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

    Generated configuration is:
{
  "source" : {
    "type" : "file",
    "format" : "json",
    "schemaInfo" : {
      "schemaPath" : "/home/<username>/nosqlMigrator/schema_json.ddl"
    },
    "dataPath" : "/home/<username>/nosqlMigrator/SampleData.json"
  },
  "sink" : {
    "type" : "nosqldb",
    "storeName" : "kvstore",
    "helperHosts" : ["localhost:5000"],
    "table" : "Migrate_JSON",
    "includeTTL" : false,
    "schemaInfo" : {
      "useSourceSchema" : true
    },
    "overwrite" : true,
    "requestTimeoutMs" : 5000
  },
  "abortOnError" : true,
  "migratorVersion" : "1.8.0"
}

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

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

    [~/nosql-migrator]$./runMigrator --config <complete/path/to/the/config/file>

  4. The utility proceeds with the data migration, as shown below. The Migrate_JSON table is created at the sink with the schema provided in the schemaPath.

    creating source from given configuration:
[INFO] source creation completed
[INFO] creating sink from given configuration:
[INFO] sink creation completed
[NFO] creating migrator pipeline
[INFO] [nosqldb sink] : start loading DDLs
[nosqldb sink] : executing DDL: create table Migrate_JSON (id INTEGER, val_json JSON, PRIMARY KEY(id))
[INFO] [nosqldb sink] : completed loading DDLs
[INFO] migration started
[INFO] Start writing data to OnDB Sink
[INFO] executing for source:SampleData
[INFO] [json file source] : start parsing JSON records from file: SampleData.json
[INFO] Writing data to OnDB Sink completed.
[INFO] migration completed.
Records provided by source=4, Records written to sink=4, Records failed=0,Records skipped=0.
Elapsed time: 0min 0sec 81ms
Migration completed.

Verification

Start the SQL prompt in your data store.

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

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

SELECT * FROM Migrate_JSON

Output:

{"id":7,"val_json":{"array":["a","b","c"],"date":"2023-02-20T02:38:57.520Z","nestarray":[[1,2,3],[10,20,30]],"nested":{"arrayofobjects":[{"datefield":"2023-01-20T02:38:57.520Z","numfield":28,"strfield":"foo"},{"datefield":"2023-01-22T02:38:57.520Z","numfield":38,"strfield":"bar"}],"nestNum":10,"nestString":"bar"},"num":1,"string":"foo"}}
{"id":3,"val_json":{"array":["g","h","i"],"date":"2023-02-02T02:38:57.520Z","nestarray":[[1,2,3],[10,20,30]],"nested":{"arrayofobjects":[{"datefield":"2023-02-02T02:38:57.520Z","numfield":28,"strfield":"foo3"},{"datefield":"2023-02-02T02:38:57.520Z","numfield":38,"strfield":"bar"}],"nestNum":10,"nestString":"bar"},"num":1,"string":"foo"}}
{"id":4,"val_json":{"array":["j","k","l"],"date":"2023-02-03T02:38:57.520Z","nestarray":[[1,2,3],[10,20,30]],"nested":{"arrayofobjects":[{"datefield":"2023-02-03T02:38:57.520Z","numfield":28,"strfield":"foo"},{"datefield":"2023-02-03T02:38:57.520Z","numfield":38,"strfield":"bar"}],"nestNum":10,"nestString":"bar"},"num":1,"string":"foo"}}
{"id":6,"val_json":{"array":["q","r","s"],"date":"2023-02-04T02:38:57.520Z","nestarray":[[1,2,3],[10,20,30]],"nested":{"arrayofobjects":[{"datefield":"2023-03-04T02:38:57.520Z","numfield":30,"strfield":"foo54"},{"datefield":"2023-02-04T02:38:57.520Z","numfield":56,"strfield":"bar23"}],"nestNum":10,"nestString":"bar"},"num":1,"string":"foo"}}

4 rows returned