Starburst SQL Server connector#

The Starburst SQL Server Connector is an extended version of the SQL Server connector with configuration and usage identical.

The following improvements are included:

Note

The additional features of the connector require a valid Starburst Enterprise license, unless otherwise noted.

Configuration#

The connector configuration is identical to the configuration for the base SQL Server connector.

A minimal configuration, with the connector-name sqlserver and the JDBC connections details, is shown in the following snippet:

connector.name=sqlserver
connection-url=jdbc:sqlserver://<host>:<port>;database=<database>
connection-user=sqlserver_username
connection-password=sqlserver_password

The connection-url parameter can use any valid JDBC connection string supported by the SQL Server JDBC driver. This allows you to specify a SQL Server instance and other parameters as desired in your specific use case.

Dynamic catalog selection#

The default configuration, e.g. in a file etc/oursqlserver.properties, enables the connection to one database running on a SQL Server instance.

connector.name=sqlserver
connection-url=jdbc:sqlserver://dbserver.example.com:1443/exampledb

The connector supports connecting to multiple SQL Server databases using a single catalog by setting a override-catalog session property. This support has to be enabled in the catalog properties file with the sqlserver.override-catalog.enabled property:

connector.name=sqlserver
connection-url=jdbc:sqlserver://dbserver.example.com:1443/exampledb
sqlserver.override-catalog.enabled=true

With sufficient access rights, available to the user specified in connection-user, the preceding example allows you to query any table in any schema in the database exampledb on the SQL Server dbserver:

SELECT * FROM oursqlserver.exampleschema.exampletable;

In order to query another database, such as testdb, you have override the database configured in the catalog. From then on you can query that database in the current user session:

SET SESSION oursqlserver.override-catalog=testdb;
SELECT * FROM oursqlserver.testdbschema.testdbtable;

Note

The access rights to the databases, schemas, tables and actual rows, continue to be determined by the configured user for the connection, and any other security setup like impersonation or Apache Ranger integration. Make sure these access rights are as restrictive as required.

SQL Server to SEP read type mapping#

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

SQL Server to SEP read type mapping#

SQL Server type

SEP type

Notes

BIT

BOOLEAN

BIGINT

BIGINT

SMALLINT

SMALLINT

TINYINT

TINYINT

INT

INTEGER

DECIMAL(p, s), ``NUMERIC(p, s)

DECIMAL(p, s)

for p <= 38

DOUBLE PRECISION

DOUBLE

FLOAT

DOUBLE

BINARY

VARBINARY

CHAR(n)

CHAR(n)

VARCHAR(n)

VARCHAR(n)

NCHAR(n)

NCHAR(n)

NVARCHAR(n)

NVARCHAR(n)

DATE

DATE

DATETIME2(n)

TIMESTAMP(n)

No other types are supported.

SEP to SQL Server write type mapping#

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

SEP to SQL Server write type mapping#

SEP type

SQL Server type

Notes

BOOLEAN

BIT

BIGINT

BIGINT

INTEGER

INT

SMALLINT

SMALLINT

DOUBLE

DOUBLE PRECISION

CHAR(n <= 4000)

NCHAR(n)

CHAR(n > 4000)

NVARCHAR(max)

VARCHAR(n <= 4000)

NVARCHAR(n)

VARCHAR, VARCHAR(n > 4000)

NVARCHAR(max)

VARBINARY

VARBINARY(max)

DATE

DATE

TIMESTAMP(n <= 7) without timezone

DATETIME2(n)

TIMESTAMP(n > 7) without timezone

DATETIME2(7)

Performance#

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

Table statistics#

This feature is available for free, and does not require a valid license.

The SQL Server 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 SQL Server and retrieved by the connector.

The connector can use information stored in single-column statistics. SQL Server Database can automatically create column statistics for certain columns. If column statistics were not created automatically for a certain column, you can create them by executing the following statement in SQL Server Database.

CREATE STATISTICS my_statistics_name ON table_schema.table_name (column_name);

SQL Server Database routinely updates the statistics. In some cases, you may want to force statistics update (e.g. after defining new column statistics or after changing data in the table). You can do that by executing the following statement in SQL Server Database.

UPDATE STATISTICS table_schema.table_name;

Refer to SQL Server documentation for information about options, limitations and additional considerations.

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.

Pushdown#

The connector supports all pushdown aggregate functions of the SQL Server connector.

Additionally, it supports pushdown of the following aggregate functions:

  • avg(bigint)()

  • avg(float)()

  • avg(decimal)()

  • count(column)()

Caching table projections#

The connectors supports table scan redirection to improve performance and reduce load on the data source.

Security#

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

User impersonation#

The SQL Server connector supports user impersonation.

User impersonation can be enabled in the catalog file:

sqlserver.impersonation.enabled=true

User impersonation in SQL Server connector is based on EXECUTE AS USER. For more details visit: docs.microsoft.com.