How to Enable SAS® SpeedyStore Bottomless Databases
SingleStore Unlimited Storage, also known as “bottomless” databases, separates compute and storage by using cloud object storage for data persistence. In other words, data in a bottomless database is ultimately stored in cloud object storage — a space that is virtually infinite and inexpensive. Local disks, which are limited and more costly, are used mainly as cache. The authoritative copy of the data always lives in the cloud object store.
Because object storage is “effectively infinite”, your database storage is considered bottomless.
This opens up a wide range of possibilities. It allows SAS SpeedyStore to scale elastically and cost-effectively, handling massive datasets without overwhelming compute resources. It also makes backup, archiving, and disaster recovery much simpler.
Behind the scenes, SingleStore manages how data moves in and out of local disks as activity happens. When you insert or update data, SingleStore writes it to local storage and then asynchronously persists it to object storage. Frequently accessed data is cached locally. If something isn’t in the local cache — for example, after a restart or if it’s rarely used — SingleStore simply fetches it from object storage.
Consider the following scenario: you possess terabytes of SAS data sets that need to be utilized within SAS Viya.
Storing these data sets in cloud block storage can be prohibitively expensive, and direct access from cloud object storage is not feasible due to its lack of POSIX compliance.
A more efficient solution is to leverage a SAS SpeedyStore bottomless database. This approach ensures all data sets remain accessible at a low cost, while frequently accessed data is cached locally to deliver high-performance access.
Creating a bottomless database
Now, let’s explore the process of creating a SAS SpeedyStore bottomless database.
First, it is important to note that this feature is supported only when SAS SpeedyStore is deployed on specific cloud platforms: AWS, Microsoft Azure, and Google Cloud. In each case, the corresponding object storage service—AWS S3, Azure Data Lake Storage (ADLS), or Google Cloud Storage (GCS)—is used to provide the bottomless storage layer.
Second, creating a bottomless database requires a SingleStore user with the SUPER permission. By default, the SingleStore administrator account provisioned during the deployment of SAS Viya and SAS SpeedyStore does not have this permission, although the root user does. As a best practice, rather than using the root account, it is recommended to grant the SUPER permission to a designated administrator account for database creation.
Finally, you must prepare the target object (blob) storage that will serve as the repository for your bottomless database.
For example, on Azure, this requires creating a Storage Account with the hierarchical namespace enabled. You will also need to provide the following details:
The final requirement is the access key for the storage account:
Step-by-Step Instructions
Now, we’re ready to create a bottomless database.
1 - Retrieve the SingleStore root password
A Kubernetes administrator with access to the SAS Viya cluster can run the following command to obtain the SingleStore root password:
kubectl -n {your-sas-viya-namespace} get secret sas-singlestore-cluster -o yaml \
| grep "^ ROOT_PASSWORD" \
| awk '{print $2}' \
| base64 -d --wrap=0
This step is necessary because only the root account initially has the SUPER privilege, which is required to create bottomless databases.
2 - Connect to the SingleStore cluster
Use SingleStore Studio to connect with the root user and the password retrieved in the previous step.
You will use root only temporarily. Best practice is to transfer the needed privilege to an admin account and avoid continued use of root.
3 - Grant the SUPER privilege to the admin account
In the SQL Editor, run:
GRANT SUPER ON *.* TO 'admin'@'%';
FLUSH PRIVILEGES;
The admin account is created automatically during SAS Viya and SAS SpeedyStore deployment. Granting it the SUPER privilege allows administrators (not root) to create bottomless databases. You may also grant this privilege to other trusted admin accounts by replacing admin with their usernames.
4 - Reconnect with the admin user
Disconnect from the root account in SingleStore Studio, and reconnect using the admin account (or any other account with the SUPER privilege).
This ensures ongoing administration tasks are performed securely by designated admin accounts, not root.
5 - Create the bottomless database
In the SQL Editor, run:
CREATE DATABASE bottomlessdb1
ON Azure "bottomlessblobcont/bottomlessdir1"
CREDENTIALS '{
"account_name":"bottomlesssa",
"account_key":"3M9hkqQ9xxxxxxxxxxxxxxxxxxxxxzl+AStWoKagA=="
}';
This command creates a bottomless database named bottomlessdb1. The database persists its data in the specified Azure Storage Account container and directory, using the provided access credentials. Equivalent commands exist for AWS and Google Cloud.
Congratulations! You have successfully created a SingleStore bottomless database, which leverages cloud object storage for cost-effective and scalable data persistence.
Testing with SAS Viya
Now, let’s verify that we can write to this bottomless database from SAS Viya using SAS Compute.
80 /* S2 parameters */
81 %let MYS2UID=admin ;
82 %let MYS2PWD=xxxxx ;
83 %let MYS2PORT=3306 ;
84 %let MYS2DB=bottomlessdb1 ;
85 %let MYS2HOST=svc-sas-singlestore-cluster-dml.{your-sas-viya-namespace}.svc.cluster.local ;
86
87 /* Assign a library to SAS SpeedyStore bottomless database */
88 libname sass2 s2 user="&MYS2UID"
89 password="&MYS2PWD" database="&MYS2DB"
90 server="&MYS2HOST" port=&MYS2PORT ;
NOTE: Libref SASS2 was successfully assigned as follows:
Engine: S2
Physical Name: svc-sas-singlestore-cluster-dml.{your-sas-viya-namespace}.svc.cluster.local
The library was successfully assigned, confirming the connection to the bottomlessdb1 database.
92 /* Load a table in SpeedyStore */
93 data sass2.product_orders(bulkload=yes) ;
94 set sasdm.product_orders ;
95 run ;
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
NOTE: There were 14400000 observations read from the data set SASDM.PRODUCT_ORDERS.
NOTE: The data set SASS2.product_orders has 14400000 observations and 11 variables.
NOTE: DATA statement used (Total process time):
real time 22.77 seconds
cpu time 18.71 seconds
Over 14 million rows were successfully written to the product_orders table in the bottomless database.
97 /* List SpeedyStore tables */
98 proc datasets lib=sass2 ;
Directory
Libref SASS2
Engine S2
Physical Name svc-sas-singlestore-cluster-dml.{your-sas-viya-namespace}.svc.cluster.local
DataBase bottomlessdb1
User admin
Server Version 8.9.29
Client Version 3.3.0
Server Comments SingleStoreDB source distribution (compatible; MySQL Enterprise & MySQL Commercial)
DBMS
Member Member
# Name Type Type
1 bigprdsale DATA TABLE
2 orders DATA TABLE
3 product_orders DATA TABLE
4 products DATA TABLE
99 quit ;
NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
The new product_orders table now appears alongside existing tables (bigprdsale, orders, products).
At this point, if you inspect the configured cloud object storage, you will see the files that back the bottomless storage for your database. This confirms that data persistence is being handled seamlessly by the underlying storage service:
That concludes today’s walkthrough—thank you for reading!
A special thanks to Marc Price for his valuable input on this topic.
If you’d like to explore SingleStore bottomless databases further, here are some useful resources:
And don’t miss the Engineering and Analyzing Data at Scale with SAS SpeedyStore course, which provides a hands-on look at the powerful benefits of working with SAS SpeedyStore.
Find more articles from SAS Global Enablement and Learning here.
