SAS SpeedyStore Data Connector

Data Connector Options for SingleStore

Authenticate to Microsoft Azure with Single Sign-On

An administrator can enable single sign-on authentication by configuring Microsoft Entra ID (formerly Azure Active Directory) as an OIDC provider. Additional steps are required for SAS SpeedyStore. For the configuration steps, see Enabling Single Sign-On for Microsoft Azure in SAS SpeedyStore: Administration and Configuration Guide.

In the SingleStore caslib, if you use single sign-on authentication, omit the userName=, password=, and authenticationDomain= options.

If single sign-on authentication is not enabled, then specify the userName= and password= options, or the authenticationDomain= option, to authenticate.

Support for SAS SpeedyStore

SAS SpeedyStore includes two main components for connecting to data:

• The SAS SpeedyStore data connector enables CAS to access tables stored in a SingleStore database that is integrated in SAS. You must license the product SAS SpeedyStore. See SAS SpeedyStore: Administration and Configuration Guide. See also Requirements for SAS SpeedyStore in System Requirements for the SAS Viya Platform.

  A standard data connector is also available to access tables in an external SingleStore database. For more information, see Two Data Connectors: When to Use SingleStore Standard and When to Use SAS SpeedyStore.

• The SingleStore LIBNAME statement connection enables SAS Compute Server to access your SingleStore compatible data source. For more information, see LIBNAME Statement for SingleStore in SAS/ACCESS for Relational Databases: Reference and the topics that follow this one.

The SAS SpeedyStore data connector offers the following benefits:

• The SAS SpeedyStore data connector provides parallel data transfer between CAS and SingleStore. Parallel data transfer can improve performance for very large tables.
• By using SAS Embedded Process, the SAS SpeedyStore data connector automatically pushes WHERE expressions and computed columns to be processed in SingleStore. Performing these operations in the database reduces the amount of data sent through the network.

Two Data Connectors: When to Use SingleStore Standard and When to Use SAS SpeedyStore

Two data connectors are available for SingleStore:

• The SingleStore standard data connector provides the standard ability to connect to an external SingleStore database. To use this data connector, specify srctype='singlestore_standard' For more information, see SingleStore Standard Data Connector.
• The SAS SpeedyStore data connector is available only if you license the product SAS SpeedyStore. This data connector provides parallel data transfer and in-database features in addition to the standard features. The product SAS SpeedyStore includes an integrated instance of SingleStore. To use this data connector, specify srctype='singlestore'.
  Note: To learn whether SAS SpeedyStore is licensed, contact your site administrator.

Here are some guidelines about using the two data connectors:

• If SAS SpeedyStore is not licensed, then use the SingleStore standard data connector.
• If SAS SpeedyStore is licensed but the SingleStore database is external to SAS, then use the SingleStore standard data connector.
• If SAS SpeedyStore is licensed and the SingleStore database is integrated with SAS, then use the SAS SpeedyStore data connector.

Here are some important differences:

• Many options are supported in one data connector and not in the other data connector. Refer to the options that are documented for each data connector.
• Parallel data transfer and other in-database features are provided by the SAS SpeedyStore data connector and not by the SingleStore standard data connector.
• The system requirements for the data sources can differ, which could result in behavior differences. The SingleStore version that is integrated in SAS SpeedyStore could be a later version than the minimum required version for the SAS Viya platform. Note that you cannot move an external SingleStore database to function as an internal database for use with SAS SpeedyStore.

Restrictions for CAS Features

You might need to adjust your code for the following behavior:

• Compression options are ignored when you create data. SingleStore compresses data by default. SingleStore compression is not reported in the output from the tableInfo action.
• Input tables can be columnstore or rowstore. Output tables are always columnstore.
• When you edit a table, a copy of the table is created. The copy is referred to as a snapshot. This copy helps to maintain data consistency. The copy contains a multipass column for multipass processing. CAS attempts to create the snapshot copy in the same database as the original table. If the snapshot copy cannot be created, then the operation fails. For example, if the user has insufficient permissions, the copy cannot be made.
• To edit a view, you must set the option createViewSnapshot="onLoad". Under the default setting createViewSnapshot="never", views are streamed without creating a snapshot copy of the view result set, and an attempt to edit a view causes an error. For more information about reading SingleStore views, see createViewSnapshot= Data Connector Option.
• If you create a SingleStore view by using external tools outside SAS, and the view references a table that was created by CAS, some column attributes in the view are not supported. CAS stores column attributes such as SAS formats and labels and certain data type attributes in the column comments when you save a table to SingleStore. The SingleStore definition of a view does not include column comments. Therefore, if the table that underlies a view was created by CAS, then any column attributes that are stored in column comments are not part of the view definition when the view is loaded into CAS.
• WHERE expressions have several requirements. See Restrictions for WHERE Processing and Computed Columns.
• Missing values are stored as null values in SingleStore. SAS special missing values are not supported, so they are also stored as null values. SingleStore null values are converted to SAS missing values when they are streamed into CAS.
• Table names and column names cannot contain 4-byte characters. This is a limitation of SingleStore. Beginning with 2025.04, 4-byte characters are supported in table labels and column labels. The SingleStore default encoding is utf8mb4 as of 2024.10. For multibyte data within tables, you must ensure that columns are defined with sufficient length. Truncation can cause partial characters, which results in an error in the log, and data fails to write to the database. For more information, see Character Set Considerations in SAS SpeedyStore: Administration and Configuration Guide.
• Beginning with 2026.05, the instance of SingleStoreDB that is included with SAS SpeedyStore has a default collation of utf8mb4_bin, which causes string comparisons to be case-sensitive. Previous versions have a default collation of utf8mb4_general_ci, which is not case-sensitive. New clusters are subject to the new default if you do not set the server collation in your deployment. Existing clusters continue to use the previous collation setting or the previous default if the collation was not set. In previous releases, SAS recommended setting the server collation to utf8mb4_bin in the deployment before any data is loaded into SingleStore. If you followed that recommendation, or if you are not using data that was previously loaded into SingleStore, then no changes are necessary. If you did not set the collation and used the default, the difference can lead to unexpected result sets if case distinctions are meaningful. You can change the setting for existing tables. For more information, see Setting or Modifying the Collation in SAS SpeedyStore: Administration and Configuration Guide.
• GPU processing is not supported.
• For the saveAttrs= parameter in the table.save action and the loadAttrs= parameter in the table.loadTable action, extended attributes tables typically have a naming convention of table-name.ATTRS.sashdat. If extended attributes tables are saved to SingleStore, the SASHDAT file extension is not used: table-name.ATTRS. In addition, SingleStore table names are limited to 64 characters. Due to the .ATTRS suffix, the table name is limited to 58 characters.
• For more limits, such as column name length, refer to the SingleStore documentation website.
• The table.tableInfo action returns a missing value (.) for the Source Modified Time value.
• The table.tableDetails action returns only the number of rows and the data size. Other details are returned as 0 (zero).
• The groupBy= parameter in the table.save action is not supported.
• The severityEst= parameter in the cdm.cdm action is not supported. However, severity model parameter estimates can be read through the severityStore= parameter.
• The image.flattenImages action is supported when the output table has fewer than 4K columns. FlattenImages generates a table with fewer than 4K columns when the input image width * height * 3 + 1 is less than or equal to 4096. If this value is greater than 4096, the output table is created and an error is generated when the table is saved to SingleStore.
• The applyRowOrder=TRUE parameter, applicable to the following actions, is not supported:
  • Clustering action set: kClus
  • Decision Tree action set: dtreeTrain, dtreePrune, dtreeSplit, dtreeScore, forestTrain, forestScore, gbtreeTrain, gbtreeScore
  • Factorization Machine action set: factmac
  • Robust Multivariate Outlier action set: mvoutlier
  • Sampling and Partitioning action set: srs, stratified, oversample, kfold
  • Support Vector Machine action set: svmTrain
• The following action sets or actions are not currently supported:
  • audio action set
  • deepLearn action set
  • deepNeural action set
  • deepRnn action set
  • graphSemiSupLearn action set
  • langModel action set
  • ldaTopic action set
  • searchAnalytics action set
  • smartData action set

Improving Performance Using In-Database Processing

Improving Performance for Multipass Analytics

Multipass analytics require data to be read more than once. Because the data connector streams data on demand, multiple passes can affect performance. The multipassMemory="cache" option can help this performance issue by reducing data movement. The requested data is temporarily loaded into the CAS disk cache and is released from the cache after the multipass action completes. You can further improve performance if you filter the data that is loaded.

Other choices are available for different use cases. The multipassMemory="stream" setting helps to conserve memory use, and uses a multipass column to improve performance. The backingStore="CASDISKCACHE" option completely avoids streaming, and loads the entire table in the CAS disk cache in the traditional manner. For a comparison of these choices, see multipassMemory= Data Connector Option.

Note that the singlePass= parameter has no effect on multipass behavior. For use with other data connectors, see Understanding the SinglePass Parameter in SAS Viya Platform: System Programming Guide.

Improving Performance Using SQL Pushdown

SQL pushdown is supported for these actions:

• Aggregate pushdown is supported by the simple.distinct action beginning in 2025.03.
• Aggregate pushdown is supported by the simple.summary action beginning in 2024.11.
• SELECT DISTINCT pushdown is supported by the simple.groupBy action beginning in 2024.09.

Aggregate pushdown enables CAS actions to push down aggregation requests to be processed in the database. The aggregated data is streamed to CAS. In general, performance improves when the aggregation can run in the database, instead of streaming the full data to CAS for aggregation. Here is how to process an aggregation request in the database:

• Either specify the aggregatePushdown=true data connector option or specify sql=true in an action that supports aggregate pushdown. If the sql= parameter is specified in the action, then that setting takes precedence over the aggregatePushdown= data connector option. If sql= is not set, then use aggregatePushdown=true to push down aggregation requests.
• Do not use the groupbyTable parameter in the simple.distinct action. This parameter is not supported for pushdown.
• Do not use any of the following parameters in the simple.summary action: freq=, groupByTable=, repeat=, weight=, whereTable=. These parameters are not supported for pushdown.

SELECT DISTINCT pushdown is not an aggregation request, but it does reduce the result set that is returned to CAS. Here is how to implement SELECT DISTINCT pushdown:

• Specify the aggregatePushdown=true data connector option. (The sql= parameter is not available in simple.groupBy.)
• Do not use any of the following parameters in the simple.groupBy action: aggregator=, freq=, scoreGt=, scoreLt=, weight=. These parameters are not supported for pushdown.

When an aggregation request is pushed down to the database, the following message is written in the SAS log:

NOTE: The aggregation is being calculated in the database.

For more information, see aggregatePushdown= Data Connector Option.

If a SAS log message indicates that a user-defined function is required, then download the open-aggregates user-defined functions (UDFs). See udfDatabase= Data Connector Option for more information.

Reading Data Types from SingleStore

The following table shows the data types that can be loaded from SingleStore into CAS. This table also shows the resulting data type for the data after it has been loaded into CAS. The length of the data format in CAS is based on the length of the source data.

Writing Data Types to SingleStore

The following table describes how CAS writes data to SingleStore data types.

Here is an example. A table is created in SingleStore by using a previously assigned caslib. In this code, informats are specified in the INPUT statement to properly read the data into the time_col and time_col_dec columns. The corresponding formats are permanently assigned by using the FORMAT statement. A format is necessary for writing a date or time data type to SingleStore. The time_col column is written as a TIME data type. The time_col_dec column is written as a TIME(6) data type, due to the .2 decimal value in the format time11.2.

libname mylib cas caslib="mycaslib";
data mylib.timedata;
   input item $ time_col time8. +1 time_col_dec time11.2;
   format time_col time8. time_col_dec time11.2;
   datalines;
apple 01:01:01 01:01:01.01
orange 11:59:59 11:59:59.44
;
run;

VARCHAR Data and the CAS Server

The CAS server supports loading, storing, and writing VARCHAR data. All tasks that can be completed using the CAS server use VARCHAR data. Any tasks that are not completed by the CAS server convert VARCHAR data to fixed-length CHAR data before the data is printed or saved.

For more information, see VARCHAR Support for Implicit and Explicit Data Type Conversion.

Integer Data Types and Numeric Precision

The CAS server supports loading, storing, and writing integer data types (INT32 and INT64). Some computations that can be completed by using the CAS server maintain the original data type. Consult the documentation to determine whether a CAS action supports an integer type. Any tasks that are not completed by the CAS server convert these data types into a SAS DOUBLE before the data is printed or saved. A SAS DOUBLE value maintains approximately 15 digits of precision.

When you read values that contain more than 15 decimal digits of precision into a DATA step that is running on the Compute Server, the data is converted to a DOUBLE value. Therefore, it is recommended that you check whether a DATA step runs on the CAS server. Check the log to verify where your DATA step is running. For more information, see DATA Step Processing Modes in SAS Cloud Analytic Services: DATA Step Programming.

When you read values that contain more than 15 decimal digits of precision into a procedure that is not supported on CAS, the values are rounded and converted to a DOUBLE value with 15 digits of precision. Most procedures are supported on the CAS server. However, it is recommended that you verify that a procedure is supported before you use it for large numeric values. For more information, see Base SAS Procedures Guide.