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:

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 image follows

Description of the illustration migrator_overview.png

Terminology used with Oracle NoSQL Database Migrator

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

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.

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
File system (file) MongoDB JSON (mongodb_json) Y N
File system (file) DynamoDB JSON (dynamodb_json) Y N
File system (file) Parquet (parquet) N Y
File system (file) CSV (csv) Y N
OCI Object Storage (object_storage_oci) JSON (json) Y Y
OCI Object Storage (object_storage_oci) MongoDB JSON (mongodb_json) Y N
OCI Object Storage (object_storage_oci) Parquet (parquet) N Y
OCI Object Storage (object_storage_oci) 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.

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.

Authorization in Oracle NoSQL Database Cloud Service sources and sinks

Access to resources in Oracle NoSQL Database Cloud Service such as tables, tablespaces, and APIs is managed through Identity and Access Management (IAM) policies. This ensures that only users or applications with the appropriate inspect, read, use, or manage table permissions within a specific compartment can interact with these resources. For more information, see Managing Access to NDCS Tables.

When using Migrator utility to import or export data from Oracle NoSQL Database Cloud Service tables, your effective IAM permissions determine the resources you can read from or write to. If a user from a defined group attempts an action beyond their authorized privileges, the Migrator utility returns the corresponding authorization error as provided by OCI IAM.

For example, the OCI IAM denies any attempt to import data into an Oracle NoSQL Database Cloud Service table if your user group only has the “read” permission on the table. An error message similar to the following is displayed in the logs:

[INSUFFICIENT_PERMISSION] Authorization failed or requested resource not found

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 image follows

Description of the illustration migrator_flow.png

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-premises 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.

Note: Migration will fail if the table is present at the sink and the DDL in the schemaPath is different than the table.

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.

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 run time 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>

Note: NoSQL Database Migrator consumes read units while performing data export from Oracle NoSQL Cloud Service table to any valid sink.

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.

Limitation

Oracle NoSQL Database Migrator does not lock the database during backup and block other users. Therefore, it is highly recommended not to perform the following activities when a migration task is running:

Migrating TTL Metadata for Table Rows

Learn how to migrate TTL data from source to the sink.

Time to Live (TTL) is a mechanism that allows you to automatically expire table rows. TTL is expressed as the amount of time, data is allowed to live in the store. Data that has reached its expiration timeout value can no longer be retrieved, and will not appear in any store statistics.

You can choose to include the TTL metadata for table rows along with the actual data when performing migration of Oracle NoSQL Database tables. The NoSQL Database Migrator provides configuration parameters to support the export and import of table row TTL metadata for the following source types:

Table - Migrating TTL metadata

Source types Source configuration parameter Sink configuration parameter
Oracle NoSQL Database includeTTL includeTTL
Oracle NoSQL Database Cloud Service includeTTL includeTTL
DynamoDB-Formatted JSON File ttlAttributeName includeTTL
DynamoDB-Formatted JSON File stored in AWS S3 ttlAttributeName includeTTL

Exporting TTL metadata in Oracle NoSQL Database and Oracle NoSQL Database Cloud Service

NoSQL Database Migrator provides the includeTTL configuration parameter to support the export of table row’s TTL metadata.

When a table is exported, the TTL data is exported for the table rows that have a valid expiration time. If a row does not expire, then the _metadata JSON object is not included explicitly in the exported data because its expiration value is always

  1. 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 the includeTTL configuration parameter in the sink configuration template.

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. However, 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. The extension is calculated as follows and added to the expiration time.

Extended time = expiration time - reference time

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 set to true.

Importing TTL Metadata in DynamoDB-Formatted JSON File and DynamoDB-Formatted JSON File stored in AWS S3

NoSQL Database Migrator provides an additional configuration parameter, ttlAttributeName to support the import of TTL metadata from the DynamoDB-formatted JSON file items.

DynamoDB exported JSON files include a specific attribute in each item to store the TTL expiration timestamp. To optionally import the TTL values from DynamoDB exported JSON files, you must supply the specific attribute’s name as a value to the ttlAttributeName configuration parameter in the DynamoDB-Formatted JSON File or DynamoDB-Formatted JSON File stored in AWS S3 source configuration files. Also, you must set the includeTTL configuration parameter in the sink configuration template. The valid sinks are Oracle NoSQL Database and Oracle NoSQL Database Cloud Service. NoSQL Database Migrator stores TTL information in the _metadata JSON object for the imported item.

The import operation manages the following use cases when migrating table items of the DynamoDB exported JSON files:

Note: You can supply the ttlRelativeDate configuration parameter in the sink configuration template as the reference time for calculating the expiration time.

Importing data to a sink with an IDENTITY column

Learn how to import data to a sink that includes 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 ID FROM migrateID ORDER BY ID DESC LIMIT 1

Output:

{"ID":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.

Filtering Data Using Query Predicates

Learn how to specify query predicates to export only the table rows that match the filter criteria.

Query Predicate

NoSQL Database Migrator provides an option to filter out data during export by specifying a query predicate. The query predicate specifies conditions that must be met for a row to be exported. The Migrator utility translates the query predicate into a SQL WHERE clause and applies it on the given table to provide a filter condition to only export the rows matching the specified condition. You can use built-in functions (modification_time(), expiration_time(), creation_time()) in the query predicate to create advanced filter options.

You can use query predicates only on Oracle NoSQL Database and Oracle NoSQL Database Cloud Service sources for all the supported sinks. For further details, see Oracle NoSQL Database and Oracle NoSQL Database Cloud Service.

For a use case demonstration, see Migrate from Oracle NoSQL Database Cloud Service to a JSON file.

Dump filter

The Migrator utility provides an option to echo the SQL query that is executed on the backend. This feature helps you verify the generated query and if required, refine your filter before running the migration task.

You can run the Migrator utility with the dump filter option as follows:

[~/nosqlMigrator]$./runMigrator --dump-filter|df [optional-config-file]

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

Procedure

To migrate the data and schema definition of your table from Oracle NoSQL Database Cloud Service to a JSON file, you can use one of the following options:

  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]$./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

4) session_token

5) oke_workload_identity

#? 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 directory to store JSON data: /home/<user>/nosqlMigrator
would you like to export data to multiple files for each source?(y/n) [y]: n
Would you like to store JSON in pretty format? (y/n) [n]: y
Would you like to migrate the table schema also? (y/n) [y]: y
Enter path to a file to store table schema: /home/<user>/nosqlMigrator/myTableSchema

  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-ashburn-1",
    "table": "myTable",
    "compartment": "ocid1.compartment.oc1..aa..rhsmq",
    "credentials": "/home/<user>/.oci/config",
    "credentialsProfile": "DEFAULT",
    "readUnitsPercent": 90,
    "requestTimeoutMs": 5000
  },
  "sink": {
    "type": "file",
    "format": "json",
    "useMultiFiles" : false,
    "schemaPath": "/home/<user>/nosqlMigrator/myTableSchema",
    "pretty": true,
    "dataPath": "/home/<user>/nosqlMigrator"
  },
  "abortOnError": true,
  "migratorVersion": "1.8.0"
}

  9. 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/<user>/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,Records skipped=0.
Elapsed time: 0min 1sec 277ms
Migration completed.

    Validation

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

-- Exported myTable Data. JSON files are created in the supplied data path

[~/nosqlMigrator]$cat myTable_1_5.json
{
  "id" : 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)))

  1. Prepare the configuration file (in JSON format) with Oracle NoSQL Database Cloud Service (NDCS) source and JSON sink details. See Source Configuration Templates and Sink Configuration Templates .

    A users table is used with the following data in this example:

    {"id":10,"firstName":"John","lastName":"Smith","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}}
{"id":20,"firstName":"Jane","lastName":"Smith","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}}
{"id":30,"firstName":"Adam","lastName":"Smith","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}}
{"id":40,"firstName":"Joanna","lastName":"Smith","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}}

    Ensure that you include the queryFilter parameter with appropriate query predicate in the source configuration template to export only the required rows from your table. For details on how to create query predicates, see the Sample Query Predicates table in the NoSQL Database Cloud Service Source topic.

    In this example, the query predicate exports rows with the city field in address JSON column = ‘Houston’ from the users table.

    {
  "source" : {
    "type" : "nosqldb_cloud",
    "endpoint" : "us-ashburn-1",
    "table" : "users",
    "queryFilter" : "$row.address.city='Houston'",
    "compartment" : "ocid1.compartment.oc1..aa..rhsmq",
    "credentials" : "/home/<user>/.oci/config",
    "credentialsProfile" : "DEFAULT",
    "readUnitsPercent" : 90,
    "includeTTL" : true,
    "requestTimeoutMs" : 5000
  },
  "sink" : {
    "type" : "file",
    "format" : "json",
    "useMultiFiles" : true,
    "chunkSize" : 32,
    "schemaPath" : "/scratch/<user>/nosqlMigrator/tableschema.ddl",
    "pretty" : false,
    "dataPath" : "/scratch/<user>/nosqlMigrator"
  },
  "abortOnError" : true,
  "migratorVersion" : "1.8.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. Use the --config or -c option.

    [~/nosqlMigrator]$./runMigrator --config <complete/path/to/the/JSON/config/file>

    Note:

    You can also run the command with the

    option to view and verify the generated query before running the migration task as follows. For more details, see

    .

    [~/nosqlMigrator]$./runMigrator --dump-filter <complete/path/to/the/JSON/config/file>

    The utility proceeds with the data migration as follows:

    [INFO] creating source from given configuration:
[INFO] [cloud source] : query = 'SELECT $row,expiration_time_millis($row) AS expiration FROM users $row where $row.address.city='Houston''
[INFO] source creation completed
[INFO] creating sink from given configuration:
[INFO] sink creation completed
[INFO] creating migrator pipeline
[INFO] [json file sink] : writing table schema to /scratch/raumesh/nosqlMigrator/tableschema.ddl
[INFO] migration started
[INFO] Migration success for source users. read=2,written=2,failed=0
[INFO] Migration is successful for all the sources.
[INFO] migration completed.
Records provided by source=2, Records written to sink=2, Records failed=0,Records skipped=0.
Elapsed time: 0min 0sec 182ms
Migration completed.

Verification

To verify the migration, you can navigate to the specified sink directory and view the schema and data. Only the rows in address JSON column with city field value ‘Houston’ are exported.

-- Exported users data. Schema and JSON files are created in the supplied data paths.

[~/nosqlMigrator]: cat tableschema.ddl

CREATE TABLE IF NOT EXISTS users (id INTEGER, firstName STRING, lastName STRING, age INTEGER, income INTEGER, address JSON, PRIMARY KEY(SHARD(id)))
[~/nosqlMigrator]: cat users_6_10.json

{"id":30,"firstName":"Adam","lastName":"Smith","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}}
{"id":40,"firstName":"Joanna","lastName":"Smith","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}}
bash-4.4$

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

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.8.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.8.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

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.8.0]/schema_json.ddl"
    },
    "dataPath" : "[~/nosql-migrator-1.8.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.8.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.8.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 image follows

Description of the illustration migrate_json1.png

Figure - Oracle NoSQL Database Cloud Service Console Table Data

Description of image follows

Description of the illustration migrate_json2.png

Migrate from MongoDB JSON file to Oracle NoSQL Database Cloud Service

This example shows how to use the Oracle NoSQL Database Migrator to copy MongoDB-formatted data to 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. The tables and data are in MongoDB and the organization wants to migrate both 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.

Let us consider the following two sample JSON files exported from MongoDB to demonstrate our use case.

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"}]}

A sample MongoDB-formatted JSON file exported from a Spring application is as follows:

{"_id":{"$oid":"63d3a87cf564fc21dac3838d"},"firstName":"John","lastName":"Smith","address":{"Country":"France"},"_class":"com.example.demo.Customer"}
{"_id":{"$oid":"63d3a87cf564fc21dac3838e"},"firstName":"Sam","lastName":"David","address":{"Country":"USA"},"_class":"com.example.demo.Customer"}
{"_id":"3","firstName":"Dona","lastName":"William","address":{"Country":"England"},"_class":"com.example.demo.Customer"}

MongoDB supports two types of extensions to the formatted JSON 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. NoSQL Database Migrator supports both the modes.

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

Procedure

To migrate the MongoDB-formatted JSON data to Oracle NoSQL Database Cloud Service, you can choose from one of the following options:

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

    Here, you set the defaultSchema configuration parameter to true. Therefore, NoSQL Database Migrator creates a table with the default schema at the sink.

    {
  "source" : {
    "type" : "file",
    "format" : "mongodb_json",
    "dataPath" : "<complete/path/to/the/MongoDB/Formatted/JSON/file>"
  },
  "sink" : {
    "type" : "nosqldb_cloud",
    "endpoint" : "us-ashburn-1",
    "table" : "mongoImport",
    "compartment" : "ocid1.compartment.oc1..aaaaaaaaadeskhnnfkenws4k2vdyklcc32hfpzzz4z3zum3ubhmpz6zxnoza",

    "includeTTL" : false,
    "schemaInfo" : {
      "readUnits" : 100,
      "writeUnits" : 60,
      "storageSize" : 1,
      "defaultSchema" : true
    },
    "credentials" : "<complete/path/to/the/oci/config/file>",
    "credentialsProfile" : "DEFAULT",
    "writeUnitsPercent" : 90,
    "overwrite" : true,
    "requestTimeoutMs" : 5000
  },
  "abortOnError" : true,
  "migratorVersion" : "1.8.0"
}

    The default schema for MongoDB-formatted JSON file source is 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 = The _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.

    Note: If the table <tablename> already exists in Oracle NoSQL Database Cloud Service and you want to migrate data to the table using the defaultSchema configuration, you must ensure that the existing table has the ID column in lower case (id) and is of the type STRING.

  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. Use --config or -c option.

    $./runMigrator --config <complete/path/to/the/JSON/config/file>

  4. The utility proceeds with the data migration, as shown below.

    [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] [cloud sink] : start loading DDLs
[INFO] [cloud sink] : executing DDL: CREATE TABLE IF NOT EXISTS mongoImport (id STRING, document JSON, PRIMARY KEY(SHARD(id))),limits: [100, 60, 1]
[INFO] [cloud sink] : completed loading DDLs
[INFO] migration started
[INFO] [mongo file source] : start parsing MongoDB JSON records from file: mongoDBSample.json
[INFO] Migration success for source mongoDBSample. read=5,written=5,failed=0
[INFO] Migration is successful for all the sources.
[INFO] migration completed.
Records provided by source=5, Records written to sink=5, Records failed=0,Records skipped=0.
Elapsed time: 0min 0sec 448ms
Migration completed.

Verification

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

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

    Here, you specify the file containing the DDL statement of the sink table in the schemaPath parameter of the source configuration template. Correspondingly, set the useSourceSchema configuration parameter to true in the sink configuration template.

    You can generate a custom schema as follows:

    • Note the names and data types for each column from the MongoDB-formatted JSON data. Use this information to create a schema DDL file for Oracle NoSQL Database Cloud Service table.

    • In the schema file, name the first column (primary key) as id of type STRING. Include the same name and type for the remaining columns as recorded in the MongoDB-formatted JSON file.

    • Save the schema file and note down it’s complete path.

    The following user-defined schema is used in this example:

    CREATE TABLE IF NOT EXISTS sampleMongoDBImp (id STRING, name STRING, scores JSON, PRIMARY KEY(SHARD(id)));

    You must include a renameFields transformation instructing NoSQL Database Migrator to convert the _id column to id while creating the table. For parameter details, see Transformation Configuration Templates. NoSQL Database Migrator creates a table with the custom schema at the sink.

    {
  "source" : {
    "type" : "file",
    "format" : "mongodb_json",
    "schemaInfo" : {
      "schemaPath" : "<complete/path/to/the/schema/file>"
    },
    "dataPath" : "<complete/path/to/the/MongoDB/Formatted/JSON/file>"
  },
  "sink" : {
    "type" : "nosqldb_cloud",
    "endpoint" : "us-ashburn-1",
    "table" : "sampleMongoDBImp",
    "compartment" : "ocid1.compartment.oc1..aaaaaaaaadeskhnnfkenws4k2vdyklcc32hfpzzz4z3zum3ubhmpz6zxnoza",
    "includeTTL" : true,
    "schemaInfo" : {
      "readUnits" : 100,
      "writeUnits" : 60,
      "storageSize" : 1,
      "useSourceSchema" : true
    },
    "credentials" : "<complete/path/to/the/oci/config/file>",
    "credentialsProfile" : "DEFAULT",
    "writeUnitsPercent" : 90,
    "overwrite" : false,
    "requestTimeoutMs" : 5000
  },
  "transforms": {
    "renameFields" : {
      "_id":"id"
    }
  },
  "abortOnError" : true,
  "migratorVersion" : "1.8.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. Use --config or -c option.

    $./runMigrator --config <complete/path/to/the/JSON/config/file>

  4. The utility proceeds with the data migration, as shown below.

    [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] [cloud sink] : start loading DDLs
[INFO] [cloud sink] : executing DDL: CREATE TABLE IF NOT EXISTS sampleMongoDBImp (id INTEGER, name STRING, scores JSON, PRIMARY KEY(SHARD(id))),limits: [100, 60, 1]
[INFO] [cloud sink] : completed loading DDLs
[INFO] migration started
[INFO] [mongo file source] : start parsing MongoDB JSON records from file: mongoDBSample.json
[INFO] Migration success for source mongoDBSample. read=5,written=5,failed=0
[INFO] Migration is successful for all the sources.
[INFO] migration completed.
Records provided by source=5, Records written to sink=5, Records failed=0,Records skipped=0.
Elapsed time: 0min 0sec 438ms
Migration completed.

Verification

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

  1. For this use case, we will use the sample MongoDB-formatted JSON file exported from a Spring application as the source. For more details on this format, see Spring Data.

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

    Here, you specify the file containing the DDL statement of the sink table in the schemaPath parameter of the source configuration template. Correspondingly, set the useSourceSchema configuration parameter to true in the sink configuration template.

    You can generate a custom schema as follows:

    • Note the names and data types for each column from the MongoDB-formatted JSON data.

    • In the schema file, name the first column (primary key) as id of type STRING. Aggregate the remaining fields to a field named kv_json_ of type JSON, adhering to the Spring data format. For more details, see the Persistence Model of spring data framework.

    • Save the schema file and note down it’s complete path.

    The following user-defined schema is used in this example:

    CREATE TABLE IF NOT EXISTS sampleMongoDBSpringImp(id STRING, kv_json_ JSON, PRIMARY KEY(SHARD(id)))

    For the Spring data sample given above, you must include the following transformations:

    • renameFields transformation to convert the _id column to id

    • ignoreFields transformation to ignore the _class column and not include it in the sink table

    • aggregateFields transformation to aggregate the remaining fields (other than id) to a field of type JSON

    For parameter details, see Transformation Configuration Templates. NoSQL Database Migrator creates a table with the custom schema at the sink.

    {
  "source": {
    "type": "file",
    "format": "mongodb_json",
    "schemaInfo": {
      "schemaPath": "<complete/path/to/the/schema/file>"
    },
    "dataPath": "<complete/path/to/the/MongoDB/Formatted/JSON/file>"
  },
  "sink": {
    "type": "nosqldb_cloud",
    "endpoint": "us-ashburn-1",
    "table": "sampleMongoDBSpringImp",
    "compartment": "ocid1.compartment.oc1..aaaaaaaaadeskhnnfkenws4k2vdyklcc32hfpzzz4z3zum3ubhmpz6zxnoza",
    "includeTTL": true,
    "schemaInfo": {
      "readUnits": 100,
      "writeUnits": 60,
      "storageSize": 1,
      "useSourceSchema": true
    },
    "credentials": "<complete/path/to/the/oci/config/file>",
    "credentialsProfile": "DEFAULT",
    "writeUnitsPercent": 90,
    "overwrite": false,
    "requestTimeoutMs": 5000
  },
  "transforms": {
    "renameFields": {
      "_id": "id"
    },
    "ignoreFields": ["_class"],
    "aggregateFields": {
      "fieldName": "kv_json_",
      "skipFields": ["id"]
    }
  },
  "abortOnError" : true,
  "migratorVersion" : "1.8.0"
}

  3. Open the command prompt and navigate to the directory where you extracted the NoSQL Database Migrator utility.

  4. Run the runMigrator command by passing the configuration file. Use --config or -c option.

    $./runMigrator --config <complete/path/to/the/JSON/config/file>

  5. The utility proceeds with the data migration, as shown below.

    creating source from given configuration:
source creation completed
creating sink from given configuration:
sink creation completed
creating migrator pipeline
[cloud sink] : start loading DDLs
[cloud sink] : executing DDL: CREATE TABLE IF NOT EXISTS sampleMongoDBSpringImp (id STRING, kv_json_ JSON, PRIMARY KEY(SHARD(id))),limits: [100, 60, 1]
[cloud sink] : completed loading DDLs
migration started
[mongo file source] : start parsing MongoDB JSON records from file: mongodbspring.json
Migration success for source mongodbspring. read=3,written=3,failed=0
Migration is successful for all the sources.
migration completed.
Records provided by source=3, Records written to sink=3, Records failed=0,Records skipped=0.
Elapsed time: 0min 0sec 393ms
Migration completed.

Verification

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

Migrate from DynamoDB JSON file to Oracle NoSQL Database Cloud Service

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

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 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"},"ttl": {"N": "1734616800"}}}
{"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"},"ttl": {"N": "1734616800"}}}

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 to Oracle NoSQL Database Cloud Service. You will use a manually created configuration file for this example.

Prerequisites

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. For details, see Source Configuration Templates and Sink Configuration Templates.

    Note: If your DynamoDB exported JSON table items contain TTL attribute, to optionally import the TTL values, specify the attribute in the ttlAttributeName configuration parameter of the source configuration template and set the includeTTL configuration parameter to true in the sink configuration template. For more details, see Migrating TTL Metadata for Table Rows.

    Here, you set the defaultSchema configuration parameter to true. Therefore, the NoSQL Database Migrator creates the default schema at the sink. You must specify the DDBPartitionKey and the corresponding NoSQL column type. Otherwise, an error is displayed.

    For details on the default schema for a DynamoDB exported JSON source, see Identify the Source and Sink topic in Workflow for Oracle NoSQL Data Migrator.

        {
     "source" : {
      "type" : "file",
      "format" : "dynamodb_json",
      "ttlAttributeName" : "ttl",
      "dataPath" : "complete/path/to/the/DynamoDB/Formatted/JSON/file"
     },
     "sink" : {
      "type" : "nosqldb_cloud",
      "endpoint" : "us-sanjose-1",
      "table" : "sampledynDBImp",
      "compartment" : "ocid
1.compartment.oc1..aaaaaaaa......smq",
      "includeTTL" : true,
      "schemaInfo" : {
       "readUnits" : 10,
       "writeUnits" : 10,
       "storageSize" : 1,
       "schemaPath" : "complete/path/to/the/DDl/file"
      },
      "credentials" : "/home/<username>/.oci/config",
      "credentialsProfile" : "DEFAULT",
      "writeUnitsPercent" : 90,
      "overwrite" : true,
      "requestTimeoutMs" : 5000
     },
     "abortOnError" : true,
     "migratorVersion" : "1.8.0"
    }

    The following default schema is used in this example:

    CREATE TABLE IF NOT EXISTS sampledynDBImp (Id INTEGER, document JSON, PRIMARY KEY(SHARD(Id)))

  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 separate configuration files. Use the --config or -c option.

    ./runMigrator --config <complete/path/to/the/JSON/config/file>

  4. The utility proceeds with the data migration as illustrated in the following sample:

    [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] [cloud sink] : start loading DDLs
[INFO] [cloud sink] : executing DDL: CREATE TABLE IF NOT EXISTS sampledynDBImp (Id INTEGER, document JSON, PRIMARY KEY(SHARD(Id)))
[INFO] [cloud sink] : completed loading DDLs
[INFO] migration started
[INFO] Start writing data to OnDB Sink
[INFO] executing for source:DynamoSample
[INFO] [DDB file source] : start parsing JSON records from file: DynamoSample.json.gz
[INFO] Writing data to OnDB Sink completed.
[INFO] migration completed.
Records provided by source=2, Records written to sink=2, Records failed=0,Records skipped=0.
Elapsed time: 0min 0sec 45ms
Migration completed.

Verification

To validate the migration, you can log in to your Oracle NoSQL Database Cloud Service console and verify that the sampledynDBImp table is created with the source data.

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

    Note: If your DynamoDB exported JSON table items contain TTL attribute, to optionally import the TTL values, specify the attribute in the ttlAttributeName configuration parameter of the source configuration template and set the includeTTL configuration parameter to true in the sink configuration template.

    Here, you set the defaultSchema configuration parameter to false. Therefore, you specify the file containing the sink table’s DDL statement in the schemaPath parameter. See Mapping of DynamoDB types to Oracle NoSQL types for more details.

    The following user-defined schema is used in this example:

    CREATE TABLE IF NOT EXISTS sampledynDBImp (Id INTEGER, document JSON, PRIMARY KEY(SHARD(Id)))

    NoSQL Database Migrator uses the schema file 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, an error is displayed.

    Note:

    • If the Dynamo DB table has a data type that is not supported in NoSQL Database, the migration fails.

    • 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 must be a 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, SQL NULL is inserted if values are not provided for the column.

    • If you are modeling DynamoDB table as a JSON document, ensure that you use AggregateFields transform in order to aggregate non-primary key data into a JSON column. For details, see aggregateFields

        Generated configuration is
    {
     "source" : {
      "type" : "file",
      "format" : "dynamodb_json",
      "ttlAttributeName" : "ttl",
      "dataPath" : "complete/path/to/the/DynamoDB/Formatted/JSON/file"
     },
     "sink" : {
      "type" : "nosqldb_cloud",
      "endpoint" : "us-sanjose-1",
      "table" : "sampledynDBImp",
      "compartment" : "ocid
1.compartment.oc1..aaaaaaaa......smq",
      "includeTTL" : true,
      "schemaInfo" : {
       "readUnits" : 10,
       "writeUnits" : 10,
       "storageSize" : 1,
       "schemaPath" : "complete/path/to/the/DDl/file"
      },
      "credentials" : "/home/<username>/.oci/config",
      "credentialsProfile" : "DEFAULT",
      "writeUnitsPercent" : 90,
      "overwrite" : true,
      "requestTimeoutMs" : 5000
     },
     "abortOnError" : true,
     "migratorVersion" : "1.8.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 separate configuration files. Use the --config or -c option.

    ./runMigrator --config <complete/path/to/the/JSON/config/file>

  4. The utility proceeds with the data migration as illustrated in the following sample:

    [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] [cloud sink] : start loading DDLs
[INFO] [cloud sink] : executing DDL: CREATE TABLE IF NOT EXISTS sampledynDBImp (Id INTEGER, document JSON, PRIMARY KEY(SHARD(Id)))
[INFO] [cloud sink] : completed loading DDLs
[INFO] migration started
[INFO] Start writing data to OnDB Sink
[INFO] executing for source:DynamoSample
[INFO] [DDB file source] : start parsing JSON records from file: DynamoSample.json.gz
[INFO] Writing data to OnDB Sink completed.
[INFO] migration completed.
Records provided by source=2, Records written to sink=2, Records failed=0,Records skipped=0.
Elapsed time: 0min 0sec 45ms
Migration completed.

Verification

To validate the migration, you can log in to your Oracle NoSQL Database Cloud Service console and verify that the sampledynDBImp table is created with the source data.

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

This example shows how to use 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

Procedure

To migrate the DynamoDB JSON data to Oracle NoSQL Database Cloud Service, use one of the following options:

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

    Note: If the items in your DynamoDB JSON File in AWS S3 contain TTL attribute, to optionally import the TTL values, specify the attribute in the ttlAttributeName configuration parameter of the source configuration template and set the includeTTL configuration parameter to true in the sink configuration template. For more details, see Migrating TTL Metadata for Table Rows.

    Set the defaultSchema to TRUE and so the Migrator utility creates the default schema at the sink. You must specify the DDBPartitionKey and the corresponding NoSQL column type. Otherwise, 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.8.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 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

  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. Use the --config or -c option.

    [~/nosqlMigrator]$./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.

Verification

You can log in to your Oracle NoSQL Database Cloud Service console and verify that the new table is created with the source data.

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

    Note: If the items in your DynamoDB JSON File in AWS S3 contain TTL attribute, to optionally import the TTL values, specify the attribute in the ttlAttributeName configuration parameter of the source configuration template and set the includeTTL configuration parameter to true in the sink configuration template. For more details, see Migrating TTL Metadata for Table Rows.

    To specify a user-defined schema file in the sink configuration template, set the defaultSchema to FALSE and specify the schemaPath as a file containing your DDL statement. For details, see Mapping of DynamoDB types to Oracle NoSQL types .

    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.
    • If you are modeling DynamoDB table as a JSON document, ensure that you use AggregateFields transform in order to aggregate non-primary key data into a JSON column. For details, see aggregateFields .
        {
     "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
     },
      "transforms": {
        "aggregateFields" : {
          "fieldName" : "document",
          "skipFields" : ["AccountId"]
        }
      },
     "abortOnError" : true,
     "migratorVersion" : "1.8.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. Use the --config or -c option.

    [~/nosqlMigrator]$./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.

Verification

You can log in to your Oracle NoSQL Database Cloud Service 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

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.8.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]$./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

Procedure

To migrate from Oracle NoSQL Database Cloud Service to OCI Object Storage, perform the following from the Cloud Shell window:

  1. Prepare a Migrator configuration file, migrator-config.json, with the Oracle NoSQL Database Cloud Service source and OCI Object Storage sink. For templates, 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.8.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]$./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 OCI Object Storage to Oracle NoSQL Database Cloud Service Using OKE Authentication

This example shows how to use Oracle NoSQL Database Migrator with OKE Workload Identity Authentication to copy data from a JSON file in the OCI Object Storage to Oracle NoSQL Database Cloud Service table.

Use case

As a developer, you are exploring an option to restore data from a JSON file in OCI Object Storage (OCI OS) bucket to Oracle NoSQL Database Cloud Service (NDCS) table using NoSQL Database Migrator in a containerized application. A Containerized application is a virtualized environment that bundles the application and all its dependencies (like libraries, binaries, and configuration files) in a package. This allows the application to run consistently across different environments, regardless of the underlying infrastructure.

You want to run NoSQL Database Migrator within an Oracle Cloud Infrastructure Container Engine for Kubernetes (OKE) pod. To securely access OCI OS and NDCS services from the OKE pod, you will use Workload Identity Authentication (WIA) method.

In this demonstration, you will build a docker image for NoSQL Database Migrator, copy the image into a repository in the Container Registry, create an OKE cluster, and deploy the Migrator image in the OKE cluster pod to run the Migrator utility. Here, you will supply a manually created NoSQL Database Migrator configuration file to run the Migrator utility as a containerized application.

Prerequisites

Procedure

To migrate from JSON file in the OCI OS bucket to NDCS table, perform the following from the Cloud Shell window:

  1. Prepare a Migrator configuration file, migrator-config.json, with OCI OS source and NDCS sink. For templates, see Source Configuration Templates and Sink Configuration Templates.

    To use the OKE WIA to access OCI OS bucket and NDCS, set the useOKEWorkloadIdentity parameter to true in both source and sink configuration templates. Here, you will use source schema from the schema DDL file in the OCI OS bucket. Therefore, set useSourceSchema parameter to true in the sink configuration template.

    Note: While using OKE WIA, you can’t generate the Migrator configuration file interactively. You must prepare the configuration file manually by referring to the source and sink configuration templates.

        {
      "source" : {
        "type" : "object_storage_oci",
        "format" : "json",
        "endpoint" : "us-ashburn-1",
        "namespace" : "",
        "bucket" : "Migrate_oci",
        "prefix" : "userSession",
        "useOKEWorkloadIdentity" : true
      },
      "sink" : {
        "type" : "nosqldb_cloud",
        "endpoint" : "us-ashburn-1",
        "table" : "users",
        "compartment" : "Training-NoSQL",
        "includeTTL" : true,
        "schemaInfo" : {
          "readUnits" : 100,
          "writeUnits" : 60,
          "storageSize" : 1,
          "useSourceSchema" : true
        },
        "useOKEWorkloadIdentity" : true,
        "writeUnitsPercent" : 90,
        "overwrite" : true,
        "requestTimeoutMs" : 5000
      },
      "abortOnError" : true,
      "migratorVersion" : "1.8.0"
    }

  2. Create a configure map resource (configmap) to pass the migrator-config.json configuration input file to the Migrator container at runtime in the Kubernetes pod. The configmap mounts the Migrator configuration file in the file system of the container as a mount volume.

    #Command:
kubectl create configmap oci-migrator-config --from-file=<Migrator configuration file> -n <namespace-name>
    #Example:
kubectl create configmap oci-migrator-config --from-file=migrator-config.json -n migrator
    #Output:
configmap/oci-migrator-config created

  3. Create a Docker registry secret that includes the OCI credentials to use while pulling the Migrator Docker image from Container Registry into the Kubernetes pod.

    #Command:
kubectl create secret docker-registry ocirsecret --docker-server=<region-key>.ocir.io --docker-username='tenancy-namespace/username' --docker-password='auth token' --docker-email='<user Email>' -n <namespace-name>
    #Example:
kubectl create secret docker-registry ocirsecret --docker-server=iad.ocir.io --docker-username='idhx..xxwjzj/rx..xxxxh@oracle.com' --docker-password='<Auth token>' --docker-email='rx..xxxxh@oracle.com' -n migrator
    #Output:
secret/ocirsecret created

  4. Create a manifest file, which you will use to specify the Migrator Docker image. Ensure that you provide the values from the previous steps for namespace, Kubernetes service account name, Docker image name, Docker registry secret, and configmap. You can refer to the following sample manifest file:

    #migrator-deployment.yaml

apiVersion: apps/v1
kind: Deployment
metadata:
  name: nosql-migrator
spec:
  replicas: 1
  selector:
    matchLabels:
      app: nosql-migrator
  template:
    metadata:
      labels:
        app: nosql-migrator
    spec:
      serviceAccountName: migratorsa
      automountServiceAccountToken: true
      containers:

        - name: nosqlmigrator
          image: iad.ocir.io/idhx..xxwjzj/okemigrator:1.0
          imagePullPolicy: Always
          args: ["--config", "/config/migrator-config.json", "--log-level", "DEBUG"]
          volumeMounts:

            - name: config-volume
              mountPath: /config  # Mount the file here
      imagePullSecrets:

        - name: ocirsecret
      volumes:

        - name: config-volume
          configMap:
            name: oci-migrator-config

  5. Deploy the Migrator Docker image in the Kubernetes pod using the following command. OKE runs the Migrator utility in a container on one of the nodes of the Kubernetes cluster.

    #Command:
kubectl create -f <manifest file> -n <namespace-name>
    #Example:
kubectl create -f migrator-deployment.yaml -n migrator
    #Output:
deployment.apps/nosql-migrator created

  6. You can check the logs using the following commands:

    1. Fetch the pod’s name on which the Migrator utility is running.

      #Command:
kubectl get pods -n <namespace-name>
      #Example:
kubectl get pods -n migrator
      #Output:
NAME                            READY   STATUS    RESTARTS   AGE
nosql-migrator-ccdbf549-6sjjg   1/1     Running   0          56s

    2. Use the pod’s name to fetch the Migrator logs.

      #Command:
kubectl logs -f <kubernetes cluster pod name> -n <namespace-name>
      #Example:
kubectl logs -f nosql-migrator-ccdbf549-6sjjg -n migrator
      #Output:
SLF4J(I): Connected with provider of type [org.apache.logging.slf4j.SLF4JServiceProvider]
[INFO] Configuration for migration:
{
  "source" : {
    "type" : "object_storage_oci",
    "format" : "json",
    "endpoint" : "us-ashburn-1",
    "namespace" : "idhkv1iewjzj",
    "bucket" : "Migrate_oci",
    "prefix" : "userSession",
    "useOKEWorkloadIdentity" : true
  },
  "sink" : {
    "type" : "nosqldb_cloud",
    "endpoint" : "us-ashburn-1",
    "table" : "users",
    "compartment" : "Training-NoSQL",
    "includeTTL" : true,
    "schemaInfo" : {
      "readUnits" : 100,
      "writeUnits" : 60,
      "storageSize" : 1,
      "useSourceSchema" : true
    },
    "useOKEWorkloadIdentity" : true,
    "writeUnitsPercent" : 90,
    "overwrite" : true,
    "requestTimeoutMs" : 5000
  },
  "abortOnError" : true,
  "migratorVersion" : "1.8.0"
}
[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] [cloud sink] : start loading DDLs
[INFO] [cloud sink] : completed loading DDLs
[INFO] [cloud sink] : start loading records
[INFO] [OCI OS source] : start parsing JSON records from object: users/userSession/Data/users_1_5_0.json
[INFO] [OCI OS source] : start parsing JSON records from object: users/userSession/Data/users_6_10_0.json
[INFO] migration completed.
Records provided by source=5, Records written to sink=5, Records failed=0, Records skipped=0.
Elapsed time: 0min 1sec 15ms
Migration completed.

    Note:

    You can iteratively run data migration as follows:

    1. Delete the nosql-migrator container using the following command:

      #Command:

kubectl delete -f <manifest file> -n <namespace-name>
      #Example:

kubectl delete -f migrator-deployment.yaml -n migrator
      #Output:

deployment.apps "nosql-migrator" deleted

    2. Update the migrator-config.json configuration input file.

    3. Delete and recreate the configmap using the commands from Step 2.

      It is not required to create the Docker registry secret, and manifest file again.

    4. Deploy the Migrator Docker image in the Kubernetes pod using the manifest file (Step 5).

Verification

To verify your data restore, log in to the Oracle NoSQL Database Cloud Service console. From the navigation bar, go to Databases > NoSQL Database. Select your compartment from the drop-down to view the users table. For the procedure to access the console, see Accessing the Service from the Infrastructure Console.

Migrate from Oracle NoSQL Database to OCI Object Storage Using Session Token Authentication

This example shows how to use Oracle NoSQL Database Migrator with session token authentication to copy data from Oracle NoSQL Database table to a JSON file in an OCI Object Storage bucket.

Use case

As a developer, you are exploring an option to back up Oracle NoSQL Database table data to OCI Object Storage (OCI OS). You want to use session token-based authentication.

In this demonstration, you will use the OCI Command Line Interface commands (CLI) to create a session token. You will manually create a Migrator configuration file and perform data migration.

Prerequisites

Note: Ensure that you have the privileges to write objects in the OCI OS bucket. For more details on setting the policies, see Write to Object Storage.

Procedure

To migrate from Oracle NoSQL Database table to a JSON file in the OCI OS bucket:

  1. Prepare the configuration file (in JSON format) with Oracle NoSQL Database source and JSON file in the OCI OS bucket sink. For templates, see Source Configuration Templates and Sink Configuration Templates.

    To use the session token authentication to access OCI OS bucket, set the useSessionToken parameter to true in the sink configuration template. Correspondingly, specify the config path in the credentials parameter and the profile name in the credentialsProfile parameter.

    {
   "source" : {
     "type" : "nosqldb",
     "storeName" : "kvstore",
     "helperHosts" : ["<hostname>:<port>"],
     "table" : "users",
     "includeTTL" : true,
     "requestTimeoutMs" : 5000
   },
   "sink" : {
     "type" : "object_storage_oci",
     "format" : "json",
     "endpoint" : "us-ashburn-1",
     "namespace" : "idhkv1iewjzj",
     "bucket" : "Migrate_oci",
     "prefix" : "userSession",
     "chunkSize" : 32,
     "compression" : "",
     "useSessionToken" : true,
     "credentials" : "$/home/.oci/config",
     "credentialsProfile" : "SESSIONPROFILE"
   },
   "abortOnError" : true,
   "migratorVersion" : "1.8.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 configuration file option. Use the --config or -c option to pass the configuration file as follows:

    ./runMigrator --config ./migrator-config.json

  4. The Migrator utility proceeds with data migration. A sample output is shown below.

    With useSessionToken parameter to true, the Migrator utility automatically authenticates using the session token. The Migrator utility copies your data from users table to a JSON file in the OCI OS bucket named 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] [OCI OS sink] : writing table schema to userSession/Schema/schema.ddl
[INFO] migration started
[INFO] Migration success for source users_6_10. read=2,written=2,failed=0

[INFO] Migration success for source users_1_5. read=3,written=3,failed=0

[INFO] Migration is successful for all the sources.
[INFO] migration completed.
Records provided by source=5, Records written to sink=5, Records failed=0,Records skipped=0.
Elapsed time: 0min 0sec 982ms
Migration completed.

    Note:

    Depending on the chunkSize parameter in the sink configuration template, the Migrator utility splits the source data into several JSON files in the same directory. In this example, the Migrator utility copies data to users_1_5_0.json and users_6_10_0.json files in Migrate_oci/userSession/Data directory.

    The source table schema is copied to schema.ddl file in Migrate_oci/userSession/Schema directory.

Verification

To verify 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 userSession directory in the Migrate_oci bucket. For the procedure to access the console, see Accessing the Service from the Infrastructure Console

Migrate from CSV file to Oracle NoSQL Database Cloud Service

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

Example

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

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

Procedure

To migrate data from course.csv file to Oracle NoSQL Database Cloud 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.8.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]/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 nosqldb_cloud option for the Sink configuration.

    Select the sink:
1) nosqldb

2) nosqldb_cloud

#? 2

  7. Supply the end point URL or region ID of your tenancy and select the authentication type for the Migrator utility to access Oracle NoSQL Database Cloud Service.

    Configuration for sink type=nosqldb_cloud
Enter endpoint URL or region ID of the Oracle NoSQL Database Cloud: us-ashburn-1
Select the authentication type:
1) credentials_file

2) instance_principal

3) delegation_token

4) session_token

5) oke_workload_identity

#? 1

  8. Based on the prompts from the utility, provide the name of the credentials file to be used for authentication, credentials profile, compartment ID, and the name of the table into which you want to copy data at the sink.

    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 []: ua_nosql
Enter table name: course

  9. 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]: y
Would you like to provide TTL reference time?(y/n) [n]: n

  10. 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]/mytable_schema.ddl

  11. Enter the throughput values and storage allocation for the sink table.

    Would you like to use on demand read and write units? (y/n) [n]: n
Enter read throughput in KB of new table: 100
Enter write throughput in KB of new table: 60
Enter storage size in GB of new table: 1
Enter percentage of table write units to be used for migration operation.
(1-100) [90]: 90

  12. Enter your choices to determine whether or not to overwrite the records if the table is already available at the sink. You can also add transformations to your source data.

    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
Would you like to continue migration if any data fails to be migrated?
 (y/n) [n]: n

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

    {
  "source" : {
    "type" : "file",
    "format" : "csv",
    "dataPath" : "[~/nosql-migrator]/course.csv",
    "hasHeader" : false,
    "csvOptions" : {
      "encoding" : "UTF-8",
      "trim" : false
    }
  },
  "sink" : {
    "type" : "nosqldb_cloud",
    "endpoint" : "us-ashburn-1",
    "table" : "course",
    "compartment" : "ua_nosql",
    "includeTTL" : true,
    "schemaInfo" : {
      "readUnits" : 100,
      "writeUnits" : 60,
      "storageSize" : 1,
      "schemaPath" : "[~/nosql-migrator]/mytable_schema.ddl"
    },
    "credentials" : "/home/<user>/.oci/config",
    "credentialsProfile" : "DEFAULT",
    "writeUnitsPercent" : 90,
    "overwrite" : true,
    "requestTimeoutMs" : 5000
  },
  "abortOnError" : true,
  "migratorVersion" : "1.8.0"
}

  14. 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, data migration is not initiated. The generated configuration file is saved in the Migrator directory. Use one of the following commands to run the Migration utility with configuration file option.

    ./runMigrator -c ./migrator-config.json

    ./runMigrator --config ./migrator-config.json

    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

  15. The NoSQL Database Migrator copies your data from the CSV file to Oracle NoSQL Database Cloud Service.

    creating source from given configuration:
source creation completed
creating sink from given configuration:
sink creation completed
creating migrator pipeline
[cloud sink] : start loading DDLs
[cloud sink] : executing DDL: create table if not exists course (id INTEGER, name STRING, location STRING, fees INTEGER, PRIMARY KEY(id)),limits: [100, 60, 1]
[cloud sink] : completed loading DDLs
migration started
[csv file source] : start parsing CSV records from file: course.csv
Migration success for source course. read=4,written=4,failed=0
Migration is successful for all the sources.
migration completed.
Records provided by source=4, Records written to sink=4, Records failed=0,Records skipped=0.
Elapsed time: 0min 0sec 395ms
Migration completed.

Verification

To verify the migration, you can log in to your Oracle NoSQL Database Cloud Service console and access the course table. The table contains 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.

Related Topics