The SAS Viya Cloud Data Exchange’s File Transfer Service enables SAS users to copy data files between a Remote Data Agent Server and the Co-located Data Agent Server storage. The File Transfer Services uses a secure, encrypted network connection to transfer the files between the Remote Data Agent and Co-located Data Agent storage. When the Remote Data Agent is not able to access a data file (.txt, .csv, .json) from an on-premises environment, it is convenient to copy the file to the SAS Viya environment and access it by using the SAS Compute Server. There could also be a use case to copy the SAS Viya environment data files to the on-premises storage for another process. The Cloud Data Exchange File Transfer Service can address all these use cases.   In this post, I discuss the configuration of the File Transfer Services at the Co-located Data Agent and Remote Data Agent.   A Cloud Data Exchange (CDE) administrator can create and configure the File Transfer Services in the SAS Viya CDE environment at the Remote Data Agent Server and Co-located Data Agent Server to enable the file transfer. The CDE environment is configured using the SAS-Viya CLI statement.     Pre-requisite   The CDE Remote Data Agent is configured with the SAS Viya environment. The CDE Remote Data Agent is mounted to a storage for data file transfer. The CDE Co-located Data Agent is mounted to a storage for data file transfer.   The following pics describe the CDE File Transfer Service and file transfer path between the Remote Data Agent Server and the SAS Viya Co-located 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 CDE File Transfer Service and Data Source   The following steps outline the creation of the CDE File Transfer Service and data source at the Remote Data Agent and Co-located Data Agent.     Log in and authenticate for sas-viya CLI statement execution   A CDE Administrator must log in and authenticate to the SAS Viya environment before executing sas-viya CLI statement to configure the CDE components.     Code:   export SAS_CLI_PROFILE=Default; export SSL_CERT_FILE=~/.certs/my_trustedcerts.pem ; sas-viya profile set-output text ; sas-viya auth login --user $Uid --password $Pwd   Log:   [cloud-user@pdcesx41524 ~]$ sas-viya auth login --user $Uid --password $Pwd https://camel-p41524-rg.gelenable.sas.com Login succeeded. Token saved. [cloud-user@pdcesx41524 ~]$     Create a File Transfer Service at the Co-located Data Agent (CDA) Server.   The following sas-viya CLI statement creates a File Transfer Data Service, catalog, and schema on CDA. The File Transfer service schema contains the path/location for the source/target data files. The --depth parameter in the schema creation CLI statement indicates the depth/layer of subdirectories for data files.   Code:   # CDA # Set #cda to name of your data agent and #cat is catalog name cda=sas-data-agent-server-colocated cat=FT_NFS_CDA_CAT sas-viya dagentsrv servers set-default --data-agent $cda # Create File Transfer Service sas-viya dagentsrv service create file-transfer --name FT_NFS_CDA # Create a Catalog under File Transfer Service sas-viya dagentsrv catalog create file-transfer --name $cat --data-service FT_NFS_CDA # Create a data file schema under the Catalog and File Transfer Service # --depth 2 indicates how many subdirectories of data are available. sas-viya dagentsrv schema create file-transfer --catalog $cat --name FT_NFS_CDA_SCHEMA1 --primary-path "/mnt/viya-share/data/CDA" --depth 2 --encoding utf8   Log:   [cloud-user@pdcesx41524 ~]$ cda=sas-data-agent-server-colocated [cloud-user@pdcesx41524 ~]$ cat=FT_NFS_CDA_CAT [cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv servers set-default --data-agent $cda The default data agent server was successfully set. [cloud-user@pdcesx41524 ~]$ [cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv service create file-transfer --name FT_NFS_CDA The data service was successfully created. [cloud-user@pdcesx41524 ~]$ [cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv catalog create file-transfer --name $cat --data-service FT_NFS_CDA The catalog was successfully created. [cloud-user@pdcesx41524 ~]$ [cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv schema create file-transfer --catalog $cat --name FT_NFS_CDA_SCHEMA1 --primary-path "/mnt/viya-share/data/CDA" --depth 2 --encoding utf8 The schema was successfully created. [cloud-user@pdcesx41524 ~]$     Create a File Transfer Service at the Remote Data Agent (RDA) Server.   The following sas-viya CLI statement creates a File Transfer Data Service, catalog, and schema on RDA. The File Transfer service schema contains the path/location for the source/target data files. The --depth parameter in the schema creation CLI statement indicates the depth/layer of subdirectories for data files.   Code:   # RDA # Set #rda to name of your data agent and #cat is catalog name rda=sas-data-agent-server-remote cat=FT_NFS_RDA_CAT sas-viya dagentsrv servers set-default --data-agent $rda # Create File Transfer Service sas-viya dagentsrv service create file-transfer --name FT_NFS_RDA # Create a Catalog under File Transfer Service sas-viya dagentsrv catalog create file-transfer --name $cat --data-service FT_NFS_RDA # Create a data file schema under the Catalog and File Transfer Service # --depth 2 indicates how many subdirectories of data are available. sas-viya dagentsrv schema create file-transfer --catalog $cat --name FT_NFS_RDA_SCHEMA1 --primary-path "/mnt/viya-share/data/RDA" --depth 2 --encoding utf8   Log:   [cloud-user@pdcesx41524 ~]$ # RDA [cloud-user@pdcesx41524 ~]$ # Set #rda to name of your data agent and #cat is catalog name [cloud-user@pdcesx41524 ~]$ rda=sas-data-agent-server-remote [cloud-user@pdcesx41524 ~]$ cat=FT_NFS_RDA_CAT [cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv servers set-default --data-agent $rda The default data agent server was successfully set. [cloud-user@pdcesx41524 ~]$ [cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv service create file-transfer --name FT_NFS_RDA The data service was successfully created. [cloud-user@pdcesx41524 ~]$ [cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv catalog create file-transfer --name $cat --data-service FT_NFS_RDA The catalog was successfully created. [cloud-user@pdcesx41524 ~]$ [cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv schema create file-transfer --catalog $cat --name FT_NFS_RDA_SCHEMA1 --primary-path "/viya-share/gelenv/data/RDA" --depth 2 --encoding utf8 The schema was successfully created. [cloud-user@pdcesx41524 ~]$     Create a Data Agent Data-source Service at CDA with reference to the RDA File Transfer Service.   The RDA File Transfer Service is available on the Remote Data Server. You need to create a Data Agent Service at CDA with reference to the RDA File Transfer Service. Having a Data Agent (reference to RDA service) at CDA enables users to access the RDA schema and objects from the CDA server. This will help in the later stage when you create a Federated Data source for file transfer activity.   In the first phase of the Data Agent service, you create a Security domain with user credentials and later use it in the data agent creation. The users in the security domain can only avail this feature to access the files at RDA and use it in file copy operations from RDA to CDA storage.   The following sas-viya CLI statement creates a Security Domain with user credentials and later creates the Data Agent Service with the security domain.   Code:   cda=sas-data-agent-server-colocated myuser=sastest1@gelenable.sas.com myuserid=sastest1@gelenable.sas.com mypassword=$AZUPW rda=sas-data-agent-server-remote sas-viya dagentsrv servers set-default --data-agent $cda # Create Security domain with credentials. Domain name can be whatever you want sas-viya dagentsrv security domain create --domain RDA --type password sas-viya dagentsrv security credentials create --domain RDA --identity $myuser --username "$myuserid" --password "$mypassword" # data agent Data service sas-viya dagentsrv service create data-agent --name RDA_FT --domain RDA --data-agent-id "$rda" --dsn FT_NFS_RDA   Log:   [cloud-user@pdcesx41524 ~]$ cda=sas-data-agent-server-colocated [cloud-user@pdcesx41524 ~]$ myuser=sastest1@gelenable.sas.com [cloud-user@pdcesx41524 ~]$ myuserid=sastest1@gelenable.sas.com [cloud-user@pdcesx41524 ~]$ mypassword=$AZUPW [cloud-user@pdcesx41524 ~]$ rda=sas-data-agent-server-remote [cloud-user@pdcesx41524 ~]$ [cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv servers set-default --data-agent $cda The default data agent server was successfully set. [cloud-user@pdcesx41524 ~]$ [cloud-user@pdcesx41524 ~]$ [cloud-user@pdcesx41524 ~]$ # Create domain with credentials. Domain name can be whatever you want [cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv security domain create --domain RDA --type password The domain was successfully created. [cloud-user@pdcesx41524 ~]$ [cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv security credentials create --domain RDA --identity $myuser --username "$myuserid" --password "$mypassword" The credential was successfully created. [cloud-user@pdcesx41524 ~]$ [cloud-user@pdcesx41350 ~]$ # data agent Data service [cloud-user@pdcesx41350 ~]$ sas-viya dagentsrv service create data-agent --name RDA_FT --domain RDA --data-agent-id "$rda" --dsn FT_NFS_RDA The data service was successfully created. [cloud-user@pdcesx41350 ~]$     Create a Federated Data Source at CDA for File Transfer.   Now that there is a Data Agent File Transfer Service (referenced to RDA File Transfer Service), and a File Transfer Service at CDA, a Federated Data Source service can be created using these two file transfer services. The Federated Data source will enable the user to access the objects from CDA and RDA using a fully qualified path under the same Co-located Data Agent(CDA) server. This enables the file transfer between the CDA and RDA environments.   The following statement lists the available data sources at CDA, and creates a Federated Data Source using the Data Agent File Transfer Service (referenced to RDA File Transfer Service), and a File Transfer Service. Notice the DSN names FT_NFS_CDA and RDA_FT; both are available at CDA. FT_NFS_CDA is the File Transfer Service for the CDA schema, and RDA_FT is the Data Agent File Transfer Service referenced to the RDA File Transfer Service.   \Code:   cda=sas-data-agent-server-colocated sas-viya dagentsrv servers set-default --data-agent $cda sas-viya dagentsrv data-sources list # Create a Federated Data Source service sas-viya dagentsrv dsns create federated --name X_FT_NFS_CDA_AND_RDA_FT --using FT_NFS_CDA,RDA_FT   Log:   [cloud-user@pdcesx41350 ~]$ sas-viya dagentsrv data-sources list Name ID Type ADMIN ADMIN DSN BASE BASE DSN BASE BASE__DATA_SERVICE__ Service FT_NFS_CDA FT_NFS_CDA DSN FT_NFS_CDA FT_NFS_CDA__DATA_SERVICE__ Service RDA_FT RDA_FT DSN RDA_FT RDA_FT__DATA_SERVICE__ Service [cloud-user@pdcesx41350 ~]$ [cloud-user@pdcesx41350 ~]$ sas-viya dagentsrv dsns create federated --name X_FT_NFS_CDA_AND_RDA_FT --using FT_NFS_CDA,RDA_FT The data source name was successfully created. [cloud-user@pdcesx41350 ~]$     List and test the Federated Data Source.   The following statement lists the available data sources at CDA, including the Federated Data Source, and test the Federated Data Source for file access.   Code:   cda=sas-data-agent-server-colocated sas-viya dagentsrv servers set-default --data-agent $cda sas-viya dagentsrv data-sources list sas-viya dagentsrv data-sources test --name X_FT_NFS_CDA_AND_RDA_FT   Log:   [cloud-user@pdcesx41350 ~]$ [cloud-user@pdcesx41350 ~]$ sas-viya dagentsrv data-sources list Name ID Type ADMIN ADMIN DSN BASE BASE DSN BASE BASE__DATA_SERVICE__ Service FT_NFS_CDA FT_NFS_CDA DSN FT_NFS_CDA FT_NFS_CDA__DATA_SERVICE__ Service RDA_FT RDA_FT DSN RDA_FT RDA_FT__DATA_SERVICE__ Service X_FT_NFS_CDA_AND_RDA_FT X_FT_NFS_CDA_AND_RDA_FT DSN [cloud-user@pdcesx41350 ~]$ [cloud-user@pdcesx41350 ~]$ sas-viya dagentsrv data-sources test --name X_FT_NFS_CDA_AND_RDA_FT Successfully connected to data source X_FT_NFS_CDA_AND_RDA_FT. [cloud-user@pdcesx41350 ~]$     List files from the Federated Data Source.   You can access the files from both the underlying data sources (RDA File Transfer Service and CDA File Transfer Service). You can list the files from RDA and CDA data sources by specifying the corresponding catalog and schema in the CLI statement.   Code:   cda=sas-data-agent-server-colocated sas-viya dagentsrv servers set-default --data-agent $cda ##list files/tables from RDA sas-viya dagentsrv data-sources tables list --data-source X_FT_NFS_CDA_AND_RDA_FT --catalog FT_NFS_RDA_CAT --schema FT_NFS_RDA_SCHEMA1 #list files/tables from CDA sas-viya dagentsrv data-sources tables list --data-source X_FT_NFS_CDA_AND_RDA_FT --catalog FT_NFS_CDA_CAT --schema FT_NFS_CDA_SCHEMA1   Log:   [cloud-user@pdcesx41350 ~]$ [cloud-user@pdcesx41350 ~]$ sas-viya dagentsrv data-sources tables list --data-source X_FT_NFS_CDA_AND_RDA_FT --catalog FT_NFS_RDA_CAT --schema FT_NFS_RDA_SCHEMA1 Name Type Native Catalog books_RDA.txt TABLE cars_source_RDA.csv TABLE prdsale_source_RDA.sas7bdat TABLE [cloud-user@pdcesx41350 ~]$ [cloud-user@pdcesx41350 ~]$ [cloud-user@pdcesx41350 ~]$ sas-viya dagentsrv data-sources tables list --data-source X_FT_NFS_CDA_AND_RDA_FT --catalog FT_NFS_CDA_CAT --schema FT_NFS_CDA_SCHEMA1 Name Type Native Catalog books_CDA.txt TABLE cars_source_CDA.csv TABLE prdsale_source_CDA.sas7bdat TABLE [cloud-user@pdcesx41350 ~]$     Copy data files between the CDA and RDA storage using the Federated Data Source.   Now that both RDA and CDA data files are available from CDA, it can be copied to each other. The data file can be copied using “dagetsrv ds copy” or “dagetsrv sql “ CLI statement. The data files is accessed specifying the corresponding catalog and schema in the CLI statement. The following CLI statement copies the data files from the CDA to RDA and RDA to CDA using “ds copy” and SQL statement. The “select * from SourceFile” in the SQL statement does not actually execute a query to the data file; it simply interprets that the named file needs to be read and copied over to the target site.   Code:   cda=sas-data-agent-server-colocated sas-viya dagentsrv servers set-default --data-agent $cda ## Copy files from CDA to RDA sas-viya dagentsrv ds copy --dsn X_FT_NFS_CDA_AND_RDA_FT --from FT_NFS_CDA_CAT.FT_NFS_CDA_SCHEMA1.\"prdsale_source_CDA.sas7bdat\" --to FT_NFS_RDA_CAT.FT_NFS_RDA_SCHEMA1.\"prdsale_source_CDA.sas7bdat\" sas-viya dagentsrv sql --sql "CREATE TABLE FT_NFS_RDA_CAT.FT_NFS_RDA_SCHEMA1.\"books_CDA.txt\" AS SELECT * FROM FT_NFS_CDA_CAT.FT_NFS_CDA_SCHEMA1.\"books_CDA.txt\" " --dsn X_FT_NFS_CDA_AND_RDA_FT sas-viya dagentsrv sql --sql "CREATE TABLE FT_NFS_RDA_CAT.FT_NFS_RDA_SCHEMA1.\"cars_source_CDA.csv\" AS SELECT * FROM FT_NFS_CDA_CAT.FT_NFS_CDA_SCHEMA1.\"cars_source_CDA.csv\" " --dsn X_FT_NFS_CDA_AND_RDA_FT ## Copy files from RDA to CDA sas-viya dagentsrv ds copy --dsn X_FT_NFS_CDA_AND_RDA_FT --from FT_NFS_RDA_CAT.FT_NFS_RDA_SCHEMA1.\"prdsale_source_RDA.sas7bdat\" --to FT_NFS_CDA_CAT.FT_NFS_CDA_SCHEMA1.\"prdsale_source_RDA.sas7bdat\" sas-viya dagentsrv sql --sql "CREATE TABLE FT_NFS_CDA_CAT.FT_NFS_CDA_SCHEMA1.\"books_RDA.txt\" AS SELECT * FROM FT_NFS_RDA_CAT.FT_NFS_RDA_SCHEMA1.\"books_RDA.txt\" " --dsn X_FT_NFS_CDA_AND_RDA_FT sas-viya dagentsrv sql --sql "CREATE TABLE FT_NFS_CDA_CAT.FT_NFS_CDA_SCHEMA1.\"cars_source_RDA.csv\" AS SELECT * FROM FT_NFS_RDA_CAT.FT_NFS_RDA_SCHEMA1.\"cars_source_RDA.csv\" " --dsn X_FT_NFS_CDA_AND_RDA_FT   Log:   ## Copy files from CDA to RDA [cloud-user@pdcesx41524 ~]$ [cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv ds copy --dsn X_FT_NFS_CDA_AND_RDA_FT --from FT_NFS_CDA_CAT.FT_NFS_CDA_SCHEMA1.\"prdsale_source_CDA.sas7bdat\" --to FT_NFS_RDA_CAT.FT_NFS_RDA_SCHEMA1.\"prdsale_source_CDA.sas7bdat\" The table was successfully copied. [cloud-user@pdcesx41524 ~]$ [cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv sql --sql "CREATE TABLE FT_NFS_RDA_CAT.FT_NFS_RDA_SCHEMA1.\"books_CDA.txt\" AS SELECT * FROM FT_NFS_CDA_CAT.FT_NFS_CDA_SCHEMA1.\"books_CDA.txt\" " --dsn X_FT_NFS_CDA_AND_RDA_FT SQL statement successfully executed. [cloud-user@pdcesx41524 ~]$ [cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv sql --sql "CREATE TABLE FT_NFS_RDA_CAT.FT_NFS_RDA_SCHEMA1.\"cars_source_CDA.csv\" AS SELECT * FROM FT_NFS_CDA_CAT.FT_NFS_CDA_SCHEMA1.\"cars_source_CDA.csv\" " --dsn X_FT_NFS_CDA_AND_RDA_FT SQL statement successfully executed. [cloud-user@pdcesx41524 ~]$ ## Copy files from RDA to CDA [cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv ds copy --dsn X_FT_NFS_CDA_AND_RDA_FT --from FT_NFS_RDA_CAT.FT_NFS_RDA_SCHEMA1.\"prdsale_source_RDA.sas7bdat\" --to FT_NFS_CDA_CAT.FT_NFS_CDA_SCHEMA1.\"prdsale_source_RDA.sas7bdat\" The table was successfully copied. [cloud-user@pdcesx41524 ~]$ [cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv sql --sql "CREATE TABLE FT_NFS_CDA_CAT.FT_NFS_CDA_SCHEMA1.\"books_RDA.txt\" AS SELECT * FROM FT_NFS_RDA_CAT.FT_NFS_RDA_SCHEMA1.\"books_RDA.txt\" " --dsn X_FT_NFS_CDA_AND_RDA_FT SQL statement successfully executed. [cloud-user@pdcesx41524 ~]$ [cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv sql --sql "CREATE TABLE FT_NFS_CDA_CAT.FT_NFS_CDA_SCHEMA1.\"cars_source_RDA.csv\" AS SELECT * FROM FT_NFS_RDA_CAT.FT_NFS_RDA_SCHEMA1.\"cars_source_RDA.csv\" " --dsn X_FT_NFS_CDA_AND_RDA_FT SQL statement successfully executed. [cloud-user@pdcesx41524 ~]$     List the files from the RDA and CDA storage after the file copy operation.   You can list the files from RDA and CDA data sources by specifying the corresponding catalog and schema in the CLI statement.   Code:   cda=sas-data-agent-server-colocated sas-viya dagentsrv servers set-default --data-agent $cda ##list files/tables from RDA sas-viya dagentsrv data-sources tables list --data-source X_FT_NFS_CDA_AND_RDA_FT --catalog FT_NFS_RDA_CAT --schema FT_NFS_RDA_SCHEMA1 #list files/tables from CDA sas-viya dagentsrv data-sources tables list --data-source X_FT_NFS_CDA_AND_RDA_FT --catalog FT_NFS_CDA_CAT --schema FT_NFS_CDA_SCHEMA1   Log:   cloud-user@pdcesx41524 ~]$ #list table from CDA [cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv data-sources tables list --data-source X_FT_NFS_CDA_AND_RDA_FT --catalog FT_NFS_CDA_CAT --schema FT_NFS_CDA_SCHEMA1 Name Type Native Catalog books_CDA.txt TABLE books_RDA.txt TABLE cars_source_CDA.csv TABLE cars_source_RDA.csv TABLE prdsale_source_CDA.sas7bdat TABLE prdsale_source_RDA.sas7bdat TABLE prdsale_source_RDA1.sas7bdat TABLE [cloud-user@pdcesx41524 ~]$ [cloud-user@pdcesx41524 ~]$ #list table from RDA [cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv data-sources tables list --data-source X_FT_NFS_CDA_AND_RDA_FT --catalog FT_NFS_RDA_CAT --schema FT_NFS_RDA_SCHEMA1 Name Type Native Catalog books_CDA.txt TABLE books_RDA.txt TABLE cars_source_CDA.csv TABLE cars_source_RDA.csv TABLE prdsale_source_CDA.sas7bdat TABLE prdsale_source_RDA.sas7bdat TABLE [cloud-user@pdcesx41524 ~]$     Copy data files between CDA and RDA from the SAS Compute Server   With the Federated Data Source configured with File Transfer Service from both RDA and CDA environments, SAS users can now access and copy the data files between CDA and RDA storage.   The following SAS code describes the file transfer from RDA to CDA and vice versa. Notice, you can copy a variety of file types between the CDA and RDA, including a .json file.   Code:   %let Rcat=FT_NFS_RDA_CAT ; %let Rsch=FT_NFS_RDA_SCHEMA1 ; %let Ccat=FT_NFS_CDA_CAT ; %let Csch=FT_NFS_CDA_SCHEMA1 ; %let Xdsn=X_FT_NFS_CDA_AND_RDA_FT ; /* copy file from RDA to CDA */ PROC SQL; connect to CDE (dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn) ; EXECUTE( CREATE TABLE &Ccat..&Csch.."prdsale_source_RDA11.sas7bdat" AS SELECT * FROM &Rcat..&Rsch.."prdsale_source_RDA.sas7bdat") BY CDE; run;QUIT; /* copy file from RDA to CDA */ PROC SQL; connect to CDE (dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn) ; EXECUTE( CREATE TABLE &Ccat..&Csch.."books_RDA11.txt" AS SELECT * FROM &Rcat..&Rsch.."books_RDA.txt") BY CDE; run;QUIT; /* copy file from RDA to CDA */ PROC SQL; connect to CDE (dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn) ; EXECUTE( CREATE TABLE &Ccat..&Csch.."reviews_RDA11.json" AS SELECT * FROM &Rcat..&Rsch.."reviews_RDA.json") BY CDE; run;QUIT; /* copy file from CDA to RDA */ PROC SQL; connect to CDE (dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn) ; EXECUTE( CREATE TABLE &Rcat..&Rsch.."books_CDA11.txt" AS SELECT * FROM &Ccat..&Csch.."books_CDA.txt") BY CDE; run;QUIT; /* copy file from CDA to RDA */ PROC SQL; connect to CDE (dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn) ; EXECUTE( CREATE TABLE &Rcat..&Rsch.."cars_source_CDA11.csv" AS SELECT * FROM &Ccat..&Csch.."cars_source_CDA.csv") BY CDE; run;QUIT; /* copy file from CDA to RDA */ PROC SQL; connect to CDE (dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn) ; EXECUTE( CREATE TABLE &Rcat..&Rsch.."reviews_CDA11.json" AS SELECT * FROM &Ccat..&Csch.."reviews_CDA.json") BY CDE; run;QUIT;   Log:   80 %let Rcat=FT_NFS_RDA_CAT ; 81 %let Rsch=FT_NFS_RDA_SCHEMA1 ; 82 %let Ccat=FT_NFS_CDA_CAT ; 83 %let Csch=FT_NFS_CDA_SCHEMA1 ; 84 %let Xdsn=X_FT_NFS_CDA_AND_RDA_FT ; 85 86 87 /* copy file from RDA to CDA */ 88 PROC SQL; 89 connect to CDE (dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn) ; 90 EXECUTE( CREATE TABLE &Ccat..&Csch.."prdsale_source_RDA11.sas7bdat" AS SELECT * FROM 90 ! &Rcat..&Rsch.."prdsale_source_RDA.sas7bdat") BY CDE; 91 run;QUIT; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: PROCEDURE SQL used (Total process time): real time 1.73 seconds cpu time 0.17 seconds 92 93 /* copy file from RDA to CDA */ 94 PROC SQL; 95 connect to CDE (dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn) ; 96 EXECUTE( CREATE TABLE &Ccat..&Csch.."books_RDA11.txt" AS SELECT * FROM &Rcat..&Rsch.."books_RDA.txt") BY CDE; 97 run;QUIT; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: PROCEDURE SQL used (Total process time): real time 1.27 seconds cpu time 0.16 seconds 98 99 /* copy file from RDA to CDA */ 100 PROC SQL; 101 connect to CDE (dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn) ; 102 EXECUTE( CREATE TABLE &Ccat..&Csch.."reviews_RDA11.json" AS SELECT * FROM &Rcat..&Rsch.."reviews_RDA.json") BY CDE; 103 run;QUIT; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: PROCEDURE SQL used (Total process time): real time 1.22 seconds cpu time 0.13 seconds 104 105 106 /* copy file from CDA to RDA */ 107 PROC SQL; 108 connect to CDE (dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn) ; 109 EXECUTE( CREATE TABLE &Rcat..&Rsch.."books_CDA11.txt" AS SELECT * FROM &Ccat..&Csch.."books_CDA.txt") BY CDE; 110 run;QUIT; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: PROCEDURE SQL used (Total process time): real time 1.28 seconds cpu time 0.15 seconds 111 112 /* copy file from CDA to RDA */ 113 PROC SQL; 114 connect to CDE (dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn) ; 115 EXECUTE( CREATE TABLE &Rcat..&Rsch.."cars_source_CDA11.csv" AS SELECT * FROM &Ccat..&Csch.."cars_source_CDA.csv") BY CDE; 116 run;QUIT; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: PROCEDURE SQL used (Total process time): real time 1.25 seconds cpu time 0.14 seconds 117 118 /* copy file from CDA to RDA */ 119 PROC SQL; 120 connect to CDE (dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn) ; 121 EXECUTE( CREATE TABLE &Rcat..&Rsch.."reviews_CDA11.json" AS SELECT * FROM &Ccat..&Csch.."reviews_CDA.json") BY CDE; 122 run;QUIT; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: PROCEDURE SQL used (Total process time): real time 1.23 seconds cpu time 0.16 seconds     Verify the CDA and RDA data files from the SAS Compute Server   The following SAS code lists the files from CDA and RDA under the respective LIBNAME reference.   Code:   %let Rcat=FT_NFS_RDA_CAT ; %let Rsch=FT_NFS_RDA_SCHEMA1 ; %let Ccat=FT_NFS_CDA_CAT ; %let Csch=FT_NFS_CDA_SCHEMA1 ; %let Xdsn=X_FT_NFS_CDA_AND_RDA_FT ; LIBNAME cdefedC cde dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn catalog=&Ccat schema=&Csch preserve_tab_names=yes; LIBNAME cdefedR cde dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn catalog=&Rcat schema=&Rsch preserve_tab_names=yes;   Results:       Important Links:   Remote SAS Data Agent: Deployment and Administration Guide   Communities 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) 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) Configuring MS-SQL Server Data Source at Remote Data Agent (Cloud Data Exchange) Configuring CSV ODBC Data Source at Remote Data Agent (Cloud Data Exchange)     Find more articles from SAS Global Enablement and Learning here. ... View more

Have you come across this question: How to access XLSX data files from an on-premises data center to a cloud-hosted SAS Viya environment without making a copy to cloud storage?   If the XLSX files are stored on Network File System (NFS) in the on-premises data center, the Remote Data Agent can access them using an ODBC driver connection. With the SAS Viya Cloud Data Exchange (CDE), the CDE administrator can deploy and configure the Remote Data Agent with an ODBC driver on an on-premises server to access the CSV, XLSX data files.   In this post, I discuss the deployment and configuration of the XLSX ODBC driver data source at the Remote Data agent.   The XLSX ODBC driver is not included with the SAS Remote Data Agent deployment. You need to purchase and download the XLSX ODBC driver from a supported vendor. The CDE administrator needs to deploy the XLSX ODBC driver and prepare the odbc.ini with ODBC connection details. The ODBC driver and ODBC configuration file must be placed at a location mounted to the Remote Data Agent container. A SAS administrator can configure the ODBC data source at the Remote Data Agent server.   The Remote Data Agent is a containerized software that runs with Docker runtime. The CDE administrator can mount an NFS location with the ODBC driver, an odbc.ini file, and the XLSX data files while starting the Remote Data Agent container.   The following pics describe the local/NFS folder with the XLS ODBC driver and an odbc.ini file mounted to the Remote Data Agent container and access to the XLS Data file folder. The XLS ODBC driver can only read the XLSX file (no write).   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 XLSX ODBC data source configuration at the Remote Data Agent Server.     Deploy the XLSX ODBC driver at the Remote Data Agent server   The XLSX ODBC driver is not included with the SAS Remote Data Agent deployment. You need to purchase and download the XLSX ODBC driver from a supported vendor. In a test environment, I have downloaded the CDATA XLSX ODBC driver to deploy and configure at the Remote Data Agent server. You can use any other vendor’s Unix-supported XLSX ODBC driver.   The CDATA XLSX ODBC driver (ExcelODBCDriverforUnix.deb)can be downloaded from the site: https://www.cdata.com/drivers/excel/odbc/    Before you can deploy the XLS ODBC driver, you may have to deploy a few pre-requisite packages on the operating system.   Code:   sudo apt-get install libc6 libstdc++6 zlib1g libgcc1 -y   It may also require the Unix driver manager application.   Code:   sudo apt-get install unixodbc unixodbc-dev -y   The CDATA XLSX ODBC driver can be installed using following statement.   Code:   sudo dpkg -i ~/ExcelODBCDriverforUnix.deb   By default, the CDATA XLSX ODBC driver will be installed under /opt/cdata/cdata-odbc-driver-for-excel folder. It will require applying the purchased/trial license by using the following statement.   Code:   /opt/cdata/cdata-odbc-driver-for-excel/bin/install-license.sh     Prepare an odbc.ini file with XLSX ODBC connection details   The CDE connection to the XLSX file is an ODBC connection. An administrator must prepare an odbc.ini file with the path to the XLSX ODBC driver and the XLSX data file. The XLSX data file, odbc.ini, and XLSX ODBC driver path (mounted) must be reachable from the Remote Data Agent container. The folder and files containing the odbc.ini file should have read permission for user “sas” (uid 1001:1001). You may copy the sample odbc.ini and odbcinst.ini files from /etc folder to the SAS access-client subfolder and append/update an XLSX ODBC DSN entry. The following is an odbc.ini example; you can customize it before using it in your environment.   The value in XLSX URI contains a folder name containing a number of xlsx files with multiple sheets in it. Each tab from the xlsx file will be viewed as a separate table.   Code:   cp /etc/odbc* /viya-share/gelenv/config/access-clients/odbc tee -a /viya-share/gelenv/config/access-clients/odbc/odbc.ini > /dev/null << EOF [CDATA_XLS] Driver=/opt/cdata/cdata-odbc-driver-for-excel/lib/libexcelodbc.x64.so ConnectionType=Local URI=/viya-share/gelenv/data AuthScheme=None EOF     Test the XLSX ODBC Driver from the VM server   You can test the XLSX ODBC configuration from the virtual machine (RDA server) where you have installed the driver. It requires initialization of ODBC-related system variables before you can test it. By using the ‘isql’ utility you can test and verify the ODBC configuration and access the XLSX data files.   Code:   export ODBCHOME=/opt/cdata/cdata-odbc-driver-for-excel export ODBCINI=/viya-share/gelenv/config/access-clients/odbc/odbc.ini export ODBCINST=/viya-share/gelenv/config/access-clients/odbc/odbcinst.ini   isql CDATA_XLS -v help ; select * from "class4.xlsx".class1 limit 5 ; quit;   Log:   jumpuser@cargo-p11125-jump-vm:~$ jumpuser@arbor-p03072-jump-vm:~$isql CDATA_XLS -v +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> help +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CData | class1.xlsx | class1 | TABLE | Retrieve data from the "class1" sheet. | | CData | class1.xlsx | class2 | TABLE | Retrieve data from the "class2" sheet. | | CData | class1.xlsx | class3 | TABLE | Retrieve data from the "class3" sheet. | | CData | class4.xlsx | class1 | TABLE | Retrieve data from the "class1" sheet. | | CData | class4.xlsx | class2 | TABLE | Retrieve data from the "class2" sheet. | | CData | class4.xlsx | class3 | TABLE | Retrieve data from the "class3" sheet. | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ SQLRowCount returns -1 6 rows fetched SQL> SQL> select * from "class4.xlsx".class1 limit 5 ; +------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | RowId | Name | Sex | Age | Height | Weight | +------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 2 | Alfred | M | 14 | 69 | 112.5 | | 3 | Alice | F | 13 | 56.5 | 84 | | 4 | Barbara | F | 13 | 65.3 | 98 | | 5 | Carol | F | 14 | 62.8 | 102.5 | | 6 | Henry | M | 14 | 63.5 | 102.5 | +------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ SQLRowCount returns -1 5 rows fetched SQL> quit; jumpuser@cargo-p11125-jump-vm:~$     Update the da-vars.env and sas-access.properties with the XLSX ODBC driver environment variable   Before you can use the XLSX ODBC driver and create a data source at RDA, you need to update the da-vars.env and sas-access.properties file with ODBC-related environment variables. Users can include and update the respective access engine environment variables. The following variable is related to the XLSX ODBC client.   Code:   da-vars.env:   ## Update the LD_LIBRARY_PATH with the ODBC driver path. LD_LIBRARY_PATH=/usr/lib64;/opt/cdata/cdata-odbc-driver-for-excel/lib   sas-access.properties :   ## Update the ODBC variables with the ODBC client folder. ODBCHOME=/opt/cdata/cdata-odbc-driver-for-excel ODBCINI=/viya-share/gelenv/config/access-clients/odbc/odbc.ini ODBCINST=/viya-share/gelenv/config/access-clients/odbc/odbcinst.ini     Update the owner of the access-clients/odbc folder   The odbc.ini folder must be owned by the user SAS for the Remote Data Agent services to access the ODBC driver and data files.   sudo chown 1001:1001 -R /viya-share/gelenv/config/access-clients/odbc; ls -l /viya-share/gelenv/config/access-clients/odbc     Start the Remote Data Agent service with the NFS folder mounted   Start the RDA service with the local data folder (e.g. /viya-share/gelenv, and /opt/cdata) mounted to the RDA container. The mounted folder contains the XLSX ODBC driver, XLSX data file, and odbc.ini file.   Code:   ./container-manager start \ --license-path ${SASVIYA_LIC} \ --sasdata sasdata \ --vars da-vars.env \ --data sasdata/data \ --mount /viya-share/gelenv:/viya-share/gelenv,/opt/cdata:/opt/cdata \ --access-vars sas-access.properties \ sas-data-agent-server-remote     Install the CDATA XLSX ODBC license inside the RDA POD/Container   You may have to install the CDATA XLSX ODBC license inside the Remote Data Agent container. It depends on the ODBC vendor and how the license is distributed.   Code:   ## to Log in to RDA container docker exec -it sas-data-agent-server-remote bash   Code:   /opt/cdata/cdata-odbc-driver-for-excel/bin/install-license.sh     Log in and authenticate a user for sas-viya CLI   The SAS-Viya CLI is the user interface to manage and maintain the configurations at the SAS Cloud Data Exchange. An administrator must log in and authenticate a user for sas-viya CLI before creating data source connections.   Code:   source ~/sasviay_cli_env_vars.txt export GELLOW_NAMESPACE=gelenv export SAS_CLI_PROFILE=${GELLOW_NAMESPACE:-Default} ; export SSL_CERT_FILE=~/.certs/${GELLOW_NAMESPACE}_trustedcerts.pem ; sas-viya auth login --user $Uid --password $Pwd sas-viya profile set-output text ; sas-viya dagentsrv servers set-default --data-agent sas-data-agent-server-remote     Create a XLSX ODBC data source   The following statement creates an XLSX ODBC data source service with the ODBC DSN name defined in the odbc.ini   Code:   sas-viya dagentsrv data-services create odbc --name odbc_xls2 --driver odbc --dsn CDATA_XLS sas-viya dagentsrv services list   Log:   jumpuser@cargo-p11125-jump-vm:~/CDERemoteDA_Deploy$sas-viya dagentsrv data-services create odbc --name odbc_xls2 --driver odbc --dsn CDATA_XLS The data service was successfully created. jumpuser@cargo-p11125-jump-vm:~/CDERemoteDA_Deploy$ jumpuser@cargo-p11125-jump-vm:~/CDERemoteDA_Deploy$sas-viya dagentsrv services list Name Type Domain Version Options BASE base --- 2.6 --- __SERVER__ server --- 2.6 PURGE_CACHE=30;CASE_SENSITIVITY=(OBJECT=F;COLUMN=F);CACHE=(NAME=AS;TIMEOUT=300) odbc_csv2 odbc odbc_csv2 2.6 CONOPTS=(DRIVER=odbc;ODBC_DSN=CDATA_CSV);CASE_SENSITIVITY=(OBJECT=F;COLUMN=F) odbc_xls2 odbc odbc_xls2 2.6 CONOPTS=(DRIVER=odbc;ODBC_DSN=CDATA_XLS);CASE_SENSITIVITY=(OBJECT=F;COLUMN=F) jumpuser@cargo-p11125-jump-vm:~/CDERemoteDA_Deploy$     Test the XLSX ODBC data source   The following statement tests the XLSX ODBC data source service created in the previous step.   Code:   sas-viya dagentsrv data-sources test --name odbc_xls2   Log:   jumpuser@cargo-p11125-jump-vm:~/CDERemoteDA_Deploy$sas-viya dagentsrv data-sources test --name odbc_xls2 Successfully connected to data source odbc_xls2. jumpuser@cargo-p11125-jump-vm:~/CDERemoteDA_Deploy$     List the catalog, schemas, tables, and table data from the XLSX ODBC data source   With a successful test connection to an XLSX ODBC data source, a user can list the catalog, schemas, tables, and data (each xlsx file with multiple tabs) from each tab using the sas-viya CLI.   Code:   sas-viya dagentsrv ds catalogs list --data-source odbc_xls2 sas-viya dagentsrv ds schemas list --catalog odbc_xls2 --data-source odbc_xls2 sas-viya dagentsrv ds tables list --catalog odbc_xls2 --schema 'class1.xlsx' --data-source odbc_xls2 sas-viya dagentsrv sql --sql "select * from \"class1.xlsx\".class3 limit 5 " --dsn odbc_xls2   Log:   jumpuser@cargo-p11125-jump-vm:~/CDERemoteDA_Deploy$sas-viya dagentsrv ds catalogs list --data-source odbc_xls2 Name Driver Description odbc_xls2 ODBC jumpuser@arbor-p03072-jump-vm:~/CDERemoteDA_Deploy$sas-viya dagentsrv ds schemas list --catalog odbc_xls2 --data-source odbc_xls2 Name class1.xlsx class4.xlsx jumpuser@cargo-p11125-jump-vm:~/CDERemoteDA_Deploy$sas-viya dagentsrv ds tables list --catalog odbc_xls2 --schema 'class1.xlsx' --data-source odbc_xls2 Name Type Native Catalog class1 TABLE CData class2 TABLE CData class3 TABLE CData jumpuser@cargo-p11125-jump-vm:~/CDERemoteDA_Deploy$ jumpuser@arbor-p11125-jump-vm:~/CDERemoteDA_Deploy$sas-viya dagentsrv sql --sql "select * from \"class1.xlsx\".class3 limit 5 " --dsn odbc_xls2 Age Height Name RowId Sex Weight === ====== ==== ===== === ====== 14 69 Alfred 2 M 112.5 13 56.5 Alice 3 F 84 13 65.3 Barbara 4 F 98 14 62.8 Carol 5 F 102.5 14 63.5 Henry 6 M 102.5 jumpuser@arbor-p11125-jump-vm:~/CDERemoteDA_Deploy$   Note: The first row of the .xlsx tab appears as column names. If the first row is a data row, it will be considered as the column name.     Access the XLSX ODBC Data Source from SAS Compute Server via Remote Data Agent   With the XLSX ODBC data source configured at the Remote Data Agent, users can access on-premise XLSX data from the SAS Compute Server using the CDE LIBNAME statement. The following code describes access to an XLSX data file sheet via a Remote Data Agent data source. The code will display the first 10 rows from the class4.xlsx file and the sheet class3.   Code:   LIBNAME cdexls2 cde dataagentname="sas-data-agent-server-remote" dsn=odbc_xls2 preserve_tab_names=yes; Proc SQL outobs=10; select * from cdexls2.class3(schema='class4.xlsx') ; run;quit;   Log:   79 80 LIBNAME cdexls2 cde dataagentname="sas-data-agent-server-remote" 81 dsn=odbc_xls2 preserve_tab_names=yes; NOTE: Libref CDEXLS2 was successfully assigned as follows: Engine: CDE Physical Name: odbc_xls2 83 84 Proc SQL outobs=10; 85 select * from cdexls2.class3(schema='class4.xlsx') ; WARNING: Statement terminated early due to OUTOBS=10 option. 86 run;quit; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: The PROCEDURE SQL printed page 4.     Access the XLSX ODBC Data Source from CAS via Remote Data Agent   With the XLSX ODBC data source configured at the Remote Data Agent, users can access on-premise XLSX data from the CAS using the CDE data connector. The following code describes the access to an XLSX data file and sheet via a Remote Data Agent data source using CDE CASLIB. The code will load CAS table from the class4.xlsx file and the sheet class3.   Code:   CAS mySession SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); caslib cdexls2 datasource=( srctype="clouddex", dataAgentName="sas-data-agent-server-remote", catalog="odbc_xls2", conopts="dsn=odbc_xls2" ) libref=cdexls2; proc casutil incaslib="cdexls2"; list files ; run;quit; /* CAS load from CDE RDA XLS ODBC table */ proc casutil incaslib="cdexls2" outcaslib="cdexls2"; load casdata="class3" casout="class_xls" datasourceoptions=(schema="class4.xlsx" ) replace ; list tables ; run;quit; cas mysession terminate;   Log:   83 84 caslib cdexls2 datasource=( 85 srctype="clouddex", 86 dataAgentName="sas-data-agent-server-remote", 87 catalog="odbc_xls2", 88 conopts="dsn=odbc_xls2" 89 ) libref=cdexls2; NOTE: Executing action 'table.addCaslib'. NOTE: 'CDEXLS2' is now the active caslib. NOTE: Cloud Analytic Services added the caslib 'CDEXLS2'. NOTE: Action 'table.addCaslib' used (Total process time): NOTE: real time 0.011001 seconds 94 95 /* CAS load from CDE RDA XLS ODBC table */ 96 proc casutil incaslib="cdexls2" outcaslib="cdexls2"; NOTE: The UUID '5575f964-b5c2-9a49-9d76-f339c75e0320' is connected using session MYSESSION. 97 load casdata="class3" casout="class_xls" 98 datasourceoptions=(schema="class4.xlsx" ) replace ; NOTE: Executing action 'table.loadTable'. NOTE: Connecting using the OAuth token for CAS user 'geldmui@gelenable.sas.com'. WARNING: The specified domain, "odbc_xls2", is unknown. WARNING: Using server default session timeout of 3600 WARNING: Function completed successfully, possibly with a non-fatal error (warning). WARNING: Function completed successfully, possibly with a non-fatal error (warning). NOTE: Cloud Analytic Services made the external data from class3 available as table CLASS_XLS in caslib cdexls2. NOTE: Action 'table.loadTable' used (Total process time):   Many thanks to Brian Hess for sharing CDE expertise and help on this post.   Important Links:   Remote SAS Data Agent: Deployment and Administration Guide   Communities 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) 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) Configuring MS-SQL Server Data Source at Remote Data Agent (Cloud Data Exchange) Configuring CSV ODBC Data Source at Remote Data Agent (Cloud Data Exchange)     Find more articles from SAS Global Enablement and Learning here. ... View more

Often, I have seen a question from the field, how to access CSV, XLS data files from an on-premise data center to a cloud-hosted SAS Viya environment without making a copy to cloud storage?   The Remote Data Agent can access the on-premise CSV, XLS data files using an ODBC driver connection. With the SAS Viya Cloud Data Exchange (CDE), the CDE administrator can deploy and configure the Remote Data Agent with an ODBC driver on an on-premise server to access the CSV, XLS data files.   In this post, I discuss the deployment and configuration of the CSV ODBC driver data source at the Remote Data agent.   The CSV ODBC driver is not included with the SAS Remote Data Agent deployment. You need to purchase and download the CSV ODBC driver from a supported vendor. The CDE administrator needs to deploy the CSV ODBC driver and prepare the odbc.ini with ODBC connection details. The ODBC driver and ODBC configuration file must be placed at a location mounted to the Remote Data Agent container. A SAS administrator can configure the ODBC data source at the Remote Data Agent server.   The Remote Data Agent is a containerized software that runs with docker runtime. The CDE administrator can mount an NFS location with the ODBC driver and an odbc.ini file while starting the Remote Data Agent container. The CSV ODBC driver can only read the CSV file (no write).   The following pics describe the local/NFS folder with the CSV ODBC driver and an odbc.ini file mounted to the Remote Data Agent container and access to the CSV Data file folder.     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 CSV ODBC data source configuration at the Remote Data Agent Server.     Deploy the CSV ODBC driver at the Remote Data Agent server   The CSV ODBC driver is not included with the SAS Remote Data Agent deployment. You need to purchase and download the CSV ODBC driver from a supported vendor. In a test environment, I have downloaded the CDATA CSV ODBC driver to deploy and configure at the Remote Data Agent server. You can use any other vendor’s CSV ODBC driver.   The CDATA CSV ODBC driver (CSVODBCDriverforUnix.deb)can be downloaded from the site: https://www.cdata.com/drivers/csv/odbc/    Before you can deploy the CSV ODBC driver, you may have to deploy a few dependencies packages at operating system.   Code:   sudo apt-get install libc6 libstdc++6 zlib1g libgcc1   It may also require the Unix driver manager application.   Code:   sudo apt-get install unixodbc unixodbc-dev   The CDATA CSV ODBC driver can be installed using following statement.   Code:   sudo dpkg -i ~/CSVODBCDriverforUnix.deb   By default, the CDATA CSV ODBC driver will installed under /opt/cdata/cdata-odbc-driver-for-csv folder. It will require applying the purchased/trial license by using following statement.   Code:   /opt/cdata/cdata-odbc-driver-for-csv/bin/install-license.sh     Prepare an odbc.ini file with CVS ODBC connection details   The CDE connection to the CSV file is an ODBC connection. An administrator must prepare an odbc.ini file with the path to the CSV ODBC driver and the CSV data file. The CSV data file, odbc.ini, and CSV ODBC driver path (mounted) must be reachable from the Remote Data Agent container. The folder and files containing the odbc.ini file should have read permission for user “sas” (uid 1001:1001). You may copy the sample odbc.ini and odbcinst.ini files from /etc folder to the SAS access-client subfolder and append/update a CSV ODBC DSN entry. The following is an odbc.ini example; you can customize it before using it in your environment.The URI= variable represents the location for the CSV data files.   Code:   cp /etc/odbc* /viya-share/gelenv/config/access-clients/odbc tee -a /viya-share/gelenv/config/access-clients/odbc/odbc.ini > /dev/null << EOF [CDATA_CSV] Driver=/opt/cdata/cdata-odbc-driver-for-csv/lib/libcsvodbc.x64.so ConnectionType=Local URI=file:///viya-share/gelenv/data AuthScheme=None EOF sudo chown 1001:1001 -R /viya-share/gelenv/config/access-clients/odbc;     Test the CSV ODBC Driver from the VM server   You can test the CSV ODBC configuration from the virtual machine (RDA server) where you have installed the driver. It requires initialization of ODBC-related system variables before you can test it. By using the ‘isql’ utility you can test and verify the ODBC configuration and access the CSV data files.   Code:   export ODBCHOME=/opt/cdata/cdata-odbc-driver-for-csv export ODBCINI=/viya-share/gelenv/config/access-clients/odbc/odbc.ini export ODBCINST=/viya-share/gelenv/config/access-clients/odbc/odbcinst.ini     isql CDATA_CSV -v help ; select * from "class.csv" limit 5 ; quit;   Log:   jumpuser@cargo-p11125-jump-vm:~$isql CDATA_CSV -v +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> SQL> help ; +----------------------------------------------+ | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | +------------------------------------------------------------------------------------+ | CData | CSV | cars_source.csv | TABLE | | | CData | CSV | class.csv | TABLE | | | CData | CSV | flight_reporting.csv | TABLE | | +------------------------------------------------------------------------------------+ SQLRowCount returns -1 3 rows fetched SQL> SQL> select * from "class.csv" limit 5 ; +-----------------+------------+-------------------------+-------------------------+ | Name | Sex | Age | Height | Weight | +----------------------------------------------------------------------------------------------------------------+------------+-------------------------+-------------------------+ | Alfred | M | 14 | 69 | 112.5 | | Alice | F | 13 | 56.5 | 84 | | Barbara | F | 13 | 65.3 | 98 | | Carol | F | 14 | 62.8 | 102.5 | | Henry | M | 14 | 63.5 | 102.5 | +----------------+------------+-------------------------+-------------------------+ SQLRowCount returns -1 5 rows fetched SQL> quit jumpuser@cargo-p11125-jump-vm:~$   Update the da-vars.env and sas-access.properties with the CSV ODBC driver environment variable   Before you can use the CSV ODBC driver and create a data source at RDA, you need to update the da-vars.env and sas-access.properties file with ODBC-related environment variables. Users can include and update the respective access engine environment variables. The following variable is related to the CSV ODBC client.   Code:   da-vars.env:   ## Update the LD_LIBRARY_PATH with the ODBC driver path. LD_LIBRARY_PATH=/usr/lib64;/opt/cdata/cdata-odbc-driver-for-csv/lib       sas-access.properties :   ## Update the ODBC variables with the ODBC client folder. ODBCHOME=/opt/cdata/cdata-odbc-driver-for-csv ODBCINI=/viya-share/gelenv/config/access-clients/odbc/odbc.ini ODBCINST=/viya-share/gelenv/config/access-clients/odbc/odbcinst.ini     Start the Remote Data Agent service with the CSV ODBC driver and odbc.ini location mounted   Start the RDA service with the local data folder (e.g. /viya-share/gelenv, and /opt/cdata) mounted to the RDA container. The mounted folder contains the CSV ODBC driver, SCV data files, and odbc.ini file.   Code:   ./container-manager start \ --license-path ${SASVIYA_LIC} \ --sasdata sasdata \ --vars da-vars.env \ --data sasdata/data \ --mount /viya-share/gelenv:/viya-share/gelenv,/opt/cdata:/opt/cdata \ --access-vars sas-access.properties \ sas-data-agent-server-remote     Install the CDATA CSV ODBC license inside the RDA POD/Container   You may have to install the CDATA CSV ODBC license inside the Remote Data Agent container. It depends on the ODBC vendor and how the license is distributed.   Code:   ## to Log in to RDA container docker exec -it sas-data-agent-server-remote bash   Code:   /opt/cdata/cdata-odbc-driver-for-csv/bin/install-license.sh     Log in and authenticate a user for sas-viya CLI   The SAS-Viya CLI is the user interface to manage and maintain the configurations at the SAS Cloud Data Exchange. An administrator must log in and authenticate a user for sas-viya CLI before creating data source connections.   Code:   source ~/sasviay_cli_env_vars.txt export GELLOW_NAMESPACE=gelenv export SAS_CLI_PROFILE=${GELLOW_NAMESPACE:-Default} ; export SSL_CERT_FILE=~/.certs/${GELLOW_NAMESPACE}_trustedcerts.pem ; sas-viya auth login --user $Uid --password $Pwd sas-viya profile set-output text ; sas-viya dagentsrv servers set-default --data-agent sas-data-agent-server-remote   Create a CSV ODBC data source   The following statement creates a CSV ODBC data source service with the ODBC DSN name defined in the odbc.ini.   Code:   sas-viya dagentsrv data-services create odbc --name odbc_csv2 --driver odbc --dsn CDATA_CSV sas-viya dagentsrv services list   Log:   jumpuser@cargo-p11125-jump-vm:~/CDERemoteDA_Deploy$sas-viya dagentsrv data-services create odbc --name odbc_csv2 --driver odbc --dsn CDATA_CSV The data service was successfully created. jumpuser@cargo-p11125-jump-vm:~/CDERemoteDA_Deploy$ jumpuser@cargo-p11125-jump-vm:~/CDERemoteDA_Deploy$sas-viya dagentsrv services list Name Type Domain Version Options BASE base --- 2.6 --- __SERVER__ server --- 2.6 PURGE_CACHE=30;CASE_SENSITIVITY=(OBJECT=F;COLUMN=F);CACHE=(NAME=AS;TIMEOUT=300) odbc_csv2 odbc odbc_csv2 2.6 CONOPTS=(DRIVER=odbc;ODBC_DSN=CDATA_CSV);CASE_SENSITIVITY=(OBJECT=F;COLUMN=F) jumpuser@cargo-p11125-jump-vm:~/CDERemoteDA_Deploy$     Test the CSV ODBC data source   The following statement tests the CSV ODBC data source service created in the previous step.   Code:   sas-viya dagentsrv data-sources test --name odbc_csv2   Log:   jumpuser@cargo-p11125-jump-vm:~/CDERemoteDA_Deploy$sas-viya dagentsrv data-sources test --name odbc_csv2 Successfully connected to data source odbc_csv2. jumpuser@cargo-p11125-jump-vm:~/CDERemoteDA_Deploy$     List the table and data from the CSV data file folder using the CSV ODBC data source   With a successful test connection to a CSV ODBC data source, a user can list the data table and data from the CSV data files using the sas-viya CLI.   Code:   sas-viya dagentsrv ds tables list --catalog odbc_csv2 --schema CSV --data-source odbc_csv2 sas-viya dagentsrv sql --sql "select * from \"class.CSV\" limit 5 " --dsn odbc_csv2   Log:   jumpuser@cargo-p11125-jump-vm:~/CDERemoteDA_Deploy$sas-viya dagentsrv ds tables list --catalog odbc_csv2 --schema CSV --data-source odbc_csv2 Name Type Native Catalog cars_source.csv TABLE CData class.csv TABLE CData flight_reporting.csv TABLE CData jumpuser@cargo-p11125-jump-vm:~/CDERemoteDA_Deploy$ jumpuser@cargo-p11125-jump-vm:~/CDERemoteDA_Deploy$sas-viya dagentsrv sql --sql "select * from \"class.CSV\" limit 5 " --dsn odbc_csv2 Age Height Name Sex Weight === ====== ==== === ====== 14 69 Alfred M 112.5 13 56.5 Alice F 84 13 65.3 Barbara F 98 14 62.8 Carol F 102.5 14 63.5 Henry M 102.5 jumpuser@cargo-p11125-jump-vm:~/CDERemoteDA_Deploy$   Note: The first row of the .csv file appears as column names. If the first row is a data row, it will show up in the column name.     Access the CSV ODBC Data Source from SAS Compute Server via Remote Data Agent   With the CSV ODBC data source configured at the Remote Data Agent, users can access on-premise CSV data files from the SAS Compute Server using the CDE LIBNAME statement. The following code describes the access to a CSV data file via a Remote Data Agent data source.   Code:   LIBNAME cdecsv2 cde dataagentname="sas-data-agent-server-remote" dsn=odbc_csv2 preserve_tab_names=yes; Proc SQL outobs=10; select * from cdecsv2.'class.csv'n ; run;quit;   Log:   80 81 LIBNAME cdecsv2 cde dataagentname="sas-data-agent-server-remote" 82 dsn=odbc_csv2 preserve_tab_names=yes; NOTE: Libref CDECSV2 was successfully assigned as follows: Engine: CDE Physical Name: odbc_csv2 83 84 85 Proc SQL outobs=10; 86 select * from cdecsv2.'class.csv'n ; WARNING: Statement terminated early due to OUTOBS=10 option. 87 run;quit; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: The PROCEDURE SQL printed page 4. NOTE: PROCEDURE SQL used (Total process time): real time 0.14 seconds cpu time 0.04 seconds 88     Access the CSV ODBC Data Source from CAS via Remote Data Agent   With the CSV ODBC data source configured at the Remote Data Agent, users can access on-premise CSV data files from the CAS using the CDE data connector. The following code describes the access to a CSV data file via a Remote Data Agent data source using CDE CASLIB.   Code:   CAS mySession SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); caslib cdecsv2 datasource=( srctype="clouddex", dataAgentName="sas-data-agent-server-remote", catalog="odbc_csv2", schema="CSV", conopts="dsn=odbc_csv2" ) libref=cdecsv2; proc casutil incaslib="cdecsv2"; list files ; run; quit; /* CAS load from CDE RDA CSV ODBC table */ proc casutil incaslib="cdecsv2" outcaslib="cdecsv2"; load casdata="class.csv" casout="class_csv" replace ; list tables ; run; quit; cas mysession terminate;   Log:   82 83 caslib cdecsv2 datasource=( 84 srctype="clouddex", 85 dataAgentName="sas-data-agent-server-remote", 86 schema="CSV", 87 catalog="odbc_csv2", 88 conopts="dsn=odbc_csv2" 89 ) libref=cdecsv2; NOTE: Executing action 'table.addCaslib'. NOTE: 'CDECSV2' is now the active caslib. NOTE: Cloud Analytic Services added the caslib 'CDECSV2'. NOTE: Action 'table.addCaslib' used (Total process time): NOTE: real time 0.011860 seconds NOTE: cpu time 0.023530 seconds (198.40%) NOTE: total nodes 3 (12 cores) NOTE: total memory 94.03G NOTE: memory 1.72M (0.00%) NOTE: CASLIB CDECSV2 for session MYSESSION will be mapped to SAS Library CDECSV2. NOTE: Action to ADD caslib CDECSV2 completed for session MYSESSION. 90 95 96 /* CAS load from CDE RDA CSV ODBC table */ 97 proc casutil incaslib="cdecsv2" outcaslib="cdecsv2"; NOTE: The UUID 'ff28b734-a944-cc46-ac77-981e0f1951eb' is connected using session MYSESSION. 98 load casdata="class.csv" casout="class_csv" replace ; NOTE: Executing action 'table.loadTable'. NOTE: Connecting using the OAuth token for CAS user 'geldmui@gelenable.sas.com'. WARNING: The specified domain, "odbc_csv2", is unknown. WARNING: Using server default session timeout of 3600 NOTE: Cloud Analytic Services made the external data from class.csv available as table CLASS_CSV in caslib cdecsv2. NOTE: Action 'table.loadTable' used (Total process time): NOTE: real time 0.729696 seconds   Many thanks to Brian Hess for sharing the expert knowledge and help on this post.   Important Links:   Remote SAS Data Agent: Deployment and Administration Guide   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) 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) Configuring MS-SQL Server Data Source at Remote Data Agent (Cloud Data Exchange)     Find more articles from SAS Global Enablement and Learning here. ... View more

Encryption is a process that transforms the data into an unreadable format using a secret key and is readable only to those who have the key to decrypt it. Encryption provides security to datasets written on disk. The SAS Viya platform supports encryption for data at rest on disk storage. The SAS Viya platform mainly uses two methods to encrypt data at rest. One, programmatically using the CASLIB and SAS data steps. Second, an encrypted disk storage class provided by the cloud vendors is used to define the Persistence Volume and data storage. The CAS server supports encryption for .sashdata data files in a PATH and DNFS CASLIB. The SAS Compute Server supports encryption for .sas7bdat datafiles in a PATH LIBNAME.   This post is about programmatically encrypting data at rest using CASLIB and SAS data steps.   The SAS Viya platform uses an Advanced Encryption Standard (AES/AES2) algorithm with 256-bit key to encrypt data at rest. Encryption requires approximately the same amount of CPU resources as compression. The SAS system decrypts the data as it reads from the disk, but is not decrypted when read at the operating system level or by an external program.   Encryption under CAS Server   The CAS server supports encryption for .sashdata datafile at rest in a PATH/DNFS CASLIB. When creating a PATH/DNFS CASLIB, the SAS Viya administrator can enable it to encrypt the data while writing to disk. The encryption is applied to source data files in the PATH/DNFS CASLIB. The PATH/DNFS CASLIB can use an encryption domain or encryption password to encrypt the data files.   The encryption domain is configured with an encryption key and a list of SAS Viya identity users. When the SAS Administrator creates a CASLIB with the Encryption domain and grants read/write permission to a SAS identity user, this does not enable the user to access the encrypted data. The identity user must be part of the encryption domain with the same encryption key, along with read/write permission on CASLIB to read and write encrypted data files.   When a SAS Viya user imports a data file (SAS tables, .csv, .txt) into a PATH/DNFS CASLIB, a .sashdat file is created in the CASLIB path location. If the CASLIB is configured with an encryption domain or password, the .sashdat files at rest will be encrypted.   The following screenshot describes the creation of an Encryption domain with an encryption key and a set of SAS Viya Identity users at the SAS Environment Manager Application. The SAS Viya CLI can also be used to create and configure the encryption domain. When the SAS Viya administrator creates the encryption domain, they can assign a list of identity users and an encryption key. In this case same encryption key becomes the encryption key for each user. It’s a useful way to configure a group of SAS Viya users with same encryption key to share the encrypted data files.   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 SAS Viya administrator can also set up a separate encryption key for a user by updating the credentials. In that case, the encrypted data file created by the user (user1) using the encryption domain can only be read by same user (user1). Other users in the same encryption domain cannot read the data files created by user1 since they have a different encryption key.     The SAS Viya administrator can also add a new user to the encryption domain with same encryption key or a different key. In case a user has a different encryption key, the encrypted data file created by the user (user2) using the encryption domain can only be read by the same user (user2). It‘s a good practice to create an encryption domain with the same encryption key for each identity user in it. As per requirement SAS Viya administrator can create multiple encryption domains with set of identity users. Having same encryption key in a domain helps to share the data file within a set of identity users.     The following code describes the usage of the encryption domain in a PATH CASLIB to create encrypted data files. For example, the code can be executed by SAS Viya identity users “Viya Administrator” and “Test user1”. These users are part of the encryption domain "SalesEncryption" and can only use it in a CASLIB to read and write the encrypted data files. If a user is not part of the encryption domain, he/she cannot use it to read the data files even though they have read and write permission on CASLIB.   Code:   CAS mySession SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); CASLIB enclib DATASOURCE=(SRCTYPE="PATH" ENCRYPTIONDOMAIN="SalesEncryption" ) path="/mnt/viya-share/data/" ; /* Save a CAS table into an encrypted .sashdat file. */ proc casutil incaslib="enclib" outcaslib="enclib"; load data=sashelp.cars casout="cars" replace; save casdata="cars" casout="cars_encrpt" replace; list files; quit; /* Load CAS from an encrypted data file saved by CAS */ proc casutil incaslib="enclib" outcaslib="enclib"; load casdata="cars_encrpt.sashdat" casout="cars_hdat" ; list tables; quit; CAS mySession TERMINATE;   Log extract:   ………… …….. NOTE: SASHELP.CARS was successfully added to the "ENCLIB" caslib as "CARS". 88 save casdata="cars" casout="cars_encrpt" replace; NOTE: Executing action 'table.save'. NOTE: Cloud Analytic Services saved the file cars_encrpt.sashdat in caslib ENCLIB. NOTE: Caslib options provided a password to encrypt the stored data. Subsequent loading of this table will require the same password. NOTE: Action 'table.save' used (Total process time): 92 /* Load CAS from an encrypted data file saved by CAS */ 93 proc casutil incaslib="enclib" outcaslib="enclib"; NOTE: The UUID '672ed24f-e92d-8341-802e-8116b5f5c7f9' is connected using session MYSESSION. 94 load casdata="cars_encrpt.sashdat" casout="cars_hdat" ; NOTE: Executing action 'table.loadTable'. NOTE: Cloud Analytic Services made the file cars_encrpt.sashdat available as table CARS_HDAT in caslib enclib. NOTE: Action 'table.loadTable' used (Total process time): ………. ……………   If a SAS Viya user executes the above code without the ENCRYPTIONDOMAIN= parameter and value in the CASLIB statement, it will generate the error message “A password is required”. When a data file is encrypted with an encrypted password, it will not open until a valid password is provided.   Code:   CASLIB enclib1 DATASOURCE=(SRCTYPE="PATH" ) path="/mnt/viya-share/data/" ;   Log extract:   ………… …….. 85 /* Load CAS from an encrypted data file saved by CAS */ 86 proc casutil incaslib="enclib1" outcaslib="enclib1" ; NOTE: The UUID 'b40d2d7e-6880-e54d-8406-770792cae6bc' is connected using session MYSESSION. 87 load casdata="cars_encrpt.sashdat" casout="cars_hdat" ; NOTE: Executing action 'table.loadTable'. ERROR: A password is required to access this encrypted file. ERROR: The action stopped due to errors. NOTE: Action 'table.loadTable' used (Total process time): ………. ……………   When a non-member user of the encryption domain “SalesEncryption”, executes the above code with the ENCRYPTIONDOMAIN= parameter and value in the CASLIB statement, it generates the error message “No Credentials found”. When a user is accessing an encrypted data file using the encryption domain, they must be part of the domain with a valid encryption key.   Code:   CASLIB enclib DATASOURCE=(SRCTYPE="PATH" ENCRYPTIONDOMAIN="SalesEncryption" ) path="/mnt/viya-share/data/" ;   Log extract:   ………… …….. 86 proc casutil incaslib="enclib" outcaslib="enclib"; NOTE: The UUID '33c5f2ad-2e5a-594d-95d9-c763af93f08d' is connected using session MYSESSION. 87 load casdata="cars_encrpt.sashdat" casout="cars_hdat" ; NOTE: Executing action 'table.loadTable'. ERROR: No credentials found for domain SalesEncryption. ERROR: You do not have stored credentials for the requested security domain. ERROR: The action stopped due to errors. NOTE: Action 'table.loadTable' used (Total process time): ………. ……………   Considerations when using encryption under the CAS server:   Once an encryption domain is created, it cannot be deleted from the SAS Environment Manager Interface. If required, the SAS Viya administrator can delete it using the sas-viya CLI statement. The encryption key cannot be changed in the encrypted data files. If a data file is encrypted using an encryption domain key, it can only be read using the same key. If you plan to change the encryption key for data files, you need to read them using the old encryption key and write them using the new encryption key. If an encrypted caslib is deleted, the data files in the associated path remain encrypted. To access the same data files, you must create a new caslib with the same encryption domain and key value. Keep the encryption key in a safe and secure place; there is no way to retrieve it from the encryption domain. You can share the key with the required admin users only.   Encryption under SAS Compute Server   The SAS Compute Server supports encryption for SAS data sets at rest. The SAS datasets can be encrypted while writing to disk. The SAS Compute Server provides two types of algorithms to encrypt the SAS datasets. One, SAS proprietary encryption is implemented with the ENCRYPT=YES option in the data set option. Second, AES (Advanced Encryption Standard) encryption is implemented with ENCRYPT=AES or AES2 in the data set option. There is no additional installation required to use the SAS proprietary or AES encryption.   When the encryption key is changed, the encrypted datasets must be rewritten with the new key. You must use the old key to open the existing encrypted data files and rewrite them with the new key. Keep the encrypted key in a safe and secure place and share it only with the required users.   SAS proprietary encryption   The SAS proprietary encryption is implemented by specifying ENCRYPT=YES in the SAS data set option. While encrypting a data set, you must provide a password/key value for READ=, PW=, WRITE=, and ALTER= options for various usage. With ENCRYPT= YES, an ALTER= password option can be used to prevent users from replacing or deleting the file and enables access to read and write to protected files. The PW= option assigns the read, write, and alter password to a SAS file and enables access to password-protected SAS data files.   The following code describes the usage of the SAS proprietary encryption to encrypt a sas7bdat data file while writing to disk.   Code:   %let secret=green; %let readpass=yellow; libname mylib "/mnt/viya-share/data/" ; /* data sets created with ENCRYPT=yes with READ= and ALTER= option */ data mylib.salary(encrypt=yes read=&readpass alter=&secret) ; input name $ yrsal bonuspct; datalines; Muriel 34567 3.2 Bjorn 74644 2.5 Agnetha 70998 4.1 ; /* While reading datasets READ= option required since it’s encrypted */ proc print data=mylib.salary(read=&readpass); run; quit;   Log extract:   ………… …….. 88 data mylib.salary(encrypt=yes read=&readpass alter=&secret) ; 89 input name $ yrsal bonuspct; 90 datalines; NOTE: The data set MYLIB.SALARY has 3 observations and 3 variables. 97 proc print data=mylib.salary(read=&readpass); 98 run; NOTE: There were 3 observations read from the data set MYLIB.SALARY. ………. ……………   If a SAS Viya user tries to access the encrypted data files without the encryption key, it will generate the error message “Invalid or missing READ password”. When a data file is encrypted with an encrypted password, it will not open until a valid password is provided.   Log extract:   ………… …….. 101 proc print data=mylib.salary; ERROR: Invalid or missing READ password on member MYLIB.SALARY.DATA. 102 run; NOTE: The SAS System stopped processing this step because of errors. ………. ……………   AES encryption   The AES Encryption is implemented by specifying ENCRYPT=AES/AES2 in the SAS data set option. While encrypting a data set, you must provide a value for the ENCRYPTKEY= option. With ENCRYPT=AES and ENCRYPTKEY=, an ALTER= or PW= password option can be used to prevent users from replacing or deleting the file, and enables access to read and write protected files. The PW= option assigns the read, write, and alter password to a SAS file.   The following code describes the usage of the AES encryption to encrypt a sas7bdat data file while writing to disk.   Code:   %let mykey=green; %let alterkey=yellow; libname mylib "/mnt/viya-share/data/" ; /* data sets created with ENCRYPT=AES with ENCRYPTKEY= and ALTER= option */ data mylib.salary_aes(encrypt=aes encryptkey=&mykey alter=&alterkey); input name $ yrsal bonuspct; datalines; Muriel 34567 3.2 Bjorn 74644 2.5 Agnetha 70998 4.1 ; /* While reading datasets encryptkey= option required since it’s encrypted */ proc print data=mylib.salary_aes(encryptkey=&mykey); run; quit;   Log extract:   ………… …….. 87 data mylib.salary_aes(encrypt=aes encryptkey=&mykey alter=&alterkey); 88 input name $ yrsal bonuspct; 89 datalines; NOTE: If you lose or forget the ENCRYPTKEY, there will be no way to open the file and recover the data. 96 proc print data=mylib.salary_aes(encryptkey=&mykey); 97 run; NOTE: There were 3 observations read from the data set MYLIB.SALARY_AES. ………. ……………   If a SAS Viya user tries to access the encrypted data files without the encryption key, it will generate the error message “Invalid ENCRYPTKEY value”.   Log extract:   ………… …….. 100 101 proc print data=mylib.salary_aes; ERROR: Invalid ENCRYPTKEY value for MYLIB.SALARY_AES.DATA. 102 run; NOTE: The SAS System stopped processing this step because of errors. ………. ……………   Important Link: SAS® Viya® Platform Encryption: Data at Rest     Find more articles from SAS Global Enablement and Learning here. ... View more

With the SAS Viya 204.07 release, the SAS Viya Compute Server can access Parquet data files from Azure ADLS2 Blob storage. The Azure Storage is supported by the parquet LIBNAME engine. The SAS Parquet engine uses the Azure Storage Account Key or Azure Client ID and Secret for Authentication.   This post talks about accessing ADLS2 blob storage Parquet files from the SAS Compute Server.   Pre-requisites   SAS Viya release 2024.07 and onwards. User access to Azure Storage Account with Storage Blob Data Contributor role. The Azure Storage Account Blob filesystem with read and write permission to users. Azure Storage account access key OR Azure Client ID (application) and Secret with API access permission to the Azure Data Lake and Azure Storage.   Data Path diagram   The following diagram describes the access to ADLS2 Blob storage parquet files from the SAS Compute 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.     Azure information required to access ADLS2 data   To access the Azure ADLS2 Blob storage parquet data file from the SAS Compute Server, you need the following information to execute a Parquet LIBNAME statement.   storage_account_name= “eagler0256viya4adls2” storage_file_system="fsdata" storage_tenant_id="XXXXXXXXXXXXXXXXXXXXXX" storage_application_id="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" storage_client_secret=”XXXXXXXXXXXXXXXXX”   ADLS2 Parquet Data file access from SAS Compute Server   The following code describes the parquet data file save and read from ADLS2 Blob storage to SAS Compute Server. A Parquet LIBNAME statement can use either the Azure Storage Account access key or Azure Client ID (application) and Secret to authentication with Azure. The following code uses Client ID and Secret to authenticate with Azure. The Storage_Shared_Key= LIBNAME option is available from SAS Viya release 2024.07. The Storage_client_secret= LIBNAME option is available from SAS Viya release 20204.08.   Code: %let MYSTRGACC="eagler0256viya4adls2"; %let MYSTRGFS="fsdata"; %let MYTNTID="a708fb09-XXXXXXXXXXXXXXXX"; %let MYAPPID="49200cb0-XXXXXXXXXXXXXX"; %let MYAPPSECRET="JVX8Q~XXXXXXXXXXXXX-XXXXXXXXXXX"; %let MYPLTFRM="ADLS"; %let MYFOLDER="/user_data"; options azuretenantid=&MYTNTID; libname prqtlib parquet &MYFOLDER storage_platform=&MYPLTFRM storage_account_name=&MYSTRGACC storage_file_system=&MYSTRGFS storage_application_id=&MYAPPID storage_client_secret=&MYAPPSECRET ; data prqtlib.fish_prqt; set sashelp.fish; run; data prqtlib.fish_brotli (compress=brotli) ; set sashelp.fish; run ; data prqtlib.fish_lz4 (compress=LZ4) ; set sashelp.fish; run ; PROC SQL outobs=20 ; select * from prqtlib.fish_prqt; run; Proc SQL outobs=20; select * from prqtlib.fish_brotli ; run;quit; Proc SQL outobs=20; select * from prqtlib.fish_lz4 ; run;quit; proc contents data=prqtlib.fish_prqt; run ; proc contents data=prqtlib.fish_brotli; run ; proc contents data=prqtlib.fish_lz4; run ;   Log extract: ………… …….. 92 93 options azuretenantid=&MYTNTID; 94 95 libname prqtlib parquet &MYFOLDER 96 storage_platform=&MYPLTFRM 97 storage_account_name=&MYSTRGACC 98 storage_file_system=&MYSTRGFS 99 storage_application_id=&MYAPPID 100 storage_client_secret=&MYAPPSECRET 101 ; NOTE: Libref PRQTLIB was successfully assigned as follows: Engine: PARQUET Physical Name: /user_data 102 103 data prqtlib.fish_prqt; 104 set sashelp.fish; 105 run; NOTE: There were 159 observations read from the data set SASHELP.FISH. NOTE: The data set PRQTLIB.fish_prqt has 159 observations and 7 variables. NOTE: DATA statement used (Total process time): real time 0.69 seconds cpu time 0.26 seconds 106 107 data prqtlib.fish_brotli (compress=brotli) ; 108 set sashelp.fish; 109 run ; NOTE: There were 159 observations read from the data set SASHELP.FISH. NOTE: The data set PRQTLIB.fish_brotli has 159 observations and 7 variables. NOTE: DATA statement used (Total process time): real time 0.36 seconds cpu time 0.04 seconds 110 111 data prqtlib.fish_lz4 (compress=LZ4) ; 112 set sashelp.fish; 113 run ; NOTE: There were 159 observations read from the data set SASHELP.FISH. NOTE: The data set PRQTLIB.fish_lz4 has 159 observations and 7 variables. NOTE: DATA statement used (Total process time): real time 0.37 seconds cpu time 0.02 seconds 114 115 PROC SQL outobs=20 ; 116 select * from prqtlib.fish_prqt; WARNING: Statement terminated early due to OUTOBS=20 option. 117 run; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. 118 ………. ……………   The following screenshot describes the Parquet data file saved to Azure ADLS2 by executing the above statement.       Data Compression option   The SAS Parquet engine supports BROTLI, GZIP, LZ4, LZ4_HADP, SNAPPY, and ZSTD data compression while saving data to ADLS2. The SAS Parquet engine supports default SNAPPY data compression.   Important Link: SAS Parquet LIBNAME Engine Requirement STORAGE_CLIENT_SECRET= LIBNAME Statement Option     Find more articles from SAS Global Enablement and Learning here. ... View more

The SAS Viya platform supports Single-Sign-On(SSO) authentication access to the Snowflake database. The SAS/ACCESS interface to Snowflake enables, and the Snowflake data connector enables the SAS Compute Server and CAS to connect to the Snowflake database. Both LIBNAME and CASLIB statements support the SSO against the Snowflake database. The SAS/ACCESS Interface to Snowflake and Snowflake data connector are distributed with the SAS Viya software deployment.   The SAS Viya platform can be configured with Microsoft Entra ID as an OIDC provider for initial user authentication. The steps to configure the SAS Viya platform for using OIDC with Microsoft Entra ID are listed in SAS documentation.   In this post, I discuss the configuration parts of the Entra ID OIDC Application, the Snowflake security integration object, and user permission at the Snowflake database for SSO-based access.   Azure Application Registration   Entra ID OIDC application configuration: When creating an Entra ID OIDC application for the Snowflake database access, apart from standard API permissions, you must include the customized Apps (e.g Snowflake OAuth Resource) API permission with the scope defined and delegated role.   The Azure tenant admin must have consented to these roles and the status must be green with text like granted for specific domain/organization.   The following pics describe an Entra ID OIDC application with API permission to another Azure application (Snowflake OAuth Resource) defined for Snowflake database access.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Snowflake OAuth Resource application configuration: It’s an Azure application with API permission and exposed API to get access tokens for the Snowflake database environment. The Microsoft Graph – User Read API permission is required for user sing-in event.   The Snowflake Resource Application includes an exposed API with a scope. The scope defined in this section should match with the user role defined at snowflake database. In this case “sesskion:scope:developer” scope is added to the Snowflake Resource Application. You can use scope as “all”, provided you have the same role given on the database side.     Snowflake Database environment Configuration   External security integration object at Snowflake database: The Azure Snowflake OAuth Resource application requires an external security integration object at the Snowflake database with the matching information. The Database external security integration objects must contain the Azure Snowflake OAuth resource application ID and tenant ID. The Snowflake database administrator must create an external security object similar to the following SQL statements.   SQL:   create security integration external_oauth_azure_gel type = external_oauth enabled = true external_oauth_type = azure external_oauth_issuer = 'https://sts.windows.net/a7000009-1d96-416a-ad34-72fa00980006d/' external_oauth_jws_keys_url = 'https://login.microsoftonline.com/a7099890009-1d96-416a-ad34-72fa00980006d/discovery/v2.0/keys' external_oauth_audience_list = ('https://gel.abc.com/06dd0a8e-07de-43a2-8226-da22d1bf029e') external_oauth_token_user_mapping_claim = 'upn' external_oauth_snowflake_user_mapping_attribute = 'login_name';   The external_oauth_issuer= and external_oauth_jws_keys_url= parameters contain the URL with tenant ID of the Snowflake OAuth resource application.   The external_oauth_audience_list= parameter contains the URL with the domain name and Snowflake OAuth resource application ID.   Snowflake database user role: The Snowflake database user must have the assigned role used in the Snowflake OAuth resource application scope. In this case, a ‘developer’ role was used in the OAuth Resource application, and the following SQL assigns the same role to a user in the Snowflake database environment.   SQL:   CREATE USER MyUser PASSWORD = '' LOGIN_NAME = 'MyUser@abc.com' DISPLAY_NAME = 'MyUser user'
COMMENT = 'Azure AD SSO user for MyUser ' EMAIL = 'MyUser@abc.com'
DEFAULT_ROLE = "DEVELOPER" 
DEFAULT_WAREHOUSE = 'USERS_WH' 
DEFAULT_NAMESPACE = 'USERS_DB.MyUser' 
MUST_CHANGE_PASSWORD = FALSE; grant role DEVELOPER to user MyUser;   SSO-based access from SAS Compute Server to Snowflake database   When the SAS Viya platform is configured with the Entra ID OIDC application, including the Snowflake OAuth Resource application, to provide initial user authentication. SAS users can use SSO-based access from the SAS Compute Server to the Snowflake database using the snow LIBNAME engine with the authscope= option. The Snowflake database must have the external security integration object configured using the information from the Azure tenant and Azure Snowflake OAuth resource application.   The following code describes the SSO-based access from the SAS Compute Server to the Snowflake database. Notice that there is no user ID and password but have an option authscope= option as part of the LIBNAME statement. The authscope= parameter using the application-id and scope configured in the Azure Snowflake OAuth resource application.   Code:   %let MYSERVER="partner.east-us-2.azure.snowflake.com" ; %let MYSCHEMA=GELDMUI; %let MYDB=USERS_DB ; %let MYWH=USERS_WH ; /* SSO based access to Snowflake database */ LIBNAME snow101 snow server=&MYSERVER database=&MYDB schema=&MYSCHEMA warehouse=&MYWH preserve_names=yes authscope="https://gel.abc.com/999999999-07de-43a2-8226-da22d1bf9999/session:scope:developer" ; data snow101.fish_sas ; set sashelp.fish ; run; Proc SQL outobs=10; select * from snow101.fish_sas ; run;quit;   Log:   ........ ............... 87 /* SSO based access to Snowflake database */ 88 LIBNAME snow101 snow 89 server=&MYSERVER 90 database=&MYDB 91 schema=&MYSCHEMA 92 warehouse=&MYWH 93 preserve_names=yes 94 authscope="https://gel.abc.com/999999999-07de-43a2-8226-da22d1bf9999/session:scope:developer" 95 ; NOTE: Libref SNOW101 was successfully assigned as follows: Engine: SNOW Physical Name: partner.east-us-2.azure.snowflake.com 96 101 data snow101.fish_sas ; 102 set sashelp.fish ; 103 run; NOTE: There were 159 observations read from the data set SASHELP.FISH. NOTE: The data set SNOW101.fish_sas has 159 observations and 7 variables. NOTE: DATA statement used (Total process time): real time 1.86 seconds cpu time 0.11 seconds 104 105 Proc SQL outobs=10; 106 select * from snow101.fish_sas ; WARNING: Statement terminated early due to OUTOBS=10 option. 107 run;quit; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: The PROCEDURE SQL printed page 2. NOTE: PROCEDURE SQL used (Total process time): real time 0.33 seconds cpu time 0.05 seconds 108   SSO-based access from CAS to Snowflake database   When the SAS Viya platform is configured with the Entra ID OIDC application, including the Snowflake OAuth Resource application, to provide initial user authentication. SAS users can use SSO-based access from the CAS to the Snowflake database using the Snowflake CASLIB with the authscope= option. The Snowflake database must have the external security integration object configured using the information from the Azure tenant and Azure Snowflake OAuth resource application.   The following code describes the SSO-based access from the CAS to the Snowflake database. Notice that there is no user ID and password but have an option authscope= option as part of the CASLIB statement. The authscope= parameter using the application-id and scope configured in the Azure Snowflake OAuth resource application.   Code:   CAS mySession SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" ); %let MYSERVER="partner.east-us-2.azure.snowflake.com" ; %let MYSCHEMA=GELDMUI; %let MYDB=USERS_DB ; %let MYWH=USERS_WH ; /* SSO based access to Snowflake database */ caslib snowlib desc='Snowflake Caslib' dataSource=(srctype='snowflake', server=&MYSERVER, database=&MYDB, schema=&MYSCHEMA, warehouse=&MYWH, authscope="https://gel.abc.com/999999999-07de-43a2-8226-da22d1bf9999/session:scope:developer" ) libref=snowlib; /*save a CAS table to Snowflake */ proc casutil incaslib="snowlib" outcaslib="snowlib"; load data=sashelp.cars casout="cars" replace; save casdata="cars" casout="cars_cas" replace; list files; quit ; /* load a Snowflake table to CAS*/ proc casutil incaslib="snowlib" outcaslib="snowlib" ; load casdata="cars_cas" casout="cars_cas" replace; list tables; quit ; cas mysession terminate;   Log:   ........ ............... 83 /* Initialize the variable with Snowflake Database connection details */ 84 %let MYSERVER="partner.east-us-2.azure.snowflake.com" ; 85 %let MYSCHEMA=GELDMUI; 86 %let MYDB=USERS_DB ; 87 %let MYWH=USERS_WH ; 88 89 /* SSO based access to Snowflake database */ 90 caslib snowlib desc='Snowflake Caslib' 91 dataSource=(srctype='snowflake', 92 server=&MYSERVER, 93 database=&MYDB, 94 schema=&MYSCHEMA, 95 warehouse=&MYWH, 96 authscope="https://gel.abc.com/999999999-07de-43a2-8226-da22d1bf9999/session:scope:developer" 97 ) libref=snowlib; NOTE: 'SNOWLIB' is now the active caslib. NOTE: Cloud Analytic Services added the caslib 'SNOWLIB'. NOTE: CASLIB SNOWLIB for session MYSESSION will be mapped to SAS Library SNOWLIB. NOTE: Action to ADD caslib SNOWLIB completed for session MYSESSION. 98 99 100 /*save a CAS table to Snowflake */ 101 proc casutil incaslib="snowlib" outcaslib="snowlib"; NOTE: The UUID 'e8f2dccf-ab97-1649-a6b4-ac53dc4c57ab' is connected using session MYSESSION. 102 load data=sashelp.cars casout="cars" replace; NOTE: The INCASLIB= option is ignored when using the DATA= option in the LOAD statement. NOTE: SASHELP.CARS was successfully added to the "SNOWLIB" caslib as "CARS". 103 save casdata="cars" casout="cars_cas" replace; NOTE: Performing serial SaveTable action using SAS Data Connector to Snowflake. NOTE: Cloud Analytic Services saved the file cars_cas in caslib SNOWLIB. NOTE: The Cloud Analytic Services server processed the request in 4.735969 seconds. ............ ................. 107 /* load a Snowflake table to CAS*/ 108 proc casutil incaslib="snowlib" outcaslib="snowlib" ; NOTE: The UUID 'e8f2dccf-ab97-1649-a6b4-ac53dc4c57ab' is connected using session MYSESSION. 109 load casdata="cars_cas" casout="cars_cas" replace; NOTE: Performing serial LoadTable action using SAS Data Connector to Snowflake. NOTE: Cloud Analytic Services made the external data from cars_cas available as table CARS_CAS in caslib snowlib. NOTE: The Cloud Analytic Services server processed the request in 2.891609 seconds. ...... .............   Important Links:   Authenticate to Microsoft Azure by Using Single Sign-On (Snowflake) Scenario: OIDC with Microsoft Entra ID (Azure Active Directory) LIBNAME Statement for Snowflake Configuring Microsoft Entra ID for External OAuth     Find more articles from SAS Global Enablement and Learning here. ... View more

The SAS Viya platform supports the Service Principal authentication-based access to the Azure DataBricks database with the CDATA JDBC drivers. This access is available for both SPARK LIBNAME and SPARK CASLIB statements against the Azure Databricks database. To access using Service Principal, the SAS user is required to include the Azure environment details, Databricks properties, and the Service Principle credential in the properties= option of the LIBNAME and CASLIB statement.   In this post, I discuss the configuration parts of the Azure Service Principal, Databricks workspace, and Service Principal permission at DataBricks for accessing the Azure Databricks database.   The CDATA JDBC driver is distributed with the SAS Viya software deployment and is the default driver for SAS users to connect to the DataBricks database. With the CDATA JDBC connection string properties, SAS users can provide the Azure TenantID, SubscriptionID, ResourceGroup, DatabricksWorksSpace, ClientID, ClientSecrete, and AuthScheme. The JDBC connection will acquire an access token from the Databricks environment using the connection properties when AuthScheme is service principal. The Azure Service Principal (ClientId) must have a valid secret and the Databricks API permission as part of the configuration.   The CDATA documentation to access the Azure DataBricks using Service Principal.   Azure Service Principle (application) configuration:   When creating an Entra ID Service Principle (application) for Databricks access, apart from standard API permissions, you must include the “Azure DataBricks” API permission with the user_impersonation delegated role.   The Azure tenant admin must have consented to these roles and the status must be green with text like granted for specific domain/organization.   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 Azure Service Principle (ClientId) must have a valid secret.   Databricks Workspace Configuration:   The PAT (Personal Access Token) Option must be enabled at Databricks Workspace for PAT, SSO, and Service Principal (SP) based access path to work. The user processes connecting to Azure Databricks with SSO or SP authentication using the JDBC driver initiate a PAT token at Databricks instance, even though it’s the SSO or SP based connection.     Add an Entra ID Service Principle(application) to the Databricks environment for DataBricks workspace, and database tables access.     Use the “Microsoft Entra ID Managed” option while adding and provide the application ID from Azure Service Principle (application) configuration steps. Under Entitlement section, select the “Databricks SQL Access” and “Allow Workspace access” with status active.       Grant access privileges to user (geldm) to use the Service principle. The users or groups who need to use the service principle must be granted with “Service Principle: user” privilege. The user with “Service Principle: manager” privilege, by default does not get ability to use it. The “Service Principle: user” privilege must be granted to the user (geldm) to use the Service Principle.   At the Service Principle details, under the Permission tab, you can add and update the additional privileges to users with the required privileges and permissions.     The Entra ID Service Principal (application) added to the DataBricks workspace must be part of the admin group for Service Principle-based authentication. Behind the scenes, the authentication process creates a DataBricks PAT token for SSO or Service Principal users. As per the DataBricks configuration, only admin group users can manage and create PAT tokens at DataBricks.     Service Principal based access from SAS Compute Server to Azure DataBricks   When the Service Principal is configured with the required API permission and a valid credential. The DataBricks Workspace is included with a valid Service principle and permissions. The SAS users can use the Service Principal in properites= option of the LIBNAME statement to access the Azure Databricks database using SPARK LIBNAME engine.   The LIBNAME with the Service Principal is supported with the CDATA JDBC driver. The driver is distributed with the SAS Viya software deployment and is the default driver for SAS users to connect to the DataBricks database.   The following code describes the Service Principal based access from the SAS Compute Server to the Azure Databricks database. Notice that there is no user ID and password but have Service Principal, credential, workspace name, and other information in the properties= option of the LIBNAME statement.   Code:   %let MYRSGRP=clove-r-0302-viya4-data ; %let MYAZWS=ws-clove-r-0302-viya4-data; %let MYDBRICKS=adb-3290662697234059.19.azuredatabricks.net; %let MYHTTPPATH=sql/protocolv1/o/3290662697234059/0916-145442-7dn19gjc; %let MYTNTID=a708fb09-1d96-416a-ad34-72fa07ff196d; %let MYSUBID=d588dad0-2004-4b14-a34e-6bf0519e32e4; %let MYCLNT_ID=49200cb0-a10c-420e-8ba5-6d06ee6ac1f6; /* AppReg client secret */ %let MYCLNT_SECRET=H8N8Q~XXXXXX~XXXXXXX; %let MYCATALOG=hive_metastore; %let MYSCHEMA=default; /* Azure Service Principal based LIBNAME to Databricks */ libname spkdb spark platform=databricks server="&MYDBRICKS" database="&MYSCHEMA" port=443 httppath="&MYHTTPPATH" properties="Catalog=&MYCATALOG;AuthScheme=AzureServicePrincipal;AzureTenantId=&MYTNTID;AzureClientId=&MYCLNT_ID;AzureClientSecret=&MYCLNT_SECRET;AzureSubscriptionId=&MYSUBID;AzureResourceGroup=&MYRSGRP;AzureWorkspace=&MYAZWS;QueryPassthrough=true;DefaultColumnSize=1024;Other=ConnectRetryWaitTime=20;" bulkload=no character_multiplier=1 dbmax_text=50; Proc SQL outobs=5; select * from spkdb.baseball_prqt ; run; quit; data spkdb.prdsal2_sas3 ; set sashelp.prdsal2 ; run;   Log:   ......... ..... 82 %let MYRSGRP=birch-p03073-viya4-data ; 83 %let MYAZWS=ws-birch-p03073-viya4-data; 84 %let MYDBRICKS=adb-3916581250425199.19.azuredatabricks.net; 85 %let MYHTTPPATH=sql/protocolv1/o/3916581250425199/0927-154850-ssq0x1nk; 86 87 98 /* Azure Service Principal based LIBNAME to Databricks */ 99 libname spkdb spark platform=databricks 100 server="&MYDBRICKS" 101 database="&MYSCHEMA" 102 port=443 103 httppath="&MYHTTPPATH" 104 properties="Catalog=&MYCATALOG;AuthScheme=AzureServicePrincipal;AzureTenantId=&MYTNTID;AzureClientId=&MYCLNT_ID; 104! AzureClientSecret=&MYCLNT_SECRET;AzureSubscriptionId=&MYSUBID;AzureResourceGroup=&MYRSGRP;AzureWorkspace=&MYAZWS; 104! QueryPassthrough=true;DefaultColumnSize=1024;Other=ConnectRetryWaitTime=20;" NOTE: The quoted string currently being processed has become more than 262 bytes long. You might have unbalanced quotation marks. 105 bulkload=no 106 character_multiplier=1 107 dbmax_text=50; NOTE: Libref SPKDB was successfully assigned as follows: Engine: SPARK Physical Name: jdbc:cdata:databricks:Server=adb-3916581250425199.19.azuredatabricks.net;Database=default;HTTPPath=sql/protocolv1/o/3916581250 425199/0927-154850-ssq0x1nk;QueryPassthrough=true;UseCloudFetch=true;Catalog=hive_metastore;AuthScheme=AzureServicePrincipal;A zureTenantId=a708fb09-1d96-416a-ad34-72fa07ff196d;AzureClientId=49200cb0-a10c-420e-8ba5-6d06ee6ac1f6;AzureClientSecret=H8N8Q~v 2KTT1VCbjXXXXXXXXXX;AzureSubscriptionId=d588dad0-2004-4b14-a34e-6bf0519e32e4;AzureResourceGroup=birch-p03073-viy a4-data;AzureWorkspace=ws-birch-p03073-viya4-data;QueryPassthrough=true;DefaultColumnSize=1024;Other=ConnectRetryWaitTime=20; 108 109 Proc SQL outobs=5; 110 select * from spkdb.baseball_prqt ; WARNING: Statement terminated early due to OUTOBS=5 option. 111 run; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. 112 quit; NOTE: The PROCEDURE SQL printed page 3. NOTE: PROCEDURE SQL used (Total process time): real time 1.40 seconds cpu time 0.07 seconds 113 114 data spkdb.prdsal2_sas3 ; 115 set sashelp.prdsal2 ; 116 run; NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables. NOTE: There were 23040 observations read from the data set SASHELP.PRDSAL2. NOTE: The data set SPKDB.PRDSAL2_SAS3 has 23040 observations and 11 variables. NOTE: DATA statement used (Total process time): .... ...........   Results:     Service Principal based access from CAS to Azure DataBricks   When the Service Principal is configured with the required API permission and a valid credential. The DataBricks Workspace is included with a valid Service principle and permissions. The SAS users can use the Service Principal in properites= option of the CASLIB statement to access and load CAS from the Azure Databricks database.   The CASLIB with the Service Principal is supported with the CDATA JDBC driver. The driver is distributed with the SAS Viya software deployment and is the default driver for SAS users to connect to the DataBricks database.   The following code describes the Service Principal based access from the CAS to the Azure Databricks database. Notice that there is no user ID and password but have Service Principal, credential, workspace name, and other information in the properties= option of the CASLIB statement.   Code:   %let MYRSGRP=clove-r-0302-viya4-data ; %let MYAZWS=ws-clove-r-0302-viya4-data; %let MYDBRICKS=adb-3290662697234059.19.azuredatabricks.net; %let MYHTTPPATH=sql/protocolv1/o/3290662697234059/0916-145442-7dn19gjc; %let MYTNTID=a708fb09-1d96-416a-ad34-72fa07ff196d; %let MYSUBID=d588dad0-2004-4b14-a34e-6bf0519e32e4; %let MYCLNT_ID=49200cb0-a10c-420e-8ba5-6d06ee6ac1f6; /* AppsReg client secret */ %let MYCLNT_SECRET=H8N8Q~XXXXXXXXXXXXXXXXXXX; %let MYCATALOG=hive_metastore; %let MYSCHEMA=default; CAS mySession SESSOPTS=( CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); /* Azure Service Principal based CASLIB to Databricks */ caslib Cdtspkcaslib datasource=(srctype='spark', platform=databricks, schema="&MYSCHEMA", server="&MYDBRICKS", httpPath="&MYHTTPPATH", bulkload=no, port=443, properties="Catalog=&MYCATALOG;AuthScheme=AzureServicePrincipal;AzureTenantId=&MYTNTID;AzureClientId=&MYCLNT_ID;AzureClientSecret=&MYCLNT_SECRET;AzureSubscriptionId=&MYSUBID;AzureResourceGroup=&MYRSGRP;AzureWorkspace=&MYAZWS;QueryPassthrough=true;DefaultColumnSize=1024;Other=ConnectRetryWaitTime=20;" ) libref=dbxlib ; /* Save CAS table to DataBricks database table */ proc casutil outcaslib="Cdtspkcaslib" incaslib="Cdtspkcaslib"; load data=sashelp.cars casout="cars" replace; save casdata="cars" casout="cars_sas" replace; list files; quit; /* Load CAS from DataBricks database table */ proc casutil outcaslib="Cdtspkcaslib" incaslib="Cdtspkcaslib" ; load casdata="cars_sas" casout="cars_sas" replace; list tables; quit; CAS mySession TERMINATE;   Log:   ... ....... 97 /* Azure Service Principal based CASLIB to Databricks */ 98 caslib Cdtspkcaslib datasource=(srctype='spark', 99 platform=databricks, 100 schema="&MYSCHEMA", 101 server="&MYDBRICKS", 102 httpPath="&MYHTTPPATH", 103 bulkload=no, 104 port=443, 105 properties="Catalog=&MYCATALOG;AuthScheme=AzureServicePrincipal;AzureTenantId=&MYTNTID;AzureClientId=&MYCLNT_ID; 105! AzureClientSecret=&MYCLNT_SECRET;AzureSubscriptionId=&MYSUBID;AzureResourceGroup=&MYRSGRP;AzureWorkspace=&MYAZWS; 105! QueryPassthrough=true;DefaultColumnSize=1024;Other=ConnectRetryWaitTime=20;" NOTE: The quoted string currently being processed has become more than 262 bytes long. You might have unbalanced quotation marks. 106 ) libref=dbxlib ; NOTE: Executing action 'table.addCaslib'. NOTE: 'CDTSPKCASLIB' is now the active caslib. …. …. …. 107 108 /* Save CAS table to DataBricks database table */ 109 proc casutil outcaslib="Cdtspkcaslib" incaslib="Cdtspkcaslib"; NOTE: The UUID '70d1e97b-dd79-564d-ae5b-91243d85c31e' is connected using session MYSESSION. 110 load data=sashelp.cars casout="cars" replace; NOTE: The INCASLIB= option is ignored when using the DATA= option in the LOAD statement. NOTE: Executing action 'table.addTable'. NOTE: Action 'table.addTable' used (Total process time): NOTE: real time 0.017585 seconds NOTE: cpu time 0.029144 seconds (165.73%) NOTE: total nodes 3 (12 cores) NOTE: total memory 94.03G NOTE: memory 3.51M (0.00%) NOTE: bytes moved 68.08K NOTE: SASHELP.CARS was successfully added to the "CDTSPKCASLIB" caslib as "CARS". 111 save casdata="cars" casout="cars_sas" replace; NOTE: Executing action 'table.save'. NOTE: Performing serial SaveTable action using SAS Data Connector to Spark. NOTE: Cloud Analytic Services saved the file cars_sas in caslib CDTSPKCASLIB. NOTE: Action 'table.save' used (Total process time): NOTE: real time 17.310049 seconds NOTE: cpu time 0.129744 seconds (0.75%) NOTE: total nodes 3 (12 cores) NOTE: total memory 94.03G NOTE: memory 7.41M (0.01%) NOTE: The Cloud Analytic Services server processed the request in 17.310049 seconds. 112 list files; NOTE: Executing action 'table.caslibInfo'. …… …… ….. 115 /* Load CAS from DataBricks database table */ 116 proc casutil outcaslib="Cdtspkcaslib" incaslib="Cdtspkcaslib" ; NOTE: The UUID '70d1e97b-dd79-564d-ae5b-91243d85c31e' is connected using session MYSESSION. 117 load casdata="cars_sas" casout="cars_sas" replace; NOTE: Executing action 'table.loadTable'. NOTE: Performing serial LoadTable action using SAS Data Connector to Spark. NOTE: Cloud Analytic Services made the external data from cars_sas available as table CARS_SAS in caslib Cdtspkcaslib. NOTE: Action 'table.loadTable' used (Total process time): NOTE: real time 3.580677 seconds NOTE: cpu time 0.072706 seconds (2.03%) NOTE: total nodes 3 (12 cores) NOTE: total memory 94.03G NOTE: memory 8.63M (0.01%) NOTE: bytes moved 127.68K NOTE: The Cloud Analytic Services server processed the request in 3.580677 seconds. 118 list tables; … ..   Results:     Important Links: CDATA document - access the Azure DataBricks using Service Principal     ... View more

The SAS Viya platform supports Single-Sign-On(SSO) authentication access to the Azure DataBricks environment with the CDATA and Databrick JDBC drivers. The SSO is supported for both SPARK LIBNAME and SPARK CASLIB statements against the Databricks database. The LIBNAME and CASLIB statement must include option AUTH=OAUTH2 to use the SSO authenticated connection to DataBricks. The CDATA JDBC driver is distributed with the SAS Viya software deployment and is the default driver for SAS to connect to the DataBricks database.   The SAS Viya platform can be configured with Microsoft Entra ID as an OIDC provider for initial user authentication. The steps to configure the SAS Viya platform for using OIDC with Microsoft Entra ID are listed in SAS documentation.   In this post, I discuss the critical configuration parts of the Entra ID OIDC Application, Databricks workspace, and user permission at DataBricks when using SSO-based access to the Databricks database.   Entra ID OIDC application configuration:   When creating an Entra ID OIDC application for Databricks access, apart from standard API permissions, you must include the “Azure DataBricks” API permission with the user_impersonation delegated role.   The Azure tenant admin must have consented to these roles and the status must be green with text like granted for specific domain/organization.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.     Databricks Workspace Configuration:   The PAT (Personal Access Token) Option must be enabled at Databricks Workspace for PAT, SSO, and Service Principal (SP) based access path to work. The user processes connecting to Azure Databricks with SSO or SP authentication using the JDBC driver initiate a PAT token at Databricks instance, even though it’s the SSO or SP connection.     The Databricks admin user can add additional Entra ID users to the Databricks environment for DataBricks workspace, and database tables access.     All Entra ID users added to the DataBricks workspace must be part of the admin group for SSO authentication. Behind the scenes, the OAuth process creates a DataBricks PAT token for SSO users. As per the DataBricks configuration, only admin group users can manage and create PAT tokens at DataBricks.       SSO-based access from SAS Compute Server to Azure DataBricks   When the SAS Viya platform is configured with Entra ID OIDC application to provide initial user authentication and Databricks Workspace is configured with Entra ID user access permissions. SAS users can use SSO-based access from SAS Compute Server to the Azure Databricks database using SPARK LIBNAME engine with AUTH=OAUTH2 option.   The following code describes the SSO-based access from the SAS Compute Server to the Azure Databricks database. Notice that there is no user ID and password but have an option AUTH=OAUTH2 as part of the LIBNAME statement.   Code:   %let MYDBRICKS=adb-3916581250425199.19.azuredatabricks.net; %let MYHTTPPATH=sql/protocolv1/o/3916581250425199/0927-154850-ssq0x1nk; %let MYDRIVERCLASS="cdata.jdbc.databricks.DatabricksDriver"; %let MYCATALOG=hive_metastore; %let MYSCHEMA=default; libname CdtSpark spark platform=databricks driverClass=&MYDRIVERCLASS auth=oauth2 server="&MYDBRICKS" database="&MYSCHEMA" httpPath="&MYHTTPPATH" port=443 bulkload=no character_multiplier=1 dbmax_text=50 properties="Catalog=&MYCATALOG;Other=ConnectRetryWaitTime=20;DefaultColumnSize=1024;" ; Proc SQL outobs=5; select * from CdtSpark.baseball_prqt ; run;quit; data CdtSpark.prdsal2_sas2 ; set sashelp.prdsal2 ; run;   Log:   80 %let MYDBRICKS=adb-3916581250425199.19.azuredatabricks.net; 81 %let MYHTTPPATH=sql/protocolv1/o/3916581250425199/0927-154850-ssq0x1nk; 82 83 %let MYDRIVERCLASS="cdata.jdbc.databricks.DatabricksDriver"; 84 %let MYCATALOG=hive_metastore; 85 %let MYSCHEMA=default; 86 87 libname CdtSpark spark platform=databricks 88 driverClass=&MYDRIVERCLASS 89 auth=oauth2 90 server="&MYDBRICKS" 91 database="&MYSCHEMA" 92 httpPath="&MYHTTPPATH" 93 port=443 94 bulkload=no 95 character_multiplier=1 96 dbmax_text=50 97 properties="Catalog=&MYCATALOG;Other=ConnectRetryWaitTime=20;DefaultColumnSize=1024;" 98 ; NOTE: Libref CDTSPARK was successfully assigned as follows: Engine: SPARK Physical Name: jdbc:cdata:databricks:Server=adb-3916581250425199.19.azuredatabricks.net;Database=default;HTTPPath=sql/protocolv1/o/3916581250 425199/0927-154850-ssq0x1nk;QueryPassthrough=true;UseCloudFetch=true;InitiateOAuth=OFF;AuthScheme=AzureAD;OAuthAccessToken=*** ***;Catalog=hive_metastore;Other=ConnectRetryWaitTime=20;DefaultColumnSize=1024; 99 100 Proc SQL outobs=5; 101 select * from CdtSpark.baseball_prqt ; WARNING: Statement terminated early due to OUTOBS=5 option. 102 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 1.66 seconds cpu time 0.07 seconds 103 104 data CdtSpark.prdsal2_sas2 ; 105 set sashelp.prdsal2 ; 106 run; NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables. NOTE: There were 23040 observations read from the data set SASHELP.PRDSAL2. NOTE: The data set CDTSPARK.PRDSAL2_SAS2 has 23040 observations and 11 variables. 107   Results:       SSO-based access from CAS to Azure DataBricks   When the SAS Viya platform is configured with Entra ID OIDC application to provide initial user authentication and Databricks Workspace is configured with Entra ID user access permissions. The SSO-based access can be used to load CAS from the Azure Databricks database using SPARK CASLIB with the AUTH=OAUTH2 option.   The following code describes the SSO-based access from CAS to the Azure Databricks database. Notice that there is no user ID and password but have an option AUTH=OAUTH2 as part of the CASLIB statement.   Code:   %let MYDBRICKS=adb-3916581250425199.19.azuredatabricks.net; %let MYHTTPPATH=sql/protocolv1/o/3916581250425199/0927-154850-ssq0x1nk; %let MYDRIVERCLASS=cdata.jdbc.databricks.DatabricksDriver; %let MYCATALOG=hive_metastore; %let MYSCHEMA=default; CAS mySession SESSOPTS=( CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); /* SSO based CASLIB to Databricks */ caslib Cdtspkcaslib datasource=(srctype='spark', platform=databricks, auth=oauth2, schema="&MYSCHEMA", server="&MYDBRICKS", httpPath="&MYHTTPPATH", driverclass="&MYDRIVERCLASS", bulkload=no, port=443, useSsl=yes, charMultiplier=1, dbmaxText=50, properties="Catalog=&MYCATALOG;DefaultColumnSize=255;Other=ConnectRetryWaitTime=20" ); /* Load CAS from DataBricks database table */ proc casutil outcaslib="Cdtspkcaslib" incaslib="Cdtspkcaslib" ; load casdata="iot_device" casout="iot_device" replace; list tables; quit; /* Save CAS data to DataBricks database table */ proc casutil outcaslib="Cdtspkcaslib" incaslib="Cdtspkcaslib"; load data=sashelp.cars casout="cars" replace; save casdata="cars" casout="cars_sas" replace; list files; quit; CAS mySession TERMINATE;   Log:   90 /* SSO based CASLIB to Databricks */ 91 caslib Cdtspkcaslib datasource=(srctype='spark', 92 platform=databricks, 93 auth=oauth2, 94 schema="&MYSCHEMA", 95 server="&MYDBRICKS", 96 httpPath="&MYHTTPPATH", 97 driverclass="&MYDRIVERCLASS", 98 bulkload=no, 99 port=443, 100 useSsl=yes, 101 charMultiplier=1, 102 dbmaxText=50, 103 properties="Catalog=&MYCATALOG;DefaultColumnSize=255;Other=ConnectRetryWaitTime=20" 104 ); NOTE: Executing action 'table.addCaslib'. NOTE: 'CDTSPKCASLIB' is now the active caslib. NOTE: Cloud Analytic Services added the caslib 'CDTSPKCASLIB'. 105 106 /* Load CAS from DataBricks database table */ 107 proc casutil outcaslib="Cdtspkcaslib" incaslib="Cdtspkcaslib" ; NOTE: The UUID 'c5e703f1-0786-fe4d-b8df-19f813db3445' is connected using session MYSESSION. 108 load casdata="iot_device" casout="iot_device" replace; NOTE: Executing action 'table.loadTable'. NOTE: Performing serial LoadTable action using SAS Data Connector to Spark. NOTE: Cloud Analytic Services made the external data from iot_device available as table IOT_DEVICE in caslib Cdtspkcaslib. NOTE: Action 'table.loadTable' used (Total process time): 111 112 /* Save CAS data to DataBricks database table */ 113 proc casutil outcaslib="Cdtspkcaslib" incaslib="Cdtspkcaslib"; NOTE: The UUID 'c5e703f1-0786-fe4d-b8df-19f813db3445' is connected using session MYSESSION. 114 load data=sashelp.cars casout="cars" replace; NOTE: The INCASLIB= option is ignored when using the DATA= option in the LOAD statement. NOTE: Executing action 'table.addTable'. NOTE: Action 'table.addTable' used (Total process time): NOTE: SASHELP.CARS was successfully added to the "CDTSPKCASLIB" caslib as "CARS". 115 save casdata="cars" casout="cars_sas" replace; NOTE: Executing action 'table.save'. NOTE: Performing serial SaveTable action using SAS Data Connector to Spark. NOTE: Cloud Analytic Services saved the file cars_sas in caslib CDTSPKCASLIB. NOTE: Action 'table.save' used (Total process time):   Results:     Important Links:   Authenticate to Databricks on Microsoft Azure by Using Single Sign-On Scenario: OIDC with Microsoft Entra ID (Azure Active Directory) LIBNAME Statement for Spark     Find more articles from SAS Global Enablement and Learning here. ... View more