With the SAS Viya 2023.08 stable release and onwards, the Cloud Data Exchange (CDE) ODBC driver for Windows is available for SAS Viya users to securely access the on-premises and cloud storage data from third-party applications. Users can deploy and configure the CDE ODBC driver for Windows at a client application server and securely access the data via the CDE Remote (RDA) and Co-located (CDA) Data Agent Server. The CDE ODBC driver is a 64-bit Windows platform driver.   In this post, I discuss the deployment and configuration of the CDE ODBC driver at the client machine and access to the on-premises and cloud storage data.   The CDE ODBC driver enables users to leverage the CDE components for third-party applications. The third-party applications connect to CDE RDA using ODBC driver to access data behind the on-premises firewall.   The following pics describe the client machine having a third-party application with the CDE ODBC driver and accessing the on-premises and cloud storage data via RDA and CDA.     Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Pre-Requisite   The SAS Viya stable-2023.08 or above environment including Remote Data Agent Server. ODBC driver for CDE deployed at the client machine having third-party applications. ODBC data source configured at the client machine against the CDE RDA and CDA servers.   How to download the CDE ODBC driver for Windows?   The CDE ODBC driver for Windows can be downloaded from support.sas.com using the SAS user profile.   https://support.sas.com/downloads/browse.htm?fil=&cat=40        How to deploy CDE ODBC driver on the client machine   Start an admin DOS prompt on the Windows client machine and execute the .msi file using the following statement to deploy the CDE ODBC driver.   Code:   C:\Users\student\Downloads>msiexec /i SASCDEODBCLibrariesInstaller.msi   How to configure ODBC data source on the client machine   With the ODBC Data source configuration manager, create the system data source connection on the client machine using the data source name, server name, port, and schema information from the Remote and Co-located Data Agent server. The SAS Viya user ID and password are used in the ODBC data source configuration to log in to RDA, and CDA.   While configuring the ODBC driver and connection, the CDA and RDA must be reachable from the client machine over the network and security firewall.   The following pics and steps describe the ODBC data source connection against the Co-located Data Agent (CDA) server. At the client machine, start the ODBC Data Source manager with the administrator user.     Use the Add option to create a new system DSN connection at the client machine. With CDE ODBC deployed at the client machine, you will have an option to use it.     In the SAS Cloud Data Exchange Server DSN setup window, the Server name, Port, and Schema name are used from the Co-located Data Agent server. The SAS Viya user ID and password are used in the configuration to log in to CDA.     When the Remote Data Agent server is available over the firewall and network from the client machine, a user can also configure SAS Cloud Data Exchange Server ODBC DSN against the Remote Data (RDA) server. The following pics describe the ODBC DSN configuration against the RDA server.     Third-party application access to CDA and RDA data sources   The third-party application that supports the Win ODBC driver can use the CDE ODBC data source name to access the data in their environment. The following pics describe a client application Power-BI accessing data from the cloud storage data using CDA.     With the CDECDA ODBC data source configured at the client machine the application Power-BI has an option to choose the data source.     Very first-time the Power-BI application may ask username and password to log in to the CDA environment through the CDECDA ODBC data source.     The Power-BI application data navigator will list the available data table from the schema for the user’s report.     Based on the selected data user can prepare the desired Power BI Report.     Summary: The CDE ODBC driver enables SAS Viya users to let a third-party application also use the SAS data sets via the Cloud Data Exchange environment.   Important Links:   Remote SAS Data Agent: Deployment and Administration Guide   Post: SAS Cloud Data Exchange for the SAS Viya Platform. SAS Viya Cloud Data Exchange Deployment and Configuration (Part -1) SAS Viya Cloud Data Exchange Deployment and Configuration (Part -2) Configuring BASE SAS Data Source at Remote Data Agent (Cloud Data Exchange) Configuring Oracle Data Source at Remote Data Agent (Cloud Data Exchange)  Configuring SAS Federated Data Source at Remote Data Agent (Cloud Data Exchange) Configuring DB2 Data Source at Remote Data Agent (Cloud Data Exchange)     Find more articles from SAS Global Enablement and Learning here. ... View more

In the SAS Viya Cloud Data Exchange (CDE) environment, the Remote Data Agent supports the DB2 database access as well along with other databases. The DB2 client configuration at the On-Premises server is a vital task in the process of DB2 data source configuration. The CDE administrator is required to deploy a compatible DB2 client to the location mounted with the Data Agent container.   In this post, I discuss the configuration of the DB2 data source at the Remote Data agent.   The Remote Data Agent is a containerized software and runs with docker runtime. The CDE administrator can mount an NFS location with the DB2 client deployed while starting the Remote Data Agent container.   The following pics describe the local/NFS folder with the DB2 client mounted to the Remote Data Agent container and access to the DB2 database.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   The following steps describe the local/NFS folder with the DB2 client configuration and the Remote Data Agent mount to the same location with the DB2 data source configuration.   Deploy Compatible DB2 Client   The CDE administrator is required to deploy a compatible DB2 client to a location reachable from the remote data agent server. The DB2 client compatibility should be tested against the DB2 database server.   The DB2 client can be downloaded by using the instructions listed at the site https://www.ibm.com/docs/en/db2/11.5?topic=clients-installing-data-server-unix-linux.   The download file is a compressed JAR file. It needs to be uncompressed on Unix server as user “sas” (uid 1001:1001). Place the compressed file “v11.5.8_linuxx64_client.tar.gz” under /tmp folder to execute the following statement.   Code:   sudo su – sas cd tar -xvf /tmp/v*linuxx64_client.tar.gz ./client/db2_install -b $HOME/sqllib -f sysreq -y   Note: -f sysreq is used to not check the minimum requirements on the server   Configure and Validate DB2 Client against DB2 Database   Source the DB2 client profile on Unix server as user “sas” (uid 1001:1001).   Code:   . $HOME/sqllib/db2profile Create a DB2 database DSN profile under client configuration on the Unix server as user “sas” (uid 1001:1001).   Code:   db2cli writecfg add -database sample -host $(hostname -f) -port 50000 db2cli writecfg add -dsn sampledsn -database sample -host $(hostname -f) -port 50000 cat $HOME/sqllib/cfg/db2dsdriver.cfg Validate the access to DB2 DSN using db2cli on the Unix server as user “sas” (uid 1001:1001).   Code: db2cli validate -dsn sampledsn -connect -user db2inst1 -passwd   Log:   sas@p04206-jump-vm:~$ db2cli validate -dsn sampledsn -connect -user db2inst1 -passwd XXXXX ====================================================================== Client information for the current copy: ====================================================================== Client Package Type : IBM Data Server Client Client Version (level/bit): DB2 v11.5.8.0 (s2209201700/64-bit) Client Platform : Linux/X8664 Install/Instance Path : /home/sas/sqllib …. …. … ====================================================================== Connection attempt for data source name "sampledsn": ====================================================================== [SUCCESS] ====================================================================== The validation is completed. ====================================================================== sas@p04206-jump-vm:~$   Make the DB2 Client Accessible from the CDE RDA container   Prepare a folder structure as user “sas” at the shared location available to the RDA container to copy the customized DB2 client files.   Code:   mkdir -p /viya-share/gelenv/config/access-clients/db2client mkdir -p /viya-share/gelenv/config/access-clients/db2 ls -l /viya-share/gelenv/config/access-clients/   Copy the DB2 Client files as user “sas” from the home folder to the shared location available to the RDA container.   Code:   cp -R -L $HOME/sqllib /viya-share/gelenv/config/access-clients/db2client/sqllib chmod -R 755 /viya-share/gelenv/config/access-clients/db2client/   Update the db2profile as user “sas” with new paths at the shared location.   Code:   export DB2_NET_CLIENT_PATH=/viya-share/gelenv/config/access-clients/db2client/sqllib sed -i 's|^DB2DIR=.*|DB2DIR='$DB2_NET_CLIENT_PATH'|g' $DB2_NET_CLIENT_PATH/db2profile sed -i 's|^INSTHOME=.*|INSTHOME=/viya-share/gelenv/config/access-clients/db2|g' $DB2_NET_CLIENT_PATH/db2profile # DB2INSTANCE should already be set to the user who installed the client (sas for us) # DB2INSTANCE=   Copy the db2profile to the shared location db2 folder.   Code:   export DB2_NET_CLIENT_PATH=/viya-share/gelenv/config/access-clients/db2client/sqllib source $DB2_NET_CLIENT_PATH/db2profile export DB2_APPL_DATA_PATH=/viya-share/gelenv/config/access-clients/db2 export DB2_APPL_CFG_PATH=/viya-share/gelenv/config/access-clients/db2 $DB2_NET_CLIENT_PATH/bin/db2ccprf -f -t /viya-share/gelenv/config/access-clients/db2 chmod -R 755 /viya-share/gelenv/config/access-clients/db2   Update the sas-access.properties with the DB2 environment Variable   The sas-access.properties file is meant for SAS/ACCESS engine-related environment variables. Users can include and update the respective access engine environment variables. The following variable is related to the DB2 client location and configuration.   Code:   cd ~/CDERemoteDA_Deploy DB2CLIENTPATH=/viya-share/gelenv/config/access-clients/db2client/sqllib DB2PATH=/viya-share/gelenv/config/access-clients/db2 tee sas-access-db2.properties > /dev/null << EOF ########################## # SAS/ACCESS to DB2 ########################## CUR_INSTHOME= CUR_INSTNAME= DASWORKDIR= DB2DIR=${DB2CLIENTPATH} DB2INSTANCE=sas DB2LIB=${DB2CLIENTPATH} DB2_HOME=${DB2CLIENTPATH} DB2_NET_CLIENT_PATH=${DB2CLIENTPATH} IBM_DB_DIR=${DB2CLIENTPATH} IBM_DB_HOME=${DB2CLIENTPATH} IBM_DB_INCLUDE=${DB2CLIENTPATH} IBM_DB_LIB=${DB2CLIENTPATH} INSTHOME=${DB2PATH} INST_DIR=${DB2CLIENTPATH} PREV_DB2_PATH= DB2=${DB2CLIENTPATH}/lib64:${DB2CLIENTPATH}/lib64/gskit:${DB2CLIENTPATH}/lib32 DB2_BIN=${DB2CLIENTPATH}/bin:${DB2CLIENTPATH}/adm:${DB2CLIENTPATH}/misc EOF more sas-access-db2.properties cat sas-access-db2.properties >> sas-access.properties   Start the RDA service with the DB2 client location folder   Start the RDA service with the local shared folder (e.g. /viya-share/gelenv) mounted to the RDA container. The mounted folder contains the DB2 client and configuration files.   Code:   ./container-manager start \ --license-path SASViyaV4_9CV11D_license.jwt \ --sasdata sasdata \ --vars da-vars.env \ --data sasdata/data \ --mount /viya-share/gelenv:/viya-share/gelenv \ --access-vars sas-access.properties \ sas-data-agent-server-remote   Create a DB2 Security Domain at RDA server   Create a DB2 security domain on the RDA server to keep the SAS Viya user identity and DB2 database credentials in pairs.   Code:   sas-viya dagentsrv servers set-default --data-agent sas-data-agent-server-remote sas-viya dagentsrv security domains create --domain DB2DOM1 sas-viya dagentsrv security domains list   Log:   jumpuser@p04206-jump-vm:~/CDERemoteDA_Deploy$ sas-viya dagentsrv security domains create --domain DB2DOM1 The domain was successfully created. jumpuser@utkuma-p04206-jump-vm:~/CDERemoteDA_Deploy$ jumpuser@p04206-jump-vm:~/CDERemoteDA_Deploy$ sas-viya dagentsrv security domains list ID Type Label Description kerberos GSSCredential Kerberos credentials captured by logon and cached for delegation DefaultAuth password The default authentication domain for SAS Viya EsriAuth password EsriAuth Esri authentication credential domain sas.naturalLanguageConversations.connectors connectorDomain EsriPortalAuth password EsriPortalAuth Local Esri portal authentication credential domain EsriPortalRefreshToken token EsriPortalRefreshToken Esri portal OAuth2 refresh credential domain DB2DOM1 shadow jumpuser@p04206-jump-vm:~/CDERemoteDA_Deploy$   • Add Viya user identity to DB2 Security Domain at RDA server   Add the SAS Viya user identity with the Oracle database user ID and password to access the database objects from the SAS Viya applications. The Oracle database user password used in the following statement is a one-time user credentials setup. The Password of the database user never leaves the on-premises Remote Data Agent Server. It resides in the SAS Secrets Manager (Vault) at the Remote Data Agent Server.   Code:   ### gatedemo001@gelenable.sas.com - SAS Viya user id. ### ### db2inst1 – DB2 database user ### ### xxxxxxxxxx – DB2 database password for user db2inst1 ### sas-viya dagentsrv security credentials create --domain DB2DOM1 --identity gatedemo001@gelenable.sas.com --username db2inst1 --password ‘xxxxxxx’ sas-viya dagentsrv security credentials create --domain DB2DOM1 --identity sasadm@gelenable.sas.com --username db2inst1 --password 'xxxxxxxxxx' sas-viya dagentsrv security credentials create --domain DB2DOM1 --identity geldmui@gelenable.sas.com --username db2inst1 --password 'xxxxxxxxxx' sas-viya dagentsrv security credentials list --domain DB2DOM1   Log:   jumpuser@p04206-jump-vm:~/CDERemoteDA_Deploy$ sas-viya dagentsrv security credentials list --domain DB2DOM1 Identity Type UserId gatedemo001@gelenable.sas.com user db2inst1 geldmui@gelenable.sas.com user db2inst1 sasadm@gelenable.sas.com user db2inst1 jumpuser@p04206-jump-vm:~/CDERemoteDA_Deploy$   Create a DB2 data source at RDA   The following statement creates a DB2 data service at the Remote Data Agent using DB2 DSN name as a database name. The statement uses the DB2 domain name which has the database user-id password saved with the SAS Viya user identity.   Code:   sas-viya dagentsrv data-services create db2 --name db2srv12 --domain DB2DOM1 --database-name sampledsn sas-viya dagentsrv services list   Log:   jumpuser-p04206-jump-vm:~$ sas-viya dagentsrv data-services create db2 --name db2srv12 --domain DB2DOM1 --database-name sampledsn The data service was successfully created. jumpuser@p04206-jump-vm:~$ jumpuser@p04206-jump-vm:~$ sas-viya dagentsrv services list Name Type Domain Version Options BASE base --- 2.5 --- __SERVER__ server --- 2.5 PURGE_CACHE=30;CASE_SENSITIVITY=(OBJECT=F;COLUMN=F);CACHE=(NAME=AS;TIMEOUT=300) db2srv12 db2 DB2DOM1 2.5 CONOPTS=(DRIVER=DB2;DATABASE=sampledsn);CASE_SENSITIVITY=(OBJECT=T;COLUMN=T) jumpuser@p04206-jump-vm:~$   Test the DB2 Remote Data Agent data source   In this case, the login to the DB2 database is using the Viya identity user to fetch the database password from the DB2 security domain and SAS security vault. The Viya identity user is authenticated against the Viya platform using the sas-viya CLI.   Code:   sas-viya dagentsrv data-sources test --name db2srv12   Log:   jumpuser@04206-jump-vm:~$ sas-viya dagentsrv data-sources test --name db2srv12 Successfully connected to data source db2srv12. jumpuser@p04206-jump-vm:~$   List the DB2 database table and data from a DB2 data source table   With a successful test connection to the DB2 data source, the user can list the data table and data from the DB2 database using the sas-viya CLI.   Code:   sas-viya dagentsrv data-sources tables list --data-source db2srv12 --catalog db2srv12 --schema DB2INST1 sas-viya dagentsrv sql --sql "select * from DB2INST1.department " --dsn db2srv12   Log:   jumpuser@p04206-jump-vm:~$ sas-viya dagentsrv data-sources tables list --data-source db2srv12 --catalog db2srv12 --schema DB2INST1 Name Type Native Catalog ACT TABLE NULL ADEFUSR MATERIALIZED QUERY TABLE NULL CL_SCHED TABLE NULL DEPARTMENT TABLE NULL DEPT ALIAS NULL EMP ALIAS NULL EMP_ACT ALIAS NULL … ….. … jumpuser@p04206-jump-vm:~$ sas-viya dagentsrv sql --sql "select * from DB2INST1.department " --dsn db2srv12 ADMRDEPT DEPTNAME DEPTNO LOCATION MGRNO ======== ======== ====== ======== ===== A00 SPIFFY COMPUTER SERVICE DIV. A00 000010 A00 PLANNING B01 000020 A00 INFORMATION CENTER C01 000030 A00 DEVELOPMENT CENTER D01 D01 MANUFACTURING SYSTEMS D11 000060 D01 ADMINISTRATION SYSTEMS D21 000070 …. ….. jumpuser@p04206-jump-vm:~$   SAS Access to DB2 Data Source at Remote Data Agent (RDA)   With the DB2 data source configured at the Remote Data Agent, users can access the DB2 data tables from the SAS Compute Server using the CDE LIBNAME statement. The following code describes the access of a DB2 database table from a DB2 RDA data source.   Code:   LIBNAME cdedb2 cde dataagentname="sas-data-agent-server-remote" dsn=db2srv12 preserve_tab_names=yes; data cdedb2.FISH_SAS ; set sashelp.fish ; run; Proc SQL outobs=20; select * from cdedb2.FISH_SAS ; run;quit;   Log:   79 80 LIBNAME cdedb2 cde dataagentname="sas-data-agent-server-remote" 81 dsn=db2srv12 preserve_tab_names=yes; NOTE: Libref CDEDB2 was successfully assigned as follows: Engine: CDE Physical Name: db2srv12 82 83 80 81 data cdedb2.FISH_SAS ; 82 set sashelp.fish ; 83 run; NOTE: There were 159 observations read from the data set SASHELP.FISH. NOTE: The data set CDEDB2.FISH_SAS has 159 observations and 7 variables. NOTE: DATA statement used (Total process time): real time 1.41 seconds cpu time 0.10 seconds 84 85 80 Proc SQL outobs=20; 81 select * from cdedb2.FISH_SAS ; WARNING: Statement terminated early due to OUTOBS=20 option. 82 run;quit; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: The PROCEDURE SQL printed page 1. NOTE: PROCEDURE SQL used (Total process time): real time 0.16 seconds cpu time 0.05 seconds 83   CAS Access to DB2 Data Source at Remote Data Agent   With the DB2 data source configured at the Remote Data Agent, users can access the DB2 data tables from the CAS using the CDE data connector. The following code describes the CAS access to the DB2 table from a DB2 data source using the CDE CASLIB statement.   Code:   CAS mySession SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); caslib cdedb12 datasource=( srctype="clouddex", dataAgentName="sas-data-agent-server-remote", catalog="DB2SRV12", schema="DB2INST1", conopts="dsn=db2srv12" ) libref=cdedb12; proc casutil incaslib="cdedb12"; list files ; run;quit; /* CAS load from CDE RDA DB2 table */ proc casutil incaslib="cdedb12" outcaslib="cdedb12"; load casdata="DEPT" casout="DEPT" replace ; list tables ; run;quit; /* Save CAS table to RDA DB2 database */ proc casutil incaslib="cdedb12" outcaslib="cdedb12" ; droptable casdata="SAS_CARS" quiet ; load data=sashelp.cars casout="SAS_CARS" ; save casdata="SAS_CARS" casout="SAS_CARS" replace ; list files ; quit ; cas mysession terminate;   Log:   81 82 caslib cdedb12 datasource=( 83 srctype="clouddex", 84 dataAgentName="sas-data-agent-server-remote", 85 catalog="DB2SRV12", 86 schema="DB2INST1", 87 conopts="dsn=db2srv12" 88 ) libref=cdedb12; NOTE: Executing action 'table.addCaslib'. NOTE: 'CDEDB12' is now the active caslib. NOTE: Cloud Analytic Services added the caslib 'CDEDB12'. NOTE: Action 'table.addCaslib' used (Total process time): NOTE: real time 0.011457 seconds 80 /* CAS load from CDE RDA DB2 table */ 81 proc casutil incaslib="cdedb12" outcaslib="cdedb12"; NOTE: The UUID 'a2d24d50-926b-184c-90a0-756067585be5' is connected using session MYSESSION. 82 load casdata="DEPT" casout="DEPT" replace ; NOTE: Executing action 'table.loadTable'. NOTE: Connecting using the OAuth token for CAS user 'geldmui@gelenable.sas.com'. WARNING: Using server default session timeout of 3600 NOTE: Cloud Analytic Services made the external data from DEPT available as table DEPT in caslib cdedb12. NOTE: Action 'table.loadTable' used (Total process time): NOTE: real time 0.902593 seconds NOTE: SASHELP.CARS was successfully added to the "CDEDB12" caslib as "SAS_CARS". 84 save casdata="SAS_CARS" casout="SAS_CARS" replace ; NOTE: Executing action 'table.save'. NOTE: Performing serial SaveTable action using SAS Data Connector to Cloud Data Exchange. NOTE: Connecting using the OAuth token for CAS user 'geldmui@gelenable.sas.com'. WARNING: Using server default session timeout of 3600 NOTE: Cloud Analytic Services saved the file SAS_CARS in caslib CDEDB12. NOTE: Action 'table.save' used (Total process time): NOTE: real time 3.448311 seconds   Important Links:   Cloud Data Exchange for the SAS Viya Platform   Cloud Data Exchange for the SAS Viya Platform SAS Viya Cloud Data Exchange Deployment and Configuration (Part -1) SAS Viya Cloud Data Exchange Deployment and Configuration (Part -2) Configuring BASE SAS Data Source at Remote Data Agent (Cloud Data Exchange) Configuring Oracle Data Source at Remote Data Agent (Cloud Data Exchange) Configuring SAS Federated Data Source at Remote Data Agent (Cloud Data Exchange)   Find more articles from SAS Global Enablement and Learning here. ... View more

In the 1st part of this series, I talked about having an Azure Storage Account (ADLS2) with NFS 3.0 protocol, Hierarchical namespace, and Azure Blob Storage CSI driver enabled on the AKS cluster, you can mount the Blob Storage to AKS Pods. You can create a Persistent Volume (PV) and Persistence Volume Claim (PVC) against Azure Blob Storage using the azureblob-nfs-premium storage class and mount it to the SAS Compute Pod and CAS Pods to access the sas7bdat data files.   In the second part of the series, I talk about the configuration for Azure Storage Account and AKS to NFS mount the Compute and CAS pod to Azure Blob storage.     Pre-requisite   An Azure Storage Account with Hierarchical namespace enabled. Azure Storage Account with NFS 3.0 Protocol enabled. AKS cluster with Azure Blob Storage CSI driver enabled. AKS cluster with NFS 3.0 or above Protocol. Storage Account network access to AKS VNet and Subnet. Azure CLI Ver 2.48 and onwards.   The following picture describes the SAS Compute and CAS Pod mount to Azure Blob Storage.    Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.     Storage Account configuration   In this process, you can use either a Standard or Premium ADLS2 Storage Account. The Premium Storage account has better performance. When creating an Azure Storage Account, include the Hierarchical namespace and NFS 3.0 Protocol. Also, making it part of the AKS VNet and Subnet network, means it allows the network traffic from the AKS cluster. The ACL of the Blob Storage folder with read/write permission for the required user group and users.   The Azure Storage Account (ADLS2) with Hierarchical Name Space, NFS 3. 0 protocol, and selected network (AKS VNet and Subnet) access.     The Azure Storage Account (ADLS2) network access to AKS Vnet and subnet Network.     The ACL for specific Blob folders with read and write permission. Provision the required read/write permission for user groups and users to the Blob storage folder.       AKS Cluster configuration   The AKS cluster must support NFS 3.0 and above protocol with Azure Blob CSI drive installed.   If required, you can enable the Azure Blob CSI driver and storage class using the following statement. To execute the following update statement, you must have Azure CLI Ver 2.48 and above.   Code: az account set --subscription "PSGEL286 SAS Viya 4: Data Management on Azure Cloud" RG_NAME=mydata_rg AKSCLUSTER=p03039-aks echo $RG_NAME echo $AKSCLUSTER az aks update -g $RG_NAME -n $AKSCLUSTER --enable-blob-driver --load-balancer-managed-outbound-ip-count 2   Verify the AKS cluster for Azure Blob CSI driver and Storage class. Notice the azureblob-nfs-premium and azureblob-fuse-premium storage class in the list.   Code: kubectl get sc   Log: [cloud-user@pdcesx11142 scripts]$ kubectl get sc NAME PROVISIONER RECLAIMPOLICY VOLUMEBINDINGMODE ALLOWVOLUMEEXPANSION A azureblob-fuse-premium blob.csi.azure.com Delete Immediate true 1 azureblob-nfs-premium blob.csi.azure.com Delete Immediate true 1 azurefile file.csi.azure.com Delete Immediate true 1 … ……. ………….. [cloud-user@pdcesx11142 scripts]$     Create PV and PVC against Azure Blob Storage   Having azureblob-nfs-premium driver and storage class at the AKS cluster, you can create a Persistence Volume (PV) and Persistence Volume Claim (PVC) against the Azure Blob Storage using the azureblob-nfs-premium storage class.   If required, you can create a customized storage class as well using blob.csi.azure.com driver provider.   The following statement can be used to create a .yaml file for Blob PV for NFS mount. When creating multiple PVs make sure to have a unique attribute value for volumeHandle: unique-volumeid101 .   Code: export NS=<mynamespace> export STRG_RG=<mystrgrg> export STRG_ACC=<mystrgacct> export STRG_FILE=<myfsdata> cat > ~/project/deploy/${NS}/site-config/data-access/pv-blob-nfs.yaml <<-EOF apiVersion: v1 kind: PersistentVolume metadata: annotations: pv.kubernetes.io/provisioned-by: blob.csi.azure.com name: pv-azblob spec: capacity: storage: 5G accessModes: - ReadWriteMany persistentVolumeReclaimPolicy: Retain # If set as "Delete" container would be removed after pvc deletion storageClassName: azureblob-nfs-premium csi: driver: blob.csi.azure.com readOnly: false # make sure volumeid is unique for every identical storage blob container in the cluster # character `#` is reserved for internal use and cannot be used in volumehandle volumeHandle: unique-volumeid101 volumeAttributes: resourceGroup: ${STRG_RG} storageAccount: ${STRG_ACC} containerName: ${STRG_FILE} protocol: nfs EOF   The following statement can be used to create a .yaml file for Blob PVC for NFS mount.   Code: export NS=<mynamespace> cat > ~/project/deploy/${NS}/site-config/data-access/pvc-blob-nfs.yaml <<-EOF kind: PersistentVolumeClaim apiVersion: v1 metadata: name: pvc-azblob spec: accessModes: - ReadWriteMany resources: requests: storage: 3Gi volumeName: pv-azblob storageClassName: azureblob-nfs-premium EOF   The following statement can be used to create a Blob PV and PVC for NFS mount. Apply the .yaml file to the AKS cluster under specified namespace to get the Blob PV and PVC.   Code: export NS=<mynamespace> kubectl config set-context --current --namespace=${NS} kubectl apply -f ~/project/deploy/${NS}/site-config/data-access/pv-blob-nfs.yaml kubectl apply -f ~/project/deploy/${NS}/site-config/data-access/pvc-blob-nfs.yaml kubectl -n ${NS} get pv pv-azblob kubectl -n ${NS} get pvc pvc-azblob   Log: [cloud-user@pdcesx03039 data-access]$ kubectl -n ${NS} get pv pv-azblob NAME CAPACITY ACCESS MODES RECLAIM POLICY STATUS CLAIM STORAGECLASS REASON AGE pv-azblob 5G RWX Retain Bound gelenv/pvc-azblob azureblob-nfs-premium 3h28m [cloud-user@pdcesx03039 data-access]$ [cloud-user@pdcesx03039 data-access]$ kubectl -n ${NS} get pvc pvc-azblob NAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS AGE pvc-azblob Bound pv-azblob 5G RWX azureblob-nfs-premium 3h28m [cloud-user@pdcesx03039 data-access]$     Mount the Azure Blob PVC to SAS Compute and CAS Pods   With Azure Blob PV and PVC at the AKS cluster, you can mount it to Compute and CAS Pods to store and access sas7bdat files.   The following statement can be used to create a .yaml file for Compute Pod to mount Blob PVC.   Code: export NS=<mynamespace> cat > ~/project/deploy/${NS}/site-config/data-access/blobdata-mounts-job.yaml <<-EOF # General example for adding mounts to CAS workers # PatchTransformer apiVersion: builtin kind: PatchTransformer metadata: name: blobdata-mounts-job patch: |- ## Azure Blob container - kubernetes will mount these for you - op: add path: /template/spec/containers/0/volumeMounts/- value: name: blob01 mountPath: "/mnt/blob01" - op: add path: /template/spec/volumes/- value: name: blob01 persistentVolumeClaim: claimName: pvc-azblob target: kind: PodTemplate annotationSelector: sas.com/sas-access-config=true labelSelector: "sas.com/template-intent=sas-launcher" EOF   The following statement can be used to create a .yaml file for CAS Pod to mount Blob PVC.   Code: export NS=<mynamespace> cat > ~/project/deploy/${NS}/site-config/data-access/blobdata-mounts-cas.yaml <<-EOF # General example for adding mounts to CAS workers # PatchTransformer apiVersion: builtin kind: PatchTransformer metadata: name: blobdata-mounts-cas patch: |- ## Azure File Share - kubernetes will mount these for you - op: add path: /spec/controllerTemplate/spec/containers/0/volumeMounts/- value: name: blob01 mountPath: "/mnt/blob01" - op: add path: /spec/controllerTemplate/spec/volumes/- value: name: blob01 persistentVolumeClaim: claimName: pvc-azblob target: kind: CASDeployment annotationSelector: sas.com/sas-access-config=true EOF   The following statement can be used to create a .yaml file for CAS Pod to allow the access to mounted path.   Code: export NS=<mynamespace> cat > ~/project/deploy/${NS}/site-config/data-access/cas-add-allowlist-paths.yaml << EOF apiVersion: builtin kind: PatchTransformer metadata: name: cas-add-allowlist-paths patch: |- - op: add path: /spec/appendCASAllowlistPaths/- value: /mnt/blob01 target: group: viya.sas.com kind: CASDeployment name: .* version: v1alpha1 EOF   Update the kustomization.yaml to include the additional .yaml files under the “transformers” section.   Code: transformers: ………. …….. - site-config/data-access/blobdata-mounts-cas.yaml ## To mount Azure BLOB PVC to CAS - site-config/data-access/blobdata-mounts-job.yaml ## To mount Azure BLOB PVC to Compute Server - site-config/data-access/cas-add-allowlist-paths.yaml ## To Allow Path in CAS …… …..   Build and apply the manifestation to the AKS cluster and recycle the CAS and SAS Compute pod.   Code: export NS=<mynamespace> cd ~/project/deploy/${NS}/ kustomize build -o site.yaml kubectl -n ${NS} apply -f site.yaml kubectl -n ${NS} delete pods -l casoperator.sas.com/server=default kubectl -n ${NS} delete pod --selector='app=sas-compute' kubectl -n ${NS} delete pod --selector='app=sas-launcher'   When CAS Pods are running, validate the Blob Storage is mounted to CAS Pods.   Code: ##validate the disk is correctly mounted kubectl exec sas-cas-server-default-controller -c sas-cas-server -- touch /mnt/blob01/sample_data/test2.txt kubectl exec sas-cas-server-default-controller -- ls -l /mnt/blob01/sample_data   Log: [cloud-user@pdcesx03039 gelenv]$ kubectl exec sas-cas-server-default-controller -c sas-cas-server -- touch /mnt/blob01/data/test2.txt [cloud-user@pdcesx03039 gelenv]$ [cloud-user@pdcesx03039 gelenv]$ kubectl exec sas-cas-server-default-controller -- ls /mnt/blob01/sample_data Defaulted container "sas-cas-server" out of: sas-cas-server, sas-backup-agent, sas-consul-agent, sas-certframe (init), sas-config-init (init) test2.txt [cloud-user@pdcesx03039 gelenv]$   If the SAS Compute Server is in a lockdown state, update the Compute service:autoexec_code configuration instance from the SAS Environment Manager application to include the required path.       SAS Compute Server Access to Azure Blob Storage PVC   With Azure Blob PVC mounted to the SAS Compute Server, it can read and write sas7bdat files to Azure Blob Storage (ADLS2). The Azure Blob Storage is available to the SAS Compute Server using NFS 3 protocol and Azure Blob CSI driver. The following statement can be used to save and read a sas7bdat file to Azure Blob Storage using a PATH-based LIBNAME statement.   Code: /* # LIBNAME Statement for Azure Blob Share Location */ libname azshrlib "/mnt/blob01/sample_data" ; data azshrlib.fish_sas ; set sashelp.fish ; run; Proc SQL outobs=20; select * from azshrlib.fish_sas ; run;quit;   Log: 80 /* # LIBNAME Statement for Azure Blob Share Location */ 81 82 libname azshrlib "/mnt/blob01/sample_data" ; NOTE: Libref AZSHRLIB was successfully assigned as follows: Engine: V9 Physical Name: /mnt/blob01/sample_data 83 84 data azshrlib.fish_sas ; 85 set sashelp.fish ; 86 run; NOTE: There were 159 observations read from the data set SASHELP.FISH. NOTE: The data set AZSHRLIB.FISH_SAS has 159 observations and 7 variables. NOTE: DATA statement used (Total process time): real time 0.46 seconds cpu time 0.00 seconds 87 88 Proc SQL outobs=20; 89 select * from azshrlib.fish_sas ; WARNING: Statement terminated early due to OUTOBS=20 option. 90 run;quit; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: The PROCEDURE SQL printed page 11. NOTE: PROCEDURE SQL used (Total process time): real time 0.06 seconds cpu time 0.04 seconds 91     CAS load/Save from Azure Blob Storage PVC   With Azure Blob PVC mounted to the CAS, it can load/save data from sas7bdat files located at Azure Blob Storage (ADLS2). The Azure Blob Storage is available to CAS using NFS 3 protocol and Azure Blob CSI driver. CAS can access the sas7bdat data files saved by the SAS Compute Server and vice versa.   The following statement can be used to load/save a sas7bdat file to Azure Blob Storage using a PATH-based CASLIB statement.   Code: CAS mySession SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); CASLIB azlib DATASOURCE=(SRCTYPE="PATH") path="/mnt/blob01/sample_data" ; /* Save a CAS table into a sas7bdat file. */ proc casutil incaslib="azlib" outcaslib="azlib"; load data=sashelp.cars casout="cars" replace; save casdata="cars" casout="cars.sas7bdat" replace; list files; quit; /* Load CAS from sas7bdat data files*/ proc casutil incaslib="azlib" outcaslib="azlib"; load casdata="cars.sas7bdat" casout="cars_7bdat" replace; load casdata="fish_sas.sas7bdat" casout="fish_7bdat" replace; list tables; quit; CAS mySession TERMINATE;   Log: ……. ….. 82 CASLIB azlib DATASOURCE=(SRCTYPE="PATH") path="/mnt/blob01/sample_data" ; NOTE: Executing action 'table.addCaslib'. NOTE: 'AZLIB' is now the active caslib. NOTE: Cloud Analytic Services added the caslib 'AZLIB'. NOTE: Action 'table.addCaslib' used (Total process time): NOTE: real time 0.039090 seconds NOTE: cpu time 0.017151 seconds (43.88%) NOTE: total nodes 3 (12 cores) NOTE: total memory 94.03G NOTE: memory 1.41M (0.00%) NOTE: Action to ADD caslib AZLIB completed for session MYSESSION. 83 84 /* Save a CAS table into a sas7bdat file. */ 85 proc casutil incaslib="azlib" outcaslib="azlib"; NOTE: The UUID 'dde2b86d-583b-e842-8736-8fb4390b584a' is connected using session MYSESSION. …. …. 88 save casdata="cars" casout="cars.sas7bdat" replace; NOTE: Executing action 'table.save'. NOTE: Cloud Analytic Services saved the file cars.sas7bdat in caslib AZLIB. NOTE: Action 'table.save' used (Total process time): NOTE: real time 0.216190 seconds NOTE: cpu time 0.041189 seconds (19.05%) NOTE: total nodes 3 (12 cores) NOTE: total memory 94.03G NOTE: memory 4.52M (0.00%) NOTE: The Cloud Analytic Services server processed the request in 0.21619 seconds. …. ……. 92 /* Load CAS from sas7bdat table */ 93 proc casutil incaslib="azlib" outcaslib="azlib"; NOTE: The UUID 'dde2b86d-583b-e842-8736-8fb4390b584a' is connected using session MYSESSION. 95 load casdata="cars.sas7bdat" casout="cars_7bdat" replace; NOTE: Executing action 'table.loadTable'. NOTE: Cloud Analytic Services made the file cars.sas7bdat available as table CARS_7BDAT in caslib azlib. NOTE: Action 'table.loadTable' used (Total process time): NOTE: real time 0.071273 seconds NOTE: cpu time 0.039901 seconds (55.98%) NOTE: total nodes 3 (12 cores) NOTE: total memory 94.03G NOTE: memory 7.08M (0.01%) NOTE: bytes moved 68.08K NOTE: The Cloud Analytic Services server processed the request in 0.071273 seconds. 100 load casdata="fish_sas.sas7bdat" casout="fish_7bdat" replace; NOTE: Executing action 'table.loadTable'. NOTE: Cloud Analytic Services made the file fish_sas.sas7bdat available as table FISH_7BDAT in caslib azlib. NOTE: Action 'table.loadTable' used (Total process time): NOTE: real time 0.052144 seconds NOTE: cpu time 0.042362 seconds (81.24%) NOTE: total nodes 3 (12 cores) NOTE: total memory 94.03G NOTE: memory 7.06M (0.01%) NOTE: bytes moved 11.14K NOTE: The Cloud Analytic Services server processed the request in 0.052144 seconds. ……. …………… …………….     Azure Blob Storage with sas7bdat files       Performance   To be practical, the performance of mounted blob storage is not going to be like a local disk or local NFS drive. You must be prepared for slow performance. The performance of the data file access from Azure Blob Storage to SAS Compute Server and CAS environment depends on various components, including the capacity of the VM Server used in the AKS Node-pool. The usage of Standard Vs Premium Storage Account. The network interface and I/O throughput of VM machines. The high-end Azure VM in the AKS Node-pool with the Premium Storage Account would perform better.   The following test result is from a Standard and Premium Storage Account with the “Standard_E4s_v3” type VM machine for CAS and Standard_D8s_v3 type VM machine for the SAS Compute Server.   SAS Compute Server time to save sas7bdat data to ADLS2 Blob Storage     SAS Compute Server time to read sas7bdat data (Proc Freq) from ADLS2 Blob Storage     CAS time to load sas7bdat data file from ADLS2 Blob Storage       Conclusion   Considering the features and limitations of Azure Storage Account Blob Storage and VM in AKS Node-pool, you can mount the Blob storage to SAS Compute Server and CAS pods to access the sas7bdat files. This could be a useful option to migrate the existing sas7bdat files from the on-premises environment to cloud-based object storage (Blob). It enables SAS users to read and write sas7bdat files to Blob storage from the SAS Compute Server and CAS. The sas7bdat data files created by either SAS Compute Server or CAS can be read by both and vice versa.   Important Link: Using Azure Blob Storage for sas7bdat files with SAS Viya – Part 1         Find more articles from SAS Global Enablement and Learning here. ... View more

Azure Data Lake (ADLS) Blob Storage is a cloud-based distributed file storage service. Often the question is asked, can we use the Azure Blob Storage to store sas7bdat files and access them from the SAS Viya applications? When a customer migrates from a traditional SAS 9.4 to the SAS Viya environment, they like to bring existing sas7bdat data files to cloud storage and access them from the SAS Compute Server and the CAS environment.   In short, the answer to the above question is Yes!   An Azure Storage Account (ADLS2) with Hierarchical-namespace is a POSIX-compliant. The Azure Blob storage supports the Network File System (NFS) 3.0 protocol. This support provides Linux file system compatibility at object storage and enables Linux clients to mount a container in blob storage.   The Azure Blob Storage Container Storage Interface (CSI) driver is a CSI specification-compliant driver used by AKS to manage the lifecycle of Azure Blob Storage. The CSI is a standard for exposing block and file storage systems to containerized applications on Kubernetes.   With NFS 3.0 protocol and Hierarchical namespace enabled on an Azure Storage Account (ADLS2) and Azure Blob Storage CSI driver enabled on the AKS cluster, you can create a Persistent Volume (PV) and Persistence Volume Claim (PVC) against Azure Blob Storage using azureblob-nfs-premium storage class. The Azure CSI Blob Driver has two storage classes (azureblob-nfs-premium and azureblob-fuse-premium) for blob storage. The PVC can be mounted to the SAS Compute Pod and CAS Pods using either of the storage classes. This enables SAS Viya users to use Azure Blob Storage to store sas7bdat files and access them from the SAS Compute Server and CAS environment.   Access to Azure Blob storage from SAS Compute Server and CAS is a vital step for customers migrating from SAS 9.4 to Viya and looking to store the sas7bdat file at ADLS blob storage. The sas7bdat data files saved by the SAS Compute Server can be accessed by CAS and vice versa.   The data access path from ADLS2 Blob storage to SAS Compute Server and CAS.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   The AKS Cluster is enabled with the CSI Blob driver and Storage class.     The Azure Storage Account (ADLS2) is enabled with Hierarchical Name Space, NFS 3. 0 protocol, and selected network access.     The Azure Blob Storage (ADLS2) with sas7bdat files.     The SAS Compute Server reads and writes sas7bdat files to Azure Blob Storage (ADLS2).     The CAS load/save data from sas7bdat files located at Azure Blob Storage (ADLS2). The sas7bdat files saved by the SAS Compute Server load to CAS.     Stay tuned in for configuration details for Azure Storage Account and AKS to mount the Azure Blob Storage to SAS Viya Compute and CAS Pods to access sas7bdat files.   Important Links: Use Azure Blob storage Container Storage Interface (CSI) driver Create and use a volume with Azure Blob storage in Azure Kubernetes Service (AKS)     Find more articles from SAS Global Enablement and Learning here. ... View more

A SAS Federated Data Source is a common place for end users to access data from multiple data sources. A Cloud Data Exchange (CDE) administrator can create a Federated Data Source in the SAS Viya CDE environment from existing data sources like Oracle, DB2, and BASE SAS tables. The Federated Data Source can have a table schema defined from combinations of multiple data source tables using a few columns from each source data table. The data can also be masked in a Federated Data Source table schema.   In this post, I discuss the configuration of the Federated Data Source at the Remote Data agent.   The Federated Data Source schema is defined using existing data sources. The client configuration must be deployed and configured at the Remote Data Agent Server to support the various data sources like Oracle, DB2, and MS-SQL Server before using it in the Federated Data Source.   Pre-requisite   • There are at least two or more data sources (e.g. BASE, Oracle) defined at the Remote Data Agent Server.   The following pics describe the Federated Data Source from multiple data sources at the Remote Data Agent Server.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Steps to create a Federated Data Source   The following steps describe the creation of a Federated Data Source at the Remote Data Agent using various existing data sources.   List the predefined data sources   A CDE Administrator should know the list of existing data sources to create a new Federated Data Source.   Code:   sas-viya dagentsrv servers set-default --data-agent sas-data-agent-server-remote sas-viya dagentsrv data-sources list   Log:   jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv servers set-default --data-agent sas-data-agent-server-remote The default data agent server was successfully set. jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv data-sources list Name ID Type ADMIN ADMIN DSN BASE BASE DSN BASE BASE__DATA_SERVICE__ Service orasrv1 orasrv1 DSN orasrv1 orasrv1__DATA_SERVICE__ Service jumpuser@p02190-jump-vm:/viya-share/gelenv/data$   Create a Federated Data Source.   The following statement creates a Federated Data Source using Oracle and BASE data service at the Remote Data Agent Server.   Code:   sas-viya dagentsrv dsns create federated --name DEMOFED --using "BASE,orasrv1"   Log:   umpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv dsns create federated --name DEMOFED --using "BASE,orasrv1" The data source name was successfully created.   List and test the Federated Data Source.   Code:   sas-viya dagentsrv data-sources list sas-viya dagentsrv data-sources test --name DEMOFED   Log:   jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv data-sources list Name ID Type ADMIN ADMIN DSN BASE BASE DSN BASE BASE__DATA_SERVICE__ Service DEMOFED DEMOFED DSN orasrv1 orasrv1 DSN orasrv1 orasrv1__DATA_SERVICE__ Service jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv data-sources test --name DEMOFED Successfully connected to data source DEMOFED. jumpuser@p02190-jump-vm:/viya-share/gelenv/data$   List tables from Federated Data Source.   You can access the tables from both the underlying data sources (Oracle and BASE). You can list the table from Oracle and BASE data sources by specifying the corresponding catalog and schema in the CLI statement.   Code:   as-viya dagentsrv data-sources tables list --data-source DEMOFED --catalog ORASRV1 --schema GELDM sas-viya dagentsrv data-sources tables list --data-source DEMOFED --catalog BASECATR1 --schema SCHEMAR1   Log:   jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv data-sources tables list --data-source DEMOFED --catalog ORASRV1 --schema GELDM Name Type Native Catalog CARSVIEW1 VIEW NULL fish_sas TABLE NULL SAS_CAR TABLE NULL sas_cars TABLE NULL TEST_TABLE TABLE NULL TEST2 TABLE NULL TESTVIEW1 VIEW NULL jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv data-sources tables list --data-source DEMOFED --catalog BASECATR1 --schema SCHEMAR1 Name Type Native Catalog FISH_SAS TABLE BASECATR1 SAS_CARS TABLE BASECATR1 TEST TABLE BASECATR1 TEST_TABLE TABLE BASECATR1 jumpuser@p02190-jump-vm:/viya-share/gelenv/data$   List a few rows from the Federated Data Source tables.   You can access the data from Oracle and BASE data sources by specifying the corresponding catalog and schema in the CLI statement.   Code:   sas-viya dagentsrv sql --sql "select * from ORASRV1.GELDM.TEST_TABLE where id < 10 " --dsn DEMOFED sas-viya dagentsrv sql --sql "select * from BASECATR1.SCHEMAR1.SAS_CARS where Make='Acura' " --dsn DEMOFED   Log:   jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv sql --sql "select * from ORASRV1.GELDM.TEST_TABLE where id < 10 " --dsn DEMOFED DATE_VALUE ID TEXT_VALUE ========== == ========== 1 EanOFzMlzSHKhhPTolDW 2 jBctxUPyQscSbnNiRsNU 3 UVwYWRHVwoELUyXIeEPF 4 RtvXPzHgJtXrLHXtBSRE 5 bMbpjSRxWGfODrkwgDaA 6 ozMjNApZjIrSpHdOpNVO 7 YwPcHobUBKFEPLabfJBa 8 pvnsRhySuOywHQHAKiBA 9 nYDsRnmPPCLJzQNwaZiq jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv sql --sql "select * from BASECATR1.SCHEMAR1.SAS_CARS where Make='Acura' " --dsn DEMOFED Cylinders DriveTrain EngineSize Horsepower Invoice Length MPG_City MPG_Highway MSRP Make Model Origin Type Weight Wheelbase ========= ========== ========== ========== ======= ====== ======== =========== ==== ==== ===== ====== ==== ====== ========= 6 All 3.5 265 33337 189 17 23 36945 Acura MDX Asia SUV 4451 106 4 Front 2.4 200 24647 183 22 29 26990 Acura TSX 4dr Asia Sedan 3230 105 6 Front 3.5 225 39014 197 18 24 43755 Acura 3.5 RL 4dr Asia Sedan 3880 115 6 Rear 3.2 290 79978 174 17 24 89765 Acura NSX coupe 2dr manual S Asia Sports 3153 100 4 Front 2 200 21761 172 24 31 23820 Acura RSX Type S 2dr Asia Sedan 2778 101 6 Front 3.2 270 30299 186 20 28 33195 Acura TL 4dr Asia Sedan 3575 108 6 Front 3.5 225 41100 197 18 24 46100 Acura 3.5 RL w/Navigation 4dr Asia Sedan 3893 115 jumpuser@p02190-jump-vm:/viya-share/gelenv/data$   Create a new object schema (data masked and un-masked view) in Federated Data Source   Create a View (Schema) in Federated Data Source.   You can create a view in Federated Data Source based on two different data source tables. The data reside at the original place (Database / BASE table), only schema is created at Federated Data Source.   Code:   sas-viya dagentsrv views create --dsn DEMOFED --name baseorav2 --sql "select a.ID, a.DATE_VALUE, b.TEXT_VALUE from BASECATR1.SCHEMAR1.TEST_TABLE_ORA_IMPORTED a , ORASRV1.GELDM.TEST_TABLE b where a.ID = b.ID and a.ID < 10 "   Log:   jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv views create --dsn DEMOFED --name baseorav2 --sql "select a.ID, a.DATE_VALUE, b.TEXT_VALUE from BASECATR1.SCHEMAR1.TEST_TABLE_ORA_IMPORTED a , ORASRV1.GELDM.TEST_TABLE b where a.ID = b.ID and a.ID < 10 " View successfully created. jumpuser@p02190-jump-vm:/viya-share/gelenv/data$   Access the data from View (Schema) in Federated Data Source.   Code:   sas-viya dagentsrv sql --sql "select * from BASEORAV2 " --dsn DEMOFED   Log:   jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv sql --sql "select * from baseorav2 " --dsn DEMOFED DATE_VALUE ID TEXT_VALUE ========== == ========== 1 EanOFzMlzSHKhhPTolDW 2 jBctxUPyQscSbnNiRsNU 3 UVwYWRHVwoELUyXIeEPF 4 RtvXPzHgJtXrLHXtBSRE 5 bMbpjSRxWGfODrkwgDaA 6 ozMjNApZjIrSpHdOpNVO 7 YwPcHobUBKFEPLabfJBa 8 pvnsRhySuOywHQHAKiBA 9 nYDsRnmPPCLJzQNwaZiq jumpuser@p02190-jump-vm:/viya-share/gelenv/data$   Create a View (Schema) with data masked at Federated Data Source.   You can create a view in Federated Data Source with data columns masked. The original source data columns are not masked but access to data via Federated view is masked. This feature enables the CDE administrator to mask some data from specific user groups.   Code:   sas-viya dagentsrv views create --dsn DEMOFED --name carsviewmask --sql "select MAKE, MODEL,TYPE, syscat.dm.mask('ENCRYPT',PUT("INVOICE",8.), 'alg', 'AES') AS INVOICE_MASK from ORASRV1.GELDM.SAS_CARS "   Log:   jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv views create --dsn DEMOFED --name carsviewmask --sql "select MAKE, MODEL,TYPE, syscat.dm.mask('ENCRYPT',PUT("INVOICE",8.), 'alg', 'AES') AS INVOICE_MASK from ORASRV1.GELDM.SAS_CARS " View successfully created. jumpuser@p02190-jump-vm:/viya-share/gelenv/data$   Access the data from View with masked data at Federated Data Source.   You can create a view in Federated Data Source with data columns masked. The original source data columns are not masked but access to data via Federated view is masked. This feature enables the CDE administrator to mask some data from specific user groups.   Code:   sas-viya dagentsrv sql --sql "select * from CARSVIEWMASK where Make='Acura' " --dsn DEMOFED   Log:   jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv sql --sql "select * from CARSVIEWMASK where Make='Acura' " --dsn DEMOFED INVOICE_MASK Make Model Type ============ ==== ===== ==== CAA68FFFB0069143BA851C3B381DCB0C720E Acura MDX SUV A9BFE48C4ECE7328D6D9F13BF3376E59574A Acura TSX 4dr Sedan AED8143F92CA41424701E3EC79EBC859EDD2 Acura 3.5 RL 4dr Sedan 45F12849A89770A4128638BF9814549E5C92 Acura NSX coupe 2dr manual S Sports CC094DBC9DB884557BD329B535104C67593B Acura RSX Type S 2dr Sedan 64226CB01D9DFF2842A0215E6E67F60D4856 Acura TL 4dr Sedan DE3AB29C43489B60976D4FADA095A58B817A Acura 3.5 RL w/Navigation 4dr Sedan jumpuser@p02190-jump-vm:/viya-share/gelenv/data$   SAS Compute Server Access to Federated Data Source at Remote Data Agent   With the Federated Data Source configured and schema defined at the Remote Data Agent, users can access Federated data tables from the SAS Compute Server using the CDE LIBNAME statement. The following code describes the access of a Federated table.   Code:   IBNAME cdefed1 cde dataagentname="sas-data-agent-server-remote" dsn=DEMOFED preserve_tab_names=yes; Proc SQL outobs=5; select * from cdefed1.carsviewmask; run;quit;   Log:   80 81 LIBNAME cdefed1 cde dataagentname="sas-data-agent-server-remote" 82 dsn=DEMOFED preserve_tab_names=yes; NOTE: Libref CDEFED1 was successfully assigned as follows: Engine: CDE Physical Name: DEMOFED 83 91 92 Proc SQL outobs=5; 93 select * from cdefed1.carsviewmask; WARNING: Statement terminated early due to OUTOBS=5 option. 94 run;quit; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: The PROCEDURE SQL printed page 12. NOTE: PROCEDURE SQL used (Total process time): real time 1.00 seconds cpu time 0.08 seconds   CAS Access to Federated Data Source at Remote Data Agent   With the Federated Data Source configured and schema defined at the Remote Data Agent, users can access Federated data tables from the CAS using the CDE data connector. The following code describes the CAS access to the Federated table using the CDE CASLIB statement.   Code:   CAS mySession SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); caslib cdefed datasource=( srctype="clouddex", dataAgentName="sas-data-agent-server-remote", conopts="dsn=DEMOFED" ) libref=cdefed; /* CAS load from CDE RDA Federated table */ proc casutil incaslib="cdefed" outcaslib="cdefed"; load casdata="baseorav2" casout="baseorav2" replace ; list tables ; run; quit; cas mysession terminate;   Log:   3 84 caslib cdefed datasource=( 85 srctype="clouddex", 86 dataAgentName="sas-data-agent-server-remote", 87 conopts="dsn=DEMOFED" 88 ) libref=cdefed; NOTE: Executing action 'table.addCaslib'. NOTE: 'CDEFED' is now the active caslib. NOTE: Cloud Analytic Services added the caslib 'CDEFED'. 81 /* CAS load from CDE RDA Federated table */ 82 proc casutil incaslib="cdefed" outcaslib="cdefed"; NOTE: The UUID '60e9bfc7-ac8d-ff4e-b122-33ed678dee4d' is connected using session MYSESSION. 83 load casdata="baseorav2" casout="baseorav2" replace ; NOTE: Executing action 'table.loadTable'. NOTE: Connecting using the OAuth token for CAS user 'geldmui@gelenable.sas.com'. WARNING: Using server default session timeout of 3600 NOTE: Cloud Analytic Services made the external data from baseorav2 available as table BASEORAV2 in caslib cdefed. NOTE: Action 'table.loadTable' used (Total process time):   Important Links:   Cloud Data Exchange for the SAS Viya Platform   Communities posts: SAS Cloud Data Exchange for the SAS Viya Platform SAS Viya Cloud Data Exchange Deployment and Configuration (Part -1) SAS Viya Cloud Data Exchange Deployment and Configuration (Part -2) Configuring BASE SAS Data Source at Remote Data Agent (Cloud Data Exchange) Configuring Oracle Data Source at Remote Data Agent (Cloud Data Exchange)     Find more articles from SAS Global Enablement and Learning here. ... View more

With the SAS Viya Cloud Data Exchange (CDE) environment, one widely used data source is the Oracle data source. The Remote Data Agent supports the Oracle database access with a compatible Oracle client. The CDE administrator is required to deploy a compatible Oracle client to the location mounted with the Data Agent container. As there are two types of data agents, an administrator can configure the Oracle data source at both data agents.   In this post, I discuss the configuration of the Oracle data source at the Remote Data agent.   The Remote Data Agent is a containerized software and runs with docker runtime. The CDE administrator can mount an NFS location with the Oracle client deployed while starting the Remote Data Agent container.   The following pics describe the local/NFS folder with the Oracle client mounted to the Remote Data Agent container and access to the Oracle database.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   The following steps describe the local/NSF folder with the Oracle client mount to the Remote Data Agent and the configuration of the Oracle data source.   Deploy Compatible Oracle Client   CDE Administrator is required to deploy a compatible Oracle client to a location reachable from the remote data agent server. The Oracle client compatibility should be tested against the Oracle database server (Oracle SID).   The Oracle client can be downloaded and installed by using the following statement. The folder containing the Oracle client should have read and write permission for user “sas” (uid 1001:1001).   Code:   mkdir -p /viya-share/gelenv/config/access-clients/oracle cd /viya-share/gelenv/config/access-clients/oracle wget https://download.oracle.com/otn_software/linux/instantclient/instantclient-basic-linuxx64.zip wget https://download.oracle.com/otn_software/linux/instantclient/instantclient-sqlplus-linuxx64.zip wget https://download.oracle.com/otn_software/linux/instantclient/instantclient-tools-linuxx64.zip unzip instantclient-basic-linuxx64.zip unzip instantclient-sqlplus-linuxx64.zip unzip instantclient-tools-linuxx64.zip sudo chown -R sas:sas /viya-share/gelenv/config/access-clients/oracle   Prepare a sqlnet.ora and tnsnames.ora config file   Sometimes the Oracle database may not be accessible from the Remote Data Agent Server container without a sqlnet.ora and tnsnames.ora file. It’s suggested to have the Oracle configuration file to access the Oracle SID. Place the Oracle configuration file under a folder (mounted) reachable from remote data agent container. The folder containing the Oracle tnsnames files should have read permission for user “sas” (uid 1001:1001).   Code:   mkdir /viya-share/gelenv/config/access-clients/oracle/tnsnames ; tee /viya-share/gelenv/config/access-clients/oracle/tnsnames/sqlnet.ora > /dev/null << EOF DISABLE_OOB=ON EOF RDA_HOST=`hostname ` ; echo $RDA_HOST ; tee /viya-share/gelenv/config/access-clients/oracle/tnsnames/tnsnames.ora > /dev/null << EOF ORACLE_LOCAL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ${RDA_HOST})(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = XE) ) ) EOF sudo chown sas:sas -R /viya-share/gelenv/config/access-clients/oracle/tnsnames ;   Update the sas-access.properties with the Oracle environment Variable   The sas-access.properties file is meant for SAS/ACCESS engine-related environment variables. Users can include and update the respective access engine environment variables. The following variable is related to the Oracle client and tnsnames location.   Code:   ORACLE=/gelenv/config/access-clients/oracle/instantclient_21_10 ORACLE_HOME=/gelenv/config/access-clients/oracle/instantclient_21_10 TNS_ADMIN=/gelenv/config/access-clients/oracle/tnsnames   Start the RDA service with the Oracle client and tnsnames folder mounted   Start the RDA service with the local data folder (e.g. /viya-share/gelenv) mounted to the RDA container. The mounted folder contains the oracle client and tnsnames files.   Code:   ./container-manager start \ --license-path SASViyaV4_9CV11D_license.jwt \ --sasdata sasdata \ --vars da-vars.env \ --data sasdata/data \ --mount /viya-share/gelenv:/gelenv \ --access-vars sas-access.properties \ sas-data-agent-server-remote   Create an Oracle Security Domain at RDA server   Create an Oracle domain on the RDA server to keep the SAS Viya user identity and database credentials in pairs.   Code:   sas-viya dagentsrv servers set-default --data-agent sas-data-agent-server-remote sas-viya dagentsrv security domains create --domain ORADOM1 sas-viya dagentsrv security domains list   Log:   jumpuser@p03173-vm:~$ sas-viya dagentsrv security domains create --domain ORADOM1 The domain was successfully created. jumpuser@p03173-vm:~$ jumpuser@p03173-vm:~$ sas-viya dagentsrv security domains list ID Type Label Description kerberos GSSCredential Kerberos credentials captured by logon and cached for delegation DefaultAuth password The default authentication domain for SAS Viya EsriAuth password EsriAuth Esri authentication credential domain EsriPortalAuth password EsriPortalAuth Local Esri portal authentication credential domain EsriPortalRefreshToken token EsriPortalRefreshToken Esri portal OAuth2 refresh credential domain sas.naturalLanguageConversations.connectors connectorDomain ORADOM1 shadow jumpuser@p03173-vm:~$   Add Viya user identity to Oracle Security Domain at RDA server   Add the SAS Viya user identity with the Oracle database user ID and password to access the database objects from the SAS Viya applications. The Oracle database user password used in the following statement is a one-time user credentials setup. The Password of the database user never leaves the on-premises Remote Data Agent Server. It resides in the SAS Secrets Manager (Vault) at the Remote Data Agent Server.   Code:   ### gatedemo001@gelenable.sas.com - SAS Viya user id. ### ### GELDM - Oracle database user ### ### xxxxxxxxxx - Oracle database password for user GELDM ### sas-viya dagentsrv security credentials create --domain ORADOM1 --identity gatedemo001@gelenable.sas.com --username GELDM --password 'xxxxxxxxxx' sas-viya dagentsrv security credentials create --domain ORADOM1 --identity sasadm@gelenable.sas.com --username GELDM --password 'xxxxxxxxxx' sas-viya dagentsrv security credentials create --domain ORADOM1 --identity geldmui@gelenable.sas.com --username GELDM --password 'xxxxxxxxxx' sas-viya dagentsrv security credentials list --domain ORADOM1   Log:   jumpuser@p03173-vm:~$ sas-viya dagentsrv security credentials list --domain ORADOM1 Identity Type UserId ShadowKey gatedemo001@gelenable.sas.com user GELDM 0848e6c0-ce51-fa4e-90df-70fb1c721602 geldmui@gelenable.sas.com user GELDM 3c2a53e0-20e5-8446-b574-3771a15258fa sasadm@gelenable.sas.com user GELDM 135223bd-b8fb-cc42-bc38-337252cd36a9 jumpuser@p03173-vm:~$   Create an Oracle data source   The following statement creates an Oracle data service using the Remote Server name. You can use IP address if it's convenient. The statement uses the Oracle domain name which has the database user-id password saved with the SAS Viya user identity.   Code:   RDA_HOST=`hostname ` ; sas-viya dagentsrv data-services create oracle --name orasrv1 --path "//${RDA_HOST}:1521/xe" --domain ORADOM1 sas-viya dagentsrv services list   Log:   jumpuser@p03173-vm:~$ sas-viya dagentsrv data-services create oracle --name orasrv1 --path "//${RDA_HOST}:1521/xe" --domain ORADOM1 The data service was successfully created. jumpuser@p03173-vm:~$ sas-viya dagentsrv services list Name Type Domain Version Options BASE base --- 2.5 --- __SERVER__ server --- 2.5 PURGE_CACHE=30;CASE_SENSITIVITY=(OBJECT=F;COLUMN=F);CACHE=(NAME=AS;TIMEOUT=300) orasrv1 oracle ORADOM1 2.5 CONOPTS=(DRIVER=ORACLE;PATH=//utkuma-p03173-jump-vm:1521/xe); CASE_SENSITIVITY=(OBJECT=T;COLUMN=T) jumpuser@p03173-vm:~$   Test the Oracle data source   Log in to the Oracle database using the Viya identity user to fetch the database password from the Oracle security domain and SAS security vault. The Viya identity user is authenticated against the Viya platform using the sas-viya CLI.   Code:   sas-viya dagentsrv data-sources test --name orasrv1   Log:   jumpuser@p03173-vm:~$ sas-viya dagentsrv data-sources test --name orasrv1 Successfully connected to data source orasrv1. jumpuser@p03173-vm:~$   List Oracle database table and data from Oracle data source   With successful test connection to oracle data source, user can list the data table and data from Oracle database using the sas-viya CLI.   Code:   sas-viya dagentsrv data-sources tables list --data-source orasrv1 --catalog ORASRV1 --schema GELDM sas-viya dagentsrv sql --sql "select * from GELDM.TEST_TABLE where id < 10 " --dsn orasrv1   Log:   jumpuser@p03173-vm:~$ sas-viya dagentsrv data-sources tables list --data-source orasrv1 --catalog ORASRV1 --schema GELDM Name Type Native Catalog TEST_TABLE TABLE NULL jumpuser@p03173-vm:~$ jumpuser@p03173-vm:~$ sas-viya dagentsrv sql --sql "select * from GELDM.TEST_TABLE where id < 10 " --dsn orasrv1 DATE_VALUE ID TEXT_VALUE ========== == ========== 1 qIMspSFbYlMvmDElNCGS 2 JyjxLVaLohOyCLwabAeW 3 ouPeMcUPFmHWEoThTAGm 4 WoYrMeFQkwvNynKMSaaN 5 KlCSkfxgNqBLSgHujcMQ 6 SluhQGuIhTRYsoZDotsv 7 vPbUtXnYjmtzNrCIMtMQ 8 miplWVAmetIfmneRUppg 9 hgIaABSQFredHglwUgBh jumpuser@p03173-vm:~$   SAS Access to Oracle Data Source at Remote Data Agent   With the Oracle data source configured at the Remote Data Agent, users can access Oracle data tables from the SAS Compute Server using the CDE LIBNAME statement. The following code describes the access of an Oracle database table from an Oracle data source.   Code:   LIBNAME cdeora1 cde dataagentname="sas-data-agent-server-remote" dsn=orasrv1 preserve_tab_names=yes; data cdeora1.FISH_SAS ; set sashelp.fish ; run; Proc SQL outobs=20; select * from cdeora1.FISH_SAS ; run;quit;   Log:   79 80 LIBNAME cdeora1 cde dataagentname="sas-data-agent-server-remote" 81 dsn=orasrv1 preserve_tab_names=yes; NOTE: Libref CDEORA1 was successfully assigned as follows: Engine: CDE Physical Name: orasrv1 82 83 79 80 data cdelib.fish_sas ; 81 set sashelp.fish ; 82 run; NOTE: There were 159 observations read from the data set SASHELP.FISH. NOTE: The data set CDELIB.fish_sas has 159 observations and 7 variables. NOTE: DATA statement used (Total process time): real time 0.91 seconds cpu time 0.09 seconds 83 79 80 data cdeora1.fish_sas ; 81 set sashelp.fish ; 82 run; NOTE: There were 159 observations read from the data set SASHELP.FISH. NOTE: The data set CDEORA1.fish_sas has 159 observations and 7 variables. NOTE: DATA statement used (Total process time): real time 1.31 seconds cpu time 0.17 seconds 83   CAS Access to Oracle Data Source at Remote Data Agent   With the Oracle data source configured at the Remote Data Agent, users can access Oracle data tables from the CAS using the CDE data connector. The following code describes the CAS access to the Oracle table from an Oracle data source using the CDE CASLIB statement.   Code:   CAS mySession SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); caslib cdeora datasource=( srctype="clouddex", dataAgentName="sas-data-agent-server-remote", catalog="ORASRV1", schema="GELDM", conopts="dsn=orasrv1" ) libref=cdeora; proc casutil incaslib="cdeora"; list files ; run; quit; /* CAS load from CDE RDA Oracle table */ proc casutil incaslib="cdeora" outcaslib="cdeora"; load casdata="TEST_TABLE" casout="TEST_TABLE" replace ; list tables ; run; quit; /* Save CAS table to RDA Oracle database */ proc casutil incaslib="cdeora" outcaslib="cdeora" ; droptable casdata="SAS_CARS" quiet ; load data=sashelp.cars casout="SAS_CARS" ; save casdata="SAS_CARS" casout="SAS_CARS" replace ; list files ; quit ; cas mysession terminate;   Log:   81 82 caslib cdeora datasource=( 83 srctype="clouddex", 84 dataAgentName="sas-data-agent-server-remote", 85 catalog="ORASRV1", 86 schema="GELDM", 87 conopts="dsn=orasrv1" 88 ) libref=cdeora; NOTE: Executing action 'table.addCaslib'. NOTE: 'CDEORA' is now the active caslib. NOTE: Cloud Analytic Services added the caslib 'CDEORA'. NOTE: Action 'table.addCaslib' used (Total process time): NOTE: real time 0.014052 seconds 0 /* CAS load from CDE RDA Oracle table */ 81 proc casutil incaslib="cdeora" outcaslib="cdeora"; NOTE: The UUID 'abb1d057-0994-304e-8e91-871e585ace36' is connected using session MYSESSION. 82 load casdata="TEST_TABLE" casout="TEST_TABLE" replace ; NOTE: Executing action 'table.loadTable'. NOTE: Connecting using the OAuth token for CAS user 'sasadm@gelenable.sas.com'. WARNING: Using server default session timeout of 3600 NOTE: Cloud Analytic Services made the external data from TEST_TABLE available as table TEST_TABLE in caslib cdeora. NOTE: Action 'table.loadTable' used (Total process time): NOTE: real time 2.450688 seconds 84 save casdata="SAS_cars" casout="SAS_cars" replace ; NOTE: Executing action 'table.save'. NOTE: Performing serial SaveTable action using SAS Data Connector to Cloud Data Exchange. NOTE: Connecting using the OAuth token for CAS user 'sasadm@gelenable.sas.com'. WARNING: Using server default session timeout of 3600 NOTE: Connecting using the OAuth token for CAS user 'sasadm@gelenable.sas.com'. WARNING: Using server default session timeout of 3600 NOTE: Cloud Analytic Services saved the file SAS_cars in caslib CDEORA. NOTE: Action 'table.save' used (Total process time): NOTE: real time 0.991539 seconds   Many thanks to colleague Brian Hess for sharing the expert knowledge and help on this post.     Important Links:   Cloud Data Exchange for the SAS Viya Platform Cloud Data Exchange for the SAS Viya Platform SAS Viya Cloud Data Exchange Deployment and Configuration (Part -1) SAS Viya Cloud Data Exchange Deployment and Configuration (Part -2) Configuring BASE SAS Data Source at Remote Data Agent (Cloud Data Exchange)    Find more articles from SAS Global Enablement and Learning here. ... View more

With SAS Viya Cloud Data Exchange (CDE) environment, one widely used data source is the BASE SAS data source. The BASE SAS data source connector/driver is available with CDE deployment. The CDE administrator requires to mount the data folder with a data agent container to avail the datasets. As there are two types of data agents, an administrator can configure the BASE SAS data source at both data agents with access to the data source folder.   In this post, I discuss the configuration of the BASE SAS data source at the Remote Data agent.   The Remote Data Agent is a containerized software and runs with docker runtime. The CDE administrator can mount the required folder of SAS datasets (7bdat) files while starting the Remote Data Agent container. The mounting step can include multiple folders.   The following pics describe the local data folder mounted to the Remote Data Agent container.     Select any image to see a larger version. Mobile users: If you do not see this image, scroll to the bottom of the page and select the "Full" version of this post.   The following steps describe the local data folder mount to Remote Data Agent while starting container service.   Change the user permission of the data folder   The folder containing SAS data sets should have read and write permission for user “sas” (uid 1001:1001).    sudo chown sas:sas /viya-share/gelenv/data   Start the RDA service with the data folder mounted   Start the RDA service with local data folder (e.g. /viya-share/gelenv/data) mounted to the RDA container. Multiple folder values can be specified by separating them with commas, such as --mount /u/my/data:/mydata,/tmp:/vmtmp.    ./container-manager start \ --license-path SASViyaV4_9CV11D_license.jwt \ --sasdata sasdata \ --vars da-vars.env \ --data sasdata/data \ --mount /viya-share/gelenv/data:/gelenv/data \ --access-vars sas-access.properties \ sas-data-agent-server-remote   Create a logical BASE Catalog   Create a logical catalog and schema to access the SAS BASE datasets from the mounted data folder. Use the sas-viya CLI interface to create a logical BASE catalog. A valid user must authenticate against the Viya platform with the required permission to execute CLI.   Code:   sas-viya dagentsrv servers set-default --data-agent sas-data-agent-server-remote; sas-viya dagentsrv catalogs create base --name BASECATR1 ; sas-viya dagentsrv catalogs list ;   Log:   jumpuser@p03173-jump-vm:~$ sas-viya dagentsrv servers set-default --data-agent sas-data-agent-server-remote; The default data agent server was successfully set. jumpuser@p03173-jump-vm:~$ sas-viya dagentsrv catalogs create base --name BASECATR1 ; The catalog was successfully created. jumpuser@p03173-jump-vm:~$ sas-viya dagentsrv catalogs list ; Name Service Native Catalog Options BASECATR1 BASE   Create a Schema under BASE Catalog   Create a BASE schema under logical catalog using the sas-viya CLI interface against the local folder mounted to the RDA container. The Schema features the folder name to access the data sets.   Code:   sas-viya dagentsrv schemas create base --catalog BASECATR1 --name SCHEMAR1 --primary-path /gelenv/data/ ; sas-viya dagentsrv schemas list ;   Log:   jumpuser@p03173-jump-vm:~$ sas-viya dagentsrv schemas create base --catalog BASECATR1 --name SCHEMAR1 --primary-path /gelenv/data/ ; The schema was successfully created. jumpuser@p03173-jump-vm:~$ sas-viya dagentsrv schemas list ; Name Catalog Options SCHEMAR1 BASECATR1 PRIMARYPATH=/gelenv/data/   Create a Test table to BASE data source   Create a test table at the BASE data source and verify the data manipulation using the CLI statement.   Code:   sas-viya dagentsrv sql --sql "create table SCHEMAR1.TEST(col CHAR(10))" --dsn BASE --data-agent sas-data-agent-server-remote sas-viya dagentsrv sql --sql "insert into SCHEMAR1.TEST values ('success')" --dsn BASE --data-agent sas-data-agent-server-remote sas-viya dagentsrv sql --sql "select * from SCHEMAR1.TEST" --dsn BASE --data-agent sas-data-agent-server-remote   Log:   jumpuser@utkuma-p03173-jump-vm:~$ sas-viya dagentsrv sql --sql "create table SCHEMAR1.TEST(col CHAR(10))" --dsn BASE --data-agent sas-data-agent-server-remote SQL statement successfully executed. jumpuser@utkuma-p03173-jump-vm:~$ sas-viya dagentsrv sql --sql "insert into SCHEMAR1.TEST values ('success')" --dsn BASE --data-agent sas-data-agent-server-remote SQL statement successfully executed. jumpuser@utkuma-p03173-jump-vm:~$ sas-viya dagentsrv sql --sql "select * from SCHEMAR1.TEST" --dsn BASE --data-agent sas-data-agent-server-remoteCOL === success jumpuser@p03173-jump-vm:~$   SAS Access to BASE SAS Data Source at Remote Data Agent   With BASE SAS Catalog and Schema configured at Remote Data Agent, users can access SAS data sets from SAS Compute Server using CDE LIBNAME statement. The following code describes the access of SAS data sets from a BASE SAS data source.   Code:   LIBNAME cdelib cde dataagentname="sas-data-agent-server-remote" dsn=BASE catalog=BASECATR1 schema=SCHEMAR1 preserve_tab_names=yes; data cdelib.fish_sas ; set sashelp.fish ; run; Proc SQL outobs=20; select * from cdelib.fish_sas ; run;quit;   Log:   80 81 LIBNAME cdelib cde dataagentname="sas-data-agent-server-remote" 82 dsn=BASE catalog=BASECATR1 schema=SCHEMAR1 preserve_tab_names=yes; NOTE: Libref CDELIB was successfully assigned as follows: Engine: CDE Physical Name: BASE 83 84 79 80 data cdelib.fish_sas ; 81 set sashelp.fish ; 82 run; NOTE: There were 159 observations read from the data set SASHELP.FISH. NOTE: The data set CDELIB.fish_sas has 159 observations and 7 variables. NOTE: DATA statement used (Total process time): real time 0.91 seconds cpu time 0.09 seconds 83 79 80 Proc SQL outobs=20; 81 select * from cdelib.fish_sas ; WARNING: Statement terminated early due to OUTOBS=20 option. 82 run;quit; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: The PROCEDURE SQL printed page 1. NOTE: PROCEDURE SQL used (Total process time): real time 0.51 seconds cpu time 0.06 seconds 83   CAS Access to BASE SAS Data Source at Remote Data Agent   With BASE SAS Catalog and Schema configured at Remote Data Agent, users can access the SAS data sets from CAS using the CDE Data connector. The following code describes the access of SAS data sets from CAS using the CDE CASLIB statement.   Code:   CAS mySession SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); caslib cdebase datasource=( srctype="clouddex", dataAgentName="sas-data-agent-server-remote", catalog="BASECATR1", schema="SCHEMAR1", conopts="dsn=BASE" ) libref=cdebase; /* Save CAS table to CDE table/file */ proc casutil incaslib="cdebase" outcaslib="cdebase" ; droptable casdata="SAS_cars" quiet ; load data=sashelp.cars casout="SAS_cars" replace; save casdata="SAS_cars" casout="SAS_cars" replace ; list tables ; list files ; quit ; /* CAS load from CDE table/file */ proc casutil incaslib="cdebase" outcaslib="cdebase"; load casdata="SAS_cars" casout="sas_cars_cde" replace ; list tables ; run; quit; cas mysession terminate;   Log:   81 82 caslib cdebase datasource=( 83 srctype="clouddex", 84 dataAgentName="sas-data-agent-server-remote", 85 catalog="BASECATR1", 86 schema="SCHEMAR1", 87 conopts="dsn=BASE" 88 ) libref=cdebase; NOTE: Executing action 'table.addCaslib'. NOTE: 'CDEBASE' is now the active caslib. NOTE: Cloud Analytic Services added the caslib 'CDEBASE'. NOTE: Action 'table.addCaslib' used (Total process time): NOTE: real time 0.012973 seconds /* Save CAS table to CDE table/file */ 84 save casdata="SAS_cars" casout="SAS_cars" replace ; NOTE: Executing action 'table.save'. NOTE: Performing serial SaveTable action using SAS Data Connector to Cloud Data Exchange. NOTE: Connecting using the OAuth token for CAS user 'geldmui@gelenable.sas.com'. WARNING: Using server default session timeout of 3600 NOTE: Cloud Analytic Services saved the file SAS_cars in caslib CDEBASE. NOTE: Action 'table.save' used (Total process time): NOTE: real time 0.797028 seconds 81 /* CAS load from CDE table */ 82 proc casutil incaslib="cdebase" outcaslib="cdebase"; NOTE: The UUID '43f2b3f5-82dd-1544-9b29-51c55950d09f' is connected using session MYSESSION. 83 load casdata="SAS_cars" casout="sas_cars_cde" replace ; NOTE: Executing action 'table.loadTable'. NOTE: Connecting using the OAuth token for CAS user 'geldmui@gelenable.sas.com'. WARNING: Using server default session timeout of 3600 NOTE: Cloud Analytic Services made the external data from SAS_cars available as table SAS_CARS_CDE in caslib cdebase. NOTE: Action 'table.loadTable' used (Total process time): NOTE: real time 0.516114 seconds   Next, stay tuned in for a post on Oracle data source configuration at the Remote Data Agent server.   Many thanks to Brian Hess for sharing the expert knowledge and help on this post.   Important Links: Cloud Data Exchange for the SAS Viya Platform   Posts: Cloud Data Exchange for the SAS Viya Platform SAS Viya Cloud Data Exchange Deployment and Configuration (Part-1) SAS Viya Cloud Data Exchange Deployment and Configuration (Part-2)     Find more articles from SAS Global Enablement and Learning here. ... View more

This is the second part of the series “SAS Viya Cloud Data Exchange deployment and configuration”.   The SAS Viya Cloud Data Exchange software is available with most of the SAS Viya bundle license. There is no separate license for Cloud Data Exchange components like Co-located Data Agent and Remote Data Agent services.   SAS Viya Cloud Data exchange have two types of Data Agents.   Co-located Data Agent - Reside at SAS Viya platform environment. Remote Data Agent - Hosted at on-premises data center. In last post, I discuss about Co-located Data Agent deployment and configuration. In this post I discuss Remote Data Agent deployment and configuration at on-premises server.   Deployment and configuration of Remote Data Agent Service The Remote Data Agent is a containerized software and runs with docker runtime. The Remote Data Agent software and components are delivered by using container-manager. It’s a SAS provided tool available at my.sas.com and can be download using standard user profile. You need to download container-manager, Viya Asset ( zip file ), and Viya License file from my.sas.com to deploy Remote Data Agent at on-premises server.   You can deploy more than one Remote Data Agent on on-premises server with same Viya license. More than one Remote Data Agent can be associated with one single Viya platform.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Pre-requisite for on-premises server to host Remote Data Agent Co-located Data Agent at Viya platform configured with data agent credential. Docker Version 18.09.6 or later Container Manager utility tool (Zip file) SAS Viya License and Asset file (Zip file) SSL Certificate for Remote Data Agent Server Public Ip for On-Premises Remote Data Agent Server On-Premises Firewall open for port used by the Remote Data agent ( Default #25141)   Steps to deploy and configure the Remote Data Agent Server.   Generate SSL certificate for Remote Data Agent Server The Remote Data Agent communicate with Viya platform over a secured and encrypted network connection. To facilitate the secured and compatible connection the Remote Data Agent service SSL certificate must be signed by the same root cert authority who have signed for Viya platform. You can use self-signed certificate to start the Remote Data Agent service, but it’s recommended to customer provided Certificate. The Certificate could be generated using a third-party vendor tool or using open SSL tool.   Following are the steps to create a customized SSL Certificate for Remote Data Agent.   Create a Certificate Signing Request.    REMOTE_SERVER=”MyServer.test.com” openssl req -newkey rsa:2048 -keyout ~/certs/rda.key -out ~/certs/rda.csr \ -subj "/C=US/ST=North Carolina/L=Cary/O=SAS/CN=$REMOTE_SERVER" \ -passout pass:mypass   Sign the CSR using the root CA private key and certificate.   While signing the certificate, you include the Remote Data Agent server Public IP and Private Ip addresses.   openssl x509 -req -extensions v3_ca \ -extfile <(echo "[v3_ca]"; echo "extendedKeyUsage=serverAuth"; echo "subjectAltName=DNS:${REMOTE_SERVER},DNS:*.${REMOTE_SERVER},IP:${REMOTE_SERV_PUBIP},IP:${REMOTE_SERV_PVTIP}") \ -days 820 -in ~/certs/rda.csr -CAkey ~/certs/ca_key.pem -CA ~/certs/ca_cert.pem -out ~/certs/rda.pem -passin pass:mypass   Create a certificate container file.   openssl pkcs12 -export -out ~/certs/certificate.pfx -inkey ~/certs/rda.key -in ~/certs/rda.pem \ -certfile ~/certs/ca_cert.pem -passout pass:mypass -passin pass:mypass   Extract the private key and Certificate from container file.   openssl pkcs12 -in ~/certs/certificate.pfx -nocerts -out ~/certs/mykey.pem -nodes -passin pass:mypass openssl pkcs12 -in ~/certs/certificate.pfx -nokeys -out ~/certs/chain.pem -passin pass:mypass   At the end of this process, you obtain the mykey.pem and chain.pem file for Remote Data Agent Service configuration.   Create specific folder structure at Remote Data Agent Server To run the Remote Data Agent services, it requires a specific folder structure owned by user:group 1001:1001. The required files like SSL Cert file, Viya Asset License file, Container Manager runtime, and Data Agent config filers needs to be placed under specific folder. In the following example the deploy folder name could be anything like “CDERemoteDA_Deploy” , but underneath subfolder name has to be same as listed here.   cd ~ mkdir CDERemoteDA_Deploy cd CDERemoteDA_Deploy sudo mkdir -m 700 sasdata sudo mkdir -m 700 sasdata/data sudo chown 1001:1001 -R sasdata/   Copy SSL Certificate to Remote Data Agent deploy folder Copy/move the SSL certificate to Remote Data Agent deploy folder. You can have your own certificate or generate by using above steps described. Change the owner and group of files to 1001:1001.   cp ~/cert/mykey.pem ~/CDERemoteDA_Deploy/sasdata/mykey.pem cp ~/cert/chain.pem ~/CDERemoteDA_Deploy/sasdata/chain.pem sudo chown 1001:1001 -R ~/CDERemoteDA_Deploy/sasdata/   Copy Viya Asset, License file, and Container Manager file to Remote Data Agent deploy folder Copy/move the Viya asset, license, and container manager files to the Remote Data Agent deploy folder, files downloaded from my.sas.com site. I am assuming downloaded files are under ~/mydownload folder at Remote Data Agent Server. The following is the example asset file and license name. Notice the folder location is different from last step.   cp ~/mydownload/SASViyaV4_9CV11D_certs.zip ~/CDERemoteDA_Deploy/ cp ~/mydownload/SASViyaV4_9CV11D_license.jwt ~/CDERemoteDA_Deploy/ cp ~/mydownload/containermgr-linux.tgz ~/CDERemoteDA_Deploy/   Deploy Remote Data Agent container image by using container manager The container manager will deploy the Remote Data Agent container image from cr.sas.com to on-premises server. If you have a local container image repositor, you can use e.g. --image-repository repulpmaster.unx.sas.com ; to deploy the container images. The deployment process use the SAS Viya asset file to fetch the compatible software.   cd ~/CDERemoteDA_Deploy/ tar xvf containermgr-linux.tgz ./container-manager install --deployment-data SASViyaV4_9CV11D_certs.zip sas-data-agent-server-remote   Copy and update Remote Data Agent properties files to deploy folder When you run container manager to deploy Remote Data Agent container image, it also provide a sample configuration file. You copy these files and update as per your environment. The sample config files are located under ~/.sas-container-manager/sas-data-agent-server-remote/stable/files/.   cd ~/CDERemoteDA_Deploy cp ~/.sas-container-manager/sas-data-agent-server-remote/stable/files/* .   Update da-vars.env file   The da-vars.env file is meant for system level configuration of Remote Data Agent. The configuration file includes variable like corresponding Viya Service URL and OAuth secret used at co-lcoated data agent configuration. The Secret must match with the Co-located Data Agent configuration parameter. This config file also contains the location and file name of SSL certificate and key for Remote Data Agent server.   Following is the example from da-vars.env.   # update the SAS_DA_SERVICES_URL= from your Viya Platform URL. The listed value is just as an example. SAS_DA_SERVICES_URL=https://utkuma-p03089-rg.gelenable.sas.com:443 # The SAS_DA_OAUTH_SECRET= hasa the same value what you have it for CDA configuration file at Viya Platform . # The Cert file is the copy of same file used at Viya platform SAS_DA_NAMESPACE=gelenv SAS_DA_OAUTH_SECRET=*3434F0D512A9E05E4EF36450FC676EF151D94B51 SAS_DA_SSLCALISTLOC=/sasdata/chain.pem SAS_DA_SSLPVTKEYLOC=/sasdata/mykey.pem SAS_DA_SSLCERTLOC=/sasdata/chain.pem # Update SAS_DA_RESTRICT_CONTENT_ROOT= with value as FALSE , which will allow data access from folder other than /data folder. # By default RDA can read and write data files to /sasdata/data/ CDE deploy folder.By Putting "FALSE" you can use other folder as well. SAS_DA_RESTRICT_CONTENT_ROOT=FALSE   Start Remote Data Agent Container Service Copy/move the Viya asset, license, and container manager files to the Remote Data Agent deploy folder, files downloaded from my.sas.com site. I am assuming downloaded files are under ~/mydownload folder at Remote Data Agent Server. The following is the example asset file and license name. Notice the folder location is different from last step.   cd ~/CDERemoteDA_Deploy ./container-manager start \ --license-path SASViyaV4_9CV11D_license.jwt \ --sasdata sasdata \ --vars da-vars.env \ --data sasdata/data \ --access-vars sas-access.properties \ sas-data-agent-server-remote   Verify Remote Data Agent service log You can view and verify the log of Remote Data Agent service by using docker statement. The log includes all the steps performed to start the Remote Data agent service.   jumpuser@p03089-jump-vm:~/CDERemoteDA_Deploy$ docker logs sas-data-agent-server-remote {"level":"info","message":"Starting Vault....","properties":{"caller":"sas-data-agent-server-remote-entrypoint.sh:104","logger":"Script","pod":"sas-data-agent-server-remote","sessionUser":"Script:sas","thread":"00000000"},"source":"dagentsrv","timeStamp":"2023-05-10T16:22:39.421542+00:00","version":1} ..... ............... ............... {"level":"info","message":"SAH061999I Server SAS Data Agent, State, running","properties":{"caller":"tkecrapp.c:270","logger":"Admin.Operations","pod":"sas-data-agent-server-remote","sessionUser":"Server:sas","thread":"00000005"},"source":"dagentsrv","timeStamp":"2023-05-10T16:22:59.704000+00:00","version":1} jumpuser@p03089-jump-vm:~/CDERemoteDA_Deploy$   Verify the network access between Remote Data Agent and Viya Platform Once Remote Data Agent container service is up and running at on-premises server. You should verify the access path between Remote Data Agent and Viya platform. The verification process will prove whether SSL certificate is working or not with port open at on-premises firewall. The verification steps include the execution of curl statement at SAS Viya pod container to access Remote data agent container and vice -versa.   At Viya Platform hop onto data agent container and execute Curl Statement to Remote Data Agent container.   [cloud-user@pdcesx03089 ~]$ kubectl get pods -n gelenv | grep data-agent sas-data-agent-server-colocated-0 1/1 Running 0 99m sas-data-agent-services-d77bcf87c-jxr5v 1/1 Running 0 100m [cloud-user@pdcesx03089 ~]$   cloud-user@pdcesx03089 ~]$ [cloud-user@pdcesx03089 ~]$ kubectl exec -i -t -n gelenv sas-data-agent-services-d77bcf87c-jxr5v -c sas-data-agent-services -- sh -c "(bash || ash || sh)" bash-4.4$ ## Once you are in the Pod’s shell, run the curl statement with Remote Data Agent public/pvtIp and port. bash-4.4$ curl -kv https://192.168.2.5:25141 * Rebuilt URL to: https://192.168.2.5:25141/ * Trying 192.168.2.5... * TCP_NODELAY set * Connected to 192.168.2.5 (192.168.2.5) port 25141 (#0) * ALPN, offering h2 * ALPN, offering http/1.1 * successfully set certificate verify locations: * CAfile: /etc/pki/tls/certs/ca-bundle.crt CApath: none * TLSv1.3 (OUT), TLS handshake, Client hello (1): * TLSv1.3 (IN), TLS handshake, Server hello (2): .... ..........   At Remote Data Agent server, hop onto Remote Data Agent container and execute Curl Statement to access SAS Viya platform (SAS Viya Service URL). You need to update the AKS cluster Security Network Group (NSG) to include the incoming traffic from Remote Data agent Ips.   jumpuser@p03173-jump-vm:~/CDERemoteDA_Deploy$ docker exec -it sas-data-agent-server-remote bash bash-4.4$ bash-4.4$ curl -kv https://utkuma-p03173-rg.gelenable.sas.com:443 * Rebuilt URL to: https://utkuma-p03173-rg.gelenable.sas.com:443/ * Trying 20.241.132.184... * TCP_NODELAY set * Connected to utkuma-p03173-rg.gelenable.sas.com (20.241.132.184) port 443 (#0) * ALPN, offering h2 * ALPN, offering http/1.1 * successfully set certificate verify locations: * CAfile: /etc/pki/tls/certs/ca-bundle.crt CApath: none * TLSv1.3 (OUT), TLS handshake, Client hello (1): * TLSv1.3 (IN), TLS handshake, Server hello (2): * TLSv1.3 (IN), TLS handshake, [no content] (0): ..... ............. ........................   Register the Remote Data Agent server to Viya Platform With successful access path verification between Remote Data Agent Server and Viya platform your Viya environment is ready to register a Remote Data Agent server. The Data Agent registration enables SAS compute server and CAS to access the data located at on-premises server using Remote Data Agent Data Sources.   The sas-viya CLI is the interface to administer CDE Data Agent Service. Before you can use the CLI you need to authenticated against the SAS Service URL .   [cloud-user@pdcesx03089 ~]$ export SSL_CERT_FILE=~/.certs/my_trustedcerts.pem [cloud-user@pdcesx03089 ~]$ sas-viya auth login --user geldmui@gelenable.sas.com --password 'XXXXXXXXXXX' Enter credentials for https://utkuma-p03089-rg.gelenable.sas.com: Login succeeded. Token saved. [cloud-user@pdcesx03089 ~]$   While registering, use the Public Ip (or Pvt Ip) address of Remote Data Agent server along with the Port # which is open at on-premises Firewall.   sas-viya dagentsrv servers register --name sas-data-agent-server-remote --address https://192.168.2.5:25141 --context-root rda   Execute few CLI statement to access the Remote Data Agent components. The successful execution of these statement verifies that the environment configuration is good and it’s ready for various data source ( SAS datasets, Oracle etc… ).   [cloud-user@pdcesx03089 ~]$ sas-viya profile set-output text [cloud-user@pdcesx03089 ~]$ sas-viya dagentsrv servers set-default --data-agent sas-data-agent-server-remote The default data agent server was successfully set. [cloud-user@pdcesx03089 ~]$ sas-viya dagentsrv server list Name Host Port Attributes sas-data-agent-server-colocated utkuma-p03089-rg.gelenable.sas.com 443 contextroot=dataAgentServerColocated;ssl=Y sas-data-agent-server-remote 192.168.2.5 25141 contextroot=rda;ssl=Y [cloud-user@pdcesx03089 ~]$ sas-viya -k dagentsrv data-sources list Name ID Type ADMIN ADMIN DSN BASE BASE DSN BASE BASE__DATA_SERVICE__ Service [cloud-user@pdcesx03089 ~]$ sas-viya -k dagentsrv services list Name Type Domain Version Options BASE base --- 2.5 --- __SERVER__ server --- 2.5 PURGE_CACHE=30;CASE_SENSITIVITY=(OBJECT=F;COLUMN=F);CACHE=(NAME=AS;TIMEOUT=300)   Next, stay tuned-in for post on various data source configuration at Remote Data Agent server .   Many thanks to Brian Hess for sharing the expert knowledge and help on this post.   Important Links: Cloud Data Exchange for the SAS Viya Platform SAS Viya Cloud Data Exchange Deployment and Configuration (Part -1)     ... View more

In my last post I talked about SAS Viya Cloud Data Exchange, it’s components and how it can help SAS users to access on-remises data from cloud hosted SAS Viya platform. As mentioned in last post, the configuration between Remote Data Agent and SAS Viya platform is a challenging task. So, let’s look at the details steps to integrate Remote Data Agent service with SAS Viya platform.   This post is in two parts, the first part describing the deployment and configuration of Co-located Data Agent service. The second part is describing the deployment and configuration of Remote Data Agent.   The SAS Viya Cloud Data Exchange software is available with most of the SAS Viya bundle license. There is no separate license for Cloud Data Exchange components like Co-located Data Agent service and Remote Data Agent services.   SAS Viya Cloud Data exchange have two types of Data Agents. Co-located Data Agent - Reside at SAS Viya platform environment. Remote Data Agent - Hosted at on-premises data center.       Co-located Data Agent Deployment and configuration   The Co-located Data Agent software and components are delivered with the standard SAS Viya deployment. The Co-located Data Agent (CDA) pods and services starts along with the rest of SAS Viya services. Configuration of Co-located Data Agents are described in the following steps.   Use example configuration file for Co-located Data Agent (CDA)   The CDA configuration example is provided under ../sas-bases/examples/sas-data-agent-server-colocated folder to configure the Co-located Data Agent and integrate with Remote Data Agent services. You can copy the example folder to site-config folder and update the configuration files.   cp -r …./sas-bases/examples/sas-data-agent-server-colocated …./site-config/ chmod 755 …./site-config/sas-data-agent-server-colocated/*.*   There are two configuration files sas-data-agent-server-colocated-secret.properties and sas-data-agent-server-colocated-config.properties.   The sas-data-agent-server-colocated-secret.properties file contains the SAS_DA_OAUTH_SECRET= variable. The value of SAS_DA_OAUTH_SECRET= is used by Remote Data Agent service to connect to SAS Viya platform for system level information like SAS Viya user identity. You must provide the same value while configuring Remote Data Agent service at on-premise server. The password could be in plain text or encrypted or hashed.   # hashed password in sas-data-agent-server-colocated-secret.properties SAS_DA_OAUTH_SECRET=*3434F0D512A9E05E4EF36450FC676EF151D94B51   The sas-data-agent-server-colocated-config.properties file is meant for Data Agent level configuration. It contain variable like debugging trace flag, user data folder, SAS Access clients variables and so on.   # Variable examples in sas-data-agent-server-colocated-config.properties SAS_DA_DEBUG_LOGTYPE=TRACEALL SAS_DA_RESTRICT_CONTENT_ROOT=TRUE SAS_DA_CONTENT_ROOT=/gelcontent/data/ Update kustomization.yaml file   Include the sas-data-agent-server-colocated-secret.properties and sas-data-agent-server-colocated-config.properties file in kustomization.yaml to build and start the Co-located Data agent with updated parameters.   printf " - command: update path: secretGenerator[+] value: name: sas-data-agent-server-colocated-secrets behavior: merge envs: - site-config/sas-data-agent-server-colocated/sas-data-agent-server-colocated-secret.properties" | yq -I 4 w -i -s - …./kustomization.yaml printf " - command: update path: configMapGenerator[+] value: name: sas-data-agent-server-colocated-config behavior: merge envs: - site-config/sas-data-agent-server-colocated/sas-data-agent-server-colocated-config.properties" | yq -I 4 w -i -s - ….kustomization.yaml   Build and deploy the updated manifestation   cd ~/project/deploy/ kustomize build -o site.yaml NS=myenv; kubectl apply -n ${NS} -f ~/project/deploy/gelenv/site.yaml ; ## Recycle Data Agent Pods. kubectl -n ${NS} delete pod --selector='app.kubernetes.io/name=sas-data-agent-server-colocated' kubectl -n ${NS} delete pod --selector='app.kubernetes.io/name=sas-data-agent-services'   Add SAS Viya users to DataAgentAdmin and DataAgentPower user group   The administrative tasks of Data Agents are executed using sas-viya CLI. Make sure you have sas-viya CLI installed at your local client machine or at jump server. To execute Data Agent administrative task, user must be in a specific SAS Viya user group. The users from DataAgentAdminstrators group can configure and administer Data Agents, Data Source , Security Domain and Data Services. The users from DataAgentPowerUsers group can access the data sources by executing DQL, DML and DDL statement. As per user place them in the respective SAS Viya user’s group.   export SSL_CERT_FILE=~/.cerrts/my_trustedcerts.pem sas-viya auth login --user sasboot –password sas-viya identities add-member --group-id DataAgentAdministrators --user-member-id geldmui@gelenable.sas.com sas-viya identities add-member --group-id DataAgentPowerUsers --user-member-id geldmui@gelenable.sas.com sas-viya identities add-member --group-id DataAgentPowerUsers --user-member-id gatedemo001@gelenable.sas.com sas-viya identities add-member --group-id DataAgentPowerUsers --user-member-id gatedemo002@gelenable.sas.com   With the above steps, SAS Viya environment is configured to integrate with Remote Data Agent services. You can also create data sources at Co-located Data Agent server which are directly accessible from Viya platform.   Stay tuned-in for second part of this series on Remote Data Agent Deployment and Configuration.   Many thanks to my colleague Brian Hess for sharing the expert knowledge and help on this post.   Important Links:   Document: Cloud Data Exchange for the SAS Viya Platform Blog: Cloud Data Exchange for the SAS Viya Platform     ... View more

The Cloud Data Exchange(CDE) for SAS Viya platform is available with the 2023.03 release. The CDE allows SAS users to connect a database which could be either co-located at SAS Viya platform or located at an on-premises data center. The CDE enables user to securely exchange on-premises data from behind a firewall to the SAS Viya platform in the cloud. All data transfer/exchange occurs on secured standard-based communications along with sophisticated authentication and authorization. The on-premises data center requires a single port opened through the firewall to facilitate the data exchange.   The CDE Data Agents comes with the standard SAS Viya platform and does not require a separate additional license. The CDE includes two SAS Data Agent to facilitate the data access and exchange. A Co-located SAS Data Agent, used for accessing data available at the SAS Viya platform. A Remote SAS Data Agent, used for accessing data available at on-premises data center (behind a firewall) from SAS Viya platform in cloud. There can be multiple Remote SAS Data Agents associated with a single SAS Viya deployment. The Data Agents are containerized run time service. The Co-located SAS Data Agent container starts with the standard SAS Viya platform. The Remote SAS Data Agent is a on-premises server that runs as a docker container.   The following picture describes an overall data architecture diagram of the SAS Viya platform, co-located Data Agent, and Remote Data Agent to exchange data between various locations.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   The CDE enables users to directly load CAS from on-premises data and save it back to the on-premises server. The CDE access engine is available to both SAS Compute Server and CAS. A SAS user can use the CDE LIBNAME statement from the SAS Compute server and the CDE CASLIB statement from CAS to access the on-premises data sources. The CDE also enables users to move/copy on-premises data files to SAS Viya cloud storage and vice versa.   The SAS Data Agent administration and data access is supported by a microservice that provides a set of REST interfaces. A Command Line Interface 'sas-viya' CLI is available to administer the SAS Data Agents. A SAS user from the "SAS Data Agent Administrators" group can execute administrative tasks like creating domains and data services at the SAS Data Agent. A SAS user from the “Data Agent Power Users” group can perform tasks like DML (insert, update, and delete), DQL(select), and DDL (create and drop table) at SAS Data Agent.   The CDE Data Agent supports following data sources to exchange data.     The configuration of communication between the Remote Data Agent and the SAS Viya platform is a challenging task. An admin user has to have some degree of expertise in configuring the SAS Viya platform with encrypted (TLS..) communication. Stay tuned in for the next blog on how to deploy and configure Remote Data Agent to securely communicate with the SAS Viya Platform.   Many thanks to my colleague Brian Hess for sharing the expert knowledge and help on this post.   Important Links:   Cloud Data Exchange for the SAS Viya Platform CDE Supported Data Services     Find more articles from SAS Global Enablement and Learning here. ... View more

If you have SAS Viya with SingleStore deployment, you notice that the CAS load from a SingleStore table (with auto-increment field) is blazing fast. The CAS and SingleStore(S2) database services are tightly integrated. When CAS loads from a SingleStore table, it does not bring all the data to make a copy into CAS, instead on-the-fly streams the data required by CAS actions. The integration (CAS and S2) features enabled users to instantly view new datasets at CAS when ingested into a S2 table.   SingleStore(S2) Pipeline plays a vital role in data ingestion into S2 tables. S2 Pipeline is a feature that continuously loads data into the S2 table from an external data source as it arrives at the source environment. It enabled users to instantly ingest the data into the S2 table with new datasets and view/experience the additional datasets at the S2 table, CAS, and VA reports.   This post highlights the SingleStore Pipeline from ADLS2 cloud storage to CAS. The post is an extension to “SAS Viya with SingleStore: Near Real-Time Dashboarding Using SAS Visual Analytics”, posted by  @NicolasRobert    SingleStore(S2) Pipeline is a built-in component of the S2 cloud database and can be configured against various data sources like Azure Blob Storage, S3 Storage, GCS Storage, HDFS storage, Kafka, and an external linked database. The S2 Pipeline can perform ETL tasks (Extract Transform and Load) without the need for any additional tools.   The S2 Pipeline supports JSON, Avro, Parquet, and CSV file format of data to ingest into the S2 table.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   S2 Pipeline with data files at ADLS2   With the S2 Database instance up and running and data files located at ADLS2 storage with known schema, you can use the following steps to create the S2 Pipeline. The S2 Pipeline requires the ADLS2 information including Storage Key to access the data files.   The following screen describes the data file located at the ADLS2 location. The listed data files in the folder have the same file structure/schema.     Create a sample table at S2 database.   With known datafile schema need to create an S2 database table.   /* Set the working database */ use geldm ; /* Drop objects if exist */ DROP TABLE IF EXISTS prdsale_from_pl ; /* Create the prdsale table */ CREATE TABLE IF NOT EXISTS prdsale_from_pl ( actual double, predict double, country varchar(20), region varchar(20), division varchar(20), prodtype varchar(20), product varchar(20), quarter double, year double, month date, /* auto_increment column that will be useful for CAS later */ rowId bigint(20) NOT NULL AUTO_INCREMENT , KEY rowId (rowId) USING CLUSTERED COLUMNSTORE, SHARD KEY () ) AUTOSTATS_CARDINALITY_MODE=INCREMENTAL AUTOSTATS_HISTOGRAM_MODE=CREATE AUTOSTATS_SAMPLING=ON SQL_MODE='STRICT_ALL_TABLES' ; /* Check the created table */ DESCRIBE prdsale_from_pl ;       Create S2 Pipeline with data file location to ADSL2.   use geldm ; /* Drop objects if exist */ DROP PIPELINE IF EXISTS prdsale_pl ; /* Define the pipeline */ /* AGGREGATOR pipeline to support AUTO_INCREMENT */ /* Specific notation to parse the date from the CSV file */ CREATE AGGREGATOR PIPELINE IF NOT EXISTS prdsale_pl AS LOAD DATA AZURE 'blobdata/data/prdsale/' CREDENTIALS '{"account_name": "", "account_key": ""}' INTO TABLE prdsale_from_pl FIELDS TERMINATED BY ',' (actual,predict,country,region,division,prodtype,product,quarter,year,@month) SET month = STR_TO_DATE(@month, '%d%b%Y') ; Verify S2 Pipeline   use geldm ; /* Show status of pipelines */ SHOW PIPELINES ; /* Show status of detected files by the pipeline */ SELECT * FROM information_schema.PIPELINES_FILES ;         Start S2 Pipeline   use geldm ; /* Start the pipeline */ START PIPELINE IF NOT RUNNING prdsale_pl ; /* Show status of pipelines */ SHOW PIPELINES ; /* Show status of detected files by the pipeline */ SELECT * FROM information_schema.PIPELINES_FILES ;       View data from S2 database table   use geldm; /* Print a sample */ select * from prdsale_from_pl ; /* Simple aggregation */ select country, sum(actual) as actual, sum(predict) as predict from prdsale_from_pl group by country ;       View data at CAS table loaded from S2 database   Before you can run the following statement, you need to load the S2 table to CAS using “singlestore” type CASLIB as global CAS table.   /* simple aggregation at CAS table */ proc fedSQL sessref=mysession ; select country, sum(actual) as actual, sum(predict) as predict from s2.prdsale_from_s2 group by country ; quit ;     Add new data files to ADLS2 location   The following screenshot describes the additional data files added to the ADLS2 location. As the S2 pipeline is active and running, it will load the data to the S2 table and eventually be available in the CAS table.     Check the SingleStore Pipeline and database   use geldm ; /* Show status of detected files by the pipeline */ SELECT * FROM information_schema.PIPELINES_FILES ; /* Simple aggregation */ select country, sum(actual) as actual, sum(predict) as predict from prdsale_from_pl group by country ;   Since two new data files are part of the S2 pipeline and loaded to the S2 table, the aggregate SQL returns two more groups.       View data from CAS able loaded from S2 database   The global CAS table is loaded from the S2 table when new data is ingested into the S2 table, immediately available to the CAS table as well. The aggregation query on the CAS table returns two more groups.   /* simple aggregation at CAS table */ proc fedSQL sessref=mysession ; select country, sum(actual) as actual, sum(predict) as predict from s2.prdsale_from_s2 group by country ; quit ;       To update S2 Pipeline   To apply changes to S2 Pipeline use ALTER PIPELINE statement. For example, if the Azure Storage Account Key changes, you can use the following statement to update the Pipeline with the latest Key. After updating the Pipeline, you may have to restart.   ALTER PIPELINE prdsale_pl SET CREDENTIALS '{"account_name": "XXXXXXXX", "account_key": "F8tLDXMXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"}'; START PIPELINE IF NOT RUNNING prdsale_pl ; Additional CREATE Pipeline Examples   While creating S2 Pipelines you may use the SKIP, IGNORE, or REPLACE clause. You can use one or all these options in the same create a PIPELINE statement. More information is available at Additional CREATE S2 PIPELINE Examples. Example:   CREATE PIPELINE AS LOAD DATA SKIP CONSTRAINT ERRORS INTO TABLE ; CREATE PIPELINE AS LOAD DATA IGNORE PARSER ERRORS REPLACE INTO TABLE ;   Many Thanks to @NicolasRobert for his help and contribution to this post .       Important Links:    SAS Viya with SingleStore: Near Real-Time Dashboarding Using SAS Visual Analytics   References:   Create S2 Pipeline Alter S2 Pipeline Additional CREATE S2 PIPELINE Examples       Find more articles from SAS Global Enablement and Learning here. ... View more

SAS Viya (CAS) user can read and write parquet data files to cloud storage ( ADLS2, S3, and GCS) and path storage ( DNFS, NFS, and local Unix FS). The SAS Viya Compute Server also supports parquet file read and write to two cloud storage (GCS and S3) and path storage ( DNFS, NFS, and local Unix FS) with various compression.   This post highlights the additional features supported for SAS Viya and Parquet files in the last SAS Viya releases.   CAS Load from S3-Parquet folder with status (_SUCCESS ) files   SAS Viya (CAS) can read and write parquet data files to an S3 bucket using S3 CASLIB. If you have parquet files generated to an S3 bucket by a third-party application like SPARK, Databrick, etc., you may notice few additional process status files (like _SUCCESS_, _started_, _committed_) along with actual data files. These status files were an issue for CAS while loading from a folder containing n-number of parquet data files.   With Viya 2022.12 release, CAS can now read the S3-parquet data file folder with additional status files. The CAS load process ignores these status files and only considers the actual data files.   The following screenshot describes an S3 bucket folder with parquet files and process status files generated by a SPARK cluster.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   The following code describes the CAS load from the above S3 bucket folder. If the folder name does not have a .parquet extension, you need to specify the IMPORTOPTIONS=(FILETYPE="PARQUET") in the CAS load action.   Code:   %let userid=utkuma; %let s3bucket=&userid.dmviya4 ; %let aws_config_file="/gelcontent/keys/awskeyconfig" ; %let aws_credentials_file="/gelcontent/keys/credentials" ; %let aws_profile="182696677754-testers" ; %let aws_region="US_East"; %let objpath="/data/"; CAS mySession SESSOPTS=( CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); /* CASLIB to S3 bucket data folder */ caslib AWSCAS1 datasource=(srctype="s3", awsConfigPath=&aws_config_file, awsCredentialsPath=&aws_credentials_file , awsCredentialsProfile=&aws_profile, region=&aws_region, bucket=&s3bucket, objectpath=&objpath ) subdirs ; /* Load CAS from Parquet data files ( with _SUCCESS _started files) */ proc casutil incaslib="AWSCAS1" outcaslib="public"; droptable casdata="BaseballSpark" incaslib="public" quiet; load casdata="PARQUET/BaseballSpark" casout="BaseballSpark" IMPORTOPTIONS=(FILETYPE="PARQUET") promote ; run; quit; proc casutil incaslib="public" outcaslib="public"; list tables ; run;quit; CAS mySession TERMINATE;   Log extract:   .... .............. 101 102 /* Load CAS from Parquet data files ( with _SUCCESS _started files) */ 103 proc casutil incaslib="AWSCAS1" outcaslib="public"; NOTE: The UUID 'a5e9318c-4422-5243-9375-b3aa82c2f214' is connected using session MYSESSION. 105! load casdata="PARQUET/BaseballSpark" casout="BaseballSpark" IMPORTOPTIONS=(FILETYPE="PARQUET") promote ; NOTE: Executing action 'table.loadTable'. NOTE: Cloud Analytic Services made the file PARQUET/BaseballSpark available as table BASEBALLSPARK in caslib public. NOTE: Action 'table.loadTable' used (Total process time): NOTE: real time 0.938480 seconds NOTE: cpu time 0.475109 seconds (50.63%) NOTE: total nodes 4 (32 cores) NOTE: total memory 251.04G NOTE: memory 61.29M (0.02%) NOTE: bytes moved 438.31K NOE: The Cloud Analytic Services server processed the request in 0.93848 seconds. 106 run; 107 quit; .... ..............   Result Output:      Parquet Engine support additional data types   The SAS Viya Compute server supports access to parquet files at GCS, Path locations, and S3 bucket. With the last few releases, additional data types are supported while reading Parquet data files to SAS. More details are available in the documentation under Conversion between Parquet and SAS Data types.   The following data types are supported with 2022.12 release.   DATE INTERVAL TIME TIMESTAMP   The following data types are supported with 2023.01 release. STRING ENUM UUID     Important Links:   About Parquet and ORC Engines Conversion between Parquet and SAS Data types Parquet and ORC LIBNAME statement and Options Restriction for Parquet File Features    Find more articles from SAS Global Enablement and Learning here. ... View more