Cloud Data Exchange (CDE) is a capability to securely access customer’s on-premise data (behind a firewall) from SAS Viya 3.4 application hosted at a public or private cloud environment. Before a user can access the data through Cloud Data Exchange the Data Agent Administrator must create data services at the Data Agent server. This blog is about creating different types of data services at Data Agent server.
Depending on the data source (databases) the data agent offers various connection parameters for each data service. The pre-requisites and process to create a data service for BASE SAS, Hadoop, Oracle etc. are little different from each other. For understanding purpose, let’s look at the three types of data services, a BASE SAS, a JDBC (Hadoop/Hive) and an ODBC (MSSQL) data service. When adding a new data service you can always use CLI statement “sas-admin dagentsrv services create -help ” to list out all the available option for a data service. To create a supported database source service, corresponding database SAS/ACCESS product license is required at Data Agent Server.
BASE SAS data service (In-built):
Data Agent Server comes with a BASE SAS data service already configured. To make a set of .sas7bdat data files available under BASE data service, Data Agent Administrator need to add a new catalog and schema with folder location containing data files. The CLI statement is used to create a new catalog and schema at Data Agent Server. The admin user must have read/write access to the data folder and files to access the data from CAS through data service. While creating a schema for BASE SAS data service, the parameter “--primary-path” accept the folder location name without single or double quotes. A catalog can have multiple schemas attached with different folder location.
The following CLI statement describes the creation of a new catalog and schema under BASE SAS data service. The CLI statement also lists the available data tables from the newly created catalog and schema.
Adding a catalog to BASE SAS data service.
[sastest1@sasdagnt01 ~]$ sas-admin dagentsrv services list;
Name Type Domain Version Options
__SERVER__ server --- 2.3 PURGE_CACHE=30;CACHE=(NAME=AS;TIMEOUT=300)
BASE base --- 2.3 ---
[sastest1@sasdagnt01 ~]$ sas-admin dagentsrv catalogs create base --name BASECAT
[sastest1@sasdagnt01 ~]$
[sastest1@sasdagnt01 ~]$ sas-admin dagentsrv catalogs list
Name Service Native Catalog Options
BASECAT BASE
Adding multiple Schemas with newly created catalog for BASE SAS data service. Note, the folder location value is not in single or double quotes
[sastest1@sasdagnt01 ~]$ sas-admin dagentsrv schemas create base --catalog BASECAT --name SCHEMA1 --primary-path /data/sasdata/
[sastest1@sasdagnt01 ~]$
[sastest1@sasdagnt01 ~]$ sas-admin dagentsrv schemas create base --catalog BASECAT --name myschema --primary-path /data/mydata/
[sastest1@sasdagnt01 ~]$ sas-admin dagentsrv schemas list
Name Catalog Options
SCHEMA1 BASECAT PRIMARYPATH=/data/sasdata/
myschema BASECAT PRIMARYPATH=/data/mydata/
Testing BASE SAS data service and listing tables from newly created catalog and schema.
[sastest1@sasdagnt01 ~]$ sas-admin dagentsrv ds test --name BASE
Successfully connected to data source BASE.
[sastest1@sasdagnt01 ~]$
[sastest1@sasdagnt01 ~]$ sas-admin dagentsrv data-sources tables list --data-source BASE --catalog BASECAT --schema SCHEMA1
Name Type Native Catalog
AIRLINE_OPINION TABLE BASECAT
CODINGCOMPLAINTS TABLE BASECAT
CONSUMER_COMPLAINTS TABLE BASECAT
COUNT_DATA TABLE BASECAT
…
……..
[sastest1@sasdagnt01 ~]$
If a user does not have access to the data files folder, he or she cannot access the data through data service. The CLI statement does not generate any error message when a schema is created using a non-accessible folder. But, the table list CLI statement does not show any object from schema using non-accessible folder. The following example describes that folder /data/mydata is owned by ‘root’ user and no other user have read/write permission to it, hence CLI statement does not show any table from schema pointing to this folder.
[root@sasdagnt01 data]# ls -l
total 4
drwx------. 2 root root 42 Oct 26 15:47 mydata
drwxr-xr-x. 2 sas sas 4096 Sep 24 23:41 sasdata
[root@sasdagnt01 data]# ls -l mydata
total 10752
-rwxr-xr-x. 1 root root 11010048 Oct 26 15:47 consumer_complaints.sas7bdat
[root@sasdagnt01 data]#
[sastest1@sasdagnt01 ~]$ sas-admin dagentsrv data-sources tables list --data-source BASE --catalog BASECAT --schema myschema
Name Type Native Catalog
[sastest1@sasdagnt01 ~]$
Hadoop/Hive data service (JDBC Connection):
To access a Hadoop/Hive data tables through Data Agent Server, an admin user need to add a new data service. Before adding a new hive source service, the Data Agent Server must have compatible Hadoop Jars and Config files staged and /etc/sysconfig/sas/sas-viya-dagentsrv-default configuration file updated with the path of Jars and config files. The access from Data Agent Server to Hadoop/Hive server is based on JDBC connection and JAVA home must be defined with a valid path in the configuration file. The lib/amd64/server must be available under $JAVA_HOME location. The LD_LIBARARY_PATH must include the $JAVA_HOME/lib/amd64/server, $DA_HADOOP_JAR_PATH and $DA_HADOOP_CONFIG_PATH. Once config file updated, Data Agent Server must be restarted to pick up the changes.
Note: If a Hadoop/Hive service is not working it’s due to the incorrect value of $JAVA_HOME or non-compatible Hadoop Jars and Config files are staged at Data Agent Server.
An example of valid entry in /etc/sysconfig/sas/sas-viya-dagentsrv-default config file for Hadoop/Hive service.
export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.171-8.b10.el7_5.x86_64/jre
export DA_HADOOP_CONFIG_PATH=/opt/hadoop/HDP/conf
export DA_HADOOP_JAR_PATH=/opt/hadoop/HDP/lib:/opt/hadoop/HDP/spark
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$JAVA_HOME/lib/amd64/server:$DA_HADOOP_JAR_PATH:$DA_HADOOP_CONFIG_PATH
With the above configuration in place, an admin user can add a hive data service at Data Agent Server. For seamless access of data from SAS Viya to Hive via Data Agent Server, you must create a shadow security domain at Data Agent Server. Under the security domain, attach one or more identity user with database access credential. The credentials for Hadoop/hive environment attached with a security domain never leaves the on-premise data center and securely stored in a vault at Data Agent Server. When creating the hive data service use the shadow security domain to access hive database. As a part of hive data service creation, it also creates a catalog with the same name at Data Agent Server.
The Following CLI describe the creation of a shadow domain, attaching identity user and database credential with shadow domain, creating Hadoop/Hive data service, testing the Hive data service and listing data table from hive service.
Adding a shadow domain, attaching identity user and database credential with shadow domain.
[sastest1@sasdagnt01 ~]$ sas-admin dagentsrv security domains create --domain HIVE_DOM
[sastest1@sasdagnt01 ~]$
[sastest1@sasdagnt01 ~]$ sas-admin dagentsrv security domains list
ID Type Label Description
HIVE_DOM shadow
[sastest1@sasdagnt01 ~]$
[sastest1@sasdagnt01 ~]$ sas-admin dagentsrv security credentials create --domain HIVE_DOM --identity viyademo01 --username gatedemo100 --password xxxxx
[sastest1@sasdagnt01 ~]$
[sastest1@sasdagnt01 ~]$ sas-admin dagentsrv security credentials list --domain HIVE_DOM
Identity Type UserId ShadowKey
viyademo01 user gatedemo100 5E783B9F-128E-3645-9C72-5C00A88461E9
[sastest1@sasdagnt01 ~]$
Adding a new Hadoop/Hive data service and listing data table from hive data service.
[sastest1@sasdagnt01 ~]$ sas-admin dagentsrv services create hive --name hivesrv1 --domain HIVE_DOM --server "sasxxx.race.sas.com" --schema casdm
[sastest1@sasdagnt01 ~]$
[sastest1@sasdagnt01 ~]$ sas-admin dagentsrv services list
Name Type Domain Version Options
__SERVER__ server --- 2.3 PURGE_CACHE=30;CACHE=(NAME=AS;TIMEOUT=300)
BASE base --- 2.3 ---
hivesrv1 hive HIVE_DOM 2.3 CONOPTS=(DRIVER=HIVE;SCHEMA=casdm;SERVER=sasxxx.race.sas.com);
CASE_SENSITIVITY=(OBJECT=F;COLUMN=F)
[sastest1@sasdagnt01 ~]$ sas-admin dagentsrv ds test --name hivesrv1
Successfully connected to data source hivesrv1.
[sastest1@sasdagnt01 ~]$
[sastest1@sasdagnt01 ~]$ sas-admin dagentsrv data-sources tables list --data-source hivesrv1 --catalog hivesrv1 --schema casdm
Name Type Native Catalog
cars_hv TABLE
heart_hv TABLE
hello_world TABLE
prdsal3_hv TABLE
[sastest1@sasdagnt01 ~]$
MS-SQL Server data service (ODBC Connection):
To access an MS SQL Server database tables through the Data Agent Server, an admin user need to add a new data service. The access from Data Agent Server to MS SQL Server is based on ODBC connection. Before adding a new MS-SQL data service, the Data Agent Server must have Unix ODBC client installed and /etc/sysconfig/sas/sas-viya-dagentsrv-default configuration file updated with following ODBC variables. You also must have an entry in odbc.ini for DSN used for data service. Once config file updated, Data Agent Server must be restarted to pick up the changes.
An example of valid entry in /etc/sysconfig/sas/sas-viya-dagentsrv-default config file for ODBC based MSSQL service.
export ODBCHOME=/opt/sas/viya/home/lib64/accessclients
export ODBCSYSINI=/opt/sas/odbc
export ODBCINI=/opt/sas/odbc/odbc.ini
export ODBCINST=/opt/sas/odbc/odbcinst.ini
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib64:/opt/sas/viya/home/lib64
An entry in /opt/sas/odbc/odbc.ini for DSN sqls_CASDM
…
………..
[sqls_CASDM]
Driver=/opt/sas/viya/home/lib64/accessclients/lib/S0sqls27.so
Description=SAS Institute, Inc 7.1 SQL Server Wire Protocol
AlternateServers=
AlwaysReportTriggerResults=0
AnsiNPW=1
ApplicationName=
….
………..
With the above configuration in place, an admin user can add an MSSQL data service at Data Agent Server. The process to add an MSSQL data service is same as described in Hadoop/Hive data service section. While creating an MSSQL Server data service you can use “--register-all” or “--native-catalogs” option to register all or selective native catalogs from the source database to Data Agent Server.
The Following CLI statement describes the creation of a shadow domain, attaching identity user and database credential with shadow domain, creating MSSQL data service, testing the MSSQL data service, and listing data table from MSSQL data service.
Adding a shadow domain, attaching identity user and database credential with shadow domain.
[sastest1@sasdagnt01 ~]$ sas-admin dagentsrv security domains create --domain MSSQL_DOM
[sastest1@sasdagnt01 ~]$
[sastest1@sasdagnt01 ~]$ sas-admin dagentsrv security domains list
ID Type Label Description
MSSQL_DOM shadow
[sastest1@sasdagnt01 ~]$
[sastest1@sasdagnt01 ~]$ sas-admin dagentsrv security credentials create --domain MSSQL_DOM --identity viyademo01 --username casdm --password 'xxxxxxx'
[sastest1@sasdagnt01 ~]$
[sastest1@sasdagnt01 ~]$ sas-admin dagentsrv security credentials list --domain MSSQL_DOM
Identity Type UserId ShadowKey
viyademo01 user casdm B038225C-A05F-574B-BDD1-89451008A810
[sastest1@sasdagnt01 ~]$
Adding a new MSSQL data source service using DNS name from odbc.ini file and listing data table from MSSQL data source service.
[sastest1@sasdagnt01 ~]$ sas-admin dagentsrv services create sqlserver --name sqlsrv1 --domain MSSQL_DOM --database-name "casdm" --dsn sqls_CASDM
[sastest1@sasdagnt01 ~]$
[sastest1@sasdagnt01 ~]$ sas-admin dagentsrv ds test --name sqlsrv1
Successfully connected to data source sqlsrv1.
[sastest1@sasdagnt01 ~]$
[sastest1@sasdagnt01 ~]$ sas-admin dagentsrv services list
Name Type Domain Version Options
__SERVER__ server --- 2.3 PURGE_CACHE=30;CACHE=(NAME=AS;TIMEOUT=300)
BASE base --- 2.3 ---
sqlsrv1 sqlserver MSSQL_DOM 2.3 CONOPTS=(DRIVER=MSSQLSVR;DATABASE=casdm;ODBC_DSN=sqls_CASDM);
CASE_SENSITIVITY=(OBJECT=F;COLUMN=F)
[sastest1@sasdagnt01 ~]$ sas-admin dagentsrv data-sources tables list --data-source sqlsrv1 --catalog casdm --schema dbo
Name Type Native Catalog
hello_world TABLE casdm
[sastest1@sasdagnt01 ~]$
Resources:
