16 Using the Oracle NoSQL Handler

Learn how to use the Oracle NoSQL Handler, which can replicate transactional data from Oracle GoldenGate to a target Oracle NoSQL Database.

Topics:

16.1 Overview

Oracle NoSQL Database is a NoSQL-type distributed key-value database. It provides a powerful and flexible transaction model that greatly simplifies the process of developing a NoSQL-based application. It scales horizontally with high availability and transparent load balancing even when dynamically adding new capacity.

Oracle NoSQL Database provides a very simple data model to the application developer. Each row is identified by a unique key, and also has a value, of arbitrary length, which is interpreted by the application. The application can manipulate (insert, delete, update, read) a single row in a transaction. The application can also perform an iterative, non-transactional scan of all the rows in the database, see https://www.oracle.com/database/nosql and https://docs.oracle.com/cd/NOSQL/docs.htm.

The Oracle NoSQL Handler streams change data capture into Oracle NoSQL using the Table API. The Table API provides some of the functionality of an RDBMS, including tables, schemas, data types, and primary keys. Oracle NoSQL also supports a Key Value API. The Key Value API stores raw data in Oracle NoSQL based on a key. The NoSQL Handler does not support the Key Value API.

Parent topic: Using the Oracle NoSQL Handler

16.2 Detailed Functionality

Topics:

Parent topic: Using the Oracle NoSQL Handler

16.2.1 Oracle NoSQL Data Types

Oracle NoSQL provides a number of column data types and most of these data types are supported by the Oracle NoSQL Handler. A data type conversion from the column value in the trail file to the corresponding Java type representing the Oracle NoSQL column type in the Oracle NoSQL Handler is required.

The Oracle NoSQL Handler does not support Array, Map and Record data types by default. To support them, you can implement a custom data converter and override the default data type conversion logic to override it with your own custom logic to support your use case. Contact Oracle Support for guidance.

The following Oracle NoSQL data types are supported:

Binary
Boolean
Double
Float
Integer
Long
Java String

Parent topic: Detailed Functionality

16.2.2 Performance Considerations

Configuring the Oracle NoSQL Handler for batch mode provides better performance than the interactive mode. The batch processing mode provides an efficient and transactional mechanism for executing a sequence of operations associated with tables that share the same shard key portion of their primary keys. The efficiency results from the use of a single network interaction to accomplish the entire sequence of operations. All the operations specified in a batch are executed within the scope of a single transaction that effectively provides serializable isolation.

Parent topic: Detailed Functionality

16.2.3 Operation Processing Support

The Oracle NoSQL Handler moves operations to Oracle NoSQL using synchronous API. The Insert, update, and delete operations are processed differently in Oracle NoSQL databases rather than in a traditional RDBMS:

The following explains how insert, update, and delete operations are interpreted by the handler depending on the mode of operation:

insert – If the row does not exist in your database, then an insert operation is processed as an insert. If the row exists, then an insert operation is processed as an update.
update – If a row does not exist in your database, then an update operation is processed as an insert. If the row exists, then an update operation is processed as update.
delete – If the row does not exist in your database, then a delete operation has no effect. If the row exists, then a delete operation is processed as a delete.

The state of the data in Oracle NoSQL databases is eventually idempotent. You can replay the source trail files or replay sections of the trail files. Ultimately, the state of an Oracle NoSQL database is the same regardless of the number of times the trail data was written into Oracle NoSQL.

Primary key values for a row in Oracle NoSQL databases are immutable. An update operation that changes any primary key value for a Oracle NoSQL row must be treated as a delete and insert. The Oracle NoSQL Handler can process update operations that result in the change of a primary key in an Oracle NoSQL database only as a delete and insert. To successfully process this operation, the source trail file must contain the complete before and after change data images for all columns.

Parent topic: Detailed Functionality

16.2.4 Column Processing

Add Column Functionality

You can configure the Oracle NoSQL Handler to add columns that exist in the source trail file table definition though are missing in the Oracle NoSQL table definition. The Oracle NoSQL Handler can accommodate metadata change events of adding a column. A reconciliation process occurs that reconciles the source table definition to the Oracle NoSQL table definition. When configured to add columns, any columns found in the source table definition that do not exist in the Oracle NoSQL table definition are added. The reconciliation process for a table occurs after application start up the first time an operation for the table is encountered. The reconciliation process reoccurs after a metadata change event on a source table, when the first operation for the source table is encountered after the change event.

Drop Column Functionality

Similar to adding, you can configure the Oracle NoSQL Handler to drop columns. The Oracle NoSQL Handler can accommodate metadata change events of dropping a column. A reconciliation process occurs that reconciles the source table definition to the Oracle NoSQL table definition. When configured to drop columns, any columns found in the Oracle NoSQL table definition that are not in the source table definition are dropped.

Caution:

Dropping a column is potentially dangerous because it is permanently removing data from an Oracle NoSQL Database. Carefully consider your use case before configuring dropping.

Primary key columns cannot be dropped.

Column name changes are not handled well because there is no DDL-processing. The Oracle NoSQL Handler can handle any case change for the column name. A column name change event on the source database appears to the handler like dropping an existing column and adding a new column.

Parent topic: Detailed Functionality

16.2.5 Table Check and Reconciliation Process

First, the Oracle NoSQL Handler interrogates the target Oracle NoSQL database for the table definition. If the table does not exist, the Oracle NoSQL Handler does one of two things. If gg.handler.name.ddlHandling includes CREATE, then a table is created in the database. Otherwise, the process abends and a message is logged that tells you the table that does not exist. If the table exists in the Oracle NoSQL database, then the Oracle NoSQL Handler performs a reconciliation between the table definition from the source trail file and the table definition in the database. This reconciliation process searches for columns that exist in the source table definition and not in the corresponding database table definition. If it locates columns fitting this criteria and the gg.handler.name.ddlHandling property includes ADD, then the Oracle NoSQL Handler alters the target table in the database to add the new columns. Otherwise, those columns are ignored.

Next, the reconciliation process search for columns that exist in the target Oracle NoSQL table though do not exist in the source table definition. If it locates columns fitting this criteria and the gg.handler.name.ddlHandling property includes DROP then the Oracle NoSQL Handler alters the target table in Oracle NoSQL to drop these columns. Otherwise, those columns are ignored.

Parent topic: Detailed Functionality

16.2.6 Security

The Oracle NoSQL Handler supports two authentication methods, Basic and Kerberos

Both of these authentication methods uses SSL as the transport mechanism to the KV Store. You must specify the relative or absolute path of the public trust file for SSL as a part of the Oracle NoSQL Handler configuration in the Adapter properties file.

The basic authentication mechanism tries to login into the Oracle NoSQL database using the username and password specified as configuration parameters in the properties file. You can create a credential store for your Big Data environment in Oracle GoldenGate. After you create a credential store for your Big Data environment, you can add users to the store.

To create a user, run this command in GGSCI:

ALTER CREDENTIALSTORE ADD USER userid PASSWORD password [ALIAS alias] [DOMAIN domain]

Where:

userid is the user name. Only one instance of a user name can exist in the credential store unless the ALIAS or DOMAIN option is used.
password is the user's password. The password is echoed (not obfuscated) when this option is used. If you don’t use this option, then you are prompted for the password. The password is obfuscated as you type (recommended because it is more secure).
alias is an alias for the user name. The alias substitutes for the credential in parameters and commands where a login credential is required. If you don’t use the ALIAS option, the alias defaults to the user name.

The user created should have the access to read-write from the Oracle NoSQL database. For details about Oracle NoSQL user management, see

https://docs.oracle.com/cd/NOSQL/html/SecurityGuide/config_auth.html.

The only supported external login mechanism to the Oracle NoSQL is Kerberos. The Kerberos authentication mechanism tries to log in to the Oracle NoSQL database using the Kerberos principal, realm, and the keytab file. You specify these values as configuration parameters in the properties file.

The handler first tries to check if the security properties file is available to the handler for logging in to the Oracle NoSQL database as an administrator. If the user.security file is available to the handler, it logs in as an administrator into the database. If the security properties file is not available to the handler, it checks the AuthType, which can be basic or Kerberos. If the Oracle NoSQL store is configured to run with security disabled, then the handler allows access to the NoSQL store with authType set to none.

Parent topic: Detailed Functionality

16.3 Oracle NoSQL Handler Configuration

You configure the Oracle NoSQL Handler operation using the properties file. These properties are located in the Java Adapter properties file (not in the Replicat properties file).

To enable the selection of the Oracle NoSQL Handler, you must first configure the handler type by specifying gg.handler.name.type=nosql and the other Oracle NoSQL properties as follows:

Properties	Required/Optional	Legal Values	Default	Explanation
`gg.handlerlist`	Required	Any String.	None	Provides a name for the Oracle NoSQL Handler. The Oracle NoSQL Handler name becomes part of the property names listed in the table.
`gg.handler.name.type`	Required	`nosql`	None	Selects the Oracle NoSQL Handler for streaming change data capture into an Oracle NoSQL Database.
`gg.handler.name.fullyQualifiedTableName`	Optional	`true \| false`	`false`	The Oracle NoSQL Handler adds the schema name to the table name and stores it as a fully qualified table name in the NoSQL store.
`gg.handler.name.mode`	Optional	`op \| tx`	`op`	The default is recommended. In `op` mode, operations are processed as received. In `tx` mode, operations are cached and processed at transaction commit. The `tx` mode is slower and creates a larger memory footprint.
`gg.handler.name.nosqlStore`	Required	Any String.	None	The name of the store. The name you specify must be identical to the name used when you installed the store.
`gg.handler.name.nosqlURL`	Required	Any String.	None	The network name and the port information for the node currently belonging to the store.
`gg.handler.name.interactiveMode`	Optional	`true \| false`	`true`	The Oracle NoSQL Handler can operate in either interactive mode where one operation is processed each time or batch mode where a group of operations are processed together.
`gg.handler.name.ddlHandling`	Optional	`CREATE \| ADD\| DROP` in any combination with values delimited by a comma.	None	Configure the Oracle NoSQL Handler for the DDL functionality to provide. Options include `CREATE`, `ADD` and `DROP`. When `CREATE` is enabled, the handler creates tables in Oracle NoSQL if a corresponding table does not exist. When `ADD` is enabled, the handler adds columns that exist in the source table definition, but do not exist in the corresponding target Oracle NoSQL table definition. When `DROP` is enabled, the handler drops columns that exist in the Oracle NoSQL table definition, but do not exist in the corresponding source table definition.
`gg.handler.name.retries`	Optional	Any number.	3	The number of retries on any read or write exception that the Oracle NoSQL Handler encounters.
`gg.handler.name.username`	Optional	A legal username string.	None	A username for the connection to Oracle NoSQL store. It is required if the `AuthType` is set to `basic`.
`gg.handler.name.password`	Optional	A legal password string.	None	A password for the connection to Oracle NoSQL store. It is required if the `AuthType` is set to `basic`.
`gg.handler.name.authType`	Optional	`basic\| kerberos\| none`	None	The authentication type to login into the Oracle NoSQL store. If `authType` is set to `basic`, it needs a username and password to login. If `authType` is set to Kerberos, it needs a Kerberos principal, Kerberos realm, and a Kerberos key tab file location to login.
`gg.handler.name.securityPropertiesFile`	Optional	Relative or absolute path to the security file.	None	The security file enables the Oracle NoSQL Handler to have administrator access into the KV Store.
`gg.handler.name.publicTrustFile`	Optional	Relative or absolute path to the trust file.	None	The public trust file to enable SSL transport.
`gg.handler.name.kerberosKeyTabFile`	Optional	Relative or absolute path to the Kerberos key tab file	None	The key tab file allows the Oracle NoSQL Handler to access a password to perform `kinit` operation for Kerberos security.
`gg.handler.name.kerberosPrincipal`	Optional	A legal Kerberos principal name like `user/FQDN@MY.REALM`	None	The Kerberos principal name for Kerberos authentication.
`gg.handler.name.kerberosRealm`	Optional	A Kerberos Realm name	None	The Kerberos realm name for Kerberos authentication.
`gg.handler.name.dataConverterClass`	Optional	The fully qualified data converter class name.	`DefaultDataConverter`	The custom data converter can be implemented to override the default data conversion logic to support your specific use case.

Parent topic: Using the Oracle NoSQL Handler

16.4 Review a Sample Configuration

The following excerpt shows a sample configuration for the Oracle NoSQL Handler as it appears in the Java adapter properties file:

gg.handlerlist=nosql

#The handler properties
gg.handler.nosql.type= nosql
gg.handler.nosql.mode= op
gg.handler.nosql.nosqlStore= kvstore
gg.handler.nosql.nosqlURL= localhost:5000
gg.handler.nosql.ddlHandling= CREATE,ADD,DROP
gg.handler.nosql.interactiveMode=true
gg.handler.nosql.retries= 2
gg.handler.nosql.authType=basic
gg.handler.nosql.username= ORACLEWALLETUSERNAME[myalias mydomain]
gg.handler.nosql.password= ORACLEWALLETPASSWORD[myalias mydomain]

Parent topic: Using the Oracle NoSQL Handler

16.5 Performance Considerations

Parent topic: Using the Oracle NoSQL Handler

16.6 Full Image Data Requirements

In Oracle NoSQL, update operations perform a complete reinsertion of the data for the entire row. This Oracle NoSQL feature improves ingest performance, but in turn levies a critical requirement. Updates must include data for all columns, also known as full image updates. Partial image updates are not supported (updates with just the primary key information and data for the columns that changed). Using the Oracle NoSQL Handler with partial image update information results in incomplete data in the target NoSQL table.

Parent topic: Using the Oracle NoSQL Handler