Use Case

An organization decides to train a model using the Oracle NoSQL Database data to predict future behaviors and provide personalized recommendations. They can take a periodic copy of the NoSQL Database 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 NoSQL Database to a JSON file.

Procedure

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

1. Open the command prompt and navigate to the directory where you extracted the NoSQL Database Migrator utility.
2. To generate the configuration file using the NoSQL Database Migrator, run the runMigrator command without any runtime parameter.

   [~/nosqlMigrator]$./runMigrator

3. 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
   Generating a configuration file interactively.

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

   Enter a location for your config [./migrator-config.json]: /home/<user>/nosqlMigrator/
   Select the source: 
   1) nosqldb
   2) nosqldb_cloud
   3) file
   4) object_storage_oci
   5) aws_s3
   #? 1
   
   Configuration for source type=nosqldb
   
   Enter store name of the Oracle NoSQL Database: kvstore
   
   Enter comma separated list of host:port of Oracle NoSQL Database: <hostname>:5000
   
   Enter fully qualified table name: myTable
   
   Include TTL data? If you select 'yes' TTL of rows will also 
   be included in the exported data.(y/n) [n]: 
   
   Is the store secured? (y/n) [y]:
   
   Enter store operation timeout in milliseconds. (1-30000) [5000]:

5. 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
   Select the sink file format: 
   1) json
   2) parquet
   #? 1
   
   Enter path to a directory to store JSON data: /home/<user>/nosqlMigrator
   
   would you like to export data to multiple files for each source?(y/n) [y]: n
   
   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/<user>/nosqlMigrator/myTableSchema.ddl

6. 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]:

7. 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]:

8. The utility displays the generated configuration on the screen.

   Generated configuration is:
   {
    "source" : {
     "type" : "nosqldb",
     "storeName" : "kvstore",
     "helperHosts" : ["<hostname>:5000"],
     "table" : "myTable",
     "includeTTL" : true,
     "requestTimeoutMs" : 5000
    },
    "sink" : {
     "type" : "file",
     "format" : "json",
     "useMultiFiles" : false,
     "schemaPath" : "/home/<username>/nosqlMigrator/myTableSchema.ddl",
     "pretty" : true,
     "dataPath" : "/home/<username>/nosqlMigrator/"
    },
    "abortOnError" : true,
    "migratorVersion" : "1.7.0"
   }

Verification

To verify the migration, you can navigate to the specified sink directory and view the schema and data.

-- Exported myTable Data. JSON files are created in the supplied data path
[~/nosqlMigrator]$cat myTable_1_5.json

{
  "id" : 2,
  "document" : "{\"course\":\"Bio-Technology\",\"name\":\"Raja\",\"studentid\":108}"
}
{
  "id" : 7,
  "document" : "{\"course\":\"Computer Science\",\"name\":\"Ruby\",\"studentid\":100}"
}
{
  "id" : 4,
  "document" : "{\"course\":\"Computer Science\",\"name\":\"Ruby\",\"studentid\":100}"
}
{
  "id" : 10,
  "document" : "{\"course\":\"Computer Science\",\"name\":\"Neena\",\"studentid\":105}"
}
{
  "id" : 5,
  "document" : "{\"course\":\"Journalism\",\"name\":\"Rani\",\"studentid\":106}"
}

[~/nosqlMigrator]$cat myTable_6_10.json

{
  "id" : 8,
  "document" : "{\"course\":\"Computer Science\",\"name\":\"Tom\",\"studentid\":103}"
}
{
  "id" : 3,
  "document" : "{\"course\":\"Computer Science\",\"name\":\"John\",\"studentid\":107}"
}
{
  "id" : 9,
  "document" : "{\"course\":\"Computer Science\",\"name\":\"Peter\",\"studentid\":109}"
}
{
  "id" : 6,
  "document" : "{\"course\":\"Bio-Technology\",\"name\":\"Rekha\",\"studentid\":104}"
}
{
  "id" : 1,
  "document" : "{\"course\":\"Journalism\",\"name\":\"Tracy\",\"studentid\":110}"
}

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