Previous Next JavaScript must be enabled to correctly display this content

## Migrate from Oracle NoSQL Database 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 to a JSON file.

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.

Prerequisites

- Identify the source and sink for the migration.
  - Source: Oracle NoSQL Database
  - Sink: JSON file
- Identify the following details for the on-premise KVStore:
  - storeName: `kvstore`
  - helperHosts: `<hostname>:5000`
  - table: `myTable`

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)))