BookmarkSubscribeRSS Feed

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

Started ‎11-09-2023 by
Modified ‎11-09-2023 by
Views 277

A SAS Federated Data Source is a common place for end users to access data from multiple data sources. A Cloud Data Exchange (CDE) administrator can create a Federated Data Source in the SAS Viya CDE environment from existing data sources like Oracle, DB2, and BASE SAS tables. The Federated Data Source can have a table schema defined from combinations of multiple data source tables using a few columns from each source data table. The data can also be masked in a Federated Data Source table schema.

 

In this post, I discuss the configuration of the Federated Data Source at the Remote Data agent.

 

The Federated Data Source schema is defined using existing data sources. The client configuration must be deployed and configured at the Remote Data Agent Server to support the various data sources like Oracle, DB2, and MS-SQL Server before using it in the Federated Data Source.

 

Pre-requisite

 

• There are at least two or more data sources (e.g. BASE, Oracle) defined at the Remote Data Agent Server.

 

The following pics describe the Federated Data Source from multiple data sources at the Remote Data Agent Server.

 

01_UK_CDE_Confgiure_Federated_Data_Source_RDA_1.png

Select any image to see a larger version.

Mobile users: To view the images, select the "Full" version at the bottom of the page.

 

Steps to create a Federated Data Source

 

The following steps describe the creation of a Federated Data Source at the Remote Data Agent using various existing data sources.

 

List the predefined data sources

 

A CDE Administrator should know the list of existing data sources to create a new Federated Data Source.

 

Code:

 

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

sas-viya dagentsrv data-sources list

 

Log:

 

jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv servers set-default --data-agent sas-data-agent-server-remote
The default data agent server was successfully set.
jumpuser@p02190-jump-vm:/viya-share/gelenv/data$


jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv data-sources list
Name      ID                        Type
ADMIN     ADMIN                     DSN
BASE      BASE                      DSN
BASE      BASE__DATA_SERVICE__      Service
orasrv1   orasrv1                   DSN
orasrv1   orasrv1__DATA_SERVICE__   Service
jumpuser@p02190-jump-vm:/viya-share/gelenv/data$

 

Create a Federated Data Source.

 

The following statement creates a Federated Data Source using Oracle and BASE data service at the Remote Data Agent Server.

 

Code:

 

sas-viya dagentsrv dsns create federated --name DEMOFED --using "BASE,orasrv1"

 

Log:

 

umpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv dsns create federated --name DEMOFED --using "BASE,orasrv1"
The data source name was successfully created.

 

List and test the Federated Data Source.

 

Code:

 

sas-viya dagentsrv data-sources list

sas-viya dagentsrv  data-sources test --name DEMOFED

 

Log:

 

jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv data-sources list
Name      ID                        Type
ADMIN     ADMIN                     DSN
BASE      BASE                      DSN
BASE      BASE__DATA_SERVICE__      Service
DEMOFED   DEMOFED                   DSN
orasrv1   orasrv1                   DSN
orasrv1   orasrv1__DATA_SERVICE__   Service
jumpuser@p02190-jump-vm:/viya-share/gelenv/data$


jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv  data-sources test --name DEMOFED
Successfully connected to data source DEMOFED.
jumpuser@p02190-jump-vm:/viya-share/gelenv/data$

 

List tables from Federated Data Source.

 

You can access the tables from both the underlying data sources (Oracle and BASE). You can list the table from Oracle and BASE data sources by specifying the corresponding catalog and schema in the CLI statement.

 

Code:

 

as-viya dagentsrv data-sources tables list --data-source DEMOFED --catalog ORASRV1 --schema GELDM

sas-viya dagentsrv data-sources tables list --data-source DEMOFED --catalog BASECATR1 --schema SCHEMAR1

 

Log:

 

jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv data-sources tables list --data-source DEMOFED --catalog ORASRV1 --schema GELDM
Name         Type    Native Catalog
CARSVIEW1    VIEW    NULL
fish_sas     TABLE   NULL
SAS_CAR      TABLE   NULL
sas_cars     TABLE   NULL
TEST_TABLE   TABLE   NULL
TEST2        TABLE   NULL
TESTVIEW1    VIEW    NULL

jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv data-sources tables list --data-source DEMOFED --catalog BASECATR1 --schema SCHEMAR1
Name         Type    Native Catalog
FISH_SAS     TABLE   BASECATR1
SAS_CARS     TABLE   BASECATR1
TEST         TABLE   BASECATR1
TEST_TABLE   TABLE   BASECATR1
jumpuser@p02190-jump-vm:/viya-share/gelenv/data$

 

List a few rows from the Federated Data Source tables.

 

You can access the data from Oracle and BASE data sources by specifying the corresponding catalog and schema in the CLI statement.

 

Code:

 

sas-viya dagentsrv sql --sql "select * from ORASRV1.GELDM.TEST_TABLE  where id < 10 " --dsn DEMOFED

sas-viya dagentsrv sql --sql "select * from BASECATR1.SCHEMAR1.SAS_CARS where Make='Acura' " --dsn DEMOFED

 

Log:

 

jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv sql --sql "select * from ORASRV1.GELDM.TEST_TABLE  where id < 10 " --dsn DEMOFED
DATE_VALUE   ID   TEXT_VALUE
==========   ==   ==========
             1    EanOFzMlzSHKhhPTolDW
             2    jBctxUPyQscSbnNiRsNU
             3    UVwYWRHVwoELUyXIeEPF
             4    RtvXPzHgJtXrLHXtBSRE
             5    bMbpjSRxWGfODrkwgDaA
             6    ozMjNApZjIrSpHdOpNVO
             7    YwPcHobUBKFEPLabfJBa
             8    pvnsRhySuOywHQHAKiBA
             9    nYDsRnmPPCLJzQNwaZiq
jumpuser@p02190-jump-vm:/viya-share/gelenv/data$



jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv sql --sql "select * from BASECATR1.SCHEMAR1.SAS_CARS where Make='Acura' " --dsn DEMOFED
Cylinders   DriveTrain   EngineSize   Horsepower   Invoice   Length   MPG_City   MPG_Highway   MSRP    Make    Model                      Origin   Type     Weight   Wheelbase
=========   ==========   ==========   ==========   =======   ======   ========   ===========   ====    ====    =====                      ======   ====     ======   =========
6           All          3.5          265          33337     189      17         23            36945   Acura    MDX                       Asia     SUV      4451     106
4           Front        2.4          200          24647     183      22         29            26990   Acura    TSX 4dr                   Asia     Sedan    3230     105
6           Front        3.5          225          39014     197      18         24            43755   Acura    3.5 RL 4dr                Asia     Sedan    3880     115
6           Rear         3.2          290          79978     174      17         24            89765   Acura    NSX coupe 2dr manual S    Asia     Sports   3153     100
4           Front        2            200          21761     172      24         31            23820   Acura    RSX Type S 2dr            Asia     Sedan    2778     101
6           Front        3.2          270          30299     186      20         28            33195   Acura    TL 4dr                    Asia     Sedan    3575     108
6           Front        3.5          225          41100     197      18         24            46100   Acura    3.5 RL w/Navigation 4dr   Asia     Sedan    3893     115
jumpuser@p02190-jump-vm:/viya-share/gelenv/data$

 

Create a new object schema (data masked and un-masked view) in Federated Data Source

 

Create a View (Schema) in Federated Data Source.

 

You can create a view in Federated Data Source based on two different data source tables. The data reside at the original place (Database / BASE table), only schema is created at Federated Data Source.

 

Code:

 

sas-viya dagentsrv  views create --dsn DEMOFED --name baseorav2  --sql "select a.ID, a.DATE_VALUE, b.TEXT_VALUE from  BASECATR1.SCHEMAR1.TEST_TABLE_ORA_IMPORTED a , ORASRV1.GELDM.TEST_TABLE b where a.ID = b.ID  and a.ID < 10 "

 

Log:

 

jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv  views create --dsn DEMOFED --name baseorav2  --sql "select a.ID, a.DATE_VALUE, b.TEXT_VALUE from  BASECATR1.SCHEMAR1.TEST_TABLE_ORA_IMPORTED a , ORASRV1.GELDM.TEST_TABLE b where a.ID = b.ID  and a.ID < 10 "
View successfully created.
jumpuser@p02190-jump-vm:/viya-share/gelenv/data$

 

Access the data from View (Schema) in Federated Data Source.

 

Code:

 

sas-viya dagentsrv sql --sql "select * from BASEORAV2 " --dsn DEMOFED

 

Log:

 

jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv sql --sql "select * from baseorav2 " --dsn DEMOFED
DATE_VALUE   ID   TEXT_VALUE
==========   ==   ==========
             1    EanOFzMlzSHKhhPTolDW
             2    jBctxUPyQscSbnNiRsNU
             3    UVwYWRHVwoELUyXIeEPF
             4    RtvXPzHgJtXrLHXtBSRE
             5    bMbpjSRxWGfODrkwgDaA
             6    ozMjNApZjIrSpHdOpNVO
             7    YwPcHobUBKFEPLabfJBa
             8    pvnsRhySuOywHQHAKiBA
             9    nYDsRnmPPCLJzQNwaZiq
jumpuser@p02190-jump-vm:/viya-share/gelenv/data$

 

Create a View (Schema) with data masked at Federated Data Source.

 

You can create a view in Federated Data Source with data columns masked. The original source data columns are not masked but access to data via Federated view is masked. This feature enables the CDE administrator to mask some data from specific user groups.

 

Code:

 

sas-viya dagentsrv  views create --dsn DEMOFED --name carsviewmask  --sql "select MAKE, MODEL,TYPE, syscat.dm.mask('ENCRYPT',PUT("INVOICE",8.), 'alg', 'AES') AS INVOICE_MASK  from  ORASRV1.GELDM.SAS_CARS "

 

Log:

 

jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv  views create --dsn DEMOFED --name carsviewmask  --sql "select MAKE, MODEL,TYPE, syscat.dm.mask('ENCRYPT',PUT("INVOICE",8.), 'alg', 'AES') AS INVOICE_MASK  from  ORASRV1.GELDM.SAS_CARS "
View successfully created.
jumpuser@p02190-jump-vm:/viya-share/gelenv/data$

 

Access the data from View with masked data at Federated Data Source.

 

You can create a view in Federated Data Source with data columns masked. The original source data columns are not masked but access to data via Federated view is masked. This feature enables the CDE administrator to mask some data from specific user groups.

 

Code:

 

sas-viya dagentsrv sql --sql "select * from CARSVIEWMASK  where Make='Acura' " --dsn DEMOFED

 

Log:

 

jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv sql --sql "select * from CARSVIEWMASK  where Make='Acura' " --dsn DEMOFED
INVOICE_MASK                           Make    Model                      Type
============                           ====    =====                      ====
CAA68FFFB0069143BA851C3B381DCB0C720E   Acura    MDX                       SUV
A9BFE48C4ECE7328D6D9F13BF3376E59574A   Acura    TSX 4dr                   Sedan
AED8143F92CA41424701E3EC79EBC859EDD2   Acura    3.5 RL 4dr                Sedan
45F12849A89770A4128638BF9814549E5C92   Acura    NSX coupe 2dr manual S    Sports
CC094DBC9DB884557BD329B535104C67593B   Acura    RSX Type S 2dr            Sedan
64226CB01D9DFF2842A0215E6E67F60D4856   Acura    TL 4dr                    Sedan
DE3AB29C43489B60976D4FADA095A58B817A   Acura    3.5 RL w/Navigation 4dr   Sedan
jumpuser@p02190-jump-vm:/viya-share/gelenv/data$

 

SAS Compute Server Access to Federated Data Source at Remote Data Agent

 

With the Federated Data Source configured and schema defined at the Remote Data Agent, users can access Federated data tables from the SAS Compute Server using the CDE LIBNAME statement. The following code describes the access of a Federated table.

 

Code:

 

IBNAME cdefed1 cde dataagentname="sas-data-agent-server-remote"
dsn=DEMOFED preserve_tab_names=yes;

Proc SQL outobs=5;
select * from cdefed1.carsviewmask;
run;quit;

 

Log:

 

80
81   LIBNAME cdefed1 cde dataagentname="sas-data-agent-server-remote"
82   dsn=DEMOFED preserve_tab_names=yes;
NOTE: Libref CDEFED1 was successfully assigned as follows:
      Engine:        CDE
      Physical Name: DEMOFED
83


91
92   Proc SQL outobs=5;
93   select * from cdefed1.carsviewmask;
WARNING: Statement terminated early due to OUTOBS=5 option.
94   run;quit;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: The PROCEDURE SQL printed page 12.
NOTE: PROCEDURE SQL used (Total process time):
      real time           1.00 seconds
      cpu time            0.08 seconds

 

CAS Access to Federated Data Source at Remote Data Agent

 

With the Federated Data Source configured and schema defined at the Remote Data Agent, users can access Federated data tables from the CAS using the CDE data connector. The following code describes the CAS access to the Federated table using the CDE CASLIB statement.

 

Code:

 

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

caslib cdefed datasource=(
      srctype="clouddex",
      dataAgentName="sas-data-agent-server-remote",
      conopts="dsn=DEMOFED"
) libref=cdefed;

/* CAS load from CDE RDA Federated table  */
proc casutil  incaslib="cdefed"  outcaslib="cdefed";
  load casdata="baseorav2" casout="baseorav2" replace ;
  list tables ;
run;
quit;

cas mysession terminate;

 

Log:

 

3
84   caslib cdefed datasource=(
85         srctype="clouddex",
86         dataAgentName="sas-data-agent-server-remote",
87         conopts="dsn=DEMOFED"
88   ) libref=cdefed;
NOTE: Executing action 'table.addCaslib'.
NOTE: 'CDEFED' is now the active caslib.
NOTE: Cloud Analytic Services added the caslib 'CDEFED'.



81   /* CAS load from CDE RDA Federated table  */
82   proc casutil  incaslib="cdefed"  outcaslib="cdefed";
NOTE: The UUID '60e9bfc7-ac8d-ff4e-b122-33ed678dee4d' is connected using session MYSESSION.
83     load casdata="baseorav2" casout="baseorav2" replace ;
NOTE: Executing action 'table.loadTable'.
NOTE: Connecting using the OAuth token for CAS user 'geldmui@gelenable.sas.com'.
WARNING: Using server default session timeout of 3600
NOTE: Cloud Analytic Services made the external data from baseorav2 available as table BASEORAV2 in caslib cdefed.
NOTE: Action 'table.loadTable' used (Total process time):

 

Important Links:

 

Cloud Data Exchange for the SAS Viya Platform

 

Communities posts:

SAS Cloud Data Exchange for the SAS Viya Platform

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

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

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

Configuring Oracle Data Source at Remote Data Agent (Cloud Data Exchange)

 

 

Find more articles from SAS Global Enablement and Learning here.

Version history
Last update:
‎11-09-2023 09:47 PM
Updated by:
Contributors

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Labels
Article Tags