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

  • Identify the source and sink for the migration.
    • Source: JSON source file.

      SampleData.json is the source file. It contains multiple JSON documents with one document per line, delimited by a new line character. 

      {"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"}}
{"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":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":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"}}
    • Sink: Oracle NoSQL Database
  • Identify the following details for the JSON source file:
    • schemaPath: <absolute path to the schema definition file containing DDL statements for the NoSQL table at the sink>.

      In this example, the DDL file is schema_json.ddl. 

      create table Migrate_JSON (id INTEGER, val_json JSON, PRIMARY KEY(id));

      The Oracle NoSQL Database Migrator provides an option to create a table with the default schema if the schemaPath is not provided. For more details, see Identify the Source and Sink topic in the Workflow for Oracle NoSQL Database Migrator.

    • Datapath: <absolute path to a file or directory containing the JSON data for migration>
  • Identify the following details for the on-premise KVStore:
    • storename: kvstore
    • helperHosts: <hostname>:5000
    • table: Migrate_JSON

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.7.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-1.7.0]$./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