X

The Oracle NoSQL Database Blog covers all things Oracle NoSQL Database. On-Prem, Cloud and more.

  • June 14, 2015

Uploading NoSQL tables from Golden Gate User Exits

Golden Gate and NoSQL Integration

The aim of this post is to illustrate how to use Golden Gate to stream relational transactions to Oracle NoSQL 12.3.4. We follow the structure of the post which illustrated how to use Golden Gate to stream data into HBase. 

 As shown in the diagram below, integrating database with NoSQL is accomplished by
developing a custom handler using Oracle GoldenGate's  and NoSQL's Java APIs.

The custom handler is deployed as an integral part of the
Oracle GoldenGate Pump process.   The
Pump process and the custom adapter are configured through the Pump parameter
file and custom adapter's properties file. The Pump reads
the Trail File created by the Oracle GoldenGate Capture process and passes the
transactions to the adapter. Based on the configuration, the adapter writes the
transactions into NoSQL table.

You can find the Java code for the handler at this Github repository in folder StreamFromGG.

The steps to generate and test an example are:

  1. Prepare the database to stream data from a table
  2. Define a NoSQL table
  3. Define the pump extract parameter file from the data base
  4. Define  the extract parameter file and the adapter properties file for NoSQL
  5. Register the extract process
  6. Start the GoldenGate extract processes
  7. Do some data manipulation on the data base and verify the content on the NoSQL table

Lets take an example

Prepare the database to stream data from a table

This part is not detailed lets say that a database user is defined on Oracle 12c to allow Golden Gate transnational streaming. The database parameters are set to log transnational SQL commands in the appropriate way to satisfy Golden Gate requirements.

We will focus on the database table T2 from the gha schema, whose definition is:

CREATE TABLE "GHA"."T2" 

   ( "ID" NUMBER, 

"CREATED" TIMESTAMP (6), 

"NOM" VARCHAR2(32 BYTE), 

"VILLE" VARCHAR2(128 BYTE), 

CONSTRAINT "PK_T2" PRIMARY KEY ("ID", "CREATED")

Define a NoSQL table

 After connecting to the NoSQL store the following commands create the table T2:

table create -name T2

# Add table fields

add-field -name ID -type STRING

add-field -name NOM -type STRING

add-field -name CREATED -type STRING

add-field -name VILLE -type STRING

# Assign a field as primary key

primary-key -field ID -field CREATED

shard-key -field ID

exit

# Add table to the database

plan add-table -wait -name T2

Define the pump extract parameter file from the data base

The extract for the database requires previously the use of defgen  utility to create what is called a data definition file which contains the definition of the source table.

The content of the extract parameter's file is:

EXTRACT E_ghat2

TARGETDEFS ./dirsql/t2.sql

SETENV (ORACLE_SID=cdb)

userid c##ogg, password ogg

exttrail /u01/ogg/dirdat/T2

GETUPDATEBEFORES

table orcl.gha.t2  TARGET gha.t2;

The extract name is E_ghat2, the table definition file  is t2.sql, the oracle user for the transnational streaming is c##ogg, trail files generated are prefixed with T2, the container of the schema gha is orcl.

Define  the extract parameter file  and the adapter properties file for NoSQL

 When using GoldenGate java adapters, there are two files, one defines the extract parameters, the other gives the java specific properties for the adapter (the default name for this file is <extract_name>.properties, if a different name is used it should be given on the extract parameters. Our extract name is nosqlt2. Par of the content of nosqlt2.properties is:

jvm.bootoptions= -Xms64m -Xmx512M -Dlog4j.configuration=log4j.properties -Djava.class.path=dirprm:/u01/nosql/kv-ee/lib/jackson-core-asl.jar:/u01/nosql/kv-ee/lib/jackson-mapper-asl.jar:/u01/nosql/kv-ee/lib/avro.jar:/u01/ogg/ggjava/oggnosql.jar:/u01/nosql/kv-ee/lib/kvclient.jar:/u01/ogg/ggjava/ggjava.jar:/usr/lib/hadoop/client/commons-configuration-1.6.jar:/etc/hadoop/conf:/usr/lib/hadoop/client/commons-cli.jar

#Nosql Handler.

gg.handlerlist=nosqlhandler

gg.handler.nosqlhandler.type=com.goldengate.delivery.handler.nosql.NosqlHandler

gg.handler.nosqlhandler.NosqlStore=kvstore

gg.handler.nosqlhandler.NosqlUrl=bigdatalite:5000

gg.handler.nosqlhandler.NosqlTable=T2

gg.handler.nosqlhandler.NosqlCols=ID,CREATED,NOM,VILLE

gg.handler.nosqlhandler.NosqlPKCols=ID,CREATED

gg.handler.nosqlhandler.NosqlShardCols=ID

gg.handler.nosqlhandler.NosqlMappings=ID,ID;CREATED,CREATED;NOM,NOM;VILLE,VILLE

The meaning of these properties is: 

  • jvm.bootoptions, gives the path for the nosql java classes including json data managing and the jar for nosql adapter
  • gg.handlerlist, gives the list of handlers in this case noqsqlhandler will be used to identify the properties
  • gg.handler.nosqlhandler.type, gives the class used as adapter
  • gg.handler.nosqlhandler.NosqlStore, gives the name of the  Nosql store to connect to
  • gg.handler.nosqlhandler.NosqlUrl, gives the nosql store url (hostname:port)
  • gg.handler.nosqlhandler.NosqlTable, gives the name of the table
  • gg.handler.nosqlhandler.NosqlCols, gives a comma separated list of the Nosql Table columns
  • gg.handler.nosqlhandler.NosqlPKCols, gives a comma separated list of the Nosql Table primary key columns
  • gg.handler.nosqlhandler.NosqlShardCols, gives a comma separated list of the Nosql Table shard columns (should be a non void subset of the primary key columns)
  • gg.handler.nosqlhandler.NosqlMappings, gives a semi-colon separated list of mapping pairs (source column:target column)

The adapter implementation of NoSQL data manipulation, delete, update, create uses the shard column values to batch operations into the NoSQL database. The execution of the batched operations is done only when the shard stored value changes.

Register the extract process

 Use ggsci utility to issue the following commands (replacing <OGG_HOME> with it's real value): 

add extract E_GHAT2 ,integrated tranlog, begin now

add exttrail  <OGG_HOME>/dirdat/T2 , extract E_GHAT2 , megabytes 10

register extract E_GHAT2  database container (orcl)

add extract NOSQLT2, exttrailsource <OGG_HOME>/dirdat/T2 

 Start the GoldenGate extract processes

 Use ggsci utility to start e_ghat2 and nosqlt2

 Verify that the process are running:

GGSCI (bigdatalite.localdomain) 1> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           

EXTRACT     RUNNING     E_GHAT2     00:00:02      00:01:07    

EXTRACT     RUNNING     NOSQLT2     49:22:05      00:01:56    

Data manipulation and verifications between Oracle 12c and NoSQL

Get the table count on NoSQL

kv-> aggregate table -name t2  -count

Row count: 4329

Delete data from t2 on Oracle 12c

delete t2 where id = 135

commit 

2 rows deleted 

Recompute the table count on NoSQL

kv-> aggregate table -name t2  -count

Row count: 4327

Note that last batch of NoSQL operations is flushed when the extract nosqlt2 is stopped 

Join the discussion

Comments ( 2 )
  • guest Wednesday, March 9, 2016

    Hi garango,

    i'm trying to implement replication from Oracle database 12c to Oracle nosql.

    i've been following instructions of this blog to do so, but i'm facing errors compiling oggnosql.jar from the source files of the github link

    could you please explain how to compile them and generate oggnosql.jar

    Thanks in advance,

    Mohamed Elshafey


  • guest Wednesday, March 9, 2016

    Hi garango,

    i'm trying to implement replication from Oracle database 12c to Oracle nosql.

    i've been following instructions of this blog to do so, but i'm facing errors compiling oggnosql.jar from the source files of the github link

    could you please explain how to compile them and generate oggnosql.jar

    Thanks in advance,

    Mohamed Elshafey


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.