Understand the SAS/ACCESS Configuration README File

2 Likes

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.

Why do I need to “attach storage to SAS Viya” in the context of SAS/ACCESS?

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.

Which SAS components need access to a database client?

How do I attach storage to those components?

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:

data-mounts-cas.sample.yaml

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.

data-mounts-deployment.sample.yaml

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.

data-mounts-job.sample.yaml

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.

data-mounts-statefulset.sample.yaml

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.

Anatomy of a SAS/ACCESS PatchTransformer

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.

Proposed SAS/ACCESS Configuration Procedure

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).

Identify the Jump Server VM (bastion host) and the NFS Server VM provisioned for SAS Viya (the NFS Server /export directory is by default mounted on the Jump Server as /viya-share).
Prepare some directories on the NFS Server through the Jump Server. For example:

/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)

Install needed database clients into their corresponding folders (/viya-share/access-clients/<database>).
Copy/paste needed JDBC drivers to /viya-share/access-clients/jdbc.
Copy/paste sample odbc.ini and odbcinst.ini from $deploy/sas-bases/examples/data-access (odbc.sample.ini and odbcinst.sample.ini) on the Client machine to /viya-share/access-clients/odbc on the Jump Server and remove “.sample” from the file names.
Configure ODBC data sources if needed in odbc.ini and odbcinst.ini.
Create $deploy/site-config/data-access on the Client machine.
Copy PatchTransformer’s YAML files $deploy/sas-bases/examples/data-access/data-mounts-*.sample.yaml to $deploy/site-config/data-access and remove “.sample” from the file names.
Edit them accordingly (you can edit only the ones needed, for example CAS, or all 4 files so that all SAS components can access database clients). I suggest the latter.

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

Copy environment variables definition file $deploy/sas-bases/examples/data-access/sas-access.properties to $deploy/site-config/data-access.
Edit it accordingly to define the needed environment variables. This will use the “mountPath” information defined in the PatchTransformers. For example:

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

Edit your base kustomization.yaml in $deploy to include the PatchTransformers (data-mounts-*.yaml) created earlier (use the transformers block).
Edit your base kustomization.yaml in $deploy to include the environment variables file (sas-access.properties) created earlier (use the configMapGenerator block)
Edit your base kustomization.yaml in $deploy to include the provided data-env.yaml file (use the transformers block)
Deploy/redeploy SAS Viya
Restart CAS if needed

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.