Starburst SingleStore (MemSQL) connector#

The Starburst SingleStore (formerly known as MemSQL) connector is an extended version of the SingleStore connector. It can be used to connect and query SingleStore databases. Configuration and usage is identical.

Note

The connector name is currently still memsql. No changes are required at this time to any existing catalogs. New catalogs must also use the memsql connector name.

Requirements#

SQL support#

The connector supports all of the SQL statements listed in the SingleStore connector documentation.

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 SingleStore 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 SingleStore and retrieved by the connector.

Table-level statistics are based on SingleStore’s INFORMATION_SCHEMA.TABLE_STATISTICS table.

SingleStore can automatically update its table and column statistics. In some cases, you may want to force a statistics update, for example after creating new columns or after changing data in the table. You can do that by executing the following statement in the SingleStore database:

ANALYZE TABLE table_name;

Note

SingleStore statistics are estimates, and SEP and SingleStore may use statistics information in different ways. For this reason, the accuracy of table and column statistics returned by the SingleStore connector might be lower than that of others connectors.

Improving statistics accuracy

You can improve statistics accuracy and access column-level statistics with histogram statistics. Column-level statistics are based on SingleStore’s column statistics INFORMATION_SCHEMA.ADVANCED_HISTOGRAMS table, if available. If that table is not available, the information is based on the INFORMATION_SCHEMA.OPTIMIZER_STATISTICS table instead.

The ADVANCED_HISTOGRAMS table includes additional stats such as the MIN and MAX values for a column, which are not available in the LEGACY_HISTOGRAMS table. SingleStore’s ADVANCED_HISTOGRAMS feature requires version 6.5 or later, and a cardinality_estimation_level >= 6.5.

To check for these conditions and determine whether ADVANCED_HISTOGRAMS are available to you, execute the following statement in SingleStore:

SELECT
   COLUMN_NAME,
   IF(RANGE_STATS=1, true, false) as histograms_available,
   IF(ADVANCED_HISTOGRAMS=1, 'Advanced', 'Legacy') as histogram_type
FROM INFORMATION_SCHEMA.OPTIMIZER_STATISTICS
WHERE DATABASE_NAME = 'db' AND TABLE_NAME = 'table';

If they are available in your SingleStore version, use the following statement to populate the ADVANCED_HISTOGRAMS table:

ANALYZE TABLE table_name COLUMNS ALL ENABLE;

Refer to the SingleStore documentation for information about options, limitations and additional considerations.

Pushdown#

The connector supports pushdown of all operations listed in the SingleStore connector documentation.

In addition, the connector supports pushdown for the following aggregate 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 degrade query performance and is only recommended for testing and troubleshooting.

AUTOMATIC

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.

Starburst Cached Views#

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