Using Oracle NoSQL Database Migrator

Learn about Oracle NoSQL Database Migrator and how to use it for data migration.

Oracle NoSQL Database Migrator is a tool that enables you to migrate Oracle NoSQL tables from one data source to another. This tool can operate on tables in Oracle NoSQL Database Cloud Service, Oracle NoSQL Database on-premises and AWS S3. The Migrator tool supports several different data formats and physical media types. Supported data formats are JSON, Parquet, MongoDB-formatted JSON, DynamoDB-formatted JSON, and CSV files. Supported physical media types are files, OCI Object Storage, Oracle NoSQL Database on-premises, Oracle NoSQL Database Cloud Service and AWS S3.

This article has the following topics:

Related Topics

Overview

Oracle NoSQL Database Migrator lets you move Oracle NoSQL tables from one data source to another, such as Oracle NoSQL Database on-premises or cloud or even a simple JSON file.

There can be many situations that require you to migrate NoSQL tables from or to an Oracle NoSQL Database. For instance, a team of developers enhancing a NoSQL Database application may want to test their updated code in the local Oracle NoSQL Database Cloud Service (NDCS) instance using cloudsim. To verify all the possible test cases, they must set up the test data similar to the actual data. To do this, they must copy the NoSQL tables from the production environment to their local NDCS instance, the cloudsim environment. In another situation, NoSQL developers may need to move their application data from on-premise to the cloud and vice-versa, either for development or testing.

In all such cases and many more, you can use Oracle NoSQL Database Migrator to move your NoSQL tables from one data source to another, such as Oracle NoSQL Database on-premise or cloud or even a simple JSON file. You can also copy NoSQL tables from a MongoDB-formatted JSON input file, DynamoDB-formatted JSON input file (either stored in AWS S3 source or from files), or a CSV file into your NoSQL Database on-premises or cloud.

As depicted in the following figure, the NoSQL Database Migrator utility acts as a connector or pipe between the data source and the target (referred to as the sink). In essence, this utility exports data from the selected source and imports that data into the sink. This tool is table-oriented, that is, you can move the data only at the table level. A single migration task operates on a single table and supports migration of table data from source to sink in various data formats.

Oracle NoSQL Database Migrator is designed such that it can support additional sources and sinks in the future. For a list of sources and sinks supported by Oracle NoSQL Database Migrator as of the current release, see Supported Sources and Sinks.

Description of migrator_overview.eps follows
Description of the illustration migrator_overview.eps

Terminology used with Oracle NoSQL Database Migrator

Learn about the different terms used in the above diagram, in detail.

  • Source: An entity from where the NoSQL tables are exported for migration. Some examples of sources are Oracle NoSQL Database on-premise or cloud, JSON file, MongoDB-formatted JSON file, DynamoDB-formatted JSON file, and CSV files.
  • Sink: An entity that imports the NoSQL tables from NoSQL Database Migrator. Some examples for sinks are Oracle NoSQL Database on-premise or cloud and JSON file.

The NoSQL Database Migrator tool supports different types of sources and sinks (that is physical media or repositories of data) and data formats (that is how the data is represented in the source or sink). Supported data formats are JSON, Parquet, MongoDB-formatted JSON, DynamoDB-formatted JSON, and CSV files. Supported source and sink types are files, OCI Object Storage, Oracle NoSQL Database on-premise, and Oracle NoSQL Database Cloud Service.

  • Migration Pipe: The data from a source will be transferred to the sink by NoSQL Database Migrator. This can be visualized as a Migration Pipe.
  • Transformations: You can add rules to modify the NoSQL table data in the migration pipe. These rules are called Transformations. Oracle NoSQL Database Migrator allows data transformations at the top-level fields or columns only. It does not let you transform the data in the nested fields. Some examples of permitted transformations are:
    • Drop or ignore one or more columns,
    • Rename one or more columns, or
    • Aggregate several columns into a single field, typically a JSON field.
  • Configuration File : A configuration file is where you define all the parameters required for the migration activity in a JSON format. Later, you pass this configuration file as a single parameter to the runMigrator command from the CLI. A typical configuration file format looks like as shown below.
    {
 "source": {
   "type" : <source type>,
   //source-configuration for type. See Source Configuration Templates  .
 },
 "sink": {
   "type" : <sink type>,
   //sink-configuration for type. See Sink Configuration Templates  .
 },
 "transforms" : {
   //transforms configuration. See Transformation Configuration Templates  .
 },
 "migratorVersion" : "<migrator version>",
 "abortOnError" : <true|false>
}
    Group Parameters Mandatory (Y/N) Purpose Supported Values
    source type Y Represents the source from which to migrate the data. The source provides data and metadata (if any) for migration. To know the type value for each source, see Supported Sources and Sinks.
    source source-configuration for type Y Defines the configuration for the source. These configuration parameters are specific to the type of source selected above. See Source Configuration Templates . for the complete list of configuration parameters for each source type.
    sink type Y Represents the sink to which to migrate the data. The sink is the target or destination for the migration. To know the type value for each source, see Supported Sources and Sinks.
    sink sink-configuration for type Y Defines the configuration for the sink. These configuration parameters are specific to the type of sink selected above. See Sink Configuration Templates for the complete list of configuration parameters for each sink type.
    transforms transforms configuration N Defines the transformations to be applied to the data in the migration pipe. See Transformation Configuration Templates for the complete list of transformations supported by the NoSQL Data Migrator.
    - migratorVersion N Version of the NoSQL Data Migrator -
    - abortOnError N

    Specifies whether to stop the migration activity in case of any error or not.

    The default value is true indicating that the migration stops whenever it encounters a migration error.

    If you set this value to false, the migration continues even in case of failed records or other migration errors. The failed records and migration errors will be logged as WARNINGs on the CLI terminal.

    		true, false

    Note:

    As JSON file is case-sensitive, all the parameters defined in the configuration file are case-sensitive unless specified otherwise.

Supported Sources and Sinks

This topic provides the list of the sources and sinks supported by the Oracle NoSQL Database Migrator.

You can use any combination of a valid source and sink from this table for the migration activity. However, you must ensure that at least one of the ends, that is, source or sink must be an Oracle NoSQL product. You can not use the NoSQL Database Migrator to move the NoSQL table data from one file to another.

Type
(value)

Format
(value)

Valid Source Valid Sink

Oracle NoSQL Database
(nosqldb)

NA Y Y

Oracle NoSQL Database Cloud Service
(nosqldb_cloud)

NA Y Y

File system
(file)

JSON
(json)

Y Y

MongoDB JSON
(mongodb_json)

Y N

DynamoDB JSON
(dynamodb_json)

Y N

Parquet(parquet)

N Y

CSV
(csv)

Y N

OCI Object Storage
(object_storage_oci)

JSON
(json)

Y Y

MongoDB JSON
(mongodb_json)

Y N

Parquet(parquet)

N Y

CSV
(csv)

Y N
AWS S3

DynamoDB JSON
(dynamodb_json)

Y N

Note:

Many configuration parameters are common across the source and sink configuration. For ease of reference, the description for such parameters is repeated for each source and sink in the documentation sections, which explain configuration file formats for various types of sources and sinks. In all the cases, the syntax and semantics of the parameters with the same name are identical.

Source and Sink Security

Some of the source and sink types have optional or mandatory security information for authentication purposes.

All sources and sinks that use services in the Oracle Cloud Infrastructure (OCI) can use certain parameters for providing optional security information. This information can be provided using an OCI configuration file or Instance Principal.

Oracle NoSQL Database sources and sinks require mandatory security information if the installation is secure and uses an Oracle Wallet-based authentication. This information can be provided by adding a jar file to the <MIGRATOR_HOME>/lib directory.

Wallet-based Authentication

If an Oracle NoSQL Database installation uses Oracle Wallet-based authentication, you need an additional jar file that is part of the EE installation. For more information, see Oracle Wallet.

Without this jar file, you will get the following error message:

Could not find kvstore-ee.jar in lib directory. Copy kvstore-ee.jar to lib directory.

To prevent the exception shown above, you must copy the kvstore-ee.jar file from your EE server package to the <MIGRATOR_HOME>/lib directory. <MIGRATOR_HOME> is the nosql-migrator-M.N.O/ directory created by extracting the Oracle NoSQL Database Migrator package and M.N.O represent the software release.major.minor numbers. For example, nosql-migrator-1.1.0/lib.

Note:

The wallet-based authentication is supported ONLY in the Enterprise Edition (EE) of Oracle NoSQL Database.

Authenticating with Instance Principals

Instance principals is an IAM service feature that enables instances to be authorized actors (or principals) that can perform actions on service resources. Each compute instance has its own identity, and it authenticates using the certificates added to it.

Oracle NoSQL Database Migrator provides an option to connect to a NoSQL cloud and OCI Object Storage sources and sinks using instance principal authentication. It is only supported when the NoSQL Database Migrator tool is used within an OCI compute instance, for example, the NoSQL Database Migrator tool running in a VM hosted on OCI. To enable this feature use the useInstancePrincipal attribute of the NoSQL cloud source and sink configuration file. For more information on configuration parameters for different types of sources and sinks, see Source Configuration Templates and Sink Configuration Templates .

For more information on instance principals, see Calling Services from an Instance.

Workflow for Oracle NoSQL Database Migrator

Learn about the various steps involved in using the Oracle NoSQL Database Migrator utility for migrating your NoSQL data.

The high level flow of tasks involved in using NoSQL Database Migrator is depicted in the below figure.


Description of migrator_flow.eps follows
Description of the illustration migrator_flow.eps

Download the NoSQL Data Migrator Utility

The Oracle NoSQL Database Migrator utility is available for download from the Oracle NoSQL Downloads page. Once you download and unzip it on your machine, you can access the runMigrator command from the command line interface.

Note:

Oracle NoSQL Database Migrator utility requires Java 11 or higher versions to run.

Identify the Source and Sink

Before using the migrator, you must identify the data source and sink. For instance, if you want to migrate a NoSQL table from Oracle NoSQL Database on-premise to a JSON formatted file, your source will be Oracle NoSQL Database and sink will be JSON file. Ensure that the identified source and sink are supported by the Oracle NoSQL Database Migrator by referring to Supported Sources and Sinks. This is also an appropriate phase to decide the schema for your NoSQL table in the target or sink, and create them.
  • Identify Sink Table Schema: If the sink is Oracle NoSQL Database on-premise or cloud, you must identify the schema for the sink table and ensure that the source data matches with the target schema. If required, use transformations to map the source data to the sink table.
    • Default Schema: NoSQL Database Migrator provides an option to create a table with the default schema without the need to predefine the schema for the table. This is useful primarily when loading JSON source files into Oracle NoSQL Database.
      If the source is a MongoDB-formatted JSON file, the default schema for the table will be as follows:
      CREATE TABLE IF NOT EXISTS <tablename>(ID STRING, DOCUMENT JSON,PRIMARY KEY(SHARD(ID))

      Where:

      — tablename = value provided for the table attribute in the configuration.

      — ID = _id value from each document of the mongoDB exported JSON source file.

      — DOCUMENT = For each document in the mongoDB exported file, the contents excluding the _id field are aggregated into the DOCUMENT column.

      If the source is a DynamoDB-formatted JSON file, the default schema for the table will be as follows:
      CREATE TABLE IF NOT EXISTS <TABLE_NAME>(DDBPartitionKey_name DDBPartitionKey_type, 
[DDBSortKey_name DDBSortKey_type],DOCUMENT JSON,
PRIMARY KEY(SHARD(DDBPartitionKey_name),[DDBSortKey_name]))

      Where:

      — TABLE_NAME = value provided for the sink table in the configuration

      — DDBPartitionKey_name = value provided for the partition key in the configuration

      — DDBPartitionKey_type = value provided for the data type of the partition key in the configuration

      — DDBSortKey_name = value provided for the sort key in the configuration if any

      — DDBSortKey_type = value provided for the data type of the sort key in the configuration if any

      — DOCUMENT = All attributes except the partition and sort key of a Dynamo DB table item aggregated into a NoSQL JSON column

      If the source format is a CSV file, a default schema is not supported for the target table. You can create a schema file with a table definition containing the same number of columns and data types as the source CSV file. For more details on the Schema file creation, see Providing Table Schema.

      For all the other sources, the default schema will be as follows:
      CREATE TABLE IF NOT EXISTS <tablename> (ID LONG GENERATED ALWAYS AS IDENTITY, DOCUMENT JSON, PRIMARY KEY(ID))

      Where:

      — tablename = value provided for the table attribute in the configuration.

      — ID = An auto-generated LONG value.

      — DOCUMENT = The JSON record provided by the source is aggregated into the DOCUMENT column.

      Note:

      If the _id value is not provided as a string in the MongoDB-formatted JSON file, NoSQL Database Migrator converts it into a string before inserting it into the default schema.
  • Providing Table Schema: NoSQL Database Migrator allows the source to provide schema definitions for the table data using schemaInfo attribute. The schemaInfo attribute is available in all the data sources that do not have an implicit schema already defined. Sink data stores can choose any one of the following options.
    • Use the default schema defined by the NoSQL Database Migrator.
    • Use the source-provided schema.
    • Override the source-provided schema by defining its own schema. For example, if you want to transform the data from the source schema to another schema, you need to override the source-provided schema and use the transformation capability of the NoSQL Database Migrator tool.

    Description of source_sink_schema_example.png follows
    Description of the illustration source_sink_schema_example.png

    The table schema file, for example, mytable_schema.ddl can include table DDL statements. The NoSQL Database Migrator tool executes this table schema file before starting the migration. The migrator tool supports no more than one DDL statement per line in the schema file. For example,
    CREATE TABLE IF NOT EXISTS(id INTEGER, name STRING, age INTEGER, PRIMARY KEY(SHARD(ID)))

    Note:

    Migration will fail if the table is present at the sink and the DDL in the schemaPath is different than the table.
  • Create Sink Table: Once you identify the sink table schema, create the sink table either through the Admin CLI or using the schemaInfo attribute of the sink configuration file. See Sink Configuration Templates .

    Note:

    If the source is a CSV file, create a file with the DDL commands for the schema of the target table. Provide the file path in schemaInfo.schemaPath parameter of the sink configuration file.

Migrating TTL Metadata for Table Rows

You can choose to include the TTL metadata for table rows along with the actual data when performing migration of NoSQL tables. The NoSQL Database Migrator provides a configuration parameter to support the export and import of table row TTL metadata. Additionally, the tool provides an option to select the relative expiry time for table rows during the import operation. You can optionally export or import TTL metadata using the includeTTL parameter.

Note:

The support for migrating TTL metadata for table rows is only available for Oracle NoSQL Database and Oracle NoSQL Database Cloud Service.

Exporting TTL metadata

When a table is exported, TTL data is exported for the table rows that have a valid expiration time. If a row does not expire, then it is not included explicitly in the exported data because its expiration value is always 0. TTL information is contained in the _metadata JSON object for each exported row. The NoSQL Database Migrator exports the expiration time for each row as the number of milliseconds since the UNIX epoch (Jan 1st, 1970). For example,
//Row 1
{
    "id" : 1,
    "name" : "xyz",
    "age" : 45,
    "_metadata" : {
        "expiration" : 1629709200000   //Row Expiration time in milliseconds
    }
}

//Row 2
{
    "id" : 2,
    "name" : "abc",
    "age" : 52,
    "_metadata" : {
        "expiration" : 1629709400000   //Row Expiration time in milliseconds
    }
}

//Row 3 No Metadata for below row as it will not expire
{
    "id" : 3,
    "name" : "def",
    "age" : 15
}

Importing TTL metadata

You can optionally import TTL metadata using a configuration parameter, includeTTL. The import operation handles the following use cases when migrating table rows containing TTL metadata. These use-cases are applicable only when the includeTTL configuration parameter is specified.

In the use-cases 2 and 3, the default Reference Time of import operation is the current time in milliseconds, obtained from System.currentTimeMillis(), of the machine where the NoSQL Database Migrator tool is running. But you can also set a custom Reference Time using the ttlRelativeDate configuration parameter if you want to extend the expiration time and import rows that would otherwise expire immediately.
  • Use-case 1: No TTL metadata information is present in the importing table row.

    When you import a JSON source file produced from an external source or exported using earlier versions of the migrator, the importing row does not have TTL information. But since the includeTTL configuration parameter is equal to true, the migrator set the TTL=0 for the table row, which means the importing table row never expires.

  • Use-case 2: TTL value of the source table row is expired relative to the Reference Time when the table row gets imported.

    When you export a table row to a file and try to import it after the expiration time of the table row, the table row is ignored and is not written into the store.

  • Use-case 3: TTL value of the source table row is not expired relative to the Reference Time when the table row gets imported.

    When you export a table row to a file and try to import it before the expiration time of the table row, the table row gets imported with a TTL value. But the new TTL value for the table row may not be equal to exported TTL value because of the integer hour and day window constraints in the TimeToLive class. For example,

    Exported table row
    {
  "id" : 8,
  "name" : "xyz",
  "_metadata" : {
    "expiration" : 1629709200000 //Monday, August 23, 2021 9:00:00 AM in UTC
  }
}

    The reference time while importing is 1629707962582, which is Monday, August 23, 2021 8:39:22.582 AM.

    Imported table row
    {
  "id" : 8,
  "name" : "xyz",
  "_metadata" : {
    "ttl" :  1629712800000 //Monday, August 23, 2021 10:00:00 AM UTC
  }
}

Importing data to a sink with an IDENTITY column

You can import the data from a valid source to a sink table (On-premises/Cloud Services) with an IDENTITY column. You create the IDENTITY column as either GENERATED ALWAYS AS IDENTITY or GENERATED BY DEFAULT AS IDENTITY. For more information on table creation with an IDENTITY column, see Creating Tables With an IDENTITY Column in the SQL Reference Guide.

Before importing the data, make sure that the Oracle NoSQL Database table at the sink is empty if it exists. If there is pre-existing data in the sink table, migration can lead to issues such as overwriting existing data in the sink table or skipping source data during the import.

Sink table with IDENTITY column as GENERATED ALWAYS AS IDENTITY

Consider a sink table with the IDENTITY column created as GENERATED ALWAYS AS IDENTITY. The data import is dependent on whether or not the source supplies the values to the IDENTITY column and ignoreFields transformation parameter in the configuration file.

For example, you want to import data from a JSON file source to the Oracle NoSQL Database table as the sink. The schema of the sink table is: 

CREATE TABLE IF NOT EXISTS migrateID(ID INTEGER GENERATED ALWAYS AS IDENTITY, name STRING, course STRING, PRIMARY KEY
      (ID))
The Migrator utility handles the data migration as described in the following cases:
Source condition User action Migration outcome

CASE 1: Source data does not supply a value for the IDENTITY field of the sink table.

Example: JSON source file sample_noID.json

{"name":"John", "course":"Computer Science"}
{"name":"Jane", "course":"BioTechnology"}
{"name":"Tony", "course":"Electronics"}

Create/generate the configuration file.

Data migration is successful. IDENTITY column values are auto-generated. Migrated data in Oracle NoSQL Database sink table migrateID: 

{"ID":1001,"name":"Jane","course":"BioTechnology"}
{"ID":1003,"name":"John","course":"Computer Science"}
{"ID":1002,"name":"Tony","course":"Electronics"}

CASE 2: Source data supplies values for the IDENTITY field of the sink table.

Example: JSON source file sampleID.json

{"ID":1, "name":"John", "course":"Computer Science"}
{"ID":2, "name":"Jane", "course":"BioTechnology"}
{"ID":3, "name":"Tony", "course":"Electronics"}

Create/generate the configuration file. You provide an ignoreFields transformation for the ID column in the sink configuration template.

"transforms" : { "ignoreFields" : ["ID"] }

Data migration is successful. The supplied ID values are skipped and the IDENTITY column values are auto-generated.

Migrated data in Oracle NoSQL Database sink table migrateID:
{"ID":2003,"name":"John","course":"Computer Science"}
{"ID":2002,"name":"Tony","course":"Electronics"}
{"ID":2001,"name":"Jane","course":"BioTechnology"}

You create/generate the configuration file without the ignoreFields transformation for the IDENTITY column.

Data migration fails with the following error message:

"Cannot set value for a generated always identity column".

For more details on the transformation configuration parameters, see the topic Transformation Configuration Templates.

Sink table with IDENTITY column as GENERATED BY DEFAULT AS IDENTITY

Consider a sink table with the IDENTITY column created as GENERATED BY DEFAULT AS IDENTITY. The data import is dependent on whether or not the source supplies the values to the IDENTITY column and ignoreFields transformation parameter.

For example, you want to import data from a JSON file source to the Oracle NoSQL Database table as the sink. The schema of the sink table is: 

CREATE TABLE IF NOT EXISTS migrateID(ID INTEGER GENERATED BY DEFAULT AS IDENTITY, name STRING, course STRING, PRIMARY KEY
      (ID))
The Migrator utility handles the data migration as described in the following cases:
Source condition User action Migration outcome

CASE 1: Source data does not supply a value for the IDENTITY field of the sink table.

Example: JSON source file sample_noID.json

{"name":"John", "course":"Computer Science"}
{"name":"Jane", "course":"BioTechnology"}
{"name":"Tony", "course":"Electronics"}

Create/generate the configuration file.
Data migration is successful. IDENTITY column values are auto-generated. Migrated data in Oracle NoSQL Database sink table migrateID:
{"ID":1,"name":"John","course":"Computer Science"}
{"ID":2,"name":"Jane","course":"BioTechnology"}
{"ID":3,"name":"Tony","course":"Electronics"}

CASE 2: Source data supplies values for the IDENTITY field of the sink table and it is a Primary Key field.

Example: JSON source file sampleID.json

{"ID":1, "name":"John", "course":"Computer Science"}
{"ID":2, "name":"Jane", "course":"BioTechnology"}
{"ID":3, "name":"Tony", "course":"Electronics"}

Create/generate the configuration file. You provide an ignoreFields transformation for the ID column in the sink configuration template (Recommended).

"transforms" : { "ignoreFields" : ["ID"] }

Data migration is successful. The supplied ID values are skipped and the IDENTITY column values are auto-generated.

Migrated data in Oracle NoSQL Database sink table migrateID:
{"ID":1002,"name":"John","course":"Computer Science"}
{"ID":1001,"name":"Jane","course":"BioTechnology"}
{"ID":1003,"name":"Tony","course":"Electronics"}

You create/generate the configuration file without the ignoreFields transformation for the IDENTITY column.

Data migration is successful. The supplied ID values from the source are copied into the ID column in the sink table.

When you try to insert an additional row to the table without supplying an ID value, the sequence generator tries to auto-generate the ID value. The sequence generator's starting value is 1. As a result, the generated ID value can potentially duplicate one of the existing ID values in the sink table. Since this is a violation of the primary key constraint, an error is returned and the row does not get inserted.

See Sequence Generator for additional information.

To avoid the primary key constraint violation, the sequence generator must start the sequence with a value that does not conflict with existing ID values in the sink table. To use the START WITH attribute to make this modification, see the example below:

Example: Migrated data in Oracle NoSQL Database sink table migrateID:
{"ID":1,"name":"John","course":"Computer Science"}
{"ID":2,"name":"Jane","course":"BioTechnology"}
{"ID":3,"name":"Tony","course":"Electronics"}
To find the appropriate value for the sequence generator to insert in the ID column, fetch the maximum value of the ID field using the following query:
SELECT max(ID) FROM migrateID
Output: 
{"Column_1":3}
The maximum value of the ID column in the sink table is 3. You want the sequence generator to start generating the ID values beyond 3 to avoid duplication. You update the sequence generator's START WITH attribute to 4 using the following statement:
ALTER Table migrateID (MODIFY ID GENERATED BY DEFAULT AS IDENTITY (START WITH 4))

This will start the sequence at 4.

Now when you insert rows to the sink table without supplying the ID values, the sequence generator auto-generates the ID values from 4 onwards averting the duplication of the IDs.

For more details on the transformation configuration parameters, see the topic Transformation Configuration Templates.

Run the runMigrator command

The runMigrator executable file is available in the extracted NoSQL Database Migrator files. You must install Java 11 or higher version and bash on your system to successfully run the runMigrator command.

You can run the runMigrator command in two ways:
  1. By creating the configuration file using the runtime options of the runMigrator command as shown below. 
    [~]$ ./runMigrator
configuration file is not provided. Do you want to generate configuration? (y/n)                                                                              
 
[n]: y
...
...
    • When you invoke the runMigrator utility, it provides a series of runtime options and creates the configuration file based on your choices for each option.
    • After the utility creates the configuration file, you have a choice to either proceed with the migration activity in the same run or save the configuration file for a future migration.
    • Irrespective of your decision to proceed or defer the migration activity with the generated configuration file, the file will be available for edits or customization to meet your future requirements. You can use the customized configuration file for migration later.
  2. By passing a manually created configuration file (in the JSON format) as a runtime parameter using the -c or --config option. You must create the configuration file manually before running the runMigrator command with the -c or --config option. For any help with the source and sink configuration parameters, see Oracle NoSQL Database Migrator Reference.
    [~]$ ./runMigrator -c </path/to/the/configuration/json/file>

Logging Migrator Progress

NoSQL Database Migrator tool provides options, which enables trace, debugging, and progress messages to be printed to standard output or to a file. This option can be useful in tracking the progress of migration operation, particularly for very large tables or data sets.

  • Log Levels

    To control the logging behavior through the NoSQL Database Migrator tool, pass the --log-level or -l run time parameter to the runMigrator command. You can specify the amount of log information to write by passing the appropriate log level value. 

    $./runMigrator --log-level <loglevel>
    Example:
    $./runMigrator --log-level debug

    Table - Supported Log Levels for NoSQL Database Migrator

    Log Level Description
    warning Prints errors and warnings.
    info (default) Prints the progress status of data migration such as validating source, validating sink, creating tables, and count of number of data records migrated.
    debug Prints additional debug information.
    all Prints everything. This level turns on all levels of logging.
  • Log File:
    You can specify the name of the log file using --log-file or -f parameter. If --log-file is passed as run time parameter to the runMigrator command, the NoSQL Database Migrator writes all the log messages to the file else to the standard output.
    $./runMigrator --log-file <log file name>
    Example:
    $./runMigrator --log-file nosql_migrator.log

Use Case Demonstrations for Oracle NoSQL Database Migrator

Learn how to perform data migration using the Oracle NoSQL Database Migrator for specific use cases. You can find detailed systematic instructions with code examples to perform migration in each of the use cases.

This article has the following topics:

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.
    [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:
  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 parameters.
    [~/nosqlMigrator/nosql-migrator-1.0.0]$./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/NDCS2JSON
Select the source: 
1) nosqldb
2) nosqldb_cloud
3) file
4) object_storage_oci
5) aws_s3
#? 2

Configuration for source type=nosqldb_cloud
Enter endpoint URL or region ID of the Oracle NoSQL Database Cloud: us-phoenix-1
Select the authentication type: 
1) credentials_file
2) instance_principal
3) delegation_token
#? 1
Enter path to the file containing OCI credentials [/home/<user>/.oci/config]:
Enter the profile name in OCI credentials file [DEFAULT]: 
Enter the compartment name or id of the table []: developers
Enter table name: myTable
Include TTL data? If you select 'yes' TTL of rows will also 
be included in the exported data.(y/n) [n]: 
Enter percentage of table read units to be used for migration operation. (1-100) [90]:
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
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
  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_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"
}
  9. 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]:
  10. 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)))

Migrate from Oracle NoSQL Database On-Premise to Oracle NoSQL Database Cloud Service

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 Oracle NoSQL Database Cloud Service (NDCS).

Use Case

As a developer, you are exploring options to avoid the overhead of managing the resources, clusters, and garbage collection for your existing NoSQL Database KVStore workloads. As a solution, you decide to migrate your existing on-premise KVStore workloads to Oracle NoSQL Database Cloud Service because NDCS manages them automatically.

Example

For the demonstration, let us look at how to migrate the data and schema definition of a NoSQL table called myTable from the NoSQL Database KVStore to NDCS. We will also use this use case to show how to run the runMigrator utility by passing a precreated configuration file.
Prerequisites
  • Identify the source and sink for the migration.
    • Source: Oracle NoSQL Database
    • Sink: Oracle NoSQL Database Cloud Service
  • 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
  • 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 NoSQL Database KVStore to NDCS:
  1. Prepare the configuration file (in JSON format) with the identified Source and Sink details. See Source Configuration Templates and Sink Configuration Templates .
    {
  "source" : {
    "type" : "nosqldb",
    "storeName" : "kvstore",
    "helperHosts" : ["<hostname>:5000"],
    "table" : "myTable",
    "requestTimeoutMs" : 5000
  },
  "sink" : {
    "type" : "nosqldb_cloud",
    "endpoint" : "us-phoenix-1",
    "table" : "myTable",
    "compartment" : "developers",
    "schemaInfo" : {
      "schemaPath" : "<complete/path/to/the/JSON/file/with/DDL/commands/for/the/schema/definition>",
      "readUnits" : 100,
      "writeUnits" : 100,
      "storageSize" : 1
    },
    "credentials" : "<complete/path/to/oci/config/file>",
    "credentialsProfile" : "DEFAULT",
    "writeUnitsPercent" : 90,
    "requestTimeoutMs" : 5000
  },
  "abortOnError" : true,
  "migratorVersion" : "1.0.0"
}
  2. Open the command prompt and navigate to the directory where you extracted the NoSQL Database Migrator utility.
  3. Run the runMigrator command by passing the configuration file using the --config or -c option.
    [~/nosqlMigrator/nosql-migrator-1.0.0]$./runMigrator --config <complete/path/to/the/JSON/config/file>
  4. The utility proceeds with the data migration, as shown below.
    Records provided by source=10, Records written to sink=10, Records failed=0.
Elapsed time: 0min 10sec 426ms
Migration completed.
Validation

To validate the migration, you can login to your NDCS console and verify that myTable is created with the source data.

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:
  1. 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"
}
  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.5.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:
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 - Oracle NoSQL Database Cloud Service Console Tables


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

Figure - Oracle NoSQL Database Cloud Service Console Table Data


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

Migrate from MongoDB JSON file to an Oracle NoSQL Database Cloud Service

This example shows how to use the Oracle NoSQL Database Migrator to copy Mongo-DB Formatted Data to the Oracle NoSQL Database Cloud Service (NDCS).

Use Case

After evaluating multiple options, an organization finalizes Oracle NoSQL Database Cloud Service as its NoSQL Database platform. As its NoSQL tables and data are in MongoDB, they are looking for a way to migrate those tables and data to Oracle NDCS.

You can copy a file or directory containing the MongoDB exported JSON data for migration by specifying the file or directory in the source configuration template.

A sample MongoDB-formatted JSON File is as follows:
{"_id":0,"name":"Aimee Zank","scores":[{"score":1.463179736705023,"type":"exam"},{"score":11.78273309957772,"type":"quiz"},{"score":35.8740349954354,"type":"homework"}]}
{"_id":1,"name":"Aurelia Menendez","scores":[{"score":60.06045071030959,"type":"exam"},{"score":52.79790691903873,"type":"quiz"},{"score":71.76133439165544,"type":"homework"}]}
{"_id":2,"name":"Corliss Zuk","scores":[{"score":67.03077096065002,"type":"exam"},{"score":6.301851677835235,"type":"quiz"},{"score":66.28344683278382,"type":"homework"}]}
{"_id":3,"name":"Bao Ziglar","scores":[{"score":71.64343899778332,"type":"exam"},{"score":24.80221293650313,"type":"quiz"},{"score":42.26147058804812,"type":"homework"}]}
{"_id":4,"name":"Zachary Langlais","scores":[{"score":78.68385091304332,"type":"exam"},{"score":90.2963101368042,"type":"quiz"},{"score":34.41620148042529,"type":"homework"}]}

MongoDB supports two types of extensions to the JSON format of files, Canonical mode and Relaxed mode. You can supply the MongoDB-formatted JSON file that is generated using the mongoexport tool in either Canonical or Relaxed mode. Both the modes are supported by the NoSQL Database Migrator for migration.

For more information on the MongoDB Extended JSON (v2) file, See mongoexport_formats.

For more information on the generation of MongoDB-formatted JSON file, See mongoexport.

Example

For the demonstration, let us look at how to migrate a MongoDB-formatted JSON file to NDCS. We will use a manually created configuration file for this example.
Prerequisites
  • Identify the source and sink for the migration.
    • Source: MongoDB-Formatted JSON File
    • Sink: Oracle NoSQL Database Cloud Service
  • Extract the data from Mongo DB using the mongoexport utility. See mongoexport for more information.
  • Create a NoSQL table in the sink with a table schema that matches the data in the Mongo-DB-formatted JSON file. As an alternative, you can instruct the NoSQL Database Migratorto create a table with the default schema structure by setting the defaultSchema attribute to true.

    Note:

    For a MongoDB-Formatted JSON source, the default schema for the table will be as:
    CREATE TABLE IF NOT EXISTS <tablename>(ID STRING, DOCUMENT JSON,PRIMARY KEY(SHARD(ID))
    Where:
    • tablename = value of the table config.
    • ID = _id value from the mongoDB exported JSON source file.
    • DOCUMENT = The entire contents of the mongoDB exported JSON source file is aggregated into the DOCUMENT column excluding the _id field.
  • 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 MongoDB-formatted JSON data to the Oracle NoSQL Database Cloud Service:

  1. 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" : "mongodb_json",
    "dataPath" : "<complete/path/to/the/MongoDB/Formatted/JSON/file>"
  },
  "sink" : {
    "type" : "nosqldb_cloud",
    "endpoint" : "us-phoenix-1",
    "table" : "mongoImport",
    "compartment" : "developers",
    "schemaInfo" : {
      "defaultSchema" : true,
      "readUnits" : 100,
      "writeUnits" : 60,
      "storageSize" : 1
    },
    "credentials" : "<complete/path/to/the/oci/config/file>",
    "credentialsProfile" : "DEFAULT",
    "writeUnitsPercent" : 90,
    "requestTimeoutMs" : 5000
  },
  "abortOnError" : true,
  "migratorVersion" : "1.0.0"
}
  2. Open the command prompt and navigate to the directory where you extracted the NoSQL Database Migrator utility.
  3. Run the runMigrator command by passing the configuration file using the --config or -c option.
    [~/nosqlMigrator/nosql-migrator-1.0.0]$./runMigrator --config <complete/path/to/the/JSON/config/file>
  4. The utility proceeds with the data migration, as shown below.
    Records provided by source=29,353, Records written to sink=29,353, Records failed=0.
Elapsed time: 9min 9sec 630ms
Migration completed.
Validation

To validate the migration, you can login to your NDCS console and verify that myTable is created with the source data.

Migrate from DynamoDB JSON file to Oracle NoSQL Database

This example shows how to use the Oracle NoSQL Database Migrator to copy DynamoDB JSON file to Oracle NoSQL Database.

Use Case:

After evaluating multiple options, an organization finalizes Oracle NoSQL Database over DynamoDB database. The organization wants to migrate their tables and data from DynamoDB to Oracle NoSQL Database (On-premises).

See Mapping of DynamoDB table to Oracle NoSQL table for more details.

You can migrate a file or directory containing the DynamoDB exported JSON data from a file system by specifying the path in the source configuration template.

A sample DynamoDB-formatted JSON File is as follows:
{"Item":{"Id":{"N":"101"},"Phones":{"L":[{"L":[{"S":"555-222"},{"S":"123-567"}]}]},"PremierCustomer":{"BOOL":false},"Address":{"M":{"Zip":{"N":"570004"},"Street":{"S":"21 main"},"DoorNum":{"N":"201"},"City":{"S":"London"}}},"FirstName":{"S":"Fred"},"FavNumbers":{"NS":["10"]},"LastName":{"S":"Smith"},"FavColors":{"SS":["Red","Green"]},"Age":{"N":"22"}}}
{"Item":{"Id":{"N":"102"},"Phones":{"L":[{"L":[{"S":"222-222"}]}]},"PremierCustomer":{"BOOL":false},"Address":{"M":{"Zip":{"N":"560014"},"Street":{"S":"32 main"},"DoorNum":{"N":"1024"},"City":{"S":"Wales"}}},"FirstName":{"S":"John"},"FavNumbers":{"NS":["10"]},"LastName":{"S":"White"},"FavColors":{"SS":["Blue"]},"Age":{"N":"48"}}}

You copy the exported DynamoDB table data from AWS S3 storage to a local mounted file system.

Example:

For this demonstration, you will learn how to migrate a DynamoDB JSON file to Oracle NoSQL Database(On-premises). You will use a manually created configuration file for this example.

Prerequisites

  • Identify the source and sink for the migration.
    • Source: DynamoDB JSON File
    • Sink: Oracle NoSQL Database (On-premises)
  • In order to import DynamoDB table data to Oracle NoSQL Database, you must first export the DynamoDB table to S3. Refer to steps provided in Exporting DynamoDB table data to Amazon S3 to export your table. While exporting, you select the format as DynamoDB JSON. The exported data contains DynamoDB table data in multiple gzip files as shown below.
    / 01639372501551-bb4dd8c3 
|-- 01639372501551-bb4dd8c3 ==> exported data prefix
|----data
|------sxz3hjr3re2dzn2ymgd2gi4iku.json.gz  ==>table data
|----manifest-files.json
|----manifest-files.md5
|----manifest-summary.json
|----manifest-summary.md5
|----_started
  • You must download the files from AWS s3. The structure of the files after the download will be as shown below.
    download-dir/01639372501551-bb4dd8c3     
|----data    
|------sxz3hjr3re2dzn2ymgd2gi4iku.json.gz  ==>table data   
|----manifest-files.json   
|----manifest-files.md5   
|----manifest-summary.json   
|----manifest-summary.md5   
|----_started

Procedure

To migrate the DynamoDB JSON data to the Oracle NoSQL Database:
  1. Prepare the configuration file (in JSON format) with the identified source and sink details.See Source Configuration Templates and Sink Configuration Templates .
    You can choose one of the following two options.
    • Option 1: Importing DynamoDB table a as JSON document using default schema config.
      Here the defaultSchema is TRUE and so the migrator creates the default schema at the sink. You need to specify the DDBPartitionKey and the corresponding NoSQL column type. Else an error is thrown.
      {
 "source" : {
   "type" : "file",
   "format" : "dynamodb_json",
   "dataPath" : "<complete/path/to/the/DynamoDB/Formatted/JSON/file>"
 },
 "sink" : {
    "type" : "nosqldb",
    "table" : "<table_name>",
    "storeName" : "kvstore",
    "helperHosts" : ["<hostname>:5000"]
    "schemaInfo" : {
       "defaultSchema" : true,    
       "DDBPartitionKey" : "<PrimaryKey:Datatype>",
     },  
  },
  "abortOnError" : true,
  "migratorVersion" : "1.0.0"
}
      For a DynamoDB JSON source, the default schema for the table will be as shown below:
      CREATE TABLE IF NOT EXISTS <TABLE_NAME>(DDBPartitionKey_name DDBPartitionKey_type, 
[DDBSortKey_name DDBSortKey_type], DOCUMENT JSON, 
PRIMARY KEY(SHARD(DDBPartitionKey_name),[DDBSortKey_name]))

      Where

      TABLE_NAME = value provided for the sink 'table' in the configuration

      DDBPartitionKey_name = value provided for the partiiton key in the configuration

      DDBPartitionKey_type = value provided for the data type of the partition key in the configuration

      DDBSortKey_name = value provided for the sort key in the configuration if any

      DDBSortKey_type = value provided for the data type of the sort key in the configuration if any

      DOCUMENT = All attributes except the partition and sort key of a Dynamo DB table item aggregated into a NoSQL JSON column

    • Option 2: Importing DynamoDB table as fixed columns using a user-supplied schema file.
      Here the defaultSchema is FALSE and you specify the schemaPath as a file containing your DDL statement. See Mapping of DynamoDB types to Oracle NoSQL types for more details.

      Note:

      If the Dynamo DB table has a data type that is not supported in NoSQL, the migration fails.
      A sample schema file is shown below.
      CREATE TABLE IF NOT EXISTS sampledynDBImp (AccountId INTEGER,document JSON, 
PRIMARY KEY(SHARD(AccountId)));
      The schema file is used to create the table at the sink as part of the migration. As long as the primary key data is provided, the input JSON record will be inserted, otherwise it throws an error.

      Note:

      If the input data does not contain a value for a particular column(other than the primary key) then the column default value will be used. The default value should be part of the column definition while creating the table. For example id INTEGER not null default 0. If the column does not have a default definition then SQL NULL is inserted if no values are provided for the column. 
      {
  "source" : {
    "type" : "file",
    "format" : "dynamodb_json",
    "dataPath" : "<complete/path/to/the/DynamoDB/Formatted/JSON/file>"
  },
  "sink" : {
    "type" : "nosqldb",
    "table" : "<table_name>",
    "schemaInfo" : {
      "defaultSchema" : false,
      "readUnits" : 100,
      "writeUnits" : 60,
      "schemaPath" : "<full path of the schema file with the DDL statement>",
      "storageSize" : 1
    },
    "storeName" : "kvstore",
    "helperHosts" : ["<hostname>:5000"]
  },
  "abortOnError" : true,
  "migratorVersion" : "1.0.0"
}
  2. Open the command prompt and navigate to the directory where you extracted the NoSQL Database Migrator utility.
  3. Run the runMigrator command by passing the configuration file using the --config or -c option.
    [~/nosqlMigrator/nosql-migrator-1.0.0]$./runMigrator 
--config <complete/path/to/the/JSON/config/file>
  4. The utility proceeds with the data migration, as shown below.
    Records provided by source=7..,
Records written to sink=7,
Records failed=0,
Records skipped=0.
Elapsed time: 0 min 2sec 50ms
Migration completed.

Validation

Start the SQL prompt in your KVStore.
 java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore
Verify that the new table is created with the source data:
desc <table_name>
SELECT * from <table_name>

Migrate from DynamoDB JSON file in AWS S3 to an Oracle NoSQL Database Cloud Service

This example shows how to use the Oracle NoSQL Database Migrator to copy DynamoDB JSON file stored in an AWS S3 store to the Oracle NoSQL Database Cloud Service (NDCS).

Use Case:

After evaluating multiple options, an organization finalizes Oracle NoSQL Database Cloud Service over DynamoDB database. The organization wants to migrate their tables and data from DynamoDB to Oracle NoSQL Database Cloud Service.

See Mapping of DynamoDB table to Oracle NoSQL table for more details.

You can migrate a file containing the DynamoDB exported JSON data from the AWS S3 storage by specifying the path in the source configuration template.

A sample DynamoDB-formatted JSON File is as follows:
{"Item":{"Id":{"N":"101"},"Phones":{"L":[{"L":[{"S":"555-222"},{"S":"123-567"}]}]},"PremierCustomer":{"BOOL":false},"Address":{"M":{"Zip":{"N":"570004"},"Street":{"S":"21 main"},"DoorNum":{"N":"201"},"City":{"S":"London"}}},"FirstName":{"S":"Fred"},"FavNumbers":{"NS":["10"]},"LastName":{"S":"Smith"},"FavColors":{"SS":["Red","Green"]},"Age":{"N":"22"}}}
{"Item":{"Id":{"N":"102"},"Phones":{"L":[{"L":[{"S":"222-222"}]}]},"PremierCustomer":{"BOOL":false},"Address":{"M":{"Zip":{"N":"560014"},"Street":{"S":"32 main"},"DoorNum":{"N":"1024"},"City":{"S":"Wales"}}},"FirstName":{"S":"John"},"FavNumbers":{"NS":["10"]},"LastName":{"S":"White"},"FavColors":{"SS":["Blue"]},"Age":{"N":"48"}}}

You export the DynamoDB table to AWS S3 storage as specified in Exporting DynamoDB table data to Amazon S3.

Example:

For this demonstration, you will learn how to migrate a DynamoDB JSON file in an AWS S3 source to NDCS. You will use a manually created configuration file for this example.

Prerequisites

  • Identify the source and sink for the migration.
    • Source: DynamoDB JSON File in AWS S3
    • Sink: Oracle NoSQL Database Cloud Service
  • Identify the table in AWS DynamoDB that needs to be migrated to NDCS. Login to your AWS console using your credentials. Go to DynamoDB. Under Tables, choose the table to be migrated.
  • Create an object bucket and export the table to S3. From your AWS console, go to S3. Under buckets, create a new object bucket. Go back to DynamoDB and click Exports to S3. Provide the source table and the destination S3 bucket and click Export.
    Refer to steps provided in Exporting DynamoDB table data to Amazon S3 to export your table. While exporting, you select the format as DynamoDB JSON. The exported data contains DynamoDB table data in multiple gzip files as shown below.
    / 01639372501551-bb4dd8c3 
|-- 01639372501551-bb4dd8c3 ==> exported data prefix
|----data
|------sxz3hjr3re2dzn2ymgd2gi4iku.json.gz  ==>table data
|----manifest-files.json
|----manifest-files.md5
|----manifest-summary.json
|----manifest-summary.md5
|----_started
  • You need aws credentials (including access key ID and secret access key) and config files (credentials and optionally config) to access AWS S3 from the migrator. See Set and view configuration settings for more details on the configuration files. See Creating a key pair for more details on creating access keys.
  • Identify your OCI cloud credentials and capture them in the OCI config file. Save the config file in a directory .oci under your home directory (~/.oci/config). See Acquiring Credentials for more details. 
    [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. For example,
    • endpoint: us-phoenix-1
    • compartment: developers

Procedure

To migrate the DynamoDB JSON data to the Oracle NoSQL Database:
  1. Prepare the configuration file (in JSON format) with the identified source and sink details. See Source Configuration Templates and Sink Configuration Templates .
    You can choose one of the following two options.
    • Option 1: Importing DynamoDB table a as JSON document using default schema config.
      Here the defaultSchema is TRUE and so the migrator creates the default schema at the sink. You need to specify the DDBPartitionKey and the corresponding NoSQL column type. Else an error is thrown.
      {
 "source" : {
   "type" : "aws_s3",
   "format" : "dynamodb_json",
   "s3URL" : "<https://<bucket-name>.<s3_endpoint>/export_path>",
   "credentials" : "</path/to/aws/credentials/file>",
   "credentialsProfile" : <"profile name in aws credentials file">
 },
 "sink" : {
   "type" : "nosqldb_cloud",
   "endpoint" : "<region_name>",
   "table" : "<table_name>",
   "compartment" : "<compartment_name>",
   "schemaInfo" : {
      "defaultSchema" : true,
      "readUnits" : 100,
      "writeUnits" : 60,
      "DDBPartitionKey" : "<PrimaryKey:Datatype>",
      "storageSize" : 1
   },
   "credentials" : "<complete/path/to/the/oci/config/file>",
   "credentialsProfile" : "DEFAULT",
   "writeUnitsPercent" : 90,
   "requestTimeoutMs" : 5000
 },
 "abortOnError" : true,
 "migratorVersion" : "1.0.0"
}
      For a DynamoDB JSON source, the default schema for the table will be as shown below:
      CREATE TABLE IF NOT EXISTS <TABLE_NAME>(DDBPartitionKey_name DDBPartitionKey_type, 
[DDBSortKey_name DDBSortKey_type], DOCUMENT JSON, 
PRIMARY KEY(SHARD(DDBPartitionKey_name),[DDBSortKey_name]))

      Where

      TABLE_NAME = value provided for the sink 'table' in the configuration

      DDBPartitionKey_name = value provided for the partiiton key in the configuration

      DDBPartitionKey_type = value provided for the data type of the partition key in the configuration

      DDBSortKey_name = value provided for the sort key in the configuration if any

      DDBSortKey_type = value provided for the data type of the sort key in the configuration if any

      DOCUMENT = All attributes except the partition and sort key of a Dynamo DB table item aggregated into a NoSQL JSON column

    • Option 2: Importing DynamoDB table as fixed columns using a user-supplied schema file.
      Here the defaultSchema is FALSE and you specify the schemaPath as a file containing your DDL statement. See Mapping of DynamoDB types to Oracle NoSQL types for more details.

      Note:

      If the Dynamo DB table has a data type that is not supported in NoSQL, the migration fails.
      A sample schema file is shown below.
      CREATE TABLE IF NOT EXISTS sampledynDBImp (AccountId INTEGER,document JSON, 
PRIMARY KEY(SHARD(AccountId)));
      The schema file is used to create the table at the sink as part of the migration. As long as the primary key data is provided, the input JSON record will be inserted, otherwise it throws an error.

      Note:

      If the input data does not contain a value for a particular column(other than the primary key) then the column default value will be used. The default value should be part of the column definition while creating the table. For example id INTEGER not null default 0. If the column does not have a default definition then SQL NULL is inserted if no values are provided for the column. 
      {
 "source" : {
   "type" : "aws_s3",
   "format" : "dynamodb_json",
   "s3URL" : "<https://<bucket-name>.<s3_endpoint>/export_path>",
   "credentials" : "</path/to/aws/credentials/file>",
   "credentialsProfile" : <"profile name in aws credentials file">
 },
 "sink" : {
   "type" : "nosqldb_cloud",
   "endpoint" : "<region_name>",
   "table" : "<table_name>",
   "compartment" : "<compartment_name>",
   "schemaInfo" : {
      "defaultSchema" : false,
      "readUnits" : 100,
      "writeUnits" : 60,
      "schemaPath" : "<full path of the schema file with the DDL statement>",
      "storageSize" : 1
   },
   "credentials" : "<complete/path/to/the/oci/config/file>",
   "credentialsProfile" : "DEFAULT",
   "writeUnitsPercent" : 90,
   "requestTimeoutMs" : 5000
 },
 "abortOnError" : true,
 "migratorVersion" : "1.0.0"
}
  2. Open the command prompt and navigate to the directory where you extracted the NoSQL Database Migrator utility.
  3. Run the runMigrator command by passing the configuration file using the --config or -c option.
    [~/nosqlMigrator/nosql-migrator-1.0.0]$./runMigrator 
--config <complete/path/to/the/JSON/config/file>
  4. The utility proceeds with the data migration, as shown below.
    Records provided by source=7..,
Records written to sink=7,
Records failed=0,
Records skipped=0.
Elapsed time: 0 min 2sec 50ms
Migration completed.

Validation

You can login to your NDCS console and verify that the new table is created with the source data.

Migrate between Oracle NoSQL Database Cloud Service regions

This example shows the usage of Oracle NoSQL Database Migrator to perform cross-region migration.

Use case

An organization uses Oracle NoSQL Database Cloud Service to store and manage its data. It decides to replicate data from an existing region to a newer region for testing purposes before the new region can be launched for the production environment.

In this use case, you will learn to use the NoSQL Database Migrator to copy data from the user_data table in the Ashburn region to the Phoenix region.

You run the runMigrator utility by passing a pre-created configuration file. If you don't provide the configuration file as a runtime parameter, the runMigrator utility prompts you to generate the configuration through an interactive procedure.

Prerequisites
  • Download Oracle NoSQL Database Migrator from the Oracle NoSQL Downloads page and extract the contents to your machine. For details, see Workflow for Oracle NoSQL Database Migrator.
  • Identify the source and sink for the migration.
    • Source: The user_data table in the Ashburn region.
      The user_data table includes the following data: 
      {"id":40,"firstName":"Joanna","lastName":"Smith","otherNames":[{"first":"Joanna","last":"Smart"}],"age":null,"income":75000,"address":{"city":"Houston","number":401,"phones":[{"area":null,"kind":"work","number":1618955},{"area":451,"kind":"home","number":4613341},{"area":481,"kind":"mobile","number":4613382}],"state":"TX","street":"Tex Ave","zip":95085},"connections":[70,30,40],"email":"joanna.smith123@reachmail.com","communityService":"**"}

{"id":10,"firstName":"John","lastName":"Smith","otherNames":[{"first":"Johny","last":"Good"},{"first":"Johny2","last":"Brave"},{"first":"Johny3","last":"Kind"},{"first":"Johny4","last":"Humble"}],"age":22,"income":45000,"address":{"city":"Santa Cruz","number":101,"phones":[{"area":408,"kind":"work","number":4538955},{"area":831,"kind":"home","number":7533341},{"area":831,"kind":"mobile","number":7533382}],"state":"CA","street":"Pacific Ave","zip":95008},"connections":[30,55,43],"email":"john.smith@reachmail.com","communityService":"****"}

{"id":20,"firstName":"Jane","lastName":"Smith","otherNames":[{"first":"Jane","last":"BeGood"}],"age":22,"income":55000,"address":{"city":"San Jose","number":201,"phones":[{"area":608,"kind":"work","number":6538955},{"area":931,"kind":"home","number":9533341},{"area":931,"kind":"mobile","number":9533382}],"state":"CA","street":"Atlantic Ave","zip":95005},"connections":[40,75,63],"email":"jane.smith201@reachmail.com","communityService":"*****"}

{"id":30,"firstName":"Adam","lastName":"Smith","otherNames":[{"first":"Adam","last":"BeGood"}],"age":45,"income":75000,"address":{"city":"Houston","number":301,"phones":[{"area":618,"kind":"work","number":6618955},{"area":951,"kind":"home","number":9613341},{"area":981,"kind":"mobile","number":9613382}],"state":"TX","street":"Indian Ave","zip":95075},"connections":[60,45,73],"email":"adam.smith201reachmail.com","communityService":"***"}
      Identify either the region endpoint or the service endpoint and compartment name for your source.
      • endpoint: us-ashburn-1
      • compartment: ocid1.compartment.oc1..aaaaaaaahcrgrgptoaq4cgpoymd32ti2ql4sdpu5puroausdf4og55z4tnya
    • Sink: The user_data table in the Phoenix region.
      Identify either the region endpoint or the service endpoint and the compartment name for your sink.
      • endpoint: us-phoenix-1
      • compartment: ocid1.compartment.oc1..aaaaaaaaleiwplazhwmicoogv3tf4lum4m4nzbcv5wfjmoxuz3doreagvdma

      Identify the sink table schema.

      You can use the same table name and schema as the source table. For information on other schema options, see Identify the Source and Sink topic in Workflow for Oracle NoSQL Database Migrator

  • Identify your OCI cloud credentials for both regions and capture them in the configuration file. Save the config file on your machine in the location /home/<user>/.oci/config. For more details, see Acquiring Credentials.

Note:

  • If the regions are under different tenancies, you must provide different profiles in the /home/<user>/.oci/config file with appropriate OCI cloud credentials for each of them.
  • If the regions are under the same tenancy, you can have a single profile in the /home/<user>/.oci/config file.

In this example, the regions are under different tenancies. The DEFAULT profile includes OCI credentials for the Ashburn region and DEFAULT2 includes OCI credentials for the Phoenix region.

In the migrator configuration file endpoint parameter (both source and sink configuration templates), you can provide either the service endpoint URL or the region ID of the regions. For the list of data regions supported for Oracle NoSQL Database Cloud Service and their service endpoint URLs, see Data Regions and Associated Service URLs in the Oracle NoSQL Database Cloud Service document.
[DEFAULT]
user=ocid1.user.oc1....
fingerprint=fd:96:....
tenancy=ocid1.tenancy.oc1....
region=us-ashburn-1
key_file=</fully/qualified/path/to/the/private/key/>
pass_phrase=abcd


[DEFAULT2]
user=ocid1.user.oc1....
fingerprint=1b:68:....
tenancy=ocid1.tenancy.oc1....
region=us-phoenix-1
key_file=</fully/qualified/path/to/the/private/key/>
pass_phrase=23456
Procedure
To migrate the user_data table from the Ashburn region to the Phoenix region, 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 .
    {
  "source" : {
    "type" : "nosqldb_cloud",
    "endpoint" : "us-ashburn-1",
    "table" : "user_data",
    "compartment" : "ocid1.compartment.oc1..aaaaaaaahcrgrgptoaq4cgpoymd32ti2ql4sdpu5puroausdf4og55z4tnya",
    "credentials" : "/home/<user>/.oci/config",
    "credentialsProfile" : "DEFAULT",
    "readUnitsPercent" : 100,
    "includeTTL" : false,
    "requestTimeoutMs" : 5000
  },
  "sink" : {
    "type" : "nosqldb_cloud",
    "endpoint" : "us-phoenix-1",
    "table" : "user_data",
    "compartment" : "ocid1.compartment.oc1..aaaaaaaaleiwplazhwmicoogv3tf4lum4m4nzbcv5wfjmoxuz3doreagvdma",
    "includeTTL" : false,
    "schemaInfo" : {
      "readUnits" : 100,
      "writeUnits" : 60,
      "storageSize" : 1,
      "useSourceSchema" : true
    },
    "credentials" : "/home/<user>/.oci/config",
    "credentialsProfile" : "DEFAULT2",
    "writeUnitsPercent" : 90,
    "overwrite" : true,
    "requestTimeoutMs" : 5000
  },
  "abortOnError" : true,
  "migratorVersion" : "1.5.0"
}
  2. On your machine, navigate to the directory where you extracted the NoSQL Database Migrator utility. You can access the runMigrator command from the command line interface.
  3. 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>
  4. The utility proceeds with data migration as shown below. The user_data table is created at the sink with the same schema as the source table as you have configured the useSourceSchema parameter as true in the sink configuration template. 
    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 IF NOT EXISTS user_data (id INTEGER, firstName STRING, lastName STRING, otherNames ARRAY(RECORD(first STRING, last STRING)), age INTEGER, income INTEGER, address JSON, connections ARRAY(INTEGER), email STRING, communityService STRING, PRIMARY KEY(SHARD(id))),limits: [100, 60, 1]
[cloud sink] : completed loading DDLs
[cloud sink] : start loading records
migration completed.
Records provided by source=5, Records written to sink=5, Records failed=0, Records skipped=0.
Elapsed time: 0min 5sec 603ms
Migration completed.

    Note:

    • If the table already exists at the sink with the same schema as the source table, the rows with the same primary keys are overwritten as you have provided the overwrite parameter as true in the configuration template.
    • If the table already exists at the sink with a different schema from the source table, the migration will fail.
Validation

To validate the migration, you can log in to your Oracle NoSQL Database Cloud Service console in the Phoenix region. Verify that the source data from the user_data table in the Ashburn region is copied to the user_data table in this region. For the procedure to access the console, see Accessing the Service from the Infrastructure Console article.

Migrate from Oracle NoSQL Database Cloud Service to OCI Object Storage

This example shows the usage of Oracle NoSQL Database Migrator from a Cloud Shell.

Use case

A start-up venture plans to use Oracle NoSQL Database Cloud Service as its data storage solution. The company wants to use Oracle NoSQL Database Migrator to copy data from a table in the Oracle NoSQL Database Cloud Service to OCI Object Storage to make periodic backups of their data. As a cost-effective measure, they want to run the NoSQL Database Migrator utility from the Cloud Shell, which is accessible to all the OCI users.

In this use case, you will learn to copy the NoSQL Database Migrator utility to a Cloud Shell in the subscribed region and perform a data migration. You migrate the source data from Oracle NoSQL Database Cloud Service table to a JSON file in the OCI Object Storage.

You run the runMigrator utility by passing a pre-created configuration file. If you don't provide the configuration file as a runtime parameter, the runMigrator utility prompts you to generate the configuration through an interactive procedure.

Prerequisites
  • Download Oracle NoSQL Database Migrator from the Oracle NoSQL Downloads page to your local machine.
  • Launch the Cloud Shell from the Developer tools menu on your cloud console. The web browser opens your home directory. Click the Cloud Shell menu on the top, right corner of the Cloud Shell window and select the upload option from the drop-down. In the pop-up window, either drag and drop the Oracle NoSQL Database Migrator package from your local machine, or click the Select from your computer option, select the package from your local machine, and click the Upload button. You can also drag and drop the Oracle NoSQL Database Migrator package directly from your local machine to your home directory in the Cloud Shell. Extract the contents of the package.
  • Identify the source and sink for the backup.

    • Source: NDCSupload table in Oracle NoSQL Database Cloud Service Ashburn region.

      For demonstration, consider the following data in the NDCSupload table:
      {"id":1,"name":"Jane Smith","email":"iamjane@somemail.co.us","age":30,"income":30000.0}
{"id":2,"name":"Adam Smith","email":"adam.smith@mymail.com","age":25,"income":25000.0}
{"id":3,"name":"Jennifer Smith","email":"jenny1_smith@mymail.com","age":35,"income":35000.0}
{"id":4,"name":"Noelle Smith","email":"noel21@somemail.co.us","age":40,"income":40000.0}

      Identify the endpoint and compartment ID for your source. For the endpoint, you can supply either the region identifier or the service endpoint. For the list of data regions supported in Oracle NoSQL Database Cloud Service, see Data Regions and Associated Service URLs.

      • endpoint: us-ashburn-1
      • compartment ID: ocid1.compartment.oc1..aaaaaaaahcrgrgptoaq4cgpoymd32ti2ql4sdpu5puroausdf4og55z4tnya

    • Sink: JSON file in the OCI Object Storage Bucket.

      Identify the region endpoint, namespace, bucket, and prefix for OCI Object Storage. For more details, see Accessing Oracle Cloud Object Storage. For the list of OCI Object Storage service endpoints, see Object Storage Endpoints.

      • endpoint: us-ashburn-1
      • bucket: Migrate_oci
      • prefix: Delegation
      • namespace: <> If you do not provide a namespace, the utility uses the default namespace of the tenancy.

      Note:

      If the Object Storage Bucket is in a different compartment, ensure that you have the privileges to write objects in the bucket. For more details on setting the policies, see Let users write objects to Object Storage buckets.
Procedure
To back up the NDCSupload table from Oracle NoSQL Database Cloud Service to a JSON file in the OCI Object Storage Bucket using the Cloud Shell, 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 . Ensure that you set the useDelegationToken parameter to true in source and sink configuration templates.
    The following configuration template is used in this use case:
    {
  "source" : {
    "type" : "nosqldb_cloud",
    "endpoint" : "us-ashburn-1",
    "table" : "NDCSupload",
    "compartment" : "ocid1.compartment.oc1..aaaaaaaahcrgrgptoaq4cgpoymd32ti2ql4sdpu5puroausdf4og55z4tnya",
    "useDelegationToken" : true,
    "readUnitsPercent" : 90,
    "includeTTL" : true,
    "requestTimeoutMs" : 5000
  },
  "sink" : {
    "type" : "object_storage_oci",
    "format" : "json",
    "endpoint" : "us-ashburn-1",
    "namespace" : "",
    "bucket" : "Migrate_oci",
    "prefix" : "Delegation",
    "chunkSize" : 32,
    "compression" : "",
    "useDelegationToken" : true
  },
  "abortOnError" : true,
  "migratorVersion" : "1.6.0"
}
  2. From your Cloud Shell, navigate to the directory where you extracted the NoSQL Database Migrator utility.
  3. Run the runMigrator command by passing the configuration file using the --config or -c option
    [~/nosql-migrator-1.6.0]$./runMigrator --config <complete/path/to/the/config/file>
  4. The NoSQL Database Migrator utility proceeds with data migration. As you have set the useDelegationToken parameter to true, the Cloud Shell automatically authenticates using the delegation token while running the NoSQL Database Migrator utility. The NoSQL Database Migrator copies your data from the NDCSupload table to a JSON file in the Object Storage bucket Migrate_oci. Check the logs for successful data backup.
    [INFO] creating source from given configuration:
[INFO] source creation completed
[INFO] creating sink from given configuration:
[INFO] sink creation completed
[INFO] creating migrator pipeline
[INFO] migration started
[INFO] [OCI OS sink] : writing table schema to Delegation/Schema/schema.ddl
[INFO] [OCI OS sink] : start writing records with prefix Delegation
[INFO] migration completed.
Records provided by source=4,Records written to sink=4,Records failed=0.
Elapsed time: 0min 0sec 486ms
Migration completed.

    Note:

    Data is copied to the file: Migrate_oci/NDCSupload/Delegation/Data/000000.json

    Depending on the chunkSize parameter in the sink configuration template, the source data can be split into several JSON files in the same directory.

    The schema is copied to the file: Migrate_oci/NDCStable1/Delegation/Schema/schema.ddl

Validation

To validate your data backup, log in to the Oracle NoSQL Database Cloud Service console. Navigate through the menus, Storage > Object Storage & Archive Storage > Buckets. Access the files from the NDCSupload/Delegation directory in the Migrate_oci bucket. For the procedure to access the console, see Accessing the Service from the Infrastructure Console article.

Migrate from CSV file to Oracle NoSQL Database

This example shows the usage of Oracle NoSQL Database Migrator to copy data from a CSV file to Oracle NoSQL Database.

Example

After evaluating multiple options, an organization finalizes Oracle NoSQL Database as its NoSQL Database platform. As its source contents are in CSV file format, they are looking for a way to migrate them to Oracle NoSQL Database.

In this example, you will learn to migrate the data from a CSV file called course.csv, which contains information about various courses offered by a university. You generate the configuration file from the runMigrator utility.

You can also prepare the configuration file with the identified source and sink details. See Oracle NoSQL Database Migrator Reference.

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

      In this example, the source file is course.csv

      
cat [~/nosql-migrator-1.5.0]/course.csv
1,"Computer Science", "San Francisco", "2500"
2,"Bio-Technology", "Los Angeles", "1200"
3,"Journalism", "Las Vegas", "1500"
4,"Telecommunication", "San Francisco", "2500"
    • Sink: Oracle NoSQL Database
  • The CSV file must conform to the RFC4180 format.
  • Create a file containing the DDL commands for the schema of the target table, course. The table definition must match the CSV data file concerning the number of columns and their types.

    In this example, the DDL file is mytable_schema.ddl

    
cat [~/nosql-migrator-1.5.0]/mytable_schema.ddl
create table course (id INTEGER, name STRING, location STRING, fees INTEGER, PRIMARY KEY(id));
Procedure
To migrate the CSV file data from course.csv to Oracle NoSQL Database Service, perform the following steps:
  1. Open the command prompt and navigate to the directory where you extracted the Oracle NoSQL Database Migrator utility.
  2. To generate the configuration file using Oracle NoSQL Database Migrator, execute the runMigrator command without any runtime parameters.
    [~/nosql-migrator-1.5.0]$./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.
    You can choose a location for the configuration file or retain the default location by pressing the Enter key. 
    
Configuration file is not provided. Do you want to generate
configuration? (y/n) [n]: y
Generating a configuration file interactively.

Enter a location for your config [./migrator-config.json]: 
./migrator-config.json already exist. Do you want to overwrite?(y/n) [n]: y
  4. Based on the prompts from the utility, choose your options for the Source configuration.
    
Select the source: 
1) nosqldb
2) nosqldb_cloud
3) file
4) object_storage_oci
5) aws_s3
#? 3

Configuration for source type=file
Select the source file format: 
1) json
2) mongodb_json
3) dynamodb_json
4) csv
#? 4
  5. Provide the path to the source CSV file. Further, based on the prompts from the utility, you can choose to reorder the column names, select the encoding method, and trim the tailing spaces from the target table. 
    
Enter path to a file or directory containing csv data: [~/nosql-migrator-1.5.0]/course.csv
Does the CSV file contain a headerLine? (y/n) [n]: n
Do you want to reorder the column names of NoSQL table with respect to
CSV file columns? (y/n) [n]: n
Provide the CSV file encoding. The supported encodings are:
UTF-8,UTF-16,US-ASCII,ISO-8859-1. [UTF-8]: 
Do you want to trim the tailing spaces? (y/n) [n]: n
  6. Based on the prompts from the utility, choose your options for the Sink configuration. 
    
Select the sink:
1) nosqldb
2) nosqldb_cloud
#? 1
Configuration for sink type=nosqldb
Enter store name of the Oracle NoSQL Database: mystore
Enter comma separated list of host:port of Oracle NoSQL Database: <hostname>:5000
  7. Based on the prompts from the utility, provide the name of the target table. 
    
Enter fully qualified table name: course
  8. Enter your choice to set the TTL value. The default value is n. 
    
Include TTL data? If you select 'yes' TTL value provided by the
source will be set on imported rows. (y/n) [n]: n
  9. Based on the prompts from the utility, specify whether or not the target table must be created through the Oracle NoSQL Database Migrator tool. If the table is already created, it is suggested to provide n. If the table is not created, the utility will request the path for the file containing the DDL commands for the schema of the target table. 
    
Would you like to create table as part of migration process?
Use this option if you want to create table through the migration tool.
If you select yes, you will be asked to provide a file that contains
table DDL or to use schema provided by the source or default schema.
(y/n) [n]: y
Enter path to a file containing table DDL: [~/nosql-migrator-1.5.0]/mytable_schema.ddl
Is the store secured? (y/n) [y]: n
would you like to overwrite records which are already present?
If you select 'no' records with same primary key will be skipped [y/n] [y]: y
Enter store operation timeout in milliseconds. [5000]:
Would you like to add transformations to source data? (y/n) [n]: n
  10. Enter your choice to determine whether to proceed with the migration in case any record fails to migrate. 
    
Would you like to continue migration if any data fails to be migrated? 
(y/n) [n]: n
  11. The utility displays the generated configuration on the screen. 
    
Generated configuration is:
{
  "source" : {
    "type" : "file",
    "format" : "csv",
    "dataPath" : "[~/nosql-migrator-1.5.0]/course.csv",
    "hasHeader" : false,
    "csvOptions" : {
      "encoding" : "UTF-8",
      "trim" : false
    }
  },
  "sink" : {
    "type" : "nosqldb",
    "storeName" : "mystore",
    "helperHosts" : ["<hostname>:5000"],
    "table" : "migrated_table",
    "query" : "",
    "includeTTL" : false,
    "schemaInfo" : {
      "schemaPath" : "[~/nosql-migrator-1.5.0]/mytable_schema.ddl"
    },
    "overwrite" : true,
    "requestTimeoutMs" : 5000
  },
  "abortOnError" : true,
  "migratorVersion" : "1.5.0"
}
  12. Finally, the utility prompts you to specify whether or not to proceed with the migration using the generated configuration file. The default option is y.
    Note: If you select n, you can use the generated configuration file to perform the migration. Specify 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 ./migrator-config.json
(y/n) [y]: y
  13. The NoSQL Database Migrator copies your data from the CSV file to Oracle NoSQL Database.
    
creating source from given configuration:
source creation completed
creating sink from given configuration:
sink creation completed
creating migrator pipeline
migration started
[nosqldb sink] : start loading DDLs
[nosqldb sink] : executing DDL: create table course (id INTEGER, name STRING, location STRING, fees INTEGER, PRIMARY KEY(id))
[nosqldb sink] : completed loading DDLs
[nosqldb sink] : start loading records
[csv file source] : start parsing CSV records from file: course.csv
migration completed. Records provided by source=4, Records written to sink=4, Records failed=0,Records skipped=0.
Elapsed time: 0min 0sec 559ms
Migration completed.
Validation
Start the SQL prompt in your KVStore.
 java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore
Verify that the new table is created with the source data:

sql-> select * from course;
{"id":4,"name":"Telecommunication","location":"San Francisco","fees":2500}
{"id":1,"name":"Computer Science","location":"San Francisco","fees":2500}
{"id":2,"name":"Bio-Technology","location":"Los Angeles","fees":1200}
{"id":3,"name":"Journalism","location":"Las Vegas","fees":1500}
 
4 rows returned