Migrate from JSON file source to Oracle NoSQL Database Cloud Service

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

After evaluating multiple options, an organization finalizes Oracle NoSQL Database Cloud Service 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 Cloud Service.

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 Cloud Service.

Identify your OCI cloud credentials and capture them in the configuration file. Save the config file in /home/user/.oci/config. For more details, see Acquiring Credentials in Using Oracle NoSQL Database Cloud Service.
```
[DEFAULT]
tenancy=ocid1.tenancy.oc1....
user=ocid1.user.oc1....
fingerprint= 43:d1:....
region=us-ashburn-1
key_file=</fully/qualified/path/to/the/private/key/>
pass_phrase=<passphrase>
```
Identify the region endpoint and compartment name for your Oracle NoSQL Database Cloud Service.
- endpoint: us-ashburn-1
- compartment: Training-NoSQL
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>.

Procedure

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

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

{
  "source" : {
    "type" : "file",
    "format" : "json",
    "schemaInfo" : {
      "schemaPath" : "[~/nosql-migrator-1.5.0]/schema_json.ddl"
    },
    "dataPath" : "[~/nosql-migrator-1.5.0]/SampleData.json"
  },
  "sink" : {
    "type" : "nosqldb_cloud",
    "endpoint" : "us-ashburn-1",
    "table" : "Migrate_JSON",
    "compartment" : "Training-NoSQL",
    "includeTTL" : false,
    "schemaInfo" : {
      "readUnits" : 100,
      "writeUnits" : 60,
      "storageSize" : 1,
      "useSourceSchema" : true
    },
    "credentials" : "/home/user/.oci/config",
    "credentialsProfile" : "DEFAULT",
    "writeUnitsPercent" : 90,
    "overwrite" : true,
    "requestTimeoutMs" : 5000
  },
  "abortOnError" : true,
  "migratorVersion" : "1.5.0"
}

Open the command prompt and navigate to the directory where you extracted the Oracle NoSQL Database Migrator utility.
Run the runMigrator command by passing the configuration file using the --config or -c option.
```
[~/nosql-migrator-1.5.0]$./runMigrator --config <complete/path/to/the/config/file>
```

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:
source creation completed
creating sink from given configuration:
sink creation completed
creating migrator pipeline
migration started
[cloud sink] : start loading DDLs
[cloud sink] : executing DDL: create table Migrate_JSON (id INTEGER, val_json JSON, PRIMARY KEY(id)),limits: [100, 60, 1]
[cloud sink] : completed loading DDLs
[cloud sink] : start loading records
[json file source] : start parsing JSON records from file: SampleData.json
[INFO] migration completed.
Records provided by source=4, Records written to sink=4, Records failed=0, Records skipped=0.
Elapsed time: 0min 5sec 778ms
Migration completed.

Validation

To validate the migration, you can log in to your Oracle NoSQL Database Cloud Service console and verify that the Migrate_JSON table is created with the source data. For the procedure to access the console, see Accessing the Service from the Infrastructure Console article in the Oracle NoSQL Database Cloud Service document.

Figure 5-1 Oracle NoSQL Database Cloud Service Console Tables

Description of "Figure 5-1 Oracle NoSQL Database Cloud Service Console Tables"

Figure 5-2 Oracle NoSQL Database Cloud Service Console Table Data

Description of "Figure 5-2 Oracle NoSQL Database Cloud Service Console Table Data"