Oracle NoSQL Database Migrator Reference

Learn about source, sink, and transformation configuration template parameters available for Oracle NoSQL Database Migrator.

This article has the following topics:

Parameters

The NoSQL Database Migrator requires a configuration file where you define all the parameters to perform the migration activity. A few parameters are common across several sources and sinks. This topic provides a list of these common parameters. For the list of other parameters that are unique to individual sources or sinks, see the corresponding configuration template sections.

Common Configuration Parameters

The following are the common configuration parameters. See the individual configuration template sections for examples.

bucket

chunkSize

credentials

credentialsProfile

endpoint

format

namespace

prefix

requestTimeoutMs

security

type

useDelegationToken

useInstancePrincipal

useOKEWorkloadIdentity

For a sample use case, see Migrate from OCI Object Storage to Oracle NoSQL Database Cloud Service Using OKE Authentication.

Note: You can select only one of the authentication options. Therefore, specify only one of these parameters - credentials, useInstancePrincipal, useDelegationToken, useSessionToken, or useOKEWorkloadIdentity in the configuration template.

useSessionToken

To use the session token-based authentication, you must generate a session token using OCI Command Line Interface (CLI) commands.. For a sample use case, see Migrate from Oracle NoSQL Database to OCI Object Storage Using Session Token Authentication.

Note:

Source Configuration Templates

Learn about the source configuration file formats for each valid source and the purpose of each configuration parameter.

For the configuration file template, see Configuration File in Terminology used with NoSQL Data Migrator.

For details on valid sink formats for each of the source, see Sink Configuration Templates.

Topics

The following topics describe the source configuration templates referred by Oracle NoSQL Database Migrator to copy the data from the given source to a valid sink.

JSON File Source

The configuration file format for JSON file as a source of NoSQL Database Migrator is shown below.

You can migrate a JSON source file by specifying the file path or a directory in the source configuration template.

A sample JSON source file is as follows:

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

Source Configuration Template

"source": {
  "type": "file",
  "format": "json",
  "dataPath": "<path/to/JSON/[file|dir]>",
  "schemaInfo": {
    "schemaPath": "<path/to/schema/file>"
  }
},

Source Parameters

Common Configuration Parameters

Unique Configuration Parameters

dataPath

schemaInfo

schemaInfo.schemaPath

JSON File in OCI Object Storage Bucket

The configuration file format for JSON file in OCI Object Storage bucket as a source of NoSQL Database Migrator is shown below.

You can migrate a JSON file in the OCI Object Storage bucket by specifying the name of the bucket in the source configuration template.

A sample JSON source file in the OCI Object Storage bucket is as follows:

{"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-02-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-04T02:38:57.520Z","nestarray":[[1,2,3],[10,20,30]],"nested":{"arrayofobjects":[{"datefield":"2023-02-04T02:38:57.520Z","numfield":28,"strfield":"foo3"},{"datefield":"2023-02-04T02:38:57.520Z","numfield":38,"strfield":"bar"}],"nestNum":10,"nestString":"bar"},"num":1,"string":"foo"}}

Note: The valid sink types for OCI Object Storage source type are nosqldb and nosqldb_cloud.

Source Configuration Template

"source" : {
  "type" : "object_storage_oci",
  "format" : "json",
  "endpoint" : "<OCI Object Storage service endpoint URL or region ID>",
  "namespace" : "<OCI Object Storage namespace>",
  "bucket" : "<bucket name>",
  "prefix" : "<object prefix>",
  "schemaInfo" : {
     "schemaObject" : "<object name>"
  },
  "credentials" : "</path/to/oci/config/file>",
  "credentialsProfile" : "<profile name in oci config file>",
  "useInstancePrincipal" : <true|false>,
  "useDelegationToken" : <true|false>,
  "useSessionToken" : <true|false>,
  "useOKEWorkloadIdentity" : <true|false>
}

Source Parameters

Common Configuration Parameters

Unique Configuration Parameters

schemaInfo

schemaInfo.schemaObject

MongoDB-Formatted JSON File

The configuration file format for MongoDB-formatted JSON File as a source of NoSQL Database Migrator is shown below.

You can migrate a MongoDB exported JSON data by specifying the file or directory in the source configuration template.

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 for more information.

A sample MongoDB-formatted Relaxed mode 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"}]}

Source Configuration Template

"source": {
  "type": "file",
  "format": "mongodb_json",
  "dataPath": "</path/to/json/[file|dir]>",
  "schemaInfo": {
    "schemaPath": "</path/to/schema/file>"
  }
}

Source Parameters

Common Configuration Parameters

Unique Configuration Parameters

dataPath

schemaInfo

schemaInfo.schemaPath

MongoDB-Formatted JSON File in OCI Object Storage bucket

The configuration file format for MongoDB-Formatted JSON file in OCI Object Storage bucket as a source of NoSQL Database Migrator is shown below.

You can migrate the MongoDB exported JSON data in the OCI Object Storage bucket by specifying the name of the bucket in the source configuration template.

Extract the data from MongoDB using the mongoexport utility and upload it to the OCI Object Storage bucket. See mongoexport for more information. MongoDB supports two types of extensions to the JSON format of files, Canonical mode and Relaxed mode. Both formats are supported in the OCI Object Storage bucket.

A sample MongoDB-formatted Relaxed mode 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"}]}

Note: The valid sink types for OCI Object Storage source type are nosqldb and nosqldb_cloud.

Source Configuration Template

"source" : {
  "type" : "object_storage_oci",
  "format" : "mongodb_json",
  "endpoint" : "<OCI Object Storage service endpoint URL or region ID>",
  "namespace" : "<OCI Object Storage namespace>",
  "bucket" : "<bucket name>",
  "prefix" : "<object prefix>",
  "schemaInfo" : {
     "schemaObject" : "<object name>"
  },
  "credentials" : "</path/to/oci/config/file>",
  "credentialsProfile" : "<profile name in oci config file>",
  "useInstancePrincipal" : <true|false>,
  "useDelegationToken" : <true|false>,
  "useSessionToken" : <true|false>,
  "useOKEWorkloadIdentity" : <true|false>
}

Source Parameters

Common Configuration Parameters

  1. "credentials" : "/home/user/.oci/config"

  2. "credentials" : "/home/user/security/config"

Unique Configuration Parameters

schemaInfo

schemaInfo.schemaObject

DynamoDB-Formatted JSON File stored in AWS S3

The configuration file format for DynamoDB-formatted JSON File in AWS S3 as a source of NoSQL Database Migrator is shown below.

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"},"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 must export the DynamoDB table to AWS S3 storage as specified in Exporting DynamoDB table data to Amazon S3.

The valid sink types for DynamoDB-formatted JSON stored in AWS S3 are nosqldb and nosqldb_cloud.

Source Configuration Template

"source" : {
  "type" : "aws_s3",
  "format" : "dynamodb_json",
  "ttlAttributeName" : "<DynamoDB exported TTL attribute name>",
  "s3URL" : "<S3 object url>",
  "credentials" : "</path/to/aws/credentials/file>",
  "credentialsProfile" : "<profile name in aws credentials file>"
}

Source Parameters

Common Configuration Parameters

Unique Configuration Parameters

s3URL

credentials

credentialsProfile

ttlAttributeName

DynamoDB-Formatted JSON File

The configuration file format for DynamoDB-formatted JSON File as a source of NoSQL Database Migrator is shown below.

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 must copy the exported DynamoDB table data from AWS S3 storage to a local mounted file system.

The valid sink types for DynamoDB JSON file are nosqldb and nosqldb_cloud.

Source Configuration Template

"source" : {
  "type" : "file",
  "format" : "dynamodb_json",
  "ttlAttributeName" : <DynamoDB exported TTL attribute name>,
  "dataPath" : "<path/to/[file|dir]/containing/exported/DDB/tabledata>"
}

Source Parameters

Common Configuration Parameters

Unique Configuration Parameter

dataPath

ttlAttributeName

Oracle NoSQL Database

The configuration file format for Oracle NoSQL Database as a source of NoSQL Database Migrator is shown below.

You can migrate a table from Oracle NoSQL Database by specifying the table name in the source configuration template.

A sample Oracle NoSQL Database table is as follows:

{"id":20,"firstName":"Jane","lastName":"Smith","otherNames":[{"first":"Jane","last":"teacher"}],"age":25,"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],"expenses":null}
{"id":10,"firstName":"John","lastName":"Smith","otherNames":[{"first":"Johny","last":"chef"}],"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],"expenses":null}
{"id":30,"firstName":"Adam","lastName":"Smith","otherNames":[{"first":"Adam","last":"handyman"}],"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],"expenses":null}

Source Configuration Template

"source" : {
  "type": "nosqldb",
  "storeName" : "<store name>",
  "helperHosts" : ["hostname1:port1","hostname2:port2,..."],
  "table" : "<fully qualified table name>",
  "queryFilter" : "<query predicate>",
  "includeTTL": <true|false>,
  "security" : "</path/to/store/security/file>",
  "requestTimeoutMs" : 5000
}

Source Parameters

Common Configuration Parameter

Unique Configuration Parameters

storeName

helperHosts

table

includeTTL

queryFilter

Supported Expressions:

The Migrator utility supports the following expressions in the query predicate. For detailed syntax and examples, see SQL Reference Guide.

Field step expressions
Map-filter step expressions
Array-filter step expressions
Array-slice step expressions
Arithmetic operators
Value comparison operators
Sequence comparison operators
Logical operators AND, OR and NOT
IS NULL and IS NOT NULL operators
IN operator
Regular expression
EXISTS operator
IS OF TYPE operator
CONCAT operator
CAST expression
Row functions

The following table provides examples of valid query predicates for different expressions and resulting exported data.

Note:

Table - Sample Query Predicates

Query/predicate Exported data
“id=10” Rows from the given table with id=10.
“name=’John’” Rows from the given table with name = ‘John’.
“age>30 and gender=’male’” Rows from the given table with age greater than 30 and gender = ‘male’.
“$row.address.state = ‘CA’” Rows from the given table with state field in the address JSON column = ‘CA’. Here, you use a field step expression in the predicate to access the required field value from a JSON field.
“$row.expenses.keys($value > 1000) = ‘food’” Rows from the given table where the expenses category = ‘food’ and expenses amount is greater than 1000. Here, you use a map-filter step expression to select either the field names (keys) or the field values of the map/record fields.
“$row.expenses.keys($value > $.clothes) = ‘food’” Rows from the given table where the expenses category = ‘food’ and expenses amount is more than that spend on clothes.
”[$row.address.phones[$element.area = 650].kind] = ‘work’” Rows from the given table where the area code of the phone in the array = 650 and type = ‘work’. Here, you use array-filter step expression as the address field is a JSON array.
“[connections[$element > 100 and $pos < 10]] > 100” Rows from the given table with maximum of 10 connections and number of connections > 100. Here, you use an array-filter step expression as the connections field is an array.
“$row.income IS NULL” Rows from the given table which don’t have a known income. For more details, see IS NULL and IS NOT NULL Operators.
“a in (1, 5, 4)” Rows from the given table where a is 1, 5 or 4. For more details, see IN Operator.
“(a, b) in ((1, ‘a’), (5, ‘g’), (4, ‘t’))” Rows from the given table where (a is 1 and b is ‘a’) OR (a is 5 and b is ‘g’) OR (a is 4 and b is ‘t’).
“regex_like(name, ‘j.*’)” Rows from the given table whose name starts with j. For more details, see Regular Expressions.
“EXISTS $row.person.address.zipcode” Rows from the given table where person json column has zipcode in the address. For more details, see Exists Operator.
“$row.address is of type (string)” Rows from the given table where address column is of type string. For more details, see Is-Of-Type Operator.
“lastLogin > CAST(‘2022-10-01’ AS TIMESTAMP)” Rows from the given table with last login after October 1st 2022. For more details, see Cast Expressions.
“$row.connections[ ]=any 1” Rows from the given table whose connections array column has element 1. For more details, see Sequence Comparison Operators.
“modification_time($row) >= 2022-10-01’’ Rows from the given table that are modified on or after October 1st 2022. For more details, see Functions on Rows.
“expiration_time_millis($row) > 0” Rows from the given table that are not expired. For more details, see Functions on Rows.

Oracle NoSQL Database Cloud Service

The configuration file format for Oracle NoSQL Database Cloud Service as a source of NoSQL Database Migrator is shown below.

You can migrate a table from Oracle NoSQL Database Cloud Service by specifying the name or OCID of the compartment in which the table resides in the source configuration template.

A sample Oracle NoSQL Database Cloud Service table is as follows:

{"id":20,"firstName":"Jane","lastName":"Smith","otherNames":[{"first":"Jane","last":"teacher"}],"age":25,"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],"expenses":null}
{"id":10,"firstName":"John","lastName":"Smith","otherNames":[{"first":"Johny","last":"chef"}],"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],"expenses":null}
{"id":30,"firstName":"Adam","lastName":"Smith","otherNames":[{"first":"Adam","last":"handyman"}],"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],"expenses":null}

Source Configuration Template

"source" : {
  "type" : "nosqldb_cloud",
  "endpoint" : "<Oracle NoSQL Cloud Service endpoint URL or region ID>",
  "table" : "<table name>",
  "queryFilter" : "<query predicate>",
  "compartment" : "<OCI compartment name or id>",
  "credentials" : "<path/to/oci/credential/file>",
  "credentialsProfile" : "<profile name in oci config file>",
  "useInstancePrincipal" : <true|false>,
  "useDelegationToken" : <true|false>,
  "useSessionToken" : <true|false>,
  "useOKEWorkloadIdentity" : <true|false>,
  "readUnitsPercent" : <table readunits percent>,
  "includeTTL": <true|false>,
  "requestTimeoutMs" : <timeout in milli seconds>
}

Source Parameters

Common Configuration Parameters

Unique Configuration Parameters

table

compartment

Note: If the useInstancePrincipal parameter is set to true, the compartment must specify the compartment OCID and not the name.

readUnitsPercent

includeTTL

queryFilter

Supported Expressions:

The Migrator utility supports the following expressions in the query predicate. For detailed syntax and examples, see SQL Reference Guide.

Field step expressions
Map-filter step expressions
Array-filter step expressions
Array-slice step expressions
Arithmetic operators
Value comparison operators
Sequence comparison operators
Logical operators AND, OR and NOT
IS NULL and IS NOT NULL operators
IN operator
Regular expression
EXISTS operator
IS OF TYPE operator
CONCAT operator
CAST expression
Row functions

The following table provides examples of valid query predicates for different expressions and resulting exported data.

Note:

Table - Sample Query Predicates

Query/predicate Exported data
“id=10” Rows from the given table with id = 10.
“name=’John’” Rows from the given table with name = ‘John’.
“age>30 and gender=’male’” Rows from the given table with age greater than 30 and gender = ‘male’.
“$row.address.state = ‘CA’” Rows from the given table with state field in the address JSON column = ‘CA’. Here, you use a field step expression in the predicate to access the required field value from a JSON field.
“$row.expenses.keys($value > 1000) = ‘food’” Rows from the given table where the expenses category = ‘food’ and expenses amount is greater than 1000. Here, you use a map-filter step expression to select either the field names (keys) or the field values of the map/record fields.
“$row.expenses.keys($value > $.clothes) = ‘food’” Rows from the given table where the expenses category = ‘food’ and expenses amount is more than that spend on clothes.
”[$row.address.phones[$element.area = 650].kind] = ‘work’” Rows from the given table where the area code of the phone in the array = 650 and type = ‘work’. Here, you use array-filter step expression as the address field is a JSON array.
“[connections[$element > 100 and $pos < 10]] > 100” Rows from the given table with maximum of 10 connections and number of connections > 100. Here, you use an array-filter step expression as the connections field is an array.
“$row.income IS NULL” Rows from the given table which don’t have a known income. For more details, see IS NULL and IS NOT NULL Operators.
“a in (1, 5, 4)” Rows from the given table where a is 1, 5 or 4. For more details, see IN Operator.
“(a, b) in ((1, ‘a’), (5, ‘g’), (4, ‘t’))” Rows from the given table where (a is 1 and b is ‘a’) OR (a is 5 and b is ‘g’) OR (a is 4 and b is ‘t’).
“regex_like(name, ‘j.*’)” Rows from the given table whose name starts with j. For more details, see Regular Expressions.
“EXISTS $row.person.address.zipcode” Rows from the given table where person json column has zipcode in the address. For more details, see Exists Operator.
“$row.address is of type (string)” Rows from the given table where address column is of type string. For more details, see Is-Of-Type Operator.
“lastLogin > CAST(‘2022-10-01’ AS TIMESTAMP)” Rows from the given table with last login after October 1st 2022. For more details, see Cast Expressions.
“$row.connections[ ]=any 1” Rows from the given table whose connections array column has element 1. For more details, see Sequence Comparison Operators.
“modification_time($row) >= 2022-10-01’’ Rows from the given table that are modified on or after October 1st 2022. For more details, see Functions on Rows.
“expiration_time_millis($row) > 0” Rows from the given table that are not expired. For more details, see Functions on Rows.

CSV File Source

The configuration file format for the CSV file as a source of NoSQL Database Migrator is shown below. The CSV file must conform to the RFC4180 format.

You can migrate a CSV file or a directory containing the CSV data by specifying the file name or directory in the source configuration template.

A sample CSV file is as follows:

1,"Computer Science","San Francisco","2500"
2,"Bio-Technology","Los Angeles","1200"
3,"Journalism","Las Vegas","1500"
4,"Telecommunication","San Francisco","2500"

Source Configuration Template

"source" : {
  "type" : "file",
  "format" : "csv",
  "dataPath": "</path/to/a/csv/[file|dir]>",
  "hasHeader" : <true | false>,
  "columns" : ["column1", "column2", ....],
  "csvOptions": {
    "encoding": "<character set encoding>",
    "trim": "<true | false>"
 }
}

Source Parameters

Common Configuration Parameters

Unique Configuration Parameters

dataPath

Note: The CSV files must contain only scalar values. Importing CSV files containing complex types such as MAP, RECORD, ARRAY, and JSON is not supported. The NoSQL Database Migrator tool does not check for the correctness of the data in the input CSV file. The NoSQL Database Migrator tool supports the importing of CSV data that conforms to the RFC4180 format. CSV files containing data that does not conform to the RFC4180 standard may not get copied correctly or may result in an error. If the input data is corrupted, the NoSQL Database Migrator tool will not parse the CSV records. If any errors are encountered during migration, the NoSQL Database Migrator tool logs the information about the failed input records for debugging and informative purposes. For more details, see Logging Migrator Progress in Using Oracle NoSQL Data Migrator .

hasHeader

columns

csvOptions

csvOptions.encoding

csvOptions.trim

CSV file in OCI Object Storage Bucket

The configuration file format for the CSV file in OCI Object Storage bucket as a source of NoSQL Database Migrator is shown below. The CSV file must conform to the RFC4180 format.

You can migrate a CSV file in the OCI Object Storage bucket by specifying the name of the bucket in the source configuration template.

A sample CSV file in the OCI Object Storage bucket is as follows:

1,"Computer Science","San Francisco","2500"
2,"Bio-Technology","Los Angeles","1200"
3,"Journalism","Las Vegas","1500"
4,"Telecommunication","San Francisco","2500"

Note: The valid sink types for OCI Object Storage source type are nosqldb and nosqldb_cloud.

Source Configuration Template

"source" : {
  "type" : "object_storage_oci",
  "format" : "csv",
  "endpoint" : "<OCI Object Storage service endpoint URL or region ID>",
  "namespace" : "<OCI Object Storage namespace>",
  "bucket" : "<bucket name>",
  "prefix" : "<object prefix>",
  "credentials" : "</path/to/oci/config/file>",
  "credentialsProfile" : "<profile name in oci config file>",
  "useInstancePrincipal" : <true|false>,
  "useDelegationToken" : <true|false>,
  "useSessionToken" : <true|false>,
  "useOKEWorkloadIdentity" : <true|false>,
   "hasHeader" : <true | false>,
   "columns" : ["column1", "column2", ....],
   "csvOptions" : {
     "encoding" : "<character set encoding>",
     "trim" : <true | false>
   }
 }

Source Parameters

Common Configuration Parameters

Unique Configuration Parameters

hasHeader

columns

csvOptions

csvOptions.encoding

csvOptions.trim

Sink Configuration Templates

Learn about the sink configuration file formats for each valid sink and the purpose of each configuration parameter.

For the configuration file template, see Configuration File in Terminology used with NoSQL Data Migrator.

For details on valid source formats for each of the sinks, see Source Configuration Templates.

Topics

The following topics describe the sink configuration templates referred by Oracle NoSQL Database Migrator to copy the data from a valid source to the given sink.

JSON File Sink

The configuration file format for JSON File as a sink of NoSQL Database Migrator is shown below.

Sink Configuration Template

"sink" : {
  "type" : "file",
  "format" : "json",
  "dataPath": "</path/to/a/directory>",
  "schemaPath" : "<path/to/a/file>",
  "pretty" : <true|false>,
  "useMultiFiles" : <true|false>,
  "chunkSize" : <size in MB>
}

Sink Parameters

Common Configuration Parameters

Unique Configuration Parameters

dataPath

schemaPath

pretty

useMultiFiles

Parquet File

The configuration file format for Parquet File as a sink of NoSQL Database Migrator is shown below.

Sink Configuration Template

"sink" : {
  "type" : "file",
  "format" : "parquet",
  "dataPath": "</path/to/a/dir>",
  "chunkSize" : <size in MB>,
  "compression": "<SNAPPY|GZIP|NONE>",
  "parquetOptions": {
    "useLogicalJson": <true|false>,
    "useLogicalEnum": <true|false>,
    "useLogicalUUID": <true|false>,
    "truncateDoubleSpecials": <true|false>
  }
}

Sink Parameters

Common Configuration Parameters

Unique Configuration Parameters

dataPath

compression

parquetOptions

parquetOptions.useLogicalJson

parquetOptions.useLogicalEnum

parquetOptions.useLogicalUUID

parquetOptions.truncateDoubleSpecials

JSON File in OCI Object Storage Bucket

The configuration file format for JSON file in OCI Object Storage bucket as a sink of NoSQL Database Migrator is shown below.

Note: The valid source types for OCI Object Storage as the sink are nosqldb and nosqldb_cloud.

Sink Configuration Template

"sink" : {
  "type" : "object_storage_oci",
  "format" : "json",
  "endpoint" : "<OCI Object Storage service endpoint URL or region ID>",
  "namespace" : "<OCI Object Storage namespace>",
  "bucket" : "<bucket name>",
  "prefix" : "<object prefix>",
  "chunkSize" : <size in MB>,
  "pretty" : <true|false>,
  "credentials" : "</path/to/oci/config/file>",
  "credentialsProfile" : "<profile name in oci config file>",
  "useInstancePrincipal" : <true|false>,
  "useDelegationToken" : <true|false>,
  "useSessionToken" : <true|false>,
  "useOKEWorkloadIdentity" : <true|false>
}

Sink Parameters

Common Configuration Parameters

Unique Configuration Parameter

pretty

Parquet File in OCI Object Storage Bucket

The configuration file format for Parquet file in OCI Object Storage bucket as a sink of NoSQL Database Migrator is shown below.

Note: The valid source types for OCI Object Storage source type are nosqldb and nosqldb_cloud.

Sink Configuration Template

"sink" : {
  "type" : "object_storage_oci",
  "format" : "parquet",
  "endpoint" : "<OCI Object Storage service endpoint URL or region ID>",
  "namespace" : "<OCI Object Storage namespace>",
  "bucket" : "<bucket name>",
  "prefix" : "<object prefix>",
  "chunkSize" : <size in MB>,
  "compression": "<SNAPPY|GZIP|NONE>",
  "parquetOptions": {
    "useLogicalJson": <true|false>,
    "useLogicalEnum": <true|false>,
    "useLogicalUUID": <true|false>,
    "truncateDoubleSpecials": <true|false>
  },
  "credentials" : "</path/to/oci/config/file>",
  "credentialsProfile" : "<profile name in oci config file>",
  "useInstancePrincipal" : <true|false>,
  "useDelegationToken" : <true|false>,
  "useSessionToken" : <true|false>,
  "useOKEWorkloadIdentity" : <true|false>
}

Sink Parameters

Common Configuration Parameters

Unique Configuration Parameter

compression

parquetOptions

parquetOptions.useLogicalJson

parquetOptions.useLogicalEnum

parquetOptions.useLogicalUUID

parquetOptions.truncateDoubleSpecials

Oracle NoSQL Database

The configuration file format for Oracle NoSQL Database as a sink of NoSQL Database Migrator is shown below.

Sink Configuration Template

"sink" : {
  "type": "nosqldb",
  "storeName" : "<store name>",
  "helperHosts" : ["hostname1:port1","hostname2:port2,..."],
  "security" : "</path/to/store/credentials/file>",
  "table" : "<fully qualified table name>",
  "includeTTL": <true|false>,
  "ttlRelativeDate": "<date-to-use in UTC>",
  "schemaInfo" : {
    "schemaPath" : "</path/to/a/schema/file>",
    "defaultSchema" : <true|false>,
    "useSourceSchema" : <true|false>,
    "DDBPartitionKey" : <"name:type">,
    "DDBSortKey" : "<name:type>"
  },
  "overwrite" : <true|false>,
  "requestTimeoutMs" : <timeout in milli seconds>
}

Sink Parameters

Common Configuration Parameter

Unique Configuration Parameter

storeName

helperHosts

table

includeTTL

ttlRelativeDate

schemainfo

schemaInfo.schemaPath

schemaInfo.defaultSchema

schemaInfo.useSourceSchema

schemaInfo.DDBPartitionKey

schemaInfo.DDBSortKey

overwrite

Oracle NoSQL Database Cloud Service

The configuration file format for Oracle NoSQL Database Cloud Service as a sink of NoSQL Database Migrator is shown below.

Sink Configuration Template

"sink" : {
  "type" : "nosqldb_cloud",
  "endpoint" : "<Oracle NoSQL Cloud Service Endpoint>",
  "table" : "<table name>",
  "compartment" : "<OCI compartment name or id>",
  "includeTTL": <true|false>,
  "ttlRelativeDate" : "<date-to-use in UTC>",
  "schemaInfo" : {
    "schemaPath" : "</path/to/a/schema/file>",
    "defaultSchema" : <true|false>,
    "useSourceSchema" : <true|false>,
    "DDBPartitionKey" : <"name:type">,
    "DDBSortKey" : "<name:type>",
    "onDemandThroughput" : <true|false>,
    "readUnits" : <table read units>,
    "writeUnits" : <table write units>,
    "storageSize" : <storage size in GB>
   },
  "credentials" : "</path/to/oci/credential/file>",
  "credentialsProfile" : "<profile name in oci config file>",
  "useInstancePrincipal" : <true|false>,
  "useDelegationToken" : <true|false>,
  "useSessionToken" : <true|false>,
  "useOKEWorkloadIdentity" : <true|false>,
  "writeUnitsPercent" : <table writeunits percent>,
  "requestTimeoutMs" : <timeout in milli seconds>,
  "overwrite" : <true|false>
}

Sink Parameters

Common Configuration Parameters

Unique Configuration Parameter


table


compartment


includeTTL


ttlRelativeDate


schemaInfo


schemaInfo.schemaPath

Note: defaultSchema and schemaPath are mutually exclusive.


schemaInfo.defaultSchema


schemaInfo.useSourceSchema


schemaInfo.DDBPartitionKey


schemaInfo.DDBSortKey


schemaInfo.onDemandThroughput

Note: This parameter is not applicable for child tables as they share the throughput of the top-level parent table.


schemaInfo.readUnits


schemaInfo.writeUnits


schemaInfo.storageSize


writeUnitsPercent


overwrite

Transformation Configuration Templates

This topic explains the configuration parameters for the different transformations supported by the Oracle NoSQL Database Migrator. For the complete configuration file template, see Configuration File in Terminology used with NoSQL Data Migrator.

Oracle NoSQL Database Migrator lets you modify the data, that is, add data transformations as part of the migration activity. You can define multiple transformations in a single migration. In such a case, the order of transformations is vital because the source data undergoes each transformation in the given order. The output of one transformation becomes the input to the next one in the migrator pipeline.

The different transformations supported by the NoSQL Data Migrator are:

Table - Transformations

Transformation Config Attribute You can use this transformation to …
ignoreFields Ignore the identified columns from the source row before writing to the sink.
includeFields Include the identified columns from the source row before writing to the sink.
renameFields Rename the identified columns from the source row before writing to the sink.
aggregateFields Aggregate multiple columns from the source into a single column in the sink. As part of this transformation, you can also identify the columns that you want to exclude in the aggregation. Those fields will be skipped from the aggregated column.

You can find the configuration template for each supported transformation below.

ignoreFields

The configuration file format for the ignoreFields transformation is shown below.

Transformation Configuration Template

"transforms" : {
  "ignoreFields" : ["<field1>","<field2>",...]
}

Transformation Parameter

ignoreFields

includeFields

The configuration file format for the includeFields transformation is shown below.

Transformation Configuration Template

"transforms" : {
  "includeFields" : ["<field1>","<field2>",...]
}

Transformation Parameter

includeFields

renameFields

The configuration file format for the renameFields transformation is shown below.

Transformation Configuration Template

"transforms" : {
  "renameFields" : {
    "<old_name>" : "<new_name>",
    "<old_name>" : "<new_name>,"
    .....
  }
}

Transformation Parameter

renameFields

aggregateFields

The configuration file format for the aggregateFields transformation is shown below.

Transformation Configuration Template

"transforms" : {
  "aggregateFields" : {
    "fieldName" : "name of the new aggregate field",
    "skipFields" : ["<field1>","<field2">,...]
  }
}

Transformation Parameter

aggregateFields

Mapping of DynamoDB types to Oracle NoSQL types

The table below shows the mapping of DynamoDB types to Oracle NoSQL types.

Table - Mapping DynamoDB type to Oracle NoSQL type

# DynamoDB type JSON type for NoSQL JSON column Oracle NoSQL type
1 String (S) JSON String STRING
2 Number Type (N) JSON Number INTEGER/LONG/FLOAT/DOUBLE/NUMBER
3 Boolean (BOOL) JSON Boolean BOOLEAN
4 Binary type (B) - Byte buffer BASE-64 encoded JSON String BINARY
5 NULL JSON null NULL
6 String Set (SS) JSON Array of Strings ARRAY(STRING)
7 Number Set (NS) JSON Array of Numbers ARRAY(INTEGER/LONG/FLOAT/DOUBLE/NUMBER)
8 Binary Set (BS) JSON Array of Base-64 encoded Strings ARRAY(BINARY)
9 LIST (L) Array of JSON ARRAY(JSON)
10 MAP (M) JSON Object JSON
11 PARTITION KEY NA PRIMARY KEY and SHARD KEY
12 SORT KEY NA PRIMARY KEY
13 Attribute names with dash and dot JSON field names with a underscore Column names with underscore

Few additional points to consider while mapping DynamoDB types to Oracle NoSQL types:

Oracle NoSQL to Parquet Data Type Mapping

Describes the mapping of Oracle NoSQL data types to Parquet data types.

NoSQL Type Parquet Type
BOOLEAN BOOLEAN
INTEGER INT32
LONG INT64
FLOAT DOUBLE
DOUBLE DOUBLE
BINARY BINARY
FIXED_BINARY BINARY
STRING BINARY(STRING)
ENUM BINARY(STRING)

or

BINARY(ENUM), if the logical ENUM is configured
UUID BINARY(STRING)

or

FIXED_BINARY(16), if the logical UUID is configured
TIMESTAMP(p) INT64(TIMESTAMP(p))
NUMBER DOUBLE
field_name ARRAY(T) 
group field_name(LIST) { repeated group list { required T element } }
field_name MAP(T) 
group field_name (MAP) { repeated group key_value (MAP_KEY_VALUE) { required binary key (STRING); required T value; } }
field_name RECORD(K1 T1 N1, Kٖ2 T2 N2, ....)

where:

K = Key name

T = Type

N = Nullable or not

 
group field_name { ni == true ? optional Ti ki : required Ti ki  }
JSON BINARY(STRING)

or

BINARY(JSON), if logical JSON is configured

Note: When the NoSQL Number type is converted to Parquet Double type, there may be some loss of precision in case the value cannot be represented in Double. If the number is too big to represent as Double, it is converted to Double.NEGATIVE_INFINITY or Double.POSITIVE_INFINITY.

Mapping of DynamoDB table to Oracle NoSQL table

In DynamoDB, a table is a collection of items, and each item is a collection of attributes. Each item in the table has a unique identifier, or a primary key. Other than the primary key, the table is schema-less. Each item can have its own distinct attributes.

DynamoDB supports two different kinds of primary keys:

In contrast, Oracle NoSQL tables support flexible data models with both schema and schema-less design.

There are two different ways of modelling a DynamoDB table:

  1. Modeling DynamoDB table as a JSON document(Recommended): In this modeling, you map all the attributes of the Dynamo DB tables into a JSON column of the NoSQL table except partition key and sort key. You will model partition key and sort key as the Primary Key columns of the NoSQL table. You will use AggregateFields transform in order to aggregate non-primary key data into a JSON column.

    Note: The Migrator provides a user-friendly configuration defaultSchema to automatically create a schema-less DDL table which also aggregates attributes into a JSON column.

  2. Modeling DynamoDB table as fixed columns in NoSQL table: In this modeling, for each attribute of the DynamoDB table, you will create a column in the NoSQL table as specified in the Mapping of DynamoDB types to Oracle NoSQL types. You will model partition key and sort key attributes as Primary key(s). This should be used only when you are certain that importing DynamoDB table schema is fixed and each item has values for the most of the attributes. If DynamoDB items do not have common attributes, this can result in lot of NoSQL columns with empty values.

    Note: We highly recommend using schema-less tables when migrating data from DynamoDB to Oracle NoSQL Database due to the nature of DynamoDB tables being schema-less. This is especially for large tables where the content of each record may not be uniform across the table.

Troubleshooting the Oracle NoSQL Database Migrator

Learn about the general challenges that you may face while using the , and how to resolve them.

Migration has failed. How can I resolve this?

A failure of the data migration can be because of multiple underlying reasons. The important causes are listed below:

Table - Migration Failure Causes

Error Message Meaning Resolution
Failed to connect to Oracle NoSQL Database The migrator could not establish a connection with the NoSQL Database.
  • Check if the values of the storeName and helperHosts attributes in the configuration JSON file are valid and that the hosts are reachable.
  • For a secured store, verify if the security file is valid with correct user name and password values.
Failed to connect to Oracle NoSQL Database Cloud Service The migrator could not establish a connection with the Oracle NoSQL Database Cloud Service.
  • Verify if the endpoint URL or region name specified in the configuration JSON file is correct.
  • Check if the OCI credentials file is available in the path specified in the configuration JSON file.
  • Ensure that the OCI credentials provided in the OCI credentials are valid.
Table not found The table identified for the migration could not be located by the NoSQL Database Migrator.

For the Source:

  • Verify if the table is present in the source database.
  • Ensure that the table is qualified with its namespace in the configuration JSON file, if the table is created in a non-default namespace.
  • Verify if you have the required read/write authorization to access the table.
  • If the source is Oracle NoSQL Database Cloud Service, verify if the valid compartment name is specified in the configuration JSON file, and ensure that you have the required authorization to access the table.

For the Sink:

  • Verify if the table is present in the Sink. If it does not exist, you must either create the table manually or use the schemaInfo config to create it through the migration.
DDL Execution failed The DDL commands provided in the input schema definition file is invalid.
  • Check the syntax of the DDL commands in the schemaPath file.
  • Ensure that there is only one DDL statement per line in the schemaPath file.
failed to write record to the sink table with java.lang.IllegalArgumentException The input record is not matching with the table schema of the sink.
  • Check if the data types and column names specified in the target sink table are matching with sink table schema.
  • If you applied any transformation, check if the transformed records are matching with the sink table schema.
Request timeout The source or sink’s operation did not complete within the expected time.
  • Verify the network connection.
  • Check if the NoSQL Database is up and running.
  • Try to increase requestTimeout value in the configuration JSON file.

What should I consider before restarting a failed migration?

When a data migration task fails, the sink will be at an intermediate state containing the imported data until the point of failure. You can identify the error and failure details from the logs and restart the migration after diagnosing and correcting the error. A restarted migration starts over, processing all data from the beginning. There is no way to checkpoint and restart the migration from the point of failure. Therefore, NoSQL Database Migrator overwrites any record that was migrated to the sink already.

Best Practices

The time taken for the data migration depends on multiple factors such as volume of data being migrated, network speed, current load on the database. In case of a cloud service, the speed of migration also depends on the read throughput and the write throughput provisioned. So, to improve the migration speed, you can:

The Migrator utility is inherently designed to achieve higher migration speed by processing multiple streams in parallel. The following points suggest how to leverage this capability for various migration scenarios:

I have a long running migration involving huge datasets. How can I track the progress of the migration?

You can enable additional logging to track the progress of a long-running migration. To control the logging behavior of Oracle NoSQL Database Migrator, you must set the desired level of logging in the logging.properties file. This file is provided with the NoSQL Database Migrator package and available in the directory where the Oracle NoSQL Database Migrator was unpacked. The different levels of logging in the order of increasing verbosity are OFF, SEVERE, WARNING, INFO, FINE, and ALL.

Setting the log level to OFF turns off all the logging information, whereas setting the log level to ALL provides the full log information.

The default log level is WARNING. All the logging output is configured to go to the console by default.

You can see comments in the logging.properties file to know about each log level.