Starburst Stargate

Starburst Stargate is a connector 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 uses a JDBC connection to the remote cluster. 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.

In addition, note that the connector only allows read access.

Requirements

• Starburst Enterprise clusters using the same version.

• The workers and coordinators of the source cluster must be able to connect to the target coordinator using the configured HTTP or HTTPS protocol.

• A valid Starburst Enterprise license.

Configuration

There is no special preparation required on the remote cluster. Make sure both local and remote clusters run the exact same SEP version.

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 Starburst Stargate 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 a 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	Default value
case-insensitive-name-matching	Support case insensitive schema and table names.	false
case-insensitive-name-matching.cache-ttl	This value should be a duration.	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.	null
case-insensitive-name-matching.config-file.refresh-period	Frequency with which Trino checks the name matching configuration file for changes. This value should be a duration.	(refresh disabled)
metadata.cache-ttl	The duration for which metadata, including table and column statistics, is cached.	0s (caching disabled)
metadata.cache-missing	Cache the fact that metadata, including table and column statistics, is not available	false
metadata.cache-maximum-size	Maximum number of objects stored in the metadata cache	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.	1000
dynamic-filtering.enabled	Push down dynamic filters into JDBC queries	true
dynamic-filtering.wait-timeout	Maximum duration for which Trino will wait 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.	20s

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 connector supports globally available and read operation statements to access data and metadata in JDBC.

ALTER TABLE EXECUTE

The connector supports 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 connector provides specific table functions to access Starburst Stargate.

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 connector includes a number of performance features, detailed in the following sections.

Table statistics

Starburst Stargate supports 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 connectors 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 connector supports 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 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 connector supports pushdown for a number of operations:

• Join pushdown

• Limit pushdown

• Top-N pushdown

Aggregate pushdown for the following functions:

• avg()

• approx_distinct()

• checksum()

• count(), also count(distinct x)

• max()

• max_by()

• min()

• sum()

• covar_samp()

• covar_pop()

• geometric_mean()

• stddev() and stddev_samp()

• stddev_pop()

• var_pop()

• variance() and var_samp()

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 connector includes a number of security-related features, detailed in the following sections.

User impersonation

The connector supports user impersonation.

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

stargate.impersonation.enabled=true

Kerberos authentication

The connector supports 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 connector supports 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 connector supports 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 connector supports table scan redirection to improve performance and reduce load on the remote catalog.