Writing Data to a Greenplum Table

Writing Data to a Greenplum Table

After opening a Greenplum Database table with a specific open mode, a GPSS client can write one or more rows of data to the table. The client must map the source data to a gRPC data type. The GPSS server maps the gRPC type to a Greenplum Database type as specified in Data Type Mapping.

The Write service definition and relevant messages follow:

rpc Write(WriteRequest) returns(google.protobuf.Empty) {}

message DBValue {
  oneof DBType {
    int32 Int32Value = 1;
    int64 Int64Value = 2;
    float Float32Value = 5;
    double Float64Value = 6;
    string StringValue = 7;
    bytes BytesValue = 8;
    google.protobuf.Timestamp TimeStampValue = 10;
    google.protobuf.NullValue NullValue = 11;
  }
}

message Row {
  repeated DBValue Columns = 1;
}

message RowData {    
  bytes Data = 1;
}

message WriteRequest {
  Session Session = 1;
  repeated RowData Rows = 2;
}

The GPSS client application must provide values for all columns declared in the Greenplum table definition. If there is no data to write for a specific column, you must explicitly specify a null value for the column when generating the row's RowData.

Sample Java code to write two rows of data to the loaninfo table that you opened in insert mode in the previous section follows:

// create an array of rows
ArrayList<RowData> rows = new ArrayList<>();
for (int row = 0; row < 2; row++) {
  // create a row builder
  api.Row.Builder builder = api.Row.newBuilder();

  // create builders for each column, in order, and set values - text, int, text
  api.DBValue.Builder colbuilder1 = api.DBValue.newBuilder();
  colbuilder1.setStringValue("xxx");
  builder.addColumns(colbuilder1.build());
  api.DBValue.Builder colbuilder2 = api.DBValue.newBuilder();
  colbuilder2.setInt32Value(77);
  builder.addColumns(colbuilder2.build());
  api.DBValue.Builder colbuilder3 = api.DBValue.newBuilder();
  colbuilder3.setStringValue("yyy");
  builder.addColumns(colbuilder3.build());

  // build the row
  RowData.Builder rowbuilder = RowData.newBuilder().setData(builder.build().toByteString());

  // add the row
  rows.add(rowbuilder.build());
}

// create a write request builder
WriteRequest wReq = WriteRequest.newBuilder()
    .setSession(mSession)
    .addAllRows(rows)
  .build();

// use the blocking stub to call the Write service; it returns nothing
bStub.write(wReq);

If GPSS encounters an error, it rolls back the pending write transaction; rolling back all writes since the Open.

The client determines the success or failure of the write operation from the TransferStats returned when the client invokes the Close service to close the table.