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

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

Use Case:

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

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

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

A sample DynamoDB-formatted JSON File is as follows:

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

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

Example:

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

Prerequisites

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

Identify your OCI cloud credentials and capture them in the OCI config file. Save the config file in a directory .oci under your home directory (~/.oci/config). See Acquiring Credentials for more details.

[DEFAULT]              
tenancy=ocid1.tenancy.oc1....         
user=ocid1.user.oc1....         
fingerprint= 43:d1:....         
key_file=</fully/qualified/path/to/the/private/key/>              
pass_phrase=<passphrase>

Identify the region endpoint and compartment name for your Oracle NoSQL Database. For example,
- endpoint: us-phoenix-1
- compartment: developers

Procedure

To migrate the DynamoDB JSON data to the Oracle NoSQL Database:

Prepare the configuration file (in JSON format) with the identified source and sink details. See Source Configuration Templates and Sink Configuration Templates .
You can choose one of the following two options.
- Option 1: Importing DynamoDB table a as JSON document using default schema config.
  Here the defaultSchema is TRUE and so the migrator creates the default schema at the sink. You need to specify the DDBPartitionKey and the corresponding NoSQL column type. Else an error is thrown.
```
{
 "source" : {
   "type" : "aws_s3",
   "format" : "dynamodb_json",
   "s3URL" : "<https://<bucket-name>.<s3_endpoint>/export_path>",
   "credentials" : "</path/to/aws/credentials/file>",
   "credentialsProfile" : <"profile name in aws credentials file">
 },
 "sink" : {
   "type" : "nosqldb_cloud",
   "endpoint" : "<region_name>",
   "table" : "<table_name>",
   "compartment" : "<compartment_name>",
   "schemaInfo" : {
      "defaultSchema" : true,
      "readUnits" : 100,
      "writeUnits" : 60,
      "DDBPartitionKey" : "<PrimaryKey:Datatype>",
      "storageSize" : 1
   },
   "credentials" : "<complete/path/to/the/oci/config/file>",
   "credentialsProfile" : "DEFAULT",
   "writeUnitsPercent" : 90,
   "requestTimeoutMs" : 5000
 },
 "abortOnError" : true,
 "migratorVersion" : "1.0.0"
}
```
  For a DynamoDB JSON source, the default schema for the table will be as shown below:
```
CREATE TABLE IF NOT EXISTS <TABLE_NAME>(DDBPartitionKey_name DDBPartitionKey_type, 
[DDBSortKey_name DDBSortKey_type], DOCUMENT JSON, 
PRIMARY KEY(SHARD(DDBPartitionKey_name),[DDBSortKey_name]))
```
  Where
  
  TABLE_NAME = value provided for the sink 'table' in the configuration
  
  DDBPartitionKey_name = value provided for the partiiton key in the configuration
  
  DDBPartitionKey_type = value provided for the data type of the partition key in the configuration
  
  DDBSortKey_name = value provided for the sort key in the configuration if any
  
  DDBSortKey_type = value provided for the data type of the sort key in the configuration if any
  
  DOCUMENT = All attributes except the partition and sort key of a Dynamo DB table item aggregated into a NoSQL JSON column
- Option 2: Importing DynamoDB table as fixed columns using a user-supplied schema file.
  Here the defaultSchema is FALSE and you specify the schemaPath as a file containing your DDL statement. See Mapping of DynamoDB types to Oracle NoSQL types for more details.
  
  Note:
  If the Dynamo DB table has a data type that is not supported in NoSQL, the migration fails.
  A sample schema file is shown below.
```
CREATE TABLE IF NOT EXISTS sampledynDBImp (AccountId INTEGER,document JSON, 
PRIMARY KEY(SHARD(AccountId)));
```
  The schema file is used to create the table at the sink as part of the migration. As long as the primary key data is provided, the input JSON record will be inserted, otherwise it throws an error.
  
  Note:
  If the input data does not contain a value for a particular column(other than the primary key) then the column default value will be used. The default value should be part of the column definition while creating the table. For example id INTEGER not null default 0. If the column does not have a default definition then SQL NULL is inserted if no values are provided for the column.
```
{
 "source" : {
   "type" : "aws_s3",
   "format" : "dynamodb_json",
   "s3URL" : "<https://<bucket-name>.<s3_endpoint>/export_path>",
   "credentials" : "</path/to/aws/credentials/file>",
   "credentialsProfile" : <"profile name in aws credentials file">
 },
 "sink" : {
   "type" : "nosqldb_cloud",
   "endpoint" : "<region_name>",
   "table" : "<table_name>",
   "compartment" : "<compartment_name>",
   "schemaInfo" : {
      "defaultSchema" : false,
      "readUnits" : 100,
      "writeUnits" : 60,
      "schemaPath" : "<full path of the schema file with the DDL statement>",
      "storageSize" : 1
   },
   "credentials" : "<complete/path/to/the/oci/config/file>",
   "credentialsProfile" : "DEFAULT",
   "writeUnitsPercent" : 90,
   "requestTimeoutMs" : 5000
 },
 "abortOnError" : true,
 "migratorVersion" : "1.0.0"
}
```
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.