Specifying and Preparing a Greenplum Table for Writing

A newer version of this documentation is available. Click here to view the most up-to-date release of the Greenplum 5.x documentation.

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 a data load operation, the GPSS client invokes the Close service on the table, specifying the maximum number of error rows to return. Close returns the success row count and the error row 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; // -1: return all, 0: nothing, positive: max rows.
}

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

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)
  .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() );