Starburst Remote connector#

The Starburst Remote connector lets you link a local catalog on your Starburst Enterprise cluster to a catalog on a remote Starburst Enterprise cluster with the same version.

The Remote connector 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 the Remote connector.

The Remote connector 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.

Note

The connector requires 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, for example, remote_postgres.properties.

Configure the catalog to use the Remote connector by setting the connector.name property to starburst-remote.

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

  • The JDBC protocol is presto.

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

  • Follow the URL with */catalog-name* 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.

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

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.

Example 1: A catalog remotehive.properties to access a remote Hive catalog:

connector.remote=starburst-remote
connection-url=jdbc:presto://remote-cluster.example.net:8080/hive
connection-user=some_sep_user
connection-password=some_password

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

Example 2: A catalog remotedb to access a remote PostgreSQL catalog called postgresdb accessed, with plain text password:

connector.name=starburst-remote
connection-url=jdbc:presto://remote-cluster.example.net:8080/postgresdb
connection-user=some_sep_user
connection-password=some_password

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

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

connector.name=starburst-remote
connection-url=jdbc:presto://remote-cluster.example.net:8080/website?\
  SSL=true&\
  SSLTrustStorePath=/usr/local/jks/pgadmin.jks&\
  SSLTrustStorePassword=some_password
connection-user=remotepost

Remote connector to PrestoSQL read 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 Remote connector type mapping#

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.

Performance#

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

Table statistics#

The Remote connector supports table and column statistics 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 the Remote connector.

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

Pushdown#

The connector supports aggregation pushdown for processing the following aggregate functions that return a data type supported by the connector:

The connector supports pushdown for LIMIT clauses of a SELECT statement.

Dynamic filtering#

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

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

Security#

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

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:

starburst.authentication.type=PASSWORD_PASS_THROUGH

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

Caching table projections#

The connector supports table scan redirection to improve performance and reduce load on the remote catalog.

Limitations#

The connector only supports read access, which means many SQL statements, , including the following, are not supported: