Starburst Greenplum connector

The Starburst Greenplum connector allows querying and creating tables in an external Greenplum Database.

The Greenplum Database is a massively parallel implementation of the PostgreSQL database, and shares many of its characteristics.

Requirements

To connect to Greenplum, you need:

• Greenplum Database or Tanzu Greenplum version 6.0 or higher.

• Network access from the coordinator and workers to the Greenplum server. Port 5432 is the default port.

• A valid Starburst Enterprise license.

Configuration

Create a catalog properties file in etc/catalog named, for example, mygreenplumdb.properties to access the configured Greenplum database in the mygreenplumdb catalog. Configure the usage of the connector by specifying the name greenplum and replace the connection properties as appropriate for your setup.

connector.name=greenplum
connection-url=jdbc:postgresql://example.net:5432/database
connection-user=root
connection-password=secret

Note that the connection-url uses the syntax from the PostgreSQL JDBC driver used by the Greenplum connector.

Multiple databases or master hosts

The connector can only access a single database managed by a Greenplum system per catalog. Thus, if you have multiple Greenplum databases, or you want to connect to multiple Greenplum master hosts, you must configure multiple catalogs using the connector.

Type mapping

Because SEP and Greenplum each support types that the other does not, this connector modifies some types when reading or writing data.

Greenplum to SEP read type mapping

The following read type mapping applies when data is read from existing tables in Greenplum, or inserted into existing tables in Greenplum from SEP.

Greenplum to SEP read type mapping

Greenplum Database type	SEP type	Notes
BOOLEAN, BIT(1)	BOOLEAN
SMALLINT, INT2	SMALLINT
INTEGER, INT, INT4, SERIAL, SERIAL4	INTEGER
BIGINT, INT8, BIGSERIAL, SERIAL8	BIGINT
REAL	REAL
DOUBLE PRECISION, FLOAT, FLOAT8	DOUBLE
REAL, FLOAT4	REAL	Special values Infinity, -Infinity, and NaN are supported.
DECIMAL(p, s)	DECIMAL(p, s)
DECIMAL	DOUBLE
MONEY	VARCHAR	Be aware of locale-specific formatting of MONEY set by lc_monetary.
VARCHAR(n), CHARACTER VARYING	VARCHAR(n)
TEXT	VARCHAR (unbounded)
VARBINARY(n)	BYTEA
DATE	DATE
TIME	TIME(3)	TIME WITH TIME ZONE is not supported.
TIMESTAMP	TIMESTAMP(6)	TIMESTAMP WITH TIME ZONE is supported.
UUID	UUID
JSON, JSONB	JSON

No other type is supported.

SEP to Greenplum write type mapping

The following write type mapping applies when tables are created in Greenplum from SEP.

SEP to Greenplum write type mapping

SEP type	Greenplum Database type	Notes
BOOLEAN	BOOLEAN
TINYINT	SMALLINT	Greenplum coerces TINYINT to SMALLINT in passing; thereafter, the written column’s type is SMALLINT.
SMALLINT	SMALLINT
INTEGER	INTEGER
BIGINT	BIGINT
REAL	REAL
DOUBLE	DOUBLE PRECISION
DECIMAL(p, s)	DECIMAL(p, s)
CHAR	CHAR
VARCHAR	VARCHAR
VARBINARY	BYTEA
DATE	DATE
TIME, TIME(3)	TIME
TIMESTAMP(p)	TIMESTAMP(p)	With or without time zone. All precisions supported.

No other type is supported.

SQL support

The connector provides read and write access to data and metadata in the Greenplum database. In addition to the globally available and read operation statements, the connector supports the following features:

• INSERT

• CREATE TABLE

• CREATE TABLE AS

• DROP TABLE

• ALTER TABLE

• CREATE SCHEMA

• DROP SCHEMA

Decimal type handling

DECIMAL types with precision larger than 38 can be mapped to a SEP DECIMAL by setting the decimal-mapping property, or the decimal_mapping catalog session property to allow_overflow. The scale of the resulting type is controlled with the decimal-default-scale configuration property, or the decimal-rounding-mode catalog session property. The precision is always 38.

By default, values that require rounding or truncation to fit cause a failure at runtime. This behavior is controlled with the decimal-rounding-mode configuration property or the decimal_rounding_mode session property, which can be set to UNNECESSARY (the default), UP, DOWN, CEILING, FLOOR, HALF_UP, HALF_DOWN, or HALF_EVEN. (See RoundingMode.)

Array type handling

The Greenplum array implementation does not support fixed dimensions, whereas SEP supports only arrays with fixed dimensions. You can configure how the Greenplum connector handles arrays with the greenplum.array-mapping property, or the array_mapping catalog session property. The following values are accepted for this property:

• DISABLED (default): array columns are skipped

• AS_ARRAY: array columns are interpreted as the SEP ARRAY type, for array columns with fixed dimensions.

• AS_JSON: array columns are interpreted as SEP JSON type, with no constraint on dimensions

General configuration properties

The following properties can be used to configure how data types from the connected data source are mapped to Trino data types and how the metadata is cached in Trino.

Property name	Description	Default value
unsupported-type-handling	Configure how unsupported column data types are handled: IGNORE, column is not accessible. CONVERT_TO_VARCHAR, column is converted to unbounded VARCHAR. The respective catalog session property is unsupported_type_handling.	IGNORE
jdbc-types-mapped-to-varchar	Allow forced mapping of comma separated lists of data types to convert to unbounded VARCHAR
case-insensitive-name-matching	Support case insensitive database and collection names	False
case-insensitive-name-matching.cache-ttl		1 minute
metadata.cache-ttl	Duration for which metadata, including table and column statistics, is cached	0 (disabled caching)
metadata.cache-missing	Cache the fact that metadata, including table and column statistics, is not available	False

Performance

The connector includes a number of performance improvements, detailed in the following sections.

Parallelism

You can specify the Greenplum database’s concurrency strategy for reading to take advantage of the parallel processing power of Greenplum and SEP.

Greenplum supports two types of concurrency. The default is NO_PARALLELISM, where data is read from Greenplum in a single split. The other type, SEGMENTS, creates multiple splits to read data from Greenplum in parallel, using the gp_segment_id column on the table or materialized view. Splits are processed in parallel on workers.

With SEGMENTS, you specify the maximum number of splits to create per scan. Note that specifying a number larger than the number of segments in Greenplum results in fewer splits than specified. For example, if Greenplum has 16 segments but max-splits-per-scan is set to 20, only 16 splits are created. Ideally the worker count in SEP is equal to the number of segment servers in Greenplum or larger.

Greenplum parallelism configuration properties

Property name	Description	Default
greenplum.parallelism-type	Specify either NO_PARALLELISM or SEGMENTS	NO_PARALLELISM
greenplum.parallel.max-splits-per-scan	Specify an integer from 1 to 100	10

For example:

greenplum.parallelism-type=SEGMENTS
greenplum.parallel.max-splits-per-scan=20

Table statistics

The Greenplum connector supports table and column statistics to improve query processing performance based on the actual data in the data source.

The statistics are collected by Greenplum and retrieved by the connector.

To collect statistics for a table, execute the following statement in Greenplum.

ANALYZE table_schema.table_name;

Refer to Greenplum documentation for additional ANALYZE options.

Pushdown

The connector supports pushdown for a number of operations:

• Join pushdown

• Limit pushdown

• Top-N pushdown

Aggregate pushdown for the following functions:

• avg()

• count()

• max()

• min()

• sum()

• stddev() and stddev_samp()

• stddev_pop()

• variance() and var_samp()

• var_pop()

• covar_samp()

• covar_pop()

• corr()

• regr_intercept()

• regr_slope()

Dynamic filtering

Dynamic filtering is enabled by default. It causes the connector to wait for dynamic filtering to complete before starting a JDBC query.

You can disable dynamic filtering by setting the property dynamic-filtering.enabled in your catalog properties file to false.

JDBC connection pooling

You can improve performance by enabling JDBC connection pooling, which is disabled by default.

Security

The connector includes a number of security-related features, detailed in the following sections.

User impersonation

The connector supports user impersonation. Enable user impersonation in the catalog properties file:

greenplum.impersonation.enabled=true

User impersonation in the Greenplum connector is based on the SET ROLE command supported in PostgreSQL.

Kerberos authentication

The connector supports Kerberos-based authentication with the following configuration:

greenplum.authentication.type=KERBEROS
kerberos.client.principal=example@example.com
kerberos.client.keytab=etc/kerberos/example.keytab
kerberos.config=etc/kerberos/krb5.conf

With this configuration the user example@example.com, defined in the principal property, is used to connect to the database, and the related Kerberos service ticket is located in the example.keytab file.

Kerberos credential pass-through

The connector can be configured to pass through Kerberos credentials received by SEP to the Greenplum database. Configure Kerberos and SEP, following the instructions in Kerberos credential pass-through.

Next, configure the connector to pass through the credentials from the server to the database in your catalog properties file, and ensure the Kerberos client configuration properties are in place on all nodes.

greenplum.authentication.type=KERBEROS_PASS_THROUGH
http.authentication.krb5.config=/etc/krb5.conf
http-server.authentication.krb5.service-name=exampleServiceName
http-server.authentication.krb5.keytab=/path/to/Keytab/File

Now any database access via SEP is subject to the data access restrictions and permissions of the user supplied via Kerberos.

Password credential pass-through

The connector supports password credential pass-through. To enable it, edit the catalog properties file to include the authentication type:

greenplum.authentication.type=PASSWORD_PASS_THROUGH

For more information about configurations and limitations, see Password credential pass-through.