Starburst SAS connector#

The Starburst SAS connector lets you query SAS datasets stored in SAS7BDAT files with SQL. The connector reads .sas7bdat files from a local file system, a mounted network share, or Amazon S3, and exposes the files as schemas and tables.

Note

The Starburst SAS connector is a public preview in Starburst Enterprise platform (SEP). Contact your Starburst account team with questions or feedback.

Requirements#

To use the SAS connector, you need:

  • SAS datasets stored in the SAS7BDAT file format with the .sas7bdat file extension. The connector does not support other SAS file formats, such as .sas7bcat catalog files or .xpt transport files.

  • Access to the data directory, depending on where you store the files:

    • For a local file system or network-attached storage (NAS), the data directory at the same path on the coordinator and on every worker.

    • For Amazon S3 or S3-compatible object storage, network access from the coordinator and workers to the object storage.

  • A valid Starburst Enterprise license.

Configuration#

To configure the SAS connector, create a catalog properties file in etc/catalog, such as example.properties. Set connector.name to sas, and set sas.data-directory to the URI of the directory that contains your SAS7BDAT files:

connector.name=sas
sas.data-directory=file:///mnt/nas/sas

The scheme of the sas.data-directory URI determines which file system the connector uses. For more information, see File system access configuration.

General configuration properties#

The following table describes the catalog configuration properties for the connector:

Property name

Description

Default

sas.data-directory

Required. URI of the directory that contains the SAS7BDAT files. Use the file:// scheme for a local or mounted file system, such as file:///mnt/nas/sas. Use the s3://, s3a://, or s3n:// scheme for Amazon S3, such as s3://example-bucket/sas. The connector does not accept a path without a scheme.

sas.mapping-type

Strategy for mapping the data directory to schemas and tables. Possible values are:

  • FS: The directory structure defines the schemas and tables.

  • JSON: A JSON mapping file defines the schemas and tables.

For more information, see Schema and table mapping.

FS

sas.json-file

Path to the JSON mapping file on the local file system of the coordinator. Required when sas.mapping-type is set to JSON.

sas.split-count

Number of splits that the connector divides each table into. Set a value greater than 1 to let multiple workers read different page ranges of the same file in parallel. For more information, see Parallel reads.

1

sas.min-page-per-split

Minimum number of SAS file pages in each split. The connector does not create splits smaller than this value, regardless of the sas.split-count value.

2000

File system access configuration#

The connector selects a file system based on the scheme of the sas.data-directory URI:

  • file://: A local file system, including a NAS share mounted on each node.

  • s3://, s3a://, or s3n://: Amazon S3 or S3-compatible object storage.

The connector does not support other file systems, such as Azure Storage, Google Cloud Storage, or HDFS.

Local file system#

To read SAS7BDAT files from a local file system, set sas.data-directory to a file:// URI. The directory must exist at the same path on the coordinator and on every worker. To use a NAS share, mount the share at the same mount point on every node, for example with NFS.

connector.name=sas
sas.data-directory=file:///mnt/nas/sas

Amazon S3#

To read SAS7BDAT files from Amazon S3 or S3-compatible object storage, set sas.data-directory to an S3 URI. Configure the connection with the S3 file system properties, such as s3.region:

connector.name=sas
sas.data-directory=s3://example-bucket/sas
s3.region=us-east-1

The connector supports the standard S3 file system properties, including authentication with an IAM role or access keys, custom endpoints, path-style access, and S3 security mapping. The connector enables S3 access based on the URI scheme, so you do not set fs.native-s3.enabled.

You can also set sas.data-directory to the root of a bucket, such as s3://example-bucket.

Schema and table mapping#

The sas.mapping-type property controls how the connector maps the files in the data directory to schemas and tables. The connector supports the following mapping types:

  • FS: The directory structure defines the schemas and tables. This is the default.

  • JSON: A JSON mapping file defines the schemas and tables.

Schema and table names are not case-sensitive. If two directories or files in the same location have names that differ only by case, queries that use that location fail with an ambiguous name error.

File system mapping#

With the FS mapping type, each subdirectory directly under sas.data-directory is a schema, and each .sas7bdat file in a schema directory is a table. The table name is the file name without the .sas7bdat extension.

The following directory structure results in a finance schema with the revenue and expenses tables, and an hr schema with the headcount table:

/mnt/nas/sas/
├── finance/
│   ├── revenue.sas7bdat
│   └── expenses.sas7bdat
└── hr/
    └── headcount.sas7bdat

The connector also exposes .sas7bdat files in subdirectories of a schema directory as tables. The table name includes the path relative to the schema directory, such as 2024/revenue. Enclose these table names in double quotes in queries:

SELECT * FROM example.finance."2024/revenue";

JSON file mapping#

With the JSON mapping type, a JSON file maps schema and table names to SAS7BDAT files, independent of the directory structure. Use this mapping type when the directory structure does not match how you want to query the data, or when you want several schemas to point to the same directory.

To use JSON mapping, set sas.mapping-type to JSON, and set sas.json-file to the path of the mapping file:

connector.name=sas
sas.data-directory=file:///mnt/nas/sas
sas.mapping-type=JSON
sas.json-file=/etc/starburst/sas-mapping.json

The mapping file must be on the local file system of the coordinator, including when sas.data-directory uses an S3 URI.

The mapping file contains a schemas array. Each entry in the array sets the schema name in the schema field, and defines the tables in one of the following ways:

  • tables: A list of tables. Each table sets the table name in the table field and the path to the SAS7BDAT file in the file field.

  • path: A directory. Each .sas7bdat file directly in the directory is a table named after the file. The connector does not include files in subdirectories.

All paths are relative to sas.data-directory. Schema names must be unique, and are not case-sensitive.

The following example maps the finance schema to two specific files, and maps the hr schema to all SAS7BDAT files in the exports/hr_current directory:

{
  "schemas": [
    {
      "schema": "finance",
      "tables": [
        { "table": "revenue", "file": "/raw/2024/rev_final.sas7bdat" },
        { "table": "expenses", "file": "/raw/2024/exp_q4.sas7bdat" }
      ]
    },
    {
      "schema": "hr",
      "path": "/exports/hr_current"
    }
  ]
}

Type mapping#

Because Trino and SAS each support types that the other does not, this connector modifies some types when reading data. The connector maps each column based on the SAS column type and the SAS display format recorded in the file.

SAS to Trino type mapping#

The connector maps SAS types to the corresponding Trino types following this table:

SAS to Trino type mapping#

SAS column type and display format

Trino type

Character

VARCHAR(n), where n is the column length. VARCHAR if the length is unknown.

Numeric with no display format

DOUBLE

Numeric with the PERCENT format

DOUBLE

Numeric with the HOUR, MMSS, or HHMM format

INTEGER

Numeric with the MMDDYY or DDMMYYD format

DATE

Numeric with a format name that contains TIME, such as DATETIME

TIMESTAMP(3)

Numeric with the E8601TM or E8601LS format

VARCHAR

Numeric with any other format

VARCHAR

The connector returns the values of columns mapped to VARCHAR as text. To use the values as a different type, convert them with Conversion functions, such as CAST.

SQL support#

The connector provides read-only access to data and metadata in SAS7BDAT files. The connector supports the globally available and read operation statements. The connector does not support statements that write data or modify metadata, such as INSERT, CREATE TABLE, or DELETE.

The connector reads the file system at query time and does not cache schema or table listings. If a file is added, removed, or replaced while a query runs, the query can fail.

Basic usage examples#

The following examples use the example catalog with the directory structure from File system mapping.

List the schemas in the catalog:

SHOW SCHEMAS FROM example;

List the tables in the finance schema:

SHOW TABLES FROM example.finance;

Show the columns and types of the revenue table. The connector reads the column names and types from the SAS7BDAT file metadata:

DESCRIBE example.finance.revenue;

Query the data:

SELECT region, sum(amount) AS total
FROM example.finance.revenue
WHERE fiscal_year = 2024
GROUP BY region;

Performance#

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

Parallel reads#

By default, the connector reads each table as a single split. To read large files in parallel, set sas.split-count to a value greater than 1. The connector divides each file into page ranges, and multiple workers read the page ranges in parallel.

The sas.min-page-per-split property sets the minimum number of pages in each split. The connector does not divide small files into small splits. For example, with sas.split-count=4 and the default sas.min-page-per-split value of 2000, the connector reads a file with 10,000 pages as four splits of 2,500 pages.

Materialized view substitution#

You can use a SAS table as the base table of an Iceberg materialized view that is eligible for automatic materialized view substitution. SEP can then answer queries on the SAS table from the materialized view instead of reading the SAS7BDAT files.