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