While SAS/ACCESS engines and Data Connectors are included in every SAS Viya foundational offering, there is still work to do to configure access to databases from SAS Viya. If the configuration of SAS/ACCESS is part of your role, you probably know that most of the information necessary is available in the README(s) file(s). You can access it in the my.sas.com portal or in the deployment assets once you have downloaded and extracted them.
In this blog, I will walk through the SAS/ACCESS configuration section of the README file.
SAS/ACCESS (in its entirety, which includes Data Connectors) relies on a piece of software (aka the “database client” or sometimes simply a database/ODBC/JDBC driver) accessible locally to communicate with a database server available mostly remotely. Some database clients are already embedded in a SAS Viya deployment like PostgreSQL, Microsoft SQL Server, Snowflake, etc. But several others are not.
And this is where the attached storage comes into play. You need a place to install database clients and make them available to all SAS Viya components that need them. Usually, you can use NFS to share them easily and with SAS Viya you can provision a NFS server using the Terraform scripts provided (SAS Viya Infrastructure As Code GitHub projects: viya4-iac-azure, viya4-iac-gcp, viya4-iac-aws).
That drives us to some other questions.
SAS Viya has many data processing engines: SAS Compute Server, CAS, MAS, ESP, etc. They potentially need access to database data. Configuring access to database clients in the Kubernetes world is not a trivial task.
SAS provides example files (PatchTransformers) to attach the storage you used to install the database clients so that it is accessible from the required pods.
In the recent versions (since release 2023.03), 4 files are provided and their names don’t make it easy to understand which SAS engine is targeted (except maybe the first one).
You can find them in $deploy/sas-bases/examples/data-access:
Use it when you want to make the database clients installed available to CAS (controller and workers). The component targeted by this PatchTranformer is sas-cas-operator.
Use it when you want to make the database clients installed available to SAS/CONNECT. The component targeted by this PatchTranformer is sas-connect-spawner.
Use it when you want to make the database clients installed available to SAS Compute Sessions triggered in different contexts. The components targeted by this PatchTranformer are sas-batch, sas-compute, sas-connect, sas-job-flow-scheduling and sas-launcher.
Use it when you want to make the database clients installed available to SAS Cloud Data Exchange (colocated data agent). The component targeted by this PatchTranformer is sas-data-agent-server-colocated.
Let’s have a look at the data-mounts-cas.sample.yaml (for CAS) sample PatchTransformer file and identify the main sections:
Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.
Finally, when we have all the information in mind, we can summarize SAS/ACCESS configuration with the following to-do list. We assume here that we are deploying SAS Viya from the “Client” machine (the machine where we downloaded SAS Viya assets).
/viya-share/access-clients (root directory)
/viya-share/access-clients/odbc (to store odbc.ini and odbcinst.ini)
/viya-share/access-clients/jdbc (to store additional JDBC drivers)
/viya-share/access-clients/oracle (to store the Oracle client for example)
/viya-share/access-clients/teradata (to store Teradata client for example)
data-mounts-cas.yaml example:
# General example for adding mounts to CAS workers
# PatchTransformer
apiVersion: builtin
kind: PatchTransformer
metadata:
name: data-mounts-cas
patch: |-
## NFS path example - kubernetes will mount these for you
- op: add
path: /spec/controllerTemplate/spec/containers/0/volumeMounts/-
value:
name: access
mountPath: "/access-clients"
- op: add
path: /spec/controllerTemplate/spec/volumes/-
value:
name: access
nfs:
path: /export/access-clients
server:
## JAR files for JDBC NFS path example - kubernetes will mount these for you
## Do not change the mountPath value from "/data-drivers/jdbc"
- op: add
path: /spec/controllerTemplate/spec/containers/0/volumeMounts/-
value:
name: jdbcjars
mountPath: "/data-drivers/jdbc"
- op: add
path: /spec/controllerTemplate/spec/volumes/-
value:
name: jdbcjars
nfs:
path: /export/access-clients/jdbc
server:
target:
kind: CASDeployment
annotationSelector: sas.com/sas-access-config=true
ODBCINI=/access-clients/odbc/odbc.ini
ODBCINST=/access-clients/odbc/odbcinst.ini
ORACLE=/access-clients/oracle/instantclient_21_10
ORACLE_HOME=/access-clients/oracle/instantclient_21_10
ORACLE_BIN=/access-clients/oracle/instantclient_21_10
COPLIB=/access-clients/teradata
TERADATA=/access-clients/teradata/client/17.20/lib64
Since we use the reserved "/data-drivers/jdbc” path as the mount path for JDBC drivers, they will be auto-discovered. No need to change the CLASSPATH in the JDBC libraries and CASLIBs. See this article.
Thanks for reading.
Find more articles from SAS Global Enablement and Learning here.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning and boost your career prospects.