Migrate from Oracle NoSQL Database Cloud Service to a JSON file

This example shows how to use the Oracle NoSQL Database Migrator to copy data and the schema definition of a NoSQL table from Oracle NoSQL Database Cloud Service (NDCS) to a JSON file.

Use Case

An organization decides to train a model using the Oracle NoSQL Database Cloud Service (NDCS) data to predict future behaviors and provide personalized recommendations. They can take a periodic copy of the NDCS tables' data to a JSON file and apply it to the analytic engine to analyze and train the model. Doing this helps them separate the analytical queries from the low-latency critical paths.

Example

For the demonstration, let us look at how to migrate the data and schema definition of a NoSQL table called myTable from NDCS to a JSON file.

Prerequisites

Identify the source and sink for the migration.
- Source: Oracle NoSQL Database Cloud Service
- Sink: JSON file
Identify your OCI cloud credentials and capture them in the OCI config file. Save the config file in /home/.oci/config. See Acquiring Credentials in Using Oracle NoSQL Database Cloud Service.
```
[DEFAULT]
tenancy=ocid1.tenancy.oc1....
user=ocid1.user.oc1....
fingerprint= 43:d1:....
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-phoenix-1
- compartment: developers

Procedure

To migrate the data and schema definition of myTable from Oracle NoSQL Database Cloud Service to a JSON file:

Open the command prompt and navigate to the directory where you extracted the NoSQL Database Migrator utility.
To generate the configuration file using the NoSQL Database Migrator, run the runMigrator command without any runtime parameters.
```
[~/nosqlMigrator/nosql-migrator-1.0.0]$./runMigrator
```
As you did not provide the configuration file as a runtime parameter, the utility prompts if you want to generate the configuration now. Type y.
```
configuration file is not provided. Do you want to generate configuration? (y/n) [n]: y
 
This command provides a walkthrough of creating a valid config for
Oracle NoSQL data migrator.
 
```

Based on the prompts from the utility, choose your options for the Source configuration.

Enter a location for your config [./migrator-config.json]: /home/apothula/nosqlMigrator/NDCS2JSON
Select the source:
1) nosqldb
2) nosqldb_cloud
3) file
#? 2
Configuration for source type=nosqldb_cloud
Enter endpoint URL or region of the Oracle NoSQL Database Cloud: us-phoenix-1
Enter table name: myTable
Enter compartment name or id of the source table []: developers
Enter path to the file containing OCI credentials [/home/apothula/.oci/config]:
Enter the profile name in OCI credentials file [DEFAULT]:
Enter percentage of table read units to be used for migration operation. (1-100) [90]:
Enter store operation timeout in milliseconds. (1-30000) [5000]:

Based on the prompts from the utility, choose your options for the Sink configuration.

Select the sink:
1) nosqldb
2) nosqldb_cloud
3) file
#? 3
Configuration for sink type=file
Enter path to a file to store JSON data: /home/apothula/nosqlMigrator/myTableJSON
Would you like to store JSON in pretty format? (y/n) [n]: y
Would you like to migrate the table schema also? (y/n) [y]: y
Enter path to a file to store table schema: /home/apothula/nosqlMigrator/myTableSchema

Based on the prompts from the utility, choose your options for the source data transformations. The default value is n.
```
Would you like to add transformations to source data? (y/n) [n]:
```
Enter your choice to determine whether to proceed with the migration in case any record fails to migrate.
```
Would you like to continue migration in case of any record/row is failed to migrate?: (y/n) [n]:
```

The utility displays the generated configuration on the screen.

generated configuration is:
{
  "source": {
    "type": "nosqldb_cloud",
    "endpoint": "us-phoenix-1",
    "table": "myTable",
    "compartment": "developers",
    "credentials": "/home/apothula/.oci/config",
    "credentialsProfile": "DEFAULT",
    "readUnitsPercent": 90,
    "requestTimeoutMs": 5000
  },
  "sink": {
    "type": "file",
    "format": "json",
    "schemaPath": "/home/apothula/nosqlMigrator/myTableSchema",
    "pretty": true,
    "dataPath": "/home/apothula/nosqlMigrator/myTableJSON"
  },
  "abortOnError": true,
  "migratorVersion": "1.0.0"
}

Finally, the utility prompts for your choice to decide whether to proceed with the migration with the generated configuration file or not. The default option is y.

Note:
If you select n, you can use the generated configuration file to run the migration using the ./runMigrator -c or the ./runMigrator --config option.
```
would you like to run the migration with above configuration?
If you select no, you can use the generated configuration file to run the migration using
./runMigrator --config /home/apothula/nosqlMigrator/NDCS2JSON
(y/n) [y]:
```

The NoSQL Database Migrator migrates your data and schema from NDCS to the JSON file.

Records provided by source=10,Records written to sink=10,Records failed=0.
Elapsed time: 0min 1sec 277ms
Migration completed.

Validation

To validate the migration, you can open the JSON Sink files and view the schema and data.

-- Exported myTable Data
 
[~/nosqlMigrator]$cat myTableJSON
{
  "id" : 10,
  "document" : {
    "course" : "Computer Science",
    "name" : "Neena",
    "studentid" : 105
  }
}
{
  "id" : 3,
  "document" : {
  "course" : "Computer Science",
    "name" : "John",
    "studentid" : 107
  }
}
{
  "id" : 4,
  "document" : {
    "course" : "Computer Science",
    "name" : "Ruby",
    "studentid" : 100
  }
}
{
  "id" : 6,
  "document" : {
    "course" : "Bio-Technology",
    "name" : "Rekha",
    "studentid" : 104
  }
}
{
  "id" : 7,
  "document" : {
    "course" : "Computer Science",
    "name" : "Ruby",
    "studentid" : 100
  }
}
{
  "id" : 5,
  "document" : {
    "course" : "Journalism",
    "name" : "Rani",
    "studentid" : 106
  }
}
{
  "id" : 8,
  "document" : {
    "course" : "Computer Science",
    "name" : "Tom",
    "studentid" : 103
  }
}
{
  "id" : 9,
  "document" : {
    "course" : "Computer Science",
    "name" : "Peter",
    "studentid" : 109
  }
}
{
  "id" : 1,
  "document" : {
    "course" : "Journalism",
    "name" : "Tracy",
    "studentid" : 110
  }
}
{
  "id" : 2,
  "document" : {
    "course" : "Bio-Technology",
    "name" : "Raja",
    "studentid" : 108
  }
}

-- Exported myTable Schema
 
[~/nosqlMigrator]$cat myTableSchema
CREATE TABLE IF NOT EXISTS myTable (id INTEGER, document JSON, PRIMARY KEY(SHARD(id)))