Migrating Last Write Metadata for Table Rows

Learn how to migrate Last Write Metadata for table rows from source to sink.

Last Write Metadata is user-provided metadata associated with the most recent successful write of a table row.

Use the includeLastWriteMetadata configuration parameter in the source configuration to control whether Last Write Metadata is exported, and in the sink configuration to control whether Last Write Metadata is imported.

The default value of includeLastWriteMetadata is false.

Exporting Last Write Metadata

To export Last Write Metadata, set includeLastWriteMetadata to true in the Oracle NoSQL Database source configuration.

When a row has Last Write Metadata, the NoSQL Database Migrator includes it in the _metadata JSON object of the exported row as lastWriteMetadata.

The following example shows an exported row with both TTL and Last Write Metadata, a row without either type of metadata, and a row with Last Write Metadata only.

{
  "id": 3,
  "name": "Bob",
  "_metadata": {
    "expiration": 1738357200000,
    "lastWriteMetadata": {
      "updatedBy": "Dona"
    }
  }
}
{
  "id": 4,
  "name": "Alice"
}
{
  "id": 5,
  "name": "John",
  "_metadata": {
    "lastWriteMetadata": {
      "name": "John",
      "age": 30,
      "isStudent": false,
      "courses": [
        "Math",
        "Science"
      ],
      "address": {
        "street": "123 Main St",
        "city": "Anytown"
      },
      "notes": "lastWriteMetadataDetails"
    }
  }
}

Importing Last Write Metadata

To import Last Write Metadata, set includeLastWriteMetadata to true in the sink configuration.

If an input row contains _metadata.lastWriteMetadata, the NoSQL Database Migrator writes the supplied Last Write Metadata along with the row data.

If overwrite is true (the default) and a row with the same primary key already exists in the sink, the existing row is replaced, and its Last Write Metadata, if available, is also updated.

If overwrite is false and a row with the same primary key already exists in the sink, the incoming row is skipped.

Note: Last Write Metadata must be supported by the Oracle NoSQL Database source or sink involved in the migration (Release 26.x or later). If includeLastWriteMetadata is set to true against a source or sink server that does not support Last Write Metadata, the migration fails.

If you are migrating in a mixed-version environment in which Last Write Metadata is not supported by either the source or sink, leave includeLastWriteMetadata set to false in the applicable source and sink configurations, or upgrade to a release that supports Last Write Metadata.

Use Case Demonstration

Migrate from Oracle NoSQL Database to a JSON file

This example shows how to use the Oracle NoSQL Database Migrator to migrate the data and schema definition of a table from Oracle NoSQL Database to a JSON file.

For the demonstration, you migrate the data and schema definition of a table called myTable from Oracle NoSQL Database to a JSON file.

Prerequisites:

Identify the source and sink for the migration.
- Source: Oracle NoSQL Database
- Sink: JSON file
Identify the following details for Oracle NoSQL Database.
- storeName: kvstore
- helperHosts: <hostname>:<port>
- table: myTable

To migrate the data and schema definition of your table from Oracle NoSQL Database to a JSON file:

Open the command prompt and navigate to the directory where you extracted the Oracle NoSQL Database Migrator utility.
To generate the configuration file using the NoSQL Database Migrator utility, run the runMigrator command without any runtime parameter.
```
[~/nosqlMigrator]$ ./runMigrator
```

The utility prompts if you want to generate the configuration. Type y.

Configuration file is not provided. Do you want to generate configuration? (y/n) [n]: y
Generating a configuration file interactively.

Choose your options for the source configuration.

Enter a location for your config [./migrator-config.json]: /home/<user>/nosqlMigrator/
Select the source:
1) nosqldb
2) nosqldb_cloud
3) file
4) object_storage_oci
5) aws_s3
#? 1
Configuration for source type=nosqldb
Enter store name of the Oracle NoSQL Database: kvstore
Enter comma separated list of host:port of Oracle NoSQL Database: <hostname>:5000
Enter fully qualified table name: myTable
Include TTL data? If you select 'yes' TTL of rows will also
be included in the exported data. (y/n) [n]: y
Would you like to export LastWriteMetadata along with the row data? If you select 'yes' LastWriteMetadata of every row (if exists) will also
be included in the exported rows. (y/n) [n]: y
Is the store secured? (y/n) [y]: n
Provide the where clause query to filter out data.
(Example input to filter out all the person from the state california is: $row.address.state='california')
If you do not wish to filter data just press enter:
Enter store operation timeout in milliseconds. (1-30000) [5000]:

Choose your options for the sink configuration.

Select the sink:
1) nosqldb
2) nosqldb_cloud
3) file
4) object_storage_oci
#? 3
Configuration for sink type=file
Select the sink file format:
1) json
2) parquet
#? 1
Enter path to a directory to store JSON data: /home/<user>/nosqlMigrator
would you like to export data to multiple files for each source? (y/n) [y]: n
Would you like to store JSON in pretty format? (y/n) [n]: n
Would you like to migrate the table schema also? (y/n) [y]: y
Enter path to a file to store table schema: /home/<user>/nosqlMigrator/myTableSchema.ddl
would you like to set write parallelism ?
If you select 'no' a values will be calculated internally as per sink type. [y/n] [n]:

Choose your options for the source data transformations.

Would you like to add transformations to source data? (y/n) [n]:

Enter your choice to determine whether to proceed with the migration in case any record fails to migrate.
```
Would you like to continue migration if any data fails to be migrated? (y/n) [n]:
```

The utility displays the generated configuration.

{
  "source": {
    "type": "nosqldb",
    "storeName": "kvstore",
    "helperHosts": [
      "<hostname>:5000"
    ],
    "table": "myTable",
    "queryFilter": "",
    "includeTTL": true,
    "includeLastWriteMetadata": true,
    "requestTimeoutMs": 5000
  },
  "sink": {
    "type": "file",
    "format": "json",
    "useMultiFiles": false,
    "schemaPath": "/home/<username>/nosqlMigrator/myTableSchema.ddl",
    "pretty": false,
    "parallelism": 0,
    "dataPath": "/home/<username>/nosqlMigrator/"
  },
  "abortOnError": true,
  "migratorVersion": "1.9.0"
}

Choose whether to proceed with the migration using the generated configuration file. The default option is y.

would you like to run the migration with above configuration?
If you select no, you can use the generated configuration file to run the migration using
./runMigrator --config /home/<user>/nosqlMigrator/migrator-config.json
(y/n) [y]:

The NoSQL Database Migrator migrates the table data and schema from Oracle NoSQL Database to the JSON file. Because Last Write Metadata export is enabled, the exported rows also include Last Write Metadata where available.
```
Records provided by source=4, Records written to sink=4, Records failed=0, Records skipped=0.
Elapsed time: 0min 0sec 103ms
Migration completed.
```

The migrated JSON file looks as follows:

{
  "id": 1,
  "address_line1": "10 Red Street",
  "address_line2": "Apt 3",
  "pin": 1234567,
  "_metadata": {
    "lastWriteMetadata": {
      "joined_in": "January 2012",
      "modified_by": "Shreya Sharma",
      "update_reason": "Change in address"
    }
  }
}
{
  "id": 2,
  "address_line1": "2 Green Street",
  "address_line2": "Suite 9",
  "pin": 1234567,
  "_metadata": {
    "lastWriteMetadata": {
      "modified_by": "Slvia Sanz",
      "data_source": "individual update"
    }
  }
}
{
  "id": 3,
  "address_line1": "5 Blue Ave",
  "address_line2": "Floor 2",
  "pin": 1234567,
  "_metadata": {
    "lastWriteMetadata": {
      "joined_in": "September 2023",
      "modified_by": "Raul Daniels"
    }
  }
}
{
  "id": 4,
  "address_line1": "add1",
  "address_line2": "add2",
  "pin": 87654321,
  "_metadata": {
    "lastWriteMetadata": {
      "joined_in": "May 2004",
      "modified_by": "Shreya Sharma",
      "update_reason": "Internal job transfer"
    }
  }
}