SAS Cloud Data Exchange Preview
Cloud Data Exchange(CDE) came out with 18W38 release, let’s take a look at the software to get an idea of what it look like.
Cloud Data Exchange is a data connection capability to securely access customer’s on-premise data (behind a firewall) from a public or private cloud applications (read SAS Viya 3.4). A customer may have on-premise data in various data sources (like Oracle, Teradata, Hadoop etc.). CDE enables users to securely access these data sources from cloud-based applications (SAS Viya 3.4). CDE negotiates customer’s on-premise firewall securely and responsibly while transferring high volume data between on-premise data sources and cloud application (SAS Viya 3.4). CDE stores on-premise data source credentials (userid/password) in a secured vault. So, these never have to be stored or accessed outside the on-premise firewall.
A high-level topology for Cloud Data Exchange and the SAS Viya 3.4 environment.
CDE provides a command line Input (CLI) interface for data source connection administration and control. The SAS Data Agent Server resides on-premises (behind the firewall) where you can configure the services for data access using the CLI. There is no visual interface. The Data Agent CLI operates as a plugin to the “sas-admin” CLI and is available for Linux, Windows and the MacOS operating systems.
We'll look at some specifics of the Cloud Data Exchange and Data Agent components below.
Before you can use the CLI, you need to perform the following steps on the machine running CLI (e.g. on-premise server).
- Set the SSL_CERT_FILE= environment variable.
CLI uses encrypted communication for all network commination and requires a server certificate in the trust store.
e.g. export SSL_CERT_FILE=/opt/sas/viya/config/etc/SASSecurityCertificateFramework/cacerts/trustedcerts.pem - You may include the /opt/sas/viya/home/bin folder into $PATH system variable for easy access to CLI statement.
- Set the base URL.
Most of the CLI commands require support from a REST interface hence commands must reflect the base URL which describes where the endpoints are located. You can persist the default endpoint in your SAS profile by running “sas-admin profile init” statement. The SAS profile is stored under the ~/.sas subdirectory.
Example:$ sas-admin profile init Enter configuration options: Service Endpoint> https://dataagent-test-fri-cloud.dmmdev.sashq-d.openstack.sas.com:443 Output type (text|json|fulljson)> text Enable ANSI colored output (y/n)?> y Saved 'Default' profile to /home/centos/.sas/config.json. $
You can also view the existing profile by issuing the following statement:$ sas-admin profile show Current settings for the [Default] profile: Setting Current Value ssl-cert-file /opt/sas/viya/config/etc/SASSecurityCertificateFramework/cac... refresh-token access-token eyJhbGciOiJSUzI1NiIsImtpZCI6ImxlZ2FjeS10b2tlbi1rZXkiLCJ0eXAi... expiry 2018-08-21T06:54:30Z ansi-colors-enabled true oauth-client-id sas.cli output text sas-endpoint https://dataagent-test-fri-cloud.dmmdev.sashq-d.openstack.sas.com:443 $ - Obtain login credentials
All commands that works with the SAS VIYA REST interface uses OAUTH2 login credentials to create a token which provides authentication and authorization for each server. This token must be acquired one time before executing additional commands. When token expires, you must acquire a new token.
Example:$ sas-admin auth login --user dbitest --password XXXXXXXXXXX Enter credentials for https://dataagent-test-fri-cloud.dmmdev.sashq-d.openstack.sas.com:443: Login succeeded. Token saved. $
Help on CLI statements
For CDE CLI newbies, you can use “-h” at each stage to get help when you don’t know what the commands, plugins, and options are available to execute.
Example:
$ sas-admin -h
NAME:
sas-admin - SAS Administrative Command Line Interface
USAGE:
sas-admin [global options] command [command options] [arguments...]
……………..
………………………
$ sas-admin dagentsrv -h
NAME:
sas-dagentsrv
USAGE:
sas-admin dagentsrv command [command options] [arguments...]
COMMANDS:
catalogs Operate on catalogs in the Data Agent
data-agents, servers Operate on Data Agent servers
data-services, services Operate on data services on the Data Agen
………………….
………………………
$ sas-admin dagentsrv servers -h
NAME:
sas-dagentsrv data-agents - Operate on Data Agent servers
USAGE:
sas-dagentsrv data-agents command [command options] [arguments...]
COMMANDS:
help, h Shows a list of commands or help for one command.
list List available Data Agent servers
set-default Set the default Data Agent server name
…………..
……………………….
$ sas-admin dagentsrv services create oracle -h
NAME:
sas-dagentsrv data-services create oracle - Create an ORACLE data service definition
USAGE:
sas-dagentsrv data-services create oracle [command options]
OPTIONS:
--case-sensitive-columns Specify if column names are case sensitive
--case-sensitive-objects Specify if object names are case sensitive
--catalog Specify the name of the logical catalog for this data service
--ct-preserve Preserve result set column types, can be STRICT, SAFE, FORCE, or FORCE_COL_SIZE
--data-agent, -d Specify the name of the Data Agent
--database-password Specify the password used to connect to the database
Frequently used CLI statement
To list available Data Agent Servers:
$ sas-admin dagentsrv servers list
Name Host Port Attributes
dagentsrv-shared-default dataagent-test-fri-onprem.dmmdev.sashq-d.openstack.sas.com 443 ssl=Y;contextroot=dagentsrv-shared-default-private
$
To list available Data services (connections) on the Data Agent Server:
$ sas-admin dagentsrv services list
Name Type Domain Version Options
__SERVER__ server --- 2.3 PURGE_CACHE=111;CONNECTION_POOL_TIMEOUT=11;CACHE=(NAME=AS;TIMEOUT=300)
BASE base --- 2.3 ---
generic_fed generic_fed generic_fed 2.3 ---
netezza netezza netezza 2.3 ---
sqlserver sqlserver sqlserver 2.3 ---
DSNS_PAG_Test0 hive DSNS_PAG_Test0 2.3 ---
….
……………..
DSNS_PAG_Test14 fedsvr DSNS_PAG_Test14 2.3 ---
orasrv1 oracle ORACLE_DOMAIN 2.3 CONOPTS=(DRIVER=ORACLE;PATH=tktsora);CASE_SENSITIVITY=(OBJECT=T;COLUMN=T)
orasrv3 oracle ORACLE_DOMAIN 2.3 CONOPTS=(DRIVER=ORACLE;PATH=orcl12ep);CASE_SENSITIVITY=(OBJECT=T;COLUMN=T)
To see supported source data sources:
$ sas-admin dagentsrv services create -h
NAME:
sas-dagentsrv data-services create - Generate a data service definition
USAGE:
sas-dagentsrv data-services create command [command options]
COMMANDS:
db2unxpc, db2 Create a DB2 for Unix and PC data service definition
federation-server, fedsvr Create a data service definition to communicate with Federation Server
hive Create an Apache Hive data service definition
odbc Create an ODBC data service definition
oracle Create an ORACLE data service definition
postgres Create a POSTGRES data service definition
redshift Create a RedShift data service definition
saphana, hana Create a SAP HANA data service definition
sqlserver, sqlsvr Create SQL Server (via ODBC) data service definition
teradata Create a Teradata data service definition
Configuring a new Oracle Data Service (connection) on the Data Agent Server
As a Data Agent Administrator, execute the following steps to create a new database connection on the Data Agent.
(Note: As a part of post installation steps, administrator adds user to Data Agent Administrator group)
- To configure and connect to a database environment you need the respective database client installed on Data Agent Server. The environment variable for all source databases are located in /opt/sas/viya/config/etc/sysconfig/dagentsrv/default/sas-dagentsrv config file.
For an Oracle database connection, the following variables are used. Other data sources use different variables but the process of exporting the required environment variables is the same.export ORACLE_HOME=/dbi/oracle/12c export TWO_TASK=oraclev12 export ORAENV_ASK=NO export SASORA=V9 export PATH=$PATH:$ORACLE_HOME/bin export TNS_ADMIN=/~/fedadmin/tnsnames export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib - Create a security Domain(shadow) for database access.
$ sas-admin dagentsrv security domains create --domain ORACLE_DOMAIN
View the created Domain:$ sas-admin dagentsrv security domains list ID Type Label Description DACLI_SQLSERVER_DOMAIN shadow ORACLE_DOMAIN shadow
To delete a Domain:$ sas-admin dagentsrv security domains delete --domain ORACLE_DOMAIN --cascade - Add database credentials and identity user to the newly created Domain(shadow).
(Note: "--username" is for valid database user (in this case oracle user); "--password" is for valid database access password.)$ sas-admin dagentsrv security credentials create --domain ORACLE_DOMAIN --identity dbitest --username TKTSTST1 --password XXXXXXX
View the shadow credentials in a domain:$ sas-admin dagentsrv security credentials list --domain ORACLE_DOMAIN --identity dbitest Identity Type UserId ShadowKey dbitest user TKTSTST1 000737E0-11C9-4A4F-9361-6B9B71B7E1DD $ - Create oracle database service at Data Agent using newly created Domain(shadow).
$ sas-admin dagentsrv services create oracle --name orasrv2 --path TKTSORA --domain ORACLE_DOMAIN (Note: --path - is oracle instance name from tnsnames.ora file located under TNS_ADMIN=/~/fedadmin/tnsnames folder)
View the newly created data service:$ sas-admin dagentsrv services list Name Type Domain Version Options __SERVER__ server --- 2.3 PURGE_CACHE=111;CONNECTION_POOL_TIMEOUT=11;CACHE=(NAME=AS;TIMEOUT=300) BASE base --- 2.3 --- generic_fed generic_fed generic_fed 2.3 --- …. ……. orasrv2 oracle ORACLE_DOMAIN 2.3 CONOPTS=(DRIVER=ORACLE;PATH=TKTSORA);CASE_SENSITIVITY=(OBJECT=T;COLUMN=T)
To delete a data services:$ sas-admin dagentsrv services delete --cascade --name orasrv2 - Test the newly created oracle database service at Data Agent.
$ sas-admin dagentsrv ds test --name orasrv2 Successfully connected to data source orasrv2. $
Creating a CASLib on SAS Viya to access data from newly created Data Service at Data Agent Server
Once the SAS data agent server is registered, you can create a CASLib and access the data through the Data Agent. In Environment Manager, create a new CASLib using source type as CDE or use your own SAS code to create a CDE CASLib. Once you have the CDE type CASLib created, you can load data to CAS from the source environment and save it back to source environment as well.
The following screen capture shows the creation of a CDE type CASLIB.
On the advanced tab, you can use additional parameter to filter tables from specific schema.
The following screen captures shows the details of CDE type CASLib.
The following screen capture shows the data tables available from the CDE type CASLib and can be loaded into CAS (in-memory).
