BookmarkSubscribeRSS Feed

SAS Cloud Data Exchange Preview

Started ‎12-14-2018 by
Modified ‎12-14-2018 by
Views 3,400

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_Prevw_1_0.png

 

 

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

     

    $ sas-admin dagentsrv security credentials create --domain ORACLE_DOMAIN --identity dbitest --username TKTSTST1 --password XXXXXXX
    
    (Note: "--username" is for valid database user (in this case oracle user); "--password" is for valid database access password.)

     

    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.

 

CDE_Prevw_2.png

 

On the advanced tab, you can use additional parameter to filter tables from specific schema.

 

CDE_Prevw_3.png

 

The following screen captures shows the details of CDE type CASLib.

 

CDE_Prevw_4.png

 

The following screen capture shows the data tables available from the CDE type CASLib and can be loaded into CAS (in-memory).

 

CDE_Prevw_5.png

 

 

Version history
Last update:
‎12-14-2018 04:33 PM
Updated by:
Contributors

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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