BookmarkSubscribeRSS Feed

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

Started ‎09-01-2023 by
Modified ‎09-01-2023 by
Views 429

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.

 

uk_1_CDE_Confgiure_Oracle_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.

 

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-remote

 

Create 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.

Version history
Last update:
‎09-01-2023 03:25 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 Tags