Specifying and Preparing a Greenplum Table for Writing

The client uses the GPSS Open service to specify and prepare a Greenplum Database table for writing. The Close service closes, or ends, a write operation on the table.

The Open service definition follows:

rpc Open(OpenRequest) returns(google.protobuf.Empty) {}

The GPSS client can insert or merge data into or update the data in a Greenplum Database table. The client specifies the mode of the write operation via a mode-specific Option that it provides to the OpenRequest message. Supported write operation modes include:

• Insert - add data to the table, optionally truncating before writing
• Update - update table data, specifying the join column and an optional update condition
• Merge - insert table data, specifying the join column and an optional insert condition

Relevant messages for the Open service include:

message InsertOption {
  repeated string InsertColumns = 1;
  bool TruncateTable = 2;
  int64 ErrorLimitCount = 4;
  int32 ErrorLimitPercentage = 5;
}

message UpdateOption {
  repeated string MatchColumns = 1;
  repeated string UpdateColumns = 2;
  string Condition = 3;
  int64 ErrorLimitCount = 4;
  int32 ErrorLimitPercentage = 5;
}

message MergeOption {
  repeated string InsertColumns = 1;
  repeated string MatchColumns = 2;
  repeated string UpdateColumns = 3;
  string Condition = 4;
  int64 ErrorLimitCount = 5;
  int32 ErrorLimitPercentage = 6;
}

message OpenRequest {
  Session Session = 1;
  string SchemaName = 2;
  string TableName = 3;
  string PreSQL = 4;
  string PostSQL = 5;
  int32 Timeout = 6; //seconds
  string Encoding = 7;
  string StagingSchema = 8;

  oneof Option {
    InsertOption InsertOption = 100;
    UpdateOption UpdateOption = 101;
    MergeOption MergeOption = 102;
  }
}

After it completes loading data or encounters an error from GPSS or the source, the GPSS client invokes the Close service on the table. Close returns the success and error row counts and any error strings in the TransferStats message.

The Close service definition and relevant messages follow:

rpc Close(CloseRequest) returns(TransferStats) {}

message CloseRequest {
  Session session = 1;
  int32 MaxErrorRows = 2;
  bool Abort = 3;
}

message TransferStats {
  int64 SuccessCount = 1;
  int64 ErrorCount = 2;
  repeated string ErrorRows = 3;
}

Use MaxErrorRows to identify the form and amount of error information that GPSS returns:

MaxErrorRows Value	Description
-1	Returns an ErrorCount and all ErrorRows (error messages).
0	Returns only an ErrorCount; no ErrorRows. The default.
n > 0	Returns an ErrorCount and a maximum of n ErrorRows.

If the GPSS client encounters an unrecoverable error that affects the load operation to Greenplum Database, it may choose to cancel writing the current batch of data. When the CloseRequest message is instantiated with .setAbort(true), GPSS cancels and rolls back the pending write transaction. This rolls back all writes since the Open.

Sample Code

Suppose you create a Greenplum Database table with the following command:

CREATE TABLE public.loaninfo( loantitle text, riskscore int, d2iratio text);

Sample Java code to prepare to open the loaninfo table for insert, and then close the table follows:

Integer errLimit = 25;
Integer errPct = 25;
// create an insert option builder
InsertOption iOpt = InsertOption.newBuilder()
    .setErrorLimitCount(errLimit)
    .setErrorLimitPercentage(errPct)
    .setTruncateTable(false)
    .addInsertColumns("loantitle")
    .addInsertColumns("riskscore")
    .addInsertColumns("d2iratio")
  .build();

// create an open request builder
OpenRequest oReq = OpenRequest.newBuilder()
    .setSession(mSession)
    .setSchemaName(schemaName)
    .setTableName(tableName)
    //.setPreSQL("")
    //.setPostSQL("")
    //.setEncoding("")
    .setTimeout(5)
    //.setStagingSchema("")
    .setInsertOption(iOpt)
  .build();

// use the blocking stub to call the Open service; it returns nothing
bStub.open(oReq);

// (placeholder) write data here

// create a close request builder
TransferStats tStats = null;
CloseRequest cReq = CloseRequest.newBuilder()
    .setSession(mSession)
    //.setMaxErrorRows(15)
    //.setAbort(true)
  .build();

// use the blocking stub to call the Close service
tStats = bStub.close(cReq);

// display the result to stdout
System.out.println( "CloseRequest tStats: " + tStats.toString() );