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
.sas7bdatfile extension. The connector does not support other SAS file formats, such as.sas7bcatcatalog files or.xpttransport 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 |
|---|---|---|
|
Required. URI of the directory that contains the SAS7BDAT files. Use the
|
|
|
Strategy for mapping the data directory to schemas and tables. Possible values are:
For more information, see Schema and table mapping. |
|
|
Path to the JSON mapping file on the local file system of the coordinator.
Required when |
|
|
Number of splits that the connector divides each table into. Set a value
greater than |
|
|
Minimum number of SAS file pages in each split. The connector does not
create splits smaller than this value, regardless of the |
|
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://, ors3n://: 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 thetablefield and the path to the SAS7BDAT file in thefilefield.path: A directory. Each.sas7bdatfile 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 column type and display format |
Trino type |
|---|---|
Character |
|
Numeric with no display format |
|
Numeric with the |
|
Numeric with the |
|
Numeric with the |
|
Numeric with a format name that contains |
|
Numeric with the |
|
Numeric with any other format |
|
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.