Starburst Stargate connectors#

Starburst Stargate is a feature that lets you link a local catalog on your Starburst Enterprise cluster to a catalog on a remote Starburst Enterprise cluster with the same version.

Starburst Stargate can access a single catalog on the remote cluster for each catalog. Thus, if you have multiple remote catalogs, or you want to connect to multiple remote clusters, you must configure multiple catalogs using Starburst Stargate.

Starburst Stargate connects directly to the remote cluster using a JDBC connection. This means you can connect to any data source and catalog supported by the remote cluster, but features of the connection are restricted to those supported by JDBC.

There are two connector options to configure a Starburst Stargate catalog, Starburst Stargate and Starburst Stargate parallel:

  • The Starburst Stargate connector establishes a connection directly to the remote SEP coordinator using the default Direct protocol, where remote worker nodes all send in-progress query result segments to be reassembled by the coordinator node. The finished query results are then retrieved by the local cluster’s Starburst Stargate catalog.

  • The Starburst Stargate parallel connector establishes a connection to a remote SEP cluster that is using the Spooling protocol to have worker nodes independently send query result segments to a shared object storage location. These segments are accessed and reassembled by the local Starburst Stargate parallel catalog before being sent to the client application. The reduction of bottlenecks allows for a higher-throughput connection to the remote cluster.

The Starburst Stargate connectors only allow read access.

Requirements#

Starburst Stargate connector#

The baseline Starburst Stargate connector uses the default Direct protocol and requires no additional configuration on the remote cluster.

Configuration#

On the local cluster’s nodes, create a catalog properties file in etc/catalog named example.properties. Replace example with your database name or some other descriptive name of the catalog.

Configure the catalog to use the Starburst Stargate connector by setting the connector.name property to stargate.

For the connection-url property, specify a JDBC string as follows:

  • The JDBC protocol is trino.

  • Specify the URL and port of the remote cluster’s coordinator.

  • Follow the URL with */example* to specify the catalog on the remote cluster to access.

For the connection-user and connection-password properties, specify credentials valid on the remote SEP cluster. This connector supports usernames and passwords specified as plain text.

Alternatively you can configure password credential pass-through to use the credentials of the user on the local SEP cluster to connect to the remote cluster, or OAuth 2.0 token pass-through to use a locally-issued access token to connect.

The remote catalog is available for access using the local catalog name, such as example.

If the name of the catalog file on the local cluster is identical to the name of the catalog file in the remote cluster, you can use the same catalog.schema.table access in your queries. As a result, any query on that catalog you run in the remote cluster also works in the local cluster without modifications.

We strongly advise that any connection to a remote cluster uses TLS/SSL and an authenticated connection. Set the ssl.enabled property to true to enable TLS/SSL, and provide the username and password to use for the connection.

The remote cluster must be configured to use TLS/HTTPS and a supported PASSWORD authentication type, such as LDAP authentication or Password file authentication.

If you are not using globally trusted certificates for TLS/HTTP, you have to configure ssl.truststore.path, ssl.truststore.password, and ssl.truststore.type to set the truststore to use for the JDBC connection.

Example 1: A catalog remotehive_example.properties file to access a remote Hive catalog called remotehive_example. TLS running on port 8443.

connector.name=stargate
connection-url=jdbc:trino://remote-cluster.example.net:8443/hive
connection-user=some_sep_user
connection-password=some_password
ssl.enabled=true

You can access the remote catalog with the remotehive_example catalog in the local cluster.

Example 2: A catalog remotedb_example.properties file to access a remote PostgreSQL catalog called remotedb_example. TLS behind a load balancer so that default port 443 is used:

connector.name=stargate
connection-url=jdbc:trino://remote-cluster.example.net:443/postgresdb
connection-user=some_sep_user
connection-password=some_password
ssl.enabled=true

You can access the remote catalog with the remotedb_example catalog in the local cluster.

Example 3: A catalog website_example.properties file to access a remote PostgreSQL catalog, which is called website_example there as well. The remote cluster requires a TLS connection:

connector.name=stargate
connection-url=jdbc:trino://remote-cluster.example.net:8443/website
connection-user=remotepost
connection-password=some_password
ssl.enabled=true

General configuration properties#

The following table describes general catalog configuration properties for the connector:

Property name

Description

case-insensitive-name-matching

Support case insensitive schema and table names. Defaults to false.

case-insensitive-name-matching.cache-ttl

Duration for which case insensitive schema and table names are cached. Defaults to 1m.

case-insensitive-name-matching.config-file

Path to a name mapping configuration file in JSON format that allows Trino to disambiguate between schemas and tables with similar names in different cases. Defaults to null.

case-insensitive-name-matching.config-file.refresh-period

Frequency with which Trino checks the name matching configuration file for changes. The duration value defaults to 0s (refresh disabled).

metadata.cache-ttl

Duration for which metadata, including table and column statistics, is cached. Defaults to 0s (caching disabled).

metadata.cache-missing

Cache the fact that metadata, including table and column statistics, is not available. Defaults to false.

metadata.schemas.cache-ttl

Duration for which schema metadata is cached. Defaults to the value of metadata.cache-ttl.

metadata.tables.cache-ttl

Duration for which table metadata is cached. Defaults to the value of metadata.cache-ttl.

metadata.statistics.cache-ttl

Duration for which tables statistics are cached. Defaults to the value of metadata.cache-ttl.

metadata.cache-maximum-size

Maximum number of objects stored in the metadata cache. Defaults to 10000.

write.batch-size

Maximum number of statements in a batched execution. Do not change this setting from the default. Non-default values may negatively impact performance. Defaults to 1000.

dynamic-filtering.enabled

Push down dynamic filters into JDBC queries. Defaults to true.

dynamic-filtering.wait-timeout

Maximum duration for which Trino waits for dynamic filters to be collected from the build side of joins before starting a JDBC query. Using a large timeout can potentially result in more detailed dynamic filters. However, it can also increase latency for some queries. Defaults to 20s.

Starburst Stargate parallel connector#

Starburst Stargate can be configured to support a connection with parallelism that allows for data retrieval with a much higher throughput. This enhancement becomes much more apparent when trying to retrieve large amounts of data while executing a query.

While the standard Starburst Stargate connector uses the Direct protocol for connectivity, the Starburst Stargate parallel connector uses the Spooling protocol to establish a connection either directly to the remote cluster’s object storage or through the SEP coordinator as a proxy.

Note

Starburst Stargate parallel is available as a public preview in Starburst Enterprise. Contact Starburst Support with questions or feedback.

Some queries explicitly return large amounts of data, but there are cases where queries contain aggregates or joins that are not pushed down to the data source. While Starburst Stargate parallel would help in these cases, note that this connector is not intended to replace pushdown operations.

Depending on the network location of each cluster and the object storage used, using Starburst Stargate parallel catalogs may incur additional cloud costs.

Configuration#

Starburst Stargate parallel catalogs are configured identically to Starburst Stargate catalogs as described previously, but with the connector.name catalog configuration property set to stargate_parallel. The following example catalog configuration defines a Starburst Stargate parallel catalog to connect to a Hive catalog on the remote cluster with TLS on port 8443:

connector.name=stargate_parallel
connection-url=jdbc:trino://remote-cluster.example.net:8443/hive
connection-user=some_sep_user
connection-password=some_password
ssl.enabled=true

You must also ensure that the spooling protocol is properly configured on the coordinator and all worker nodes, per the following example config.properties configuration:

protocol.spooling.enabled=true
protocol.spooling.shared-secret-key=exampleSharedSecretKey
# Enables direct storage access in order to allow fetching against the storage.
protocol.spooling.retrieval-mode=storage

Security#

Query result retrieval, whether directly from the object storage or through the remote SEP coordinator, uses pre-signed URIs. Data is encrypted at rest using ephemeral keys. Ephemeral keys are unique for every query and only known to both clusters for the duration of the query and not stored long-term. Once the query is done, even if segments are not deleted, they cannot be decrypted.

Abandoned query segments can be cleaned up via a pruning job in SEP or with a life-cycle policy configured in the object storage. See Spooling protocol properties for pruning configuration options in SEP.

Type mapping#

Starburst Stargate to Trino type mapping#

The following read type mapping applies when data is read from existing tables on the remote data source.

Remote data source connector to Starburst Stargate local SEP type#

Remote data source type

Local SEP type

Notes

BOOLEAN

BOOLEAN

TINYINT

TINYINT

SMALLINT

SMALLINT

INTEGER

INTEGER

BIGINT

BIGINT

REAL

REAL

DOUBLE

DOUBLE

DECIMAL

DECIMAL

all precisions

CHAR

CHAR

all precisions

VARCHAR

VARCHAR

all precisions

VARBINARY

VARBINARY

DATE

DATE

TIME

TIME

all precisions

TIME WITH TIME ZONE

TIME WITH TIME ZONE

all precisions

TIMESTAMP

TIMESTAMP

all precisions

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH TIME ZONE

all precisions

JSON

JSON

No other type is supported.

Type mapping 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

SQL support#

The connectors support globally available and read operation statements to access data and metadata in JDBC.

ALTER TABLE EXECUTE#

The connectors support the following commands for use with ALTER TABLE EXECUTE:

collect_statistics#

The collect_statistics command is used with Managed statistics to collect statistics for a table and its columns.

The following statement collects statistics for the example_table table and all of its columns:

ALTER TABLE example_table EXECUTE collect_statistics;

Collecting statistics for all columns in a table may be unnecessarily performance-intensive, especially for wide tables. To only collect statistics for a subset of columns, you can include the columns parameter with an array of column names. For example:

ALTER TABLE example_table
    EXECUTE collect_statistics(columns => ARRAY['customer','line_item']);

Table functions#

The connectors provide specific table functions for the Starburst Stargate connection.

query(VARCHAR) -> table#

The query function allows you to query the underlying database directly. It requires syntax native to the data source, because the full query is pushed down and processed in the data source. This can be useful for accessing native features or for improving query performance in situations where running a query natively may be faster.

The query table function is available in the system schema of any catalog that uses the Starburst Stargate connector, such as example. The following example passes myQuery to the data source. myQuery has to be a valid query for the data source, and is required to return a table as a result:

SELECT
  *
FROM
  TABLE(
    example.system.query(
      query => 'myQuery'
    )
  );

Performance#

The connectors include a number of performance features, detailed in the following sections.

Table statistics#

Starburst Stargate connectors support table and column statistics for cost based optimizations if the remote catalog’s connector supports it, such as the Hive connector. In this case, this feature improves query processing performance based on the actual data in the data source.

For remote catalogs that support this feature:

  • The statistics are collected by the remote data source and are retrieved by Starburst Stargate.

  • To collect statistics for a table, use the command required by the connector there. For example, for Hive catalogs you can use the following statement on the remote data source:

    ANALYZE table_schema.table_name;

  • Retrieving statistics for Hive views defined in the remote data source is supported. Statistics for other views, such as on PostgreSQL catalogs, are not supported.

Managed statistics#

The connectors support Managed statistics allowing SEP to collect and store its own table and column statistics that can then be used for performance optimizations in query planning. When you enable managed statistics on a Starburst Stargate catalog, statistics are stored on the local cluster, eliminating the need for the optimizer to reference statistics on a remote cluster.

Statistics must be collected manually using the built-in collect_statistics command, see collect_statistics for details and examples.

Pushdown#

The connectors support pushdown for a number of operations:

Aggregate pushdown for the following functions:

Cost-based join pushdown#

The connector supports cost-based Join pushdown to make intelligent decisions about whether to push down a join operation to the data source.

When cost-based join pushdown is enabled, the connector only pushes down join operations if the available Table statistics suggest that doing so improves performance. Note that if no table statistics are available, join operation pushdown does not occur to avoid a potential decrease in query performance.

The following table describes catalog configuration properties for join pushdown:

Property name

Description

Default value

join-pushdown.enabled

Enable join pushdown. Equivalent catalog session property is join_pushdown_enabled.

true

join-pushdown.strategy

Strategy used to evaluate whether join operations are pushed down. Set to AUTOMATIC to enable cost-based join pushdown, or EAGER to push down joins whenever possible. Note that EAGER can push down joins even when table statistics are unavailable, which may result in degraded query performance. Because of this, EAGER is only recommended for testing and troubleshooting purposes.

AUTOMATIC

Note

If the remote system does not expose table statistics, then join pushdown does not occur regardless of how join-pushdown.strategy is configured.

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 dynamic-filtering.enabled property in your catalog configuration file to false.

Wait timeout#

By default, table scans on the connector are delayed up to 20 seconds until dynamic filters are collected from the build side of joins. Using a large timeout can potentially result in more detailed dynamic filters. However, it can also increase latency for some queries.

You can configure the dynamic-filtering.wait-timeout property in your catalog properties file:

dynamic-filtering.wait-timeout=1m

You can use the dynamic_filtering_wait_timeout catalog session property in a specific session:

SET SESSION example.dynamic_filtering_wait_timeout = 1s;

Compaction#

The maximum size of dynamic filter predicate, that is pushed down to the connector during table scan for a column, is configured using the domain-compaction-threshold property in the catalog properties file:

domain-compaction-threshold=100

You can use the domain_compaction_threshold catalog session property:

SET SESSION domain_compaction_threshold = 10;

By default, domain-compaction-threshold is set to 32. When the dynamic predicate for a column exceeds this threshold, it is compacted into a single range predicate.

For example, if the dynamic filter collected for a date column dt on the fact table selects more than 32 days, the filtering condition is simplified from dt IN ('2020-01-10', '2020-01-12',..., '2020-05-30') to dt BETWEEN '2020-01-10' AND '2020-05-30'. Using a large threshold can result in increased table scan overhead due to a large IN list getting pushed down to the data source.

Metrics#

Metrics about dynamic filtering are reported in a JMX table for each catalog:

jmx.current."io.trino.plugin.jdbc:name=example,type=dynamicfilteringstats"

Metrics include information about the total number of dynamic filters, the number of completed dynamic filters, the number of available dynamic filters and the time spent waiting for dynamic filters.

Security#

The connectors include a number of security-related features, detailed in the following sections.

User impersonation#

The connectors support user impersonation.

You can enable it in the catalog file with the following configuration:

stargate.impersonation.enabled=true

Kerberos authentication#

The connectors support Kerberos authentication. Use the following properties in the catalog properties file to configure it.

stargate.authentication.type=KERBEROS
kerberos.client.principal=example@example.com
kerberos.client.keytab=/etc/kerberos/example.keytab
kerberos.config=/etc/krb5.conf
kerberos.remote.service-name=server-service-name
kerberos.service-principal-pattern=${SERVICE}@${HOST}
kerberos.service-use-canonical-hostname=true

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.service-principal-pattern is optional and defaults to ${SERVICE}@${HOST}. kerberos.service-use-canonical-hostname is also optional, and defaults to true.

Password credential pass-through#

The connectors support password credential pass-through. It uses the user credentials on the local cluster to connect to the remote cluster.

To enable it, edit the catalog properties file to include the authentication type, and remove the connection-user and connection-password properties:

stargate.authentication.type=PASSWORD_PASS_THROUGH

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

OAuth 2.0 token pass-through#

The connectors support OAuth 2.0 token pass-through to use a locally-issued access token to authenticate with the remote cluster.

To enable this feature, the remote cluster must be configured to use OAuth 2.0 authentication. Set the stargate.authentication.type catalog configuration property to OAUTH2_PASSTHROUGH:

stargate.authentication.type=OAUTH2_PASSTHROUGH

Starburst Cached Views#

The connectors support table scan redirection to improve performance and reduce load on the remote catalog.