Starburst Greenplum connector#

The Starburst Greenplum connector allows querying and creating tables in an external Greenplum Database. This can be used to join data between different systems such as a Greenplum Database and Hive, or between different Greenplum instances.

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

Note

The connector requires a valid Starburst Enterprise Presto 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.

Greenplum to Presto 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 Presto.

Greenplum to Presto read type mapping#

Greenplum Database type

Presto 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.

Presto to Greenplum write type mapping#

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

Presto to Greenplum write type mapping#

Presto 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.

Decimal type handling#

DECIMAL types with precision larger than 38 can be mapped to a Presto 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 Presto 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 Presto ARRAY type, for array columns with fixed dimensions.

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

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 Presto.

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 Presto workers.

With SEGMENTS, you specify the maximum number of splits to create per scan. Note that specifying a number larger than the number of segment servers in Greenplum results in fewer splits than specified. For example, if Greenplum has 16 segment servers but max-splits-per-scan is set to 20, only 16 splits are created. Ideally the worker count in Presto 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.parallelism.max-splits-per-scan

Specify an integer from 1 to 100

10

For example:

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

Table statistics#

The Greenplum connector supports table and column statistics, as documented in Table Statistics. 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.

The table and column statistics can be viewed in Presto using SHOW STATS and are used for Cost based optimizations.

Table statistics configuration properties#

Property name

Description

Default

statistics.enabled

Enables table and column statistics

true

statistics.cache-ttl

Duration for which table and column statistics are cached

0s

statistics.cache-missing

Cache the fact that table statistics are not available

false

Pushdown#

The connector supports pushdown for processing the following aggregate functions:

Dynamic filtering#

The connector supports dynamic filtering as a query processing performance improvements.

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 passthrough#

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 passthrough.

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.

Limitations#

The following SQL statements are not supported: