Configuring BASE SAS Data Source at Remote Data Agent (Cloud Data Exchange)

2 Likes

With SAS Viya Cloud Data Exchange (CDE) environment, one widely used data source is the BASE SAS data source. The BASE SAS data source connector/driver is available with CDE deployment. The CDE administrator requires to mount the data folder with a data agent container to avail the datasets. As there are two types of data agents, an administrator can configure the BASE SAS data source at both data agents with access to the data source folder.

In this post, I discuss the configuration of the BASE SAS data source at the Remote Data agent.

The Remote Data Agent is a containerized software and runs with docker runtime. The CDE administrator can mount the required folder of SAS datasets (7bdat) files while starting the Remote Data Agent container. The mounting step can include multiple folders.

The following pics describe the local data folder mounted to the Remote Data Agent container.

Select any image to see a larger version.
Mobile users: If you do not see this image, scroll to the bottom of the page and select the "Full" version of this post.

The following steps describe the local data folder mount to Remote Data Agent while starting container service.

Change the user permission of the data folder

The folder containing SAS data sets should have read and write permission for user “sas” (uid 1001:1001).

sudo chown sas:sas /viya-share/gelenv/data

Start the RDA service with the data folder mounted

Start the RDA service with local data folder (e.g. /viya-share/gelenv/data) mounted to the RDA container. Multiple folder values can be specified by separating them with commas, such as --mount /u/my/data:/mydata,/tmp:/vmtmp.

./container-manager start \
--license-path SASViyaV4_9CV11D_license.jwt \
--sasdata sasdata \
--vars da-vars.env \
--data sasdata/data \
--mount /viya-share/gelenv/data:/gelenv/data \
--access-vars sas-access.properties \
sas-data-agent-server-remote

Create a logical BASE Catalog

Create a logical catalog and schema to access the SAS BASE datasets from the mounted data folder. Use the sas-viya CLI interface to create a logical BASE catalog. A valid user must authenticate against the Viya platform with the required permission to execute CLI.

Code:

sas-viya dagentsrv servers set-default --data-agent sas-data-agent-server-remote;

sas-viya dagentsrv catalogs create base --name BASECATR1 ;

sas-viya dagentsrv catalogs  list ;

Log:

 jumpuser@p03173-jump-vm:~$ sas-viya dagentsrv servers set-default --data-agent sas-data-agent-server-remote;
The default data agent server was successfully set.

jumpuser@p03173-jump-vm:~$ sas-viya dagentsrv catalogs create base --name BASECATR1 ;
The catalog was successfully created.

jumpuser@p03173-jump-vm:~$ sas-viya dagentsrv catalogs  list ;
Name        Service   Native Catalog   Options
BASECATR1   BASE

Create a Schema under BASE Catalog

Create a BASE schema under logical catalog using the sas-viya CLI interface against the local folder mounted to the RDA container. The Schema features the folder name to access the data sets.

Code:

sas-viya dagentsrv schemas create base --catalog BASECATR1  --name SCHEMAR1 --primary-path /gelenv/data/ ;

sas-viya dagentsrv  schemas list ;

Log:

jumpuser@p03173-jump-vm:~$ sas-viya dagentsrv schemas create base --catalog BASECATR1  --name SCHEMAR1 --primary-path /gelenv/data/ ;
The schema was successfully created.


jumpuser@p03173-jump-vm:~$ sas-viya dagentsrv  schemas list ;
Name       Catalog     Options
SCHEMAR1   BASECATR1   PRIMARYPATH=/gelenv/data/

Create a Test table to BASE data source

Create a test table at the BASE data source and verify the data manipulation using the CLI statement.

Code:

sas-viya dagentsrv sql --sql "create table SCHEMAR1.TEST(col CHAR(10))" --dsn BASE --data-agent sas-data-agent-server-remote


sas-viya dagentsrv sql --sql "insert into SCHEMAR1.TEST values ('success')" --dsn BASE --data-agent sas-data-agent-server-remote


sas-viya dagentsrv sql --sql "select * from SCHEMAR1.TEST" --dsn BASE --data-agent sas-data-agent-server-remote

Log:

jumpuser@utkuma-p03173-jump-vm:~$ sas-viya dagentsrv sql --sql "create table SCHEMAR1.TEST(col CHAR(10))" --dsn BASE --data-agent sas-data-agent-server-remote
SQL statement successfully executed.


jumpuser@utkuma-p03173-jump-vm:~$ sas-viya dagentsrv sql --sql "insert into SCHEMAR1.TEST values ('success')" --dsn BASE --data-agent sas-data-agent-server-remote
SQL statement successfully executed.


jumpuser@utkuma-p03173-jump-vm:~$ sas-viya dagentsrv sql --sql "select * from SCHEMAR1.TEST" --dsn BASE --data-agent sas-data-agent-server-remoteCOL
===
success
jumpuser@p03173-jump-vm:~$

SAS Access to BASE SAS Data Source at Remote Data Agent

With BASE SAS Catalog and Schema configured at Remote Data Agent, users can access SAS data sets from SAS Compute Server using CDE LIBNAME statement. The following code describes the access of SAS data sets from a BASE SAS data source.

Code:

LIBNAME cdelib cde dataagentname="sas-data-agent-server-remote"
dsn=BASE catalog=BASECATR1 schema=SCHEMAR1  preserve_tab_names=yes;

data cdelib.fish_sas ;
set sashelp.fish ;
run;

Proc SQL outobs=20;
select * from cdelib.fish_sas ;
run;quit;

Log:

80
81   LIBNAME cdelib cde dataagentname="sas-data-agent-server-remote"
82   dsn=BASE catalog=BASECATR1 schema=SCHEMAR1  preserve_tab_names=yes;
NOTE: Libref CDELIB was successfully assigned as follows:
      Engine:        CDE
      Physical Name: BASE
83
84




79
80   data cdelib.fish_sas ;
81   set sashelp.fish ;
82   run;
NOTE: There were 159 observations read from the data set SASHELP.FISH.
NOTE: The data set CDELIB.fish_sas has 159 observations and 7 variables.
NOTE: DATA statement used (Total process time):
      real time           0.91 seconds
      cpu time            0.09 seconds

83




79
80   Proc SQL outobs=20;
81   select * from cdelib.fish_sas ;
WARNING: Statement terminated early due to OUTOBS=20 option.
82   run;quit;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: The PROCEDURE SQL printed page 1.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.51 seconds
      cpu time            0.06 seconds

83

CAS Access to BASE SAS Data Source at Remote Data Agent

With BASE SAS Catalog and Schema configured at Remote Data Agent, users can access the SAS data sets from CAS using the CDE Data connector. The following code describes the access of SAS data sets from CAS using the CDE CASLIB statement.

Code:

CAS mySession  SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true);

caslib cdebase datasource=(
      srctype="clouddex",
      dataAgentName="sas-data-agent-server-remote",
      catalog="BASECATR1",
      schema="SCHEMAR1",
      conopts="dsn=BASE"
) libref=cdebase;

/* Save CAS table to CDE table/file  */
proc casutil incaslib="cdebase" outcaslib="cdebase" ;
   droptable casdata="SAS_cars" quiet ;
   load data=sashelp.cars casout="SAS_cars"  replace;
   save casdata="SAS_cars" casout="SAS_cars" replace ;
   list tables ;
   list files ;
quit ;

/* CAS load from CDE table/file  */
proc casutil  incaslib="cdebase"  outcaslib="cdebase";
  load casdata="SAS_cars" casout="sas_cars_cde" replace ;
  list tables ;
run;
quit;

cas mysession terminate;

Log:

81
82   caslib cdebase datasource=(
83         srctype="clouddex",
84         dataAgentName="sas-data-agent-server-remote",
85         catalog="BASECATR1",
86         schema="SCHEMAR1",
87         conopts="dsn=BASE"
88   ) libref=cdebase;
NOTE: Executing action 'table.addCaslib'.
NOTE: 'CDEBASE' is now the active caslib.
NOTE: Cloud Analytic Services added the caslib 'CDEBASE'.
NOTE: Action 'table.addCaslib' used (Total process time):
NOTE:       real time               0.012973 seconds




/* Save CAS table to CDE table/file  */
84      save casdata="SAS_cars" casout="SAS_cars" replace ;
NOTE: Executing action 'table.save'.
NOTE: Performing serial SaveTable action using SAS Data Connector to Cloud Data Exchange.
NOTE: Connecting using the OAuth token for CAS user 'geldmui@gelenable.sas.com'.
WARNING: Using server default session timeout of 3600
NOTE: Cloud Analytic Services saved the file SAS_cars in caslib CDEBASE.
NOTE: Action 'table.save' used (Total process time):
NOTE:       real time               0.797028 seconds





81   /* CAS load from CDE table  */
82   proc casutil  incaslib="cdebase"  outcaslib="cdebase";
NOTE: The UUID '43f2b3f5-82dd-1544-9b29-51c55950d09f' is connected using session MYSESSION.
83     load casdata="SAS_cars" casout="sas_cars_cde" replace ;
NOTE: Executing action 'table.loadTable'.
NOTE: Connecting using the OAuth token for CAS user 'geldmui@gelenable.sas.com'.
WARNING: Using server default session timeout of 3600
NOTE: Cloud Analytic Services made the external data from SAS_cars available as table SAS_CARS_CDE in caslib cdebase.
NOTE: Action 'table.loadTable' used (Total process time):
NOTE:       real time               0.516114 seconds

Next, stay tuned in for a post on Oracle data source configuration at the Remote Data Agent server.

Many thanks to Brian Hess for sharing the expert knowledge and help on this post.

Important Links:

Cloud Data Exchange for the SAS Viya Platform

Posts:

Cloud Data Exchange for the SAS Viya Platform

SAS Viya Cloud Data Exchange Deployment and Configuration (Part-1)

SAS Viya Cloud Data Exchange Deployment and Configuration (Part-2)

Find more articles from SAS Global Enablement and Learning here.