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