Retrieving Greenplum Schema and Table Info

Your GPSS client may need to examine Greenplum Database schemas or the definition of a Greenplum table. The GPSS Data API defines three services to obtain metadata information about Greenplum schemas and tables:

• ListSchema - list all schemas defined in the database
• ListTable - list all tables in a schema
• DescribeTable - return the definition of each column in a table

Listing the Schemas in the Database

The GPSS Data API defines the ListSchema service and supporting messages to list the schemas defined in a Greenplum database:

rpc ListSchema(ListSchemaRequest) returns (Schemas) {}

message ListSchemaRequest {
  Session Session = 1;
}

message Schemas {
  repeated Schema Schemas = 1;
}

message Schema {
  string Name = 1;
  string Owner = 2;
}

ListSchema returns the list of schemas defined in the database identified by the session specified by the client. This service returns the name of the schema and the Greenplum Database role that owns the schema.

This sample Java client code collects the names of the schemas in the database identified by the specified session:

import java.util.ArrayList;
import java.util.List;

// create a list schema request builder
ListSchemaRequest lsReq = ListSchemaRequest.newBuilder()
    .setSession(mSession)
  .build();

// use the blocking stub to call the ListSchema service
List<Schema> listSchema = bStub.listSchema(lsReq).getSchemasList();

// extract the name of each schema and save in an array
ArrayList<String> schemaNameList = new ArrayList<String>();
for(Schema s : listSchema) {
  schemaNameList.add(s.getName());
} 

Listing the Tables in a Schema

The GPSS Data API defines the ListTable service and supporting messages to list the tables defined in a specific Greenplum Database schema:

rpc ListTable(ListTableRequest) returns (Tables) {}

message ListTableRequest {
  Session Session = 1;
  string Schema = 2;
}

enum RelationType {
  Table = 0;
  View = 1;
  Index = 2;
  Sequence = 3;
  Special = 4;
  Other = 255;
}

message TableInfo {
  string Name = 1;
  RelationType Type = 2;
}

message Tables {
  repeated TableInfo Tables = 1;
}

ListTable returns a list of the tables in the schema and the database (session) specified by the client. This service also returns the type of Greenplum Database table/relation.

This sample Java code collects a list of the names of all of the tables defined in the specified schema and database:

// use the first schema name returned in the ListSchema code excerpt
String schemaName = schemaNameList.get(0);

// create a list table request builder
ListTableRequest ltReq = ListTableRequest.newBuilder()
    .setSession(mSession)
    .setSchema(schemaName)
  .build();

// use the blocking stub to call the ListTable service
List<TableInfo> tblList = bStub.listTable(ltReq).getTablesList();

// extract the name of each table only and save in an array
ArrayList<String> tblNameList = new ArrayList<String>();
for(TableInfo ti : tblList) {
  if(ti.getTypeValue() == RelationType.Table_VALUE) {
    tblNameList.add(ti.getName());
  }
}

Acquiring the Column Definitions of a Table

The GPSS Data API defines the DescribeTable service and supporting messages to retrieve the column definitions of a Greenplum Database table:

rpc DescribeTable(DescribeTableRequest) returns (Columns) {}

message DescribeTableRequest {
  Session Session = 1;
  string SchemaName = 2;
  string TableName = 3;
}

message Columns {
  repeated ColumnInfo Columns = 1;
}

message ColumnInfo {
  string Name = 1;
  string DatabaseType = 2;
  bool HasLength = 3;
  int64 Length = 4;
  bool HasPrecisionScale = 5;
  int64 Precision = 6;
  int64 Scale = 7;
  bool HasNullable = 8;
  bool Nullable = 9;
}

DescribeTable returns a list of column definitions for the table in the schema and the database (session) specified by the client. The column definition includes the name and the type of the Greenplum Database column. The definition also includes length, precision, and scale information, if applicable.

Sample Java code to retrieve the column definitions of the table in the specified schema and database, and print the column name and type to stdout:

// the name of the first table returned in the ListTable code excerpt
String tableName = tblNameList.get(0);

// create a describe table request builder
DescribeTableRequest dtReq = DescribeTableRequest.newBuilder()
    .setSession(mSession)
    .setSchemaName(schemaName)
    .setTableName(tableName)
  .build();

// use the blocking stub to call the DescribeTable service
List<ColumnInfo> columnList = bStub.describeTable(dtReq).getColumnsList();

// print the name and type of each column
for(ColumnInfo ci : columnList) {
  String colname = ci.getName();
  String dbtype = ci.getDatabaseType();
  // display the column name and type to stdout
  System.out.println( "column " + colname + " type: " + dbtype );
}