16 Developing Custom Filters, Formatters, and Handlers

Example 16-1 Custom Formatting Implementation

package com.mycompany.MyFormatter;
import oracle.goldengate.datasource.DsOperation;
import oracle.goldengate.datasource.DsTransaction;
import oracle.goldengate.datasource.format.DsFormatterAdapter;
import oracle.goldengate.datasource.meta.ColumnMetaData;
import oracle.goldengate.datasource.meta.DsMetaData;
import oracle.goldengate.datasource.meta.TableMetaData;
import java.io.PrintWriter;
public class MyFormatter extends DsFormatterAdapter {

        public MyFormatter() { }
        @Override
        public void formatTx(DsTransaction tx,

DsMetaData meta,
PrintWriter out)

        {

            out.print("Transaction: " );
            out.print("numOps=\'" + tx.getSize() + "\' " );
            out.println("ts=\'" + tx.getStartTxTimeAsString() + "\'");
            for(DsOperation op: tx.getOperations()) {
TableName currTable = op.getTableName();
TableMetaData tMeta = dbMeta.getTableMetaData(currTable);
String opType = op.getOperationType().toString();
String table = tMeta.getTableName().getFullName();
out.println(opType + " on table \"" + table + "\":" );
int colNum = 0;
for(DsColumn col: op.getColumns())
{

ColumnMetaData cMeta = tMeta.getColumnMetaData( colNum++ );
out.println(
cMeta.getColumnName() + " = " + col.getAfterValue() );
}

        }
        @Override
        public void formatOp(DsTransaction tx,

DsOperation op,
TableMetaData tMeta,
PrintWriter out)

        {

            // not used...

        }

}

The formatter defines methods for either formatting complete transactions (after they are committed) or individual operations (as they are received, before the commit). If the formatter is in operation mode, then formatOp(...) is called; otherwise, formatTx(...) is called at transaction commit.

To compile and use this custom formatter, include the Oracle GoldenGate for Java JARs in the classpath and place the compiled .class files in gg_install_dir/dirprm:

javac -d gg_install_dir/dirprm
-classpath ggjava/ggjava.jar MyFormatter.java

The resulting class files are located in resources/classes (in correct package structure):

gg_install_dir/dirprm/com/mycompany/MyFormatter.class

Alternatively, the custom classes can be put into a JAR; in this case, either include the JAR file in the JVM classpath using the user exit properties (using java.class.path in the jvm.bootoptions property), or by setting the Java application properties file to include your custom JAR:

# set properties on 'one'
gg.handler.one.type=file
gg.handler.one.format=com.mycompany.MyFormatter
gg.handler.one.file=output.xml
gg.classpath=/path/to/my.jar,/path/to/directory/of/jars/*

Example 16-2 Custom Formatter for Java Delivery

/*
 *
 * Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.
 *
 */
package oracle.goldengate.datasource.format;

//Logging imports
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.PrintWriter;
import oracle.goldengate.datasource.DsColumn;
import oracle.goldengate.datasource.DsConfiguration;
import oracle.goldengate.datasource.DsOperation;
import oracle.goldengate.datasource.DsToken;
import oracle.goldengate.datasource.DsTransaction;
import oracle.goldengate.datasource.TxOpMode;
import oracle.goldengate.datasource.meta.ColumnMetaData;
import oracle.goldengate.datasource.meta.DsMetaData;
import oracle.goldengate.datasource.meta.TableMetaData;
import oracle.goldengate.datasource.meta.TableName;
import oracle.goldengate.util.CDataUtil;

/**
 * This class provides an example for Oracle GoldenGate Java Adapter customers 
 * to write their own custom formatter which can be plugged into the Java file 
 * writer or Java JMS handler.
 * This example does NOT work with the new Big Data integrations such as HDFS, Kafka,
 * or Flume as formatter interface for those has been changed.  
 * This formatter formats data in CSV format, by default.  The field and line 
 * delimiters are configurable with the default as a comma and a line feed
 * respectively.
 * The goal is to provide an example custom formatter for customers wishing
 * to develop a custom formatter, to see how it works.  The functionality
 * can be extended and/or altered as needed to fulfill the specific needs of 
 * the customer.
 * User configures the use of this formatter by setting the follow configuration
 * in the Oracle GoldenGate Java Adapter properties file.
 * gg.handler.name.format=oracle.goldengate.datasource.format.ExampleFormatter
 * @author Tom Campbell 
 * @email thomas.campbell@oracle.com
 */
public class ExampleFormatter extends DsFormatterAdapter{
    private static final Logger logger=LoggerFactory.getLogger(ExampleFormatter.class);
    
    //The field delimiter defaults to a comma
    private String fieldDelimiter = ",";
    //The line delimiter defaults to the line separator
    private String lineDelimiter = System.lineSeparator();
    
    /**
     * Default Constructor
     */
    public ExampleFormatter() {
        super(TxOpMode.op); 
        //IMPLEMENTERS - Add constructor implementation code below
    }
    
    /**
     * Constructor with operation mode argument.
     * @param mode Mode of operation Transaction (tx) or Operation (op)
     */
    public ExampleFormatter(TxOpMode mode) {
        super(mode);
        //IMPLEMENTERS - Add constructor implementation code below
    }
    
    /**
     * Setter method to set the field delimiter.  User can configure 
     * generic setters on the formatter than are configured in the Oracle 
     * GoldenGate Java Adapter properties file as follows:
     * gg.handler.name.format.fieldDelimiter=somevalue
     * The user is free to generate any setter methods for the formatter.  The
     * configuration initialization code uses Java reflection to find and invoke
     * the corresponding setter method.  If the method is not found an exception
     * will be thrown and the Oracle GoldenGate process will ABEND.
     * @param fd The field delimiter value
     */
    void setFieldDelimiter(String fd){
        //The CDataUtil.unwrapCData allows the user to configure values that
        //are considered to be whitespace.  Examples are spaces, tabs, carriage
        //returns, line feeds, etc.  To configure white space use the CDATA[] wrapper
        //as follows.
        //gg.handler.name.format.fieldDelimiter=CDATA[somevalue]
        //The configuration value will be "somevalue".
        //The CDATA[] functionality is needed to preserve whitespace because the
        //default behavior of the GG config code is to trim whitespace.
        fieldDelimiter = CDataUtil.unwrapCData(fd);
    }
    
    /**
     * Setter method to set the line delimiter.  User can configure 
     * generic setters on the formatter than are configured in the Oracle 
     * GoldenGate Java Adapter properties file as follows:
     * gg.handler.name.format.lineDelimiter=somevalue
     * The user is free to generate any setter methods for the formatter.  The
     * configuration initialization code uses Java reflection to find and invoke
     * the corresponding setter method.  If the method is not found an exception
     * will be thrown and the Oracle GoldenGate process will ABEND.
     * @param fd The field delimiter value
     */
    void setLineDelimiter(String ld){
        //The CDataUtil.unwrapCData allows the user to configure values that
        //are considered to be whitespace.  Examples are spaces, tabs, carriage
        //returns, line feeds, etc.  To configure whitespace use the CDATA[] wrapper
        //as follows.
        //gg.handler.name.format.fieldDelimiter=CDATA[\n]
        //The configuration value will be a line feed.
        //The CDATA[] functionality is needed to preserve whitespace because the
        //default behavior of the GG config code is to trim whitespace.
        lineDelimiter = CDataUtil.unwrapCData(ld);
    }
    
    /**
     * Initialization method.  This is called once after all of the setter
     * methods are called to set the explicit configuration methods of the 
     * formatter.
     * @param conf data source configuration info from properties file
     * @param meta metadata from data source, describing columns/tables, etc
     */
    @Override
    public void init(DsConfiguration conf, DsMetaData meta) {
        //Call the base class first.
        super.init(conf, meta);
        //IMPLEMENTERS - Add initialization code below
        logger.info("Initializing the example formatter.");
        logger.info("  The field delimiter is configured as [" + fieldDelimiter + "].");
        logger.info("  The line delimiter is configured as [" + lineDelimiter + "].");
    }

    /**
     * The format operation method.  This is where the bulk of the formatting
     * logic will reside.
     * @param tx The transaction object
     * @param op The operation object
     * @param tmeta The table metadata object
     * @param writer The print writer object to which results should be written
     */
    @Override
    public void formatOp(DsTransaction tx, DsOperation op, TableMetaData tmeta, 
            PrintWriter writer) {
        logger.info("Method formatOp called...");
        //IMPLEMENTERS - Below is the call the example implementation
        //comment out and insert custom implementation here.
        internalFormatOperation(tx, op, tmeta, writer);
    }

    @Override
    public void formatTx(DsTransaction tx, DsMetaData dbmeta, PrintWriter writer) {
        logger.info("Method formatTx called...");
        //In transaction mode (i.e. gg.handler.name.mode=tx) the handler may 
        //be configured to call this method to format data at the end of the 
        //into a single print writer.  This is probably not the usual use case.
        //Using replicat transaction grouping (see GROUPTRANSOPS replicat configuration)
        //the number of operations in a grouped transaction can be substantial.
        //Replicat GROUPTRANSOPS is set to 1000 by default meaning as many as
        //1000 source transactions are getting grouped into a single target
        //transaction.  
        for (DsOperation op : tx) {
            TableName currTable = op.getTableName();
            TableMetaData tmeta = dbmeta.getTableMetaData(currTable);
            //Then call the internal formatting
            internalFormatOperation(tx, op, tmeta, writer);
        }
    }
    
    private void internalFormatOperation(DsTransaction tx, DsOperation op, 
            TableMetaData tmeta, PrintWriter writer) {
        //Use a string builder for performance.
        StringBuilder sb = new StringBuilder();
        
        //IMPLEMENTERS - example code shown below outputs commonly used data.
        //Modify the code below as need for the specific implementation.
        
        //Format the table name
        formatTableName(op, sb);
        //Format the operation type.
        formatOperationType(op, sb);
        //Format the operation timestamp.
        formatOperationTimestamp(op, sb);
        //Format the position
        formatPosition(op, sb);
        //Add primary keys
        formatPrimaryKeys(tmeta, sb);
        //Format tokens
        formatGGTokens(op, sb);
        //Format the column data
        formatColumnData(op, tmeta, sb);  
        
        //Transfer the message to the print writer.
        writer.print(sb.toString());
    }
    
    /**
     * This method formats the table name for output
     * @param op The operation object
     * @param sb The string builder object to append the data
     */
    private void formatTableName(DsOperation op, StringBuilder sb){
        sb.append("TableName");
        sb.append(fieldDelimiter);
        //The original fully qualified table name in the original case
        sb.append(op.getTableName().getOriginalName());
        sb.append(fieldDelimiter);
    }
    
    /**
     * Method to format the operation type data.  Operation types are 
     * insert, update, delete, and primary key update.
     * @param op The operation object
     * @param sb The string builder object to append the data
     */
    private void formatOperationType(DsOperation op, StringBuilder sb){
                //Output the operation type
        sb.append("OperationType");
        sb.append(fieldDelimiter);
        if (op.getOperationType().isInsert()){
            sb.append("insert");
        }else if (op.getOperationType().isDelete()){
            sb.append("delete");
        }else if (op.getOperationType().isPkUpdate()){
            //This is a specialzied use case of update.  The isUpdate() call also
            //returns true for primary key updates.
            sb.append("primarykeyupdate");
        }else if (op.getOperationType().isUpdate()){
            sb.append("update");
        }
        sb.append(fieldDelimiter);
    }
    
    /**
     * Method to format the operation timestamp.  This is the transaction commit
     * time of the transaction that contains the operation.  All operations
     * within a transaction have the same operation timestamp.
     * @param op The operation object
     * @param sb The string builder object to append the data
     */
    private void formatOperationTimestamp(DsOperation op, StringBuilder sb){
        sb.append("OperationTimestamp");
        sb.append(fieldDelimiter);
        sb.append(op.getTimestampAsString());
        sb.append(fieldDelimiter);
    }
    
    /**
     * Method to format the position.
     * The position is the concatentated trail file sequence number followed
     * by the RBA number (offset in the trail file).  The two together provide
     * traceability of the operation back to source trail file.
     * @param op The operation object
     * @param sb The string builder object to append the data.
     */
    private void formatPosition(DsOperation op, StringBuilder sb){
        sb.append("Position");
        sb.append(fieldDelimiter);
        sb.append(op.getPosition());
        sb.append(fieldDelimiter);
    }
    
    /**
     * Method to format the primary keys.
     * @param tmeta The table metadata object.
     * @param sb The string builder object to append the data.
     */
    private void formatPrimaryKeys(TableMetaData tmeta, StringBuilder sb){
        sb.append("PrimaryKeys");
        for(ColumnMetaData cmeta :tmeta.getColumnMetaData()){
            if (cmeta.isKeyCol()){
                sb.append(fieldDelimiter);
                sb.append(cmeta.getOriginalColumnName());
            }
        }
    }
    
    /**
     * Method to format the GoldenGate token key/value pairs from the source
     * trail file.
     * @param op The operation object.
     * @param sb The string builder object to append the data.
     */
    private void formatGGTokens(DsOperation op, StringBuilder sb){
        //If this is false there will not be any tokens
        if(op.getIncludeTokens()){
            sb.append(fieldDelimiter);
            sb.append("GGTokens");
            sb.append(fieldDelimiter);
            for(DsToken token : op.getTokens().values()){
                sb.append(token.getKey());
                sb.append(fieldDelimiter);
                sb.append(token.getValue());
                sb.append(fieldDelimiter);
            }
        }
    }
    
    /**
     * Method to output the column value data.  This data starts with the column
     * name, "before", the before image value, "after, and the after image value.
     * This before and after column values include special handling for missing
     * and null values.  Missing values are output as "MISSING".  Null values are
     * output as "NULL"
     * @param op The operation object.
     * @param tmeta The table metadata object
     * @param sb 
     */
    private void formatColumnData(DsOperation op, TableMetaData tmeta, StringBuilder sb){
        int cIndex = 0;
        sb.append(fieldDelimiter);
        sb.append("TableData");
        for(DsColumn col : op.getColumns()) {
            //Get the column metadata
            ColumnMetaData cmeta = tmeta.getColumnMetaData(cIndex++);
            //Output the column name
            sb.append(fieldDelimiter);
            sb.append(cmeta.getOriginalColumnName());
            sb.append(fieldDelimiter);
            
            //Output the before value
            //Insert operations have no before values
            //Delete operations generally only have column values for primary
            //keys and not the complete row.
            //Updates will have all column values if NOCOMPRESSUPDATES is configured
            //else updates will have only primary keys and the column values that changed.
            sb.append("Before");
            sb.append(fieldDelimiter);
            if (col.getBefore() == null){
                //The before image value is missing for the column.
                sb.append("MISSING");
            }else if(col.getBefore().isValueNull()){
                sb.append("NULL");
            }else{
                sb.append(col.getBefore().getValue());
            }
            sb.append(fieldDelimiter);
            
            //Output the after value
            //Insert operations have after values
            //Delete operations have no after values
            //Update operations will have all column values if NOCOMPRESSUPDATES is configured
            //else updates will have only primary keys and the column values that changed.
            sb.append("After");
            sb.append(fieldDelimiter);
            if (col.getAfter() == null){
                //The before image value is missing for the column.
                sb.append("MISSING");
            }else if(col.getAfter().isValueNull()){
                sb.append("NULL");
            }else{
                sb.append(col.getAfter().getValue());
            }       
        }
        //Add the line delimiter at the end
        sb.append(lineDelimiter);
    }

}

This type of custom formatter works well with the JMS and the Java File Writer handlers. Once processed, this outputs operation data in a CSV format. Operation metadata is output first, and then the column data including before and after change column image data. The field and line delimiters are configurable.