Using Oracle NoSQL Database Migrator
Learn about Oracle NoSQL Database Migrator and how to use it for data migration.
Oracle NoSQL Database Migrator is a tool that enables you to migrate Oracle NoSQL tables from one data source to another. This tool can operate on tables in Oracle NoSQL Database Cloud Service, Oracle NoSQL Database on-premises and AWS S3. The Migrator tool supports several different data formats and physical media types. Supported data formats are JSON, Parquet, MongoDB-formatted JSON, DynamoDB-formatted JSON, and CSV files. Supported physical media types are files, OCI Object Storage, Oracle NoSQL Database on-premises, Oracle NoSQL Database Cloud Service and AWS S3.
This article has the following topics:
Related Topics
Overview
Oracle NoSQL Database Migrator lets you move Oracle NoSQL tables from one data source to another, such as Oracle NoSQL Database on-premises or cloud or even a simple JSON file.
There can be many situations that require you to migrate NoSQL tables from or to an Oracle NoSQL Database. For instance, a team of developers enhancing a NoSQL Database application may want to test their updated code in the local Oracle NoSQL Database Cloud Service (NDCS) instance using cloudsim. To verify all the possible test cases, they must set up the test data similar to the actual data. To do this, they must copy the NoSQL tables from the production environment to their local NDCS instance, the cloudsim environment. In another situation, NoSQL developers may need to move their application data from on-premise to the cloud and vice-versa, either for development or testing.
In all such cases and many more, you can use Oracle NoSQL Database Migrator to move your NoSQL tables from one data source to another, such as Oracle NoSQL Database on-premise or cloud or even a simple JSON file. You can also copy NoSQL tables from a MongoDB-formatted JSON input file, DynamoDB-formatted JSON input file (either stored in AWS S3 source or from files), or a CSV file into your NoSQL Database on-premises or cloud.
As depicted in the following figure, the NoSQL Database Migrator utility acts as a connector or pipe between the data source and the target (referred to as the sink). In essence, this utility exports data from the selected source and imports that data into the sink. This tool is table-oriented, that is, you can move the data only at the table level. A single migration task operates on a single table and supports migration of table data from source to sink in various data formats.
Terminology used with Oracle NoSQL Database Migrator
Learn about the different terms used in the above diagram, in detail.
- Source: An entity from where the NoSQL tables are exported for migration. Some examples of sources are Oracle NoSQL Database on-premise or cloud, JSON file, MongoDB-formatted JSON file, DynamoDB-formatted JSON file, and CSV files.
- Sink: An entity that imports the NoSQL tables from NoSQL Database Migrator. Some examples for sinks are Oracle NoSQL Database on-premise or cloud and JSON file.
The NoSQL Database Migrator tool supports different types of sources and sinks (that is physical media or repositories of data) and data formats (that is how the data is represented in the source or sink). Supported data formats are JSON, Parquet, MongoDB-formatted JSON, DynamoDB-formatted JSON, and CSV files. Supported source and sink types are files, OCI Object Storage, Oracle NoSQL Database on-premise, and Oracle NoSQL Database Cloud Service.
- Migration Pipe: The data from a source will be transferred to the sink by NoSQL Database Migrator. This can be visualized as a Migration Pipe.
- Transformations: You can add rules to modify the NoSQL table data in the migration pipe. These rules are called Transformations. Oracle NoSQL Database Migrator allows data transformations at the top-level fields or columns only. It does not let you transform the data in the nested fields. Some examples of permitted transformations are:
- Drop or ignore one or more columns,
- Rename one or more columns, or
- Aggregate several columns into a single field, typically a JSON field.
- Configuration File : A configuration file is where you define all the parameters required for the migration activity in a JSON format. Later, you pass this configuration file as a single parameter to the
runMigrator
command from the CLI. A typical configuration file format looks like as shown below.{ "source": { "type" : <source type>, //source-configuration for type. See Source Configuration Templates . }, "sink": { "type" : <sink type>, //sink-configuration for type. See Sink Configuration Templates . }, "transforms" : { //transforms configuration. See Transformation Configuration Templates . }, "migratorVersion" : "<migrator version>", "abortOnError" : <true|false> }
Group Parameters Mandatory (Y/N) Purpose Supported Values source
type
Y Represents the source from which to migrate the data. The source provides data and metadata (if any) for migration. To know the type
value for each source, see Supported Sources and Sinks.source
source-configuration for type Y Defines the configuration for the source. These configuration parameters are specific to the type of source selected above. See Source Configuration Templates . for the complete list of configuration parameters for each source type. sink
type
Y Represents the sink to which to migrate the data. The sink is the target or destination for the migration. To know the type
value for each source, see Supported Sources and Sinks.sink
sink-configuration for type Y Defines the configuration for the sink. These configuration parameters are specific to the type of sink selected above. See Sink Configuration Templates for the complete list of configuration parameters for each sink type. transforms
transforms configuration N Defines the transformations to be applied to the data in the migration pipe. See Transformation Configuration Templates for the complete list of transformations supported by the NoSQL Data Migrator. - migratorVersion
N Version of the NoSQL Data Migrator - - abortOnError
N Specifies whether to stop the migration activity in case of any error or not.
The default value is true indicating that the migration stops whenever it encounters a migration error.
If you set this value to false, the migration continues even in case of failed records or other migration errors. The failed records and migration errors will be logged as WARNINGs on the CLI terminal.
true, false Note:
As JSON file is case-sensitive, all the parameters defined in the configuration file are case-sensitive unless specified otherwise.
Supported Sources and Sinks
This topic provides the list of the sources and sinks supported by the Oracle NoSQL Database Migrator.
You can use any combination of a valid source and sink from this table for the migration activity. However, you must ensure that at least one of the ends, that is, source or sink must be an Oracle NoSQL product. You can not use the NoSQL Database Migrator to move the NoSQL table data from one file to another.
Type |
Format |
Valid Source | Valid Sink |
---|---|---|---|
Oracle NoSQL Database |
NA | Y | Y |
Oracle NoSQL Database Cloud Service |
NA | Y | Y |
File system |
JSON |
Y | Y |
MongoDB JSON |
Y | N | |
DynamoDB JSON |
Y | N | |
Parquet( |
N | Y | |
CSV |
Y | N | |
OCI Object Storage |
JSON |
Y | Y |
MongoDB JSON |
Y | N | |
Parquet( |
N | Y | |
CSV |
Y | N | |
AWS S3 |
DynamoDB JSON |
Y | N |
Note:
Many configuration parameters are common across the source and sink configuration. For ease of reference, the description for such parameters is repeated for each source and sink in the documentation sections, which explain configuration file formats for various types of sources and sinks. In all the cases, the syntax and semantics of the parameters with the same name are identical.Source and Sink Security
Some of the source and sink types have optional or mandatory security information for authentication purposes.
All sources and sinks that use services in the Oracle Cloud Infrastructure (OCI) can use certain parameters for providing optional security information. This information can be provided using an OCI configuration file or Instance Principal.
Oracle NoSQL Database sources and sinks require mandatory security information if the installation is secure and uses an Oracle Wallet-based authentication. This information can be provided by adding a jar file to the <MIGRATOR_HOME>/lib
directory.
Wallet-based Authentication
If an Oracle NoSQL Database installation uses Oracle Wallet-based authentication, you need an additional jar file that is part of the EE installation. For more information, see Oracle Wallet.
Without this jar file, you will get the following error message:
Could not find kvstore-ee.jar in lib directory. Copy kvstore-ee.jar to lib directory.
kvstore-ee.jar
file from your EE server package to the <MIGRATOR_HOME>/lib
directory. <MIGRATOR_HOME> is the nosql-migrator-M.N.O/
directory created by extracting the Oracle NoSQL Database Migrator package and M.N.O represent the software release.major.minor numbers. For example, nosql-migrator-1.1.0/lib
.
Note:
The wallet-based authentication is supported ONLY in the Enterprise Edition (EE) of Oracle NoSQL Database.Authenticating with Instance Principals
Instance principals is an IAM service feature that enables instances to be authorized actors (or principals) that can perform actions on service resources. Each compute instance has its own identity, and it authenticates using the certificates added to it.
Oracle NoSQL Database Migrator provides an option to connect to a NoSQL cloud and OCI Object Storage sources and sinks using instance principal authentication. It is only supported when the NoSQL Database Migrator tool is used within an OCI compute instance, for example, the NoSQL Database Migrator tool running in a VM hosted on OCI. To enable this feature use the useInstancePrincipal attribute of the NoSQL cloud source and sink configuration file. For more information on configuration parameters for different types of sources and sinks, see Source Configuration Templates and Sink Configuration Templates .
For more information on instance principals, see Calling Services from an Instance.
Workflow for Oracle NoSQL Database Migrator
Learn about the various steps involved in using the Oracle NoSQL Database Migrator utility for migrating your NoSQL data.
The high level flow of tasks involved in using NoSQL Database Migrator is depicted in the below figure.
Download the NoSQL Data Migrator Utility
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
- Identify Sink Table Schema: If the sink is Oracle NoSQL Database on-premise or cloud, you must identify the schema for the sink table and ensure that the source data matches with the target schema. If required, use transformations to map the source data to the sink table.
- Default Schema: NoSQL Database Migrator provides an option to create a table with the default schema without the need to predefine the schema for the table. This is useful primarily when loading JSON source files into Oracle NoSQL Database.
If the source is a MongoDB-formatted JSON file, the default schema for the table will be as follows:
CREATE TABLE IF NOT EXISTS <tablename>(ID STRING, DOCUMENT JSON,PRIMARY KEY(SHARD(ID))
Where:
— tablename = value provided for the table attribute in the configuration.
— ID = _id value from each document of the mongoDB exported JSON source file.
— DOCUMENT = For each document in the mongoDB exported file, the contents excluding the _id field are aggregated into the DOCUMENT column.
If the source is a DynamoDB-formatted JSON file, the default schema for the table will be as follows:CREATE TABLE IF NOT EXISTS <TABLE_NAME>(DDBPartitionKey_name DDBPartitionKey_type, [DDBSortKey_name DDBSortKey_type],DOCUMENT JSON, PRIMARY KEY(SHARD(DDBPartitionKey_name),[DDBSortKey_name]))
Where:
— TABLE_NAME = value provided for the sink table in the configuration
— DDBPartitionKey_name = value provided for the partition key in the configuration
— DDBPartitionKey_type = value provided for the data type of the partition key in the configuration
— DDBSortKey_name = value provided for the sort key in the configuration if any
— DDBSortKey_type = value provided for the data type of the sort key in the configuration if any
— DOCUMENT = All attributes except the partition and sort key of a Dynamo DB table item aggregated into a NoSQL JSON column
If the source format is a CSV file, a default schema is not supported for the target table. You can create a schema file with a table definition containing the same number of columns and data types as the source CSV file. For more details on the Schema file creation, see Providing Table Schema.
For all the other sources, the default schema will be as follows:CREATE TABLE IF NOT EXISTS <tablename> (ID LONG GENERATED ALWAYS AS IDENTITY, DOCUMENT JSON, PRIMARY KEY(ID))
Where:
— tablename = value provided for the table attribute in the configuration.
— ID = An auto-generated LONG value.
— DOCUMENT = The JSON record provided by the source is aggregated into the DOCUMENT column.Note:
If the _id value is not provided as a string in the MongoDB-formatted JSON file, NoSQL Database Migrator converts it into a string before inserting it into the default schema.
- Default Schema: NoSQL Database Migrator provides an option to create a table with the default schema without the need to predefine the schema for the table. This is useful primarily when loading JSON source files into Oracle NoSQL Database.
- Providing Table Schema: NoSQL Database Migrator allows the source to provide schema definitions for the table data using schemaInfo attribute. The schemaInfo attribute is available in all the data sources that do not have an implicit schema already defined. Sink data stores can choose any one of the following options.
- Use the default schema defined by the NoSQL Database Migrator.
- Use the source-provided schema.
- Override the source-provided schema by defining its own schema. For example, if you want to transform the data from the source schema to another schema, you need to override the source-provided schema and use the transformation capability of the NoSQL Database Migrator tool.
The table schema file, for example,mytable_schema.ddl
can include table DDL statements. The NoSQL Database Migrator tool executes this table schema file before starting the migration. The migrator tool supports no more than one DDL statement per line in the schema file. For example,CREATE TABLE IF NOT EXISTS(id INTEGER, name STRING, age INTEGER, PRIMARY KEY(SHARD(ID)))
Note:
Migration will fail if the table is present at the sink and the DDL in theschemaPath
is different than the table. - Create Sink Table: Once you identify the sink table
schema, create the sink table either through the Admin CLI or using the
schemaInfo
attribute of the sink configuration file. See Sink Configuration Templates .Note:
If the source is a CSV file, create a file with the DDL commands for the schema of the target table. Provide the file path in schemaInfo.schemaPath parameter of the sink configuration file.
Migrating TTL Metadata for Table Rows
Note:
The support for migrating TTL metadata for table rows is only available for Oracle NoSQL Database and Oracle NoSQL Database Cloud Service.Exporting TTL metadata
_metadata
JSON object for each exported row. The NoSQL Database Migrator exports the expiration time for each row as the number of milliseconds since the UNIX epoch (Jan 1st, 1970). For example,//Row 1
{
"id" : 1,
"name" : "xyz",
"age" : 45,
"_metadata" : {
"expiration" : 1629709200000 //Row Expiration time in milliseconds
}
}
//Row 2
{
"id" : 2,
"name" : "abc",
"age" : 52,
"_metadata" : {
"expiration" : 1629709400000 //Row Expiration time in milliseconds
}
}
//Row 3 No Metadata for below row as it will not expire
{
"id" : 3,
"name" : "def",
"age" : 15
}
Importing TTL metadata
You can optionally import TTL metadata using a configuration parameter, includeTTL. The import operation handles the following use cases when migrating table rows containing TTL metadata. These use-cases are applicable only when the includeTTL configuration parameter is specified.
- Use-case 1: No TTL metadata information is present in the importing table row.
When you import a JSON source file produced from an external source or exported using earlier versions of the migrator, the importing row does not have TTL information. But since the includeTTL configuration parameter is equal to
true
, the migrator set the TTL=0 for the table row, which means the importing table row never expires. - Use-case 2: TTL value of the source table row is expired relative to the Reference Time when the table row gets imported.
When you export a table row to a file and try to import it after the expiration time of the table row, the table row is ignored and is not written into the store.
- Use-case 3: TTL value of the source table row is not expired relative to the Reference Time when the table row gets imported.
When you export a table row to a file and try to import it before the expiration time of the table row, the table row gets imported with a TTL value. But the new TTL value for the table row may not be equal to exported TTL value because of the integer hour and day window constraints in the TimeToLive class. For example,
Exported table row{ "id" : 8, "name" : "xyz", "_metadata" : { "expiration" : 1629709200000 //Monday, August 23, 2021 9:00:00 AM in UTC } }
The reference time while importing is 1629707962582, which is Monday, August 23, 2021 8:39:22.582 AM.
Imported table row{ "id" : 8, "name" : "xyz", "_metadata" : { "ttl" : 1629712800000 //Monday, August 23, 2021 10:00:00 AM UTC } }
Importing data to a sink with an IDENTITY column
You can import the data from a valid source to a sink table (On-premises/Cloud Services) with an IDENTITY column. You create the IDENTITY column as either GENERATED ALWAYS AS IDENTITY or GENERATED BY DEFAULT AS IDENTITY. For more information on table creation with an IDENTITY column, see Creating Tables With an IDENTITY Column in the SQL Reference Guide.
Before importing the data, make sure that the Oracle NoSQL Database table at the sink is empty if it exists. If there is pre-existing data in the sink table, migration can lead to issues such as overwriting existing data in the sink table or skipping source data during the import.
Sink table with IDENTITY column as GENERATED ALWAYS AS IDENTITY
Consider a sink table with the IDENTITY column created as GENERATED ALWAYS AS IDENTITY. The data import is dependent on whether or not the source supplies the values to the IDENTITY column and ignoreFields transformation parameter in the configuration file.
For example, you want to import data from a JSON file source to the Oracle NoSQL Database table as the sink. The schema of the sink table is:
CREATE TABLE IF NOT EXISTS migrateID(ID INTEGER GENERATED ALWAYS AS IDENTITY, name STRING, course STRING, PRIMARY KEY
(ID))
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
|
Create/generate the configuration file. |
Data migration is successful. IDENTITY column values are auto-generated. Migrated data in Oracle NoSQL Database sink table
|
CASE 2: Source data supplies values for the IDENTITY field of the sink table. Example: JSON source file
|
Create/generate the configuration file. You provide an ignoreFields transformation for the ID column in the sink configuration template.
|
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 :
|
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))
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
|
Create/generate the configuration file. |
Data migration is successful. IDENTITY column values are auto-generated. Migrated data in Oracle NoSQL Database sink table
migrateID :
|
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
|
Create/generate the configuration file. You provide an ignoreFields transformation for the ID column in the sink configuration template (Recommended).
|
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 :
|
You create/generate the configuration file without the ignoreFields transformation for the IDENTITY column. |
Data migration is successful. The supplied 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 :
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:
Output:
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:
This will start the sequence at 4. Now when you insert rows to the sink table without supplying the ID values, the sequence generator auto-generates the ID values from 4 onwards averting the duplication of the IDs. |
For more details on the transformation configuration parameters, see the topic Transformation Configuration Templates.
Run the runMigrator
command
The runMigrator
executable file is available in the extracted NoSQL Database Migrator files. You must install Java 11 or higher version and bash on your system to successfully run the runMigrator
command.
runMigrator
command in two ways:
- By creating the configuration file using the runtime options of the
runMigrator
command as shown below.[~]$ ./runMigrator configuration file is not provided. Do you want to generate configuration? (y/n) [n]: y ... ...
- When you invoke the
runMigrator
utility, it provides a series of runtime options and creates the configuration file based on your choices for each option. - After the utility creates the configuration file, you have a choice to either proceed with the migration activity in the same run or save the configuration file for a future migration.
- Irrespective of your decision to proceed or defer the migration activity with the generated configuration file, the file will be available for edits or customization to meet your future requirements. You can use the customized configuration file for migration later.
- When you invoke the
- 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 therunMigrator
command with the-c
or--config
option. For any help with the source and sink configuration parameters, see Oracle NoSQL Database Migrator Reference.[~]$ ./runMigrator -c </path/to/the/configuration/json/file>
Logging Migrator Progress
NoSQL Database Migrator tool provides options, which enables trace, debugging, and progress messages to be printed to standard output or to a file. This option can be useful in tracking the progress of migration operation, particularly for very large tables or data sets.
- Log Levels
To control the logging behavior through the NoSQL Database Migrator tool, pass the --log-level or -l run time parameter to the
runMigrator
command. You can specify the amount of log information to write by passing the appropriate log level value.$./runMigrator --log-level <loglevel>
Example:$./runMigrator --log-level debug
Table - Supported Log Levels for NoSQL Database Migrator
Log Level Description warning Prints errors and warnings. info (default) Prints the progress status of data migration such as validating source, validating sink, creating tables, and count of number of data records migrated. debug Prints additional debug information. all Prints everything. This level turns on all levels of logging. - Log File:
You can specify the name of the log file using --log-file or -f parameter. If --log-file is passed as run time parameter to the
runMigrator
command, the NoSQL Database Migrator writes all the log messages to the file else to the standard output.$./runMigrator --log-file <log file name>
Example:$./runMigrator --log-file nosql_migrator.log
Use Case Demonstrations for Oracle NoSQL Database Migrator
Learn how to perform data migration using the Oracle NoSQL Database Migrator for specific use cases. You can find detailed systematic instructions with code examples to perform migration in each of the use cases.
This article has the following topics:
Migrate from Oracle NoSQL Database Cloud Service to a JSON file
This example shows how to use the Oracle NoSQL Database Migrator to copy data and the schema definition of a NoSQL table from Oracle NoSQL Database Cloud Service (NDCS) to a JSON file.
Use Case
An organization decides to train a model using the Oracle NoSQL Database Cloud Service (NDCS) data to predict future behaviors and provide personalized recommendations. They can take a periodic copy of the NDCS tables' data to a JSON file and apply it to the analytic engine to analyze and train the model. Doing this helps them separate the analytical queries from the low-latency critical paths.
Example
For the demonstration, let us look at how to migrate the data and schema definition of a NoSQL table calledmyTable
from NDCS to a JSON
file.- Identify the source and sink for the migration.
- Source: Oracle NoSQL Database Cloud Service
- Sink: JSON file
- Identify your OCI cloud credentials and capture them in the OCI config file. Save the config file in
/home/.oci/config
. See Acquiring Credentials.[DEFAULT] tenancy=ocid1.tenancy.oc1.... user=ocid1.user.oc1.... fingerprint= 43:d1:.... key_file=</fully/qualified/path/to/the/private/key/> pass_phrase=<passphrase>
- Identify the region endpoint and compartment name
for your Oracle NoSQL Database Cloud Service.
- endpoint:
us-phoenix-1
- compartment:
developers
- endpoint:
myTable
from Oracle NoSQL Database Cloud Service to a JSON file:
To validate the migration, you can open the JSON Sink files and view the schema and data.
-- Exported myTable Data
[~/nosqlMigrator]$cat myTableJSON
{
"id" : 10,
"document" : {
"course" : "Computer Science",
"name" : "Neena",
"studentid" : 105
}
}
{
"id" : 3,
"document" : {
"course" : "Computer Science",
"name" : "John",
"studentid" : 107
}
}
{
"id" : 4,
"document" : {
"course" : "Computer Science",
"name" : "Ruby",
"studentid" : 100
}
}
{
"id" : 6,
"document" : {
"course" : "Bio-Technology",
"name" : "Rekha",
"studentid" : 104
}
}
{
"id" : 7,
"document" : {
"course" : "Computer Science",
"name" : "Ruby",
"studentid" : 100
}
}
{
"id" : 5,
"document" : {
"course" : "Journalism",
"name" : "Rani",
"studentid" : 106
}
}
{
"id" : 8,
"document" : {
"course" : "Computer Science",
"name" : "Tom",
"studentid" : 103
}
}
{
"id" : 9,
"document" : {
"course" : "Computer Science",
"name" : "Peter",
"studentid" : 109
}
}
{
"id" : 1,
"document" : {
"course" : "Journalism",
"name" : "Tracy",
"studentid" : 110
}
}
{
"id" : 2,
"document" : {
"course" : "Bio-Technology",
"name" : "Raja",
"studentid" : 108
}
}
-- Exported myTable Schema
[~/nosqlMigrator]$cat myTableSchema
CREATE TABLE IF NOT EXISTS myTable (id INTEGER, document JSON, PRIMARY KEY(SHARD(id)))
Migrate from Oracle NoSQL Database On-Premise to Oracle NoSQL Database Cloud Service
This example shows how to use the Oracle NoSQL Database Migrator to copy data and the schema definition of a NoSQL table from Oracle NoSQL Database to Oracle NoSQL Database Cloud Service (NDCS).
Use Case
As a developer, you are exploring options to avoid the overhead of managing the resources, clusters, and garbage collection for your existing NoSQL Database KVStore workloads. As a solution, you decide to migrate your existing on-premise KVStore workloads to Oracle NoSQL Database Cloud Service because NDCS manages them automatically.
Example
For the demonstration, let us look at how to migrate the data and schema definition of a NoSQL table calledmyTable
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.- Identify the source and sink for the migration.
- Source: Oracle NoSQL Database
- Sink: Oracle NoSQL Database Cloud Service
- Identify your OCI cloud credentials and capture them
in the OCI config file. Save the config file in
/home/.oci/config
. See Acquiring Credentials in Using Oracle NoSQL Database Cloud Service.[DEFAULT] tenancy=ocid1.tenancy.oc1.... user=ocid1.user.oc1.... fingerprint= 43:d1:.... key_file=</fully/qualified/path/to/the/private/key/> pass_phrase=<passphrase>
- Identify the region endpoint and compartment name
for your Oracle NoSQL Database Cloud Service.
- endpoint:
us-phoenix-1
- compartment:
developers
- endpoint:
- Identify the following details for the on-premise KVStore:
- storeName:
kvstore
- helperHosts:
<hostname>:5000
- table:
myTable
- storeName:
myTable
from
NoSQL Database KVStore to NDCS:
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.
- Identify the source and sink for the migration.
- Source: JSON source file.
SampleData.json
is the source file. It contains multiple JSON documents with one document per line, delimited by a new line character.{"id":6,"val_json":{"array":["q","r","s"],"date":"2023-02-04T02:38:57.520Z","nestarray":[[1,2,3],[10,20,30]],"nested":{"arrayofobjects":[{"datefield":"2023-03-04T02:38:57.520Z","numfield":30,"strfield":"foo54"},{"datefield":"2023-02-04T02:38:57.520Z","numfield":56,"strfield":"bar23"}],"nestNum":10,"nestString":"bar"},"num":1,"string":"foo"}} {"id":3,"val_json":{"array":["g","h","i"],"date":"2023-02-02T02:38:57.520Z","nestarray":[[1,2,3],[10,20,30]],"nested":{"arrayofobjects":[{"datefield":"2023-02-02T02:38:57.520Z","numfield":28,"strfield":"foo3"},{"datefield":"2023-02-02T02:38:57.520Z","numfield":38,"strfield":"bar"}],"nestNum":10,"nestString":"bar"},"num":1,"string":"foo"}} {"id":7,"val_json":{"array":["a","b","c"],"date":"2023-02-20T02:38:57.520Z","nestarray":[[1,2,3],[10,20,30]],"nested":{"arrayofobjects":[{"datefield":"2023-01-20T02:38:57.520Z","numfield":28,"strfield":"foo"},{"datefield":"2023-01-22T02:38:57.520Z","numfield":38,"strfield":"bar"}],"nestNum":10,"nestString":"bar"},"num":1,"string":"foo"}} {"id":4,"val_json":{"array":["j","k","l"],"date":"2023-02-03T02:38:57.520Z","nestarray":[[1,2,3],[10,20,30]],"nested":{"arrayofobjects":[{"datefield":"2023-02-03T02:38:57.520Z","numfield":28,"strfield":"foo"},{"datefield":"2023-02-03T02:38:57.520Z","numfield":38,"strfield":"bar"}],"nestNum":10,"nestString":"bar"},"num":1,"string":"foo"}}
- Sink: Oracle NoSQL Database Cloud Service.
- Source: JSON source file.
- Identify your OCI cloud credentials and capture them in the configuration file. Save the config file in
/home/user/.oci/config
. For more details, see Acquiring Credentials in Using Oracle NoSQL Database Cloud Service.[DEFAULT] tenancy=ocid1.tenancy.oc1.... user=ocid1.user.oc1.... fingerprint= 43:d1:.... region=us-ashburn-1 key_file=</fully/qualified/path/to/the/private/key/> pass_phrase=<passphrase>
- Identify the region endpoint and compartment name for your Oracle NoSQL Database Cloud Service.
- endpoint:
us-ashburn-1
- compartment:
Training-NoSQL
- endpoint:
- Identify the following details for the JSON source file:
-
schemaPath:
<absolute path to the schema definition file containing DDL statements for the NoSQL table at the sink>
.In this example, the DDL file isschema_json.ddl
.create table Migrate_JSON (id INTEGER, val_json JSON, PRIMARY KEY(id));
The Oracle NoSQL Database Migrator provides an option to create a table with the default schema if the
schemaPath
is not provided. For more details, see Identify the Source and Sink topic in the Workflow for Oracle NoSQL Database Migrator. - Datapath:
<absolute path to a file or directory containing the JSON data for migration>
.
-
SampleData.json
to Oracle NoSQL Database Cloud Service, perform the following:
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
Figure - Oracle NoSQL Database Cloud Service Console Table Data
Migrate from MongoDB JSON file to an Oracle NoSQL Database Cloud Service
This example shows how to use the Oracle NoSQL Database Migrator to copy Mongo-DB Formatted Data to the Oracle NoSQL Database Cloud Service (NDCS).
Use Case
After evaluating multiple options, an organization finalizes Oracle NoSQL Database Cloud Service as its NoSQL Database platform. As its NoSQL tables and data are in MongoDB, they are looking for a way to migrate those tables and data to Oracle NDCS.
You can copy a file or directory containing the MongoDB exported JSON data for migration by specifying the file or directory in the source configuration template.
{"_id":0,"name":"Aimee Zank","scores":[{"score":1.463179736705023,"type":"exam"},{"score":11.78273309957772,"type":"quiz"},{"score":35.8740349954354,"type":"homework"}]}
{"_id":1,"name":"Aurelia Menendez","scores":[{"score":60.06045071030959,"type":"exam"},{"score":52.79790691903873,"type":"quiz"},{"score":71.76133439165544,"type":"homework"}]}
{"_id":2,"name":"Corliss Zuk","scores":[{"score":67.03077096065002,"type":"exam"},{"score":6.301851677835235,"type":"quiz"},{"score":66.28344683278382,"type":"homework"}]}
{"_id":3,"name":"Bao Ziglar","scores":[{"score":71.64343899778332,"type":"exam"},{"score":24.80221293650313,"type":"quiz"},{"score":42.26147058804812,"type":"homework"}]}
{"_id":4,"name":"Zachary Langlais","scores":[{"score":78.68385091304332,"type":"exam"},{"score":90.2963101368042,"type":"quiz"},{"score":34.41620148042529,"type":"homework"}]}
MongoDB supports two types of extensions to the JSON format of files, Canonical mode and Relaxed mode. You can supply the MongoDB-formatted JSON file that is generated using the mongoexport tool in either Canonical or Relaxed mode. Both the modes are supported by the NoSQL Database Migrator for migration.
For more information on the MongoDB Extended JSON (v2) file, See mongoexport_formats.
For more information on the generation of MongoDB-formatted JSON file, See mongoexport.
Example
For the demonstration, let us look at how to migrate a MongoDB-formatted JSON file to NDCS. We will use a manually created configuration file for this example.- Identify the source and sink for the migration.
- Source: MongoDB-Formatted JSON File
- Sink: Oracle NoSQL Database Cloud Service
- Extract the data from Mongo DB using the mongoexport utility. See mongoexport for more information.
- Create a NoSQL table in the sink with a table
schema that matches the data in the Mongo-DB-formatted JSON
file. As an alternative, you can instruct the NoSQL Database Migratorto create a table with the
default schema structure by setting the
defaultSchema
attribute to true.Note:
For a MongoDB-Formatted JSON source, the default schema for the table will be as:CREATE TABLE IF NOT EXISTS <tablename>(ID STRING, DOCUMENT JSON,PRIMARY KEY(SHARD(ID))
Where:tablename
= value of the table config.ID
=_id
value from the mongoDB exported JSON source file.DOCUMENT
= The entire contents of the mongoDB exported JSON source file is aggregated into theDOCUMENT
column excluding the_id
field.
- Identify your OCI cloud credentials and capture them
in the OCI config file. Save the config file in
/home/.oci/config
.See Acquiring Credentials in Using Oracle NoSQL Database Cloud Service.[DEFAULT] tenancy=ocid1.tenancy.oc1.... user=ocid1.user.oc1.... fingerprint= 43:d1:.... key_file=</fully/qualified/path/to/the/private/key/> pass_phrase=<passphrase>
- Identify the region endpoint and compartment name
for your Oracle NoSQL Database Cloud Service.
- endpoint:
us-phoenix-1
- compartment:
developers
- endpoint:
To migrate the MongoDB-formatted JSON data to the Oracle NoSQL Database Cloud Service:
To validate the migration, you
can login to your NDCS console and verify that
myTable
is created with the source
data.
Migrate from DynamoDB JSON file to Oracle NoSQL Database
This example shows how to use the Oracle NoSQL Database Migrator to copy DynamoDB JSON file to Oracle NoSQL Database.
Use Case:
After evaluating multiple options, an organization finalizes Oracle NoSQL Database over DynamoDB database. The organization wants to migrate their tables and data from DynamoDB to Oracle NoSQL Database (On-premises).
See Mapping of DynamoDB table to Oracle NoSQL table for more details.
You can migrate a file or directory containing the DynamoDB exported JSON data from a file system by specifying the path in the source configuration template.
{"Item":{"Id":{"N":"101"},"Phones":{"L":[{"L":[{"S":"555-222"},{"S":"123-567"}]}]},"PremierCustomer":{"BOOL":false},"Address":{"M":{"Zip":{"N":"570004"},"Street":{"S":"21 main"},"DoorNum":{"N":"201"},"City":{"S":"London"}}},"FirstName":{"S":"Fred"},"FavNumbers":{"NS":["10"]},"LastName":{"S":"Smith"},"FavColors":{"SS":["Red","Green"]},"Age":{"N":"22"}}}
{"Item":{"Id":{"N":"102"},"Phones":{"L":[{"L":[{"S":"222-222"}]}]},"PremierCustomer":{"BOOL":false},"Address":{"M":{"Zip":{"N":"560014"},"Street":{"S":"32 main"},"DoorNum":{"N":"1024"},"City":{"S":"Wales"}}},"FirstName":{"S":"John"},"FavNumbers":{"NS":["10"]},"LastName":{"S":"White"},"FavColors":{"SS":["Blue"]},"Age":{"N":"48"}}}
You copy the exported DynamoDB table data from AWS S3 storage to a local mounted file system.
Example:
For this demonstration, you will learn how to migrate a DynamoDB JSON file to Oracle NoSQL Database(On-premises). You will use a manually created configuration file for this example.
Prerequisites
- Identify the source and sink for the
migration.
- Source: DynamoDB JSON File
- Sink: Oracle NoSQL Database (On-premises)
- In order to import DynamoDB table data to
Oracle NoSQL Database, you must first export the
DynamoDB table to S3. Refer to steps provided in
Exporting DynamoDB
table data to Amazon S3 to export your
table. While exporting, you select the format as
DynamoDB JSON. The exported data contains
DynamoDB table data in multiple
gzip
files as shown below./ 01639372501551-bb4dd8c3 |-- 01639372501551-bb4dd8c3 ==> exported data prefix |----data |------sxz3hjr3re2dzn2ymgd2gi4iku.json.gz ==>table data |----manifest-files.json |----manifest-files.md5 |----manifest-summary.json |----manifest-summary.md5 |----_started
- You must download the files from AWS s3. The
structure of the files after the download will be
as shown
below.
download-dir/01639372501551-bb4dd8c3 |----data |------sxz3hjr3re2dzn2ymgd2gi4iku.json.gz ==>table data |----manifest-files.json |----manifest-files.md5 |----manifest-summary.json |----manifest-summary.md5 |----_started
Procedure
- Prepare the configuration file (in JSON format) with the identified source and sink details.See Source Configuration Templates and Sink Configuration Templates .
You can choose one of the following two options.
- Option 1: Importing DynamoDB table a as JSON document using default schema config.
Here the
defaultSchema
isTRUE
and so the migrator creates the default schema at the sink. You need to specify theDDBPartitionKey
and the corresponding NoSQL column type. Else an error is thrown.{ "source" : { "type" : "file", "format" : "dynamodb_json", "dataPath" : "<complete/path/to/the/DynamoDB/Formatted/JSON/file>" }, "sink" : { "type" : "nosqldb", "table" : "<table_name>", "storeName" : "kvstore", "helperHosts" : ["<hostname>:5000"] "schemaInfo" : { "defaultSchema" : true, "DDBPartitionKey" : "<PrimaryKey:Datatype>", }, }, "abortOnError" : true, "migratorVersion" : "1.0.0" }
For a DynamoDB JSON source, the default schema for the table will be as shown below:CREATE TABLE IF NOT EXISTS <TABLE_NAME>(DDBPartitionKey_name DDBPartitionKey_type, [DDBSortKey_name DDBSortKey_type], DOCUMENT JSON, PRIMARY KEY(SHARD(DDBPartitionKey_name),[DDBSortKey_name]))
Where
TABLE_NAME = value provided for the sink 'table' in the configuration
DDBPartitionKey_name = value provided for the partiiton key in the configuration
DDBPartitionKey_type = value provided for the data type of the partition key in the configuration
DDBSortKey_name = value provided for the sort key in the configuration if any
DDBSortKey_type = value provided for the data type of the sort key in the configuration if any
DOCUMENT = All attributes except the partition and sort key of a Dynamo DB table item aggregated into a NoSQL JSON column
- Option 2: Importing DynamoDB table as fixed columns using a user-supplied schema file.
Here the
defaultSchema
isFALSE
and you specify the schemaPath as a file containing your DDL statement. See Mapping of DynamoDB types to Oracle NoSQL types for more details.Note:
If the Dynamo DB table has a data type that is not supported in NoSQL, the migration fails.A sample schema file is shown below.CREATE TABLE IF NOT EXISTS sampledynDBImp (AccountId INTEGER,document JSON, PRIMARY KEY(SHARD(AccountId)));
The schema file is used to create the table at the sink as part of the migration. As long as the primary key data is provided, the input JSON record will be inserted, otherwise it throws an error.Note:
If the input data does not contain a value for a particular column(other than the primary key) then the column default value will be used. The default value should be part of the column definition while creating the table. For exampleid INTEGER not null default 0
. If the column does not have a default definition then SQL NULL is inserted if no values are provided for the column.{ "source" : { "type" : "file", "format" : "dynamodb_json", "dataPath" : "<complete/path/to/the/DynamoDB/Formatted/JSON/file>" }, "sink" : { "type" : "nosqldb", "table" : "<table_name>", "schemaInfo" : { "defaultSchema" : false, "readUnits" : 100, "writeUnits" : 60, "schemaPath" : "<full path of the schema file with the DDL statement>", "storageSize" : 1 }, "storeName" : "kvstore", "helperHosts" : ["<hostname>:5000"] }, "abortOnError" : true, "migratorVersion" : "1.0.0" }
- Option 1: Importing DynamoDB table a as JSON document using default schema config.
- Open the command prompt and navigate to the directory where you extracted the NoSQL Database Migrator utility.
- Run the
runMigrator
command by passing the configuration file using the--config
or-c
option.[~/nosqlMigrator/nosql-migrator-1.0.0]$./runMigrator --config <complete/path/to/the/JSON/config/file>
- The utility proceeds with the data migration, as shown
below.
Records provided by source=7.., Records written to sink=7, Records failed=0, Records skipped=0. Elapsed time: 0 min 2sec 50ms Migration completed.
Validation
java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore
desc <table_name>
SELECT * from <table_name>
Migrate from DynamoDB JSON file in AWS S3 to an Oracle NoSQL Database Cloud Service
This example shows how to use the Oracle NoSQL Database Migrator to copy DynamoDB JSON file stored in an AWS S3 store to the Oracle NoSQL Database Cloud Service (NDCS).
Use Case:
After evaluating multiple options, an organization finalizes Oracle NoSQL Database Cloud Service over DynamoDB database. The organization wants to migrate their tables and data from DynamoDB to Oracle NoSQL Database Cloud Service.
See Mapping of DynamoDB table to Oracle NoSQL table for more details.
You can migrate a file containing the DynamoDB exported JSON data from the AWS S3 storage by specifying the path in the source configuration template.
{"Item":{"Id":{"N":"101"},"Phones":{"L":[{"L":[{"S":"555-222"},{"S":"123-567"}]}]},"PremierCustomer":{"BOOL":false},"Address":{"M":{"Zip":{"N":"570004"},"Street":{"S":"21 main"},"DoorNum":{"N":"201"},"City":{"S":"London"}}},"FirstName":{"S":"Fred"},"FavNumbers":{"NS":["10"]},"LastName":{"S":"Smith"},"FavColors":{"SS":["Red","Green"]},"Age":{"N":"22"}}}
{"Item":{"Id":{"N":"102"},"Phones":{"L":[{"L":[{"S":"222-222"}]}]},"PremierCustomer":{"BOOL":false},"Address":{"M":{"Zip":{"N":"560014"},"Street":{"S":"32 main"},"DoorNum":{"N":"1024"},"City":{"S":"Wales"}}},"FirstName":{"S":"John"},"FavNumbers":{"NS":["10"]},"LastName":{"S":"White"},"FavColors":{"SS":["Blue"]},"Age":{"N":"48"}}}
You export the DynamoDB table to AWS S3 storage as specified in Exporting DynamoDB table data to Amazon S3.
Example:
For this demonstration, you will learn how to migrate a DynamoDB JSON file in an AWS S3 source to NDCS. You will use a manually created configuration file for this example.
Prerequisites
- Identify the source and sink for the
migration.
- Source: DynamoDB JSON File in AWS S3
- Sink: Oracle NoSQL Database Cloud Service
- Identify the table in AWS DynamoDB that needs to be migrated to NDCS. Login to your AWS console using your credentials. Go to DynamoDB. Under Tables, choose the table to be migrated.
- Create an object bucket and export the table to S3. From your AWS console, go to S3. Under buckets, create a new object bucket. Go back to DynamoDB and click Exports to S3. Provide the source table and the destination S3 bucket and click Export.
Refer to steps provided in Exporting DynamoDB table data to Amazon S3 to export your table. While exporting, you select the format as DynamoDB JSON. The exported data contains DynamoDB table data in multiple
gzip
files as shown below./ 01639372501551-bb4dd8c3 |-- 01639372501551-bb4dd8c3 ==> exported data prefix |----data |------sxz3hjr3re2dzn2ymgd2gi4iku.json.gz ==>table data |----manifest-files.json |----manifest-files.md5 |----manifest-summary.json |----manifest-summary.md5 |----_started
- You need aws credentials (including access key ID and secret access key) and config files (credentials and optionally config) to access AWS S3 from the migrator. See Set and view configuration settings for more details on the configuration files. See Creating a key pair for more details on creating access keys.
- Identify your OCI cloud
credentials and capture them in the OCI config file. Save the config file in a directory
.oci
under your home directory (~/.oci/config
). See Acquiring Credentials for more details.[DEFAULT] tenancy=ocid1.tenancy.oc1.... user=ocid1.user.oc1.... fingerprint= 43:d1:.... key_file=</fully/qualified/path/to/the/private/key/> pass_phrase=<passphrase>
- Identify the region endpoint and compartment
name for your Oracle NoSQL Database. For
example,
- endpoint: us-phoenix-1
- compartment: developers
Procedure
- Prepare the configuration file (in JSON format) with the identified source and sink details. See Source Configuration Templates and Sink Configuration Templates .
You can choose one of the following two options.
- Option 1: Importing DynamoDB table a as JSON document using default schema config.
Here the
defaultSchema
isTRUE
and so the migrator creates the default schema at the sink. You need to specify theDDBPartitionKey
and the corresponding NoSQL column type. Else an error is thrown.{ "source" : { "type" : "aws_s3", "format" : "dynamodb_json", "s3URL" : "<https://<bucket-name>.<s3_endpoint>/export_path>", "credentials" : "</path/to/aws/credentials/file>", "credentialsProfile" : <"profile name in aws credentials file"> }, "sink" : { "type" : "nosqldb_cloud", "endpoint" : "<region_name>", "table" : "<table_name>", "compartment" : "<compartment_name>", "schemaInfo" : { "defaultSchema" : true, "readUnits" : 100, "writeUnits" : 60, "DDBPartitionKey" : "<PrimaryKey:Datatype>", "storageSize" : 1 }, "credentials" : "<complete/path/to/the/oci/config/file>", "credentialsProfile" : "DEFAULT", "writeUnitsPercent" : 90, "requestTimeoutMs" : 5000 }, "abortOnError" : true, "migratorVersion" : "1.0.0" }
For a DynamoDB JSON source, the default schema for the table will be as shown below:CREATE TABLE IF NOT EXISTS <TABLE_NAME>(DDBPartitionKey_name DDBPartitionKey_type, [DDBSortKey_name DDBSortKey_type], DOCUMENT JSON, PRIMARY KEY(SHARD(DDBPartitionKey_name),[DDBSortKey_name]))
Where
TABLE_NAME = value provided for the sink 'table' in the configuration
DDBPartitionKey_name = value provided for the partiiton key in the configuration
DDBPartitionKey_type = value provided for the data type of the partition key in the configuration
DDBSortKey_name = value provided for the sort key in the configuration if any
DDBSortKey_type = value provided for the data type of the sort key in the configuration if any
DOCUMENT = All attributes except the partition and sort key of a Dynamo DB table item aggregated into a NoSQL JSON column
- Option 2: Importing DynamoDB table as fixed columns using a user-supplied schema file.
Here the
defaultSchema
isFALSE
and you specify the schemaPath as a file containing your DDL statement. See Mapping of DynamoDB types to Oracle NoSQL types for more details.Note:
If the Dynamo DB table has a data type that is not supported in NoSQL, the migration fails.A sample schema file is shown below.CREATE TABLE IF NOT EXISTS sampledynDBImp (AccountId INTEGER,document JSON, PRIMARY KEY(SHARD(AccountId)));
The schema file is used to create the table at the sink as part of the migration. As long as the primary key data is provided, the input JSON record will be inserted, otherwise it throws an error.Note:
If the input data does not contain a value for a particular column(other than the primary key) then the column default value will be used. The default value should be part of the column definition while creating the table. For exampleid INTEGER not null default 0
. If the column does not have a default definition then SQL NULL is inserted if no values are provided for the column.{ "source" : { "type" : "aws_s3", "format" : "dynamodb_json", "s3URL" : "<https://<bucket-name>.<s3_endpoint>/export_path>", "credentials" : "</path/to/aws/credentials/file>", "credentialsProfile" : <"profile name in aws credentials file"> }, "sink" : { "type" : "nosqldb_cloud", "endpoint" : "<region_name>", "table" : "<table_name>", "compartment" : "<compartment_name>", "schemaInfo" : { "defaultSchema" : false, "readUnits" : 100, "writeUnits" : 60, "schemaPath" : "<full path of the schema file with the DDL statement>", "storageSize" : 1 }, "credentials" : "<complete/path/to/the/oci/config/file>", "credentialsProfile" : "DEFAULT", "writeUnitsPercent" : 90, "requestTimeoutMs" : 5000 }, "abortOnError" : true, "migratorVersion" : "1.0.0" }
- Option 1: Importing DynamoDB table a as JSON document using default schema config.
- Open the command prompt and navigate to the directory where you extracted the NoSQL Database Migrator utility.
- Run the
runMigrator
command by passing the configuration file using the--config
or-c
option.[~/nosqlMigrator/nosql-migrator-1.0.0]$./runMigrator --config <complete/path/to/the/JSON/config/file>
- The utility proceeds with the data migration,
as shown
below.
Records provided by source=7.., Records written to sink=7, Records failed=0, Records skipped=0. Elapsed time: 0 min 2sec 50ms Migration completed.
Validation
You can login to your NDCS console and verify that the new table is created with the source data.
Migrate between Oracle NoSQL Database Cloud Service regions
This example shows the usage of Oracle NoSQL Database Migrator to perform cross-region migration.
Use case
An organization uses Oracle NoSQL Database Cloud Service to store and manage its data. It decides to replicate data from an existing region to a newer region for testing purposes before the new region can be launched for the production environment.
In this use case, you will learn to use the NoSQL Database Migrator to copy data from the user_data
table in the Ashburn region to the Phoenix region.
You run the runMigrator
utility by passing a pre-created configuration file. If you don't provide the configuration file as a runtime parameter, the runMigrator
utility prompts you to generate the configuration through an interactive procedure.
- Download Oracle NoSQL Database Migrator from the Oracle NoSQL Downloads page and extract the contents to your machine. For details, see Workflow for Oracle NoSQL Database Migrator.
- Identify the source and sink for the migration.
- Source: The
user_data
table in the Ashburn region.Theuser_data
table includes the following data:{"id":40,"firstName":"Joanna","lastName":"Smith","otherNames":[{"first":"Joanna","last":"Smart"}],"age":null,"income":75000,"address":{"city":"Houston","number":401,"phones":[{"area":null,"kind":"work","number":1618955},{"area":451,"kind":"home","number":4613341},{"area":481,"kind":"mobile","number":4613382}],"state":"TX","street":"Tex Ave","zip":95085},"connections":[70,30,40],"email":"joanna.smith123@reachmail.com","communityService":"**"} {"id":10,"firstName":"John","lastName":"Smith","otherNames":[{"first":"Johny","last":"Good"},{"first":"Johny2","last":"Brave"},{"first":"Johny3","last":"Kind"},{"first":"Johny4","last":"Humble"}],"age":22,"income":45000,"address":{"city":"Santa Cruz","number":101,"phones":[{"area":408,"kind":"work","number":4538955},{"area":831,"kind":"home","number":7533341},{"area":831,"kind":"mobile","number":7533382}],"state":"CA","street":"Pacific Ave","zip":95008},"connections":[30,55,43],"email":"john.smith@reachmail.com","communityService":"****"} {"id":20,"firstName":"Jane","lastName":"Smith","otherNames":[{"first":"Jane","last":"BeGood"}],"age":22,"income":55000,"address":{"city":"San Jose","number":201,"phones":[{"area":608,"kind":"work","number":6538955},{"area":931,"kind":"home","number":9533341},{"area":931,"kind":"mobile","number":9533382}],"state":"CA","street":"Atlantic Ave","zip":95005},"connections":[40,75,63],"email":"jane.smith201@reachmail.com","communityService":"*****"} {"id":30,"firstName":"Adam","lastName":"Smith","otherNames":[{"first":"Adam","last":"BeGood"}],"age":45,"income":75000,"address":{"city":"Houston","number":301,"phones":[{"area":618,"kind":"work","number":6618955},{"area":951,"kind":"home","number":9613341},{"area":981,"kind":"mobile","number":9613382}],"state":"TX","street":"Indian Ave","zip":95075},"connections":[60,45,73],"email":"adam.smith201reachmail.com","communityService":"***"}
Identify either the region endpoint or the service endpoint and compartment name for your source.- endpoint:
us-ashburn-1
- compartment:
ocid1.compartment.oc1..aaaaaaaahcrgrgptoaq4cgpoymd32ti2ql4sdpu5puroausdf4og55z4tnya
- endpoint:
- Sink: The
user_data
table in the Phoenix region.Identify either the region endpoint or the service endpoint and the compartment name for your sink.- endpoint:
us-phoenix-1
- compartment:
ocid1.compartment.oc1..aaaaaaaaleiwplazhwmicoogv3tf4lum4m4nzbcv5wfjmoxuz3doreagvdma
Identify the sink table schema.
You can use the same table name and schema as the source table. For information on other schema options, see Identify the Source and Sink topic in Workflow for Oracle NoSQL Database Migrator
- endpoint:
- Source: The
- Identify your OCI cloud credentials for both regions and capture them in the configuration file. Save the config file on your machine in the location
/home/<user>/.oci/config
. For more details, see Acquiring Credentials.
Note:
- If the regions are under different tenancies, you must provide different profiles in the
/home/<user>/.oci/config
file with appropriate OCI cloud credentials for each of them. - If the regions are under the same tenancy, you can have a single profile in the
/home/<user>/.oci/config
file.
In this example, the regions are under different tenancies. The DEFAULT profile includes OCI credentials for the Ashburn region and DEFAULT2 includes OCI credentials for the Phoenix region.
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
user_data
table from the Ashburn region to the Phoenix region, perform the following:
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.
- Download Oracle NoSQL Database Migrator from the Oracle NoSQL Downloads page to your local machine.
- Launch the Cloud Shell from the Developer tools menu on your cloud console. The web browser opens your home directory. Click the Cloud Shell menu on the top, right corner of the Cloud Shell window and select the upload option from the drop-down. In the pop-up window, either drag and drop the Oracle NoSQL Database Migrator package from your local machine, or click the Select from your computer option, select the package from your local machine, and click the Upload button. You can also drag and drop the Oracle NoSQL Database Migrator package directly from your local machine to your home directory in the Cloud Shell. Extract the contents of the package.
- Identify the source and sink for the backup.
-
Source:
NDCSupload
table in Oracle NoSQL Database Cloud Service Ashburn region.For demonstration, consider the following data in theNDCSupload
table:{"id":1,"name":"Jane Smith","email":"iamjane@somemail.co.us","age":30,"income":30000.0} {"id":2,"name":"Adam Smith","email":"adam.smith@mymail.com","age":25,"income":25000.0} {"id":3,"name":"Jennifer Smith","email":"jenny1_smith@mymail.com","age":35,"income":35000.0} {"id":4,"name":"Noelle Smith","email":"noel21@somemail.co.us","age":40,"income":40000.0}
Identify the endpoint and compartment ID for your source. For the endpoint, you can supply either the region identifier or the service endpoint. For the list of data regions supported in Oracle NoSQL Database Cloud Service, see Data Regions and Associated Service URLs.
- endpoint:
us-ashburn-1
- compartment ID:
ocid1.compartment.oc1..aaaaaaaahcrgrgptoaq4cgpoymd32ti2ql4sdpu5puroausdf4og55z4tnya
- endpoint:
-
Sink: JSON file in the OCI Object Storage Bucket.
Identify the region endpoint, namespace, bucket, and prefix for OCI Object Storage. For more details, see Accessing Oracle Cloud Object Storage. For the list of OCI Object Storage service endpoints, see Object Storage Endpoints.
- endpoint:
us-ashburn-1
- bucket:
Migrate_oci
- prefix:
Delegation
- namespace: <> If you do not provide a namespace, the utility uses the default namespace of the tenancy.
Note:
If the Object Storage Bucket is in a different compartment, ensure that you have the privileges to write objects in the bucket. For more details on setting the policies, see Let users write objects to Object Storage buckets. - endpoint:
-
NDCSupload
table from Oracle NoSQL Database Cloud Service to a JSON file in the OCI Object Storage Bucket using the Cloud Shell, perform the following:
To validate your data backup, log in to the Oracle NoSQL Database Cloud Service console. Navigate through the menus, Storage > Object Storage & Archive Storage > Buckets
. Access the files from the NDCSupload/Delegation
directory in the Migrate_oci
bucket. For the procedure to access the console, see Accessing the Service from the Infrastructure Console article.
Migrate from CSV file to Oracle NoSQL Database
This example shows the usage of Oracle NoSQL Database Migrator to copy data from a CSV file to Oracle NoSQL Database.
Example
After evaluating multiple options, an organization finalizes Oracle NoSQL Database as its NoSQL Database platform. As its source contents are in CSV file format, they are looking for a way to migrate them to Oracle NoSQL Database.
In this example, you will learn to migrate the data from a CSV file called course.csv
, which contains information about various courses offered by a university. You generate the configuration file from the runMigrator
utility.
You can also prepare the configuration file with the identified source and sink details. See Oracle NoSQL Database Migrator Reference.
- Identify the source and sink for the migration.
- Source: CSV file
In this example, the source file is
course.csv
cat [~/nosql-migrator-1.5.0]/course.csv 1,"Computer Science", "San Francisco", "2500" 2,"Bio-Technology", "Los Angeles", "1200" 3,"Journalism", "Las Vegas", "1500" 4,"Telecommunication", "San Francisco", "2500"
- Sink: Oracle NoSQL Database
- Source: CSV file
- The CSV file must conform to the
RFC4180
format. - Create a file containing the DDL commands for the schema of the target table,
course
. The table definition must match the CSV data file concerning the number of columns and their types.In this example, the DDL file is
mytable_schema.ddl
cat [~/nosql-migrator-1.5.0]/mytable_schema.ddl create table course (id INTEGER, name STRING, location STRING, fees INTEGER, PRIMARY KEY(id));
course.csv
to Oracle NoSQL Database Service, perform the following steps:
java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore
sql-> select * from course;
{"id":4,"name":"Telecommunication","location":"San Francisco","fees":2500}
{"id":1,"name":"Computer Science","location":"San Francisco","fees":2500}
{"id":2,"name":"Bio-Technology","location":"Los Angeles","fees":1200}
{"id":3,"name":"Journalism","location":"Las Vegas","fees":1500}
4 rows returned