Data Warehouse

GreenPlum

Connectivity Summary

Greenplum Database is a massively parallel processing (MPP) database server with an architecture specially designed to manage large-scale analytic data warehouses and business intelligence workloads.

The connectivity to Greenplum is via JDBC drive>, which is included in the platform. 

The connector currently supports GreenPlum Standard edition.

The drivers used by the connector are given below:

Driver / API: com.pivotal.jdbc.GreenplumDriver

Version: 5.1.4

Details: Uses JDK 8 - https://mvnrepository.com/artifact/com.pivotal/greenplum-jdbc/5.1.4

Technical Specifications

The connector capabilities are shown below:

Crawling

Supported Objects Data Types
Tables, Table Columns, Views, Stored Procedures, Functions, Triggers, Roles, Users, Permissions, Triggers, Usage Statistics

Supported:

“Bigint”, ” Char”, ”Date”, “Float”, “Int”, “Nchar”, “Ntext”, “Numeric”, “Nvarchar”, “Smallint” , “ Tinyint”, ”Varchar”..

Non Supported:
"blob", "clob", "lob", "img", "bytea", "aclitem[]", "xid"

Profiling

Please see Profiling Data for more details on profiling.

Feature

Support

Remarks

Table Profiling

Row count, Columns count, View sample data

 

View Profiling

Row count, Columns count, View sample data

View is treated as a table for profiling purposes

Column Profiling

Min, Max, Null count, distinct, top 50 values

 

Full Profiling 

Supported

 

Sample Profiling

Supported

 

Querying 

Operation Details

Select

Supported

Insert

Not supported, by default.

Update

Not supported, by default.

Delete

Not supported, by default.

Joins within database

Supported

Joins outside database

Not supported

Aggregations

Supported

Group By

Supported

Order By

Supported

The GreenPlum  - System Schemas:

The following system-level schemas exist in every database:

  • pg_catalog contains the system catalog tables, built-in data types, functions, and operators. It is always part of the schema search path, even if it is not explicitly named in the search path.
  • information_schema consists of a standardized set of views that contain information about the objects in the database. These views get system information from the system catalog tables in a standardized way.
  • pg_toast stores large objects such as records that exceed the page size. This schema is used internally by the Greenplum Database system.
  • pg_bitmapindex stores bitmap index objects such as lists of values. This schema is used internally by the Greenplum Database system.
  • pg_aoseg stores append-optimized table objects. This schema is used internally by the Greenplum Database system.
  • gp_toolkit is an administrative schema that contains external tables, views, and functions that you can access with SQL commands. All database users can access gp_toolkit to view and query the system log files and other system metrics.

Connection Details

The following connection settings should be added for connecting to a Greenplum database:

Configuration

Default / Sample Value*

Description

Required Field

Database type

Greenplum

Select the connection 

type from the list 

License Type

Requires Input

Standard

Connection Name

Requires Input

Select a connection name for Greenplum. The name that you specify is a reference name to easily identify your Greenplum connection in OvalEdge.

Server

Requires Input

Fully Qualified IP or Server name

Database

Requires Input

demo

Port

Requires Input

5432

Driver

Requires Input

com.pivotal.jdbc.GreenplumDriver

Username

Requires Input

ovaledge1

Password

Requires Input

0valEdge!

Connectionstring

Requires Input

jdbc:pivotal:greenplum://{server}:{port};database={sid}

FAQs

  1. What are the minimum connectivity details required? 
    The following are the minimum connectivity requirements requirements :
    1. Access to a given port.
    2. Users must have a minimum security role on the Database i.e., Users must have at least SELECT/READ privilege on all the schemas and Tables which he is in need to crawl.
    3. Users must have the privilege to access all those tables which are needed and tables with user privileges will only appear in crawling and profiling.
  2. What should I know when upgrading my driver?
    The  JDBC Driver 5.4.1 supports the JDBC pivotal specifications in the installation package as follows:

    JAR JDBC Specification JDK Version
    greenplum-jdbc-5.1.4 jdbc:pivotal  JDK 8.0
                                             jdbc:pivotal:greenplum://{server}:5432;database={sid}

  3. How much does the driver cost?
    The JDBC Driver for Greenplum  is available at no additional charge.

  4. Can I use the driver to access Greenplum from a Linux computer?
    Yes! You can use the driver to access Greenplum from Linux, Unix, and other non-Windows platforms. For having Greenplum  JDBC Authentication on Linux environment.